Skip to content
Notifications
Clear all

Has anyone tried using Hightouch syncs for the historical backfill part?

7 Posts
7 Users
0 Reactions
55 Views
(@brianw5)
Reputable Member
Joined: 4 months ago
Posts: 276
Topic starter   [#6088]

Hey folks! 👋

I'm currently neck-deep in planning a migration from our old, creaky CDP to a shiny new one (leaning towards RudderStack), and the part that's giving me the most pause is the **historical data backfill**. We've got about two years of user events that we absolutely need to have in the new system for cohort analysis and to maintain our existing ML models.

Like many of you, I'm trying to avoid writing and maintaining a massive, one-time backfill script. I've been exploring using our reverse ETL tool, **Hightouch**, to handle this, essentially treating my old CDP's warehouse tables as the source and my new CDP's API as the destination.

Has anyone actually gone down this road? I'm specifically curious about a few operational hurdles:

* **Volume & Throttling:** Hightouch syncs are great for ongoing, incremental work, but pumping millions of historical events feels like a different beast. Did you run into API rate limits from your *new* CDP? How did you manage the sync scheduling and potential retries?
* **Transformation Needs:** The schema between the old and new CDP is *never* a 1:1 match. I'm planning to use Hightouch's SQL models to do the field mapping, renaming, and maybe even some light logic to massage the data into the new format. Was this sufficient, or did you need an intermediate transformation layer?
* **Idempotency & Duplicates:** This is my biggest fear. If a sync fails partially and re-runs, how do you ensure you don't get duplicate events in the new CDP? I'm thinking of using a combination of Hightouch's sync timestamps and maybe a dedicated `backfill_id` property on the event.

My current test setup looks something like this in a Hightouch SQL model:

```sql
-- Example: Mapping old 'user_action' table to new 'track' event format
SELECT
user_id,
event_timestamp,
'Product Viewed' AS event, -- Hardcode new event name
JSON_OBJECT(
'product_id', object_id,
'legacy_event_name', event_name -- Carry over old name for reference
) AS properties,
'backfill_v1' AS context_backfill_id -- For idempotency tracking
FROM
legacy_cdp.user_actions
WHERE
event_name = 'view_product'
AND event_timestamp < '2024-01-01' -- Chunking by date
```

I'd love to hear any war stories, lessons learned, or if you concluded this was a terrible idea and went with a custom job instead. The appeal of using a tool we already know (and can monitor easily) is strong, but I don't want to walk into a trap.

bw


Automate all the things.


   
Quote
(@helenb)
Estimable Member
Joined: 4 months ago
Posts: 128
 

You mentioned using Hightouch's SQL models for field mapping. I've used them for accounting data, and my one caveat would be to watch out for date-time transformations. The warehouse, Hightouch, and the destination API can all interpret timestamps differently, especially with time zones. Did you have to adjust for that in your mapping logic?



   
ReplyQuote
(@kevinm)
Trusted Member
Joined: 4 months ago
Posts: 51
 

Oh man, time zones are a killer. We had the exact same issue. Our warehouse timestamps were in UTC, but Hightouch's preview would sometimes show the local conversion, and then the API expected ISO strings. It was messy.

I ended up forcing everything to UTC explicitly in the SQL model with `CONVERT_TIMEZONE` or `AT TIME ZONE` clauses before the field mapping. That way, Hightouch was just passing along a string that the destination API could interpret unambiguously.

Did you find a cleaner pattern for your accounting data? Forcing it in the SQL felt a bit heavy-handed.


Benchmark or bust


   
ReplyQuote
(@budget_minded_buyer)
Reputable Member
Joined: 7 months ago
Posts: 313
 

> forcing everything to UTC explicitly in the SQL model

That's the only way. "Heavy-handed" is just another word for "it works." The alternative is debugging phantom timezone drift for a week while your sync costs rack up.

Did you check if the forced UTC conversion hit your warehouse query performance? That's the real hidden cost on a two-year backfill.


always ask for a multi-year discount


   
ReplyQuote
(@martech_hopper_22)
Trusted Member
Joined: 7 months ago
Posts: 48
 

Did exactly this for a Shopify > Klaviyo backfill. It works but you need to manage the rate limits aggressively.

Your new CDP's API will absolutely throttle you. We had to:
- Batch down to 1k rows per sync
- Set syncs to run hourly, not continuously
- Use Hightouch's retry settings with long delays

For transformations, I'd build a staging view in your warehouse first, then point Hightouch at that. Makes the SQL model simpler and you can test the output before firing the sync. Saved us when we realized we needed to hash some PII before sending.


Trial number 47 this year.


   
ReplyQuote
(@lindar)
Eminent Member
Joined: 4 months ago
Posts: 18
 

Oh, I really appreciate you laying out the practical steps like that. The tip about building a staging view in the warehouse first is a lightbulb moment for me, because I was about to do all the logic directly in the Hightouch model. Being able to test the hashing or other transformations first would save so much anxiety.

When you say you set syncs to run hourly, how did you handle monitoring to know when the entire backfill was actually done? I'd be worried about setting it and then forgetting to check in, only to find it failed on day three and I've lost a chunk of time.



   
ReplyQuote
(@kubernetes_knight)
Estimable Member
Joined: 8 months ago
Posts: 68
 

Yeah, the timestamp triage is real. We hit that exact snag syncing from Snowflake to a custom API. The fix for us was actually *not* in the Hightouch SQL model, but in the destination mapping itself. Hightouch let us add a light transformation before the API call, so we formatted the timestamp string there to match the API's expected ISO 8601 with the 'Z' suffix. That kept the warehouse logic clean.

But you're right - forcing UTC upstream is probably safer. Did you run into any issues with daylight saving time boundaries in your accounting data? That's where our first approach fell apart.


YAML is not a programming language, but I treat it like one.


   
ReplyQuote