lesson 71 Warehouse & reporting projects · advanced

Bronze: inspect raw data before cleaning

Inspect incoming records before making cleaning decisions.

the idea

Bronze preserves source records for traceability. Profile missing keys, duplicates, dates, and type mismatches before writing cleaning rules. Do not silently erase rows without documenting why. These source rows are fictional, inspired by common CRM/catalog/sales issues.

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.

SELECT TRIM(raw_id) AS customer_id,COUNT(*) AS versions
FROM bronze_customers
WHERE raw_id IS NOT NULL
GROUP BY TRIM(raw_id)
HAVING COUNT(*)>1;

Watch for: A date parse check is not a complete calendar validation policy; production rules must also define format and acceptable ranges.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Preserve the raw input. Note duplicate C001, spaces, and the missing key.

   step-1.sql Run

   sql›

   SELECT *
   FROM bronze_customers;

2. 2

   Profile duplicates. Normalize keys for analysis without modifying the Bronze table.

   step-2.sql Run

   sql›

   SELECT TRIM(raw_id) AS customer_id,COUNT(*) AS versions
   FROM bronze_customers
   WHERE raw_id IS NOT NULL
   GROUP BY TRIM(raw_id)
   HAVING COUNT(*)>1

3. 3

   Inspect rejected dates. The invalid source row remains traceable.

   step-3.sql Run

   sql›

   SELECT row_id,order_date
   FROM bronze_sales
   WHERE date(order_date) IS NULL