How does medallion layering let you fix six months of silver tables corrupted by a bad transform?

## The property that makes this recoverable Medallion layering buys you one structural guarantee: silver and gold are **functions of retained raw**, not independently edited stores of truth. That turns a data-corruption incident from "write a careful UPDATE script and hope" into "fix the code, re-run the function over the affected range". Everything below is about making that recomputation safe, bounded and repeatable. ## Step one: fix and prove the transform Before touching production tables, correct the logic and validate it on a bounded sample from the affected window — a single day, a single source file — comparing old output, new output and an independently computed expected value. A backfill that spreads a second bug across six months is worse than the first bug, because now the wrong values look freshly produced. ## Step two: scope the blast radius Establish precisely which rows are wrong: which date range, which sources, which columns. Two things depend on this. First, the cost and duration of the rebuild. Second, honest communication — "EMEA revenue for January to June was overstated in these two tables" is a statement people can act on; "some numbers were wrong" is not. Query the affected window rather than assuming the deploy date bounds it; bad logic often predates the ticket that noticed it. ## Step three: rebuild the window idempotently The rebuild must be safely re-runnable, because it will be interrupted at least once. The portable shape is delete-then-insert scoped to the window, in one transaction where the platform allows it: ```sql DELETE FROM silver_orders WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2026-07-01'; INSERT INTO silver_orders (order_id, order_date, currency, amount_usd) SELECT b.order_id, b.order_date, b.currency, b.amount * r.rate FROM bronze_orders_typed b JOIN fx_rate_daily r ON r.currency = b.currency AND r.rate_date = b.order_date WHERE b.order_date >= DATE '2026-01-01' AND b.order_date < DATE '2026-07-01'; ``` Running it twice yields the same table. For very large windows, iterate a partition or month at a time so a failure costs one chunk rather than the whole job, and so you can monitor drift as it goes. ## Step four: determinism, the requirement people discover too late The rebuild reproduces the correct answer only if the transform is a pure function of retained raw plus explicit parameters. The recurring violations: - **Reading current state.** Joining to a reference table that holds only *today's* values — an exchange rate table with one row per currency, a product table without history — means rebuilding January with July's rates. The fix is effective-dated reference data joined on the event date; the general problem of dimension history belongs to its own subject, but this is where it bites a backfill. - **Non-deterministic expressions in output.** `current_timestamp`, random sampling, or ordering by a column with ties and taking the first row. Ties need an explicit tie-break. - **Generated surrogate keys.** If keys come from a sequence, a rebuild renumbers everything and downstream facts pointing at the old numbers break. Deterministic keys derived from the business key survive a rebuild; a sequence does not. - **Incremental state.** Transforms that read their own previous output, or a high-water mark table, are not functions of raw at all and cannot be replayed without resetting that state. ## Step five: rebuild downstream, in order Silver is rarely the last stop. Every gold table, aggregate and extract fed by the repaired table must be rebuilt too, in dependency order, and side effects that already left the platform — files sent to a partner, records pushed back into an operational system, a metric already reported externally — need their own remediation. Those cannot be recomputed away. ## Step six: swap rather than expose a half-rebuilt table If consumers read the table during business hours, build into a shadow table, run the tests against it, then swap it into place in a single operation. That converts a long window of visibly wrong or missing data into an instantaneous cutover, and it gives you an obvious rollback: the old table is still there until you drop it. ## Step seven: verify and communicate Re-run the grain-uniqueness and not-null tests, and reconcile the rebuilt window against something independent — source system totals, a control report, the previous values with the delta explained by the known bug. Then announce it. History that consumers had already read has changed; a dashboard screenshot from March no longer matches the warehouse, and somebody made a decision on the old number. Silence here is the part that damages trust, not the bug. ## When layering cannot save you If bronze retention was shorter than the corruption window, part of the range is unrecoverable — which is the argument for setting retention deliberately. If the source only ever delivered *snapshots* of current state, you can recompute what you had but you cannot reconstruct changes that were never landed. Table-format-level rollback to a previous table version is a separate mechanism with its own trade-offs; the layering property discussed here is that you can recompute the result regardless of whether such a rollback exists.