阅读中文版本

Backfill a Warehouse Partition Safely

Backfill a single partition in place with an atomic swap so queries see either the old or the new data, never a partial mix.

INSERT INTO events_staging
SELECT * FROM recomputed_source
WHERE event_date = DATE '2026-10-01';

ALTER TABLE events EXCHANGE PARTITION (event_date = '2026-10-01')
WITH TABLE events_staging;

What this recipe does

Backfill one partition atomically without exposing partial results to readers.

A partition had wrong data for one day, and rewriting it in place would let concurrent queries read a half-populated state.

Prerequisites

• A partitioned warehouse table
• A way to write a staging partition
• Atomic partition swap or replace support

Procedure

1. Write the corrected data to a staging partition.

INSERT INTO events_staging
SELECT * FROM recomputed_source
WHERE event_date = DATE '2026-10-01';

Expected result: Corrected rows land in a staging partition, invisible to readers.

2. Swap or replace the partition atomically.

ALTER TABLE events EXCHANGE PARTITION (event_date = '2026-10-01')
WITH TABLE events_staging;

Expected result: Readers see either the old or the new partition, never a partial state.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows during the backfill no query observes a partial partition, and the corrected day matches the source of truth.

Troubleshooting

No atomic swap available — Write to a new table and swap references in a view, or run during a quiet window.

Downstream caches stale — Invalidate caches or materialised views for that partition after the swap.

Limitations

• Atomic exchange is engine-specific.
• Does not validate the recomputed data automatically.
• Concurrent readers must tolerate the swap point.