Por qué unir solo por product_id rompe la historia

Descripción

Esta lección hace lo primero que cualquiera intentaría al unir fact_orders contra dim_product_scd: un JOIN por la llave que las conecta, product_id, sin ningún filtro adicional. Con dim_product —la versión star del módulo 2— esto funcionó perfectamente, cientos de veces, en los tres módulos anteriores. Con dim_product_scd —la versión historizada del módulo 4— produce algo distinto: más filas de las que entraron. Vas a construir ese JOIN, contarlo, y confirmar exactamente de dónde salen las filas de más.

Conexión con el módulo. Esta es la lección que hace visible, con un número concreto, el problema que la introducción del módulo solo describió en palabras. No es todavía la solución —esa es la lección 3—; es la evidencia de que la forma más obvia de unir dos tablas relacionadas por una llave natural deja de ser suficiente en cuanto una de las dos tiene más de una fila por llave.

Una analogía: preguntar por apellido en una familia con dos personas del mismo nombre

Imagina que buscas el archivo de "García" en una oficina de registro civil, y la oficina, sin darte cuenta, tiene dos expedientes distintos con el apellido García —un padre y un hijo, ambos vivos, ambos con expedientes activos—. Si tu pregunta es simplemente "dame el expediente de García", sin especificar cuál, la respuesta correcta no es "aquí está uno" — es, con toda razón, "aquí están los dos, porque tu pregunta no distinguió entre ellos". No es un error del sistema: es exactamente lo que le pediste. El error está en la pregunta, no en la respuesta.

Eso es, con precisión, lo que pasa al unir fact_orders contra dim_product_scd solo por product_id. dim_product_scd tiene dos filas con product_id = 'P002' —la versión vieja (snacks, cerrada) y la nueva (health-snacks, vigente)—, exactamente como los dos García del ejemplo. Preguntar "dame la fila de P002" sin especificar cuál versión no tiene una sola respuesta correcta: DuckDB, con toda razón, te da las dos.

Ejemplo trabajado: el JOIN sin filtro, contado

Primero, las dos tablas de entrada de este módulo, ambas heredadas sin cambios: fact_orders (40 filas, del módulo 1) y dim_product_scd (5 filas, del módulo 4). Si ya tienes kiosko.py, raw_orders.py en tu carpeta de trabajo, reutilízalos tal cual; aquí se agrega dim_product_scd.py, el archivo nuevo de este módulo, con el estado final exacto que dejó el proyecto del módulo 4:

# dim_product_scd.py -- el estado final que dejo el proyecto del modulo 4 (leccion 8)
# (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current)
DIM_PRODUCT_SCD_ROWS = [
    (1, "P001", "Bottled Water 600ml", "beverages", 0.40, "2026-08-01", None, True),
    (2, "P002", "Energy Bar", "snacks", 0.60, "2026-08-01", "2026-08-14", False),
    (5, "P002", "Energy Bar", "health-snacks", 0.68, "2026-08-15", None, True),
    (3, "P003", "Instant Coffee Sachet", "beverages", 0.35, "2026-08-01", None, True),
    (4, "P004", "Phone Charger Cable", "electronics", 2.10, "2026-08-01", None, True),
]

Fíjate en que esta lista no reconstruye la historización con MERGE INTO —eso ya lo hizo, y lo verificó con un assert, el proyecto del módulo 4—. Aquí se carga directamente, como un dato de entrada fijo, porque el foco de este módulo es el JOIN, no la construcción de la SCD. Si quieres reconstruirla desde cero con MERGE INTO, tal como la enseñó el módulo 4, esa lección sigue ahí, sin cambios.

Ahora, el script completo de esta lección: reconstruye fact_orders, carga dim_product_scd, y ejecuta el JOIN sin filtro.

# naive_join.py
from datetime import datetime
import duckdb

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

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

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 (?, ?, ?, ?, ?, ?, ?, ?)", DIM_PRODUCT_SCD_ROWS)

print("=== Grano de referencia: fact_orders, antes de cualquier JOIN ===")
print(con.sql("SELECT COUNT(*) AS total_rows FROM fact_orders"))

print("\n=== Cuantas versiones tiene cada producto en dim_product_scd ===")
print(con.sql("""
    SELECT product_id, COUNT(*) AS dim_versions
    FROM dim_product_scd
    GROUP BY product_id
    ORDER BY product_id
"""))

print("\n=== El JOIN ingenuo: solo por product_id, sin filtrar version ===")
print(con.sql("""
    SELECT COUNT(*) AS joined_rows
    FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id
"""))

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

=== Grano de referencia: fact_orders, antes de cualquier JOIN ===
┌────────────┐
│ total_rows │
│   int64    │
├────────────┤
│         40 │
└────────────┘

=== Cuantas versiones tiene cada producto en dim_product_scd ===
┌────────────┬──────────────┐
│ product_id │ dim_versions │
│  varchar   │    int64     │
├────────────┼──────────────┤
│ P001       │            1 │
│ P002       │            2 │
│ P003       │            1 │
│ P004       │            1 │
└────────────┴──────────────┘

=== El JOIN ingenuo: solo por product_id, sin filtrar version ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          50 │
└─────────────┘

fact_orders entra con 40 filas. El JOIN sin filtro sale con 50. Diez filas de más — ni una al azar, sino exactamente las diez órdenes de P002 que existen en la semana de Kiosko, cada una duplicada porque encontró dos filas candidatas en vez de una. Ningún error, ninguna advertencia: DuckDB hizo exactamente lo que la consulta le pidió, encontrar todas las filas de dim_product_scd cuyo product_id coincida con el de cada orden — y para P002, esas son dos.

Diagrama: de dónde salen las diez filas de más

flowchart TD
    A["fact_orders: 40 filas\n10 de ellas con product_id = P002"] --> C{"JOIN ON f.product_id = d.product_id\nsin ningun otro filtro"}
    B["dim_product_scd: 5 filas\nP001, P003, P004 con 1 version\nP002 con 2 versiones"] --> C
    C -->|"P001, P003, P004\n1 match cada uno"| D["30 filas de resultado\n(sin cambios)"]
    C -->|"P002\n2 matches cada uno"| E["20 filas de resultado\n(10 ordenes x 2 versiones)"]
    D --> F["Total: 50 filas\n(40 esperadas + 10 de mas)"]
    E --> F

Verifica el fan-out fila por fila, para las diez órdenes de P002 exactas:

print("=== P002 se duplica: 10 ordenes x 2 versiones = 20 filas ===")
print(con.sql("""
    SELECT f.order_id, d.product_key, d.category, d.unit_cost
    FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id
    WHERE f.product_id = 'P002'
    ORDER BY f.order_id, d.product_key
"""))

=== P002 se duplica: 10 ordenes x 2 versiones = 20 filas ===
┌──────────┬─────────────┬───────────────┬───────────┐
│ order_id │ product_key │   category    │ unit_cost │
│ varchar  │    int32    │    varchar    │  double   │
├──────────┼─────────────┼───────────────┼───────────┤
│ ORD-1002 │           2 │ snacks        │       0.6 │
│ ORD-1002 │           5 │ health-snacks │      0.68 │
│ ORD-1006 │           2 │ snacks        │       0.6 │
│ ORD-1006 │           5 │ health-snacks │      0.68 │
│ ORD-2001 │           2 │ snacks        │       0.6 │
│ ORD-2001 │           5 │ health-snacks │      0.68 │
│ ORD-2005 │           2 │ snacks        │       0.6 │
│ ORD-2005 │           5 │ health-snacks │      0.68 │
│ ORD-4003 │           2 │ snacks        │       0.6 │
│ ORD-4003 │           5 │ health-snacks │      0.68 │
│ ORD-5001 │           2 │ snacks        │       0.6 │
│ ORD-5001 │           5 │ health-snacks │      0.68 │
│ ORD-5005 │           2 │ snacks        │       0.6 │
│ ORD-5005 │           5 │ health-snacks │      0.68 │
│ ORD-6002 │           2 │ snacks        │       0.6 │
│ ORD-6002 │           5 │ health-snacks │      0.68 │
│ ORD-6006 │           2 │ snacks        │       0.6 │
│ ORD-6006 │           5 │ health-snacks │      0.68 │
│ ORD-7002 │           2 │ snacks        │       0.6 │
│ ORD-7002 │           5 │ health-snacks │      0.68 │
└──────────┴─────────────┴───────────────┴───────────┘
  20 rows                                  4 columns

Cada order_id de P002 aparece exactamente dos veces: una vez emparejado con product_key = 2 (la versión cerrada, snacks), otra vez con product_key = 5 (la versión vigente, health-snacks). Ninguna de las dos filas es "la equivocada" desde el punto de vista de DuckDB — ambas cumplen la condición f.product_id = d.product_id con total legitimidad. El error no está en el motor: está en una condición de JOIN que no alcanza a distinguir entre las dos versiones que existen.

Profundización: por qué esto es peligroso incluso cuando nadie lo nota

El peligro real de este JOIN no es que produzca 50 filas en vez de 40 —ese número es fácil de detectar con una verificación de grano, exactamente como la que el módulo 1 enseñó—. El peligro real aparece cuando alguien agrega sobre el resultado sin verificar el conteo de filas primero. Si corres SUM(revenue) directamente sobre este JOIN, sin haber contado las filas antes, el revenue de cada orden de P002 se suma dos veces — una por cada fila duplicada—, y el total deja de ser 106.15 para convertirse en un número más alto, que sigue pareciendo perfectamente razonable si no sabes que debería ser 106.15.

print("=== Revenue inflado si se agrega sobre el JOIN ingenuo, sin verificar el conteo antes ===")
print(con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) AS total_revenue_inflado, COUNT(*) AS filas
    FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id
"""))

=== Revenue inflado si se agrega sobre el JOIN ingenuo, sin verificar el conteo antes ===
┌───────────────────────┬───────┐
│ total_revenue_inflado │ filas │
│        double         │ int64 │
├───────────────────────┼───────┤
│                127.75 │    50 │
└───────────────────────┴───────┘

127.75 en vez de 106.15 — una diferencia de 21.60, que no es casualidad: es exactamente el revenue de las diez órdenes de P002 (21.60, el mismo número que vas a volver a ver en la lección 3), contado una vez de más porque cada una de esas diez órdenes se duplicó. Un reporte de revenue construido sobre este JOIN, sin la verificación de grano de por medio, reportaría un negocio 21.60 más grande de lo que realmente es — un error que ninguna alarma técnica dispara, porque 127.75 es un número perfectamente creíble para alguien que no conoce el 106.15 de referencia.

Este es, en esencia, el mismo tipo de fan-out que un JOIN mal diseñado produce en cualquier modelo relacional —una fila del lado "uno" que en realidad tiene más de una contraparte del lado "muchos"—, solo que aquí la causa específica es una dimensión historizada: cada versión adicional de un producto es, para efectos de un JOIN sin filtro, una fila más con la que emparejar. Cuantas más columnas historice dim_product_scd con el tiempo —si Kiosko algún día tuviera diez productos con cambios frecuentes—, más severo se vuelve el fan-out si nadie corrige la condición del JOIN.

Errores comunes

Confiar en que "el JOIN corrió sin errores" es suficiente evidencia de que está bien. Qué pasa: alguien escribe JOIN dim_product_scd d ON f.product_id = d.product_id, lo corre, ve un resultado con columnas razonables y ningún mensaje de error, y da el JOIN por bueno. Por qué pasa: en muchos contextos de programación, "corrió sin errores" sí es una señal fuerte de corrección. En SQL, un JOIN casi nunca falla por una condición incompleta — simplemente devuelve más o menos filas de las que alguien esperaba, sin ninguna advertencia. Cómo detectarlo: compara siempre COUNT(*) del resultado del JOIN contra COUNT(*) de la tabla de hechos original — si no coinciden, tienes fan-out (más filas) o filas perdidas (menos filas), exactamente el mismo principio de verificación de grano que el módulo 1 enseñó para fact_orders por sí sola. Cómo corregirlo: nunca declares un JOIN correcto sin esa comparación — es la primera línea de defensa contra el fan-out, antes incluso de pensar en la lógica del filtro correcto (lección 3).

Pensar que el fan-out es un bug de DuckDB o un caso raro. Qué pasa: alguien, sorprendido por las 50 filas, sospecha que hay un error en el motor o en los datos de entrada, en vez de reconocer que el JOIN, tal como está escrito, describe con precisión lo que pidió. Por qué pasa: 40 filas que entran y 50 que salen se siente contraintuitivo si no se piensa explícitamente en cuántas filas candidatas tiene cada product_id del lado de la dimensión. Cómo detectarlo: antes de sospechar del motor, cuenta las versiones por product_id en la tabla de dimensión —exactamente la consulta GROUP BY product_id de esta lección—; si algún product_id tiene más de una fila, el fan-out no es un bug, es aritmética esperada. Cómo corregirlo: cualquier vez que unas contra una tabla que podría tener más de una fila por llave natural —una dimensión historizada, una tabla con reintentos, un catálogo con versiones—, cuenta las versiones por llave antes de escribir el JOIN, no después de sorprenderte con el resultado.

Agregar (SUM, AVG, COUNT) sobre el resultado de un JOIN sin verificar primero el conteo de filas. Qué pasa: alguien salta directo a SELECT SUM(revenue) FROM fact_orders f JOIN dim_product_scd d ON ..., sin correr antes un COUNT(*) de control. Por qué pasa: el objetivo final —el revenue total, o por categoría— parece más importante que el paso intermedio de contar filas, y se siente como un paso que se puede saltar para llegar más rápido a la respuesta. Cómo detectarlo: si tu consulta final incluye una función de agregación sobre un JOIN, y nunca corriste, en algún punto del proceso, un COUNT(*) sobre ese mismo JOIN para comparar contra el conteo de la tabla de hechos original, tu resultado agregado no tiene ninguna garantía de estar bien. Cómo corregirlo: la disciplina de esta lección —contar antes de agregar— no es un paso extra opcional: es la única forma de detectar un fan-out antes de que contamine un número que alguien va a usar para tomar una decisión de negocio.

Ejercicios

Ejercicio 1 — Confirma que P001, P003 y P004 no sufren fan-out. Usando el fact_orders y dim_product_scd ya construidos, escribe una consulta que compare, para cada product_id que no sea P002, el número de órdenes en fact_orders contra el número de filas que produce el JOIN sin filtro para ese mismo producto.

print(con.sql("""
    SELECT
        f.product_id,
        COUNT(DISTINCT f.order_id) AS orders_in_fact,
        COUNT(*) AS rows_after_join
    FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id
    WHERE f.product_id != 'P002'
    GROUP BY f.product_id
    ORDER BY f.product_id
"""))

┌────────────┬─────────────────┬──────────────────┐
│ product_id │ orders_in_fact  │ rows_after_join   │
│  varchar   │      int64      │       int64       │
├────────────┼─────────────────┼───────────────────┤
│ P001       │              16 │                16 │
│ P003       │               7 │                 7 │
│ P004       │               7 │                 7 │
└────────────┴─────────────────┴───────────────────┘

Ejercicio 2 — Calcula cuántas filas produciría el fan-out si P002 tuviera una tercera versión. Usando el resultado del ejercicio 2 de la lección 5 del módulo 4 (P002 sube a 0.72 el 2026-08-25, una tercera versión), predice cuántas filas produciría el JOIN sin filtro de esta lección si esa tercera versión ya estuviera en dim_product_scd. No lo ejecutes — razónalo con los números que ya tienes.

Ejercicio 3 — Explica por qué COUNT(DISTINCT f.order_id) después del JOIN sin filtro sigue dando 40, aunque COUNT(*) dé 50. En 2-3 frases, explica esta aparente contradicción usando lo que aprendiste sobre qué produce exactamente el fan-out.

Resumen y siguiente paso

Esta lección construyó el JOIN más obvio entre fact_orders y dim_product_scd —unir solo por product_id, sin ningún filtro adicional— y confirmó, con una consulta ejecutada, que produce fan-out: 50 filas en vez de 40, las diez de más correspondientes, exactamente, a las diez órdenes de P002 que existen en la semana de Kiosko, cada una emparejada con las dos versiones históricas del producto. Confirmaste, además, que agregar sin verificar el conteo primero infla el revenue de 106.15 a 127.75 — un error silencioso, sin ningún mensaje de advertencia.

Antes de avanzar deberías poder: explicar de memoria por qué un JOIN sin filtro contra una dimensión historizada produce fan-out; recitar el número exacto de filas de más que produce en dim_product_scd (diez, una por cada orden de P002); y aplicar la disciplina de "contar antes de agregar" a cualquier JOIN nuevo que escribas de aquí en adelante.

La lección 3 corrige el fan-out —pero muestra, con la misma disciplina de evidencia, que la forma más común de corregirlo (is_current = true) no es la forma correcta: elimina las filas de más, pero atribuye todas las ventas de P002 a la categoría y el costo del presente, sin importar cuándo ocurrió cada venta. El patrón que sí resuelve el problema completo —el join punto-en-el-tiempo— es el tema central de esa lección.

Recursos

• Kimball Group — "Slowly Changing Dimension Type 2" — la definición formal de por qué dim_product_scd tiene más de una fila por product_id, la causa raíz del fan-out de esta lección. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. En inglés.
• DuckDB — documentación de las cláusulas FROM y JOIN, la referencia de sintaxis para el JOIN que esta lección construye y mide. duckdb.org/docs/current/sql/query_syntax/from. En inglés.
• DuckDB — documentación oficial del cliente Python, usada para construir y consultar fact_orders y dim_product_scd en esta lección. duckdb.org/docs/current/clients/python/overview. En inglés.