Module 7: Messy Domains And Medallion At Depth

Schema evolution without breaking gold

Next →

Description

Lesson 5 built the alarm: validate_gold_schema() detects, without fail, any difference between a gold table's real schema and the declared contract. But an alarm that goes off every time something changes — regardless of whether the change was an accident or a deliberate business decision — isn't enough on its own. This lesson solves the question left pending: when Kiosko genuinely needs fact_orders to have a new column — for example, loyalty_points, because the business launches a loyalty program — how does the schema evolve without validate_gold_schema() treating it as a break? The answer isn't turning off the alarm — it's updating the schema and the contract at the same time, in the same change.

Connection to the module. This lesson completes lesson 5's work with the other side of the coin: not only detecting accidental drift, but distinguishing it from deliberate evolution. It uses the same three discrepancies lesson 5 already named — added column, renamed column, changed-type column — but this time shows, for each one, the safe path and the unsafe path, side by side.

An analogy: renovating the house without losing the deed

Think about the difference between remodeling a house with an updated building permit, and remodeling without telling anyone. In both cases the house changes — a new wall, a room added; the real difference is whether the property's official record — the deed, the registered blueprints — gets updated at the same time as the work, or whether the work moves forward on its own, leaving the record out of date. A remodel with an updated blueprint is still a legal house, one you can look up, with clear history. A remodel done without telling anyone leaves, sooner or later, a discrepancy someone — an inspector, a future buyer — is going to discover in the worst possible way.

validate_gold_schema() is that inspector. It doesn't care whether a schema change is good or bad for the business — that's for the data team to decide, not the function; the only thing it checks is whether the "blueprint" (GOLD_CONTRACTS) matches the "work" (the real table). This lesson teaches how to do the remodel with a permit: update the table and the contract in the same change, never one without the other.

Worked example: the same change, with and without an updated contract

Scenario 1 — Adding a column: unsafe vs safe

Kiosko launches a loyalty program. fact_orders genuinely needs a new column: loyalty_points. First, the unsafe path — exactly the one you already saw in lesson 5:

# unsafe_add_column.py -- continues on top of con and fact_orders rebuilt, with FACT_ORDERS_CONTRACT declared
con.execute("ALTER TABLE fact_orders ADD COLUMN loyalty_points INTEGER")

print("=== Scenario 1: adding loyalty_points WITHOUT updating the contract (unsafe evolution) ===")
discrepancies = validate_gold_schema(con, "fact_orders", FACT_ORDERS_CONTRACT)
print(f"Discrepancies: {len(discrepancies)}")
for d in discrepancies:
    print(f"  - {d}")

What to expect.

=== Scenario 1: adding loyalty_points WITHOUT updating the contract (unsafe evolution) ===
Discrepancies: 1
  - fact_orders: unexpected column 'loyalty_points', not declared in the contract

Now, the safe path: the same ALTER TABLE, but accompanied by updating the contract in the same change — never the table alone, never the contract alone.

# safe_add_column.py -- the same ALTER TABLE, with the contract updated at the same time
FACT_ORDERS_CONTRACT_V2 = dict(FACT_ORDERS_CONTRACT)
FACT_ORDERS_CONTRACT_V2["loyalty_points"] = "INTEGER"

print("\n=== Scenario 1b: the same column, with the contract updated at the same time (safe evolution) ===")
discrepancies = validate_gold_schema(con, "fact_orders", FACT_ORDERS_CONTRACT_V2)
print(f"Discrepancies: {len(discrepancies)}")
assert discrepancies == []
print("Verification OK: adding the column AND updating the contract at the same time keeps 0 discrepancies")

What to expect.

=== Scenario 1b: the same column, with the contract updated at the same time (safe evolution) ===
Discrepancies: 0
Verification OK: adding the column AND updating the contract at the same time keeps 0 discrepancies

Neither the table nor the contract changed independently — they changed together, in the same unit of work (in a real repository, in the same pull request). FACT_ORDERS_CONTRACT_V2 is, literally, FACT_ORDERS_CONTRACT plus one line — the smallest possible form of evolution, and precisely for that reason the safest: adding a column never breaks anything that already existed, because nobody selecting explicit columns (SELECT order_id, revenue) is affected by a new column they didn't ask for.

Scenario 2 — Renaming a column: why it always breaks something

Now, the most dangerous case: someone decides quantity should be called qty, a shorter name.

# unsafe_rename.py -- simulating the rename on a new table, with the contract NOT updated
con_renamed = duckdb.connect()
con_renamed.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        qty INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP, loyalty_points INTEGER
    )
""")

print("=== Scenario 2: renaming quantity to qty (unsafe evolution, both sides broken) ===")
discrepancies = validate_gold_schema(con_renamed, "fact_orders", FACT_ORDERS_CONTRACT_V2)
print(f"Discrepancies: {len(discrepancies)}")
for d in discrepancies:
    print(f"  - {d}")

What to expect.

=== Scenario 2: renaming quantity to qty (unsafe evolution, both sides broken) ===
Discrepancies: 2
  - fact_orders: missing column 'quantity' (expected type INTEGER)
  - fact_orders: unexpected column 'qty', not declared in the contract

Notice that, unlike Scenario 1, here there isn't a symmetric "safe" version: updating the contract to expect qty instead of quantity would make validate_gold_schema() report zero discrepancies again, yes — but any existing query in the rest of this guide, or in any external dashboard, that writes SELECT quantity FROM fact_orders would break immediately, with a nonexistent-column error, with no way for validate_gold_schema() to prevent it. A rename always has two potential victims: the contract (which this module protects) and any external query using the old name (which this module can't protect). That's why, in a real production warehouse, a rename is almost never done in a single step — it's done by adding the new column, migrating consumers during a transition period, and only afterward removing the old column.

Scenario 3 — Changing a column's type: unsafe vs safe

Finally, a common production scenario: quantity starts overflowing INTEGER's range (Kiosko grows, and some corporate order asks for thousands of units), so someone widens it to BIGINT.

# unsafe_type_change.py -- the type changes, the contract doesn't
con_widened = duckdb.connect()
con_widened.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity BIGINT, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP, loyalty_points INTEGER
    )
""")

print("\n=== Scenario 3: quantity changes from INTEGER to BIGINT (type evolution, unsafe without notice) ===")
discrepancies = validate_gold_schema(con_widened, "fact_orders", FACT_ORDERS_CONTRACT_V2)
print(f"Discrepancies: {len(discrepancies)}")
for d in discrepancies:
    print(f"  - {d}")

# The safe path: update the contract in the same change
FACT_ORDERS_CONTRACT_V3 = dict(FACT_ORDERS_CONTRACT_V2)
FACT_ORDERS_CONTRACT_V3["quantity"] = "BIGINT"

print("\n=== Scenario 3b: same type change, with the contract updated to BIGINT ===")
discrepancies = validate_gold_schema(con_widened, "fact_orders", FACT_ORDERS_CONTRACT_V3)
print(f"Discrepancies: {len(discrepancies)}")
assert discrepancies == []
print("Verification OK")

What to expect.

=== Scenario 3: quantity changes from INTEGER to BIGINT (type evolution, unsafe without notice) ===
Discrepancies: 1
  - fact_orders: 'quantity' has type BIGINT, expected INTEGER

=== Scenario 3b: same type change, with the contract updated to BIGINT ===
Discrepancies: 0
Verification OK

Unlike the rename, widening a type (INTEGER to BIGINT, a wider numeric range of the same data type) is, almost always, safe for existing consumers — any query that already read quantity as an integer keeps working the same, because BIGINT still behaves like an integer, just with more range. Narrowing a type (for example, from BIGINT to INTEGER, or from DOUBLE to INTEGER) is the dangerous operation — it can truncate existing data — and this guide never recommends it without an explicit migration, out of scope here.

Diagram: the correct flow of a deliberate evolution

flowchart TD
    A["The business needs a real change\n(e.g. loyalty_points)"] --> B{"What kind of change?"}
    B -->|"Add column"| C["ALTER TABLE ADD COLUMN\n+ update GOLD_CONTRACTS\nIN THE SAME CHANGE"]
    B -->|"Widen a type"| D["ALTER TABLE ... TYPE\n+ update GOLD_CONTRACTS\nIN THE SAME CHANGE"]
    B -->|"Rename / narrow type"| E["2-step migration:\n1. add new column,\n2. transition period,\n3. only then remove the old one"]
    C --> F["validate_gold_schema()\n0 discrepancies"]
    D --> F
    E --> F

Going deeper: why "additive first" is the rule that survives in practice

The pattern emerging from this lesson's three scenarios has a name: additive evolution. Adding a new column never breaks anything that already existed — any query that doesn't ask for it simply doesn't see it. Widening a type almost never breaks anything — the range grows, it doesn't shrink. But renaming or removing a column, or narrowing a type, always has the potential to break something that already depended on the previous shape, and validate_gold_schema() — nor any local schema function — can't fully prevent it, because the problem isn't in the table: it's in the consuming code this guide doesn't control.

This is exactly why a local schema contract, like this module's, has a clear limit: it can confirm a table's shape is the expected one, but it can't track who else is querying that table, nor notify them when something changes. A real data governance system — with a catalog, with lineage, with automatic notification to consumers — can do that, and that's exactly data-reliability-and-governance-guide's territory. Here, the discipline you can take away is simpler but just as valuable: always prefer adding over renaming, and widening over narrowing — the cheapest rule to apply with no additional tooling at all.

Common mistakes

Updating the table without updating the contract, "for later." What happens: someone adds a real, necessary column to fact_orders, intending to update GOLD_CONTRACTS "in a bit," and forgets — or puts it off for another day. Why it happens: the schema change itself (the ALTER TABLE) feels like the complete job, and updating a Python dictionary in another file feels like a separable administrative step. How to spot it: if you run validate_gold_schema() and see a discrepancy about a column you yourself added on purpose, you didn't find a bug — you found your own out-of-date contract. How to fix it: the ALTER TABLE and the GOLD_CONTRACTS update should live in the same change, reviewed together — in a real repository, in the same commit or pull request — never as two steps separated in time.

Updating the contract without updating the real table. What happens: someone, planning a future change, adds loyalty_points to GOLD_CONTRACTS before the column actually exists in fact_orders — "so I don't forget later." Why it happens: it seems prudent to document the intention in advance. How to spot it: if validate_gold_schema() reports "missing column 'loyalty_points'" and you know that column shouldn't exist yet, your contract is ahead of reality, not reality behind the contract. How to fix it: the contract describes what the table is, not what you plan for it to be — add it to the contract at the same moment the ALTER TABLE (or the CREATE TABLE) makes it real, never before.

Treating a rename as if it were as safe as adding a column. What happens: someone, after seeing that "add column and update the contract" solves Scenario 1 with no problem, applies the same logic to Scenario 2's rename — updates GOLD_CONTRACTS to expect qty instead of quantity, and calls the change complete and safe. Why it happens: validate_gold_schema() does report zero discrepancies again after that change, which feels like success. How to spot it: if your "safe rename" was only verified by running validate_gold_schema(), and you never checked whether any query — in this guide, in a dashboard, in another script — still used the name quantity, your verification was incomplete. How to fix it: remember this lesson's exact distinction — a local schema contract can confirm the table and the contract match each other, but it cannot confirm no external consumer depended on the previous name. A rename needs, beyond updating the contract, a transition period where both names coexist, or a manual search for who else queries that column.

Exercises

Exercise 1 — Simulate adding channel directly to fact_orders (instead of using dim_order_flags), and confirm the contract detects it as an unexpected column. Using FACT_ORDERS_CONTRACT (without loyalty_points), add channel as a VARCHAR column to a copy of fact_orders without updating the contract, and run the validation.

See solution 
con_channel = duckdb.connect()
con_channel.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP, channel VARCHAR
    )
""")
discrepancies = validate_gold_schema(con_channel, "fact_orders", FACT_ORDERS_CONTRACT)
print(f"Discrepancies: {len(discrepancies)}")
for d in discrepancies:
    print(f"  - {d}")

Expected output:

Discrepancies: 1
  - fact_orders: unexpected column 'channel', not declared in the contract

This discrepancy is, in a sense, a second useful alarm beyond detecting accidental drift: if someone tried to add channel directly to fact_orders — the "loose columns" alternative lesson 4 already measured as a worse idea than the junk dimension — validate_gold_schema() would flag it immediately as a deviation from the declared contract, giving the team a chance to ask whether that's really the right way to add the attribute, before accepting it without question.

Exercise 2 — Design the two-step migration to safely rename quantity to qty. Without running any code, describe in 3-4 concrete steps how you would rename quantity to qty in a real production warehouse, without breaking any existing query in the process.

See solution
  1. Add qty as a new column (ALTER TABLE fact_orders ADD COLUMN qty INTEGER), populated with the same values as quantity — both columns coexist, and the contract gets updated to expect both.
  2. Announce the transition period to any known consumer (other scripts, dashboards, the next guide that versions this model), with a deadline to migrate from quantity to qty.
  3. Verify, after the transition period, that no active query still reads quantity — for example, by reviewing query logs if the engine had them, or simply confirming manually with each known consumer.
  4. Only then, remove quantity (ALTER TABLE fact_orders DROP COLUMN quantity) and update the contract to stop expecting it.

This multi-step process is, precisely, what a formal data governance system automates — consumer notices, deprecation periods, usage verification; here it's described manually because building it as a system is out of scope for this guide.

Exercise 3 — Explain, from memory, why "narrowing a type" is dangerous even if validate_gold_schema() reports no discrepancy while the change is in progress. In 2-3 sentences, describe what could go wrong if quantity went from BIGINT to INTEGER in a table with data already loaded, beyond what a schema contract can detect.

See solution

If fact_orders already had rows with quantity values exceeding INTEGER's range (possible if the table had been using BIGINT for a while), reducing the column's type could truncate or reject those values during the migration itself — a data problem, not a schema one. validate_gold_schema() only compares metadata after the change has already happened; it has no way to simulate, in advance, whether the existing data fits into the new, narrower type. That's why narrowing a type needs, beyond updating the contract, an explicit verification that no current value exceeds the new range — a data validation, the kind foundations's validate_orders() does, not the kind this module built.

Summary and next step

This lesson completed the Medallion contract's argument: adding a new column and widening a type are safe evolutions when updated at the same time as the contract — never the table alone, never the contract alone; renaming a column or narrowing a type are operations no local schema contract can make fully safe, because the real risk is in external consumers the contract can't track. The rule that survives in practice, with no additional tooling needed: add over rename, widen over narrow.

Before moving on you should be able to: explain why adding a column is the safest form of schema evolution; describe the steps of a two-stage rename migration; and justify why validate_gold_schema(), though useful, can't prevent every risk of a schema evolution on its own.

Lesson 8 — this module's closing project — integrates everything: it rebuilds Kiosko's complete warehouse, builds dim_order_flags from scratch, and runs validate_gold_schema() over the guide's four gold tables, documenting the result in a formal structure module 8 — the whole guide's capstone — is going to inherit without repeating the work.

Resources

← PreviousMultiple facts, a single conformed calendarNext →Mini-project: Kiosko's multi-fact gold layer