A bug in a revenue calculation has been in production for 3 years. Design how you reprocess the history: fixed numbers, no downtime for dashboards, and no risk of making things worse.

Fix the code, then rebuild the affected tables from the raw layer in date-range chunks into a separate copy, compare the old and new numbers, and swap each range in atomically. Every chunk must be idempotent, so a failed or repeated chunk is harmless, and you must be able to roll back. Most of the work is in planning the scope and the comparison, not the compute.

1. Scope it

• Which tables are wrong? Follow lineage (Unity Catalog records it down to the column) from the buggy transformation to every downstream table, job and dashboard, and ask about exports it cannot see.
• Is the raw input still there? Reprocessing needs bronze for the whole 3 years. If part of it is gone, decide what to do about that period before you start.
• Who consumes the numbers? Finance may need to approve changed historical figures; some may have been reported externally.

2. Build next to production, not on top of it

Write the fixed pipeline to new tables (for example gold_v2.revenue_daily). A shallow clone of the current table is a cheap starting point for testing; the backfill itself writes fresh data.

3. Run in chunks

INSERT INTO gold_v2.revenue_daily
REPLACE WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'
SELECT ... FROM silver.orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

• One month at a time: small enough to rerun cheaply, large enough to finish in reasonable time.
• Idempotent: REPLACE WHERE replaces exactly that month atomically, and fails if the query produces rows outside the range, which catches filter bugs. Rerunning a month gives the same result.
• Track progress in a control table (range, status, row counts, run ID), so the backfill can stop and resume.
• Throttle so the backfill does not starve daily jobs of compute.

4. Compare before switching

For every month: row counts, totals by key dimensions, and the difference between old and new. Expect differences only where the bug applied; investigate any others. Share the summary with the people who own the numbers.

5. Switch with a way back

• Point the dashboards at the new table (or swap with a view), in one change.
• Keep the old table, and rely on Delta history, until the new numbers have been accepted.
• New daily runs write with the fixed code from the moment of the switch, so there is no gap.

6. Prevent the next one

Add a test that would have caught the bug, and a reconciliation of revenue against the finance system, so a calculation drift shows up in days, not years.

What interviewers want: scoping by lineage, rebuilding from raw data, building beside production, chunked idempotent overwrites, a control table, numeric comparison and sign-off, an atomic switch with rollback, and a test that stops a repeat.