lesson 81 Warehouse & reporting projects · advanced

Quarantine bad rows & reconcile the batch

Separate invalid records and reconcile the whole batch.

the idea

Filtering bad rows is not enough. Keep a reject table with the source key, the raw value and a reason, and prove the batch reconciles: accepted plus rejected equals the source count, with no row in both. Classify each reject by one deterministic primary reason.

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

   Count source rows to reconcile. source_rows is the total from raw_dates—accepted plus rejected rows in your batch must equal this number.the pattern

   example.sql Run

   sql›

   SELECT COUNT(*) AS source_rows
   FROM raw_dates;

2. 2

   Classify rejects in fixed order. CASE checks missing first, then format, then calendar so each bad row gets exactly one primary reason.

   step-2.sql Run

   sql›

   SELECT
     raw_date,
     CASE
       WHEN raw_date IS NULL THEN 'missing'
       WHEN raw_date NOT GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
         THEN 'format'
       ELSE 'eight digits'
     END AS first_check
   FROM raw_dates
   WHERE raw_date IS NULL OR length(raw_date) <> 8
   LIMIT 10;

3. 3

   Verify calendar dates survive normalization. For well-formed eight-digit strings, normalized should match candidate—mismatch means the calendar is invalid.

   step-3.sql Run

   sql›

   SELECT
     raw_date,
     substr(raw_date, 1, 4) || '-' || substr(raw_date, 5, 2) || '-' || substr(raw_date, 7, 2) AS candidate,
     date(substr(raw_date, 1, 4) || '-' || substr(raw_date, 5, 2) || '-' || substr(raw_date, 7, 2), '+0 days') AS normalized
   FROM raw_dates
   WHERE raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
   LIMIT 10;

common mistake

If a row can match two reasons, CASE must check them in a fixed order so each row gets exactly one.