How Do You Roll Back a Bad Load?

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.

Design the publish so rollback is a pointer flip

The pattern is write, audit, publish. The load writes the new partition to a location consumers cannot see, the quality layers from section 0 audit it there, and only a passing audit publishes it by switching what the consumer-facing name points to. Rollback becomes the same switch in reverse: point the name back at the previous version. The write and the audit can take 40 minutes; the publish and the rollback take seconds.

CREATE OR REPLACE VIEW serving.orders AS
SELECT
  order_id,
  customer_id,
  order_total,
  currency,
  updated_at,
  dt
FROM silver.orders_v42

Versioned tables with a view on top is one implementation; table formats that keep snapshots and let you restore a previous one offer the same flip natively. Either way the property to name is that the published name and the physical data are separate, and rollback changes the name's target. Retain previous versions long enough to cover a long weekend, so a bug found on Tuesday can still roll back Friday's load.

• 7 days

When the bad data already published

If the design wrote straight into the consumer table, there is no pointer to flip, and the options rank by how fast they restore a correct state.

The second row is where sections 1 and 4 meet. An idempotent pipeline turns a rollback into a re-run: fix the conversion, trigger the load for 20 October, and the partition overwrite deletes the 100 times too large rows and writes correct ones. Without idempotency, the same re-run appends 20 million correct rows next to 20 million wrong ones and the total is now 101 times too high. The third row deserves a word too: an empty partition is a better interim state than a wrong one, because a dashboard that shows no Tuesday prompts a question, while a dashboard that shows a 100 times Tuesday prompts a decision.

Test the rollback before you need it

A rollback nobody has run is a hypothesis. Once a quarter, pick a day, flip the view to the previous version in a non-production copy, time it, and confirm the dashboards read the old numbers. The timing is the figure you quote in the interview, and the exercise surfaces the version that was quietly deleted by a cleanup job 3 months ago.

Downstream, and saying so

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.

Do

• Separate the published name from the physical data so publish and rollback are pointer flips
• Audit in staging with the same checks that would block the publish
• Retain previous versions long enough to cover a long weekend
• Re-trigger downstream tasks in dependency order after the fix

Avoid

• Write a load straight into the table consumers read
• Patch rows by hand at 07:45 under a deadline
• Roll back the one table and forget the tables built on it
• Let consumers discover the problem from their dashboards

About this lesson

This section is part of the Make It Reliable: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.