lesson 72 Warehouse & reporting projects · advanced

Silver: clean, map & deduplicate

Clean values, apply mappings and remove duplicate records.

the idea

Silver applies explicit, documented rules: trim keys and text, drop rows with no usable key, keep the latest version of each customer, and map inconsistent country codes to one spelling. Write the rules so a reviewer can see exactly why each row was kept, changed or excluded.

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

   See country spelling variants. Run this on Bronze. UK, GB, and full names show up as separate groups—Silver will map them to one country label.the pattern

   example.sql Run

   sql›

   SELECT UPPER(TRIM(country)) AS raw_country, COUNT(*) AS rows
   FROM bronze_customers
   GROUP BY raw_country
   ORDER BY rows DESC;

2. 2

   See duplicate versions of one customer. Customer 12347 appears several times with different updated_at values; Silver keeps only the latest row per trimmed key.

   step-2.sql Run

   sql›

   SELECT raw_id, name, country, updated_at
   FROM bronze_customers
   WHERE TRIM(raw_id) = '12347'
   ORDER BY updated_at;

common mistake

A new surrogate key is not deduplication: numbering every Bronze row gives one customer several keys.