Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. System Design

System design interview

Reverse ETL Architecture

MediumPro55 min read

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

system-designreverse-etlactivationintegration

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."

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.

Out of scope

  • Rebuilding Salesforce automation as the analytics engine.
  • Two-way sync of all columns.
  • Shipping raw PII columns without review.