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.

Incremental append

Incremental append: only what is new.

Append the rows a source added since the last successful run, tracked by a watermark that moves forward only after the load commits. Overlapping windows and re-runs add no duplicates.

Template in development
Load
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

02 / 10

  1. Source (used)
  2. Bronze (used)
  3. Silver
  4. Gold
  5. Apps
Group
Load
Works across
Source to Bronze
Reads
An append-only source
Writes
New rows in the target
Slots
6 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What Incremental append does.

Append only the rows that arrived since the last run, tracked by a watermark.

What it does

Incremental append reads the rows a source added since the last successful run, using a watermark column such as an event timestamp or an increasing id, and appends them to the target. The watermark moves forward only after the load commits, so a failed run is simply tried again from the same point.

An optional lookback window re-reads a short stretch before the watermark, to catch rows that were committed late in the source. The template checks each row against what the target already holds, so the overlap does not produce duplicates.

Use it when

  • Append-only sources: event logs, clickstreams, sensor readings and ledger entries that are never updated.
  • Large tables where reading everything on every run would be slow or costly.
  • Sources with a column that only grows, such as an event timestamp, an auto-increment id or a row version.

Use another shape when

  • Rows can change or be deleted after they are written:

    03CDC merge

  • The source has no usable watermark and is small enough to copy whole:

    05Snapshot

  • A past date range has to be redone on purpose:

    06Backfill

Slots

Inputs, outputs and slots.

What the Incremental append 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

  • A source table or file location with a watermark column
  • The stored watermark from the last successful run
  • An optional lookback window

Incremental append

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

Out

Outputs

  • New rows appended to the target table
  • An updated watermark, moved only after the load commits
  • A run record: rows read, rows appended, old and new watermark

Template slots

6 slots · names and types may change before release

source_table
table
The append-only source.
target_table
table
Where the new rows are appended.
watermark_column
column
The column that only grows: an event time or an increasing id.
watermark_type
enum
Timestamp or integer. The type decides how boundaries and time zones are compared.
lookback
duration
How far before the watermark to re-read, to catch rows committed late in the source. Zero is allowed.
dedupe_key
columns
The columns that identify a row, so overlap never adds one twice.

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 runs the pipeline more than once on purpose, because every failure of this shape appears at the boundary between two runs.

  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 Incremental append test checks

  1. Case 01

    Run the pipeline twice with overlapping input. The second run adds only the new rows, with no duplicates and no gaps.

  2. Case 02

    Include rows exactly at the watermark. Each one lands once.

  3. Case 03

    Fail the load after the rows were read and before the commit. The watermark does not move, and the next run loads the same rows.

  4. Case 04

    Feed timestamps with different time zone offsets. They are compared in one zone.

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 Incremental append template uses on each one, and the platform facts that decide it.

Databricks

In development

The Databricks template uses Auto Loader or Structured Streaming with an available-now trigger, or a watermark filter on a Delta table, scheduled as a job.

  • A streaming read with an available-now trigger processes everything new and then stops, so the job runs on a schedule and still keeps its progress in a checkpoint.
  • Delta's transaction log makes the append atomic: the new rows appear all at once or not at all.
  • A table-to-table watermark filter is the easier form to reason about and test, and it is the form a DuckDB golden test exercises directly.
  • A streaming watermark is a different thing from this shape's watermark column. It sets how late an event may be and still be counted in a stateful operation, such as a windowed aggregate.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template uses streams and tasks, or a dynamic table.

  • An append-only stream records only inserts on a source table, and its offset moves when a transaction that reads it commits. That offset is the watermark.
  • A task can run on a schedule or only when the stream has data, so the warehouse does not start for nothing.
  • A dynamic table with a target lag refreshes on its own and is the lightest option when the logic is a plain SELECT.
  • A stream that nobody reads for longer than the table's retention allows goes stale and has to be recreated, so the template schedules the task to keep reading.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template is a notebook that stores its watermark; its schedule is kept in Fabric.

  • A notebook reads the stored watermark, appends to a lakehouse Delta table and then writes the new watermark. Both writes belong to the same run.
  • A Data Pipeline can look up the previous watermark, pass it to the notebook as a parameter and run the notebook on a schedule.
  • Dataflow Gen2 suits smaller, low-code loads. The notebook form is the one the template's golden tests are designed around.
  • The template targets lakehouse tables. A warehouse target would use T-SQL INSERT with a SELECT and hold the watermark in a small control table.
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. Off-by-one boundaries

    A greater-than where greater-than-or-equal is needed drops the rows at the edge, and the opposite repeats them. The template fixes one comparison, and its golden test includes rows exactly at the boundary.

  2. A watermark that moves after a failed load

    Every row between the old and the new watermark is skipped for good. The template writes the watermark in the same transaction as the load, or after it commits, never before.

  3. Duplicates from overlapping windows

    A lookback window is the safe way to catch late commits and the usual way to duplicate rows. The template deduplicates against the target.

  4. Time zones compared as if they were the same

    A watermark in local time against a source in UTC shifts the boundary by hours. The template compares one type in one zone.

  5. Using it on a table whose rows change

    An incremental append never sees an updated row. If rows can change, the table needs CDC merge instead.

SQL sketch

The idea in SQL.

Append past the watermark, then move it

Illustrative sketch in generic SQL. The platform templates use each platform's own mechanisms, such as streams, checkpoints or a control table.

Illustrative sketch · SQL
BEGIN;

-- 1. Append rows newer than the watermark, minus a 2-hour lookback for rows committed late.
--    The NOT EXISTS check keeps the overlap from adding anything twice.
INSERT INTO bronze.events (event_id, user_id, event_type, event_ts)
SELECT s.event_id, s.user_id, s.event_type, s.event_ts
FROM source.events AS s
WHERE s.event_ts > (
    SELECT w.watermark - INTERVAL '2' HOUR
    FROM ops.watermarks AS w
    WHERE w.pipeline = 'events'
  )
  AND NOT EXISTS (
    SELECT 1 FROM bronze.events AS t WHERE t.event_id = s.event_id
  );

-- 2. Move the watermark in the same transaction as the load, never before it.
UPDATE ops.watermarks
SET watermark = (SELECT MAX(event_ts) FROM bronze.events)
WHERE pipeline = 'events';

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.

01

Load

Ingest (batch)

For the first landing of a source, when each batch is complete on its own.

Explore Ingest (batch)
03

Sync and history

CDC merge

When rows in the source are updated or deleted after they are written.

Explore CDC merge
07

Repair

Late-arriving

When event time and arrival time differ and past totals must be corrected.

Explore Late-arriving

Glossary

Keep reading

FAQ

Questions about Incremental append.

What is an incremental load with a watermark?

An incremental load reads only the data that is new since the last run. A watermark is the stored high-water mark of what was already read, usually the highest event timestamp or id loaded so far. The next run reads rows beyond it, and the watermark moves forward only after the load commits.

What is the difference between Incremental append and CDC merge?

Incremental append only adds rows. It suits event logs and ledgers whose rows are never updated. CDC merge applies inserts, updates and deletes from a change feed, so it suits tables whose rows change. If a source row can change after it is written, append alone leaves the old version in the table.

How does a lookback window avoid duplicates?

The window re-reads a short stretch before the watermark to catch rows that committed late in the source. The template checks each candidate row against the target by a key, so a row that is already there is not added again. The golden test runs overlapping input twice to prove it.

Why must the watermark move only after the commit?

If the watermark moves first and the load then fails, the rows between the old and the new watermark are never read again. Moving it in the same transaction as the load, or after the commit, means a failed run leaves the watermark where it was and the next run repeats the work.

In development · Not released

Test first, then deploy.

The Incremental append 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