System design interview

Reverse ETL Architecture

MediumLocked55 min read

Push transformed warehouse data into operational tools (e.g. Snowflake → Salesforce) with sync frequency, conflict resolution, and rate limits.

system-designreverse-etlactivationintegration

Start here

The problem in plain words

The customer success team wants a health score for each customer inside Salesforce, where they do their daily work. Today someone exports a CSV from the warehouse every Monday and uploads it. By Thursday the scores are stale, the file has been edited by hand, and two sales reps are looking at different versions in their emails.

Normal data pipelines move data into the warehouse so people can analyse it. Reverse ETL does the opposite, on purpose. It takes a finished, trusted table in the warehouse and pushes it back out into the tools where people act, such as Salesforce, so the numbers show up where work happens.

It sounds easy until you meet the real problems. Salesforce limits how many calls you can make, a person may have edited the same field, and a retry must never create a second copy of a customer.

Words you will meet

Warehouse
The analytics database that holds cleaned, combined data, such as Snowflake or BigQuery.
CRM / Salesforce
The system sales and support teams use to manage customers. It is an operational tool, not an analytics one.
Reverse ETL
Syncing curated warehouse data out to operational tools like a CRM, so people can use it in their daily work.
Upsert
Update the record if it exists, create it if it does not.
External id
A key from your own system, such as account_id, stored on the CRM record so the sync can always find the same record again.
Cursor (incremental sync)
A saved position, such as the last updated_at seen, so each run sends only rows that changed.
API rate limit / quota
A cap on how many requests a service accepts per period. Exceed it and the service rejects calls.
Backoff
Waiting longer between retries after each failure, so you do not hammer a service that is already struggling.
Dead-letter queue
A holding area for records that keep failing, so they do not block the rest.
The Monday CSV problem
exportuploadThursdayWarehousehas the health scoreWeekly CSVexport and uploadSalesforcewhere reps workSales repsees stale or editeddata
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.Every Monday someone exports the health scores from the warehouse into a file.
  2. 2.They upload it to Salesforce by hand. The file may have been edited in between.
  3. 3.By Thursday the scores are stale, and nobody knows which version of the file is correct.

The numbers, as things you can picture

  • Hundreds of thousands to millions of recordsToo many to push one at a time, so sends happen in chunks.
  • A hard API quotaSalesforce only allows so many calls in a given period. The design must stay inside it.

What the interviewer wants

Design a safe sync from the warehouse into Salesforce that handles retries, conflicting edits and API limits, and that you can observe when something goes wrong.

Interview framing

Design reverse ETL: sync customer health scores from Snowflake to Salesforce. Handle sync frequency, conflict resolution, and API rate limits.

The interviewer wants activation architecture: curated warehouse insights pushed into tools people already live in, safely under retries and quotas.

Background from first principles

Analytics warehouses are great at computing derived traits: health scores, churn risk, LTV bands. Sales and CS teams live in CRM tools like Salesforce. If the score only exists in a BI dashboard, it does not change how an account executive prioritizes a call.

Historically teams exported CSV weekly. The file was stale by Tuesday and wrong by Thursday. Reverse ETL (sometimes called data activation) is the pattern: select curated warehouse columns and upsert them into operational SaaS APIs on a schedule or incrementally.

Important first principle: not every field should sync both ways. The warehouse is often system of record for derived scores. The CRM is often system of record for human notes and next steps. Without a field ownership matrix, retries and dual edits create ping-pong corruption.

Full problem statement

Design warehouse to SaaS operational sync for hundreds of thousands to millions of customer records:

  • Select curated columns for sync.
  • Upsert into Salesforce objects on a schedule or incrementally.
  • Respect API rate limits and backoff.
  • Resolve conflicts when CRM and warehouse both change fields.
  • Observe sync lag and per-record failures.
  • Keep upserts idempotent.
  • Secure secrets and least-privilege tokens.
  • Avoid blindly overwriting human-edited CRM fields.

What "good" looks like

You start with CSV pain. You draw warehouse mart to reverse ETL job to Bulk API. You define field ownership. You choose incremental cursors. You plan bulk chunking and backoff. You include DLQ and observability. You discuss catch-up after outages and PII minimization.

Clarifying questions

  • Which fields are system-of-record in CRM vs warehouse?
  • Sync every 5 minutes or hourly? What SLA does CS need?
  • Hard deletes or soft deletes in CRM?
  • Expected row volume and Salesforce API tier?
  • Who approves new synced fields (privacy)?

Scale prompts

  • 2M accounts × hourly full sync is wasteful; estimate changed rows per hour.
  • Bulk API chunk sizes vs REST per-row calls.
  • Catch-up backlog after a 3-hour API outage.
  • Quota burn math for naive loops.

Out of scope

  • Replacing the CRM.
  • Bidirectional sync of every column without ownership.
  • Syncing raw PII "just in case."
  • Rebuilding Salesforce automation as the analytics engine.
  • Two-way sync of all columns.
  • Shipping raw PII columns without review.

First principles expanded

Warehouse tables are optimized for analytical queries. CRM systems are optimized for human workflows and operational objects. Bridging them means:

1. Computing traits where computation belongs (warehouse). 2. Delivering traits where action belongs (CRM). 3. Respecting API quotas and human edits. 4. Observing lag like any pipeline SLA.

Reverse ETL is that bridge. It is not magic sync. It is a carefully owned integration with a contract.

Restated problem

"Sync customer health scores from Snowflake into Salesforce for CS and sales. Design for incremental updates, idempotent upserts, rate limits, conflict policy when humans edit fields, failure isolation, catch-up after outages, and least-privilege security. Scale is hundreds of thousands to millions of accounts."

What good looks like

  • Ownership matrix on the board early.
  • Bulk API + cursor, not per-row loops.
  • Lag metrics and DLQ.
  • Clear SoR per field.
  • Privacy minimization.

Clarifying questions expanded

  • Which CRM objects and external ids?
  • Freshness SLA (5 min vs hourly)?
  • Who can request new fields?
  • Soft delete behavior when a customer leaves the mart?
  • Existing iPaaS or reverse ETL vendor?

Estimation prompts

  • Changed rows/hour vs full table size.
  • API batch size and daily quota headroom.
  • Backlog drain time after 3-hour outage at max bulk throughput.
  • Warehouse compute cost of identifying deltas.