Módulo 5: Point In Time Joins And Deduplication

Presentación del módulo: uniendo hechos contra una dimensión que tiene historia

Siguiente →

Por qué existe este módulo

El módulo 4 cerró con una pregunta que dejó pendiente a propósito, en la última línea de su propio proyecto: "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?". dim_product_scd —cinco filas, P002 historizado en dos versiones que no se superponen en el tiempo— ya existe, verificada, con un assert que confirmó que la historia se preservó correctamente. Este módulo hace exactamente la pregunta que el módulo 4 dejó abierta, y la responde con evidencia ejecutada, no con intuición.

Hasta ahora, cada vez que esta guía unió fact_orders contra una dimensión, la dimensión tenía una sola versión por fila. El JOIN del módulo 2 (fact_orders con dim_store, dim_product, dim_date) nunca tuvo que preguntarse "¿cuál versión?", porque solo había una. Esa comodidad terminó en el módulo 4: dim_product_scd tiene dos filas para P002, cada una válida en un rango de fechas distinto. Un JOIN que ignore ese rango —que una product_id con product_id, sin más— ya no tiene una sola respuesta correcta: tiene dos filas candidatas, y elegir mal entre ellas no produce un error visible. Produce un número que se ve perfectamente razonable, y que está mal.

Este módulo enseña el patrón que resuelve ese problema —el join punto-en-el-tiempo, que compara la fecha de cada venta contra el rango de vigencia de cada versión de la dimensión, BETWEEN valid_from AND valid_to— y dos problemas relacionados que aparecen en cualquier warehouse que une hechos contra dimensiones históricas: qué hacer cuando una fila de dimensión llega tarde, después de que los hechos que debería describir ya se cargaron; y cómo deduplicar filas repetidas cuando una fuente reenvía datos, con ROW_NUMBER() y QUALIFY, más ANTI JOIN/SEMI JOIN para detectar con precisión qué cambió entre dos instantáneas. Al terminar, vas a poder unir cualquier hecho contra cualquier dimensión historizada sin inflar filas, sin perder revenue y sin atribuir una venta del pasado a una versión del presente.

Conexión con el módulo. Este módulo no modifica dim_product_scd ni fact_orders —las dos tablas siguen exactamente como las dejaron los módulos 1 y 4—. Lo que construye es el patrón de consulta correcto para unirlas, y lo contrasta, con números reales de Kiosko, contra dos formas de unirlas que parecen razonables y no lo son.

Una analogía: preguntar el precio del día que compraste, no el precio de hoy

Imagina que guardas el recibo de una tienda de conveniencia de hace tres meses, y quieres saber si pagaste un precio justo por una barra energética. ¿A quién le preguntas? No tiene sentido preguntarle al cajero de hoy "¿cuánto cuesta la barra energética?" —esa pregunta te da el precio de hoy, que puede ser distinto al de hace tres meses si el proveedor subió el costo o la tienda reclasificó el producto—. La pregunta correcta es "¿cuánto costaba la barra energética el día que la compré, según el recibo?". Esa pregunta tiene una sola respuesta correcta, y es una respuesta histórica, no la respuesta de hoy.

Eso es, exactamente, lo que un join punto-en-el-tiempo hace con fact_orders y dim_product_scd: para cada venta, no pregunta "¿cuál es la versión vigente ahora de este producto?" —esa es la pregunta que responde is_current = true, y es la pregunta equivocada para un reporte histórico—. Pregunta "¿cuál era la versión vigente el día de esta venta específica?", comparando order_ts contra el rango valid_from/valid_to de cada versión. La primera pregunta te da un precio de hoy aplicado, retroactivamente, a compras del pasado. La segunda te da el precio real que aplicaba el día que la venta ocurrió — exactamente como el recibo que guardaste.

Ejemplo trabajado: el mapa de este módulo, antes de construirlo

Antes de tocar el JOIN de verdad, vale la pena ver, de un vistazo, qué construye cada lección y en qué orden — el mismo tipo de mapa que abrieron los módulos 3 y 4 antes de comparar o historizar.

# join_module_map.py
CONCEPTS = [
    ("Join por llave natural sola", "product_id = product_id, sin filtrar version. Produce fan-out."),
    ("Join roto por is_current", "Evita el fan-out, pero atribuye TODO al presente. Silencioso."),
    ("Join punto-en-el-tiempo", "order_ts BETWEEN valid_from AND valid_to. La version correcta, siempre."),
    ("Llegada tardia de la dimension", "El hecho llega antes de que la SCD-2 capture el cambio real."),
    ("Deduplicacion con QUALIFY", "ROW_NUMBER() PARTITION BY llave ORDER BY criterio, QUALIFY = 1."),
    ("ANTI JOIN / SEMI JOIN", "Filas sin match (huerfanas) o filas que cambiaron, sin subconsulta."),
]

LESSONS = [
    ("Por que unir solo por product_id rompe la historia", "El fan-out, EJECUTADO: 40 filas -> 50"),
    ("El patron de join punto-en-el-tiempo", "Correcto vs roto, EJECUTADO: snacks vs health-snacks"),
    ("Dimensiones de llegada tardia", "Kimball late arriving dimension, EJECUTADO con ordenes fuera de semana"),
    ("De donde vienen las filas duplicadas", "Un lote reenviado, EJECUTADO: 40 -> 43 filas"),
    ("Deduplicando con ROW_NUMBER y QUALIFY", "43 -> 40 filas, EJECUTADO, grano restaurado"),
    ("Anti-joins para detectar que cambio", "ANTI JOIN nativo de DuckDB, EJECUTADO sobre 2 escenarios"),
    ("Proyecto: revenue historico correcto de Kiosko", "Las 6 lecciones anteriores integradas, EJECUTADO"),
]

print("=== Los seis conceptos centrales de este modulo ===\n")
for name, description in CONCEPTS:
    print(f"- {name}")
    print(f"  {description}\n")

print("=== Las siete lecciones que construyen sobre ellos ===\n")
for i, (name, description) in enumerate(LESSONS, start=2):
    print(f"L{i}. {name}")
    print(f"    {description}\n")

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

=== Los seis conceptos centrales de este modulo ===

- Join por llave natural sola
  product_id = product_id, sin filtrar version. Produce fan-out.

- Join roto por is_current
  Evita el fan-out, pero atribuye TODO al presente. Silencioso.

- Join punto-en-el-tiempo
  order_ts BETWEEN valid_from AND valid_to. La version correcta, siempre.

- Llegada tardia de la dimension
  El hecho llega antes de que la SCD-2 capture el cambio real.

- Deduplicacion con QUALIFY
  ROW_NUMBER() PARTITION BY llave ORDER BY criterio, QUALIFY = 1.

- ANTI JOIN / SEMI JOIN
  Filas sin match (huerfanas) o filas que cambiaron, sin subconsulta.

=== Las siete lecciones que construyen sobre ellos ===

L2. Por que unir solo por product_id rompe la historia
    El fan-out, EJECUTADO: 40 filas -> 50

L3. El patron de join punto-en-el-tiempo
    Correcto vs roto, EJECUTADO: snacks vs health-snacks

L4. Dimensiones de llegada tardia
    Kimball late arriving dimension, EJECUTADO con ordenes fuera de semana

L5. De donde vienen las filas duplicadas
    Un lote reenviado, EJECUTADO: 40 -> 43 filas

L6. Deduplicando con ROW_NUMBER y QUALIFY
    43 -> 40 filas, EJECUTADO, grano restaurado

L7. Anti-joins para detectar que cambio
    ANTI JOIN nativo de DuckDB, EJECUTADO sobre 2 escenarios

L8. Proyecto: revenue historico correcto de Kiosko
    Las 6 lecciones anteriores integradas, EJECUTADO

Fíjate en el orden: primero se muestra la forma de unir que rompe el grano —fan-out, filas de más (lección 2)—; después la forma que arregla el grano pero rompe la atribución —is_current, la trampa silenciosa (lección 3, junto con el patrón correcto)—; después, dos problemas que aparecen incluso con el JOIN correcto ya escrito —la dimensión que llega tarde (lección 4) y los duplicados que llegan de la fuente (lecciones 5 y 6)—; y finalmente una herramienta nueva —ANTI JOIN/SEMI JOIN— para detectar, con una consulta, qué cambió entre dos instantáneas (lección 7). El proyecto de cierre (lección 8) integra las seis piezas sobre el mismo fact_orders y dim_product_scd de siempre.

Diagrama: dónde estabas, dónde vas a estar

flowchart LR
    subgraph M4["Modulo 4 (ya escrito)"]
        A["dim_product_scd\n5 filas, P002 con 2 versiones\nvalid_from/valid_to/is_current"]
    end

    subgraph M5["Este modulo (5 de 8)"]
        B["L2: JOIN por product_id solo\nfan-out, EJECUTADO"]
        C["L3: JOIN punto-en-el-tiempo\nvs is_current, EJECUTADO"]
        D["L4: Llegada tardia\nde la dimension, EJECUTADO"]
        E["L5: De donde vienen\nlos duplicados, EJECUTADO"]
        F["L6: QUALIFY + ROW_NUMBER\nEJECUTADO"]
        G["L7: ANTI JOIN / SEMI JOIN\nEJECUTADO"]
        H["L8: Revenue historico\ncorrecto, EJECUTADO"]
    end

    subgraph M6["Modulo 6 (siguiente)"]
        I["Accumulating snapshot\nsobre fact_sessions"]
    end

    A --> B --> C --> D --> E --> F --> G --> H --> I

El mapa de este módulo

Leccion   Que construye
────────  ──────────────────────────────────────────────────────────────
L1        (esta) El mapa: los seis conceptos, antes de construirlos
L2        Por que product_id solo rompe la historia, EJECUTADO
L3        El patron de join punto-en-el-tiempo, EJECUTADO
L4        Dimensiones de llegada tardia, EJECUTADO
L5        De donde vienen las filas duplicadas, EJECUTADO
L6        Deduplicando con ROW_NUMBER y QUALIFY, EJECUTADO
L7        Anti-joins para detectar que cambio, EJECUTADO
L8        Proyecto: revenue historico correcto de Kiosko, EJECUTADO

Las lecciones 2 y 3 son la columna vertebral: la misma pregunta —¿cómo une fact_orders contra una dimensión con más de una versión por producto?— respondida tres veces, con tres JOIN distintos, para que veas en números por qué dos de los tres están mal aunque ninguno truene con un error. La lección 4 extiende el patrón correcto a un problema de tiempo real: qué pasa cuando el pipeline de la dimensión va más lento que los hechos que describe. Las lecciones 5 y 6 cambian de tema —de "cuál versión" a "cuántas veces"— y resuelven la deduplicación, con el mismo rigor de evidencia. La lección 7 agrega una herramienta —ANTI JOIN/SEMI JOIN— que sirve tanto para el problema de la lección 4 (facts huérfanos) como para un problema nuevo (detectar qué cambió antes de aplicar un MERGE). La lección 8 integra todo.

Profundización: por qué este módulo necesita dim_product_scd ya historizada

Podría parecer que un join punto-en-el-tiempo es un tema independiente, enseñable con cualquier tabla de ejemplo con un par de fechas. Este módulo resiste esa tentación por una razón concreta: no existe una diferencia observable entre un join correcto y uno roto si la dimensión nunca tuvo más de una versión. Con dim_product —la versión star del módulo 2, sin historia, una fila por producto— cualquiera de las tres formas de unir que este módulo compara (product_id solo, is_current, punto-en-el-tiempo) produce exactamente el mismo resultado, porque solo hay una fila candidata por producto. La diferencia solo aparece, con evidencia, cuando existe una dimensión con más de una versión por llave natural — exactamente lo que dim_product_scd es, desde que el módulo 4 historizó P002.

Esto no es un detalle menor: es la razón por la que esta guía dedicó un módulo completo (el 4) a construir la dimensión historizada antes de enseñar a unirla correctamente. Sin dim_product_scd ya construida y verificada —cinco filas, P002 con dos versiones que no se superponen en el tiempo—, este módulo no tendría ningún escenario real sobre el cual demostrar el problema. fact_orders, por su parte, aporta la otra mitad del escenario: las cuarenta órdenes de la semana del 3 al 9 de agosto de 2026, todas anteriores al cambio de P002 (15 de agosto). Esa fecha no es arbitraria — significa que, si el JOIN está mal, cada una de las diez órdenes de P002 de esa semana va a caer en la versión equivocada, no solo algunas. El error es completo, no parcial, y por eso es fácil de medir con precisión en las lecciones que siguen.

Errores comunes

Pensar que este módulo va a modificar dim_product_scd o fact_orders. Qué pasa: alguien, al ver "join punto-en-el-tiempo" en el título del módulo, espera que se agreguen columnas nuevas a alguna de las dos tablas, o que se corrija algo que el módulo 4 dejó "incompleto". Por qué pasa: después de un módulo entero construyendo una tabla, es natural esperar que el siguiente módulo siga construyendo sobre ella en el mismo sentido. Cómo detectarlo: si esperas que dim_product_scd termine este módulo con una columna distinta a las ocho que ya tiene (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current), tienes esta confusión. Cómo corregirlo: dim_product_scd y fact_orders son, para este módulo, datos de entrada fijos — el módulo 4 ya las dejó completas y verificadas. Lo que este módulo construye es exclusivamente la consulta que las une correctamente, sin tocar ninguna de las dos.

Asumir que "el join está roto" significa que va a fallar o dar un error. Qué pasa: alguien espera que un JOIN mal escrito produzca un mensaje de error de DuckDB, una excepción de Python, o al menos un valor NULL visible. Por qué pasa: en la mayoría de los lenguajes de programación, un error de lógica sí produce un error visible tarde o temprano. Cómo detectarlo: si en la lección 3 esperas que el JOIN roto (is_current = true) truene con algún error, vas a sorprenderte cuando corre perfectamente y devuelve cuarenta filas, ni una de más ni de menos. Cómo corregirlo: un JOIN mal atribuido en un modelo dimensional es, casi siempre, un error silencioso — produce un resultado con la forma correcta (mismo número de filas, mismos tipos de columna) pero con el contenido equivocado. Es exactamente el tipo de error que una consulta de verificación —como el conteo de categoría que vas a construir en la lección 3— existe para atrapar, porque nada más lo va a atrapar por ti.

Confundir "revenue total correcto" con "atribución correcta". Qué pasa: alguien corre el JOIN roto de la lección 3, ve que SUM(revenue) da 106.15 —el mismo número de siempre—, y concluye que el JOIN está bien porque "el total cuadra". Por qué pasa: el revenue total de Kiosko (106.15) es un número tan familiar, repetido en cada módulo de esta guía, que verlo de nuevo se siente como una confirmación completa. Cómo detectarlo: si tu verificación de un JOIN contra una dimensión historizada se limita a SUM(revenue) sin desglosar por ninguna columna de la dimensión (category, en este caso), no probaste lo que un join punto-en-el-tiempo existe para probar. Cómo corregirlo: revenue vive en fact_orders, ya calculado (quantity * unit_price) — ningún JOIN contra dim_product_scd, correcto o roto, puede cambiar ese número, porque no participa en su cálculo. Lo que sí cambia con un JOIN mal atribuido es todo lo que depende de una columna de la dimensión: category (para agrupar), unit_cost (para calcular margen). La lección 3 mide exactamente eso.

Ejercicios

Ejercicio 1 — Recuerda las dos filas exactas del checklist que este módulo resuelve. Sin releer el proyecto del módulo 4, escribe de memoria el nombre exacto de las dos filas del checklist (introducido en el módulo 1, lección 2) que corresponden a este módulo.

Ver solución

Las dos filas dicen, textualmente: "Join punto-en-el-tiempo contra una dimensión historizada" y "Deduplicación explícita de filas repetidas". A diferencia del módulo 4 —que resolvió una sola fila del checklist (historización)—, este módulo resuelve dos, porque ambas comparten el mismo tipo de evidencia: una consulta ejecutada sobre datos reales de Kiosko que demuestra, con números, la diferencia entre hacerlo bien y hacerlo mal.

Ejercicio 2 — Explica, en tus propias palabras, por qué dim_product (la versión star, sin historia) no sirve para demostrar el problema de este módulo. Sin mirar la profundización de esta lección, escribe 2-3 frases explicando qué le falta a dim_product para que un join punto-en-el-tiempo y un join por product_id solo produzcan resultados distintos.

Ver solución

dim_product tiene exactamente una fila por product_id — nunca tuvo un cambio real que historizar, así que cualquier forma de unirla (product_id solo, is_current, o un rango de fechas) encuentra, siempre, la misma única fila candidata. Sin una segunda versión de algún producto, no hay ninguna decisión que un JOIN pueda tomar mal: no existe una fila "equivocada" para elegir. El problema de este módulo solo existe quando la dimensión tiene más de una fila candidata por llave natural — exactamente lo que dim_product_scd tiene, desde que el módulo 4 historizó P002.

Ejercicio 3 — Predice, antes de la lección 2, cuántas filas producirá un JOIN entre fact_orders (40 filas) y dim_product_scd (5 filas) usando solo f.product_id = d.product_id, sin ningún otro filtro. Usa lo que ya sabes: dim_product_scd tiene 5 filas para 4 productos distintos (uno de ellos, P002, con 2 versiones). Explica tu razonamiento en 2-3 frases.

Ver solución

Más de 40. Cada orden de P001, P003 o P004 encuentra exactamente una fila candidata en dim_product_scd (esos tres productos tienen una sola versión), así que se une una vez, sin cambios. Pero cada orden de P002 encuentra dos filas candidatas —las dos versiones históricas—, y un JOIN sin ningún filtro adicional las empareja con ambas, produciendo dos filas de resultado por cada orden de P002 en vez de una. Si fact_orders tiene diez órdenes de P002 (una cifra que vas a confirmar con una consulta en la lección 2), el total esperado es 40 + 10 = 50 filas, no 40. La lección 2 confirma este número exacto, ejecutado.

Resumen y siguiente paso

Este módulo toma fact_orders (40 filas, sin cambios desde el módulo 1) y dim_product_scd (5 filas, historizada desde el módulo 4) y responde la pregunta que el módulo 4 dejó abierta a propósito: ¿cómo se une un hecho contra una dimensión con más de una versión por producto, sin inflar filas ni atribuir mal la historia? Vas a ver, con evidencia ejecutada, tres formas de intentarlo —dos rotas, una correcta—, dos problemas adicionales que aparecen incluso con la forma correcta ya escrita (llegada tardía de la dimensión, filas duplicadas de la fuente), y una herramienta nueva (ANTI JOIN/SEMI JOIN) para detectar cambios sin subconsultas.

Antes de avanzar deberías poder: nombrar los seis conceptos centrales de este módulo y qué problema resuelve cada uno; explicar por qué dim_product (sin historia) no puede demostrar ninguno de estos problemas; y decir de memoria las dos filas exactas del checklist del módulo 1 que este módulo resuelve.

La lección 2 empieza por el error más visible de los tres: unir fact_orders contra dim_product_scd usando solo product_id, sin ningún filtro de versión, y medir exactamente cuántas filas de más produce.

Recursos

← AnteriorMini-proyecto: dim_product historizada de KioskoSiguiente →Por qué unir solo por product_id rompe la historia