Silver: dedupe, type, rename, standardize

Silver is where the judgment lives. You decide: which column wins when two exist, which type each column must have, which rows count as duplicates, which values need normalizing. Every choice is visible in SQL, so your team can review the cleaning logic in one place.

One piece of SQL here goes beyond SELECT and GROUP BY: ROW_NUMBER() OVER (PARTITION BY ...). PARTITION BY groups rows the way GROUP BY does, except the rows stay intact instead of collapsing into one. Within each group, ROW_NUMBER hands out 1, 2, 3 in the ORDER BY order. So if we number each id group by newest submission first and keep only rn = 1, exactly one survivor remains per id. That is the whole dedupe trick.

data_engineering.ipynb
sql
CREATE OR REPLACE TABLE silver AS
WITH typed AS (
  SELECT
    id,
    LOWER(COALESCE(severity, Severity_Level, 'unknown')) AS severity,
    TRY_CAST(submitted_at AS TIMESTAMP)              AS submitted_at,
    LOWER(TRIM(state))                               AS state,
    reporter_id,
    team_id,
    team_handle,
    weakness_id,
    bounty_amount,
    ROW_NUMBER() OVER (
      PARTITION BY id
      ORDER BY submitted_at DESC
    ) AS rn
  FROM bronze
)
SELECT id, severity, submitted_at, state,
       reporter_id, team_id, team_handle, weakness_id, bounty_amount
FROM typed
WHERE rn = 1
  AND submitted_at IS NOT NULL;

The cleaning pass in one SQL statement. COALESCE handles the dual severity column. TRY_CAST converts strings to real timestamps without throwing on bad rows. ROW_NUMBER gives us a deterministic way to dedupe by source id while keeping the latest submission. Descriptive columns like team_handle and weakness_id pass through untouched because gold will need them.

Writing silver as one SQL statement is a habit worth keeping. It forces every cleaning decision to be visible on the same screen. If someone asks "how do we decide severity?", the answer is line four. No hidden helpers, no inherited notebook state.

Silver cleaning pipeline

Bronze in, cleaned silver out. Each step is explicit so any future reader can audit the contract.

Resolve before cast. Cast before dedupe. Dedupe before drop. Order makes silver auditable.

Dropping in silver is safe because bronze still has them. If someone needs them, you write a different silver_with_nulls table and point them there, or you write a gold table that joins bronze directly. Dropping in silver is a statement about what the trusted layer considers valid, not a promise that the rows never existed.

Ordering exercise: Order the silver cleaning steps

Loading practice…

Quiz: Quiz

Loading practice…