lesson 81 Warehouse & reporting projects · advanced

Quarantine bad rows & reconcile the batch

Separate invalid records and reconcile the whole batch.

the idea

Filtering invalid rows is not enough: preserve a reject table with source identity, raw value, and reason. Accepted + rejected must equal source count with no overlap. This lab classifies only dates: missing, format, or calendar errors. Nothing edits the raw input.

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.

CREATE TEMP TABLE checked_dates AS
WITH shaped AS(
  SELECT
    row_id,
    raw_date,
    SUBSTR(raw_date,1,4)||'-'||SUBSTR(raw_date,5,2)||'-'||SUBSTR(raw_date,7,2) AS candidate
  FROM raw_dates
)
SELECT
  row_id,
  raw_date,
  CASE
    WHEN raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
      AND date(candidate,'+0 days')=candidate
      THEN candidate
    ELSE NULL
  END AS clean_date
FROM shaped;
CREATE TEMP TABLE date_rejects AS
SELECT
  row_id,
  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 'calendar'
  END AS reason
FROM checked_dates
WHERE clean_date IS NULL;
SELECT row_id,raw_date,reason
FROM date_rejects;

Watch for: One row can violate several rules in a general pipeline. A primary reason must be deterministic; multiple reason records require distinct row counting.

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 raw_dates;

2. 2

   Execute the transformation. Filtering invalid rows is not enough: preserve a reject table with source identity, raw value, and reason. Accepted + rejected must equal source count with no overlap. This lab classifies only dates: missing, format, or calendar errors. Nothing edits the raw input.

   step-2.sql Run

   sql›

   CREATE TEMP TABLE checked_dates AS
   WITH shaped AS(
     SELECT
       row_id,
       raw_date,
       SUBSTR(raw_date,1,4)||'-'||SUBSTR(raw_date,5,2)||'-'||SUBSTR(raw_date,7,2) AS candidate
     FROM raw_dates
   )
   SELECT
     row_id,
     raw_date,
     CASE
       WHEN raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
         AND date(candidate,'+0 days')=candidate
         THEN candidate
       ELSE NULL
     END AS clean_date
   FROM shaped;
   CREATE TEMP TABLE date_rejects AS
   SELECT
     row_id,
     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 'calendar'
     END AS reason
   FROM checked_dates
   WHERE clean_date IS NULL;
   SELECT row_id,raw_date,reason
   FROM date_rejects;

3. 3

   Test another question. One row can violate several rules in a general pipeline. A primary reason must be deterministic; multiple reason records require distinct row counting.

   step-3.sql Run

   sql›

   CREATE TEMP TABLE checked_dates AS
   WITH shaped AS(
     SELECT
       row_id,
       raw_date,
       SUBSTR(raw_date,1,4)||'-'||SUBSTR(raw_date,5,2)||'-'||SUBSTR(raw_date,7,2) AS candidate
     FROM raw_dates
   )
   SELECT
     row_id,
     raw_date,
     CASE
       WHEN raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
         AND date(candidate,'+0 days')=candidate
         THEN candidate
       ELSE NULL
     END AS clean_date
   FROM shaped;
   SELECT
     (
       SELECT COUNT(*)
       FROM raw_dates
     ) AS source_rows,
     SUM(clean_date IS NOT NULL) AS accepted_rows,
     SUM(clean_date IS NULL) AS rejected_rows,
     COUNT(*)=(
       SELECT COUNT(*)
       FROM raw_dates
     ) AS reconciled
   FROM checked_dates