lesson 71 Warehouse & reporting projects · advanced

Bronze: inspect raw data before cleaning

Inspect incoming records before making cleaning decisions.

the idea

Bronze keeps source records exactly as received so every later step can be traced back. Before writing cleaning rules, profile the raw data: missing and untrimmed keys, repeated versions of the same entity, text that should be numbers, and dates that do not parse. Profiling describes problems; it does not fix or delete anything.

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

   Find broken customer keys. Run this. Some raw_id values are missing or have extra spaces—those would fail later joins if you did not clean them in Silver.the pattern

   example.sql Run

   sql›

   SELECT raw_id, name, country, updated_at
   FROM bronze_customers
   WHERE raw_id IS NULL OR TRIM(raw_id) = '' OR raw_id <> TRIM(raw_id)
   LIMIT 20;

2. 2

   Find quantities that are not plain numbers. Bronze keeps source text as-is. You will see empty quantities, signs, and unit suffixes that Gold must reject or parse.

   step-2.sql Run

   sql›

   SELECT row_id, quantity
   FROM bronze_sales
   WHERE TRIM(quantity) = '' OR TRIM(quantity) GLOB '*[^0-9]*'
   LIMIT 20;

3. 3

   Profile country before cleaning. One summary row counts missing countries and how many distinct spellings appear—profiling describes problems without changing Bronze.

   step-3.sql Run

   sql›

   SELECT
     COUNT(*) AS rows,
     SUM(country IS NULL OR TRIM(country) = '') AS missing_country,
     COUNT(DISTINCT country) AS raw_spellings,
     COUNT(DISTINCT UPPER(TRIM(country))) AS normalized_spellings
   FROM bronze_customers;

common mistake

date(order_date) IS NULL catches impossible and badly formatted dates, but a NULL order_date is also NULL; decide whether that is a separate problem.