De ETL Frágil a ELT Declarativo y Arquitectura Medallion (Bronze, Silver, Gold)

1. Separación de Capas de Refinamiento y Garantía de Reprocesabilidad (Replayability)

flowchart LR
    SRC["Fuentes OLTP / CDC Debezium / APIs"] --> BRZ["Capa Bronze (Raw Append-Only Inmutable + Payload JSON)"]
    BRZ -->|"Deduplicación, Tipado Estricto, PII Masking"| SLV["Capa Silver (Entidades Conformed, Normalizadas 3NF / OBT)"]
    SLV -->|"Lógica de Negocio, Star Schema, Métricas"| GLD["Capa Gold (Marts de Finanzas, Producto, Feature Store)"]

1. Bronze (staging / raw): Conserva la historia cruda inmutable (append-only) con metadatos de ingesta (_ingested_at, _source_lsn). Permite reconstruir cualquier tabla aguas abajo desde cero si se descubre un bug de lógica meses después. 2. Silver (intermediate / core): Aplica contratos de tipos, deduplicación por clave primaria con ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1 y resolución de dimensiones. 3. Gold (marts): Tablas de hechos (Fact Tables), dimensiones (Star Schema) y tablas anchas desnormalizadas (One Big Table - OBT) listas para consumo BI y ML.

Modelado Dimensional de Kimball: Granularidad de Tablas de Hechos y Slowly Changing Dimensions (SCD Tipo 2)

1. Los 3 Tipos de Tablas de Hechos (Fact Tables)

1. Transactional Fact Table: Una fila por evento atómico (ej. un pago procesado). Aditiva en todas las dimensiones. 2. Periodic Snapshot Fact Table: Una fila por entidad y periodo fijo (ej. saldo diario de cuenta al cierre de las 23:59:59). Semi-aditiva: los saldos se pueden sumar entre clientes en un mismo día, pero jamás se suman a lo largo del eje del tiempo (se promedian o se toma LAST_VALUE). 3. Accumulating Snapshot Fact Table: Una fila por ciclo de vida de un proceso con múltiples hitos temporales (order_created_at, paid_at, shipped_at, delivered_at).

2. Slowly Changing Dimensions Tipo 2 (SCD Type 2 / dbt snapshot)

Para preservar la verdad histórica cuando un cliente cambia de segmento (SMB $\to$ ENTERPRISE), generamos una Surrogate Key determinista (md5(natural_key || valid_from)) y mantenemos intervalos de vigencia [dbt_valid_from, dbt_valid_to) donde el registro activo tiene dbt_valid_to IS NULL.

Star Schema vs. One Big Table (OBT) en Motores MPP Columnales Modernos (BigQuery, Snowflake, DuckDB)

1. Cuándo Desnormalizar a OBT Supera al Star Schema Clásico

En motores MPP columnares que comprimen mediante Run-Length Encoding (RLE) y Dictionary Encoding por bloque columnar, repetir el nombre de un país o segmento en $100$ millones de filas ocupa apenas unos kilobytes en disco, pero ahorra el costo de red (Shuffle / Broadcast Join) de unir la tabla de hechos con 8 dimensiones en cada consulta BI. - Patrón Híbrido Recomendado: Mantener el Star Schema (Hechos + Dimensiones SCD2) en la capa Silver/Core para gobernanza e integridad, y materializar vistas/tablas OBT en la capa Gold para las consultas de alta concurrencia.

Estrategias Incrementales en dbt: MERGE vs. INSERT OVERWRITE (Particiones) y Microbatching

1. El Costo Oculto de incremental_strategy = 'merge' en Tablas de Billones de Filas

Ejecutar un MERGE INTO target USING source ON target.id = source.id sin poda de partición obliga al motor a escanear toda la tabla histórica de destino para buscar coincidencias de id.

Optimización de Grado Principal: 1. insert_overwrite (o delete+insert por partición): Reemplaza atómicamente las particiones completas afectadas (ej. últimos 3 días) sin hacer join fila a fila. 2. Incremental Predicates (incremental_predicates): Si usas merge, restringe siempre el predicado del destino: DBT_INTERNAL_DEST.event_date >= DATEADD(day, -3, CURRENT_DATE) para habilitar Partition Pruning en el destino.

Manejo de Eventos Tardíos (Late-Arriving Data) con Ventanas de Re-Procesamiento (Lookback Windows)

1. Por Qué WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }}) Pierde Datos en Producción

En dispositivos móviles offline, colas distribuidas o conciliaciones bancarias, un evento ocurrido a las 14:00 (event_ts) puede llegar al almacén a las 18:30 (_ingested_at). Si tu filtro incremental solo mira event_ts > max_event_ts, ese evento tardío jamás será procesado.

Patrón Determinista Sin Pérdida: Filtrar por el timestamp de llegada al sistema (_ingested_at) o aplicar una Ventana de Lookback Superpuesta combinada con deduplicación por unique_key:

{% if is_incremental() %}
  WHERE event_ts >= (
    SELECT COALESCE(MAX(event_ts), '1970-01-01'::timestamptz) - INTERVAL '72 hours'
    FROM {{ this }}
  )
{% endif %}

Arquitectura dbt Mesh: Contratos de Modelos (contract: enforced), Versionado de Esquemas y Exposures

1. Desacoplamiento Entre Dominios con contract: { enforced: true }

En organizaciones con múltiples equipos de datos, un modelo público compartido entre dominios declara un contrato estricto en su YAML: - Define explícitamente el nombre de cada columna, su data_type exacto y restricciones not_null. - Si un desarrollador altera el tipo de una columna o la elimina en su PR, dbt falla en tiempo de compilación/ejecución antes de corromper los modelos de los equipos consumidores, exigiendo publicar una versión v2 con ventana de deprecación.

Leyes de la Orquestación Idempotente: Parametrización por Intervalo Lógico (data_interval_start/end) y Backfills

1. El Pecado Capital de Usar NOW() o CURRENT_DATE Dentro de un Pipeline Orquestado

Una tarea de datos es idempotente si ejecutarla $1$ vez o $10$ veces para la misma ventana lógica $[t_{\text{start}}, t_{\text{end}})$ produce exactamente el mismo estado en la tabla de destino sin duplicar filas ni alterar resultados.

Si tu script usa WHERE created_at >= CURRENT_DATE - 1, y tres semanas después ejecutas un Backfill histórico para reparar el día 2026-09-15, la tarea volverá a leer los datos de hoy en lugar de los del 15 de septiembre. Toda consulta debe recibir como parámetros inmutables {{ data_interval_start }} y {{ data_interval_end }} del orquestador.

Evolución de la Orquestación: Task-Centric (Apache Airflow) vs. Software-Defined Assets (Dagster)

- Orquestación Centrada en Tareas (Imperativa): Define verbos (extract_users >> transform_users >> load_users). El orquestador sabe si un script corrió, pero ignora qué tabla produjo, si sus particiones están frescas o cuál es el linaje de columnas. - Orquestación Centrada en Activos (Declarativa - Dagster): El nodo primario del grafo es el Activo de Datos (la tabla, vista dbt o modelo ML) junto con sus particiones y Freshness Policies, unificando la ejecución de modelos dbt y scripts Python en un único grafo de linaje.

Calidad de Datos Zero-Defect: El Patrón Write-Audit-Publish (WAP) y Circuit Breakers ante Datos Corruptos

1. Por Qué Auditar una Tabla DESPUÉS de Publicarla en Producción es Demasiado Tarde

Si primero escribes los datos en prod.fct_revenue y luego corres los tests de calidad, durante los 5 minutos que tardan los tests (o cuando fallan) los dashboards ejecutivos y los modelos de ML ya consumieron datos corruptos.

flowchart LR
    WR["1. WRITE: Materializar en Tabla/Rama Staging Aislada (audit.fct_revenue__temp)"] --> AUD["2. AUDIT: Ejecutar Suite de Invariantes (Unicidad, Nulls, Reconciliación Contable, Volumetría)"]
    AUD -->|"100% Tests PASS"| PUB["3. PUBLISH: Zero-Copy Clone / Atomic Swap a prod.fct_revenue"]
    AUD -->|"Fallo Detectado"| CB["CIRCUIT BREAKER: Abortar Swap, Retener Tabla Anterior y Alertar PagerDuty"]

Observabilidad de Datos (Data Observability): Los 5 Pilares (Freshness, Volume, Distribution, Schema, Lineage)

Además de tests unitarios estáticos (not_null, unique), el Circuit Breaker del paso Audit en WAP valida invariantes estadísticas dinámicas: - Reconciliación Financiera Cruzada: $\left|\sum \text{Gold.net\_amount} - \sum \text{Silver.net\_amount}\right| = 0.00$. - Control de Volumetría: Rechazar cualquier lote cuyo conteo de filas caiga más de un $50\%$ respecto a la mediana de los últimos 7 días equivalentes.

Laboratorio Práctico: Point-in-Time Join Exacto entre Tabla de Hechos y Dimensión Histórica SCD Tipo 2

Contexto del Desafío de Analytics Engineering

En nuestro Data Warehouse tenemos una tabla de hechos de pagos fct_payments y una dimensión histórica de clientes modelada como Slowly Changing Dimension Tipo 2 (dim_customer_scd2), donde un cliente cambió de plan (tier) a mitad de año: - fct_payments(payment_id TEXT, customer_id TEXT, payment_ts DATE, amount_usd NUMERIC) - dim_customer_scd2(surrogate_key TEXT, customer_id TEXT, customer_tier TEXT, valid_from DATE, valid_to DATE) (donde valid_to IS NULL representa la versión actualmente vigente).

Para atribuir los ingresos al customer_tier exacto que tenía el cliente en la fecha en que ocurrió cada pago, debemos hacer un Point-in-Time Join: p.payment_ts >= d.valid_from AND (p.payment_ts < d.valid_to OR d.valid_to IS NULL)

Objetivo de la Consulta SQL

Evaluación de Certificación: DB-1501

Demuestra tu dominio sobre modelado dimensional Kimball, SCD Tipo 2, modelos incrementales en dbt y el patrón Write-Audit-Publish (WAP).