Llaves sustitutas vs llaves naturales

Descripción

Hasta ahora, dim_store y dim_product se identifican por su llave natural: store_id (S01, S02, S03) y product_id (P001...P004), los mismos identificadores que ya venían del sistema de origen de Kiosko. Esta lección agrega, a cada dimensión, una llave sustituta: un número entero, generado por el propio modelo dimensional, sin ningún significado de negocio, cuyo único trabajo es identificar de forma única cada fila de la dimensión — store_key, product_key.

Conexión con el módulo. Esta es la primera pieza ejecutable del módulo: vas a modificar dim_store y dim_product —agregándoles una columna nueva, generada de forma determinista, no aleatoria— y vas a dejar sentado el patrón de llave que la lección 4 (dim_date) y la lección 7 (el star ensamblado) dan por sentado.

Una analogía: el expediente, aunque ya tengas cédula

Cuando alguien abre una cuenta en un banco, un consultorio médico o cualquier institución que necesite llevar un historial, casi siempre pasa lo mismo: la persona ya tiene un documento de identidad —una cédula, un DNI, un pasaporte—, un identificador que el estado le asignó y que, en teoría, ya la identifica de forma única. Y sin embargo, la institución le asigna otro número: un número de cliente, un número de expediente, un número de historia clínica — propio de esa institución, sin ningún significado fuera de ella.

¿Por qué molestarse en crear un segundo identificador si la persona ya tiene uno? Porque el documento de identidad tiene reglas que la institución no controla: alguien puede cambiar de nombre legal, un documento puede vencer y renovarse con un número distinto, dos personas de países distintos pueden, en teoría, compartir la misma secuencia de números bajo sistemas de identificación diferentes. El número de expediente, en cambio, es completamente interno: la institución lo genera, lo controla, y puede garantizar —porque depende solo de sí misma— que nunca se repite y que nunca cambia, pase lo que pase con el documento de identidad original.

Una llave sustituta es exactamente ese número de expediente. store_id = "S01" es la cédula de la tienda —viene del sistema de origen de Kiosko, y Kiosko controla sus reglas—. store_key = 1 es el expediente que este modelo dimensional le asigna, con una sola responsabilidad: identificar esa fila de la dimensión, sin depender de ninguna regla externa.

Ejemplo trabajado: agregando store_key y product_key

Parte de dim_store y dim_product tal como los dejó el módulo 1 —con llave natural únicamente— y agrega la llave sustituta con ROW_NUMBER() OVER (ORDER BY ...), la función de ventana que ya usaste en el módulo 1 para declarar el grano, ahora con un propósito distinto: generar un entero secuencial, determinista, ordenado siempre de la misma forma.

# 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, solo con llave natural (heredado de foundations/M1) ===")
print(con.sql("SELECT * FROM dim_store_natural ORDER BY store_id"))

print("=== Agregando la llave sustituta: 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("=== Agregando la llave sustituta: 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"))

Qué esperar. Al correr python3 surrogate_keys.py, la salida es exactamente esta:

=== dim_store, solo con llave natural (heredado de foundations/M1) ===
┌──────────┬───────────────┬──────────┐
│ store_id │  store_name   │   city   │
│ varchar  │    varchar    │ varchar  │
├──────────┼───────────────┼──────────┤
│ S01      │ Kiosko Centro │ Bogota   │
│ S02      │ Kiosko Norte  │ Lima     │
│ S03      │ Kiosko Sur    │ Santiago │
└──────────┴───────────────┴──────────┘

=== Agregando la llave sustituta: 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 │
└───────────┴──────────┴───────────────┴──────────┘

=== Agregando la llave sustituta: 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 │
└─────────────┴────────────┴───────────────────────┴─────────────┴───────────┘

Fíjate en dos cosas deliberadas de esta consulta. Primero, ROW_NUMBER() OVER (ORDER BY store_id) — la llave sustituta se genera ordenando explícitamente por la llave natural, no por el orden de inserción o cualquier otro criterio implícito. Esto garantiza que, si vuelves a correr este mismo script mañana, store_key = 1 va a seguir siendo S01, siempre — determinista, byte a byte, exactamente la disciplina de reproducibilidad que esta guía exige en cada bloque ejecutable. Segundo, la llave natural no desaparece — dim_store conserva store_id como columna, junto a store_key — la llave sustituta se agrega, nunca reemplaza a la natural dentro de la propia dimensión.

Diagrama: qué cambia y qué no cambia

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

    subgraph Despues["dim_store, esta leccion"]
        B["store_key (sustituta, INTEGER)\nstore_id (natural, se conserva)\nPK: store_key\n1, 2, 3"]
    end

    Antes -->|"se agrega store_key\nsin borrar store_id"| Despues

Profundización: por qué agregar la llave sustituta ahora, si hoy no la necesitas

Es una pregunta legítima: hoy, con dim_store y dim_product completamente estáticas —ningún cambio de nombre, ningún cambio de categoría durante toda la semana de Kiosko—, unir fact_orders por store_id (llave natural) funciona sin ningún problema. Entonces, ¿por qué el esfuerzo de agregar store_key ahora?

La respuesta tiene que ver con lo que el módulo 4 va a construir: SCD tipo 2, la técnica que historiza una dimensión que cambia, preservando cada versión anterior como una fila separada. Cuando dim_product empiece a versionar sus filas —por ejemplo, P004 con una fila para su precio antes de un cambio y otra fila para después—, product_id deja de identificar una única fila de la dimensión: P004 va a aparecer dos veces, una por cada versión histórica. En ese momento, cualquier JOIN que dependiera solo de product_id se volvería ambiguo —¿a cuál de las dos versiones te refieres?—, y necesitarías una forma de apuntar, sin ambigüedad, a la versión exacta que corresponde. Esa es, precisamente, la función de la llave sustituta: product_key va a identificar una versión específica de un producto, mientras que product_id sigue identificando al producto en general, a través de todas sus versiones.

Hoy, con una sola versión de cada tienda y cada producto, store_key y product_key se sienten casi redundantes frente a la llave natural —de hecho, en este momento, store_key = 1 siempre corresponde a store_id = "S01", sin ninguna ambigüedad—. Pero agregar la llave sustituta ahora, antes de necesitarla, es exactamente la misma disciplina que ya viste en el módulo 1 con el grano: declarar la estructura correcta desde el principio, para que sobreviva a un cambio futuro sin que nadie tenga que rediseñar nada.

Una nota práctica sobre la generación: esta lección usa ROW_NUMBER() OVER (ORDER BY ...), un entero secuencial simple, en vez de un identificador aleatorio como un UUID. La razón es doble: primero, esta guía prohíbe cualquier fuente de no determinismo en código que alimente un "Qué esperar" —un UUID generado al azar produciría una salida distinta cada vez que corrieras el script, rompiendo la reproducibilidad—; segundo, un entero secuencial es, en la práctica, la elección más común en warehouses reales para llaves sustitutas de dimensiones de tamaño moderado, porque ocupa menos espacio y es más rápido de comparar en un JOIN que un UUID de 128 bits.

Errores comunes

Eliminar la llave natural al agregar la sustituta. Qué pasa: alguien, al crear dim_store con store_key, decide que store_id ya no hace falta —"para qué guardar las dos"— y la excluye de la tabla nueva. Por qué pasa: se siente redundante mantener dos identificadores para la misma fila, y parece un ahorro de espacio razonable. Cómo detectarlo: si tu dim_store final no tiene ninguna columna que conecte de vuelta con el sistema de origen de Kiosko (store_id), perdiste la capacidad de rastrear cualquier fila hasta su origen — un problema real de depuración el día que algo no cuadre. Cómo corregirlo: la llave sustituta se agrega, la natural se conserva — siempre, en toda dimensión de esta guía. store_key identifica la fila dentro del modelo; store_id la conecta con el mundo real de Kiosko.

Generar la llave sustituta con una función no determinista. Qué pasa: alguien usa uuid() o cualquier generador aleatorio para crear la llave sustituta, en vez de ROW_NUMBER() ordenado explícitamente. Por qué pasa: un UUID se siente "más robusto" o "más profesional" porque es lo que se usa en muchos sistemas de producción reales, especialmente cuando varias fuentes cargan datos en paralelo. Cómo detectarlo: si corres tu script de generación de llaves dos veces y store_key cambia de valor entre una corrida y otra, tu proceso no es reproducible — cualquier reporte, prueba o documentación que referencie un store_key específico dejaría de tener sentido en la siguiente corrida. Cómo corregirlo: en esta guía, cualquier llave sustituta se genera con ROW_NUMBER() OVER (ORDER BY <llave natural>) — determinista, reproducible byte a byte. En un warehouse de producción con múltiples fuentes concurrentes, un generador de secuencia gestionado por la base de datos (no aleatorio) cumple el mismo papel sin sacrificar la reproducibilidad dentro de una misma carga.

Asumir que la llave sustituta debe usarse para unir fact_orders desde ya. Qué pasa: alguien, al ver store_key recién creada, intenta reescribir fact_orders para que use store_key en vez de store_id, modificando la tabla que el módulo 1 dejó cerrada. Por qué pasa: parece "más correcto" o "más completo" usar la llave sustituta de punta a punta, ahora que existe. Cómo detectarlo: si tu fact_orders ya no tiene las columnas store_id/product_id originales del módulo 1, alteraste un contrato que esta guía mantiene fijo a propósito. Cómo corregirlo: fact_orders conserva sus llaves naturales sin cambios — el JOIN de la lección 7 va a unir fact_orders.store_id contra dim_store.store_id (ambas llaves naturales), y usar store_key en el lado del hecho es una decisión de ETL más avanzada que esta guía nombra pero no implementa en este módulo, precisamente para mantener fact_orders estable mientras aprendes el resto del proceso.

Ejercicios

Ejercicio 1 — Genera una llave sustituta para una dimensión hipotética. Kiosko decide crear dim_category con las tres categorías actuales de producto (beverages, snacks, electronics), como una tabla independiente —el mismo tipo de tabla que el módulo 3 va a construir de verdad al normalizar dim_product—. Escribe el SQL que le agregaría una llave sustituta category_key, siguiendo exactamente el mismo patrón de esta lección.

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      │
└──────────────┴─────────────┘

Ejercicio 2 — Verifica que no hay huecos ni repeticiones en la secuencia de store_key. Usando dim_store ya construida en el ejemplo trabajado, escribe una consulta que confirme que store_key va exactamente de 1 a 3, sin ningún hueco ni valor repetido — una verificación de integridad básica sobre cualquier llave sustituta recién generada.

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 │
└────────────┴───────────────┴─────────┴─────────┘

Ejercicio 3 — Explica, en tus propias palabras, qué pasaría si Kiosko renombrara una tienda. Supón que Kiosko decide renombrar "Kiosko Norte" (Lima) a "Kiosko Lima Norte", manteniendo el mismo store_id = "S02". En 2-3 frases, explica qué le pasaría a store_key y por qué eso demuestra la utilidad de la llave sustituta incluso en un cambio tan simple.

Resumen y siguiente paso

En esta lección agregaste la primera pieza estructural del star schema: store_key y product_key, llaves sustitutas generadas de forma determinista con ROW_NUMBER() OVER (ORDER BY ...), conservando siempre la llave natural original (store_id, product_id) junto a la nueva. La analogía del expediente —el número que una institución asigna aunque ya exista un documento de identidad— explica por qué esta separación importa: la llave sustituta identifica la fila dentro del modelo; la natural la conecta con el sistema de origen.

Antes de avanzar deberías poder: explicar, con tus propias palabras, la diferencia entre llave natural y llave sustituta; escribir de memoria el patrón ROW_NUMBER() OVER (ORDER BY <llave natural>) para generar una llave sustituta determinista; y adelantar, sin mirar el módulo 4, por qué una llave sustituta se vuelve indispensable —no solo conveniente— el día que una dimensión empieza a versionar sus filas.

La lección 4 construye la dimensión que todavía falta por completo: dim_date, la única dimensión de esta guía cuya llave sustituta, por una razón concreta que vas a ver ahí, rompe deliberadamente la regla de "sin significado de negocio" que acabas de aprender.

Recursos

• Kimball Group — "Star Schema / OLAP Cube" — la fuente que define las llaves sustitutas como parte central del vocabulario dimensional usado en esta lección. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. En inglés.
• Microsoft Learn — "Understand star schema and the importance for Power BI" — sección "Surrogate keys", con la definición práctica de por qué una dimensión necesita un identificador propio, independiente del origen. learn.microsoft.com/en-us/power-bi/guidance/star-schema. En inglés.
• DuckDB — documentación de funciones de ventana (ROW_NUMBER), la misma función usada en el módulo 1 para declarar el grano y en esta lección para generar llaves sustitutas deterministas. duckdb.org/docs/current/sql/functions/window_functions. En inglés.