lesson 86 Warehouse & reporting projects · advanced

Stage, validate & atomically promote a refresh

Validate staged data before replacing a published result.

the idea

Refresh a published table in a transaction: BEGIN, delete the old rows, insert the staged rows, then COMMIT. Readers see either the old snapshot or the new one, never a half-empty table. If anything fails before COMMIT, ROLLBACK leaves the old snapshot intact.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Compare published and staged totals. published_totals is what readers see today; refresh_stage holds the next snapshot before you promote it in a transaction.the pattern

   example.sql Run

   sql›

   SELECT
     p.customer_id,
     p.total_sales AS published,
     s.total_sales AS staged
   FROM published_totals p
   LEFT JOIN refresh_stage s ON s.customer_id = p.customer_id;

2. 2

   Validate the stage before promotion. Check row count, null keys, and negative totals on refresh_stage so you do not commit a bad refresh.

   step-2.sql Run

   sql›

   SELECT
     COUNT(*) AS rows,
     SUM(customer_id IS NULL) AS missing_keys,
     SUM(total_sales < 0) AS negative_totals
   FROM refresh_stage;

3. 3

   Confirm rollback leaves data intact. DELETE inside BEGIN is undone by ROLLBACK—staged_rows should match the count before the failed experiment.

   step-3.sql Run

   sql›

   BEGIN;
   DELETE FROM refresh_stage;
   ROLLBACK;
   SELECT COUNT(*) AS staged_rows
   FROM refresh_stage;

common mistake

Deleting and inserting without a transaction leaves an empty or partial table if the insert fails.