No Updated_At, No Deletes, Four Hours to Load
Fintrail, a payments company, ingests a SaaS CRM (1.8M account and contact records plus roughly 40M activity records) into its cloud warehouse nightly. The CRM REST API allows 200 requests per minute and exposes no reliable updated_at for contacts; hard deletes are not exposed by the API at all. The current job performs a full extract, takes 5h40m, and fails roughly twice a week, and the finance reconciliation that depends on it must be ready by 07:00. Source edits can arrive up to nine days late because regional sales teams work offline. Last month a customer deleted in the CRM continued to receive invoices from a warehouse-driven billing feed for 11 days, and nobody noticed until the customer called. The team has one engineer on rotation and no vendor support contract.
1. Design an ingestion approach for this source. Address the missing updated_at, the invisible deletes, and the window.
Read the worked answer
Treat the absence of updated_at as a design constraint, not an inconvenience to work around with a hopeful watermark. Three mechanisms layered:
Change detection by hash. Extract candidate records (all contacts if necessary, but see parallelism below), compute a hash over business columns, and compare to the stored hash. Only changed rows are merged. This is O(n) in extraction but O(changes) in writes, and it is correct without a timestamp.
Delete detection by key-set comparison. A nightly extract of natural keys only (ids, not full records) is cheap even at 1.8M rows; diff it against the curated key set and soft-delete missing keys with deleted_at. If the CRM offers a recycle bin, bulk export, or deleted-records endpoint, use it as the primary and the key-set diff as backstop. Never rely on a single delete mechanism when the incident cost is an 11-day billing error.
Landing and merging. Write immutable raw extracts with run_id and ingest_ts, deduplicate to the latest record per natural key in staging, then MERGE on natural key with the hash comparison. MERGE makes reruns idempotent; append-only loads will duplicate on retry.
For the window: partition extraction by id range or modified region across parallel workers while respecting 200 requests per minute with a shared token bucket, and back off on 429s with jitter. Activities (40M) usually do have creation timestamps - extract incrementally by day. Keep a weekly full reconciliation as a safety net, and move to a pre-07:00 freshness SLO with an alert on lag rather than a best-effort finish time.
2. How should nine-day-late edits be handled so that finance is not silently restated after close?
Read the worked answer
Separate the technical problem from the accounting one. Technically, every row should carry both the source's effective date (when the change happened in the business) and the ingestion date (when you learned about it). Late data is then attributable rather than invisible.
Define a revision window - 14 days is reasonable given the observed nine-day maximum - and align it with finance's close calendar. Within the window, corrections flow through normally. After close, they are posted as explicit adjustments in the next period, not written backward into the closed number. This requires either point-in-time snapshots (monthly snapshot tables or bi-temporal history on the curated layer) so "what we reported" is reproducible, or a locked reporting table that the pipeline cannot mutate.
Instrument restatement magnitude: sum of value changes applied to periods older than the current one, as a percentage of the period total. Alert above a threshold such as 0.5%. Broadcast a daily watermark - latest source event time versus latest ingest time - so finance knows how complete the morning number is.
The failure mode to avoid is the seductive one: recalculating the whole history every night so the number is always "right". That makes every report irreproducible and turns a 9-day-late edit into an unexplained variance in a board pack.
3. The load fails about twice a week. What would you instrument and change first, and what does the invoice incident tell you about the pipeline boundary?
Read the worked answer
First classify the failures rather than retrying blindly. Instrument HTTP status codes (429 versus 5xx versus 4xx on specific records), duration by stage, records extracted versus records merged, and per-partition checkpoints. In practice, two failures a week on a 5h40m job with a rate-limited API is usually rate limiting plus timeouts, with an occasional poison record that fails validation every run.
Changes: checkpoint per partition so a failure resumes instead of restarting 5h40m; exponential backoff with jitter and a circuit breaker on sustained 429s; a dead-letter path for records that repeatedly fail, with an alert, so one malformed contact cannot block the batch; and reconciliation counts per run (source count versus merged count versus soft-deleted count) that must tie or the run is marked degraded.
The invoice incident is not an ETL bug - the pipeline did exactly what it was asked and never saw the delete. It is a boundary failure: the billing feed consumed a table with no validity contract. Fix it by requiring downstream consumers to filter on is_active and deleted_at, by adding an end-to-end reconciliation between CRM active accounts and warehouse active accounts with a P1 alert on divergence, and by agreeing how long a soft-deleted record may remain billable (zero, in this case). Pipeline monitoring alone would never have caught an 11-day gap in an upstream feed.