How to Backfill Data in ELT: Step by Step

A backfill is the reprocessing of historical data that should already be correct in the destination: a business rule that changed, a period that was never loaded, or a source that arrived late. Backfilling in ELT without creating duplicates or breaking the daily loads rests on one simple principle — each time window is a slice that can be deleted and reinserted as many times as needed. That is idempotency, and it is what makes reprocessing safe.

Prerequisites

• A SQL data warehouse (Fabric Warehouse, Synapse, Databricks SQL, SQL Server, Snowflake or BigQuery).
• A destination table already created and loaded incrementally.
• A staging table holding the raw data for the period to reprocess.
• DELETE and INSERT permissions on the destination.

Step 1: Define the backfill window

Before touching any data, write down what you are going to reprocess: table, start date, end date and reason. A backfill without a defined window quickly turns into a "reprocess everything" job that runs for hours and blocks the rest of the pipeline. Start by measuring the volume involved and keep this result — you will need it at the end.

-- How many rows are in the destination today, per day?
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;

Step 2: Pick the partition key

An idempotent backfill relies on a column that splits the table into independent slices — usually a date (data_venda, data_evento) or a load identifier. There is only one rule: everything a run writes must fit inside the slice that same run deletes. If your destination table does not have that column yet, add it before moving on; without it there is no safe way to roll a load back.

Step 3: Write the idempotent backfill (delete + insert)

This is the heart of the process: inside a single transaction, delete the window and reinsert it from staging. If anything fails midway, the transaction rolls back and the destination is left exactly as it was — you simply run it again. This is the pattern known as delete-insert, or partition overwrite.

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;

Notice two details that prevent most errors. The ranges use >= and <, never BETWEEN — with columns that carry a time component, BETWEEN silently drops the last day's rows. And the DELETE filter is identical to the INSERT filter: whenever the two diverge, you get duplicates or gaps.

Step 4: Run it in batches

Reprocessing a whole year in a single transaction is the most common mistake: the log grows, the query runs for hours, and one error in the last partition throws away all the work. Split the job by day (or by month) and loop:

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

Each day becomes a small, independent transaction. If the process dies on day 12, you resume from there; and re-running a day that is already done does no harm at all, precisely because the operation is idempotent.

Step 5: Log what was reprocessed

A backfill nobody can explain three months later is a guaranteed headache. Write one row per reprocessed partition into an audit table.

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';

Verify the result

Three quick checks tell you whether the backfill went well. First, compare the row count per day with the one from Step 1 and explain any difference. Second, look for duplicates on the business key. Third, confirm that no days are missing inside the window.

-- 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 rows returned means a clean backfill. If duplicates show up, the mistake is almost always in the same place: the DELETE filter does not cover exactly the rows the INSERT writes.

Conclusion

With a well-defined window, the delete-insert pattern inside a transaction and batched execution, a backfill stops being a risky operation and becomes routine — you can run the same command ten times in a row and the result is always the same. The natural next step is to parameterise this SQL inside a pipeline (Azure Data Factory, Fabric or dbt) so you can launch a backfill without opening an editor. Before that, ask your most important table one question: if you had to reprocess last month right now, would the DELETE filter catch exactly the rows the INSERT is going to write?