SCD tipo 2: historiza con valid_from/valid_to

Descripción

Esta lección aplica el mismo cambio exacto de P002 —categoría de snacks a health-snacks, costo de 0.60 a 0.68, el 15 de agosto de 2026— con la técnica que sí preserva la historia: SCD tipo 2. La definición formal de Kimball es precisa: "los cambios de SCD tipo 2 agregan una fila nueva en la dimensión con los valores de atributo actualizados", y esa fila nueva necesita, como mínimo, tres columnas adicionales: "1) fecha o marca de tiempo de inicio de vigencia de la fila; 2) fecha o marca de tiempo de expiración de la fila; y 3) un indicador de fila vigente". En Kiosko, esas tres columnas se llaman valid_from, valid_to e is_current. Vas a construir dim_product_scd con esas tres columnas, y vas a historizar el cambio de P002 con dos statements SQL explícitos —un UPDATE que cierra la fila vieja, un INSERT que abre la fila nueva—, para entender exactamente qué mecánica tiene que ejecutarse, antes de que la lección 5 la automatice con MERGE INTO.

Conexión con el módulo. Esta lección construye dim_product_scd a mano, con dos statements separados que tú mismo escribes y ordenas. Es deliberadamente la forma "larga" de resolver el problema — la lección 5 va a mostrar que un único MERGE INTO hace exactamente lo mismo, en un solo statement, de forma más segura y más escalable a un catálogo con miles de productos. Pero entender primero la mecánica manual es lo que hace que MERGE INTO, en la lección 5, se sienta como una herramienta que automatiza algo que ya entiendes, no como magia.

Una analogía: el historial de direcciones, otra vez, ahora con las dos entradas exactas

Retoma la analogía que abrió este módulo: el documento de identidad que registra tu historial de direcciones. Cuando te mudas, el sistema hace, en esencia, dos operaciones separadas y en un orden específico. Primero, cierra el registro de la dirección anterior: le agrega una fecha de "válido hasta", y lo marca como ya no vigente. Segundo, abre un registro nuevo para la dirección actual: con su propia fecha de "válido desde", y marcado como el vigente. Ningún sistema bien diseñado hace estas dos operaciones en el orden contrario —abrir el registro nuevo antes de cerrar el viejo dejaría, por un instante, dos direcciones marcadas como "vigentes" a la vez, algo que no debería ser posible—.

Esta lección hace, literalmente, esas dos operaciones sobre dim_product_scd: un UPDATE que cierra la versión vieja de P002 (valid_to = '2026-08-14', is_current = false), seguido de un INSERT que abre la versión nueva (valid_from = '2026-08-15', valid_to = NULL, is_current = true). El orden importa exactamente igual que en la analogía: cerrar primero, abrir después.

Ejemplo trabajado: dim_product_scd, historizada a mano

Primero, la tabla con las tres columnas de vigencia que SCD tipo 2 necesita, cargada con el catálogo completo de Kiosko como su primera versión —vigente desde el 1 de agosto de 2026, la fecha en la que Kiosko empezó a llevar esta dimensión historizada—:

# scd_type2_manual.py
import duckdb

from kiosko import DIM_PRODUCT

con = duckdb.connect()
con.execute("CREATE SEQUENCE product_key_seq START 1")
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 (nextval('product_key_seq'), ?, ?, ?, ?, DATE '2026-08-01', NULL, true)",
    [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)

print("=== dim_product_scd, estado inicial (vigente desde 2026-08-01) ===")
print(con.sql("SELECT * FROM dim_product_scd ORDER BY product_key"))

Qué esperar (parte 1). Al correr esta primera parte, la salida es exactamente esta:

=== dim_product_scd, estado inicial (vigente desde 2026-08-01) ===
┌─────────────┬────────────┬───────────────────────┬─────────────┬───────────┬────────────┬──────────┬────────────┐
│ product_key │ product_id │     product_name      │  category   │ unit_cost │ valid_from │ valid_to │ is_current │
│    int32    │  varchar   │        varchar        │   varchar   │  double   │    date    │   date   │  boolean   │
├─────────────┼────────────┼───────────────────────┼─────────────┼───────────┼────────────┼──────────┼────────────┤
│           1 │ P001       │ Bottled Water 600ml   │ beverages   │       0.4 │ 2026-08-01 │ NULL     │ true       │
│           2 │ P002       │ Energy Bar            │ snacks      │       0.6 │ 2026-08-01 │ NULL     │ true       │
│           3 │ P003       │ Instant Coffee Sachet │ beverages   │      0.35 │ 2026-08-01 │ NULL     │ true       │
│           4 │ P004       │ Phone Charger Cable   │ electronics │       2.1 │ 2026-08-01 │ NULL     │ true       │
└─────────────┴────────────┴───────────────────────┴─────────────┴───────────┴────────────┴──────────┴────────────┘

Fíjate en valid_to: NULL en las cuatro filas, no una fecha futura arbitraria. Esa es una decisión deliberada, y la mejor práctica documentada por la propia guía de DuckDB sobre este patrón: "mantén end_date en NULL para las filas vigentes, para mejorar el rendimiento de las consultas" — un valor NULL en valid_to se lee, sin ambigüedad, como "todavía vigente, sin fecha de expiración conocida", y evita tener que inventar una fecha centinela como 9999-12-31 en cada fila que nunca cambió.

Ahora, los dos statements que historizan el cambio de P002, en el orden exacto que importa:

print("\n=== Paso 1: UPDATE cierra la version vieja de P002 ===")
con.execute("""
    UPDATE dim_product_scd
    SET valid_to = DATE '2026-08-14', is_current = false
    WHERE product_id = 'P002' AND is_current = true
""")
print(con.sql("SELECT * FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

print("\n=== Paso 2: INSERT abre la version nueva de P002 ===")
con.execute("""
    INSERT INTO dim_product_scd VALUES
        (nextval('product_key_seq'), 'P002', 'Energy Bar', 'health-snacks', 0.68, DATE '2026-08-15', NULL, true)
""")
print(con.sql("SELECT * FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

print("\n=== dim_product_scd, estado final (4 productos, 5 filas) ===")
print(con.sql("SELECT * FROM dim_product_scd ORDER BY product_id, product_key"))

Qué esperar (pasos 1 y 2). Al correr el resto del script, la salida es exactamente esta:

=== Paso 1: UPDATE cierra la version vieja de P002 ===
┌─────────────┬────────────┬──────────────┬──────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │ product_name │ category │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │   varchar    │ varchar  │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼──────────────┼──────────┼───────────┼────────────┼────────────┼────────────┤
│           2 │ P002       │ Energy Bar   │ snacks   │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
└─────────────┴────────────┴──────────────┴──────────┴───────────┴────────────┴────────────┴────────────┘

=== Paso 2: INSERT abre la version nueva de P002 ===
┌─────────────┬────────────┬──────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │ product_name │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │   varchar    │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼──────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           2 │ P002       │ Energy Bar   │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ P002       │ Energy Bar   │ health-snacks │      0.68 │ 2026-08-15 │ NULL       │ true       │
└─────────────┴────────────┴──────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

=== dim_product_scd, estado final (4 productos, 5 filas) ===
┌─────────────┬────────────┬───────────────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │     product_name      │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │        varchar        │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼───────────────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           1 │ P001       │ Bottled Water 600ml   │ beverages     │       0.4 │ 2026-08-01 │ NULL       │ true       │
│           2 │ P002       │ Energy Bar            │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ P002       │ Energy Bar            │ health-snacks │      0.68 │ 2026-08-15 │ NULL       │ true       │
│           3 │ P003       │ Instant Coffee Sachet │ beverages     │      0.35 │ 2026-08-01 │ NULL       │ true       │
│           4 │ P004       │ Phone Charger Cable   │ electronics   │       2.1 │ 2026-08-01 │ NULL       │ true       │
└─────────────┴────────────┴───────────────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

Esta es la evidencia que la lección 3 no pudo dar: P002 ahora tiene dos filas, con product_key distinto (2 y 5), cada una con su propio rango de vigencia. Si alguien pregunta "¿cuál era la categoría de P002 el 10 de agosto de 2026?", la respuesta está ahí, sin ambigüedad: la fila con product_key = 2, valid_from = '2026-08-01', valid_to = '2026-08-14' — el 10 de agosto cae dentro de ese rango, y la categoría era snacks. Fíjate también en algo fácil de pasar por alto: product_key salta de 4 a 5, no reutiliza ningún número —la SEQUENCE sigue creciendo, nunca se recicla un product_key ya asignado, exactamente la garantía que una llave sustituta necesita.

Diagrama: la línea de tiempo completa de P002

flowchart LR
    subgraph V1["product_key = 2"]
        A["valid_from: 2026-08-01\nvalid_to: 2026-08-14\nis_current: false\ncategory: snacks\nunit_cost: 0.60"]
    end

    subgraph V2["product_key = 5"]
        B["valid_from: 2026-08-15\nvalid_to: NULL\nis_current: true\ncategory: health-snacks\nunit_cost: 0.68"]
    end

    A -->|"UPDATE cierra\n(paso 1)"| A
    A -.->|"INSERT abre\n(paso 2)"| B

Linea de tiempo de P002 en dim_product_scd
────────────────────────────────────────────────────────────────────
2026-08-01              2026-08-14 │ 2026-08-15              (hoy)
│─────────── product_key = 2 ───────│─────── product_key = 5 ───────►
│  category: snacks, unit_cost: 0.60│  category: health-snacks,     │
│  is_current: false                │  unit_cost: 0.68              │
│  (vigente en este rango)          │  is_current: true              │
────────────────────────────────────────────────────────────────────

Profundización: por qué dos statements, y por qué en ese orden exacto

Vale la pena detenerse en por qué la solución de esta lección necesita dos statements separados —un UPDATE y un INSERT—, en vez de uno solo. La razón es estructural: UPDATE y INSERT son, por definición, dos operaciones distintas. UPDATE modifica columnas de una fila que ya existe —la fila de P002 con product_key = 2, que sigue siendo, en todo momento, la misma fila física, solo con valid_to e is_current modificados—. INSERT crea una fila nueva, con un product_key nuevo, que nunca existió antes. No hay ningún statement de SQL estándar capaz de hacer ambas cosas sobre filas distintas en una sola operación atómica simple — necesitas los dos, en secuencia.

El orden importa por una razón concreta, no solo estética: si el INSERT se ejecutara antes que el UPDATE, habría un instante —por breve que fuera— en el que dim_product_scd tendría dos filas de P002 con is_current = true simultáneamente: la vieja (todavía no cerrada) y la nueva (recién abierta). Cualquier consulta que se ejecutara en ese instante contra is_current = true obtendría un resultado ambiguo — dos categorías vigentes para el mismo producto, algo que no debería ser posible por diseño. Cerrar primero, abrir después, garantiza que nunca exista ese estado intermedio inconsistente. La sección "Errores comunes" de esta lección construye exactamente ese escenario roto, para que lo veas con tus propios ojos antes de que la lección 5 te muestre cómo MERGE INTO resuelve este mismo problema de orden de forma más segura.

Una nota sobre por qué product_key usa ahora una SEQUENCE (CREATE SEQUENCE product_key_seq) en vez de ROW_NUMBER() OVER (ORDER BY product_id), el patrón que usaste en el módulo 2 para dim_store y dim_product. ROW_NUMBER() recalcula la llave sustituta desde cero, cada vez que se ejecuta, sobre todas las filas de la tabla en ese momento — funciona perfectamente para una dimensión que se reconstruye completa en cada carga, pero rompería dim_product_scd: si volvieras a numerar todas las filas con ROW_NUMBER() después de agregar la versión nueva de P002, el product_key = 2 de la versión vieja podría cambiar de valor, y cualquier tabla externa que ya hubiera referenciado ese product_key (por ejemplo, en un hecho ya cargado) apuntaría, de repente, a la fila equivocada. Una SEQUENCE resuelve esto asignando cada product_key una sola vez, de forma creciente y nunca reutilizada — las filas viejas conservan su llave para siempre, y solo las filas nuevas reciben un número nunca antes usado.

Errores comunes

Invertir el orden: INSERT primero, UPDATE después. Qué pasa: alguien escribe el INSERT de la versión nueva antes que el UPDATE que cierra la versión vieja. Por qué pasa: en el código, "agregar lo nuevo" se siente como el paso principal, y "cerrar lo viejo" como un detalle de limpieza que podría ir después. Cómo detectarlo: ejecuta una consulta SELECT COUNT(*) FROM dim_product_scd WHERE product_id = 'P002' AND is_current = true justo después de correr el INSERT pero antes del UPDATE — si devuelve 2 en vez de 1, tienes dos versiones vigentes al mismo tiempo, un estado inconsistente que ninguna consulta de punto-en-el-tiempo (módulo 5) puede interpretar de forma confiable. Cómo corregirlo: siempre cierra la fila vieja (UPDATE ... SET is_current = false) antes de abrir la fila nueva (INSERT ... is_current = true) — el orden exacto de esta lección. La lección 5 muestra cómo MERGE INTO reduce este riesgo, aunque no lo elimina del todo cuando el INSERT sigue siendo un statement separado.

Olvidar el filtro is_current = true en el UPDATE. Qué pasa: alguien escribe UPDATE dim_product_scd SET valid_to = ..., is_current = false WHERE product_id = 'P002', sin el AND is_current = true. La primera vez que corres esto no pasa nada visible —solo existe una fila de P002—, pero si alguna vez P002 ya tuviera más de una versión histórica, este UPDATE cerraría todas las versiones de P002, incluyendo las que ya estaban cerradas correctamente, sobrescribiendo su valid_to original con la fecha del cambio actual. Por qué pasa: con una sola versión existente, el filtro is_current = true parece redundante. Cómo detectarlo: si después de un segundo cambio de P002 todas sus filas históricas tienen el mismo valid_to, perdiste la fecha de cierre real de las versiones anteriores. Cómo corregirlo: el UPDATE que cierra una versión siempre debe filtrar explícitamente por is_current = true — solo la fila vigente puede cerrarse; las filas ya cerradas nunca deben tocarse de nuevo.

Usar CURRENT_DATE en vez de la fecha fija del cambio. Qué pasa: alguien escribe valid_to = CURRENT_DATE - INTERVAL 1 DAY en vez de valid_to = DATE '2026-08-14', pensando en cómo se vería esto en un sistema de producción real, donde el cambio se aplica el mismo día que ocurre. Por qué pasa: en un pipeline de producción de verdad, CURRENT_DATE es exactamente lo correcto —el MERGE corre el día del cambio, y "hoy" y "la fecha del cambio" son lo mismo—. Cómo detectarlo: si corres este script dos días distintos y obtienes valores diferentes en valid_to, tu script dejó de ser reproducible — la regla dura de esta guía. Cómo corregirlo: en esta guía, todas las fechas son fijas y explícitas —DATE '2026-08-14', DATE '2026-08-15'—, precisamente para que la salida sea idéntica, byte a byte, sin importar cuándo se ejecute el script. En producción, reemplazarías la fecha fija por la fecha real del proceso —típicamente CURRENT_DATE—, pero esa sustitución queda fuera del alcance de esta guía por la misma razón que el resto del código evita cualquier fuente de no determinismo.

Ejercicios

Ejercicio 1 — Verifica que P001, P003 y P004 conservan exactamente una fila cada uno. Después de historizar P002, escribe una consulta que confirme cuántas filas tiene cada product_id en dim_product_scd.

print(con.sql("""
    SELECT product_id, COUNT(*) AS total_versions
    FROM dim_product_scd
    GROUP BY product_id
    ORDER BY product_id
"""))

┌────────────┬────────────────┐
│ product_id │ total_versions │
│  varchar   │     int64      │
├────────────┼────────────────┤
│ P001       │              1 │
│ P002       │              2 │
│ P003       │              1 │
│ P004       │              1 │
└────────────┴────────────────┘

Ejercicio 2 — Reconstruye qué categoría tenía P002 en tres fechas distintas. Sin usar is_current, escribe una consulta que, para las fechas 2026-08-05, 2026-08-14 y 2026-08-20, devuelva la categoría vigente de P002 en cada una, usando valid_from/valid_to.

print(con.sql("""
    SELECT
        check_date,
        (SELECT category FROM dim_product_scd
         WHERE product_id = 'P002'
           AND check_date BETWEEN valid_from AND COALESCE(valid_to, DATE '9999-12-31')) AS category_on_that_date
    FROM (VALUES (DATE '2026-08-05'), (DATE '2026-08-14'), (DATE '2026-08-20')) AS t(check_date)
"""))

┌────────────┬───────────────────────┐
│ check_date │ category_on_that_date │
│    date    │        varchar        │
├────────────┼───────────────────────┤
│ 2026-08-05 │ snacks                │
│ 2026-08-14 │ snacks                │
│ 2026-08-20 │ health-snacks         │
└────────────┴───────────────────────┘

Ejercicio 3 — Explica por qué el product_key de la versión vieja de P002 (2) es menor que el de las versiones de P003 y P004 (3 y 4), aunque la versión nueva de P002 (5) fue creada después. En 2-3 frases, explica esta aparente "desordenada" numeración usando lo que aprendiste sobre SEQUENCE en la profundización de esta lección.

Resumen y siguiente paso

Esta lección construyó dim_product_scd con las tres columnas que SCD tipo 2 exige —valid_from, valid_to, is_current— e historizó el cambio de P002 a mano, con dos statements en el orden correcto: UPDATE para cerrar la versión vieja, INSERT para abrir la nueva. El resultado —dos filas para P002, con rangos de vigencia que no se superponen— es la evidencia que la lección 3 no pudo dar: cualquier pregunta sobre "¿cuál era el valor en tal fecha?" tiene ahora una respuesta exacta y verificable.

Antes de avanzar deberías poder: nombrar las tres columnas mínimas que SCD tipo 2 necesita y qué representa cada una; escribir de memoria el UPDATE que cierra una versión y el INSERT que abre la siguiente, en el orden correcto; y explicar por qué product_key en dim_product_scd usa una SEQUENCE en vez de ROW_NUMBER().

La lección 5 automatiza exactamente esta misma mecánica —cerrar la vieja, abrir la nueva— con un único statement de DuckDB diseñado específicamente para este patrón: MERGE INTO.

Recursos

• Kimball Group — "Slowly Changing Dimension Type 2" — la definición oficial de las tres columnas mínimas (valid_from, valid_to, is_current) que esta lección implementa. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. En inglés.
• DuckDB — guía oficial "Merge Statement for SCD Type 2" — el patrón de referencia, incluida la recomendación de mantener end_date en NULL para las filas vigentes, que esta lección sigue. duckdb.org/docs/current/guides/sql_features/merge. En inglés.
• DuckDB — documentación de CREATE SEQUENCE — la referencia del generador de llaves sustitutas crecientes que reemplaza a ROW_NUMBER() en una dimensión historizada. duckdb.org/docs/current/sql/statements/create_sequence. En inglés.