Make It Reliable: Intermediate

The tolerance belongs next to the check, in writing, with every reason rows leave on purpose. This transform removes 2 kinds: the duplicate versions that deduplication collapses on the way into silver, which section 2 builds, and internal test orders. Counting distinct order_id values in bronze takes the duplicates out of the arithmetic, so on a 20 million row day dropped_rows of 1,200 test orders is healthy and the threshold reads 0.5% rather than 0. A tolerance nobody wrote down becomes a 3 a.m. argument about whether 1,200 is a bug.

Picture the orchestrator retrying a task at 02:14. The first attempt wrote 6 million of the day's 20 million rows into silver.orders and then lost its connection. The retry starts from the beginning, reads all 20 million rows from bronze, and appends them. The table now holds 26 million rows for 20 October, 6 million of them twice. Nothing errored on the second attempt, so nothing paged. A pipeline is idempotent when running it 5 times leaves the table in the same state as running it once, and that property is what makes retry safe to turn on.

Two details decide whether this holds. First, the filter must use the run's logical date, which the orchestrator passes in, rather than CURRENT_DATE. A retry at 00:30 the next morning with CURRENT_DATE would delete and rewrite the wrong day. Second, there is a window between the DELETE and the INSERT where a reader sees an empty partition. If consumers query during the load window, write into a staging table and swap it in as the final step, which section 4 builds on.

Partition overwrite struggles when an update arrives for a row that lives in a partition from 3 weeks ago. Rewriting every partition in that range to apply 200,000 changed rows is wasteful, and change data capture feeds produce exactly that shape of input. MERGE matches incoming rows to existing rows on a key, updates the ones that exist, and inserts the ones that do not. A replay matches every row the first run wrote, and the updated_at guard below skips them, so they stay unchanged. The source has to hold one row per key first: if staging.orders_changes carries 2 versions of one order_id, which change feeds deliver routinely, the MERGE fails on the double match, so run the ROW_NUMBER deduplication from section 2 before merging.

The updated_at guard matters for replays out of order. If the feed redelivers Monday's change after Tuesday's already applied, the guard keeps Tuesday's values. Without it, MERGE is idempotent for a single batch but not for the sequence of batches.

SELECT DISTINCT removes rows that are identical in every column. It does nothing for 3 rows that share an order_id but carry different updated_at values, because each row is distinct. That shape, the same key with several versions, is the common one, and it needs a rule that picks the version to keep.

Row 2 and row 3 share the same updated_at, so the rule needs a second sort key. ingested_at breaks the tie: the latest arrival wins. Without a tie-breaker the engine picks whichever row it reads first, and the pipeline returns different answers on different runs.

Order 1001 comes out once with order_total 54.00 from the row ingested at 11:50. Say why ORDER BY has 2 keys when you write it, because an interviewer who sees one key will ask what happens on a tie, and a candidate who already answered it has shown they ran into it before.

The CASE in the ORDER BY turns a business rule into a sort key, and the ELSE 3 catches a source nobody listed so it never silently outranks billing. The rule here is whole-row: billing wins every column. The field-level version, where billing wins email but the CRM wins phone, uses a separate priority per column and COALESCE across sources ordered by that priority. Mention that it exists, and say you would start whole-row because consumers can reason about one winner.

ROW_NUMBER partitioned by key forces a shuffle of every row to the worker holding its key, then a sort within each key. On a 500 million row daily feed that shuffle is the most expensive step in the load, so run it once, when bronze becomes silver, and leave bronze holding every version so an audit can see what arrived. Deduplicating again in every downstream model repeats the shuffle and invites 2 models to pick different winners for the same key.

Define the SLA in consumer terms first. The orders table promises the previous day's partition, published by 07:00 each morning, so on 21 October consumers expect the partition for 20 October to be there. The monitor asks exactly that at the deadline: is the newest loaded dt the expected logical date? Store a loaded_at column at write time as well, so the alert can report when the last load landed without scanning event timestamps that may legitimately lag the load.

The WHERE on dt is the cost control. Without it the monitor scans 3 years of partitions on every run to find a maximum that only ever lives in the newest one. With it the query reads only the newest partitions and finishes in seconds, so it can repeat after the deadline until the partition lands. Comparing dates is also what keeps the page meaningful: a threshold on minutes since MAX(loaded_at) would sit above 60 for most of every day on a once-a-day load and page around the clock.

At 07:10 the orders load finished and published 20 October. The layer 3 sum check had been switched to warn rather than block while a new currency feed settled in, so its failure went to a channel and the publish went ahead. At 07:40 the on-call read that warning: order_total summed 100 times higher than its trailing median. The row count was normal, which is why the volume monitor from section 3 stayed quiet. A currency conversion change had multiplied minor units where it should have divided. Consumers refresh dashboards at 08:00. You have 20 minutes, and the question is whether the design you built gives you a rollback that fits in them.

A rollback on serving.orders leaves every table built from it holding the bad numbers until they re-run. The orchestrator knows the dependency graph, so the rollback step marks downstream tasks for the day as failed and re-triggers them after the fix, in dependency order. The message to consumers goes out before the fix, with 3 facts: which table, which dates, and when the corrected data will be live. A consumer who hears at 07:50 that Tuesday's orders are wrong and will be corrected by 09:00 waits; a consumer who finds out from their own dashboard stops trusting the table.