lesson 77 Warehouse & reporting projects · advanced

Reconcile extraction & Bronze ingestion

Reconcile source counts with ingested records.

the idea

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.

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.

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

Watch for: Counts cannot detect shifted CSV columns. BULK INSERT and file paths are T-SQL reference concepts; these are preloaded synthetic extracts.

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

2. 2

   Execute the transformation. 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.

   step-2.sql Run

   sql›

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

3. 3

   Test another question. Counts cannot detect shifted CSV columns. BULK INSERT and file paths are T-SQL reference concepts; these are preloaded synthetic extracts.

   step-3.sql Run

   sql›

   SELECT source_row,customer_id
   FROM crm_people
   WHERE customer_id IS NULL OR TRIM(customer_id)=''