El planteamiento y cuándo aplica
Esta es una pregunta sobre modelado dimensional y semántica histórica. La dirección de un cliente no puede tratarse únicamente como el valor más reciente cuando un reporte financiero debe responder a qué región pertenecía un cliente en una fecha pasada. Suponga snapshots completos diarios, sin registro de cambios (change log), y un destino que admita el estado actual, búsquedas históricas y reproducción (replay). Dataquest incluye los tipos de SCD 1, 2 y 3 entre las preguntas de entrevistas de ingeniería de datos; AWS demuestra el Tipo 2 a partir de archivos completos sin CDC.
Lo que evalúa el entrevistador
- Definir si los reportes consultan "ahora" o "a la fecha de entonces" antes de elegir un tipo.
- Distinguir entre las semánticas de sobrescritura, anexar una versión y mantener un único valor anterior.
- Mantener la unicidad con claves naturales, claves subrogadas, tiempos de vigencia y un indicador de registro actual.
- Manejar snapshots tardíos, archivos duplicados, eliminaciones, reproducción y escrituras concurrentes en lugar de escribir un solo
UPDATE.
Aclaraciones que se deben hacer primero
- ¿Los reportes deben reconstruir el estado al momento del evento o es suficiente con el perfil actual?
- ¿Los snapshots incluyen una versión, hora de extracción o watermark, y pueden llegar desordenados?
- ¿La ausencia de un registro significa una eliminación de negocio, una omisión temporal o una falla en el sistema fuente?
- ¿Se permite revisar el historial publicado y los joins con tablas de hechos, y se requiere auditoría, reproducción y reejecuciones idempotentes?
Una respuesta de 30 segundos
"Hago explícita la semántica temporal: el Tipo 1 conserva solo el valor actual, el Tipo 2 anexa una versión para cada cambio de negocio con valid_from y valid_to, y el Tipo 3 conserva uno o un número reducido de valores anteriores. Para análisis point-in-time elijo el Tipo 2, busco la fila actual por clave natural, uno las tablas de hechos mediante una clave subrogada y uso watermarks de snapshots para identificar datos tardíos y eliminaciones. Descargo y deduplico cada lote, luego cierro la versión anterior e inserto la nueva de forma atómica; reejecutar el mismo lote debe producir el mismo resultado".
Solución paso a paso
Paso 1: Definir la pregunta histórica
El Tipo 1 se adapta a correcciones o perfiles sin historial y sobrescribe en el lugar. El Tipo 2 se adapta a auditorías, atribución de ingresos y consultas point-in-time manteniendo cada versión. El Tipo 3 se adapta a una ventana fija de 'actual versus anterior'. Si el requerimiento pide la región de un cliente el trimestre pasado, los Tipos 1 y 3 pierden información, por lo que se debe elegir el Tipo 2.
Paso 2: Establecer los invariantes del Tipo 2
Almacene una clave subrogada, clave natural, atributos, valid_from, valid_to, is_current y el watermark del lote de origen. Para cada clave natural, como máximo una fila tiene is_current = true; los intervalos de vigencia no pueden superponerse. AWS utiliza fechas de inicio/fin, un indicador de registro actual y eliminaciones lógicas para el historial completo, mientras que la configuración de Tipo 2 de Microsoft requiere una clave natural, una clave subrogada, dos fechas y un indicador de activo.
Paso 3: Inferir cambios a partir de un snapshot completo
Escriba el archivo en una tabla de landing inmutable con el hash del archivo, la hora de extracción y la secuencia del lote. Compare un hash de atributos para cada clave natural contra la dimensión actual: inserte claves nuevas, cierre y anexe claves modificadas, y omita hashes iguales. Una clave faltante puede implicar una eliminación solo cuando se cumple el contrato de snapshot completo; un archivo incremental no debe inferir eliminaciones a partir de la ausencia de datos.
Paso 4: Manejar datos tardíos y desordenados
Mantenga un watermark monotónico por cada fuente. Rechace o ponga en cuarentena cualquier archivo con un valor inferior al watermark aplicado. Si se permite la corrección histórica, divida el intervalo de vigencia afectado y vuelva a calcular los joins de hechos impactados dentro de una transacción; si los reportes publicados son inmutables, coloque el lote en una cola de corrección y publique su impacto. Usar el tiempo de llegada como tiempo de validez convertiría un snapshot antiguo en un estado nuevo.
Paso 5: Eliminaciones, reproducción y concurrencia
Una clave faltante en un snapshot completo crea una versión de eliminación lógica o cierra la fila actual, conservando al mismo tiempo la evidencia de auditoría. Haga que el hash del archivo o el lote de origen sea una clave de idempotencia única; reejecutar un lote no debe anexar otra versión. Use una transacción o un bloqueo de merge para la partición de la clave natural, de modo que el cierre y la inserción sean atómicos. Aplique los lotes por orden de watermark; un lote más antiguo no debe reabrir un intervalo cerrado.
Un esquema SQL verificable
-- current_dim: one current row per customer_id
BEGIN;
UPDATE dim_customer AS old
SET valid_to = :as_of,
is_current = FALSE,
source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
AND old.is_current = TRUE
AND old.row_hash <> incoming.row_hash;
INSERT INTO dim_customer (
customer_key, customer_id, city, valid_from, valid_to,
is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
ON old.customer_id = incoming.customer_id
AND old.is_current = TRUE
WHERE old.customer_id IS NULL
OR old.row_hash <> incoming.row_hash;
COMMIT;En producción todavía se necesita una restricción de unicidad en batch_id y comprobaciones previas al commit de que cada clave natural tenga una sola fila actual, que los intervalos no se superpongan y que el watermark de entrada sea válido. El esquema asume que stage_customer se deduplica por lote; no es un manejador completo de datos fuera de orden.
Un ejemplo de respuesta de alta calidad
"Primero pregunto qué historial debe preservar el reporte. Utilizo el Tipo 1 para valores únicamente actuales, el Tipo 2 para un estado en cualquier fecha pasada y el Tipo 3 para exactamente un valor anterior. Para snapshots completos diarios, descargo el archivo en bruto y deduplico por watermark de lote y hash de archivo. Comparo las claves naturales y los hashes de atributos para clasificar inserciones, cambios y operaciones sin efecto (no-ops). Una actualización de Tipo 2 debe cerrar la fila antigua e insertar una nueva versión de forma atómica, con una sola fila actual e intervalos sin superposiciones. Las claves faltantes significan eliminaciones únicamente bajo un contrato de snapshot completo; los lotes tardíos se ordenan o se ponen en cuarentena mediante watermarks, y el contrato del reporte decide si el historial se corrige".
Errores comunes
- Usar por defecto el Tipo 2 para cada dimensión → mayor costo de almacenamiento y consulta → consultar primero el contrato de historial.
- Utilizar el tiempo de llegada como tiempo de vigencia → los snapshots desordenados corrompen el historial → usar el tiempo de vigencia del negocio o una cola de corrección.
- Tratar las claves faltantes en un archivo incremental como eliminaciones → se eliminan incrementos válidos → inferir eliminaciones únicamente a partir de snapshots completos.
- Actualizar
is_currentsin una versión de clave subrogada → las tablas de hechos no pueden unirse al estado point-in-time → cerrar la fila antigua y anexar una versión. - Omitir una clave de idempotencia de lote → la reproducción anexa historial duplicado → forzar la unicidad en el hash del archivo o en el id del lote.
Preguntas de seguimiento y respuestas sólidas
¿Debería un evento tardío revisar un reporte publicado?
Confirme si el reporte es revisable. Si lo es, divida los intervalos de vigencia según el tiempo del negocio y vuelva a calcular los hechos afectados; si no lo es, conserve el resultado publicado y emita un lote de corrección con su impacto. Ambos contratos conservan el lote en bruto para que los números no cambien de forma silenciosa.
¿Qué pasa si un cliente aparece dos veces en un snapshot?
Trátelo como un error de calidad de entrada. Seleccione una sola fila únicamente cuando una versión de negocio o una secuencia de origen defina a la ganadora; de lo contrario, póngalo en cuarentena y genere una alerta. Un LIMIT 1 arbitrario hace que el historial no sea reproducible.
¿Cuándo es el Tipo 3 mejor que el Tipo 2?
Utilice el Tipo 3 cuando la única comparación requerida sea la actual versus la anterior y nunca se consulten versiones más antiguas. Utiliza menos almacenamiento y consultas más simples. Si más adelante surge la necesidad de consultar el historial en fechas arbitrarias, migre al Tipo 2 y plantee el costo de backfill.
¿Cómo se verifica que los intervalos no se superpongan?
Ordene por clave natural y compruebe que cada intervalo finalice a más tardar cuando comience el siguiente, luego asegure que exista como máximo una fila actual por clave. Convierta esto en una validación previa a la publicación del lote (release gate) en lugar de un muestreo retrospectivo.