milestone 5/12 Quarantine bad rows & reconcile the batch

Separate invalid records and reconcile the whole batch.

CRM + ERP Integration · Follow extraction reconciliation, authoritative integration, date quarantine, quality gates, stable keys, replay-safe loads, and atomic promotion. Finish with a customer-grain capstone and field-level lineage.

approach

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.

worked-example.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;

data for this milestone

raw_dates2 columns · 8 rows

row_idinteger · key	raw_datetext
1	20250115
2	20250230
3	0
4	20240229
5	20251301

showing 5 of 8 rows