Entrevista de ingeniería de datos: Diseñar una dimensión SCD Tipo 2

Problema y alcance

Una fuente de clientes envía una instantánea diaria completa de 50 millones de filas. Alrededor del 1% de los atributos seleccionados para el seguimiento histórico cambia cada día. Los pedidos llegan de forma continua, mientras que las correcciones de la fuente pueden llegar con hasta tres días de retraso. Diseña una dimensión de clientes de tipo Dimensión de Cambio Lento Tipo 2 (Slowly Changing Dimension Type 2) que preserve el valor vigente en el momento en que ocurrió cada pedido, gestione eliminaciones, admita reejecuciones seguras y permanezca auditable tras procesos de retroalimentación de datos (backfills).

La tarea principal radica en el modelado dimensional y la corrección temporal. El mecanismo de captura de datos modificados (Change Data Capture o CDC) puede suministrar eventos de entrada, pero no define el historial de destino. La respuesta debe decidir qué atributos ameritan un tratamiento de Tipo 2, distinguir una clave de negocio duradera de la clave subrogada de una versión, definir intervalos de validez que no se superpongan y explicar cómo un hecho resuelve la versión correcta.

La escala hace evidentes las elecciones descuidadas. Una tasa de cambio diaria del 1% representa cerca de 500.000 versiones nuevas por día y 182,5 millones por año. Si una versión añadida promedia 300 bytes, eso equivale a unos 54,75 GB de datos de filas en bruto por año antes de índices, réplicas, metadatos y compresión. Estas cifras constituyen supuestos para la entrevista y una base de dimensionamiento, no una garantía de almacenamiento.

Qué evalúan los entrevistadores

Una respuesta básica dice: “se expira la fila antigua y se inserta una nueva”. Una respuesta sólida define primero la semántica del historial. El Tipo 1 sobrescribe un valor y pierde su estado anterior. El Tipo 2 crea una nueva versión con una nueva clave subrogada y preserva la versión antigua. No todas las columnas de origen deberían generar una versión: corregir el uso de mayúsculas puede ser Tipo 1, mientras que un territorio de ventas o una banda de precios utilizada en informes históricos puede ser Tipo 2.

La siguiente señal es la disciplina temporal. El candidato debe indicar una convención de intervalos como [valid_from, valid_to), garantizar que las versiones de una clave de negocio nunca se superpongan y permitir solo una versión actual. Un hecho en el momento t coincide con la versión cuyo inicio es igual o anterior a t y cuyo fin es posterior a t. Usar el tiempo de carga cuando el requisito exige el tiempo de vigencia de negocio atribuye erróneamente y de forma silenciosa los datos tardíos.

Los entrevistadores también buscan comportamiento en producción. Un lote repetido no debe crear otra versión. Una instantánea incompleta no debe eliminar millones de clientes. Cerrar una fila actual e insertar su reemplazo debe ser una operación atómica. Las correcciones tardías pueden requerir dividir un intervalo histórico en lugar de cambiar únicamente la fila actual. Si el negocio necesita tanto “cuándo entró en vigencia el valor” como “cuándo lo conoció el almacén de datos”, el SCD Tipo 2 ordinario resulta insuficiente; ese es un requisito bitemporal.

Preguntas para aclarar antes de responder

• ¿Qué atributos afectan el análisis histórico? Rastrea únicamente los campos acordados como Tipo 2. Los campos de auditoría y las marcas de tiempo de ingesta no deben crear versiones de negocio.
• ¿Es customer_id estable y nunca se reutiliza? Es la clave de negocio. Cada versión histórica recibe una clave subrogada customer_sk independiente.
• ¿Proporciona la fuente un tiempo de vigencia o secuencia confiable? Una instantánea diaria prueba cuándo se observó un valor, no necesariamente cuándo se hizo verdadero. Sin una marca temporal confiable de la fuente, el almacén de datos no puede inventar un límite retroactivo correcto.
• ¿Está cada instantánea explícitamente marcada como completa? Trata la ausencia como eliminación solo después de que se superen las comprobaciones de completitud, recuento de filas y totales de control. Un extracto parcial no es un flujo de eliminaciones.
• ¿Qué debería significar una eliminación? Este diseño cierra la versión activa e inserta una versión lápida (tombstone) actual con is_deleted = true, preservando un límite de eliminación explícito.
• ¿Los hechos almacenan customer_sk en el momento de la ingesta o se combinan por tiempo en la consulta? Resolver la clave subrogada durante la carga de hechos simplifica las consultas posteriores. Una combinación temporal sigue siendo útil para backfills y validación.
• ¿Se permite que las correcciones reescriban el historial de negocio previo? Si es así, conserva las versiones originales de la fuente y un registro de auditoría, ya que un backfill puede cambiar legítimamente resultados analíticos anteriores.
• ¿Debe el sistema retener el tiempo de conocimiento además del tiempo de negocio? Si los auditores necesitan ambos, modela el tiempo de validez y el tiempo de sistema por separado en lugar de forzar ambos en un solo intervalo.

Respuesta en 30 segundos

I would first define the business key, Type 2 attributes, trusted effective time, and delete policy.
Each version gets a surrogate key and a half-open [valid_from, valid_to) interval; one version
per customer is current. I stage and deduplicate a complete snapshot, hash only tracked attributes,
then classify rows as unchanged, new, changed, or deleted. A changed key closes the old version and
inserts the new one atomically. The batch ID and source version make reruns idempotent. Facts resolve
the version effective at order time. Late corrections split the historical interval they affect, and
validation rejects duplicate current rows, overlapping intervals, broken fact references, or an
implausible delete surge.

Análisis paso a paso a profundidad

Paso 1: Definir la granularidad y las columnas antes de la carga

La granularidad es una versión de un cliente a lo largo de un intervalo de validez. Una tabla práctica contiene:

dim_customer {
  customer_sk       // surrogate primary key for this version
  customer_id       // durable business key from the source
  segment
  sales_region
  valid_from
  valid_to           // null means open-ended
  is_current
  is_deleted
  change_hash        // canonical hash of Type 2 attributes only
  source_version
  load_batch_id
}

Utiliza [valid_from, valid_to) de forma consistente. Cuando un cambio entra en vigencia en 2026-07-10T09:00:00Z, la fila antigua termina en ese instante y la nueva fila comienza en el mismo instante. La convención semiabierta hace que el límite pertenezca exactamente a una versión. Almacena las marcas de tiempo en una zona horaria y precisión definidas; mezclar fechas, hora local y UTC genera huecos artificiales o coincidencias dobles.

La clave de negocio agrupa las versiones. La clave subrogada identifica un estado histórico inmutable y es la clave foránea almacenada por los hechos. is_current es una conveniencia, no una verdad independiente: debe coincidir con valid_to IS NULL. Un hash es solo una optimización de comparación. Canonicaliza nulos, tipos, Unicode y el orden de columnas, y conserva aun así las columnas rastreadas reales para explicaciones y auditorías.

Paso 2: Dimensionar la ruta de escritura y escaneo

La instantánea diaria escanea 50 millones de filas de origen. Con una tasa de cambio del 1%:

50,000,000 × 1% = 500,000 new versions/day
500,000 × 365 = 182,500,000 new versions/year
182,500,000 × 300 bytes ≈ 54.75 GB/year of raw row data

Esto separa el costo de escaneo del costo de escritura de cambios. Deposita la instantánea en una zona de staging una sola vez, proyecta únicamente las columnas requeridas y compárala con las filas actuales de la dimensión por clave de negocio. La partición o clustering debe seguir los patrones reales de consulta y mantenimiento—a menudo la clave de negocio para búsquedas y el tiempo de validez para poda (pruning)—en lugar de crear miles de particiones diarias minúsculas. Mide índices, compresión columnar, retención y amplificación de backfills con datos que tengan la forma de producción.

Paso 3: Hacer que el lote ordinario sea determinista y atómico

Deposita la extracción bajo un snapshot_id inmutable. Antes de tocar el destino, verifica el marcador de finalización, el esquema, la unicidad de clave esperada, el recuento de filas y los totales de control. Desduplica por customer_id usando una versión confiable de la fuente; dos filas de igual rango pero diferentes constituyen un error de entrada, no una razón para elegir una arbitrariamente.

Canonicaliza los atributos rastreados y compara cada fila en staging con la versión de destino actual:

1. No hay clave de negocio actual: inserta su primera versión.
2. Mismos valores rastreados: no hace nada; refrescar loaded_at no debe manufacturar historial.
3. Valores rastreados distintos: cierra la fila actual en el tiempo de vigencia confiable e inserta una nueva versión actual.
4. Clave de destino actual ausente de una instantánea verificada como completa: ciérrala e inserta una versión lápida actual.

Para cada clave modificada, el cierre y la inserción ocurren en una sola transacción de destino o en una operación de tabla atómica. Una regla de unicidad en la versión actual protege contra dos cargadores concurrentes. Registra snapshot_id, load_batch_id y source_version, y rechaza una versión de origen ya aplicada. De este modo, un reintento tras un acuse de recibo perdido converge sin duplicar el historial.

Paso 4: Resolver los hechos en el tiempo del evento de negocio

Al cargar un pedido, resuelve la versión del cliente utilizando la marca de tiempo de negocio del pedido y luego persiste customer_sk en la tabla de hechos. Una búsqueda puntual neutral con respecto al proveedor tiene esta estructura:

SELECT d.customer_sk
FROM dim_customer AS d
WHERE d.customer_id = :customer_id
  AND d.is_deleted = false
  AND d.valid_from <= :order_time
  AND (d.valid_to > :order_time OR d.valid_to IS NULL);

Las desigualdades codifican el intervalo semiabierto. La búsqueda debe devolver exactamente una fila. Cero coincidencias requieren una política explícita de miembro desconocido o cuarentena; múltiples coincidencias demuestran un historial corrupto. Para un hecho que llega tarde, utiliza el tiempo de su evento, no el tiempo de su ingesta. El patrón Tipo 2 de Kimball utiliza la clave subrogada de la versión en los hechos precisamente para que los hechos cargados con anterioridad conserven el perfil histórico contemporáneo.

Paso 5: Tratar las correcciones tardías como cirugía de intervalos

Supongamos que el almacén de datos tiene actualmente Seattle desde el 1 de julio y Denver desde el 12 de julio. El 14 de julio recibe una corrección confiable indicando que Denver entró en vigencia el 10 de julio. Actualizar únicamente la fila actual deja el 10 y el 11 de julio incorrectos. Encuentra la versión que contiene el 10 de julio, cierra Seattle el 10 de julio y mueve el inicio de Denver al 10 de julio. Si la corrección introduce un tercer valor dentro de un intervalo existente, divide ese intervalo y preserva el siguiente límite conocido.

Aplica las correcciones en el orden de versiones de la fuente y bloquea o serializa las actualizaciones por clave de negocio. Conserva la entrada original y una auditoría de correcciones que contenga los intervalos anteriores, los intervalos nuevos, la versión de origen, el lote y el motivo. Vuelve a resolver los hechos afectados cuando el contrato de negocio establezca que sus claves foráneas deben reflejar el historial corregido.

Existe un límite de información: una instantánea observada por primera vez el 14 de julio no puede probar que un valor entró en vigencia el 10 de julio a menos que la fuente incluya un tiempo de negocio confiable u otro registro ordenado. Utiliza el 14 de julio como tiempo de observación o pon en cuarentena la corrección; fecharla retroactivamente de manera silenciosa crearía una precisión ficticia. Si el almacén de datos debe conservar tanto el tiempo de validez del 10 de julio como el tiempo de conocimiento del 14 de julio, agrega un segundo intervalo de tiempo de sistema y clasifica el modelo como bitemporal.

Paso 6: Validar invariantes y medidas de protección operativas

Prueba el destino después de cada lote y antes de la publicación:

• cada clave subrogada es única;
• cada clave de negocio tiene exactamente una versión actual, incluida una lápida para una clave eliminada;
• is_current coincide con un valid_to abierto;
• los intervalos para una clave de negocio nunca se superponen y cada fila tiene valid_from antes de valid_to cuando existe el fin;
• los valores de origen sin cambios no agregan una versión;
• la repetición de la misma versión de origen o lote no produce nuevas filas;
• cada clave subrogada de hecho que no sea desconocida hace referencia a una fila de dimensión vigente en el tiempo del evento del hecho;
• los recuentos de filas insertadas, modificadas, sin cambios y eliminadas cuadran con la instantánea en staging.

Monitorea la completitud de la instantánea, claves duplicadas, tasa de cambio, tasa de eliminación, crecimiento de versiones, correcciones tardías rechazadas, búsquedas de hechos con cero o múltiples coincidencias, latencia de carga y diferencias en reejecuciones. Una desaparición repentina de 40 millones de claves debe fallar de forma restrictiva (fail closed) antes de convertirse en 40 millones de lápidas. Prueba una clave nueva, una instantánea idéntica repetida, un cambio normal, eliminación y recreación, una corrección con tres días de retraso, una versión de origen fuera de orden, una instantánea parcial y una caída del sistema entre el cierre y la inserción.

Ejemplo de respuesta sólida

“Modelaría una fila por versión de cliente, no una fila por cliente. customer_id es la clave de negocio duradera, mientras que cada versión obtiene un nuevo customer_sk. Los campos Tipo 2 se acuerdan con los analistas—por ejemplo, segmento y región de ventas—para que una marca de tiempo de carga o una corrección de formato no generen historial. Cada fila utiliza el intervalo semiabierto [valid_from, valid_to), y exactamente una fila por cliente es la actual.

La fuente escanea 50 millones de filas al día pero cambia alrededor de 500.000. Eso representa aproximadamente 182,5 millones de nuevas versiones por año; a un valor ilustrativo de 300 bytes por versión, el crecimiento en bruto es de unos 54,75 GB antes de la sobrecarga física. Colocaría la instantánea en staging una sola vez y la compararía solo con las filas actuales de la dimensión, en lugar de combinar repetidamente todo el historial.

Cada instantánea tiene un ID estable y debe superar comprobaciones de completitud, unicidad, esquema, recuento de filas y totales de control. Canonicalizo y calculo el hash solo de las columnas Tipo 2 rastreadas. Las claves nuevas se insertan, los hashes iguales no hacen nada y las claves modificadas cierran el intervalo antiguo e insertan una nueva versión en una sola operación atómica. Una clave verificada como ausente cierra la versión antigua e inserta una lápida de eliminación. La versión de origen más el ID de lote hacen que la carga sea idempotente, y una regla de unicidad de fila actual bloquea versiones duplicadas concurrentes.

Los pedidos resuelven customer_sk con valid_from <= order_time y valid_to > order_time, tratando un fin nulo como abierto. La búsqueda debe devolver una versión no eliminada. Los hechos tardíos utilizan el tiempo del pedido, no el tiempo de llegada. Las correcciones tardías de la dimensión se aplican al intervalo que contiene su tiempo de vigencia confiable: se divide o ajusta ese intervalo y se preservan los límites conocidos posteriores. Si la fuente solo proporciona la hora de llegada, no inventaré un tiempo de vigencia tres días anterior. Si se requiere consultar tanto el tiempo de negocio como el tiempo en que el almacén de datos conoció el dato, propondré un modelo bitemporal.

Antes de la publicación, verifico una versión actual por clave de negocio, ausencia de superposición de intervalos, límites válidos, integridad referencial de hechos, conciliación de recuentos entre origen y destino y cero cambios de filas en una reejecución idéntica. Configuro alertas ante tasas anormales de eliminación o cambio y mantengo las instantáneas en bruto junto con una auditoría de correcciones para que un backfill sea explicable y reversible”.

Errores comunes

• Calcular el hash de cada columna de la fuente → Las marcas de tiempo de auditoría crean versiones sin sentido → Calcula el hash únicamente de los atributos Tipo 2 rastreados por contrato tras su canonicalización.
• Usar la clave natural como clave primaria de la dimensión → Múltiples versiones históricas colisionan → Agrupa por la clave de negocio e identifica cada versión con una clave subrogada.
• Mezclar extremos de intervalo inclusivos → Un hecho en un límite de cambio coincide con dos filas → Adopta [valid_from, valid_to) de extremo a extremo.
• Usar el tiempo de ingesta como tiempo de vigencia → Las actualizaciones y hechos tardíos se vinculan al estado histórico incorrecto → Utiliza el tiempo de negocio confiable y define el mecanismo de respaldo (fallback) cuando no esté disponible.
• Tratar cada fila ausente en la instantánea como eliminada → Un extracto parcial puede borrar la dimensión → Exige un marcador de completitud y validaciones de anomalías antes de aplicar la semántica de ausencia.
• Cerrar e insertar en transacciones separadas → Una falla del sistema deja cero o dos filas actuales → Aplica la transición de versión de forma atómica y exige unicidad en la fila actual.
• Crear una versión en cada reejecución → Los reintentos inflan el historial y modifican respuestas pasadas → Persiste las versiones de origen y la identidad del lote, y haz que una entrada idéntica no realice ninguna operación (no-op).
• Reparar solo la fila actual ante una corrección tardía → Los hechos anteriores permanecen asociados al estado incorrecto → Divide o ajusta el intervalo histórico afectado y vuelve a resolver la ventana de hechos impactada.
• Llamar “bitemporal” a cualquier par de marcas de tiempo → Sus significados siguen siendo ambiguos → Nombra explícitamente el tiempo de validez y el tiempo de sistema, y define ambos intervalos y las reglas de corrección.
• Comprobar solo los recuentos de filas → Pueden persistir superposiciones y filas actuales duplicadas → Valida invariantes temporales, referenciales, de idempotencia y de conciliación.

Preguntas de seguimiento y respuestas

Pregunta de seguimiento 1: ¿Por qué no usar Tipo 1 para todos los atributos?

El Tipo 1 es correcto para valores cuya forma anterior no tiene significado analítico, como la corrección de un error tipográfico. Destruye el valor anterior, por lo que no puede responder “¿a qué región pertenecía este pedido en aquel momento?”. Clasifica los atributos según su semántica analítica. Una sola dimensión puede utilizar el Tipo 1 para algunas columnas y el Tipo 2 para otras, siempre que las reglas de actualización sean explícitas.

Pregunta de seguimiento 2: ¿Debe la fila actual usar valid_to = NULL o un valor centinela en el futuro lejano?

Cualquiera de los dos enfoques puede funcionar. NULL hace explícito que está “abierto”, pero requiere predicados compatibles con nulos. Un centinela como la marca de tiempo máxima admitida puede simplificar los filtros de rango, pero puede filtrarse en los reportes o exceder el rango de fechas de otro motor. Elige una representación, haz que is_current sea coherente con ella y prueba cada consulta y conector con la misma convención.

Pregunta de seguimiento 3: ¿Cómo manejas una eliminación seguida de la recreación de la misma clave de negocio?

Cierra la versión de negocio activa en el momento de la eliminación y añade una lápida de eliminación. En el momento de la recreación, cierra la lápida e inserta una nueva versión activa con una nueva clave subrogada. Primero confirma que la fuente realmente reutiliza la misma identidad de entidad; si el identificador se recicló para una persona diferente, introduce una clave duradera que distinga las entidades.

Pregunta de seguimiento 4: ¿Qué ocurre si la fuente no tiene un updated_at confiable?

Compara una lista explícita de columnas rastreadas canónicas, tal como lo hacen las estrategias de comprobación de las herramientas de instantáneas. El intervalo resultante comienza cuando el almacén de datos observa el cambio, no necesariamente cuando ocurrió el cambio de negocio. Documenta esa limitación. Si se requiere un historial exacto con vigencia de negocio, obtén una secuencia de origen, un registro de auditoría, un flujo CDC o un evento de dominio en lugar de fabricar una marca de tiempo.

Pregunta de seguimiento 5: ¿En qué se diferencia esto de una canalización CDC?

CDC responde qué inserciones, actualizaciones y eliminaciones confirmadas ocurrieron y en qué orden de la fuente. SCD Tipo 2 responde cómo los atributos de dimensión seleccionados se convierten en versiones históricas y a qué clave subrogada debe hacer referencia un hecho. Un flujo CDC puede alimentar al cargador de SCD, y una instantánea también puede alimentarlo. Una captura confiable no evita por sí sola los intervalos de validez superpuestos ni las combinaciones temporales erróneas.

Pregunta de seguimiento 6: ¿Cuándo se debe evitar el crecimiento de filas en Tipo 2?

Evítalo para atributos que cambian rápidamente y que no se necesitan para agrupaciones históricas, cargas útiles grandes de texto libre y estados operativos que se representan mejor como eventos o hechos. Utiliza el Tipo 1, una minidimensión separada, una tabla de hechos acumulativa o periódica, o una tabla de historial dedicada según el tipo de consulta. La decisión se guía por la semántica analítica y el costo medido de escritura y consulta.

Pregunta de seguimiento 7: ¿Cómo demuestras que un backfill tardío no corrompió el historial?

Ejecuta el backfill en un área de staging o tabla sombra (shadow table), compara los conjuntos de intervalos por clave de negocio y genera un informe de las versiones insertadas, divididas, acortadas, extendidas y eliminadas. Asegura la ausencia de superposiciones, una sola fila actual, claves subrogadas no afectadas estables y los cambios esperados en las claves de hechos únicamente dentro de la ventana de tiempo corregida. Conserva la versión de origen sin procesar y el manifiesto del lote para reejecuciones y reversiones (rollbacks).