Rebuilding bronze and silver from foundations

Description

This guide's seven previous modules took for granted that fact_orders already existed — you received it, ready, from foundations's capstone, and rebuilt it over and over with transform_fact_orders() over RAW_ORDERS. That shortcut was deliberate: this guide's focus was never ingestion, but modeling. But an integrated warehouse can't start halfway through — it needs, first, the two layers foundations built that this guide never explicitly rebuilt under their own names: bronze (the raw data, as it arrived) and silver (the validated, modeled data). This lesson closes that debt: it names bronze and silver with their own DuckDB tables, runs the same quality gate foundations designed, and confirms — with the same assert as always — that the result is, number for number, the fact_orders you already know.

Connection to the module. This lesson introduces no new modeling concept — it names, with real DuckDB tables, the Medallion architecture's first two layers module 7 (lesson 5) already formalized with validate_gold_schema(). It's the integrated warehouse's first step: without bronze and silver rebuilt here, none of lessons 4, 5, and 6's gold layers would have a fact_orders to build on.

An analogy: chain of custody, from the counter to the file

Think about how a clinical lab handles a blood sample: first, the raw sample, exactly as the nurse drew it, labeled with the date and patient, no result yet at all — that's bronze. Afterward, a technician processes it: discards contaminated or mislabeled samples, and converts the valid ones into a result with standard units, ready for the medical file — that's silver. No serious lab jumps straight from "the sample arrived" to "here's the result" without that intermediate quality-control step — and no lab keeps only the final result, discarding the raw sample, because if a questionable result ever needs auditing, the original sample is the only way to verify it from scratch.

This lesson builds, with bronze_orders and fact_orders, exactly that chain of custody: the raw data gets preserved, untouched; the quality gate decides what passes; and the final result — fact_orders — is traceable, step by step, all the way back to the raw row it came from.

Worked example: bronze, the quality gate, and silver

Part 1 — Bronze: the raw landing, untransformed

RAW_ORDERS, Kiosko's fixed week you already used in the previous eight modules, is this guide's equivalent of foundations's seven orders_*.csv files — the same forty values, declared as fixed data in Python instead of files on disk, so this guide is self-contained. Bronze lands those values exactly as they arrive, without converting any type yet: quantity and unit_price get stored as text (VARCHAR), just as they'd arrive from a real CSV file, because bronze's responsibility is to preserve the source data, not interpret it.

# capstone_bronze_silver.py -- Part 1: BRONZE
from datetime import datetime

import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS
from events import RAW_EVENTS

REQUIRED_COLUMNS = ["order_id", "store_id", "product_id", "quantity", "unit_price", "order_ts"]

print("=== Kiosko: rebuilding bronze and silver from foundations ===\n")

con = duckdb.connect()

bronze_rows = [
    {"order_id": r[0], "store_id": r[1], "product_id": r[2],
     "quantity": str(r[3]), "unit_price": str(r[4]), "order_ts": r[5]}
    for r in RAW_ORDERS
]
con.execute("""
    CREATE TABLE bronze_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity VARCHAR, unit_price VARCHAR, order_ts VARCHAR
    )
""")
con.executemany(
    "INSERT INTO bronze_orders VALUES (?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"], r["unit_price"], r["order_ts"]) for r in bronze_rows],
)
con.execute("CREATE TABLE bronze_events (event_id VARCHAR, event_type VARCHAR, session_id VARCHAR, event_ts VARCHAR)")
con.executemany("INSERT INTO bronze_events VALUES (?, ?, ?, ?)", RAW_EVENTS)

print("Part 1 -- BRONZE: raw landing, untransformed")
print(f"  bronze_orders   {con.sql('SELECT COUNT(*) FROM bronze_orders').fetchone()[0]:3} rows (quantity and unit_price as VARCHAR)")
print(f"  bronze_events   {con.sql('SELECT COUNT(*) FROM bronze_events').fetchone()[0]:3} rows (module 6's 32 canonical events)")

What to expect.

=== Kiosko: rebuilding bronze and silver from foundations ===

Part 1 -- BRONZE: raw landing, untransformed
  bronze_orders    40 rows (quantity and unit_price as VARCHAR)
  bronze_events    32 rows (module 6's 32 canonical events)

Notice quantity and unit_price's declared type: VARCHAR, not INTEGER nor DOUBLE. That choice isn't an oversight — it's the same decision bronze.py made in foundations when writing each row directly from the original CSV, with no int() nor float() applied yet. Bronze never decides whether a value is valid; it only preserves it, as it arrived.

Part 2 — The quality gate: validate_orders(), with no change in criteria

validate_orders(), as foundations designed it, separates valid rows from rejected ones with four rules: no required field empty, quantity an integer greater than zero, unit_price a non-negative decimal, and no repeated order_id within the same batch. This lesson rebuilds it with the exact same criteria, adapted to Kiosko's columns:

def validate_orders(rows: list[dict]) -> tuple[list[dict], list[dict]]:
    """Separates raw bronze rows into (valid, rejected) -- the same schema/null/
    type/range criteria as foundations's validate_orders()."""
    valid: list[dict] = []
    rejected: list[dict] = []
    seen_order_ids: set[str] = set()
    for row in rows:
        reasons: list[str] = []
        missing = [c for c in REQUIRED_COLUMNS if not row.get(c)]
        if missing:
            reasons.append(f"missing or empty fields: {missing}")
            rejected.append({"row": row, "reasons": reasons})
            continue
        try:
            quantity = int(row["quantity"])
        except ValueError:
            reasons.append(f"quantity is not a valid integer: {row['quantity']!r}")
            quantity = None
        try:
            unit_price = float(row["unit_price"])
        except ValueError:
            reasons.append(f"unit_price is not a valid decimal: {row['unit_price']!r}")
            unit_price = None
        if quantity is not None and quantity <= 0:
            reasons.append(f"quantity must be > 0, got {quantity}")
        if unit_price is not None and unit_price < 0:
            reasons.append(f"unit_price must be >= 0, got {unit_price}")
        if row["order_id"] in seen_order_ids:
            reasons.append(f"duplicate order_id: {row['order_id']}")
        if reasons:
            rejected.append({"row": row, "reasons": reasons})
        else:
            seen_order_ids.add(row["order_id"])
            valid.append(row)
    return valid, rejected


valid_rows, rejected_rows = validate_orders(bronze_rows)
print(f"\nPart 2 -- THE QUALITY GATE: validate_orders()")
print(f"  valid rows:    {len(valid_rows)}")
print(f"  rejected rows: {len(rejected_rows)}")
assert len(valid_rows) == 40 and len(rejected_rows) == 0
print("  Verification OK: Kiosko's 40 rows pass the gate, zero rejected")

What to expect.

Part 2 -- THE QUALITY GATE: validate_orders()
  valid rows:    40
  rejected rows: 0

Zero rejected — exactly what you already knew since foundations's module 5: Kiosko's fixed data is clean on purpose, so this guide's focus stays on dimensional modeling, not data cleaning. But notice the gate ran anyway, with all four complete rules — it didn't get skipped because "you already knew it would give zero." Confirming it with evidence, even when the result is expected, is the same discipline module 1 demanded when declaring the grain.

Part 3 — Silver: transform_fact_orders(), the gold you already know

With the rows validated, transform_fact_orders() — from module 1, with no change at all — converts each valid row into a fact_orders line, with revenue calculated for the first time.

orders = [
    Order(order_id=r["order_id"], store_id=r["store_id"], product_id=r["product_id"],
          quantity=int(r["quantity"]), unit_price=float(r["unit_price"]),
          order_ts=datetime.fromisoformat(r["order_ts"]))
    for r in valid_rows
]
fact_orders_rows = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)

con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany(
    "INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders_rows],
)
con.execute("CREATE TABLE events (event_id VARCHAR, event_type VARCHAR, session_id VARCHAR, event_ts TIMESTAMP)")
con.executemany("INSERT INTO events VALUES (?, ?, ?, ?)",
    [(r[0], r[1], r[2], datetime.fromisoformat(r[3])) for r in RAW_EVENTS])

total_orders = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
total_revenue = con.sql("SELECT ROUND(SUM(revenue), 2) FROM fact_orders").fetchone()[0]
grain_check = con.sql("SELECT COUNT(*), COUNT(DISTINCT order_id || '-' || product_id) FROM fact_orders").fetchone()

print(f"\nPart 3 -- SILVER: transform_fact_orders(), the fact_orders you always knew")
print(f"  fact_orders  {total_orders:3} rows, total revenue = {total_revenue}")
print(f"  grain verified: COUNT(*)={grain_check[0]} == COUNT(DISTINCT order_id-product_id)={grain_check[1]}")
assert total_orders == 40 and total_revenue == 106.15
assert grain_check[0] == grain_check[1] == 40
print("  Verification OK: bronze -> silver reproduces fact_orders identical to foundations's, no differences")

What to expect.

Part 3 -- SILVER: transform_fact_orders(), the fact_orders you always knew
  fact_orders   40 rows, total revenue = 106.15
  grain verified: COUNT(*)=40 == COUNT(DISTINCT order_id-product_id)=40

Verification OK: bronze -> silver reproduces fact_orders identical to foundations's, no differences

106.15, again — the number that opened this guide in module 1 is still the same at the end of the path that starts in bronze. That is, precisely, what this lesson demonstrates: nothing in this guide's eight modules changed the source data — it only added structure, history, and context around it.

Diagram: the bronze -> silver chain, with evidence at each step

flowchart LR
    A["RAW_ORDERS (40)\nRAW_EVENTS (32)\nfixed, from foundations and M6"] --> B["bronze_orders, bronze_events\nraw VARCHAR, untransformed"]
    B --> C["validate_orders()\n40 valid, 0 rejected"]
    C --> D["transform_fact_orders()\nfact_orders, revenue calculated"]
    D --> E["fact_orders: 40 rows\nrevenue = 106.15\nVERIFIED"]

Going deeper: why this guide never named "bronze" until now

It's worth being honest about a decision in this guide: modules 1 through 7 never created a table called bronze_orders — they simply assumed fact_orders already existed, rebuilt directly from RAW_ORDERS with transform_fact_orders(), with no intermediate step under its own name. That was a deliberate pedagogical decision: this guide's focus was always dimensional modeling, not ingestion — that boundary was already declared since module 1, and data-engineering-foundations-guide is the guide that teaches bronze and silver in depth, with real disk partitioning and the overwrite-partition pattern.

This lesson doesn't contradict that decision — it makes it explicit, now that the capstone needs to show the complete path. Naming bronze_orders and running validate_orders() here doesn't mean "starting to teach data ingestion" — it recognizes, with real tables, that the fact_orders this guide took for granted since its first line always had those two layers behind it, even though you never made them visible until this final module.

Common mistakes

Thinking bronze and silver, in this module, are a disk-partitioned pipeline like in foundations. What happens: someone, seeing the names "bronze" and "silver," expects to find here the same data/bronze/orders/dt=YYYY-MM-DD/orders.csv pattern with date partitioning foundations built. Why it happens: the names are identical, and it's reasonable to expect the same implementation. How to spot it: if you look in this lesson for a call to pathlib.Path or csv.DictWriter, you're not going to find one. How to fix it: as this guide's design declared since module 1, real orchestration and disk partitioning are territory of data-engineering-foundations-guide (already built) and airflow-and-declarative-orchestration-guide (sibling guide). Here, bronze and silver are DuckDB tables inside the same connection — the same conceptual responsibility, with a deliberately simpler implementation, because this guide's focus was always the model, not ingestion infrastructure.

Confusing validate_orders() with module 7's validate_gold_schema(). What happens: someone, seeing two functions with similar names ("validate"), tries to use validate_gold_schema() to check bronze_orders's rows, or validate_orders() to check a gold table's schema. Why it happens: both start with "validate," and both appear in the same bronze→silver→gold flow. How to spot it: if you pass bronze_orders (a table) to validate_orders() (which expects a list of raw-row dictionaries), or if you expect validate_gold_schema() to detect a negative quantity, you mixed up the two functions. How to fix it: remember the exact distinction module 7 already established — validate_orders() validates data (is this row correct?), runs between bronze and silver; validate_gold_schema() validates schema (does this table have the right shape?), runs over gold. This lesson only uses the first.

Skipping Part 2 because "you already know it gives zero rejected." What happens: someone, familiar with Kiosko's dataset after seven modules, decides to skip the call to validate_orders() and build fact_orders directly from bronze_rows, reasoning the result is going to be the same. Why it happens: after seeing "zero rejected" in every previous run, the gate starts to feel redundant. How to spot it: if your script jumps straight from bronze to transform_fact_orders(), with no intermediate validation step, you have no evidence the rows are valid — you have an assumption based on previous runs. How to fix it: the quality gate isn't a decorative step that can be skipped once "you already know the result" — it's the guarantee that, if Kiosko's dataset ever changed (a row with negative quantity, for example), the pipeline would detect it instead of silently letting it through to gold.

Exercises

Exercise 1 — Simulate a broken bronze row and confirm the gate rejects it. Add an extra row to bronze_rows, with quantity="-2" (an invalid quantity), and confirm validate_orders() moves it to rejected_rows with the correct reason, without affecting the original 40 rows.

broken_row = {"order_id": "ORD-9999", "store_id": "S01", "product_id": "P001",
              "quantity": "-2", "unit_price": "0.55", "order_ts": "2026-08-03T11:00:00"}
valid_with_break, rejected_with_break = validate_orders(bronze_rows + [broken_row])
print(f"valid: {len(valid_with_break)}, rejected: {len(rejected_with_break)}")
print(f"rejection reason: {rejected_with_break[0]['reasons']}")

valid: 40, rejected: 1
rejection reason: ['quantity must be > 0, got -2']

Exercise 2 — Confirm bronze_orders preserves the raw text, without converting types. Using bronze_orders, write a query that confirms quantity is still VARCHAR type in DuckDB, not INTEGER.

print(con.sql("DESCRIBE bronze_orders"))

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ order_id    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ store_id    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ product_id  │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ quantity    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ unit_price  │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ order_ts    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

Exercise 3 — Explain, from memory, why this lesson builds events (typed) in addition to bronze_events (raw). In 2-3 sentences, explain the difference between the two tables, and why this module's lesson 5 is going to need the second one, not the first.

Summary and next step

In this lesson you named, with real DuckDB tables, the Medallion architecture's first two layers this guide never made explicit until now: bronze_orders/bronze_events (raw, untransformed) and fact_orders/events (validated and typed, silver). You ran validate_orders() with foundations's exact same criteria — zero rejected out of forty — and confirmed, with the same assert as always, that the final result is identical to the fact_orders you know since module 1: 40 rows, revenue 106.15.

Before moving on you should be able to: explain the difference between bronze_orders (raw, VARCHAR) and fact_orders (silver, typed and modeled); name validate_orders()'s four rules; and confirm, from memory, the numbers this flow reproduces (40 rows, 0 rejected, revenue 106.15).

Lesson 4 takes this freshly rebuilt fact_orders and builds, on top of it, the complete star: dim_store, dim_date, and this module's central piece — dim_product_scd, historized with two MERGE INTO runs, joined with the point-in-time join that fixes a dimensional model's most expensive mistake.

Resources

• Databricks — "What is the medallion lakehouse architecture?" — the official bronze/silver/gold definition this lesson names with real tables for the first time in this guide. docs.databricks.com/aws/en/lakehouse/medallion. In English.
• DuckDB — official documentation on the DESCRIBE statement — used in exercise 2 to confirm bronze preserves each column's raw type. duckdb.org/docs/current/guides/meta/describe. In English.
• Python — official dataclasses documentation, reused with no change from foundations to represent Order. docs.python.org/3/library/dataclasses.html. In English.
• DuckDB — official Python client documentation, the interface that runs every query in this lesson. duckdb.org/docs/current/clients/python/overview. In English.