Mini-proyecto: dim_product historizada de Kiosko

Descripción

Este proyecto cierra el módulo integrando las seis piezas anteriores: el problema declarado con evidencia (lección 2), SCD tipo 1 y su pérdida de historia (lección 3), SCD tipo 2 construido a mano (lección 4), SCD tipo 2 automatizado con MERGE INTO (lección 5), la política de columnas mixta (lección 6), y el vocabulario completo de variantes (lección 7). Lo que falta es reunir todo en una sola entrega formal: dim_product_scd construida desde cero, historizada con MERGE INTO corrido dos veces sobre las dos instantáneas reales del catálogo de Kiosko, verificada número por número, y documentada en una estructura —SCD_SUMMARY— que el módulo 5 puede consultar sin repetir la construcción.

El proyecto tiene cinco partes. Primero, construyes dim_product_scd en su estado inicial, con los cuatro productos de Kiosko vigentes desde el 1 de agosto de 2026. Segundo, corres el MERGE #1, con products_v1 —idéntico al estado actual, cero cambios reales—, confirmando que el statement es seguro de repetir. Tercero, corres el MERGE #2, con products_v2 —el cambio real de P002—, historizando la dimensión de verdad. Cuarto, verificas que P002 terminó con exactamente dos versiones, una vigente y una cerrada. Quinto, documentas todo el proceso en SCD_SUMMARY, la estructura formal que cierra el módulo.

Conexión con el módulo. Este proyecto no introduce ningún concepto nuevo — es la integración final de las siete lecciones anteriores, empaquetada como SCD_SUMMARY, la estructura que el módulo 5 de esta guía puede citar sin volver a construir la historización desde cero.

Una analogía: el expediente completo, cerrado y archivado

Cada lección de este módulo trabajó una pieza del expediente por separado: el problema (qué cambió), la técnica que lo pierde (tipo 1), la técnica que lo preserva —primero a mano, después automatizada— (tipo 2), el criterio para decidir caso por caso (por columna), y el vocabulario completo de variantes (tipo 3 y más allá). Este proyecto es el momento de cerrar el expediente: todas esas piezas, ensambladas en un solo flujo, desde la tabla vacía hasta la verificación final, listas para que cualquiera —incluyendo tú mismo, en el módulo 5— pueda confiar en el resultado sin tener que repetir el trabajo.

El material: todo lo que este módulo construyó, en un solo flujo

Necesitas, en la misma carpeta: kiosko.py (idéntico a los módulos 1, 2 y 3, con DIM_PRODUCT). No necesitas ningún archivo adicional — PRODUCTS_V1, PRODUCTS_V2 y la función merge_scd() se definen directamente en el script de este proyecto, igual que en los proyectos anteriores.

La solución de referencia, verificada

Parte 1 — Construir dim_product_scd, estado inicial

# kiosko_scd_project.py -- dim_product historizada de Kiosko, mini-proyecto de cierre del modulo 4
import duckdb

from kiosko import DIM_PRODUCT

print("=== Kiosko: dim_product historizada, entrega final del modulo 4 ===\n")

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

print("Parte 1 -- dim_product_scd, estado inicial")
total_products = con.sql("SELECT COUNT(DISTINCT product_id) FROM dim_product_scd").fetchone()[0]
total_rows_p1 = con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0]
print(f"  dim_product_scd  {total_rows_p1:3} filas, {total_products:3} productos")

Esta primera parte no construye nada nuevo — reconstruye, exactamente como en la lección 4, la tabla historizada vigente desde el 1 de agosto de 2026, antes de cualquier cambio.

Parte 2 — MERGE #1: products_v1, sin cambios reales

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")

print("\nParte 2 -- MERGE #1 (staging_product = products_v1, sin cambios reales)")
print(f"  filas cerradas por el MERGE: {rows_changed_1}")
print(f"  dim_product_scd  {con.sql('SELECT COUNT(*) FROM dim_product_scd').fetchone()[0]:3} filas (sin cambio)")

Exactamente el patrón de la lección 5: staging_product cargada con products_v1, MERGE INTO ejecutado, RETURNING capturado en Python y usado para decidir, con precisión, a qué productos aplicar el INSERT de seguimiento — cero, en esta corrida, porque products_v1 no difiere en nada del estado vigente.

Parte 3 — MERGE #2: products_v2, el cambio real de P002

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

print("\nParte 3 -- MERGE #2 (staging_product = products_v2, P002 cambia category y unit_cost)")
print(f"  filas cerradas por el MERGE: {rows_changed_2}")
print(f"  dim_product_scd  {con.sql('SELECT COUNT(*) FROM dim_product_scd').fetchone()[0]:3} filas")

Exactamente el MERGE #2 de la lección 5: products_v2 trae el cambio real de P002 —category de snacks a health-snacks, unit_cost de 0.60 a 0.68—, la cláusula WHEN MATCHED AND (...) lo detecta, y el INSERT de seguimiento abre la versión nueva.

Parte 4 — Verificar: P002 tiene exactamente 2 versiones, 1 vigente

p002_check = con.sql("""
    SELECT product_id, COUNT(*) AS total_versions,
           SUM(CASE WHEN is_current THEN 1 ELSE 0 END) AS current_versions
    FROM dim_product_scd WHERE product_id = 'P002' GROUP BY product_id
""").fetchone()

print("\nParte 4 -- verificacion: P002 tiene exactamente 2 versiones, 1 vigente")
print(f"  product_id={p002_check[0]}  total_versions={p002_check[1]}  current_versions={p002_check[2]}")
assert p002_check == ("P002", 2, 1), "P002 no quedo historizado correctamente"
print("  Verificacion OK")

print("\n=== dim_product_scd, estado final (4 productos, 5 filas) ===")
print(con.sql("SELECT * FROM dim_product_scd ORDER BY product_id, product_key"))

Esta es la parte que le da confianza a todo el proyecto: sin este assert, no habría evidencia ejecutada de que la historización funcionó — solo la suposición de que funcionó. p002_check == ("P002", 2, 1) es la misma disciplina de verificación con evidencia que el módulo 1 ya exigió al declarar el grano: nunca asumas, siempre confirma con una consulta.

Parte 5 — Documentar como una estructura formal

SCD_SUMMARY = {
    "table": "dim_product_scd",
    "total_products": 4,
    "total_rows": con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0],
    "historized_product_id": "P002",
    "historized_columns": ["category", "unit_cost"],
    "change_date": "2026-08-15",
    "versions_before_change": 1,
    "versions_after_change": 2,
    "merge_runs": 2,
    "rows_changed_by_merge_run": [rows_changed_1, rows_changed_2],
}

print("\nParte 5 -- la declaracion formal: SCD_SUMMARY")
for key, value in SCD_SUMMARY.items():
    print(f"  {key}: {value}")

Qué esperar. Al correr python3 kiosko_scd_project.py completo (las cinco partes juntas), la salida es exactamente esta:

=== Kiosko: dim_product historizada, entrega final del modulo 4 ===

Parte 1 -- dim_product_scd, estado inicial
  dim_product_scd    4 filas,   4 productos

Parte 2 -- MERGE #1 (staging_product = products_v1, sin cambios reales)
  filas cerradas por el MERGE: 0
  dim_product_scd    4 filas (sin cambio)

Parte 3 -- MERGE #2 (staging_product = products_v2, P002 cambia category y unit_cost)
  filas cerradas por el MERGE: 1
  dim_product_scd    5 filas

Parte 4 -- verificacion: P002 tiene exactamente 2 versiones, 1 vigente
  product_id=P002  total_versions=2  current_versions=1
  Verificacion OK

=== dim_product_scd, estado final (4 productos, 5 filas) ===
┌─────────────┬────────────┬───────────────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │     product_name      │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │        varchar        │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼───────────────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           1 │ P001       │ Bottled Water 600ml   │ beverages     │       0.4 │ 2026-08-01 │ NULL       │ true       │
│           2 │ P002       │ Energy Bar            │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ P002       │ Energy Bar            │ health-snacks │      0.68 │ 2026-08-15 │ NULL       │ true       │
│           3 │ P003       │ Instant Coffee Sachet │ beverages     │      0.35 │ 2026-08-01 │ NULL       │ true       │
│           4 │ P004       │ Phone Charger Cable   │ electronics   │       2.1 │ 2026-08-01 │ NULL       │ true       │
└─────────────┴────────────┴───────────────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

Parte 5 -- la declaracion formal: SCD_SUMMARY
  table: dim_product_scd
  total_products: 4
  total_rows: 5
  historized_product_id: P002
  historized_columns: ['category', 'unit_cost']
  change_date: 2026-08-15
  versions_before_change: 1
  versions_after_change: 2
  merge_runs: 2
  rows_changed_by_merge_run: [0, 1]

Detente en la Parte 4 y en la Parte 5 juntas, porque son las que resumen todo el módulo en una sola imagen. p002_check == ("P002", 2, 1) es la confirmación ejecutada de que la historia se preservó correctamente: dos versiones, una sola vigente, nunca cero, nunca dos vigentes a la vez. Y SCD_SUMMARY reúne, en una sola estructura, cada dato que las siete lecciones anteriores midieron por separado: rows_changed_by_merge_run ([0, 1]) viene directamente de las lecciones 2 (sin cambios) y 5 (el cambio real); historized_columns viene de la política de la lección 6; versions_before_change/versions_after_change es, en números, la diferencia completa entre SCD tipo 1 (lección 3) y SCD tipo 2 (lecciones 4 y 5).

Diagrama: las siete piezas del módulo, cerradas con evidencia

flowchart TD
    A["L2: El problema\nVERIFICADO -- P002 cambia category y unit_cost"] --> B
    B["L3: SCD tipo 1\nVERIFICADO -- historia perdida, 1 fila"] --> C
    C["L4: SCD tipo 2 manual\nVERIFICADO -- 2 filas, UPDATE + INSERT"] --> D
    D["L5: MERGE INTO\nVERIFICADO -- 2 corridas, idempotente"] --> E
    E["L6: Tipo 1 vs tipo 2 por columna\nVERIFICADO -- product_name distinto"] --> F
    F["L7: Tipo 3 y variantes\nVERIFICADO en parte -- vocabulario completo"] --> G
    G["SCD_SUMMARY\nel contrato formal que este proyecto entrega"]
    G --> H["Modulo 5: point-in-time join\nusa dim_product_scd tal como quedo aqui"]

Cerrando el checklist de la lección 2 del módulo 1, pieza por pieza

Cuatro filas de las siete del checklist ya quedaron resueltas. El módulo 5, el siguiente en la lista, necesita específicamente dim_product_scd tal como este proyecto la dejó —cinco filas, P002 con dos versiones, rangos de vigencia que no se superponen—, no dim_product (la versión star del módulo 2, todavía sin historia, que sigue existiendo sin cambios) ni dim_product_type1/dim_product_type3 (las tablas de las lecciones 3 y 7, construidas solo para comparar). Con dim_product_scd lista, el módulo 5 puede finalmente hacer la pregunta que ningún módulo anterior pudo hacer: si fact_orders tuviera una orden de P002 fechada después del 15 de agosto, ¿el JOIN la conecta con la versión correcta de la dimensión?

Errores comunes

Entregar SCD_SUMMARY sin el assert de la Parte 4. Qué pasa: alguien, apurado por mostrar la estructura de resumen como resultado final, construye SCD_SUMMARY directamente después de la Parte 3, sin correr primero el assert p002_check == ("P002", 2, 1) de la Parte 4. Por qué pasa: la estructura de resumen se ve más presentable como "el entregable", y la verificación se siente como un paso preliminar descartable. Cómo detectarlo: si tu entrega final no incluye ninguna evidencia ejecutada de que P002 terminó con exactamente dos versiones, estás documentando un proceso sin haber confirmado que ese proceso funcionó — exactamente la misma trampa que el módulo 3 ya advirtió con su propia verificación cruzada. Cómo corregirlo: la Parte 4 de este proyecto no es opcional — es la garantía que hace confiable todo lo que SCD_SUMMARY documenta en la Parte 5.

Confundir "historizar dim_product_scd" con "reemplazar dim_product en todo el resto de la guía". Qué pasa: alguien, al terminar este proyecto, asume que de aquí en adelante toda la guía debería usar dim_product_scd en vez de dim_product, incluyendo el star schema del módulo 2 y la OBT del módulo 3. Por qué pasa: después de un módulo entero dedicado a historizar, parece natural que la versión historizada se vuelva la única versión válida. Cómo detectarlo: si esperas que el módulo 5 modifique mart_daily_sales_obt (del módulo 3) para usar dim_product_scd, perdiste de vista que cada tabla de esta guía tiene un propósito específico. Cómo corregirlo: dim_product (star, sin historia) sigue siendo correcta para cualquier reporte que solo necesite el estado actual; dim_product_scd es correcta específicamente para reportes que necesitan reconstruir el pasado — el módulo 5 va a usar dim_product_scd porque su pregunta central (revenue histórico correcto) lo exige, no porque dim_product_scd haya "reemplazado" a las tablas anteriores.

Asumir que este proyecto agotó todos los escenarios posibles de SCD. Qué pasa: alguien termina este proyecto pensando que ya vio "todos los casos" de historización de dimensiones, sin considerar escenarios que Kiosko no tuvo en este módulo —un producto descontinuado, un producto completamente nuevo, dos cambios el mismo día en productos distintos—. Por qué pasa: un solo ejemplo completo, bien construido y verificado, puede sentirse como "el caso general" cuando en realidad es un caso específico y deliberadamente simple. Cómo detectarlo: si no puedes explicar cómo se comportaría tu MERGE si staging_product trajera un P005 completamente nuevo, o si P004 desapareciera del catálogo, te falta el patrón completo que la lección 5 nombró —WHEN NOT MATCHED BY SOURCE / WHEN NOT MATCHED BY TARGET— sin implementarlo. Cómo corregirlo: este proyecto resuelve, con evidencia completa, el caso que Kiosko necesitaba —un catálogo fijo de cuatro productos, uno de los cuales cambia—; la guía oficial de DuckDB, citada en la lección 5, documenta el patrón completo para catálogos donde los productos también entran y salen.

Ejercicios

Ejercicio 1 — Verifica que P001, P003 y P004 siguen con una sola versión cada uno, sin cambios. Usando dim_product_scd ya construida, escribe una consulta que confirme que los tres productos que nunca cambiaron conservan exactamente sus valores originales del módulo 1.

print(con.sql("""
    SELECT product_id, product_name, category, unit_cost, valid_from, valid_to, is_current
    FROM dim_product_scd
    WHERE product_id != 'P002'
    ORDER BY product_id
"""))

┌────────────┬───────────────────────┬─────────────┬───────────┬────────────┬──────────┬────────────┐
│ product_id │      product_name     │  category   │ unit_cost │ valid_from │ valid_to │ is_current │
│  varchar   │        varchar        │   varchar   │  double   │    date    │   date   │  boolean   │
├────────────┼───────────────────────┼─────────────┼───────────┼────────────┼──────────┼────────────┤
│ P001       │ Bottled Water 600ml   │ beverages   │       0.4 │ 2026-08-01 │ NULL     │ true       │
│ P003       │ Instant Coffee Sachet │ beverages   │      0.35 │ 2026-08-01 │ NULL     │ true       │
│ P004       │ Phone Charger Cable   │ electronics │       2.1 │ 2026-08-01 │ NULL     │ true       │
└────────────┴───────────────────────┴─────────────┴───────────┴────────────┴──────────┴────────────┘

Ejercicio 2 — Extiende SCD_SUMMARY con un campo que indique cuántas filas tiene cada tipo de tabla de este módulo. Sin volver a correr todo el proyecto, agrega a SCD_SUMMARY un campo tables_built_this_module que liste, con su conteo de filas, las tablas construidas a lo largo del módulo: dim_product_type1 (lección 3), dim_product_scd (lecciones 4-8) y dim_product_type3 (lección 7).

SCD_SUMMARY["tables_built_this_module"] = {
    "dim_product_type1": {"rows": 4, "purpose": "SCD tipo 1 -- comparacion, no se usa en modulos siguientes"},
    "dim_product_scd":   {"rows": 5, "purpose": "SCD tipo 2 -- la tabla canonica que usa el modulo 5"},
    "dim_product_type3": {"rows": 4, "purpose": "SCD tipo 3 -- comparacion, no se usa en modulos siguientes"},
}
print(f"tables_built_this_module: {SCD_SUMMARY['tables_built_this_module']}")

tables_built_this_module: {'dim_product_type1': {'rows': 4, 'purpose': 'SCD tipo 1 -- comparacion, no se usa en modulos siguientes'}, 'dim_product_scd': {'rows': 5, 'purpose': 'SCD tipo 2 -- la tabla canonica que usa el modulo 5'}, 'dim_product_type3': {'rows': 4, 'purpose': 'SCD tipo 3 -- comparacion, no se usa en modulos siguientes'}}

Ejercicio 3 — Explica, de memoria, qué necesita el módulo 5 de este proyecto para poder empezar. Sin mirar el diseño de la guía, describe en un párrafo de 4-6 frases qué piezas de SCD_SUMMARY —y de las tablas construidas en este proyecto— va a necesitar el módulo 5 para hacer el join punto-en-el-tiempo entre fact_orders y dim_product_scd.

Resumen y siguiente paso: el final del módulo 4

Con este mini-proyecto cierras el módulo 4 completo. Construiste dim_product_scd desde cero, la historizaste con MERGE INTO corrido dos veces —una sin cambios reales, una con el cambio real y verificado de P002—, confirmaste con un assert literal que la dimensión terminó con exactamente dos versiones de P002 (una cerrada, una vigente), y documentaste todo el proceso en SCD_SUMMARY: la tabla, el producto historizado, las columnas afectadas, la fecha del cambio, y cuántas filas cambió cada corrida del MERGE.

Diste el cuarto paso de un camino de ocho módulos: dim_product_scd —product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current— es, a partir de aquí, la dimensión historizada canónica de Kiosko, lista para que cualquier hecho la consulte respetando el tiempo, en vez de asumir que el presente siempre fue el pasado.

Hacia dónde sigues. El módulo 5 —point-in-time-joins-and-deduplication— toma dim_product_scd, tal como la dejó este proyecto, y responde la pregunta que este módulo preparó pero no respondió: cuando fact_orders se une contra una dimensión historizada, ¿qué pasa si el JOIN solo filtra por is_current = true, en vez de respetar el rango de vigencia de cada venta? Vas a ver, en números reales de Kiosko, la diferencia entre un reporte histórico correcto y uno corrompido — y vas a aprender, en la misma lección, a deduplicar filas repetidas con ROW_NUMBER() y QUALIFY.

Recursos

• Kimball Group — "Slowly Changing Dimension Type 2" — la definición formal que sostiene toda la construcción de dim_product_scd en este proyecto. 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 de referencia que este proyecto integra de punta a punta, en las dos corridas del MERGE. duckdb.org/docs/current/guides/sql_features/merge. En inglés.
• DuckDB — documentación oficial del statement MERGE INTO — la referencia de sintaxis completa usada en las cinco partes de este proyecto. duckdb.org/docs/lts/sql/statements/merge_into. En inglés.
• DuckDB — documentación oficial del cliente Python, la interfaz que ejecutó cada verificación de este proyecto. duckdb.org/docs/current/clients/python/overview. En inglés.