milestone 1/12 Reconcile extraction & Bronze ingestion

Reconcile source counts with ingested records.

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

Bronze preserves raw values. Compare a supplied extraction manifest to landed counts before cleaning; a matching count alone cannot prove correct column alignment. A discrepancy is evidence to investigate, not permission to delete data.

WITH landed AS(
  SELECT 'CRM' AS source_name,COUNT(*) AS landed_rows
  FROM crm_people
  UNION ALL
  SELECT 'ERP',COUNT(*)
  FROM erp_people
)
SELECT
  m.source_name,
  m.expected_rows,
  l.landed_rows,
  l.landed_rows-m.expected_rows AS difference
FROM load_manifest m
JOIN landed l USING(source_name);

data for this milestone