Módulo 5: Point In Time Joins And Deduplication

El patrón de join punto-en-el-tiempo

Siguiente →

Descripción

La lección 2 mostró un JOIN roto de una forma fácil de detectar: produce más filas de las que debería, y una simple comparación de conteos lo delata. Esta lección muestra algo más difícil: un JOIN que corrige el problema de filas de más —filtrando por is_current = true— pero que sigue estando mal, de una forma que ningún conteo de filas puede detectar. Y, junto a él, construye el JOIN que sí es correcto: el que compara la fecha de cada venta contra el rango de vigencia de cada versión de la dimensión. Vas a correr los dos, lado a lado, sobre las mismas cuarenta órdenes de Kiosko, y vas a ver la diferencia exacta que cada uno produce.

Conexión con el módulo. Esta es la lección central del módulo. La lección 2 preparó el terreno mostrando por qué "unir sin filtro" no alcanza; esta lección resuelve el problema completo, con el patrón que el diseño de esta guía nombra explícitamente: f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, '9999-12-31'). Todo lo que sigue en este módulo —llegada tardía (lección 4), deduplicación (lecciones 5 y 6), anti-joins (lección 7)— asume que ya entendiste, con evidencia, por qué este patrón es el correcto.

Una analogía: el recibo, no el mostrador de hoy

Retoma la analogía de la introducción del módulo: quieres saber cuánto pagaste por una barra energética hace tres semanas. is_current = true es como caminar hasta el mostrador de la tienda hoy y preguntar el precio actual —una respuesta rápida, siempre disponible, y sistemáticamente equivocada para cualquier compra que no sea de hoy mismo—. El join punto-en-el-tiempo es como sacar el recibo de esa compra, leer la fecha impresa, y comparar esa fecha contra el historial de precios de la tienda para encontrar cuál precio aplicaba exactamente ese día. La pregunta "¿cuál es la versión vigente ahora?" y la pregunta "¿cuál era la versión vigente el día de esta venta?" tienen, casi siempre, la misma respuesta —cuando la venta es reciente—, y exactamente la respuesta opuesta cuando la venta es anterior a un cambio real, como le pasa a las cuarenta órdenes de Kiosko frente al cambio de P002 del 15 de agosto.

Ejemplo trabajado: el JOIN roto y el JOIN correcto, lado a lado

Reconstruye fact_orders y dim_product_scd exactamente como en la lección 2 —mismo kiosko.py, raw_orders.py, dim_product_scd.py—. Con las dos tablas cargadas, corre primero el JOIN que "parece" arreglar el fan-out de la lección anterior:

# broken_vs_correct_join.py
# (fact_orders y dim_product_scd ya cargados como en la leccion 2)

print("=== JOIN roto: is_current = true (evita el fan-out, pero fija la version en 'hoy') ===")
print(con.sql("""
    SELECT COUNT(*) AS joined_rows
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND d.is_current = true
"""))
=== JOIN roto: is_current = true (evita el fan-out, pero fija la version en 'hoy') ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          40 │
└─────────────┘

Cuarenta filas — exactamente el número correcto, sin fan-out. El filtro is_current = true hace su trabajo: para cada product_id, elige una sola fila de dim_product_scd, la vigente. Es fácil, viendo solo este número, dar el JOIN por bueno. No lo es. Fíjate en cuál versión eligió para P002:

print("=== A cual version cae CADA orden de P002, con el filtro is_current ===")
print(con.sql("""
    SELECT DISTINCT d.product_key, d.category, d.unit_cost
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND d.is_current = true
    WHERE f.product_id = 'P002'
"""))
=== A cual version cae CADA orden de P002, con el filtro is_current ===
┌─────────────┬───────────────┬───────────┐
│ product_key │   category    │ unit_cost │
│    int32    │    varchar    │  double   │
├─────────────┼───────────────┼───────────┤
│           5 │ health-snacks │      0.68 │
└─────────────┴───────────────┴───────────┘

Todas las diez órdenes de P002 —las cuarenta órdenes de Kiosko son de la semana del 3 al 9 de agosto de 2026, y el cambio de P002 ocurrió el 15 de agosto— caen en product_key = 5, la versión health-snacks/0.68. Ese cambio todavía no había ocurrido cuando esas ventas sucedieron. El JOIN con is_current = true no sabe eso, ni puede saberlo: is_current describe el estado de la dimensión ahora, en el momento en que corres la consulta — no tiene ninguna forma de mirar hacia atrás y preguntar cuál era el estado el día de cada venta.

Ahora, el JOIN correcto: en vez de is_current = true, compara order_ts contra el rango de vigencia de cada versión.

print("\n=== JOIN correcto: punto-en-el-tiempo, BETWEEN valid_from y valid_to ===")
print(con.sql("""
    SELECT COUNT(*) AS joined_rows
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
"""))

print("\n=== A cual version cae CADA orden de P002, con el JOIN punto-en-el-tiempo ===")
print(con.sql("""
    SELECT DISTINCT d.product_key, d.category, d.unit_cost
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    WHERE f.product_id = 'P002'
"""))
=== JOIN correcto: punto-en-el-tiempo, BETWEEN valid_from y valid_to ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          40 │
└─────────────┘

=== A cual version cae CADA orden de P002, con el JOIN punto-en-el-tiempo ===
┌─────────────┬──────────┬───────────┐
│ product_key │ category │ unit_cost │
│    int32    │ varchar  │  double   │
├─────────────┼──────────┼───────────┤
│           2 │ snacks   │      0.6  │
└─────────────┴──────────┴───────────┘

También cuarenta filas —el join punto-en-el-tiempo no produce fan-out, exactamente igual que is_current—, pero esta vez todas las órdenes de P002 caen en product_key = 2, la versión snacks/0.60 — la versión que estaba vigente cuando esas ventas realmente ocurrieron. Los dos JOIN producen el mismo número de filas. Solo uno de los dos produce la categoría y el costo correctos.

Diagrama: por qué BETWEEN encuentra la versión correcta

flowchart TD
    A["order_ts de una venta de P002\n(3 al 9 de agosto de 2026)"] --> C{"BETWEEN valid_from AND\nCOALESCE(valid_to, 9999-12-31)"}
    B1["Version 1: snacks / 0.60\nvalid_from=2026-08-01\nvalid_to=2026-08-14"] --> C
    B2["Version 2: health-snacks / 0.68\nvalid_from=2026-08-15\nvalid_to=NULL"] --> C
    C -->|"order_ts cae DENTRO\nde este rango"| D["Version 1 (snacks) -- MATCH correcto"]
    C -->|"order_ts NO cae\nen este rango"| E["Version 2 -- descartada para esta orden"]

Linea de tiempo de P002 -- por que las 40 ordenes SIEMPRE caen en la version 1
──────────────────────────────────────────────────────────────────────────────
2026-08-01 ─────────────────────── 2026-08-14 │ 2026-08-15 ─────────────────>
│◄── Version 1: snacks / 0.60 (valid_to) ──►│  │◄── Version 2: health-snacks / 0.68 ──►
│                                            │  │           (is_current = true)
│    ▲    ▲   ▲  ▲  ▲   ▲  ▲   ▲   ▲   ▲     │  │
│  las 10 ordenes de P002 de la semana       │  │
│  (3 al 9 de agosto) caen TODAS aqui        │  │

Profundización: la misma plata, distinta categoría — y distinto margen

Los dos JOIN de esta lección devuelven cuarenta filas, así que una verificación de grano por sí sola —la que la lección 2 enseñó— no distingue entre ellos. Necesitas una verificación distinta: agregar por una columna de la dimensión, y comparar. Agrupa por category —viene de dim_product_scd, no de fact_orders— y calcula, además del revenue, el margen (revenue - quantity * unit_cost, usando unit_cost, que también viene de la dimensión):

print("\n=== Revenue y margen por categoria -- JOIN roto (is_current) ===")
print(con.sql("""
    SELECT
        d.category,
        ROUND(SUM(f.revenue), 2) AS revenue,
        ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
    GROUP BY d.category
    ORDER BY d.category
"""))

print("\n=== Revenue y margen por categoria -- JOIN correcto (punto-en-el-tiempo) ===")
print(con.sql("""
    SELECT
        d.category,
        ROUND(SUM(f.revenue), 2) AS revenue,
        ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    GROUP BY d.category
    ORDER BY d.category
"""))
=== Revenue y margen por categoria -- JOIN roto (is_current) ===
┌───────────────┬─────────┬────────┐
│   category    │ revenue │ margin │
│    varchar    │ double  │ double │
├───────────────┼─────────┼────────┤
│ beverages     │   44.05 │  14.75 │
│ electronics   │    40.5 │   21.6 │
│ health-snacks │    21.6 │   9.36 │
└───────────────┴─────────┴────────┘

=== Revenue y margen por categoria -- JOIN correcto (punto-en-el-tiempo) ===
┌─────────────┬─────────┬────────┐
│  category   │ revenue │ margin │
│   varchar   │ double  │ double │
├─────────────┼─────────┼────────┤
│ beverages   │   44.05 │  14.75 │
│ electronics │    40.5 │   21.6 │
│ snacks      │    21.6 │   10.8 │
└─────────────┴─────────┴────────┘

beverages y electronics son idénticos en las dos tablas —ninguno de esos productos cambió nunca, así que ambos JOIN los tratan igual—. La diferencia está toda en la última fila: el JOIN roto reporta 21.6 de revenue bajo la categoría health-snacks, con un margen de 9.36; el JOIN correcto reporta el mismo 21.6 de revenue, pero bajo la categoría snacks, con un margen de 10.8. Fíjate en lo que no cambia y en lo que sí:

  • El revenue de 21.6 es idéntico en ambos casos — no es casualidad. revenue vive en fact_orders, ya calculado como quantity * unit_price en el momento de la venta; ningún JOIN contra dim_product_scd puede alterarlo, correcto o roto, porque no participa en su cálculo.
  • La categoría cambia de health-snacks (roto) a snacks (correcto) — un reporte de "revenue por categoría" construido con el JOIN roto le atribuiría a health-snacks una plata que, la semana del cambio, esa categoría ni siquiera existía todavía en las ventas reales de Kiosko.
  • El margen cambia de 9.36 (roto) a 10.8 (correcto) — una diferencia de 1.44, exactamente la que produce usar unit_cost = 0.68 (el costo nuevo) en vez de unit_cost = 0.60 (el costo real de esas ventas). Un reporte de rentabilidad construido con el JOIN roto subestimaría el margen real de esas ventas en 1.44, porque le aplica retroactivamente un costo que todavía no regía.

Confirma el revenue total, para cerrar el círculo con el número de siempre:

print("\n=== Revenue total: identico en ambos casos ===")
print(con.sql("""
    SELECT
        (SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
         JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true) AS revenue_roto,
        (SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
         JOIN dim_product_scd d ON f.product_id = d.product_id
            AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')) AS revenue_correcto
"""))
=== Revenue total: identico en ambos casos ===
┌──────────────┬───────────────────┐
│ revenue_roto │ revenue_correcto  │
│    double    │      double       │
├──────────────┼────────────────────┤
│       106.15 │            106.15 │
└──────────────┴────────────────────┘

106.15 en ambos — el mismo número que ya conoces desde foundations. Este es, precisamente, el motivo por el que este error es tan peligroso en la práctica: el número que la mayoría de la gente mira primero —el revenue total— no delata nada. El error solo aparece al desglosar por una columna de la dimensión (category) o al calcular una métrica que depende de otra columna de la dimensión (margin, que depende de unit_cost). Un reporte que solo muestra el total nunca lo va a encontrar.

Por qué COALESCE(d.valid_to, DATE '9999-12-31'), y no simplemente valid_to

Fíjate en un detalle deliberado de la condición BETWEEN: no se comparó order_ts directamente contra valid_to — se envolvió en COALESCE(d.valid_to, DATE '9999-12-31'). La razón es una regla de SQL sobre NULL que ya viste, en otro contexto, en el módulo 4: valid_to es NULL para cualquier versión vigente (la fila más reciente de cada producto), porque todavía no tiene fecha de cierre. Y en SQL, cualquier comparación contra NULL devuelve NULL, no true — ni siquiera BETWEEN. Si la condición fuera f.order_ts BETWEEN d.valid_from AND d.valid_to a secas, cualquier orden que debiera unirse con la versión vigente de un producto (por ejemplo, las órdenes que vas a agregar en la lección 4, fechadas después del 15 de agosto) fallaría en encontrar esa versión, porque algo BETWEEN fecha AND NULL nunca es true.

COALESCE(d.valid_to, DATE '9999-12-31') resuelve esto reemplazando el NULL por una fecha lejana en el futuro —cualquier fecha razonablemente posterior a cualquier venta que Kiosko vaya a registrar—, así que la versión vigente queda, en la práctica, "abierta hasta el infinito" para efectos de la comparación. 9999-12-31 no es un valor mágico ni parte del negocio de Kiosko: es, simplemente, "una fecha que ninguna venta real va a superar", el mismo truco que cualquier modelador dimensional usa para representar "sin fecha de cierre todavía" en una comparación que no tolera NULL.

Errores comunes

Usar is_current = true porque "es la versión que importa hoy". Qué pasa: alguien, al construir un reporte histórico, filtra por is_current = true razonando que es "la versión correcta del producto" — sin distinguir entre un reporte que describe el estado actual del catálogo (donde is_current = true sí es correcto) y un reporte que describe ventas pasadas (donde no lo es). Por qué pasa: is_current suena, por el nombre, como la respuesta "por defecto" correcta, y en la mayoría de los reportes simples —los que este módulo no cubre, como "muéstrame el catálogo de hoy"— sí lo es. Cómo detectarlo: pregúntate qué describe cada fila del reporte que estás construyendo — si describe un evento pasado (una venta, con su propio order_ts), is_current casi nunca es correcto; si describe el estado presente (el catálogo tal como está hoy, sin relación a ventas históricas), sí lo es. Cómo corregirlo: para cualquier reporte que una un hecho —con su propia fecha— contra una dimensión historizada, usa el join punto-en-el-tiempo de esta lección. Reserva is_current = true para consultas que describen el presente sin relación a un evento fechado (por ejemplo, "lista el catálogo vigente de Kiosko").

Verificar solo el conteo de filas, y dar el JOIN por bueno con eso. Qué pasa: alguien aplica la disciplina de la lección 2 —comparar COUNT(*) antes y después del JOIN— ve que da 40 en ambos casos (roto y correcto), y concluye que el JOIN está bien, sin agregar por ninguna columna de la dimensión. Por qué pasa: la lección 2 enseñó, con toda razón, que el conteo de filas es la primera verificación — pero es una verificación necesaria, no suficiente, cuando el problema no es fan-out sino atribución incorrecta. Cómo detectarlo: si tu única verificación de un JOIN contra una dimensión historizada es COUNT(*), no puedes distinguir entre el JOIN roto y el correcto de esta lección — ambos pasan esa prueba. Cómo corregirlo: para cualquier JOIN contra una dimensión con más de una versión por llave, agrega siempre una segunda verificación que dependa de una columna de la dimensión que sí cambió (category, unit_cost, en este caso) — el conteo de filas prueba que no hay fan-out; la agregación por columna de dimensión prueba que la atribución es correcta.

Olvidar el COALESCE y sorprenderse cuando faltan filas. Qué pasa: alguien escribe f.order_ts BETWEEN d.valid_from AND d.valid_to, sin COALESCE, y su JOIN pierde silenciosamente cualquier orden que debiera unirse con una versión vigente (valid_to IS NULL). Con las cuarenta órdenes de esta lección —todas anteriores al cambio de P002, todas cayendo en la versión cerrada— este error específico no se manifiesta, porque ninguna orden necesita unirse con una versión de valid_to = NULL. Por qué pasa: el comportamiento de NULL en comparaciones no es intuitivo la primera vez que se encuentra, y el error solo aparece con datos que efectivamente necesitan la versión vigente. Cómo detectarlo: si tu JOIN punto-en-el-tiempo pierde filas específicamente para productos o fechas recientes —cualquier venta que debería caer en la versión más nueva de un producto historizado—, sospecha primero de un valid_to sin COALESCE. Cómo corregirlo: COALESCE(d.valid_to, DATE '9999-12-31') —o una fecha equivalente, muy en el futuro— es parte integral del patrón, no un detalle opcional; la lección 4 de este módulo depende directamente de que esté presente.

Ejercicios

Ejercicio 1 — Confirma que beverages y electronics son idénticos en ambos JOIN, línea por línea. Usando las dos consultas de categoría de esta lección, escribe una tercera consulta que una los resultados de ambos JOIN por categoría y confirme, con una columna booleana, que beverages y electronics no cambiaron.

Ver solución 
print(con.sql("""
    WITH broken AS (
        SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue
        FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
        GROUP BY d.category
    ),
    correct AS (
        SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue
        FROM fact_orders f JOIN dim_product_scd d
            ON f.product_id = d.product_id
           AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
        GROUP BY d.category
    )
    SELECT
        COALESCE(b.category, c.category) AS category,
        b.revenue AS revenue_roto,
        c.revenue AS revenue_correcto,
        b.revenue IS NOT DISTINCT FROM c.revenue AS misma_categoria_y_revenue
    FROM broken b
    FULL OUTER JOIN correct c ON b.category = c.category
    ORDER BY category
"""))

Salida esperada:

┌───────────────┬──────────────┬───────────────────┬───────────────────────────┐
│   category    │ revenue_roto │ revenue_correcto   │ misma_categoria_y_revenue │
│    varchar    │    double    │       double       │          boolean          │
├───────────────┼──────────────┼────────────────────┼───────────────────────────┤
│ beverages     │        44.05 │              44.05 │ true                      │
│ electronics   │         40.5 │                40.5 │ true                      │
│ health-snacks │         21.6 │                NULL │ false                     │
│ snacks        │         NULL │               21.6 │ false                     │
└───────────────┴──────────────┴────────────────────┴───────────────────────────┘

beverages y electronics aparecen una sola vez cada uno, con misma_categoria_y_revenue = true — el FULL OUTER JOIN los encontró en ambos lados, con el mismo revenue. health-snacks y snacks aparecen como filas separadas, cada una con NULL del lado contrario — porque son, literalmente, categorías distintas: no hay ninguna fila que las una, así que el FULL OUTER JOIN las deja cada una en su propia fila. Esto confirma, con una sola consulta, exactamente lo que las dos tablas por separado ya mostraban: dos categorías coinciden, una difiere por completo en su nombre.

Ejercicio 2 — Calcula el margen total de Kiosko (las tres categorías sumadas) en ambos escenarios, y compáralo. Usando las consultas de categoría de esta lección, suma el margen de las tres categorías en cada caso (roto y correcto) y explica si el margen total de Kiosko cambia entre ambos.

Ver solución 
print(con.sql("""
    SELECT
        (SELECT ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2)
         FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true) AS margen_total_roto,
        (SELECT ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2)
         FROM fact_orders f JOIN dim_product_scd d
            ON f.product_id = d.product_id
           AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')) AS margen_total_correcto
"""))

Salida esperada:

┌───────────────────┬────────────────────────┐
│ margen_total_roto │ margen_total_correcto  │
│       double       │         double         │
├───────────────────┼─────────────────────────┤
│               45.71 │                  47.15 │
└───────────────────┴─────────────────────────┘

A diferencia del revenue total (idéntico en ambos casos), el margen total sí cambia: 45.71 con el JOIN roto contra 47.15 con el correcto — una diferencia de 1.44, la misma que ya viste al comparar solo P002. Esto confirma algo importante que el revenue total no revela por sí solo: el JOIN roto no solo desatribuye mal una categoría — subestima la rentabilidad real de la semana en 1.44, porque le aplica un costo del futuro a ventas del pasado. Cualquier decisión de negocio basada en el margen —no en el revenue— sí se ve afectada por este error, aunque el revenue total nunca lo delate.

Ejercicio 3 — Explica por qué el JOIN correcto de esta lección seguiría dando el mismo resultado si dim_product_scd tuviera diez versiones de P002 en vez de dos. En 2-3 frases, explica por qué el patrón BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31') no depende de cuántas versiones tenga la dimensión.

Ver solución

El patrón punto-en-el-tiempo no cuenta versiones ni asume un número fijo — para cada venta, compara su order_ts contra el rango de vigencia de cada versión candidata, y el diseño de SCD-2 garantiza que los rangos de un mismo product_id nunca se superponen (cada versión cierra exactamente donde empieza la siguiente). Sin importar si P002 tuviera dos versiones o diez, cualquier order_ts específico cae dentro del rango de exactamente una de ellas — nunca cero, nunca más de una—, así que el JOIN sigue produciendo una sola fila de resultado por orden, sin fan-out y sin ambigüedad, sin importar cuántas versiones históricas existan.

Resumen y siguiente paso

Esta lección construyó y comparó dos formas de unir fact_orders contra una dimensión historizada, ambas sin fan-out (cuarenta filas en los dos casos): is_current = true —que atribuye toda venta a la versión vigente hoy, sin importar cuándo ocurrió— y el join punto-en-el-tiempo —f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, '9999-12-31')— que atribuye cada venta a la versión que realmente regía el día de esa venta. Con las diez órdenes de P002 de la semana de Kiosko, la diferencia es completa: health-snacks/margen 9.36 (roto) contra snacks/margen 10.8 (correcto), con el mismo revenue de 21.6 en ambos — la evidencia exacta de por qué "el total cuadra" no es suficiente verificación.

Antes de avanzar deberías poder: escribir de memoria la condición completa del JOIN punto-en-el-tiempo, incluyendo el COALESCE; explicar por qué is_current = true es correcto para "el catálogo de hoy" pero incorrecto para "ventas históricas"; y recitar, sin mirar, la categoría y el margen exactos que cada uno de los dos JOIN le asigna a P002.

La lección 4 lleva este mismo patrón a un problema de tiempo real: qué pasa cuando una orden de P002, fechada después del 15 de agosto, llega antes de que el MERGE que historiza el cambio real haya corrido — la dimensión de llegada tardía.

Recursos

← AnteriorPor qué unir solo por product_id rompe la historiaSiguiente →Dimensiones de llegada tardía