Módulo 3: Star Vs Snowflake Vs One Big Table

Comparando el costo de un JOIN con EXPLAIN

Siguiente →

Descripción

En la lección anterior construiste dos caminos distintos hacia la misma información: dim_product (la versión star, con category como columna de texto, un solo JOIN de distancia desde fact_orders) y dim_product_normalized + dim_category (la versión snowflake, dos JOIN de distancia). Hasta ahora, esa diferencia —"un salto" contra "dos saltos"— fue una afirmación conceptual. Esta lección la convierte en evidencia: usa EXPLAIN, el comando de DuckDB que muestra el plan de ejecución real que el motor va a correr, para ver, con tus propios ojos, la diferencia física entre las dos consultas.

Conexión con el módulo. Esta lección resuelve el segundo resultado ejecutable del módulo, el que la lección 3 del diseño de esta guía nombra explícitamente: comparar planes con EXPLAIN, un salto de JOIN (star) contra dos saltos (snowflake), sobre datos y consultas ya construidas y verificadas.

Una analogía: el plano de la ruta, no el mapa de la ciudad

Cuando pides indicaciones para llegar a un lugar, hay dos formas de recibirlas. La primera es un mapa general de la ciudad: útil para tener contexto, pero no te dice, paso a paso, qué vas a hacer. La segunda es un itinerario preciso: "sal por la puerta principal, camina dos cuadras, gira a la derecha, entra al segundo edificio" — cada paso, en el orden exacto en que vas a ejecutarlo, sin ambigüedad.

EXPLAIN es ese itinerario preciso, aplicado a una consulta SQL. No te dice, en abstracto, "esto va a hacer un JOIN" — te muestra, en el orden exacto en que el motor va a ejecutarlas, cada operación física: qué tabla escanea primero, cómo combina los resultados, cuántas filas espera encontrar en cada paso. Comparar el plan de dos consultas no es comparar dos mapas generales de "cómo se ve más o menos la ruta" — es comparar dos itinerarios línea por línea, contando cuántos giros tiene cada uno.

Ejemplo trabajado: el plan de star contra el plan de snowflake

Reconstruye fact_orders y las dos versiones de la dimensión de producto —la star (dim_product, con category como texto) y la snowflake (dim_product_normalized + dim_category, de la lección anterior)— y compara el plan de ejecución de la misma pregunta resuelta por los dos caminos: "para cada línea de orden, ¿cuál es su categoría?".

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

# --- dim_product, la version star (category como columna de texto, heredada del modulo 2) ---
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_category + dim_product_normalized, la version snowflake de la leccion anterior ---
con.execute("""
    CREATE TABLE dim_category AS
    SELECT ROW_NUMBER() OVER (ORDER BY category) AS category_id, category AS category_name
    FROM (SELECT DISTINCT category FROM dim_product_natural) t
""")
con.execute("""
    CREATE TABLE dim_product_normalized AS
    SELECT ROW_NUMBER() OVER (ORDER BY n.product_id) AS product_key, n.product_id, n.product_name, c.category_id, n.unit_cost
    FROM dim_product_natural n
    JOIN dim_category c ON n.category = c.category_name
""")

star_query = """
    SELECT f.order_id, f.revenue, p.category
    FROM fact_orders f
    JOIN dim_product p ON f.product_id = p.product_id
"""

snowflake_query = """
    SELECT f.order_id, f.revenue, c.category_name AS category
    FROM fact_orders f
    JOIN dim_product_normalized p ON f.product_id = p.product_id
    JOIN dim_category           c ON p.category_id = c.category_id
"""

print(f"DuckDB version: {duckdb.__version__}\n")

print("=== EXPLAIN: star -- 1 salto de JOIN hasta llegar a category ===")
for key, value in con.execute("EXPLAIN " + star_query).fetchall():
    print(value)

print("=== EXPLAIN: snowflake -- 2 saltos de JOIN hasta llegar a category ===")
for key, value in con.execute("EXPLAIN " + snowflake_query).fetchall():
    print(value)

print("=== Verificacion: ambos caminos devuelven el mismo resultado ===")
star_rows = con.sql(f"SELECT COUNT(*) FROM ({star_query}) t").fetchone()[0]
sf_rows = con.sql(f"SELECT COUNT(*) FROM ({snowflake_query}) t").fetchone()[0]
star_rev = con.sql(f"SELECT ROUND(SUM(revenue), 2) FROM ({star_query}) t").fetchone()[0]
sf_rev = con.sql(f"SELECT ROUND(SUM(revenue), 2) FROM ({snowflake_query}) t").fetchone()[0]
print(f"star:      {star_rows} filas, revenue {star_rev}")
print(f"snowflake: {sf_rows} filas, revenue {sf_rev}")
assert star_rows == sf_rows and star_rev == sf_rev, "los dos caminos no coinciden"
print("Verificacion: ambos caminos coinciden -- OK")

Qué esperar. Al correr python3 explain_join_cost.py (con DuckDB 1.5.5, la versión usada para esta lección — el formato exacto de un plan de EXPLAIN puede variar levemente entre versiones del motor, aunque el número de operadores HASH_JOIN que vas a contar a continuación no depende de la versión), la salida es exactamente esta:

DuckDB version: 1.5.5

=== EXPLAIN: star -- 1 salto de JOIN hasta llegar a category ===
┌───────────────────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│  product_id = product_id  │              │
│                           │              │
│          ~40 rows         │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││          SEQ_SCAN         │
│    ────────────────────   ││    ────────────────────   │
│           Table:          ││           Table:          │
│  memory.main.fact_orders  ││  memory.main.dim_product  │
│                           ││                           │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│                           ││                           │
│        Projections:       ││        Projections:       │
│         product_id        ││         product_id        │
│          order_id         ││          category         │
│          revenue          ││                           │
│                           ││                           │
│          ~40 rows         ││          ~4 rows          │
└───────────────────────────┘└───────────────────────────┘
=== EXPLAIN: snowflake -- 2 saltos de JOIN hasta llegar a category ===
┌───────────────────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│  product_id = product_id  │              │
│                           │              │
│          ~40 rows         │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││         HASH_JOIN         │
│    ────────────────────   ││    ────────────────────   │
│           Table:          ││      Join Type: INNER     │
│  memory.main.fact_orders  ││                           │
│                           ││        Conditions:        │
│   Type: Sequential Scan   ││ category_id = category_id │
│                           ││                           ├──────────────┐
│        Projections:       ││                           │              │
│         product_id        ││                           │              │
│          order_id         ││                           │              │
│          revenue          ││                           │              │
│                           ││                           │              │
│          ~40 rows         ││          ~4 rows          │              │
└───────────────────────────┘└─────────────┬─────────────┘              │
                             ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
                             │          SEQ_SCAN         ││          SEQ_SCAN         │
                             │    ────────────────────   ││    ────────────────────   │
                             │           Table:          ││           Table:          │
                             │        memory.main        ││  memory.main.dim_category │
                             │  .dim_product_normalized  ││                           │
                             │                           ││   Type: Sequential Scan   │
                             │   Type: Sequential Scan   ││                           │
                             │                           ││        Projections:       │
                             │        Projections:       ││        category_id        │
                             │         product_id        ││       category_name       │
                             │        category_id        ││                           │
                             │                           ││                           │
                             │          ~4 rows          ││          ~3 rows          │
                             └───────────────────────────┘└───────────────────────────┘
=== Verificacion: ambos caminos devuelven el mismo resultado ===
star:      40 filas, revenue 106.15
snowflake: 40 filas, revenue 106.15
Verificacion: ambos caminos coinciden -- OK

Cuenta los operadores HASH_JOIN en cada plan: el árbol del star tiene exactamente uno —fact_orders se combina directamente con dim_product, cada uno leído con un SEQ_SCAN (barrido secuencial), y listo—. El árbol del snowflake tiene exactamente dos — el primer HASH_JOIN combina fact_orders con el resultado de un segundo HASH_JOIN, que a su vez combina dim_product_normalized con dim_category. Esto no es una opinión ni una estimación: es, literalmente, el plan físico que DuckDB genera para ejecutar cada consulta, con el número de operadores que vas a contar tú mismo. Y la verificación final confirma algo igual de importante: los dos planes —con distinto costo de ejecución— producen exactamente el mismo resultado, 106.15 de revenue en ambos casos. Un JOIN extra no cambia la respuesta correcta; cambia cuánto trabajo le toma al motor llegar a ella.

Diagrama: el árbol de operadores, uno al lado del otro

flowchart TD
    subgraph Star["star -- 1 HASH_JOIN"]
        A1["HASH_JOIN\nproduct_id = product_id"]
        A2["SEQ_SCAN\nfact_orders"] --> A1
        A3["SEQ_SCAN\ndim_product\n(category ya es columna)"] --> A1
    end

    subgraph Snowflake["snowflake -- 2 HASH_JOIN"]
        B1["HASH_JOIN\nproduct_id = product_id"]
        B2["SEQ_SCAN\nfact_orders"] --> B1
        B3["HASH_JOIN\ncategory_id = category_id"] --> B1
        B4["SEQ_SCAN\ndim_product_normalized"] --> B3
        B5["SEQ_SCAN\ndim_category"] --> B3
    end

Profundización: qué significa cada pieza del plan, y por qué el orden importa

Un plan de EXPLAIN de DuckDB se lee de abajo hacia arriba: los operadores en la base del árbol son los primeros en ejecutarse, y el resultado sube, nivel por nivel, hasta el operador raíz —el HASH_JOIN de más arriba, en ambos planes de esta lección—. Cada caja del plan te dice tres cosas: qué tipo de operador es (SEQ_SCAN para un barrido secuencial de una tabla completa, HASH_JOIN para combinar dos conjuntos de filas por una condición de igualdad), qué tabla o condición usa, y cuántas filas espera producir (la estimación ~N rows, calculada por el optimizador antes de ejecutar la consulta, no medida después).

Fíjate en un detalle revelador del plan del snowflake: el segundo HASH_JOIN —el que combina dim_product_normalized con dim_category— aparece antes, en el orden de ejecución, que el HASH_JOIN principal que lo combina con fact_orders. Esto tiene sentido si piensas en lo que la consulta necesita: para poder unir fact_orders contra "la categoría de cada producto", primero hay que reconstruir esa información —unir dim_product_normalized con dim_category—, y solo después usar ese resultado reconstruido como el lado derecho del JOIN principal. El snowflake no solo tiene un operador más: tiene una dependencia adicional que el motor debe resolver antes de poder completar el trabajo que, en la versión star, resuelve un solo SEQ_SCAN directo sobre dim_product.

Vale la pena ser preciso sobre qué prueba esta lección y qué no. Con tres tiendas, cuatro productos y cuarenta órdenes, la diferencia entre un HASH_JOIN y dos es, en términos de tiempo real de ejecución, insignificante — ambas consultas corren en microsegundos sobre este dataset de juguete. Lo que esta lección demuestra no es "el snowflake es lento en la práctica hoy" —con este volumen, no lo es—, sino algo más fundamental: la estructura del plan de ejecución escala con el número de saltos de normalización, sin importar el volumen de datos. Un warehouse de producción con millones de filas en su tabla de hechos y una jerarquía de dimensiones normalizada en cuatro o cinco niveles —producto → subcategoría → categoría → departamento, por ejemplo— paga ese mismo patrón, multiplicado, en cada consulta. Esta lección te enseña a leer esa estructura con EXPLAIN; no te enseña a afinar un motor de producción con millones de filas —eso es terreno de advanced-sql-querying-guide, la guía hermana que profundiza en planes de ejecución, índices y tuning real.

Errores comunes

Confundir EXPLAIN con EXPLAIN ANALYZE. Qué pasa: alguien espera que EXPLAIN le muestre tiempos reales de ejecución —cuántos milisegundos tardó cada operador—, y se sorprende cuando el plan solo muestra estimaciones (~N rows), sin ningún número de tiempo. Por qué pasa: en el lenguaje cotidiano, "explicar" una consulta suena como "decirme cómo se comportó", que es exactamente lo que hace la variante EXPLAIN ANALYZE —que sí ejecuta la consulta y mide tiempos reales—, no EXPLAIN a secas —que solo genera el plan, sin ejecutar nada—. Cómo detectarlo: si tu salida no tiene ninguna columna de tiempo (ms, μs) ni de filas realmente procesadas, y solo tiene estimaciones con el símbolo ~, estás viendo un EXPLAIN simple, no un EXPLAIN ANALYZE. Cómo corregirlo: para esta lección, EXPLAIN simple es exactamente lo que necesitas —comparar la forma del plan (cuántos operadores, qué tipo), no medir tiempos de un dataset de cuarenta filas, donde cualquier medición de tiempo real sería ruido, no señal. EXPLAIN ANALYZE —y el tuning real que depende de tiempos medidos— es terreno de advanced-sql-querying-guide.

Asumir que "más JOIN siempre es más lento en cualquier volumen". Qué pasa: alguien ve el plan del snowflake con dos HASH_JOIN y concluye, sin más evidencia, que el snowflake siempre va a ser más lento que el star, en cualquier situación y cualquier volumen de datos. Por qué pasa: "más operadores en el plan" se siente intuitivamente como "más lento", y esa intuición no está del todo equivocada — pero es incompleta. Cómo detectarlo: si tu conclusión de esta lección es "nunca normalices nada, siempre es peor", te falta la lección 6, que va a mostrarte, con evidencia igual de concreta, un escenario donde el costo adicional del JOIN es mucho menor que el beneficio de mantener un solo punto de verdad. Cómo corregirlo: el número de JOIN es un factor del costo de una consulta, no el único. El tamaño de las tablas que participan en cada JOIN (aquí, dim_category tiene solo tres filas — casi gratis de escanear), la frecuencia con la que cambia el dato, y cuántas veces se ejecuta la misma consulta contra cuántas veces se actualiza el dato, importan tanto como el conteo de operadores.

Ejecutar EXPLAIN sobre una consulta distinta a la que realmente vas a correr, y comparar peras con manzanas. Qué pasa: alguien compara el plan de una consulta que selecciona pocas columnas contra el plan de otra consulta que selecciona muchas más, o que tiene un WHERE adicional, y atribuye la diferencia de planes exclusivamente al número de JOIN. Por qué pasa: es fácil, al armar una comparación rápida, escribir dos consultas ligeramente distintas sin darse cuenta. Cómo detectarlo: si las dos consultas que estás comparando no seleccionan exactamente las mismas columnas lógicas (aquí, order_id, revenue y category, sin más), tu comparación no aísla la variable que quieres medir. Cómo corregirlo: como en esta lección, mantén las dos consultas idénticas en todo excepto en el camino que llega a category — así cualquier diferencia en el plan se explica exclusivamente por la forma de la dimensión, no por otra variable escondida.

Ejercicios

Ejercicio 1 — Cuenta los operadores SEQ_SCAN en cada plan. Sin volver a correr el script, revisa el "Qué esperar" de esta lección y cuenta cuántos operadores SEQ_SCAN (barrido secuencial de tabla) aparecen en el plan del star y cuántos en el plan del snowflake. Explica en una frase por qué el número coincide con el número de tablas que participan en cada consulta.

Ver solución

El plan del star tiene dos operadores SEQ_SCAN (uno para fact_orders, uno para dim_product), porque la consulta star solo involucra dos tablas. El plan del snowflake tiene tres operadores SEQ_SCAN (uno para fact_orders, uno para dim_product_normalized, uno para dim_category), porque la consulta snowflake involucra tres tablas. El número de SEQ_SCAN coincide, en ambos casos, con el número de tablas distintas que la consulta lee — cada tabla necesita, como mínimo, un barrido para poner sus filas a disposición de los operadores de JOIN que las combinan después.

Ejercicio 2 — Escribe la consulta snowflake para revenue por categoría, y compárala contra el resultado del módulo 2. Usando dim_product_normalized y dim_category, escribe una consulta que agrupe fact_orders por categoría y calcule revenue total — la misma pregunta que resolviste en el ejercicio 2 de la lección 7 del módulo 2, ahora por el camino snowflake.

Ver solución 
print(con.sql("""
    SELECT c.category_name AS category, ROUND(SUM(f.revenue), 2) AS revenue, SUM(f.quantity) AS total_units
    FROM fact_orders f
    JOIN dim_product_normalized p ON f.product_id = p.product_id
    JOIN dim_category           c ON p.category_id = c.category_id
    GROUP BY c.category_name
    ORDER BY c.category_name
"""))

Salida esperada:

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

Exactamente los mismos números que ya viste en el módulo 2 —44.05, 40.5, 21.6—, ahora calculados a través de dos JOIN en vez de uno. La forma del modelo cambió; el hecho de negocio que reporta, no.

Ejercicio 3 — Explica, sin código, qué pasaría con el plan si dim_category tuviera un millón de filas en vez de tres. En 2-3 frases, explica si el número de operadores HASH_JOIN en el plan del snowflake cambiaría, y qué sí cambiaría en el plan si dim_category fuera una tabla mucho más grande.

Ver solución

El número de operadores HASH_JOIN no cambiaría — seguirían siendo dos, porque la estructura de la consulta (cuántas tablas hay que unir para llegar de fact_orders a category_name) no depende del volumen de filas de ninguna tabla, solo de cuántos saltos de normalización existen entre ellas. Lo que sí cambiaría es la estimación de filas (~N rows) que aparece junto al SEQ_SCAN de dim_category — pasaría de ~3 rows a ~1000000 rows—, y esa estimación es exactamente el tipo de información que el optimizador de un motor de producción usa para decidir, por ejemplo, si construye la tabla hash a partir de dim_category o a partir del otro lado del JOIN. Ese nivel de afinación —cómo el optimizador decide el orden y la estrategia de ejecución en función del volumen real— es terreno de advanced-sql-querying-guide, no de esta lección.

Resumen y siguiente paso

En esta lección convertiste una afirmación conceptual —"el snowflake necesita un salto de JOIN más que el star"— en evidencia literal: el plan de EXPLAIN del star tiene un HASH_JOIN; el del snowflake tiene dos, con una dependencia adicional que el motor debe resolver antes de completar el JOIN principal. Verificaste, además, que ambos caminos producen exactamente el mismo resultado —106.15 de revenue—, confirmando que el costo adicional no compra ninguna corrección extra, solo un dato más consistente de mantener (lo que la lección 6 va a medir con números propios).

Antes de avanzar deberías poder: contar de memoria cuántos operadores HASH_JOIN tiene cada plan de esta lección; explicar la diferencia entre EXPLAIN y EXPLAIN ANALYZE, y por qué esta lección usa el primero; y describir, en una frase, qué prueba y qué no prueba comparar planes sobre un dataset de cuarenta filas.

La lección 4 da un paso atrás de lo estructural y entra en el argumento moderno: por qué la industria del warehouse columnar ha vuelto a considerar seriamente la tabla ancha (One Big Table) como una forma legítima, no como un atajo perezoso.

Recursos

← AnteriorNormalizando una dimensión: el snowflake schemaSiguiente →El argumento de la tabla ancha (One Big Table)