lesson 77 Warehouse & reporting projects · advanced

Reconcile extraction & Bronze ingestion

Reconcile source counts with ingested records.

the idea

Before cleaning, reconcile what landed with what the source said it sent. Compare a load manifest’s expected row counts with the rows that arrived, and list records that cannot be keyed. A difference is evidence to investigate with the source owner, not permission to delete data.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Read expected row counts. load_manifest records what CRM and ERP said they sent—reconciliation starts by comparing these expectations to what landed.the pattern

   example.sql Run

   sql›

   SELECT source_name, expected_rows
   FROM load_manifest;

2. 2

   Find CRM rows without a key. Run this. Missing or blank customer_id rows cannot join to a customer master until you fix or quarantine them.

   step-2.sql Run

   sql›

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

3. 3

   Compare row count to distinct keys. When crm_rows exceeds distinct_keys, the same customer id arrived more than once—you will dedupe in integration.

   step-3.sql Run

   sql›

   SELECT
     COUNT(*) AS crm_rows,
     COUNT(DISTINCT TRIM(customer_id)) AS distinct_keys
   FROM crm_people;

common mistake

Matching counts do not prove the load is right: shifted columns or swapped values keep the same number of rows.