Construyendo el star con dim_product historizada

Descripción

Con fact_orders reconstruido desde bronze y silver en la lección anterior, esta lección construye el star completo del warehouse de Kiosko —pero con una diferencia decisiva frente al star del módulo 2: la dimensión de producto no es la versión estática (dim_product), es dim_product_scd, historizada con dos corridas reales de MERGE INTO, exactamente como la dejó el módulo 4. Y no basta con tenerla historizada — hay que unirla bien. Esta lección construye el star (dim_store, dim_date, dim_product_scd) y, en el mismo flujo, demuestra —con los mismos números del módulo 5— por qué el join contra una dimensión historizada exige el patrón punto-en-el-tiempo, no un JOIN ingenuo por is_current.

Conexión con el módulo. Esta lección integra, por primera vez en un solo script, tres piezas que hasta ahora vivieron en módulos separados: el star schema del módulo 2, la historización SCD-2 del módulo 4, y el join punto-en-el-tiempo del módulo 5. El resultado —dim_product_scd con cinco filas y el join correcto ya demostrado— es el material que las lecciones 5 y 6 de este módulo dan por sentado sin volver a construirlo.

Una analogía: el pasaporte con historial de páginas, no una sola foto vigente

Piensa en la diferencia entre un carné de identidad que solo muestra la foto y los datos actuales de una persona, y un pasaporte con años de sellos: cada página vieja sigue ahí, con su fecha exacta, aunque la persona ya no viva en esa dirección ni tenga ese aspecto. Si un oficial de migración necesita saber dónde vivía esa persona en una fecha específica del pasado, el carné actual no le sirve de nada —solo tiene el presente—; necesita el pasaporte completo, y necesita buscar la página correcta según la fecha, no asumir que la última página siempre fue la vigente.

dim_product (del módulo 2) es el carné: siempre muestra el presente. dim_product_scd (de esta lección) es el pasaporte: tiene ambas páginas de P002 —la de antes del 15 de agosto, la de después—, y el trabajo de esta lección es aprender a "buscar la página correcta según la fecha de la venta", no quedarse con la última página por reflejo.

Ejemplo trabajado: el star completo, con la dimensión historizada unida en el tiempo

Parte 1 — dim_store, dim_date, y dim_product_scd historizada con MERGE INTO x2

Este script continúa directamente sobre la misma conexión con y el mismo fact_orders que dejó la lección 3 —no abre una conexión nueva ni reconstruye bronze otra vez—.

# capstone_star.py -- Parte 1: el star con dim_product_scd historizada (continua sobre con, con fact_orders ya reconstruido)
from datetime import date, timedelta

DAY_NAMES = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]


def generate_date_dim(start_date: str, end_date: str) -> list[dict]:
    start = date.fromisoformat(start_date)
    end = date.fromisoformat(end_date)
    rows = []
    current = start
    while current <= end:
        weekday_index = current.weekday()
        rows.append({
            "date_key": int(current.strftime("%Y%m%d")), "calendar_date": current,
            "day_of_week": DAY_NAMES[weekday_index], "month": current.month,
            "quarter": (current.month - 1) // 3 + 1, "year": current.year,
            "is_weekend": weekday_index >= 5,
        })
        current += timedelta(days=1)
    return rows


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_store AS SELECT ROW_NUMBER() OVER (ORDER BY store_id) AS store_key, store_id, store_name, city FROM dim_store_natural")

dim_date_rows = generate_date_dim("2026-08-01", "2026-08-31")
con.execute("""
    CREATE TABLE dim_date (
        date_key INTEGER, calendar_date DATE, day_of_week VARCHAR,
        month INTEGER, quarter INTEGER, year INTEGER, is_weekend BOOLEAN
    )
""")
con.executemany("INSERT INTO dim_date VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["date_key"], r["calendar_date"], r["day_of_week"], r["month"],
      r["quarter"], r["year"], r["is_weekend"]) for r in dim_date_rows])

# dim_product_scd: historizada con MERGE INTO, corrido dos veces (modulo 4)
con.execute("CREATE SEQUENCE product_key_seq START 1")
con.execute("""
    CREATE TABLE dim_product_scd (
        product_key  INTEGER PRIMARY KEY,
        product_id   VARCHAR NOT NULL,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE,
        valid_from   DATE NOT NULL,
        valid_to     DATE,
        is_current   BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany(
    "INSERT INTO dim_product_scd VALUES (nextval('product_key_seq'), ?, ?, ?, ?, DATE '2026-08-01', NULL, true)",
    [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)

PRODUCTS_V1 = [
    ("P001", "Bottled Water 600ml", "beverages", 0.40), ("P002", "Energy Bar", "snacks", 0.60),
    ("P003", "Instant Coffee Sachet", "beverages", 0.35), ("P004", "Phone Charger Cable", "electronics", 2.10),
]
PRODUCTS_V2 = [
    ("P001", "Bottled Water 600ml", "beverages", 0.40), ("P002", "Energy Bar", "health-snacks", 0.68),
    ("P003", "Instant Coffee Sachet", "beverages", 0.35), ("P004", "Phone Charger Cable", "electronics", 2.10),
]


def load_staging(products):
    con.execute("DROP TABLE IF EXISTS staging_product")
    con.execute("CREATE TABLE staging_product (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
    con.executemany("INSERT INTO staging_product VALUES (?, ?, ?, ?)", products)


def merge_scd(change_date):
    result = con.sql(f"""
        MERGE INTO dim_product_scd AS target
        USING staging_product AS source
        ON target.product_id = source.product_id AND target.is_current = true
        WHEN MATCHED AND (
            target.unit_cost <> source.unit_cost OR target.category <> source.category
        ) THEN UPDATE SET valid_to = DATE '{change_date}' - INTERVAL 1 DAY, is_current = false
        RETURNING merge_action, product_id
    """)
    changed_ids = [row[1] for row in result.fetchall()]
    if changed_ids:
        placeholders = ", ".join("?" for _ in changed_ids)
        con.execute(f"""
            INSERT INTO dim_product_scd (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current)
            SELECT nextval('product_key_seq'), source.product_id, source.product_name, source.category, source.unit_cost,
                   DATE '{change_date}', NULL, true
            FROM staging_product AS source WHERE source.product_id IN ({placeholders})
        """, changed_ids)
    return len(changed_ids)


load_staging(PRODUCTS_V1)
rows_changed_1 = merge_scd("2026-08-15")
load_staging(PRODUCTS_V2)
rows_changed_2 = merge_scd("2026-08-15")

dim_product_scd_count = con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0]
p002_versions = con.sql("""
    SELECT COUNT(*), SUM(CASE WHEN is_current THEN 1 ELSE 0 END)
    FROM dim_product_scd WHERE product_id = 'P002'
""").fetchone()

star_query = """
    SELECT f.order_id, f.store_id, f.product_id, s.store_key, s.store_name,
           d.date_key, d.calendar_date, f.quantity, f.unit_price, f.revenue
    FROM fact_orders f
    JOIN dim_store s ON f.store_id = s.store_id
    JOIN dim_date  d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
"""
con.execute(f"CREATE TABLE fact_orders_star AS {star_query}")
star_rows = con.sql("SELECT COUNT(*) FROM fact_orders_star").fetchone()[0]

print("Parte 1 -- STAR: dim_store, dim_date, dim_product_scd historizada")
print(f"  dim_store         {con.sql('SELECT COUNT(*) FROM dim_store').fetchone()[0]:3} filas")
print(f"  dim_date          {con.sql('SELECT COUNT(*) FROM dim_date').fetchone()[0]:3} filas")
print(f"  dim_product_scd   {dim_product_scd_count:3} filas (MERGE #1 cerro {rows_changed_1}, MERGE #2 cerro {rows_changed_2})")
print(f"  P002: {p002_versions[0]} versiones, {p002_versions[1]} vigente(s)")
print(f"  fact_orders_star  {star_rows:3} filas (JOIN simple: dim_store + dim_date)")
assert dim_product_scd_count == 5 and p002_versions == (2, 1)
assert star_rows == 40

Qué esperar.

Parte 1 -- STAR: dim_store, dim_date, dim_product_scd historizada
  dim_store           3 filas
  dim_date           31 filas
  dim_product_scd     5 filas (MERGE #1 cerro 0, MERGE #2 cerro 1)
  P002: 2 versiones, 1 vigente(s)
  fact_orders_star   40 filas (JOIN simple: dim_store + dim_date)

Fíjate en algo deliberado de esta parte: fact_orders_star se construyó uniendo solo contra dim_store y dim_date —las dos dimensiones que no cambian con el tiempo—, dejando dim_product_scd fuera de este JOIN simple. No fue un descuido — dim_product_scd necesita un tipo de JOIN distinto, y unirla aquí con la misma sintaxis simple habría escondido, sin querer, exactamente el error que la Parte 2 va a exponer.

Parte 2 — El join correcto: revenue histórico, sin corromper la categoría

broken = con.sql("""
    SELECT d.category, COUNT(*) AS orders, ROUND(SUM(f.revenue), 2) AS revenue,
           ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
    GROUP BY d.category ORDER BY d.category
""").fetchall()
correct = con.sql("""
    SELECT d.category, COUNT(*) AS orders, ROUND(SUM(f.revenue), 2) AS revenue,
           ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    GROUP BY d.category ORDER BY d.category
""").fetchall()

total_broken = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
""").fetchone()[0]
total_correct = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
""").fetchone()[0]

print("\nParte 2 -- JOIN roto (is_current) vs JOIN correcto (punto-en-el-tiempo)")
print("  ROTO (is_current = true):")
for category, orders_count, revenue, margin in broken:
    print(f"    {category:14} {orders_count:2} ordenes  revenue={revenue:7}  margin={margin:6}")
print("  CORRECTO (BETWEEN valid_from AND valid_to):")
for category, orders_count, revenue, margin in correct:
    print(f"    {category:14} {orders_count:2} ordenes  revenue={revenue:7}  margin={margin:6}")
print(f"  Revenue total: identico en ambos casos -> {total_broken} == {total_correct}")

broken_categories = {c for c, *_ in broken}
correct_categories = {c for c, *_ in correct}
assert "health-snacks" in broken_categories and "health-snacks" not in correct_categories
assert "snacks" in correct_categories and "snacks" not in broken_categories
assert total_broken == total_correct == 106.15
print("  Verificacion OK: revenue historico correcto -- 'snacks', no 'health-snacks' (categoria del futuro)")

Qué esperar.

Parte 2 -- JOIN roto (is_current) vs JOIN correcto (punto-en-el-tiempo)
  ROTO (is_current = true):
    beverages      23 ordenes  revenue=  44.05  margin= 14.75
    electronics     7 ordenes  revenue=   40.5  margin=  21.6
    health-snacks  10 ordenes  revenue=   21.6  margin=  9.36
  CORRECTO (BETWEEN valid_from AND valid_to):
    beverages      23 ordenes  revenue=  44.05  margin= 14.75
    electronics     7 ordenes  revenue=   40.5  margin=  21.6
    snacks         10 ordenes  revenue=   21.6  margin=  10.8
  Revenue total: identico en ambos casos -> 106.15 == 106.15
  Verificacion OK: revenue historico correcto -- 'snacks', no 'health-snacks' (categoria del futuro)

Detente en esta salida, porque es el corazón de todo el módulo. Las cuarenta órdenes de Kiosko ocurrieron entre el 3 y el 9 de agosto de 2026 —antes del cambio de P002, que ocurrió el 15—. El JOIN roto, al unir solo por is_current = true, le asigna a esas ventas la categoría vigente hoy (health-snacks), como si el cambio de nombre y precio ya hubiera ocurrido cuando esas ventas pasaron — una categoría que, en la fecha real de esas ventas, ni siquiera existía todavía. El JOIN correcto, con BETWEEN valid_from AND valid_to, le asigna la categoría que de verdad tenía P002 el día que se vendió: snacks. El revenue —21.6— nunca cambia, porque es una medida que vive en fact_orders, no en la dimensión; lo que cambia es la categoría y el margen (9.36 roto vs 10.8 correcto), porque unit_cost sí vive en la dimensión historizada.

Diagrama: por qué dim_product_scd necesita un JOIN distinto

flowchart TD
    subgraph Simple["dim_store, dim_date -- no cambian con el tiempo"]
        A["JOIN f.store_id = s.store_id\nJOIN date_key = d.date_key"]
    end
    subgraph Historizada["dim_product_scd -- SI cambia con el tiempo"]
        B["JOIN roto: ON product_id = product_id\nAND is_current = true\n-> usa la version de HOY"]
        C["JOIN correcto: ON product_id = product_id\nAND order_ts BETWEEN valid_from AND valid_to\n-> usa la version VIGENTE EL DIA DE LA VENTA"]
    end
    A --> D["fact_orders_star\n40 filas, sin fan-out"]
    B --> E["health-snacks, margin 9.36\nROTO -- categoria del futuro"]
    C --> F["snacks, margin 10.8\nCORRECTO -- categoria real de esa fecha"]

Profundización: por qué este error es silencioso, no ruidoso

Vale la pena insistir en algo que ya advirtió el módulo 5, porque este módulo lo confirma con el warehouse completo integrado: el JOIN roto nunca produce un error. No lanza ninguna excepción, no imprime ningún warning, ni siquiera cambia el revenue total —106.15 en ambos casos, idéntico—. Si alguien del equipo de BI solo revisara el número de revenue total antes de confiar en un reporte, este error pasaría completamente desapercibido, porque la única señal de que algo está mal está en una columna que nadie suele auditar con la misma atención que el total: la categoría.

Esta es, con precisión, la razón por la que el módulo 5 dedicó una lección entera a demostrar esto con números reales, y por la que este módulo lo repite aquí, integrado en el star completo: un error de modelado que no rompe ningún número visible es, en la práctica, más peligroso que uno que sí lo hace — porque nadie lo va a notar hasta que alguien, mucho después, se pregunte por qué el revenue histórico de "snacks" parece más bajo de lo que debería ser.

Errores comunes

Unir dim_product_scd con la misma sintaxis simple que dim_store y dim_date. Qué pasa: alguien, acostumbrado al patrón de la Parte 1 (JOIN dim_store s ON f.store_id = s.store_id), aplica exactamente la misma forma a dim_product_scd —JOIN dim_product_scd d ON f.product_id = d.product_id—, sin ningún filtro adicional. Por qué pasa: es la forma más corta de escribir un JOIN, y funciona sin error para dim_store y dim_date porque esas dos dimensiones nunca tienen más de una fila por llave natural. Cómo detectarlo: si tu consulta produce más de 40 filas al unir fact_orders con dim_product_scd sin ningún filtro, tienes un fan-out —cada venta de P002 se está multiplicando por sus dos versiones—, exactamente el error que el módulo 5 (lección 2) ya demostró con el número 50. Cómo corregirlo: cualquier dimensión historizada necesita, como mínimo, AND d.is_current = true para evitar el fan-out —y, como demostró esta lección, ese filtro por sí solo todavía no es suficiente para un reporte histórico correcto.

Construir la OBT o cualquier tabla derivada antes de que las dos corridas de MERGE hayan terminado. Qué pasa: alguien, al escribir su propio script de integración, construye fact_orders_star o cualquier reporte que use dim_product_scd entre el MERGE #1 y el MERGE #2, antes de que la historia esté completa. Por qué pasa: en el código de esta lección, los dos MERGE y las consultas de reporte están cerca en el script, y es fácil mover una línea sin notar que rompe el orden. Cómo detectarlo: si tu reporte muestra P002 con una sola versión (snacks, sin health-snacks en ningún lado, ni siquiera con el JOIN roto), revisa si construiste el reporte antes del MERGE #2. Cómo corregirlo: los dos MERGE de la Parte 1 de esta lección tienen que completarse antes de cualquier consulta de reporte de la Parte 2 — el mismo orden de dependencias que advirtió la lección 1 de este módulo.

Asumir que el margen roto (9.36) es "un error pequeño" porque la diferencia con el correcto (10.8) parece chica. Qué pasa: alguien, al ver que la diferencia entre 9.36 y 10.8 es de apenas 1.44, concluye que el error del JOIN roto no tiene consecuencias prácticas reales. Por qué pasa: en términos absolutos, sobre cuarenta órdenes de una semana, 1.44 parece un número pequeño para preocuparse. Cómo detectarlo: si tu conclusión es "no importa, es poca plata", perdiste de vista la escala — este dataset es de juguete a propósito (el diseño de esta guía lo advierte desde el módulo 1); el mismo error, sobre el catálogo completo de un retailer real con millones de órdenes y docenas de productos que cambian de categoría cada trimestre, no sería 1.44 — sería una fracción de margen mal atribuida, de forma sistemática, a través de todo el historial. Cómo corregirlo: la magnitud del número en el dataset de Kiosko no es el punto — el punto es que el patrón del error (unir por is_current en vez de punto-en-el-tiempo) es el mismo, sin importar la escala, y a mayor escala el costo de no corregirlo crece proporcionalmente.

Ejercicios

Ejercicio 1 — Confirma que P001, P003 y P004 dan el mismo resultado en ambos JOIN. Usando las dos consultas de la Parte 2, confirma que las categorías beverages y electronics —que nunca cambiaron— dan exactamente el mismo revenue y margen en el JOIN roto y en el correcto.

Ejercicio 2 — Calcula cuántas de las diez órdenes de P002 ocurrieron antes y después del cambio. Usando fact_orders, cuenta cuántas de las diez órdenes de P002 tienen order_ts antes del 2026-08-15 y cuántas después, y explica por qué el resultado confirma que el JOIN correcto de esta lección era predecible de antemano.

print(con.sql("""
    SELECT
        CASE WHEN order_ts < '2026-08-15' THEN 'antes del cambio' ELSE 'despues del cambio' END AS periodo,
        COUNT(*) AS ordenes
    FROM fact_orders WHERE product_id = 'P002' GROUP BY periodo
"""))

┌───────────────────┬─────────┐
│      periodo       │ ordenes │
│       varchar       │  int64  │
├───────────────────┼─────────┤
│ antes del cambio   │      10 │
└───────────────────┴─────────┘

Ejercicio 3 — Explica, de memoria, por qué fact_orders_star (Parte 1) no incluye dim_product_scd, aunque el título de esta lección diga "el star con dim_product historizada". En 2-3 frases, resuelve esta aparente contradicción.

Resumen y siguiente paso

En esta lección construiste el star completo del warehouse de Kiosko: dim_store y dim_date, unidas con el patrón simple de siempre, y dim_product_scd, historizada con dos corridas reales de MERGE INTO —P002 con dos versiones, snacks/0.60 antes del 15 de agosto, health-snacks/0.68 después—. Demostraste, con los mismos números del módulo 5 ahora integrados en este warehouse, que el JOIN correcto contra una dimensión historizada exige BETWEEN valid_from AND valid_to, no is_current = true: el revenue nunca cambia (106.15 en ambos casos), pero la categoría sí (snacks correcto, health-snacks roto), y el margen también (10.8 correcto, 9.36 roto).

Antes de avanzar deberías poder: explicar por qué dim_product_scd necesita un JOIN distinto al de dim_store/dim_date; recitar de memoria los dos números de margen (roto vs correcto) para P002; y explicar por qué el revenue total nunca es suficiente evidencia de que un JOIN histórico está bien escrito.

La lección 5 agrega dos capas más al warehouse: fact_sessions, el accumulating snapshot del funnel de sesiones, y fact_store_activity, el cumulative table design de la actividad diaria por tienda — las dos piezas del módulo 6 que ningún hecho transaccional, ni siquiera el star recién completado en esta lección, puede resolver.

Recursos

• Kimball Group — "Slowly Changing Dimension Type 2" — la definición formal que sostiene dim_product_scd, integrada en esta lección dentro del star completo. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. En inglés.
• DuckDB — guía oficial "Merge Statement for SCD Type 2" — el patrón exacto de MERGE INTO que esta lección corre dos veces sobre dim_product_scd. duckdb.org/docs/current/guides/sql_features/merge. En inglés.
• DuckDB — documentación oficial del statement MERGE INTO. duckdb.org/docs/lts/sql/statements/merge_into. En inglés.
• Kimball Group — "Late Arriving Dimension" — la técnica que sostiene, en espíritu, por qué el join punto-en-el-tiempo es la forma correcta de conectar hechos con dimensiones que cambian. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-dimension. En inglés.
• DuckDB — documentación oficial del cliente Python, la interfaz que ejecuta cada consulta de esta lección. duckdb.org/docs/current/clients/python/overview. En inglés.