lesson 86 Warehouse & reporting projects · advanced

Stage, validate & atomically promote a refresh

Validate staged data before replacing a published result.

the idea

Build and validate the candidate before replacing published data. A transaction makes related writes commit together; rollback retains the old snapshot. In this controlled lab the candidate is known to pass. Production needs explicit blocking gates, failure handling, and workload-specific isolation. SQL Server TRUNCATE syntax is not executed here.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

BEGIN;
DELETE FROM published_totals;
INSERT INTO published_totals
SELECT *
FROM refresh_stage;
ROLLBACK;
SELECT customer_id,total_sales
FROM published_totals;

Watch for: A TRY/CATCH that logs an error is not automatically atomic. This lab models recovery with an explicit rollback, not an actual crashing process.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.

   step-1.sql Run

   sql›

   SELECT *
   FROM published_totals;

2. 2

   Execute the transformation. Build and validate the candidate before replacing published data. A transaction makes related writes commit together; rollback retains the old snapshot. In this controlled lab the candidate is known to pass. Production needs explicit blocking gates, failure handling, and workload-specific isolation. SQL Server TRUNCATE syntax is not executed here.

   step-2.sql Run

   sql›

   BEGIN;
   DELETE FROM published_totals;
   INSERT INTO published_totals
   SELECT *
   FROM refresh_stage;
   ROLLBACK;
   SELECT customer_id,total_sales
   FROM published_totals;

3. 3

   Test another question. A TRY/CATCH that logs an error is not automatically atomic. This lab models recovery with an explicit rollback, not an actual crashing process.

   step-3.sql Run

   sql›

   BEGIN;
   DELETE FROM published_totals;
   INSERT INTO published_totals
   SELECT *
   FROM refresh_stage;
   COMMIT;
   SELECT customer_id,total_sales
   FROM published_totals