Backfilling history into a live Lakeflow streaming table with ONCE flows: a checklist for boundaries, CDC targets and full refresh safety

A pipeline goes live with Auto Loader on today's landing folder. Then the business asks for the last three years. The old way was to stop the pipeline, rewrite the source to read everything, and full refresh. You lost hours of freshness and reprocessed everything. One-time flows (INSERT INTO ONCE in SQL, once=True in Python) add history to the same target while the live flow keeps running. They're simple to write and easy to get subtly wrong. Work through this checklist before you merge the change.

#1. Pick the right flow type for the target

• The flow type matches the target. A plain INSERT INTO ONCE into an AUTO CDC target fails validation.
• For AUTO CDC backfills, the keys (count and order) and the SEQUENCE BY data type match the live flow.
• For SCD2 seeds, every key's first live change sequences after its last seeded change. Check this on a sample of keys. Otherwise history and current rows interleave incorrectly.

#2. Define a hard boundary between history and live data

The live Auto Loader flow and the backfill must not both load the same events. Pick one cut-off and enforce it on both sides:

CREATE OR REFRESH STREAMING TABLE prod.bronze.registrations;

-- Live flow: only events from the cut-off on
CREATE FLOW registrations_live
AS INSERT INTO prod.bronze.registrations BY NAME
SELECT * FROM STREAM read_files(
  '/Volumes/prod/landing/registrations/live/',
  format => 'json')
WHERE event_ts >= TIMESTAMP '2026-10-01';

-- One backfill flow per year: runs once, then idles
CREATE FLOW registrations_backfill_2024
AS INSERT INTO ONCE prod.bronze.registrations BY NAME
SELECT * FROM read_files(
  '/Volumes/prod/archive/registrations/year=2024/*/*',
  format => 'json', inferColumnTypes => true)
WHERE event_ts < TIMESTAMP '2026-10-01';

• The cut-off is on an event time column, not on file arrival. Late files in the live folder can contain old events, and archives can contain events after the cut-off.
• If the live flow already exists without the filter, add a dedupe step downstream instead of changing it. Adding a filter to an existing streaming flow doesn't reprocess what's already loaded.
• A business key exists that silver can deduplicate on, as a second line of defence.

#3. Check the schema before the run

• BY NAME is used, so column order differences don't silently misalign columns.
• Historical files have been profiled for type drift. A year where amount was a string becomes nulls or a failure. Use explicit schemaHints or cast in the SELECT.
• Columns that only exist in newer data are nullable on the target.
• Expectations defined for the target behave as intended on historical data. Test on one month first, because a FAIL UPDATE expectation that trips on 2023 data stops the whole update, including the live flow.

#4. Size and split the work

• The history is split into several ONCE flows (per year or per month), not one huge flow. Each flow is retried independently and they run in parallel.
• Generate them in Python if there are many:

from pyspark import pipelines as dp

for year in range(2021, 2026):
    @dp.append_flow(target="registrations", once=True,
                    name=f"registrations_backfill_{year}")
    def backfill(year=year):
        return (spark.read.format("json")
                .load(f"/Volumes/prod/archive/registrations/year={year}/*/*")
                .where("event_ts < TIMESTAMP '2026-10-01'"))

• The year=year default argument is present. Without it, every flow captures the last loop value.
• Pipeline compute can take the burst: raise the autoscaling maximum for the backfill update, or use serverless. Put the old limit back afterwards.
• For tens of TB, the target's file layout is set before the run (liquid clustering keys, optimized writes), so you don't have to rewrite it later.

#5. Protect against the full-refresh trap

A ONCE flow stays in the graph after it completes. A full refresh of the target re-runs it. That's useful if the archive is still there, and destructive if it isn't.

Caution

If the archive files are moved, deleted or lifecycle-tiered to Archive tier after the backfill, a later full refresh rebuilds the table with live data only. The history is gone from the table, and Delta time travel won't help after VACUUM.

• The archive source stays readable for as long as the flow is in code, or you deliberately remove the flow and accept that a full refresh no longer restores history.
• Full refresh is blocked on the table where history matters:

CREATE OR REFRESH STREAMING TABLE prod.bronze.registrations
TBLPROPERTIES ('pipelines.reset.allowed' = 'false');

Set it in the pipeline source, where pipeline-managed properties belong. Check what your release does on a full refresh with this set, using a dev copy, before you rely on it.

• A deep clone of the backfilled table is taken after validation, as a recovery point that doesn't depend on the archive:

CREATE TABLE prod.recovery.registrations_after_backfill
DEEP CLONE prod.bronze.registrations;

#6. Run it without a full refresh

• The change is deployed as a normal update. New flows run on the next update; don't select full refresh.
• If you trigger by API, refresh only the affected tables:

databricks api post /api/2.0/pipelines/<pipeline-id>/updates \
  --json '{"refresh_selection": ["registrations"], "full_refresh": false}'

• Progress is watched in the event log:

SELECT timestamp,
       origin.flow_name,
       details:flow_progress.status                       AS status,
       details:flow_progress.metrics.num_output_rows      AS rows_out
FROM event_log(TABLE(prod.bronze.registrations))
WHERE event_type = 'flow_progress'
  AND origin.flow_name LIKE 'registrations_backfill_%'
ORDER BY timestamp DESC;

#7. Validate

• Row counts per period match the source:

SELECT year(event_ts) AS yr, count(*) AS rows_loaded
FROM prod.bronze.registrations
GROUP BY ALL ORDER BY yr;

Compare with a count over read_files on the archive (batch, outside the pipeline).

• No duplicates across the boundary: count(*) vs count(DISTINCT registration_id) for the cut-off week.
• Downstream materialized views and gold tables picked up history. Some may need a full recompute, which is expected once, so plan the cost.
• DESCRIBE HISTORY shows the backfill commits, and the live flow's latency didn't regress during the run.

#Prevent it next time

• Design live flows with an event-time lower bound from day one, so adding history later is only a new flow.
• Keep raw archives on Cool tier rather than Archive tier while any ONCE flow references them.
• Add pipelines.reset.allowed = false to bronze tables that hold history you can't reload.
• Review every pull request that adds or removes a ONCE flow with "what happens on full refresh?" in mind.