milestone 4/12 Validate calendar dates before converting

Validate real calendar dates before converting text.

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

Eight digits do not prove a valid date. Shape YYYYMMDD to YYYY-MM-DD, require exactly eight digits, and compare the candidate to SQLite date(candidate,+0 days) so normalization exposes impossible dates. Invalid or missing values remain NULL; keep the source row. SQL Server uses TRY_CONVERT with an explicit style.

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

data for this milestone