Templates in development

Written for Databricks, Snowflake and Fabric and tested on DuckDB. None has run on a real platform yet, and none is released.

Backfill

Backfill: replay a date range safely.

Re-run a pipeline's transformation for a past date range, one partition at a time, replacing exactly those partitions. It resumes after a failure and gives the same result when run twice.

Template in development
Repair
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

06 / 10

  1. Source
  2. Bronze (used)
  3. Silver (used)
  4. Gold (used)
  5. Apps
Group
Repair
Works across
Bronze to Gold
Reads
A pipeline and a date range
Writes
Replaced partitions
Slots
7 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What Backfill does.

Re-run a pipeline for a past date range, one partition at a time, without touching the rest.

What it does

Backfill takes a date range and runs a pipeline's transformation for each partition in it, replacing exactly those partitions and nothing else. It runs in order, records which partitions it has finished and can resume after a failure.

Because each partition is replaced rather than appended to, a backfill is safe to repeat. Running the same range twice gives the same table as running it once.

Use it when

  • A new column or metric needs history.
  • A bug fix must be applied to past data.
  • A source re-delivers a period.
  • A new pipeline needs its history loaded.

Use another shape when

Slots

Inputs, outputs and slots.

What the Backfill template reads, what it writes and the typed slots it asks you for. The agent fills most slots from your catalog and your request.

In

Inputs

  • The pipeline whose transformation is re-run
  • A start date, an end date and a grain such as day or month
  • The log of partitions already finished

Backfill

  1. Source
  2. Bronze (used)
  3. Silver (used)
  4. Gold (used)
  5. Apps

Out

Outputs

  • Replaced partitions in the target, and nothing outside the range
  • An updated log of finished partitions
  • A report: partitions run, rows written and any failure

Template slots

7 slots · names and types may change before release

pipeline
identifier
The pipeline or model whose transformation is re-run.
target_table
table
The table whose partitions are replaced.
partition_column
column
The date column the target is partitioned or clustered by.
grain
enum
The size of a partition: day or month.
start_date
string
The first date of the range, ISO format.
end_date
string
The last date of the range, ISO format.
max_parallel
integer
How many partitions may run at once. One means strictly in order.

Slot types are identifier, table, column, columns, expression, expressions, string, integer, boolean, duration and enum. What is a template slot.

Testing

Tested before it ships.

Every template comes with golden tests that run on DuckDB on your machine before anything reaches your platform.

How a run goes

The golden test starts from a damaged table and checks both what the backfill fixes and what it must leave alone.

  1. 01Fixtures go in: small tables with the hard cases for the shape.
  2. 02The template runs on DuckDB on your machine, with no cluster and no warehouse compute.
  3. 03The result is compared with the expected output, row for row.
  4. 04Green can deploy. Red sends the agent back to the template.
What is a golden testGolden tests in the docs

What the Backfill test checks

  1. Case 01

    Start from a target with a gap in the middle of the range. The gap is filled.

  2. Case 02

    Partitions outside the range are unchanged, row for row.

  3. Case 03

    Run the same backfill twice. The result is identical, with no double counting.

  4. Case 04

    Stop the run after the second partition and restart it. It continues with the third and does not redo the first two.

Each template is also judged per platform: yellow when it passes on DuckDB, green when it also passes on the real platform. Nothing is released, so no template has a green mark yet.

Platforms

Written for the platform you run.

The same shape has a different correct form on each platform. This is what the Backfill template uses on each one, and the platform facts that decide it.

Databricks

In development

The Databricks template uses replaceWhere or INSERT OVERWRITE on Delta partitions, run as a job with parameters.

  • replaceWhere, or INSERT INTO ... REPLACE WHERE in SQL, overwrites only the rows that match a predicate on the partition column, and rejects rows that fall outside it.
  • Dynamic partition overwrite replaces exactly the partitions present in the incoming data. It is an alternative for partitioned tables.
  • A job with start and end date parameters runs the range, and its run history shows which partitions finished.
  • The job's principal needs MODIFY on the target in Unity Catalog, and the same on any downstream table the range also rebuilds.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template deletes and inserts per date range inside one transaction, with a warehouse size chosen for the run.

  • INSERT OVERWRITE replaces a whole table, not a range, so the template deletes the range and inserts the recomputed rows inside one transaction. Readers never see the gap.
  • A larger warehouse for the run shortens a big backfill. The template sets the size as a parameter, and its cost note says what to expect.
  • Clustering on the date column helps the delete and the later reads of one range prune data instead of scanning the table.
  • A stored procedure or a task graph loops over the dates and writes each finished date to a control table, so a restart resumes where it stopped.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template uses a notebook with parameters, called once per date from a pipeline.

  • A Data Pipeline ForEach activity calls the notebook for each date. Its sequential option and batch count control how many run at once.
  • The notebook replaces one partition of a lakehouse Delta table, with the same replaceWhere approach as on Databricks.
  • A control table records the finished dates, so a restarted pipeline skips them.
  • A warehouse target deletes and inserts the date range in one T-SQL transaction.
Emolysis for Microsoft Fabric

Whatever the platform, the template is tested on DuckDB first. The platform notes describe each product in general terms, so check the details against the version you run.

Mistakes

Mistakes it prevents.

The failures that most often hit a pipeline of this shape in production. Each one is a case in the template's golden test or a rule in its design.

  1. Overwriting partitions outside the range

    A predicate that is too wide, or an overwrite of the whole table, destroys data you meant to keep. The template replaces only the partitions in the range, and the golden test compares everything outside it.

  2. Double counting because rows were appended instead of replaced

    Appending a recomputed day on top of the old one doubles it. Each partition is replaced, so the second run gives the same totals as the first.

  3. One huge run that is costly to retry

    A year in a single statement fails at the end and starts again from the beginning. The template runs one partition at a time and records each one.

  4. Silent gaps in the middle of a range

    A failed partition that nobody notices leaves a hole in a dashboard. The log of finished partitions shows what is missing, and the test fills a deliberate gap.

  5. Forgetting downstream tables

    A corrected table whose aggregates are not rebuilt leaves two versions of the truth. Lineage shows which downstream tables depend on the range, so you can decide whether to backfill them too.

SQL sketch

The idea in SQL.

A driver for the range, and one partition at a time

Illustrative sketch in generic SQL. A runner executes the second block once per date returned by the first. Each platform's template uses that platform's replace mechanism.

Illustrative sketch · SQL
-- Driver: the dates in the range that are not yet logged as finished.
SELECT d.calendar_date AS partition_date
FROM ops.calendar AS d
WHERE d.calendar_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
  AND d.calendar_date NOT IN (
    SELECT partition_value FROM ops.backfill_log WHERE pipeline = 'daily_revenue'
  )
ORDER BY d.calendar_date;

-- Body, run once per partition_date (2026-03-14 shown). Replace, never append.
BEGIN;

DELETE FROM gold.daily_revenue WHERE revenue_date = DATE '2026-03-14';

INSERT INTO gold.daily_revenue (revenue_date, region, revenue)
SELECT CAST(o.ordered_at AS DATE), o.region, SUM(o.amount)
FROM silver.orders AS o
WHERE o.ordered_at >= DATE '2026-03-14' AND o.ordered_at < DATE '2026-03-15'
GROUP BY CAST(o.ordered_at AS DATE), o.region;

-- Record the partition as finished, so a failed run resumes after it.
INSERT INTO ops.backfill_log (pipeline, partition_value, finished_at)
VALUES ('daily_revenue', DATE '2026-03-14', CURRENT_TIMESTAMP);

COMMIT;

Related

Shapes that work alongside it.

Real pipelines are several shapes in a row. These are the ones that most often sit next to this one.

07

Repair

Late-arriving

When only some windows need correcting because facts arrived late.

Explore Late-arriving
09

Model and serve

Aggregation / gold

The tables a backfill most often rebuilds: gold metrics at a declared grain.

Explore Aggregation / gold
05

Load

Snapshot

For dated copies you may need to rebuild from the source.

Explore Snapshot

Glossary

Keep reading

FAQ

Questions about Backfill.

What is a data backfill?

A backfill re-runs a pipeline for a past period, to load history for a new table, apply a fix to old data or reprocess data a source delivered again. A safe backfill replaces exactly the partitions in its range, runs in order, can resume after a failure and gives the same result if run twice.

How do I backfill without double counting?

Replace each partition instead of appending to it. On Delta tables that means replaceWhere or a partition overwrite. On Snowflake it means deleting the date range and inserting the recomputed rows in one transaction. The template's golden test runs the same range twice and checks that the totals do not change.

Should a backfill run partitions in parallel?

Running several partitions at once finishes sooner and uses more compute at the same time, and it can compete with the production schedule for the same tables. The max_parallel slot controls it, and strictly in order is the safe starting point.

How is a backfill different from the Late-arriving shape?

A backfill redoes a range you choose, on purpose. Late-arriving works out which windows were affected by newly arrived facts and reprocesses only those. They use the same idea, replacing partitions, but they start from different questions.

In development · Not released

Test first, then deploy.

The Backfill template is written for Databricks, Snowflake and Microsoft Fabric, and its golden tests pass on DuckDB. Nothing is released yet. Early access is by invitation, for teams that want to help shape it.

Apply for early accessAll ten shapes