Cómo hacer backfill de datos en ELT: paso a paso

Un backfill es el reprocesamiento de datos históricos que ya deberían estar correctos en el destino: una regla de negocio que cambió, un periodo que quedó sin cargar o una fuente que llegó tarde. Hacer backfill en ELT sin duplicar registros y sin romper las cargas diarias se apoya en un principio sencillo: cada ventana de tiempo es una porción que puede borrarse y volver a insertarse tantas veces como haga falta. Eso es la idempotencia, y es lo que hace seguro el reprocesamiento.

Requisitos previos

• Un data warehouse con SQL (Fabric Warehouse, Synapse, Databricks SQL, SQL Server, Snowflake o BigQuery).
• Una tabla de destino ya creada y cargada de forma incremental.
• Una tabla de staging con los datos en bruto del periodo a reprocesar.
• Permisos de DELETE e INSERT en el destino.

Paso 1: Delimitar la ventana del backfill

Antes de tocar datos, escribe qué vas a reprocesar: tabla, fecha inicial, fecha final y motivo. Un backfill sin ventana definida se convierte en un "reprocesa todo" que tarda horas y bloquea el resto del pipeline. Empieza por medir el volumen implicado y guarda este resultado: lo necesitarás al final.

-- Cuantas filas hay hoy en el destino, por dia?
SELECT data_venda, COUNT(*) AS linhas
FROM dw.factos_vendas
WHERE data_venda >= '2026-05-01'
  AND data_venda <  '2026-06-01'
GROUP BY data_venda
ORDER BY data_venda;

Paso 2: Elegir la clave de partición

El backfill idempotente se apoya en una columna que divide la tabla en porciones independientes: normalmente una fecha (data_venda, data_evento) o un identificador de carga. La regla es una sola: todo lo que una ejecución escribe debe caber dentro de la porción que esa misma ejecución borra. Si tu tabla de destino todavía no tiene esa columna, añádela antes de continuar; sin ella no hay forma segura de revertir una carga.

Paso 3: Escribir el backfill idempotente (delete + insert)

Este es el corazón del proceso: dentro de una única transacción, borrar la ventana y volver a insertarla desde staging. Si algo falla a medio camino, la transacción hace rollback y el destino queda exactamente como estaba: basta con volver a ejecutarlo. Es el patrón conocido como delete-insert, o sobrescritura por partición.

BEGIN TRANSACTION;

DELETE FROM dw.factos_vendas
WHERE data_venda >= '2026-05-01'
  AND data_venda <  '2026-06-01';

INSERT INTO dw.factos_vendas (data_venda, id_cliente, id_produto, quantidade, valor)
SELECT
    CAST(s.event_date AS date),
    s.customer_id,
    s.product_id,
    s.qty,
    s.qty * s.unit_price
FROM staging.vendas_raw AS s
WHERE s.event_date >= '2026-05-01'
  AND s.event_date <  '2026-06-01'
  AND s.status = 'confirmed';

COMMIT;

Fíjate en dos detalles que evitan la mayoría de los errores. Los intervalos usan >= y <, nunca BETWEEN: con columnas que llevan hora, BETWEEN deja fuera los registros del último día. Y el filtro del DELETE es idéntico al del INSERT: cuando ambos difieren, aparecen duplicados o huecos.

Paso 4: Ejecutar por lotes

Reprocesar un año entero en una sola transacción es el error más común: el log crece, la consulta tarda horas y un fallo en la última partición tira todo el trabajo. Divide por día (o por mes) y ejecuta en bucle:

from datetime import date, timedelta

inicio = date(2026, 5, 1)
fim    = date(2026, 6, 1)

dia = inicio
while dia < fim:
    seguinte = dia + timedelta(days=1)
    cursor.execute(SQL_BACKFILL, (dia, seguinte, dia, seguinte))
    conexao.commit()
    print(f"Backfill concluido: {dia}")
    dia = seguinte

Cada día pasa a ser una transacción pequeña e independiente. Si el proceso muere en el día 12, se retoma desde ahí; y repetir un día ya hecho no causa ningún daño, precisamente porque la operación es idempotente.

Paso 5: Registrar lo que se ha reprocesado

Un backfill que nadie sabe explicar tres meses después es un dolor de cabeza asegurado. Escribe una fila por partición reprocesada en una tabla de auditoría.

INSERT INTO dw.log_cargas (tabela, particao, linhas, tipo, executado_em, motivo)
SELECT 'dw.factos_vendas', '2026-05-01', COUNT(*), 'backfill', SYSDATETIME(),
       'Correcao da regra de calculo do valor'
FROM dw.factos_vendas
WHERE data_venda = '2026-05-01';

Verificar el resultado

Tres comprobaciones rápidas te dicen si el backfill salió bien. Primero, compara el recuento por día con el del Paso 1 y explica cualquier diferencia. Segundo, busca duplicados en la clave de negocio. Tercero, confirma que no falta ningún día dentro de la ventana.

-- Duplicados en la clave de negocio
SELECT id_venda, COUNT(*) AS ocorrencias
FROM dw.factos_vendas
WHERE data_venda >= '2026-05-01'
  AND data_venda <  '2026-06-01'
GROUP BY id_venda
HAVING COUNT(*) > 1;

Cero filas devueltas significa backfill limpio. Si aparecen duplicados, el error está casi siempre en el mismo sitio: el filtro del DELETE no cubre exactamente las filas que el INSERT escribe.

Conclusión

Con una ventana bien definida, el patrón delete-insert dentro de una transacción y la ejecución por lotes, el backfill deja de ser una operación de riesgo y se convierte en rutina: puedes ejecutar el mismo comando diez veces seguidas y el resultado será siempre el mismo. El siguiente paso natural es parametrizar este SQL en un pipeline (Azure Data Factory, Fabric o dbt) para lanzar un backfill sin abrir el editor. Antes de eso, hazle una pregunta a tu tabla más importante: si tuvieras que reprocesar el mes pasado ahora mismo, ¿el filtro del DELETE cogería exactamente las filas que el INSERT va a escribir?