Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Atomic publish and table swaps

Pipelines & scenarios · Core Concepts Not Yet Covered

Atomic publish and table swaps

Mediumpipelines-57
atomic-publishtable-swapconsistencyloading

Question

How do you make sure consumers never read a half-loaded table?

Solution

Never modify a live table in several visible steps. Build the new version somewhere else, then switch consumers to it in one atomic operation. Then they see the old data or the new data, never half of each.

The bad pattern

TRUNCATE TABLE sales_summary;           -- now the table is empty
INSERT INTO sales_summary SELECT ...;   -- takes 10 minutes

For ten minutes, any dashboard reading sales_summary sees an empty table or a partial one. If the insert fails, the table stays empty.

Pattern 1: build aside and swap

Write the new data to a staging table, check it, then exchange the two names in one statement:

CREATE OR REPLACE TABLE sales_summary_new AS SELECT ...;
-- validate row counts here
ALTER TABLE sales_summary SWAP WITH sales_summary_new;   -- Snowflake

Other engines have similar tools: CREATE OR REPLACE TABLE (which swaps in one operation in many cloud warehouses), RENAME inside a transaction (Postgres supports transactional DDL), or repointing a view.

Pattern 2: a view in front

Consumers read a view called sales_summary, which points to a versioned table such as sales_summary_v20250301. Build the new version table, validate it, then repoint the view with CREATE OR REPLACE VIEW. The switch is one quick metadata change, and rolling back is just pointing the view at the old version.

Pattern 3: table formats commit atomically

With Delta, Iceberg or Hudi, a write becomes visible only when its commit completes. Readers see a consistent snapshot, either before or after. An INSERT OVERWRITE or a MERGE on these tables is atomic, which is a large reason to use them.

Pattern 4: partition overwrite

For partitioned data, write a day's partition to a temporary location, then replace only that partition in one step. Only that day is affected.

Checks before publishing

Validate row counts, key uniqueness and basic totals on the new data before the swap, and stop if they fail. The old version continues to serve, so a failed run leaves consumers with yesterday's good data, not a broken table.

Keep it simple in the answer: build, check, swap, and avoid truncate-then-insert on anything people read.

PreviousNext