Designing the Silver Layer

In the previous lecture, we built the Bronze layer — a raw, unfiltered, append-only landing zone that preserves exactly what arrived, duplicates and all. In this lecture, let's build Silver on top of it, where those duplicates finally get cleaned up.

Design Principle: Silver Enforces Contracts

Bronze accepted everything. Silver enforces contracts.

Every record that enters Silver has been cleaned, typed, deduplicated, and validated. Every record that leaves Silver meets a quality bar your downstream consumers can rely on.

This is the layer your data scientists query for exploration. This is the layer your Gold aggregations read from. If Silver is wrong, everything built on top of it is wrong. And here's the sobering part: the production cost of a bad Silver layer usually isn't a broken pipeline that throws an obvious error — it's wrong numbers in a dashboard that no one catches for months.

The two most important jobs Silver does are deduplication and type enforcement:

• Deduplication, because source systems send duplicates, and downstream consumers can't handle them.
• Type enforcement, because everything in Bronze is a string — unit prices, dates, quantities — and you cannot aggregate strings.

Step 1: Confirm the Duplicates Exist

Before fixing anything, let's confirm exactly what we're dealing with:

sqlCopy
%sql
-- How many duplicates are in Bronze orders?
SELECT order_id, COUNT(*) AS cnt
FROM dev.dbx_course.bronze_orders
GROUP BY order_id
HAVING cnt > 1;

This returns order_id 1001 with a count of 2 — exactly the duplicate we deliberately introduced into Bronze in the last lecture.

Step 2: Build Silver Orders — Deduplicate, Cast, Validate, Derive

sqlCopy
%sql
-- Silver orders: deduplicate, cast types, validate nulls, derive order_total
-- ROW_NUMBER deduplication: keep latest record per order_id
-- Null filter: any record missing a key business field is excluded from Silver

DROP TABLE IF EXISTS dev.dbx_course.silver_orders;

CREATE TABLE dev.dbx_course.silver_orders AS
SELECT
    CAST(order_id AS INT)                AS order_id,
    customer_id,
    product_id,
    CAST(quantity AS INT)                AS quantity,
    CAST(unit_price AS DECIMAL(10,2))    AS unit_price,
    CAST(order_date AS DATE)             AS order_date,
    status,
    CAST(quantity AS INT) * CAST(unit_price AS DECIMAL(10,2)) AS order_total,
    --simple multiplication that belongs in Silver rather than being recomputed in every Gold query that needs it
    _ingest_timestamp,
    _source
FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY _ingest_timestamp DESC
        ) AS rn
    FROM dev.dbx_course.bronze_orders
    WHERE order_id IS NOT NULL
      AND customer_id IS NOT NULL
      AND quantity IS NOT NULL
      AND unit_price IS NOT NULL
)
WHERE rn = 1;

Let's break down what's happening here — it's doing four distinct jobs in a single statement:

1. Type casting — order_id becomes INT, quantity becomes INT, unit_price becomes DECIMAL(10,2), order_date becomes DATE. Everything that arrived as a string in Bronze now has its correct, usable type.
2. Deduplication — ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _ingest_timestamp DESC) ranks duplicate records per order_id, most recent first. The outer query keeps only rn = 1 — the single latest version of each order.
3. Null validation — the WHERE clause requires order_id, customer_id, quantity, and unit_price to all be non-null. Any record missing one of these key business fields is silently excluded from Silver (it still exists safely in Bronze, just not promoted forward).
4. Deriving order_total — computed once, here in Silver, rather than being recalculated in every downstream Gold query that needs it. This is a deliberate design choice: a simple, reliable derived value belongs in Silver, not repeated logic scattered across every consumer.

Step 3: Verify the Result

sqlCopy
%sql
-- 8 rows expected: 9 Bronze rows minus 1 duplicate
SELECT * FROM dev.dbx_course.silver_orders ORDER BY order_id;

Silver orders — deduplicated and properly typed

The result confirms everything worked as intended:

• order_id is now an integer, not a string.
• order_id 1001 appears only once.
• order_total is correctly computed (e.g., 2 × 29.99 = 59.98).
• Row count is 8 — down from Bronze's 9, exactly matching "9 Bronze rows minus 1 duplicate."

Step 4: Build Silver Customers — The Same Pattern

sqlCopy
%sql
-- Check customer duplicates
SELECT customer_id, COUNT(*) AS cnt
FROM dev.dbx_course.bronze_customers
GROUP BY customer_id
HAVING cnt > 1;

sqlCopy
%sql
DROP TABLE IF EXISTS dev.dbx_course.silver_customers;

CREATE TABLE dev.dbx_course.silver_customers AS
SELECT
    customer_id,
    first_name,
    last_name,
    email,
    city,
    CAST(signup_date AS DATE) AS signup_date,
    tier,
    _ingest_timestamp,
    _source
FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY _ingest_timestamp DESC
        ) AS rn
    FROM dev.dbx_course.bronze_customers
    WHERE customer_id IS NOT NULL
      AND email IS NOT NULL
)
WHERE rn = 1;

sqlCopy
%sql
-- 4 rows expected: 5 Bronze rows minus 1 duplicate
SELECT * FROM dev.dbx_course.silver_customers ORDER BY customer_id;

Exactly the same three ingredients — type casting (signup_date becomes a proper DATE), deduplication (keep the latest C001 record), and null validation (customer_id and email must be present) — applied to the customers feed.

Production Pattern: Incremental Silver

Everything so far used CREATE TABLE ... AS SELECT — which rebuilds Silver completely from scratch every time. That's fine for a first load, or a demo, but it's not how Silver actually runs in production, day after day. For that, we need an incremental pattern:

sqlCopy
%sql
-- Incremental Silver load pattern: MERGE for idempotent daily runs
-- _ingest_timestamp filter ensures only new Bronze records are processed
    -- look at the highest timestamp already in Silver and only processes Bronze records that arrived after that point.
    -- This pattern scales — it works the same way whether Bronze has 10,000 records or 10 billion.
-- Run this on day 2, day 3, day N — result is always correct

MERGE INTO dev.dbx_course.silver_orders AS target
USING (
    SELECT
        CAST(order_id AS INT)                AS order_id,
        customer_id,
        product_id,
        CAST(quantity AS INT)                AS quantity,
        CAST(unit_price AS DECIMAL(10,2))    AS unit_price,
        CAST(order_date AS DATE)             AS order_date,
        status,
        CAST(quantity AS INT) * CAST(unit_price AS DECIMAL(10,2)) AS order_total,
        _ingest_timestamp,
        _source
    FROM (
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY order_id
                ORDER BY _ingest_timestamp DESC
            ) AS rn
        FROM dev.dbx_course.bronze_orders
        WHERE order_id IS NOT NULL
          AND _ingest_timestamp > (SELECT MAX(_ingest_timestamp) FROM dev.dbx_course.silver_orders)
    )
    WHERE rn = 1
) AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

A few things worth understanding about why this shape matters:

• The _ingest_timestamp filter is the key to making this incremental. ... > (SELECT MAX(_ingest_timestamp) FROM dev.dbx_course.silver_orders) looks at the latest timestamp already present in Silver, and only processes Bronze records that arrived after that point. Already-processed records are never touched again.
• This is exactly why we recall from the Bronze lecture: Bronze uses append, not overwrite, in production. Incremental Silver depends on being able to see new Bronze records distinctly from already-processed ones — which only works if Bronze keeps accumulating history rather than being wiped and replaced each run.
• This pattern scales. Whether Bronze has 10,000 records or 10 billion, this MERGE only ever processes the genuinely new slice — not the whole table, every time.
• It's idempotent. You can run this on day 2, day 3, day N — the result is always correct. If you accidentally run it twice on the same day with no new data, nothing changes (there's nothing newer than what's already in Silver to pick up).
• WHEN MATCHED THEN UPDATE SET * / WHEN NOT MATCHED THEN INSERT * — this is the MERGE upsert pattern we learned in the Delta Lake chapter, now applied to a genuinely realistic Bronze→Silver production scenario.

Summary

Silver is where raw, messy reality gets turned into something trustworthy. In the next lecture, we'll build Gold on top of this clean foundation — shaping it into the specific business metrics people actually consume.

See you again. Keep learning, and keep growing!