Prompt and context
This is a data-platform system-design question. Reverse ETL delivers trusted warehouse models to CRM, marketing, or product tools. Hightouch’s documentation describes the flow as source → model → sync → destination, while the Census guide frames warehouse-to-business-platform delivery as operational analytics. The interview tests batch and incremental processing, destination API limits, and data governance together.
What the interviewer evaluates
- Can you separate model snapshots, change detection, scheduling, queues, and destination adapters?
- Can you protect a destination with idempotency keys and versions when source delivery is at least once?
- Can you turn deletion, consent withdrawal, schema drift, and tenant isolation into explicit contracts?
- Can you prove reliability with freshness, success, backlog, and reconciliation metrics instead of only drawing a data-flow diagram?
Clarifying questions
- Does the 10-minute SLO apply only to high-priority cohorts or to every record?
- Do destinations support batch upsert, deletion, idempotency keys, and server-side cursors?
- Does the model expose a stable key, update time, and deletion tombstone? How long are snapshots retained?
- Are tenant quotas independent, and can one large tenant consume all global throughput?
- How quickly must consent withdrawal take effect, and must new syncs be blocked while deletion is failing?
“I would split the system into a versioned model, change detector, per-tenant queues, destination adapters, and reconciliation jobs. Each record carries tenantid, a stable business key, model version, rowversion, and deletion state. A high-water mark or CDC creates at-least-once tasks. The adapter batches upserts under destination limits and uses tenant, destination, recordid, and rowversion as an idempotency key. Retries cannot lower a version; deletion and consent withdrawal write an uncircumventable fence. I would monitor freshness lag, backlog, throttling, failure classes, reconciliation gaps, and destination deletion latency.”
Step-by-step deep dive
Step 1: Define the source model and versions
Treat the warehouse model as the sync input; do not let workers join several operational databases ad hoc. Emit stable record_id, tenant_id, business fields, row_version, updated_at, consent_state, and deleted_at. Each model run gets a model_run_id. When a record is deleted, emit a tombstone instead of silently omitting it, so the worker can distinguish “not scanned yet” from “explicitly delete downstream.”
Step 2: Detect changes and schedule work
Prefer a model update column or CDC high-water mark. Persist the checkpoint and use an overlap window so equal timestamps do not cause missed rows. Write an immutable change batch, then let the scheduler split it by tenant priority. Calculate the allowed delay for the 10-minute SLO from queue age; lower-priority work yields capacity when needed, but it cannot bypass the consent-withdrawal queue.
Step 3: Make upserts idempotent
At-least-once delivery means “the worker crashes after a successful send” must be safe to replay. When the destination supports idempotency, use tenant_id + destination + record_id + row_version; accept only a version no lower than the current one. Without destination idempotency, retain request fingerprints and responses, cap concurrency, and reconcile by reading the destination. Do not claim cross-system transactions provide exactly-once. Classify retries by retryable error, exponential backoff, and maximum attempts.
Step 4: Isolate throttling and overload
Maintain a token bucket or destination-reported quota per tenant, plus a global concurrency ceiling. Tenant queues, fair scheduling, and a dead-letter queue prevent one large tenant from starving others. Retry 429, 5xx, and network timeouts with delay; route schema or authorization 4xx errors to manual handling. Alert as backlog approaches the SLO and allow low-priority backfills to pause.
Step 5: Handle deletion and consent withdrawal
Write each withdrawal to an independent deletion fence with tenant, record_id, and event version. A worker checks the fence immediately before an upsert; a withdrawn record may only send a delete until governance explicitly clears it. Retain destination deletion receipts and timestamps. Reconciliation must search for forbidden records that still exist downstream; request success alone is insufficient.
Step 6: Manage schema drift and rollback
Version the model schema and validate field mappings before deployment. Gray-release additive optional fields; block incompatible type or removal changes with a report instead of breaking every tenant. Keep mapping_version on each adapter task, retry failed batches with their old mapping, and roll back to a validated version rather than overwriting a partial migration with the newest success.
Step 7: Observe and reconcile
Record source_run, task state, attempts, last successful version, API latency, throttles, queue age, and deletion latency by tenant and destination. Core metrics are high-priority freshness-lag p95, success rate, dead letters, schema-error rate, source-versus-destination count delta, and sampled field-hash delta. Run full reconciliation daily or after releases, auto-repair safe replays, and route irreconcilable gaps to operations.
Trade-offs and boundaries
Snapshot, incremental, or CDC
Snapshots are simple but rescan data. Timestamp increments are cheaper but depend on a stable clock and update column. CDC represents deletes, but the source or modeling layer must retain change facts. Explain that the choice depends on model refresh, deletion semantics, and destination capacity; keep periodic full reconciliation as a guard against missed rows.
Queue placement and consistency
Tenant partitions improve isolation and ordering. A global queue uses capacity efficiently but needs fair scheduling. You can guarantee monotonic visibility for one record version, but not an atomic commit across the warehouse and destination. Version-conditional writes, replay, and reconciliation provide explainable eventual consistency.
Backfill versus live updates
Give backfills an independent low-priority budget, pausable cursor, and throttling awareness; send live updates to the high-priority queue. If both compete for one record, the higher row_version wins, and a destination conditional write should reject an older version.
Model answer
“I would treat the warehouse model as a versioned source, create immutable change batches with a high-water mark or CDC, and queue them by tenant and destination. Records carry stable keys, row_version, model version, and mapping version. Upserts use destination idempotency or request fingerprints; exponential-backoff retries cannot overwrite a newer version. Per-tenant token buckets and a global concurrency ceiling handle throttling. Deletion and consent withdrawal write a fence checked immediately before sending, and deletion receipts are retained. I would validate freshness lag, queue age, dead letters, schema errors, field-hash reconciliation, and forbidden-record residue. The contract is at-least-once delivery with eventual consistency, not cross-system exactly-once.”
Common mistakes
- Calling Reverse ETL real-time database replication while ignoring model refresh and field mapping.
- Saying “the queue guarantees exactly-once” without handling duplicate destination requests.
- Using one global rate limit instead of tenant isolation, allowing a large tenant to consume all capacity.
- Treating disappearance from the current model as deletion without tombstones, consent fences, and downstream reconciliation.
- Broadcasting schema changes without recording which mapping version each batch used.
Follow-up questions
How do you prove the 10-minute freshness SLO?
Measure from model commit time or change-event time until the destination confirms readability, then report p95 and timeout rate for high-priority records. Worker start time and averages are insufficient.
What if a destination only supports replacing a full collection?
Build a versioned snapshot per tenant with model_run_id, upload a temporary collection, validate count and hash, and atomically switch versions. Deletion and withdrawal still need a separate fence; the next full load cannot be assumed to remove prohibited data safely.
What if the destination succeeded but the receipt was lost?
Replay the same idempotent request or reconcile using the request fingerprint and a destination read. If neither an idempotency key nor a read exists, put the uncertain result in manual review instead of marking it successful without evidence.
When should this become a dedicated sync platform?
Split into a durable task service and adapter layer when destination count, tenant quotas, mapping versions, governance fences, and reconciliation exceed one DAG’s maintainability. A small single-destination setup can start with an orchestrator and idempotent script, but it still needs deletion and retry contracts.