Como fazer backfill de dados em ELT: passo a passo

Um backfill é o reprocessamento de dados históricos que já deviam estar corretos no destino: uma regra de negócio que mudou, um período que ficou por carregar ou uma fonte que chegou atrasada. Fazer backfill em ELT sem duplicar registos e sem partir as cargas diárias assenta num princípio simples — cada janela de tempo é uma fatia que pode ser apagada e reinserida quantas vezes for preciso. Chama-se idempotência, e é o que torna o reprocessamento seguro.

Pré-requisitos

• Um data warehouse com SQL (Fabric Warehouse, Synapse, Databricks SQL, SQL Server, Snowflake ou BigQuery).
• Uma tabela de destino já criada e carregada de forma incremental.
• Uma tabela de staging com os dados brutos do período a reprocessar.
• Permissões de DELETE e INSERT no destino.

Passo 1: Delimitar a janela do backfill

Antes de mexer em dados, escreva o que vai reprocessar: tabela, data inicial, data final e motivo. Um backfill sem janela definida transforma-se num "reprocessa tudo" que demora horas e bloqueia o resto do pipeline. Comece por medir o volume envolvido e guarde este resultado — vai precisar dele no fim.

-- Quantas linhas existem hoje no 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;

Passo 2: Escolher a chave de partição

O backfill idempotente assenta numa coluna que divide a tabela em fatias independentes — normalmente uma data (data_venda, data_evento) ou um identificador de carga. A regra é uma só: tudo o que uma execução escreve tem de caber dentro da fatia que essa mesma execução apaga. Se a tabela de destino ainda não tem essa coluna, acrescente-a antes de avançar; sem ela não há forma segura de reverter uma carga.

Passo 3: Escrever o backfill idempotente (delete + insert)

Este é o coração do processo: numa única transação, apagar a janela e voltar a inseri-la a partir da staging. Se algo falhar a meio, a transação faz rollback e o destino fica exatamente como estava — basta voltar a correr. É o padrão conhecido por delete-insert, ou sobreposição por partição.

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;

Repare em dois detalhes que evitam a maioria dos erros. Os intervalos usam >= e <, nunca BETWEEN — com colunas que têm hora, o BETWEEN deixa cair os registos do último dia. E o filtro do DELETE é exatamente igual ao do INSERT: sempre que os dois divergem, nascem duplicados ou buracos.

Passo 4: Executar por lotes

Reprocessar um ano inteiro numa só transação é o erro mais comum: o log cresce, a query fica horas a correr e um erro na última partição deita fora o trabalho todo. Divida por dia (ou por mês) e corra em ciclo:

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 dia passa a ser uma transação pequena e independente. Se o processo morrer ao 12.º dia, retoma-se a partir daí; e repetir um dia já feito não faz mal nenhum, precisamente porque a operação é idempotente.

Passo 5: Registar o que foi reprocessado

Um backfill que ninguém consegue explicar três meses depois é uma dor de cabeça garantida. Escreva uma linha por partição reprocessada numa tabela de auditoria.

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 o resultado

Três verificações rápidas dizem se o backfill correu bem. Primeiro, compare a contagem por dia com a do Passo 1 e explique qualquer diferença. Segundo, procure duplicados na chave de negócio. Terceiro, confirme que não ficaram dias em falta dentro da janela.

-- Duplicados na chave 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;

Zero linhas devolvidas significa backfill limpo. Se aparecerem duplicados, o erro está quase sempre no mesmo sítio: o filtro do DELETE não cobre exatamente as linhas que o INSERT escreve.

Conclusão

Com uma janela bem definida, o padrão delete-insert dentro de uma transação e execução por lotes, o backfill deixa de ser uma operação de risco e passa a ser rotina — pode correr o mesmo comando dez vezes seguidas que o resultado é sempre o mesmo. O passo seguinte natural é parametrizar este SQL num pipeline (Azure Data Factory, Fabric ou dbt) para lançar um backfill sem abrir o editor. Antes disso, faça esta pergunta à sua tabela mais importante: se tivesse de reprocessar o mês passado agora mesmo, o filtro do DELETE apanharia exatamente as linhas que o INSERT vai escrever?