Publicando la OBT mart para el equipo de dashboards

Descripción

El warehouse ya tiene todo lo que el brief pidió, pero repartido en cinco tablas distintas —fact_orders, dim_store, dim_date, dim_product_scd, y el join punto-en-el-tiempo que hay que escribir a mano cada vez—. Esta lección cierra la capa gold del warehouse construyendo mart_daily_sales_obt: la tabla ancha del módulo 3, reconstruida aquí con una diferencia decisiva que ningún módulo anterior pudo hacer — el JOIN contra dim_product_scd usa el patrón punto-en-el-tiempo de la lección 4, no la dimensión estática que usó el módulo 3 original. El resultado es una sola tabla, con grano de día + tienda + producto, lista para que el equipo de BI la consulte con un GROUP BY simple, sin escribir un solo BETWEEN valid_from AND valid_to por su cuenta.

Conexión con el módulo. Esta es la única pieza genuinamente nueva de todo el capstone, tal como anticipó la lección 1: la combinación de la tabla ancha del módulo 3 con la dimensión historizada y su join correcto de los módulos 4 y 5. Ningún módulo anterior pudo construir esta versión, porque dim_product_scd no existía cuando el módulo 3 construyó su propia OBT.

Una analogía: el plato ya cortado, no los ingredientes crudos sobre la mesa

Piensa en la diferencia entre entregarle a alguien una receta completa con todos los ingredientes crudos sobre la mesa —incluyendo instrucciones de seguridad sobre cuál cuchillo usar para cada corte— y entregarle el plato ya preparado, cortado, cocinado y servido. Un cocinero experimentado puede trabajar con los ingredientes crudos sin ningún problema; alguien que solo quiere comer, no. La receta completa no está "mal" —es exactamente lo que un cocinero necesita—, pero exigirle a un comensal que la siga él mismo, cada vez que tiene hambre, es pedirle una habilidad que nunca quiso desarrollar.

fact_orders + dim_product_scd + el JOIN punto-en-el-tiempo son los ingredientes crudos y la receta completa —perfectos para quien ya domina el modelado dimensional, como tú después de siete módulos—. mart_daily_sales_obt, publicada en esta lección, es el plato ya servido: el equipo de BI se sienta a la mesa y encuentra la categoría correcta de cada producto ya resuelta, sin tener que cocinar nada ellos mismos.

Ejemplo trabajado: la OBT, con el join correcto ya resuelto por dentro

Parte 1 — Construyendo mart_daily_sales_obt con el join punto-en-el-tiempo

Este script continúa sobre la misma conexión con de las lecciones 4 y 5 —fact_orders, dim_store, dim_date y dim_product_scd ya están completos y verificados—.

# capstone_obt.py -- mart_daily_sales_obt (continua sobre con, con el star y dim_product_scd ya listos)
con.execute("""
    CREATE TABLE mart_daily_sales_obt AS
    SELECT
        CAST(f.order_ts AS DATE) AS sale_date, dt.day_of_week, dt.is_weekend,
        s.store_id, s.store_name, s.city,
        p.product_id, p.product_name, p.category, p.unit_cost,
        SUM(f.quantity) AS quantity, ROUND(SUM(f.revenue), 2) AS revenue,
        ROUND(SUM(f.revenue - f.quantity * p.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_store s ON f.store_id = s.store_id
    JOIN dim_date  dt ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = dt.date_key
    JOIN dim_product_scd p
        ON f.product_id = p.product_id
       AND f.order_ts BETWEEN p.valid_from AND COALESCE(p.valid_to, DATE '9999-12-31')
    GROUP BY 1,2,3,4,5,6,7,8,9,10
""")
obt_rows = con.sql("SELECT COUNT(*) FROM mart_daily_sales_obt").fetchone()[0]
obt_revenue = con.sql("SELECT ROUND(SUM(revenue), 2) FROM mart_daily_sales_obt").fetchone()[0]
obt_categories = con.sql("SELECT DISTINCT category FROM mart_daily_sales_obt ORDER BY category").fetchall()

print("Parte 1 -- mart_daily_sales_obt: la OBT para el equipo de BI")
print(f"  mart_daily_sales_obt {obt_rows:3} filas, revenue total = {obt_revenue}")
print(f"  categorias presentes: {[c[0] for c in obt_categories]}")
assert obt_revenue == 106.15
assert "snacks" in {c[0] for c in obt_categories} and "health-snacks" not in {c[0] for c in obt_categories}
print("  Verificacion OK: la OBT publicada ya trae la categoria correcta ('snacks'), sin que BI escriba el JOIN")

Qué esperar.

Parte 1 -- mart_daily_sales_obt: la OBT para el equipo de BI
  mart_daily_sales_obt  39 filas, revenue total = 106.15
  categorias presentes: ['beverages', 'electronics', 'snacks']

Verificacion OK: la OBT publicada ya trae la categoria correcta ('snacks'), sin que BI escriba el JOIN

Treinta y nueve filas —el mismo grano más grueso que el módulo 3 ya estableció (día + tienda + producto, colapsando las cuarenta líneas de orden donde dos ventas del mismo producto, misma tienda, mismo día se agrupan en una sola fila)—, y 106.15 de revenue, idéntico a siempre. Pero fíjate en la lista de categorías: ['beverages', 'electronics', 'snacks'] — sin health-snacks. Eso no es casualidad ni suerte: es el JOIN punto-en-el-tiempo de la Parte 1 haciendo, por dentro de esta tabla, exactamente lo que la lección 4 demostró que era correcto. Cualquier persona del equipo de BI que abra mart_daily_sales_obt y agrupe por category va a obtener el desglose correcto, sin haber escrito ni un BETWEEN ni un MERGE INTO en su vida.

Parte 2 — El reporte final, tal como lo pediría la gerencia

print("\n=== Reporte para la gerencia de Kiosko ===")
print(con.sql("""
    SELECT store_name, ROUND(SUM(revenue), 2) AS revenue, ROUND(SUM(margin), 2) AS margin
    FROM mart_daily_sales_obt GROUP BY store_name ORDER BY store_name
"""))
print(con.sql("""
    SELECT category, ROUND(SUM(revenue), 2) AS revenue, ROUND(SUM(margin), 2) AS margin
    FROM mart_daily_sales_obt GROUP BY category ORDER BY category
"""))

Qué esperar.

=== Reporte para la gerencia de Kiosko ===
┌───────────────┬─────────┬────────┐
│  store_name   │ revenue │ margin │
│    varchar    │ double  │ double │
├───────────────┼─────────┼────────┤
│ Kiosko Centro │    38.3 │   17.4 │
│ Kiosko Norte  │   38.8  │  17.65 │
│ Kiosko Sur    │   29.05 │   12.1 │
└───────────────┴─────────┴────────┘

┌─────────────┬─────────┬────────┐
│  category   │ revenue │ margin │
│   varchar   │ double  │ double │
├─────────────┼─────────┼────────┤
│ beverages   │   44.05 │  14.75 │
│ electronics │    40.5 │   21.6 │
│ snacks      │    21.6 │   10.8 │
└─────────────┴─────────┴────────┘

Dos consultas, dos GROUP BY de una sola línea cada una — ninguna tiene un JOIN, ninguna menciona dim_product_scd, valid_from ni MERGE INTO. Esto es, con precisión, lo que el brief de la lección 2 pidió: la gerencia (o cualquier analista de BI) obtiene el margen correcto por categoría —10.8 para snacks, no 9.36 para una health-snacks que ni siquiera existía en la fecha de esas ventas— sin necesitar entender ninguna palabra técnica del modelado dimensional que hizo posible ese número.

Diagrama: la OBT como la capa que absorbe la complejidad, no la esconde

flowchart LR
    subgraph Equipo_Datos["Equipo de datos (modulos 1-8)"]
        A["fact_orders\ndim_store, dim_date\ndim_product_scd historizada\nJOIN punto-en-el-tiempo"]
    end
    subgraph OBT["mart_daily_sales_obt (esta leccion)"]
        B["39 filas\nGRUPO POR dia+tienda+producto\ncategoria YA resuelta"]
    end
    subgraph BI["Equipo de BI"]
        C["GROUP BY category\nSUM(revenue)\nsin saber que es SCD"]
    end
    A -->|"la complejidad se resuelve UNA VEZ,\naqui, no cada vez que BI consulta"| B --> C

La flecha entre "Equipo de datos" y "mart_daily_sales_obt" no representa esconder complejidad —la lección 4 ya demostró, con toda transparencia, exactamente qué decisión de modelado hace que esta tabla sea correcta—. Representa absorberla, una sola vez, en el lugar donde vive el conocimiento para tomarla bien, en vez de repartirla a cada persona que solo necesita el resultado.

Profundización: qué se pierde al aplanar en una OBT, y por qué aquí no importa

El módulo 3 ya advirtió que una tabla ancha tiene un costo: columnas repetidas, más espacio en disco, y el riesgo de que un UPDATE masivo (como un cambio de nombre de tienda) tenga que tocar muchas más filas que en una dimensión normalizada. Esta lección no cambia esa disyuntiva —sigue siendo cierta—, pero vale la pena ser explícitos sobre algo que sí cambia: mart_daily_sales_obt, tal como la construye esta lección, no se actualiza con un MERGE INTO incremental como dim_product_scd — se reconstruye completa cada vez que el warehouse corre de nuevo, con un simple CREATE TABLE ... AS SELECT. Esa es una decisión deliberada, coherente con la frontera que esta guía declaró desde el módulo 1: la orquestación real, con actualización incremental de una tabla derivada, es terreno de airflow-and-declarative-orchestration-guide. Aquí, "publicar la OBT" significa reconstruirla desde cero cada vez, exactamente como ya hiciste en el módulo 3 — la diferencia de esta lección está en qué consulta la reconstruye, no en cómo se actualiza con el tiempo.

Errores comunes

Reconstruir mart_daily_sales_obt uniendo contra dim_product (la versión estática) en vez de dim_product_scd. Qué pasa: alguien, recordando el código del módulo 3, copia esa consulta casi literal, uniendo fact_orders con dim_product en vez de dim_product_scd. Por qué pasa: dim_product sigue existiendo en el warehouse —nunca se eliminó—, y su JOIN es más simple de escribir, sin ningún BETWEEN. Cómo detectarlo: si tu mart_daily_sales_obt muestra P002 siempre como snacks sin importar la fecha —porque dim_product, la tabla estática del catálogo original, nunca se actualizó con el cambio de agosto—, tu OBT "por casualidad" da el resultado correcto para este dataset específico, pero no porque aplicaste el patrón correcto. Cómo corregirlo: el punto central de esta lección es usar dim_product_scd con el JOIN punto-en-el-tiempo, no dim_product — en un dataset donde el cambio de categoría ocurriera durante la semana de ventas (no después, como en Kiosko), unir contra la versión estática daría un resultado distinto, y equivocado, al de esta lección.

Olvidar COALESCE(p.valid_to, DATE '9999-12-31') y perder las ventas del producto vigente. Qué pasa: alguien escribe el JOIN punto-en-el-tiempo sin el COALESCE, dejando f.order_ts BETWEEN p.valid_from AND p.valid_to a secas. Por qué pasa: parece una simplificación razonable, y funciona perfecto para las versiones cerradas de una dimensión (las que sí tienen valid_to). Cómo detectarlo: si tu OBT pierde filas completas de un producto —por ejemplo, si P001, P003 o P004, cuya única versión tiene valid_to = NULL, desaparecieran del resultado—, tienes exactamente este error: BETWEEN x AND NULL nunca es verdadero en SQL, sin importar el valor de x. Cómo corregirlo: el COALESCE(p.valid_to, DATE '9999-12-31') de esta lección —el mismo patrón que ya usaste en los módulos 4 y 5— convierte el "sin fecha de cierre" (NULL) en una fecha de cierre lejana en el futuro, para que la comparación BETWEEN funcione también para la versión vigente de cualquier producto.

Pensar que 39 filas (en vez de 40) es un error de esta lección. Qué pasa: alguien, al ver que mart_daily_sales_obt tiene 39 filas mientras que fact_orders tiene 40, sospecha que el JOIN punto-en-el-tiempo perdió una fila. Por qué pasa: después de varias lecciones insistiendo en que un JOIN nunca debe perder ni duplicar filas, ver un número distinto genera alarma automática. Cómo detectarlo: revisa el grano de cada tabla — fact_orders tiene grano de "línea de orden" (40 filas), mart_daily_sales_obt tiene grano de "día + tienda + producto" (39 filas), porque dos líneas de orden del mismo producto, misma tienda, mismo día se agrupan en una sola fila de la OBT. Cómo corregirlo: esto no es un error — es exactamente el mismo comportamiento que ya viste en el módulo 3, cuando la OBT original también tuvo 39 filas por la misma razón. La verificación correcta no es "¿mismo número de filas?", es "¿mismo revenue total?" — y 106.15 en ambas tablas confirma que la agregación no perdió ni un centavo, aunque el número de filas cambie porque el grano cambió a propósito.

Ejercicios

Ejercicio 1 — Confirma que la OBT reproduce el revenue por producto, no solo por categoría. Escribe una consulta que agrupe mart_daily_sales_obt por product_name y confirme los mismos números que ya conoces desde el módulo 1 (33.55/21.6/10.5/40.5).

print(con.sql("""
    SELECT product_name, ROUND(SUM(revenue), 2) AS revenue
    FROM mart_daily_sales_obt GROUP BY product_name ORDER BY product_name
"""))

┌───────────────────────┬─────────┐
│      product_name     │ revenue │
│        varchar        │ double  │
├───────────────────────┼─────────┤
│ Bottled Water 600ml   │   33.55 │
│ Energy Bar            │   21.6  │
│ Instant Coffee Sachet │   10.5  │
│ Phone Charger Cable   │    40.5 │
└───────────────────────┴─────────┘

Ejercicio 2 — Simula qué pasaría si una venta ocurriera después del 15 de agosto. Sin modificar fact_orders, agrega una fila hipotética de P002 con order_ts = '2026-08-20T10:00:00' a una copia temporal, y confirma que el JOIN punto-en-el-tiempo la resolvería a health-snacks, no a snacks.

con.execute("""
    CREATE TABLE fact_orders_hypothetical AS
    SELECT * FROM fact_orders
    UNION ALL
    SELECT 'ORD-9999', 'S01', 'P002', 1, 1.30, 1.30, TIMESTAMP '2026-08-20 10:00:00'
""")
print(con.sql("""
    SELECT f.order_id, f.order_ts, p.category
    FROM fact_orders_hypothetical f
    JOIN dim_product_scd p
        ON f.product_id = p.product_id
       AND f.order_ts BETWEEN p.valid_from AND COALESCE(p.valid_to, DATE '9999-12-31')
    WHERE f.order_id = 'ORD-9999'
"""))

┌──────────┬─────────────────────┬───────────────┐
│ order_id │      order_ts       │   category    │
│ varchar  │      timestamp      │    varchar    │
├──────────┼─────────────────────┼───────────────┤
│ ORD-9999 │ 2026-08-20 10:00:00 │ health-snacks │
└──────────┴─────────────────────┴───────────────┘

Ejercicio 3 — Explica, de memoria, por qué esta lección es la única del capstone que el módulo 3 no pudo anticipar. En 2-3 frases, explica qué le faltaba al módulo 3, en el momento en que se escribió, para construir esta misma versión de mart_daily_sales_obt.

Resumen y siguiente paso

En esta lección cerraste la capa gold del warehouse: mart_daily_sales_obt, treinta y nueve filas, con la categoría de cada producto ya resuelta con el join punto-en-el-tiempo de la lección 4 —snacks, no health-snacks— sin que el equipo de BI tenga que escribir ese JOIN por su cuenta. Confirmaste, con dos consultas de una sola línea cada una, el reporte exacto que la gerencia de Kiosko pidió en el brief de la lección 2: revenue y margen por tienda, revenue y margen por categoría.

Antes de avanzar deberías poder: explicar por qué esta OBT usa dim_product_scd y no dim_product; recitar de memoria el revenue y margen por categoría (beverages 44.05/14.75, electronics 40.5/21.6, snacks 21.6/10.8); y explicar por qué 39 filas en la OBT, contra 40 en fact_orders, no es un error.

La lección 7 da un paso atrás del código: con el warehouse completo ya construido, nombra, una por una, las guías hermanas del ecosistema de Data Engineering que profundizan cada limitación real que este warehouse todavía tiene.

Recursos

• Fivetran — "Star Schema vs. OBT for Data Warehouse Performance" — el benchmark que ya justificó, en el módulo 3, por qué una tabla ancha tiene sentido para un consumidor de BI concreto. fivetran.com/blog/star-schema-vs-obt. En inglés.
• dataarchitect.studio — "One Big Table vs the Star Schema: The Real Trade-off" — el argumento de capas (star como base, OBT como servicio) que esta lección aplica de forma concreta, sirviendo la OBT sobre el star ya historizado. dataarchitect.studio/essays/one-big-table-vs-star-schema. En inglés.
• DuckDB — documentación oficial del statement CREATE TABLE ... AS SELECT, el mecanismo que reconstruye mart_daily_sales_obt completa en cada corrida. duckdb.org/docs/current/sql/statements/create_table. 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.