Ensamblando el primer star real de Kiosko

Descripción

Esta es la lección central del módulo — el equivalente, para el star schema, a lo que fue la lección 5 del módulo 1 para el grano. Todas las piezas ya existen: fact_orders (heredado sin cambios), dim_store y dim_product con llave sustituta (lección 3), dim_date recién construida (lección 4). Esta lección las une, por primera vez, con los tres JOIN que forman un star schema completo — y verifica, con la misma disciplina de evidencia del módulo 1, que ese ensamblaje no pierde ni duplica ni una sola fila: cuarenta antes, cuarenta después.

Conexión con el módulo. Esta lección resuelve el resultado ejecutable central de todo el módulo, el que el diseño de esta guía nombra explícitamente: fact_orders reconstruido con los tres JOIN (dim_store, dim_product, dim_date), con un conteo de filas idéntico al del módulo 1.

Una analogía: el aeropuerto de conexión única

Piensa en un aeropuerto diseñado como hub: desde la terminal central, cualquier destino está a un solo vuelo de distancia — no hay que hacer escala en otro aeropuerto para llegar a ningún destino de la red. Eso es exactamente la promesa de un star schema bien construido: desde fact_orders —la terminal central—, llegar a cualquier atributo de tienda, de producto o de fecha toma exactamente un JOIN, nunca dos, nunca una tabla intermedia que atravesar primero.

Esta lección es el primer vuelo real desde esa terminal: no un plano del aeropuerto (eso ya lo viste en la lección 2), sino el itinerario ejecutado de verdad, con pasajeros reales —las cuarenta filas de fact_orders— llegando a sus tres destinos (dim_store, dim_product, dim_date) sin perder a nadie en el camino y sin que nadie llegue duplicado.

Ejemplo trabajado: fact_orders + los tres JOIN

Reconstruye las cuatro tablas del star, exactamente como quedaron en las lecciones 1 a 6 de este módulo, y ensambla el JOIN completo.

# assemble_star.py
from datetime import date, timedelta, datetime

import duckdb

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

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)
    if end < start:
        raise ValueError(f"end_date ({end_date}) es anterior a start_date ({start_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


# --- fact_orders, reconstruido identico a M1 ---
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],
)

# --- dim_store, dim_product con llave sustituta ---
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
""")

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

# --- dim_date ---
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],
)

print("=== Antes del ensamblaje: conteo de fact_orders solo ===")
print(con.sql("SELECT COUNT(*) AS fact_orders_rows FROM fact_orders"))

print("=== El primer star completo de Kiosko: fact_orders + 3 JOIN ===")
star_query = """
    SELECT
        f.order_id,
        s.store_name,
        p.product_name,
        d.day_of_week,
        f.quantity,
        ROUND(f.revenue, 2) AS revenue
    FROM fact_orders f
    JOIN dim_store   s ON f.store_id = s.store_id
    JOIN dim_product p ON f.product_id = p.product_id
    JOIN dim_date    d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
    ORDER BY f.order_id
"""
print(con.sql(star_query + " LIMIT 5"))

print("=== Verificacion del grano: el JOIN no debe perder ni duplicar filas ===")
print(con.sql(f"SELECT COUNT(*) AS joined_rows FROM ({star_query}) t"))

print("=== Comparacion explicita: fact_orders solo vs fact_orders + 3 JOIN ===")
before = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
after = con.sql(f"SELECT COUNT(*) FROM ({star_query}) t").fetchone()[0]
print(f"fact_orders sin unir: {before} filas")
print(f"fact_orders + dim_store + dim_product + dim_date: {after} filas")
assert before == after, "el join perdio o duplico filas -- el star esta roto"
print("Verificacion: {} == {} -> OK, el join no perdio ni duplico ni una fila".format(before, after))

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

=== Antes del ensamblaje: conteo de fact_orders solo ===
┌──────────────────┐
│ fact_orders_rows │
│      int64       │
├──────────────────┤
│               40 │
└──────────────────┘

=== El primer star completo de Kiosko: fact_orders + 3 JOIN ===
┌──────────┬───────────────┬───────────────────────┬─────────────┬──────────┬─────────┐
│ order_id │  store_name   │     product_name      │ day_of_week │ quantity │ revenue │
│ varchar  │    varchar    │        varchar        │   varchar   │  int32   │ double  │
├──────────┼───────────────┼───────────────────────┼─────────────┼──────────┼─────────┤
│ ORD-1001 │ Kiosko Centro │ Bottled Water 600ml   │ Monday      │        3 │    1.65 │
│ ORD-1002 │ Kiosko Centro │ Energy Bar            │ Monday      │        1 │     1.2 │
│ ORD-1003 │ Kiosko Norte  │ Instant Coffee Sachet │ Monday      │        2 │     1.5 │
│ ORD-1004 │ Kiosko Centro │ Phone Charger Cable   │ Monday      │        1 │     4.5 │
│ ORD-1005 │ Kiosko Sur    │ Bottled Water 600ml   │ Monday      │        5 │    2.75 │
└──────────┴───────────────┴───────────────────────┴─────────────┴──────────┴─────────┘

=== Verificacion del grano: el JOIN no debe perder ni duplicar filas ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          40 │
└─────────────┘

=== Comparacion explicita: fact_orders solo vs fact_orders + 3 JOIN ===
fact_orders sin unir: 40 filas
fact_orders + dim_store + dim_product + dim_date: 40 filas
Verificacion: 40 == 40 -> OK, el join no perdio ni duplico ni una fila

Cuarenta filas antes del ensamblaje, cuarenta después — la misma disciplina de verificación con evidencia que ya aprendiste en el módulo 1, ahora aplicada al star completo en vez de a una sola tabla. Este resultado no es casualidad: es la consecuencia directa de dos garantías que ya tenías desde antes. Primero, store_id y product_id en fact_orders siempre apuntan a una fila válida de dim_store/dim_product — foundations ya validó eso en su propio pipeline, y transform_fact_orders() lo verifica de nuevo con un raise ValueError si algo no calza. Segundo, cada order_ts de las cuarenta órdenes cae dentro del rango de agosto de 2026 que dim_date cubre completo — ninguna fecha "se cae" del calendario. Un JOIN interno (INNER JOIN, el tipo por defecto que usa la palabra clave JOIN sola) solo produce una fila de salida cuando encuentra una coincidencia en ambos lados — y como ambas garantías se cumplen para las cuarenta filas, ninguna se pierde.

Diagrama: los tres JOIN, uno por uno

flowchart LR
    FO["fact_orders\n40 filas\nstore_id, product_id, order_ts"]

    FO -->|"JOIN ON store_id"| DS["dim_store\n3 filas"]
    FO -->|"JOIN ON product_id"| DP["dim_product\n4 filas"]
    FO -->|"JOIN ON date_key derivado\nde order_ts"| DD["dim_date\n31 filas"]

    DS --> R["fact_orders_star\n40 filas -- verificado"]
    DP --> R
    DD --> R

Profundización: por qué el JOIN contra dim_date necesita una conversión, y los otros no

Fíjate en algo asimétrico en la consulta de esta lección: el JOIN contra dim_store y dim_product compara directamente dos columnas de texto (f.store_id = s.store_id), pero el JOIN contra dim_date necesita una conversión explícita: CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key. La razón es de tipos: order_ts es un TIMESTAMP completo —fecha y hora, hasta el segundo—, mientras que date_key es un INTEGER que representa solo la fecha, sin hora. No puedes comparar un TIMESTAMP directamente contra un INTEGER — necesitas, primero, extraer solo la parte de fecha del timestamp (strftime(f.order_ts, '%Y%m%d'), que produce un texto como "20260803"), y después convertir ese texto a entero para que coincida con el tipo de date_key.

Esta conversión tiene una consecuencia importante, que vale la pena hacer explícita: el JOIN contra dim_date descarta deliberadamente la hora de cada orden. ORD-1001, con order_ts = 2026-08-03T08:14:00, se une contra la fila de dim_date para 2026-08-03 completo — sin importar si la orden ocurrió a las 8:14 de la mañana o a las 11:59 de la noche. Esto es correcto para el grano de dim_date (una fila por día, no por segundo), y es exactamente la razón por la que order_ts se conserva sin cambios en fact_orders —para cualquier análisis que sí necesite la hora exacta, como "¿a qué hora del día vende más Kiosko?", la consulta usaría order_ts directamente, no dim_date—.

Una alternativa que podrías considerar —y que vale la pena nombrar como error potencial, no como recomendación— sería comparar CAST(f.order_ts AS DATE) = d.calendar_date en vez de construir date_key a partir de order_ts. Técnicamente también funciona, y en algunos motores puede ser incluso más legible. Esta lección usa la comparación por date_key porque es, específicamente, la práctica que un JOIN de producción contra una dimensión de calendario suele preferir: comparar enteros es, en términos generales, más económico para el motor que comparar fechas o timestamps, y la lección 3 del módulo 3 —cuando compares el costo de un JOIN con EXPLAIN— va a retomar esta misma idea con evidencia de plan de ejecución, no solo de convención.

Errores comunes

Usar LEFT JOIN "por seguridad", sin verificar después. Qué pasa: alguien, con la idea de "no perder ninguna fila bajo ninguna circunstancia", cambia los tres JOIN a LEFT JOIN, razonando que así fact_orders nunca pierde filas aunque alguna dimensión tuviera un problema. Por qué pasa: LEFT JOIN se siente como la opción "más segura" — conserva todas las filas del lado izquierdo, sin importar si encuentra coincidencia. Cómo detectarlo: un LEFT JOIN que no encuentra coincidencia no falla ni avisa — simplemente rellena las columnas de la dimensión con NULL, silenciosamente. Si tu integridad referencial ya está garantizada (como en Kiosko), INNER JOIN y LEFT JOIN producen el mismo resultado — pero si algún día dejara de estarlo, el LEFT JOIN escondería el problema en vez de hacerlo visible con una discrepancia de conteo. Cómo corregirlo: usa INNER JOIN cuando esperas —y ya verificaste— que la integridad referencial se cumple; reserva LEFT JOIN para los casos donde perder una fila del lado izquierdo sería, en sí mismo, un error peor que ver NULL en el resultado, y siempre verifica después con un conteo, sin importar qué tipo de JOIN uses.

Comparar order_ts directamente contra calendar_date sin conversión de tipo. Qué pasa: alguien escribe JOIN dim_date d ON f.order_ts = d.calendar_date, esperando que el motor "entienda" que debe comparar solo la parte de fecha. Por qué pasa: en algunos lenguajes o motores, comparaciones de tipos parecidos se resuelven automáticamente sin dar error, así que es fácil asumir que siempre funciona así. Cómo detectarlo: si tu JOIN contra dim_date devuelve cero filas coincidentes —incluso cuando sabes que las fechas existen en ambas tablas—, es casi seguro un problema de tipos: un TIMESTAMP con hora distinta de medianoche nunca es literalmente igual a un DATE, aunque representen "el mismo día" para un humano. Cómo corregirlo: siempre convierte explícitamente antes de comparar —CAST(f.order_ts AS DATE) = d.calendar_date, o el patrón de date_key de esta lección— nunca asumas que el motor va a resolver la ambigüedad por ti.

No verificar el conteo después del JOIN, confiando en que "si no dio error, está bien". Qué pasa: alguien ejecuta el JOIN completo, ve que la consulta corre sin ningún error de SQL, y da por sentado que el resultado es correcto, sin comparar el conteo de filas antes y después. Por qué pasa: la ausencia de un error de sintaxis se siente como suficiente confirmación de que todo salió bien. Cómo detectarlo: un JOIN mal construido —por ejemplo, uniendo por una columna equivocada que coincide por casualidad con varias filas— puede ejecutarse perfectamente, sin ningún error, y aun así duplicar filas silenciosamente, inflando cualquier suma que calcules después. Cómo corregirlo: la verificación explícita de esta lección (before == after, con un assert que fallaría ruidosamente si no coincidieran) no es un paso opcional — es la única forma de tener evidencia, no solo esperanza, de que el JOIN hizo exactamente lo que esperabas.

Ejercicios

Ejercicio 1 — Confirma que INNER JOIN y LEFT JOIN dan el mismo resultado hoy. Reescribe la consulta del ejemplo trabajado usando LEFT JOIN en vez de JOIN en los tres casos, y compara el conteo resultante contra el INNER JOIN original — la prueba concreta de que, cuando la integridad referencial está garantizada, ambos tipos de JOIN producen el mismo número de filas.

inner_count = con.sql("""
    SELECT COUNT(*) FROM fact_orders f
    JOIN dim_store s ON f.store_id = s.store_id
    JOIN dim_product p ON f.product_id = p.product_id
    JOIN dim_date d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
""").fetchone()[0]

left_count = con.sql("""
    SELECT COUNT(*) FROM fact_orders f
    LEFT JOIN dim_store s ON f.store_id = s.store_id
    LEFT JOIN dim_product p ON f.product_id = p.product_id
    LEFT JOIN dim_date d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
""").fetchone()[0]

print(f"INNER JOIN: {inner_count} filas")
print(f"LEFT JOIN:  {left_count} filas")

INNER JOIN: 40 filas
LEFT JOIN:  40 filas

Ejercicio 2 — Calcula revenue por categoría de producto, a través del star. Usando el JOIN contra dim_product (sin necesitar los otros dos), escribe una consulta que agrupe fact_orders por category y calcule revenue total y unidades totales por categoría.

print(con.sql("""
    SELECT p.category, ROUND(SUM(f.revenue), 2) AS revenue, SUM(f.quantity) AS total_units
    FROM fact_orders f
    JOIN dim_product p ON f.product_id = p.product_id
    GROUP BY p.category
    ORDER BY p.category
"""))

┌─────────────┬─────────┬─────────────┐
│  category   │ revenue │ total_units │
│   varchar   │ double  │   int128    │
├─────────────┼─────────┼─────────────┤
│ beverages   │   44.05 │          75 │
│ electronics │    40.5 │           9 │
│ snacks      │    21.6 │          18 │
└─────────────┴─────────┴─────────────┘

Ejercicio 3 — Explica, sin código, qué pasaría si dim_date solo cubriera hasta el 31 de julio de 2026. Imagina que, por un error, alguien hubiera generado dim_date con el rango "2026-07-01" a "2026-07-31" en vez de agosto completo. En 2-3 frases, explica qué pasaría con el conteo de filas del JOIN de esta lección, y por qué la verificación before == after lo detectaría inmediatamente.

Resumen y siguiente paso

En esta lección ensamblaste, por primera vez, el star schema completo de Kiosko: fact_orders unido a sus tres dimensiones —dim_store por store_id, dim_product por product_id, dim_date por una llave derivada de order_ts—, verificado con evidencia de que el conteo de filas no cambió: cuarenta antes, cuarenta después. Aprendiste por qué el JOIN contra dim_date necesita una conversión de tipo que los otros dos no necesitan, y por qué INNER JOIN —no LEFT JOIN— es la elección correcta cuando la integridad referencial ya está garantizada.

Antes de avanzar deberías poder: escribir de memoria la estructura de los tres JOIN que ensamblan el star de Kiosko; explicar por qué la comparación contra dim_date requiere CAST/strftime mientras que las otras dos no; y describir qué evidencia concreta confirma que un JOIN no perdió ni duplicó filas.

La lección 8 —el mini-proyecto de cierre— reúne todo el módulo en una sola entrega formal: el star schema completo, documentado como una estructura de datos, y verificado contra los mismos números de revenue que ya conoces desde foundations.

Recursos

• DuckDB — documentación oficial de sentencias SELECT y tipos de JOIN (INNER, LEFT), la base de la consulta central de esta lección. duckdb.org/docs/current/sql/query_syntax/from. En inglés.
• DuckDB — documentación de funciones de formato de fecha (strftime), usada para derivar date_key a partir de order_ts en el JOIN contra dim_date. duckdb.org/docs/current/sql/functions/dateformat. En inglés.
• Kimball Group — "Four-Step Dimensional Design Process" — la fuente que sostiene por qué un grano bien declarado (módulo 1) garantiza que un JOIN bien construido no pierda ni duplique filas. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process. En inglés.