lesson 72 Warehouse & reporting projects · advanced

Silver: clean, map & deduplicate

Clean values, apply mappings and remove duplicate records.

the idea

Silver applies explicit quality rules. Normalize keys and text, keep the latest customer version, map country codes, and exclude missing keys. With equal version timestamps production logic needs an additional reliable tie-breaker; this sample has none.

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.

CREATE TEMP TABLE silver_customers AS
WITH ranked AS(
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY TRIM(raw_id) ORDER BY updated_at DESC) AS rn
  FROM bronze_customers
  WHERE raw_id IS NOT NULL AND TRIM(raw_id)<>''
)
SELECT
  TRIM(raw_id) AS customer_id,
  TRIM(name) AS name,
  CASE UPPER(TRIM(country))
    WHEN 'US' THEN 'United States'
    WHEN 'USA' THEN 'United States'
    WHEN 'UK' THEN 'United Kingdom'
    WHEN 'GB' THEN 'United Kingdom'
    WHEN 'IND' THEN 'India'
    WHEN 'CA' THEN 'Canada'
    ELSE UPPER(TRIM(country))
  END AS country
FROM ranked
WHERE rn=1;
SELECT customer_id,name,country
FROM silver_customers;

Watch for: Keep the source unchanged and make exclusion rules visible. A new surrogate key is not a substitute for deduplication.

build it step by step

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

1. 1

   Profile duplicate natural keys. Bronze keeps every source version. Silver will trim keys, map countries, and keep the latest updated_at per key.

   step-1.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;

2. 2

   Execute a complete script. All setup and the final result run together in one disposable database.

   step-2.sql Run

   sql›

   CREATE TEMP TABLE silver_customers AS
   WITH ranked AS(
     SELECT
       *,
       ROW_NUMBER() OVER(PARTITION BY TRIM(raw_id) ORDER BY updated_at DESC) AS rn
     FROM bronze_customers
     WHERE raw_id IS NOT NULL AND TRIM(raw_id)<>''
   )
   SELECT
     TRIM(raw_id) AS customer_id,
     TRIM(name) AS name,
     CASE UPPER(TRIM(country))
       WHEN 'US' THEN 'United States'
       WHEN 'USA' THEN 'United States'
       WHEN 'UK' THEN 'United Kingdom'
       WHEN 'GB' THEN 'United Kingdom'
       WHEN 'IND' THEN 'India'
       WHEN 'CA' THEN 'Canada'
       ELSE UPPER(TRIM(country))
     END AS country
   FROM ranked
   WHERE rn=1;
   SELECT customer_id,name,country
   FROM silver_customers;