Surrogate keys vs natural keys

Description

So far, dim_store and dim_product are identified by their natural key: store_id (S01, S02, S03) and product_id (P001...P004), the same identifiers that already came from Kiosko's source system. This lesson adds, to each dimension, a surrogate key: an integer, generated by the dimensional model itself, with no business meaning, whose only job is to uniquely identify each row of the dimension — store_key, product_key.

Connection to the module. This is the module's first executable piece: you're going to modify dim_store and dim_product — adding a new column, generated deterministically, not randomly — and you're going to set the key pattern that lesson 4 (dim_date) and lesson 7 (the assembled star) take for granted.

An analogy: the file number, even when you already have an ID card

When someone opens an account at a bank, a doctor's office, or any institution that needs to keep a history, almost the same thing always happens: the person already has an identity document — an ID card, a passport, a national ID number — an identifier the state assigned them that, in theory, already identifies them uniquely. And yet, the institution assigns them another number: a customer number, a file number, a medical record number — belonging to that institution, with no meaning outside it.

Why bother creating a second identifier if the person already has one? Because the identity document has rules the institution doesn't control: someone can change their legal name, a document can expire and get renewed under a different number, two people from different countries could, in theory, share the same number sequence under different identification systems. The file number, on the other hand, is completely internal: the institution generates it, controls it, and can guarantee — because it depends only on itself — that it never repeats and never changes, no matter what happens to the original identity document.

A surrogate key is exactly that file number. store_id = "S01" is the store's ID card — it comes from Kiosko's source system, and Kiosko controls its rules. store_key = 1 is the file number this dimensional model assigns it, with a single responsibility: identifying that dimension row, without depending on any external rule.

Worked example: adding store_key and product_key

Start from dim_store and dim_product exactly as module 1 left them — with a natural key only — and add the surrogate key with ROW_NUMBER() OVER (ORDER BY ...), the window function you already used in module 1 to declare the grain, now for a different purpose: generating a deterministic sequential integer, always ordered the same way.

# surrogate_keys.py
from datetime import datetime

import duckdb

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

orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)

con = duckdb.connect()
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],
)

con.execute("CREATE TABLE dim_store_natural (store_id VARCHAR, store_name VARCHAR, city VARCHAR)")
con.executemany(
    "INSERT INTO dim_store_natural VALUES (?, ?, ?)",
    [(s["store_id"], s["store_name"], s["city"]) for s in DIM_STORE],
)

con.execute("CREATE TABLE dim_product_natural (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany(
    "INSERT INTO dim_product_natural VALUES (?, ?, ?, ?)",
    [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)

print("=== dim_store, natural key only (inherited from foundations/M1) ===")
print(con.sql("SELECT * FROM dim_store_natural ORDER BY store_id"))

print("=== Adding the surrogate key: store_key ===")
con.execute("""
    CREATE TABLE dim_store AS
    SELECT
        ROW_NUMBER() OVER (ORDER BY store_id) AS store_key,
        store_id,
        store_name,
        city
    FROM dim_store_natural
""")
print(con.sql("SELECT * FROM dim_store ORDER BY store_key"))

print("=== Adding the surrogate key: product_key ===")
con.execute("""
    CREATE TABLE dim_product AS
    SELECT
        ROW_NUMBER() OVER (ORDER BY product_id) AS product_key,
        product_id,
        product_name,
        category,
        unit_cost
    FROM dim_product_natural
""")
print(con.sql("SELECT * FROM dim_product ORDER BY product_key"))

What to expect. Running python3 surrogate_keys.py, the output is exactly this:

=== dim_store, natural key only (inherited from foundations/M1) ===
┌──────────┬───────────────┬──────────┐
│ store_id │  store_name   │   city   │
│ varchar  │    varchar    │ varchar  │
├──────────┼───────────────┼──────────┤
│ S01      │ Kiosko Centro │ Bogota   │
│ S02      │ Kiosko Norte  │ Lima     │
│ S03      │ Kiosko Sur    │ Santiago │
└──────────┴───────────────┴──────────┘

=== Adding the surrogate key: store_key ===
┌───────────┬──────────┬───────────────┬──────────┐
│ store_key │ store_id │  store_name   │   city   │
│   int64   │ varchar  │    varchar    │ varchar  │
├───────────┼──────────┼───────────────┼──────────┤
│         1 │ S01      │ Kiosko Centro │ Bogota   │
│         2 │ S02      │ Kiosko Norte  │ Lima     │
│         3 │ S03      │ Kiosko Sur    │ Santiago │
└───────────┴──────────┴───────────────┴──────────┘

=== Adding the surrogate key: product_key ===
┌─────────────┬────────────┬───────────────────────┬─────────────┬───────────┐
│ product_key │ product_id │     product_name      │  category   │ unit_cost │
│    int64    │  varchar   │        varchar        │   varchar   │  double   │
├─────────────┼────────────┼───────────────────────┼─────────────┼───────────┤
│           1 │ P001       │ Bottled Water 600ml   │ beverages   │       0.4 │
│           2 │ P002       │ Energy Bar            │ snacks      │       0.6 │
│           3 │ P003       │ Instant Coffee Sachet │ beverages   │      0.35 │
│           4 │ P004       │ Phone Charger Cable   │ electronics │       2.1 │
└─────────────┴────────────┴───────────────────────┴─────────────┴───────────┘

Notice two deliberate things about this query. First, ROW_NUMBER() OVER (ORDER BY store_id) — the surrogate key gets generated by explicitly ordering by the natural key, not by insertion order or any other implicit criterion. This guarantees that, if you run this exact same script tomorrow, store_key = 1 will keep being S01, always — deterministic, byte for byte, exactly the reproducibility discipline this guide demands in every executable block. Second, the natural key doesn't disappear — dim_store keeps store_id as a column, alongside store_key — the surrogate key gets added, it never replaces the natural one inside the dimension itself.

Diagram: what changes and what doesn't

flowchart LR
    subgraph Antes["dim_store, module 1"]
        A["store_id (natural)\nPK: store_id\nS01, S02, S03"]
    end

    subgraph Despues["dim_store, this lesson"]
        B["store_key (surrogate, INTEGER)\nstore_id (natural, kept)\nPK: store_key\n1, 2, 3"]
    end

    Antes -->|"store_key is added\nwithout deleting store_id"| Despues

Going deeper: why add the surrogate key now, if you don't need it today

It's a legitimate question: today, with dim_store and dim_product completely static — no name change, no category change during Kiosko's entire week — joining fact_orders by store_id (natural key) works with no problem at all. So why the effort of adding store_key now?

The answer has to do with what module 4 is going to build: SCD type 2, the technique that historizes a dimension that changes, preserving each earlier version as a separate row. Once dim_product starts versioning its rows — for example, P004 with one row for its price before a change and another row for after — product_id stops identifying a single row of the dimension: P004 is going to show up twice, once per historical version. At that point, any JOIN that depended only on product_id would become ambiguous — which of the two versions do you mean? — and you'd need a way to point, unambiguously, to the exact version that applies. That's precisely the surrogate key's job: product_key is going to identify a specific version of a product, while product_id keeps identifying the product in general, across all its versions.

Today, with a single version of each store and each product, store_key and product_key feel almost redundant next to the natural key — in fact, right now, store_key = 1 always corresponds to store_id = "S01", with no ambiguity at all. But adding the surrogate key now, before you need it, is exactly the same discipline you already saw in module 1 with the grain: declare the correct structure from the start, so it survives a future change without anyone having to redesign anything.

A practical note about generation: this lesson uses ROW_NUMBER() OVER (ORDER BY ...), a simple sequential integer, instead of a random identifier like a UUID. The reason is twofold: first, this guide forbids any source of non-determinism in code that feeds a "What to expect" — a randomly generated UUID would produce a different output every time you ran the script, breaking reproducibility; second, a sequential integer is, in practice, the most common choice in real warehouses for surrogate keys of moderately sized dimensions, because it takes up less space and is faster to compare in a JOIN than a 128-bit UUID.

Common mistakes

Deleting the natural key when adding the surrogate one. What happens: someone, while creating dim_store with store_key, decides store_id is no longer needed — "why keep both" — and excludes it from the new table. Why it happens: it feels redundant to keep two identifiers for the same row, and it seems like a reasonable space saving. How to spot it: if your final dim_store has no column connecting back to Kiosko's source system (store_id), you lost the ability to trace any row back to its origin — a real debugging problem the day something doesn't add up. How to fix it: the surrogate key gets added, the natural one stays — always, in every dimension in this guide. store_key identifies the row within the model; store_id connects it to Kiosko's real world.

Generating the surrogate key with a non-deterministic function. What happens: someone uses uuid() or any random generator to create the surrogate key, instead of an explicitly ordered ROW_NUMBER(). Why it happens: a UUID feels "more robust" or "more professional" because it's what many real production systems use, especially when several sources load data in parallel. How to spot it: if you run your key-generation script twice and store_key changes value between runs, your process isn't reproducible — any report, test, or documentation referencing a specific store_key would stop making sense on the next run. How to fix it: in this guide, any surrogate key gets generated with ROW_NUMBER() OVER (ORDER BY <natural key>) — deterministic, reproducible byte for byte. In a production warehouse with multiple concurrent sources, a database-managed sequence generator (not random) fills the same role without sacrificing reproducibility within a single load.

Assuming the surrogate key should already be used to join fact_orders. What happens: someone, seeing the newly created store_key, tries to rewrite fact_orders to use store_key instead of store_id, modifying the table module 1 left closed. Why it happens: it seems "more correct" or "more complete" to use the surrogate key end to end, now that it exists. How to spot it: if your fact_orders no longer has the original store_id/product_id columns from module 1, you altered a contract this guide deliberately keeps fixed. How to fix it: fact_orders keeps its natural keys unchanged — lesson 7's JOIN is going to join fact_orders.store_id against dim_store.store_id (both natural keys), and using store_key on the fact side is a more advanced ETL decision this guide names but doesn't implement in this module, precisely to keep fact_orders stable while you learn the rest of the process.

Exercises

Exercise 1 — Generate a surrogate key for a hypothetical dimension. Kiosko decides to create dim_category with the three current product categories (beverages, snacks, electronics), as an independent table — the same kind of table module 3 is going to actually build when normalizing dim_product. Write the SQL that would add it a surrogate key category_key, following this lesson's exact same pattern.

con.execute("CREATE TABLE dim_category_natural (category VARCHAR)")
con.executemany("INSERT INTO dim_category_natural VALUES (?)",
                 [("beverages",), ("snacks",), ("electronics",)])
print(con.sql("""
    SELECT ROW_NUMBER() OVER (ORDER BY category) AS category_key, category
    FROM dim_category_natural
    ORDER BY category_key
"""))

┌──────────────┬─────────────┐
│ category_key │  category   │
│    int64     │   varchar   │
├──────────────┼─────────────┤
│            1 │ beverages   │
│            2 │ electronics │
│            3 │ snacks      │
└──────────────┴─────────────┘

Exercise 2 — Verify there are no gaps or repeats in the store_key sequence. Using dim_store already built in the worked example, write a query that confirms store_key runs exactly from 1 to 3, with no gaps and no repeated value — a basic integrity check on any freshly generated surrogate key.

print(con.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT store_key) AS distinct_keys,
        MIN(store_key) AS min_key,
        MAX(store_key) AS max_key
    FROM dim_store
"""))

┌────────────┬───────────────┬─────────┬─────────┐
│ total_rows │ distinct_keys │ min_key │ max_key │
│   int64    │     int64     │  int64  │  int64  │
├────────────┼───────────────┼─────────┼─────────┤
│          3 │             3 │       1 │       3 │
└────────────┴───────────────┴─────────┴─────────┘

Exercise 3 — Explain, in your own words, what would happen if Kiosko renamed a store. Suppose Kiosko decides to rename "Kiosko Norte" (Lima) to "Kiosko Lima Norte," keeping the same store_id = "S02". In 2-3 sentences, explain what would happen to store_key, and why that demonstrates the surrogate key's usefulness even in such a simple change.

Summary and next step

In this lesson you added the star schema's first structural piece: store_key and product_key, surrogate keys generated deterministically with ROW_NUMBER() OVER (ORDER BY ...), always keeping the original natural key (store_id, product_id) alongside the new one. The file-number analogy — the number an institution assigns even though an identity document already exists — explains why this separation matters: the surrogate key identifies the row within the model; the natural one connects it to the source system.

Before moving on you should be able to: explain, in your own words, the difference between a natural key and a surrogate key; write from memory the pattern ROW_NUMBER() OVER (ORDER BY <natural key>) to generate a deterministic surrogate key; and anticipate, without looking at module 4, why a surrogate key becomes indispensable — not just convenient — the day a dimension starts versioning its rows.

Lesson 4 builds the dimension that's still completely missing: dim_date, the only dimension in this guide whose surrogate key, for a concrete reason you're going to see there, deliberately breaks the "no business meaning" rule you just learned.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the source that defines surrogate keys as a central part of the dimensional vocabulary used in this lesson. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
• Microsoft Learn — "Understand star schema and the importance for Power BI" — "Surrogate keys" section, with the practical definition of why a dimension needs its own identifier, independent of the source. learn.microsoft.com/en-us/power-bi/guidance/star-schema. In English.
• DuckDB — window function documentation (ROW_NUMBER), the same function used in module 1 to declare the grain and in this lesson to generate deterministic surrogate keys. duckdb.org/docs/current/sql/functions/window_functions. In English.