Medallion contracts between bronze, silver, and gold

Description

Foundations already used the Medallion architecture — bronze, silver, gold — to organize its pipeline, and this guide rebuilt, module after module, tables that live in that gold layer: fact_orders, dim_date, fact_sessions, fact_store_activity, and now dim_order_flags. But no lesson, until this one, put in writing a verifiable rule about what shape each gold table must have before it's considered published. This lesson solves exactly that: validate_gold_schema(con, table, expected_columns), a Python function that compares a table's real schema against the expected schema — column names, data types — and reports any discrepancy as a list of text. You're going to run it over this guide's four gold tables, with zero discrepancies, and use it to actually catch a schema change that would silently break a report if nobody verified it.

Connection to the module. This lesson builds the module's second central executable result: the function this guide's design explicitly names, run over fact_orders, fact_sessions, fact_store_activity, and dim_date — the guide's four gold tables. This lesson's "contract" is deliberately small: a local function, not a published system — the exact boundary that separates this guide from data-reliability-and-governance-guide.

An analogy: the packing list, checked before closing the suitcase

Think about how someone packs a suitcase for an important trip — a job interview, a wedding: they write, in advance, a list of what must go inside — suit, documents, charger — and before closing the suitcase, they check it against the list, item by item. The list doesn't check whether the suit is clean or whether the charger works — that would be a different, deeper check; it only confirms each expected item is present, and that nothing unexpected snuck in (like an item from the previous trip nobody took out). Without that list, someone could close the suitcase confidently and discover, already at the airport, that the charger stayed home.

validate_gold_schema() is that packing list, applied to a table instead of a suitcase. It doesn't check whether the data inside fact_orders is correct — validate_orders() in foundations and module 1's grain assert already did that; it only checks that the expected columns — the schema's "items" — are all present, with the right type, and that no unexpected column snuck in. Running it before publishing each gold table is, exactly, checking the list before closing the suitcase.

Worked example: validate_gold_schema(), built and tested

Part 1 — The function, with DESCRIBE as the source of truth

DuckDB exposes any table's real schema with DESCRIBE, a statement that returns, per column, its name and its type — the same source you already used, without naming it that way, every time you checked a new table in this guide.

# validate_gold_schema.py
import duckdb


def validate_gold_schema(con: duckdb.DuckDBPyConnection, table: str, expected_columns: dict[str, str]) -> list[str]:
    """Compares table's real columns (name and type) against expected_columns
    (dict column_name -> duckdb_type). Returns a list of text discrepancies;
    an empty list means the real schema matches the expected one exactly.
    Doesn't validate data, only shape -- this guide's contract is local, not a system."""
    actual_rows = con.sql(f"DESCRIBE {table}").fetchall()
    actual_columns = {row[0]: row[1] for row in actual_rows}
    discrepancies = []

    for column_name, expected_type in expected_columns.items():
        if column_name not in actual_columns:
            discrepancies.append(f"{table}: missing column '{column_name}' (expected type {expected_type})")
        elif actual_columns[column_name] != expected_type:
            discrepancies.append(
                f"{table}: '{column_name}' has type {actual_columns[column_name]}, expected {expected_type}"
            )

    for column_name in actual_columns:
        if column_name not in expected_columns:
            discrepancies.append(f"{table}: unexpected column '{column_name}', not declared in the contract")

    return discrepancies

Notice the two-pass structure: the first walks expected_columns and looks for each one in actual_columns (detects missing columns and wrong-typed columns); the second walks actual_columns and looks for each one in expected_columns (detects unexpected columns, ones nobody declared but that reached the table anyway). A schema contract that only did the first pass would let through, with no alert at all, any new column someone added without announcing it — exactly the scenario lesson 7 is going to exploit in depth.

Part 2 — The declared contract, over fact_orders

Before running the function over all four gold tables, test it on just one — fact_orders, rebuilt exactly as it stood since module 1 — with its schema contract explicitly declared:

# fact_orders_contract.py -- continues on top of con, with fact_orders already rebuilt (modules 1-4)
FACT_ORDERS_CONTRACT = {
    "order_id": "VARCHAR", "store_id": "VARCHAR", "product_id": "VARCHAR",
    "quantity": "INTEGER", "unit_price": "DOUBLE", "revenue": "DOUBLE", "order_ts": "TIMESTAMP",
}

discrepancies = validate_gold_schema(con, "fact_orders", FACT_ORDERS_CONTRACT)
print(f"fact_orders: {len(discrepancies)} discrepancies")
for d in discrepancies:
    print(f"  - {d}")
assert discrepancies == [], "fact_orders's contract broke"
print("Verification OK: fact_orders meets its schema contract, no discrepancies at all")

What to expect.

fact_orders: 0 discrepancies
Verification OK: fact_orders meets its schema contract, no discrepancies at all

Zero discrepancies — the complete packing list, nothing missing and no surprises. Now, proof that the function actually detects something when the schema does change: simulate, on purpose, that someone added a column to fact_orders without updating the contract.

# schema_drift_demo.py -- simulates an unannounced schema change
con.execute("ALTER TABLE fact_orders ADD COLUMN loyalty_points INTEGER")

discrepancies_after_drift = validate_gold_schema(con, "fact_orders", FACT_ORDERS_CONTRACT)
print(f"fact_orders (after ALTER TABLE): {len(discrepancies_after_drift)} discrepancies")
for d in discrepancies_after_drift:
    print(f"  - {d}")

What to expect.

fact_orders (after ALTER TABLE): 1 discrepancies
  - fact_orders: unexpected column 'loyalty_points', not declared in the contract

validate_gold_schema() immediately detected the new column nobody declared — exactly the kind of silent change that, without this verification, would propagate to any report querying fact_orders with a SELECT *, with nobody finding out until something broke downstream.

The complete contract: the guide's four gold tables

With the function already tested on fact_orders, declare the guide's four gold tables' complete contract — rebuilt without the previous demonstration's ALTER TABLE — and run the validation on all four at once.

# gold_contracts.py -- rebuilds the 4 gold tables without the demonstration's ALTER TABLE, and validates
GOLD_CONTRACTS = {
    "fact_orders": {
        "order_id": "VARCHAR", "store_id": "VARCHAR", "product_id": "VARCHAR",
        "quantity": "INTEGER", "unit_price": "DOUBLE", "revenue": "DOUBLE", "order_ts": "TIMESTAMP",
    },
    "fact_sessions": {
        "session_id": "VARCHAR", "store_id": "VARCHAR", "session_date": "DATE",
        "view_ts": "TIMESTAMP", "add_to_cart_ts": "TIMESTAMP", "purchase_ts": "TIMESTAMP",
        "is_converted": "BOOLEAN",
    },
    "fact_store_activity": {
        "store_id": "VARCHAR", "activity_date": "DATE", "daily_revenue": "DOUBLE",
        "revenue_array_7d": "DOUBLE[]", "active_days_7d": "INTEGER",
        "revenue_array_30d": "DOUBLE[]", "active_days_30d": "INTEGER",
    },
    "dim_date": {
        "date_key": "INTEGER", "calendar_date": "DATE", "day_of_week": "VARCHAR",
        "month": "INTEGER", "quarter": "INTEGER", "year": "INTEGER", "is_weekend": "BOOLEAN",
    },
}

print("=== validate_gold_schema(): contract run over the guide's 4 gold tables ===")
total_discrepancies = 0
for table, expected_columns in GOLD_CONTRACTS.items():
    discrepancies = validate_gold_schema(con, table, expected_columns)
    total_discrepancies += len(discrepancies)
    status = "OK, 0 discrepancies" if not discrepancies else f"{len(discrepancies)} discrepancies"
    print(f"  {table:20} {status}")
    for d in discrepancies:
        print(f"    - {d}")

assert total_discrepancies == 0, "the Medallion contract broke: there are schema discrepancies"
print(f"\nVerification: {len(GOLD_CONTRACTS)} gold tables, {total_discrepancies} discrepancies total -- OK")

What to expect.

=== validate_gold_schema(): contract run over the guide's 4 gold tables ===
  fact_orders          OK, 0 discrepancies
  fact_sessions        OK, 0 discrepancies
  fact_store_activity  OK, 0 discrepancies
  dim_date             OK, 0 discrepancies

Verification: 4 gold tables, 0 discrepancies total -- OK

Four gold tables, twenty-eight columns in total across all four, zero discrepancies — Kiosko's gold layer's complete contract, verified with a single function, no manual review needed. Notice something important: dim_order_flags, the dimension built in the previous lesson, does not appear in this contract — not because it doesn't matter, but because this guide's design defines the four gold tables verified here as fact_orders, fact_sessions, fact_store_activity, and dim_date, with dim_order_flags as a smaller, supporting dimension. Nothing stops you from extending GOLD_CONTRACTS with its own schema — this lesson's exercise 2 does exactly that.

Diagram: where the contract lives, between which layers

flowchart LR
    B["BRONZE\nRaw CSV, partitioned\nby date (foundations)"] --> S["SILVER\nvalidate_orders() +\ntransform_fact_orders()\n(foundations)"]
    S --> G["GOLD\nfact_orders, fact_sessions,\nfact_store_activity, dim_date\n(this guide)"]
    G -.->|"validate_gold_schema()\nBEFORE publishing"| G
    G --> BI["BI consumers\n(dashboards, reports)"]

Going deeper: why the contract lives in gold, not bronze or silver

It's worth being explicit about where this module places the verification, because it isn't arbitrary. Bronze is, by design — as foundations built it — tolerant of any shape: it receives the raw data as it arrives, with no schema guarantee at all, precisely because its job is to preserve what arrived, not to judge it. Silver already applies a first quality gate — validate_orders() — but over data, not schema: rows with empty fields, invalid types, duplicates — never a missing or wrong-typed column, because silver in foundations always writes with a fixed schema its own code controls.

Gold is different: it's the layer other teams consume directly — dashboards, reports, the next guide in this series (dbt-analytics-engineering-guide, which versions this same model as code) — and it's exactly there where an undetected schema change does the most damage, because it propagates to consumers who don't even know the warehouse changed. That's why validate_gold_schema() runs, precisely, over the gold layer: it's the last checkpoint before the data leaves the direct control of the team modeling it, toward people who trust the shape didn't change without notice.

Common mistakes

Thinking validate_gold_schema() replaces foundations's validate_orders(). What happens: someone, seeing two functions with similar names ("validate"), assumes one replaces the other, or that validate_orders() no longer needs to run if validate_gold_schema() exists. Why it happens: both start with "validate," and both appear in the same conceptual bronze→silver→gold pipeline. How to spot it: if you stop running validate_orders() over bronze thinking validate_gold_schema() already covers that validation, you're going to let rows with negative quantity or empty fields through all the way to silver, with no gate at all. How to fix it: remember this lesson's exact distinction — 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, before publishing. They're complementary, not substitutes.

Declaring expected_columns by copying the table's real schema, instead of declaring it independently. What happens: someone, to "save time," generates expected_columns by running DESCRIBE over the already-built table, instead of writing the contract by hand, independently, before looking at the table. Why it happens: copying the current schema guarantees validate_gold_schema()'s first run is going to give zero discrepancies, which feels like immediate success. How to spot it: if your expected_columns always matches the real table exactly, with no exceptions, that's a sign you're never going to detect anything — the contract became a mirror of reality, not an independent expectation reality must meet. How to fix it: declare expected_columns before building the table, or at least independently of its current schema — as this lesson did, writing FACT_ORDERS_CONTRACT from the definition module 1 already established, not reading it off the table at the moment. Only then can the contract actually fail when something changes.

Running validate_gold_schema() only once, at the end of the project, instead of before every publication. What happens: someone runs the validation a single time, after finishing the complete warehouse build, and assumes the contract is then "fulfilled forever." Why it happens: running something once and seeing "0 discrepancies" feels like a permanently solved problem. How to spot it: if someone modifies fact_orders — adds a column, changes a type — six months later, and nobody reruns validate_gold_schema(), the discrepancy never gets detected, exactly as this lesson's ALTER TABLE demonstrated. How to fix it: a schema contract's real value isn't in running it once — it's in running it every time the gold layer gets rebuilt or published, as part of the pipeline itself, not as an occasional manual check. This guide doesn't go as far as automating that "every time" — that's real orchestration, territory of airflow-and-declarative-orchestration-guide — but the function itself is designed to run on every publication, not just once.

Exercises

Exercise 1 — Extend the contract with dim_order_flags and validate it. Declare DIM_ORDER_FLAGS_CONTRACT with its three columns (flag_key: INTEGER, payment_method: VARCHAR, channel: VARCHAR), add it to GOLD_CONTRACTS, and confirm it also passes with no discrepancies.

GOLD_CONTRACTS["dim_order_flags"] = {
    "flag_key": "INTEGER", "payment_method": "VARCHAR", "channel": "VARCHAR",
}
discrepancies = validate_gold_schema(con, "dim_order_flags", GOLD_CONTRACTS["dim_order_flags"])
print(f"dim_order_flags: {len(discrepancies)} discrepancies")
assert discrepancies == []

dim_order_flags: 0 discrepancies

Exercise 2 — Simulate someone renaming quantity to qty in fact_orders, and observe the two discrepancies it produces. Without using ALTER TABLE ... ADD COLUMN as in the worked example, use ALTER TABLE fact_orders RENAME COLUMN quantity TO qty and rerun validate_gold_schema().

con.execute("ALTER TABLE fact_orders RENAME COLUMN quantity TO qty")
discrepancies = validate_gold_schema(con, "fact_orders", FACT_ORDERS_CONTRACT)
print(f"fact_orders (after RENAME): {len(discrepancies)} discrepancies")
for d in discrepancies:
    print(f"  - {d}")

fact_orders (after RENAME): 2 discrepancies
  - fact_orders: missing column 'quantity' (expected type INTEGER)
  - fact_orders: unexpected column 'qty', not declared in the contract

Exercise 3 — Explain, from memory, why validate_gold_schema() doesn't receive any date or row-range parameter. In 2-3 sentences, explain why this function, unlike almost all the rest of the executable code in the guide, needs no business data as input.

Summary and next step

This lesson built validate_gold_schema(): a function that compares a table's real schema — via DESCRIBE — against an explicitly declared contract, and reports any missing, wrong-typed, or unexpected column as a list of text discrepancies. Run over this guide's four gold tables — fact_orders, fact_sessions, fact_store_activity, dim_date — it confirmed zero discrepancies; simulated against an unannounced ALTER TABLE, it immediately detected the new column. This is, precisely, the "contract" this module's title refers to: a local function, run over metadata, not a published data governance system.

Before moving on you should be able to: write validate_gold_schema()'s two-pass structure from memory (missing/wrong-typed columns, unexpected columns); explain why the contract lives in gold and not in bronze or silver; and describe the difference between validating data (validate_orders()) and validating schema (validate_gold_schema()).

Lesson 6 uses dim_date — one of this contract's four tables — for its full conformed purpose: joining Kiosko's three facts against the same calendar, something no previous module did with all three tables at once.

Resources

• Databricks — "What is the medallion lakehouse architecture?" — the official bronze/silver/gold definition this lesson deepens with a verifiable contract between silver and gold. docs.databricks.com/aws/en/lakehouse/medallion. In English.
• DuckDB — official documentation on the DESCRIBE statement (schema metadata: column name, type), validate_gold_schema()'s source of truth. duckdb.org/docs/current/guides/meta/describe. In English.
• DuckDB — official documentation on the ALTER TABLE statement (add, rename, change column type), used in this lesson to simulate unannounced schema changes. duckdb.org/docs/current/sql/statements/alter_table. 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.