Planteamiento y contexto
Un sistema de pagos reporta 1,000 órdenes ayer, pero el reporte del data warehouse tiene 998. Diseña un pipeline que encuentre diferencias a través del origen, la capa de aterrizaje (landing), los modelos de transformación y el reporte.
No limites la respuesta a dbt, Airflow o un data warehouse específico. El objetivo es una cadena de evidencia repetible que distinga registros faltantes, duplicados, discordantes, tardíos y con definiciones distintas.
Qué está evaluando el entrevistador
Granularidad de la conciliación
Define la clave de negocio, la semántica temporal y la precisión monetaria antes de elegir una granularidad por orden, comercio, día o lote. Una validación basada únicamente en conteos puede ocultar errores de compensación.
Validaciones explicables
Verifica conteos, montos, distribuciones de estado, unicidad de claves y detalles muestreados. Guarda de forma persistente la versión de la regla y la instantánea de entrada para que cada resultado sea reproducible.
Incidentes de ciclo cerrado
Clasifica las diferencias, asigna un responsable, preserva la evidencia, admite la reproducción (replay) y registra el cierre. Un correo electrónico sin un caso rastreable no constituye un ciclo operativo.
Operaciones seguras
Maneja datos tardíos, reejecuciones idempotentes, particiones, cambios de esquema, frescura (freshness) e historial de auditoría sin que las reparaciones creen nuevas discrepancias.
Preguntas de clarificación que debes hacer
- ¿Qué zona horaria y definición de tiempo de evento (event-time) utiliza "ayer"?
- ¿Cuál es la clave de negocio de la orden y cómo se contabilizan los reembolsos y cancelaciones?
- ¿Puede el origen enviar actualizaciones o eliminaciones tardías?
- ¿El data warehouse es por lotes (batch), streaming o híbrido?
- ¿Qué moneda y precisión decimal aplican?
- ¿La remediación debe ser automática, reproducida o aprobada manualmente primero?
Estructura de respuesta en 30 segundos
"Congelaría una ventana de tiempo e instantánea, conciliaría conteos, montos y estados por clave de orden, y luego profundizaría en errores de registros faltantes, duplicados, no llegados a tiempo y de transformación. Cada diferencia almacena una versión de regla, resumen del origen, resumen del data warehouse y un responsable. Marcas de agua (watermarks) y una ventana de reintento separan los datos tardíos de las pérdidas reales. Un backfill o replay idempotente repara los casos aprobados, y luego se vuelve a conciliar la misma instantánea. Las entradas, salidas, alertas y acciones humanas se registran en una tabla de auditoría."
Análisis detallado paso a paso
Paso 1: Congelar el alcance y la instantánea
Registra el lote de origen, el rango de tiempo de evento, el tiempo de procesamiento, la zona horaria y el ID de instantánea (snapshot ID). Los resultados deben apuntar al mismo conjunto de entrada en lugar de fluctuar mientras el origen cambia.
Paso 2: Normalizar claves
Normaliza el ID de orden, el ID de comercio, el mapeo de estados, la moneda y la precisión del monto. Conserva un hash o resumen de cada lado; retén únicamente el mínimo de datos sensibles necesarios para el diagnóstico.
Paso 3: Ejecutar validaciones en capas
Compara primero el conteo y el monto total, luego agrupa por comercio, fecha y estado. Continúa con validaciones de unicidad de clave, nulos, duplicados, tolerancia y anti-joins detallados. Que los totales coincidan no demuestra que los detalles sean idénticos.
Paso 4: Separar la llegada tardía de la modificación
Utiliza el tiempo de evento y una marca de agua (watermark) para distinguir los datos que aún no han llegado de los datos faltantes. Mantén los registros pendientes dentro de una ventana de backfill; escala solo después de que se cierre. Recalcula actualizaciones, reembolsos y eliminaciones utilizando la versión o el tiempo de cambio.
Paso 5: Clasificar y remediar
Clasifica los casos por monto, conteo de órdenes, impacto en el negocio y duración. La reparación automática se limita a backfills o replays idempotentes. Los cambios en las definiciones financieras requieren aprobación y evidencia de antes y después.
Paso 6: Reejecutar y auditar
Ejecuta de manera idempotente por snapshot ID, partición y versión de regla. Almacena el alcance de entrada, la versión de la consulta, las métricas, las diferencias en los detalles, las alertas, el responsable, los reintentos y la hora de cierre para su revisión.
Respuesta modelo de alta calidad
"Generaría un snapshot_id para cada lote de origen y congelaría una ventana de tiempo de evento en UTC. Una capa de normalización alinea los IDs de orden, estados, monedas y precisión de montos. Comparamos conteos, montos y distribuciones de estado, y luego usamos anti-joins de clave primaria para encontrar registros exclusivos del origen, exclusivos del data warehouse, duplicados y con discrepancias en los montos.
Una marca de agua y una ventana de backfill de dos horas clasifican los eventos tardíos como pendientes; solo las diferencias no resueltas tras cerrarse la ventana se escalan. La tabla de diferencias almacena la versión de la regla, los resúmenes de ambos registros, la diferencia en el monto, el responsable y la evidencia. Un replay aprobado es idempotente respecto a snapshot_id más la clave de negocio. Después de la reparación, reejecuto la misma instantánea y escribo el rango de entrada, la versión del código, las alertas y la hora de cierre en la tabla de auditoría."
Errores comunes
- Comparación basada únicamente en el conteo total → los errores de compensación permanecen ocultos → utiliza anti-joins por clave de negocio y persiste las diferencias en los detalles.
- Uso del tiempo de procesamiento como tiempo de evento → los registros tardíos o con diferentes zonas horarias desplazan las ventanas → define el tiempo de evento, el tiempo de procesamiento y la zona horaria por separado.
- Omisión de la semántica de reembolsos, cancelaciones, actualizaciones y eliminaciones → cada capa explica un número diferente → versiona el mapeo de estados y las reglas de tratamiento.
- Comparación de dinero con punto flotante → el ruido decimal se convierte en un falso incidente → utiliza unidades menores enteras, moneda y una tolerancia explícita.
- Alertar inmediatamente por cada registro tardío → incidentes ruidosos provocan reparaciones erróneas → utiliza una marca de agua y una ventana de backfill con un estado pendiente.
- Reparación no idempotente → las reejecuciones duplican filas → realiza upserts de forma idempotente por instantánea y clave de negocio.
- Falta de responsable, evidencia o estado de cierre → los incidentes no se pueden asignar ni revisar → agrega un caso y un registro de auditoría.
- Retención exclusiva de las cifras finales → el resultado no se puede reproducir → almacena la instantánea de entrada, la versión de la regla y la versión del código.
Preguntas de seguimiento y respuestas
Pregunta de seguimiento 1: ¿Qué sucede si los totales coinciden pero los registros difieren?
Utiliza anti-joins por clave de negocio, validaciones de claves duplicadas y distribuciones agrupadas para encontrar adiciones y eliminaciones compensatorias. Guarda de forma persistente las diferencias de detalle para su revisión.
Pregunta de seguimiento 2: ¿Cómo evitas falsas alertas por datos tardíos?
Utiliza el tiempo de evento, una marca de agua y una ventana de backfill explícita. Marca los casos como pendientes dentro de la ventana y escala los casos no resueltos después de que esta finalice, registrando la versión de la ventana.
Pregunta de seguimiento 3: ¿Cómo puede reejecutarse la reparación de forma segura?
Utiliza el snapshot ID, la partición y la clave de negocio como claves de idempotencia; aplica upsert o desduplica las escrituras; compara conteos y montos antes y después de cada reintento.
Pregunta de seguimiento 4: ¿Cómo pruebas las reglas de conciliación?
Crea fixtures para casos de datos faltantes, duplicados, tardíos, reembolsos y multidivisa. Prueba versiones de reglas, tolerancias, fechas límite y zonas horarias, y luego monitorea las tasas de falsos positivos.
Pregunta de seguimiento 5: ¿Cómo enrutas las alertas y la asignación de responsables?
Enruta por monto, conteo, duración y severidad para el negocio. Vincula cada alerta a un lote, evidencia y acción de reparación; el cierre requiere un motivo y debe permanecer auditable.
Fuente 1: Fuentes de dbt y pruebas de fuentes
La documentación de fuentes de dbt Developer Hub describe cómo declarar fuentes, construir linaje, asociar pruebas de datos y medir la frescura (freshness), patrones de gobernanza útiles para las entradas de conciliación.
Fuente 2: Frescura y ventanas de SLA
La guía de source-freshness de dbt Developer Hub muestra campos loaded-at, umbrales de advertencia/error y resultados de instantáneas para la gestión de frescura, lo que orienta la definición de ventanas de datos tardíos y escalamiento.
Fuente 3: Pruebas de calidad de datos
La guía de calidad de datos de dbt Labs cubre unicidad, relaciones, validaciones de nulos y frescura, ilustrando cómo las validaciones automatizadas deben conectarse con modelos versionados y alertas.