Publishing Data Atomically

Scenario

Example company: the sales_summary table is rebuilt each morning by deleting it and inserting fresh rows in batches over 20 minutes. A finance analyst opens the dashboard at 07:10, during the rebuild. On another day, the rebuild crashes halfway.

What you see in production

• The 07:10 dashboard shows half of yesterday's sales; refreshing at 07:30 shows the full number.
• After the crash, the table stays half-empty until someone notices and reruns the job.
• Exports scheduled during the rebuild send partial data to a partner.

The naive code

DELETE FROM sales_summary;
INSERT INTO sales_summary SELECT ... ;   -- many batches over 20 minutes, readers see each step

Reproduce it

Readers query the table at different points of a rebuild that fails at 60%. The naive load writes directly into the live table; the fix builds a staging table, validates it and swaps it in one step.

old_rows = 1000
new_rows = 1040

def in_place(progress, failed_at=None):
    if failed_at is not None and progress > failed_at:
        return int(new_rows * failed_at)          # stuck at the failure point
    return int(new_rows * progress)              # readers see a growing partial table

def swap(progress, failed_at=None):
    building_done = progress >= 1.0 and failed_at is None
    return new_rows if building_done else old_rows   # live table changes only on a successful swap

for label, failed_at in [("successful run", None), ("run fails at 60%", 0.6)]:
    print(label)
    for progress in [0.0, 0.3, 0.6, 1.0]:
        print(f"  at {progress:>4.0%}: in-place readers see {in_place(progress, failed_at):>4} rows | swap readers see {swap(progress, failed_at):>4}")

successful run
  at   0%: in-place readers see    0 rows | swap readers see 1000
  at  30%: in-place readers see  312 rows | swap readers see 1000
  at  60%: in-place readers see  624 rows | swap readers see 1000
  at 100%: in-place readers see 1040 rows | swap readers see 1040
run fails at 60%
  at   0%: in-place readers see    0 rows | swap readers see 1000
  at  30%: in-place readers see  312 rows | swap readers see 1000
  at  60%: in-place readers see  624 rows | swap readers see 1000
  at 100%: in-place readers see  624 rows | swap readers see 1000

Root cause

Readers could see the table during the rebuild, and a failure left it in a half-built state. The load wasn't atomic from the reader's point of view.

The fix

Build, validate, then publish atomically:

1. Write the new data to a staging table (or a new partition, or new files).
2. Run data tests on the staging table.
3. Publish in one atomic step: a transaction that swaps or renames tables (ALTER TABLE ... RENAME, CREATE OR REPLACE TABLE ... AS, Snowflake's ALTER TABLE ... SWAP WITH), a partition exchange, or a table-format commit (Iceberg and Delta commit a new snapshot atomically).
4. If anything fails, the live table is untouched; alert and retry.

Many tools do this for you: dbt builds table models into a temporary relation and swaps them in, and lakehouse table formats make every write an atomic snapshot.

For views over several tables that must change together, publish a new version of all of them and switch a pointer (a view or a "current version" setting) in one step.

Trade-offs

• Staging needs extra storage during the build.
• Swapping whole tables is simplest for small or medium tables; large tables use partition-level overwrite instead.
• Dependent views or grants may need recreating after a rename on some databases; test the swap.

Detect it in production

• Watch for dashboards queried during load windows showing unusual dips.
• Alert when a run fails between "build" and "publish", and verify the live table still matches the last good version.

Key points

• Readers should see old data or new data, never a mix.
• Build in staging, validate, then swap atomically.
• Failed runs must leave the live table untouched.