Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Design a reverse ETL flow

Pipelines & scenarios · System Design Questions

Design a reverse ETL flow

Hardpipelines-42
scenarioreverse-etlcrmsyncupsert

Question

Marketing wants warehouse customer segments synced to the CRM every hour. How do you build it?

Solution

Reverse ETL means pushing data from the warehouse back into operational tools. Here the warehouse computes customer segments, and a sync job writes them to the CRM every hour. The risks are on the CRM side: API limits, partial failures, and overwriting people's data.

warehouse: segments table -> diff vs last synced snapshot -> sync job -> CRM API

Compute in the warehouse

Build the segment logic as a normal tested model (for example crm_customer_segments), with one row per customer: the external CRM id, the segment, and a few attributes. Do the heavy logic here, in SQL, where it can be tested and versioned, and keep the sync step dumb.

Sync only the changes

Do not send 5 million rows every hour. Keep a copy of what you last synced (a snapshot table with a row hash), and compare:

SELECT n.crm_id, n.segment, n.row_hash
FROM crm_customer_segments n
LEFT JOIN synced_snapshot s ON s.crm_id = n.crm_id
WHERE s.crm_id IS NULL OR s.row_hash <> n.row_hash;

This returns new and changed customers only. Also find rows that disappeared, and decide what that means in the CRM (clear the field, or leave it).

Talk to the API properly

  • Batch requests (bulk endpoints, if offered), within payload limits.
  • Respect rate limits, with backoff on 429 responses, and a cap on concurrency.
  • Upsert by the external id, so retries do not create duplicates.
  • Only update the fields you own, and never overwrite fields that sales users edit.

Failures and partial syncs

Some batches succeed while others fail. Record the result per row or per batch, and update the snapshot only for rows that succeeded. The next run then retries the failures. Alert when the failure rate is above a threshold, and keep rejected records with the API error message.

Build or buy

Hightouch and Census do all of this (diffing, rate limits, mapping, monitoring) with a UI. Buy when you have many destinations or want analysts to manage syncs. Build when the destination is unusual, or the volume and rules are simple enough for a small job.

Safeguards

Add a sanity check before each sync, such as "the segment size changed by more than 30 percent", and stop the sync for a person to look. A bad model upstream can otherwise push wrong data into the CRM within the hour.

PreviousNext