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.

Snapshot

Snapshot: full copies, kept on schedule.

Read the whole source on a schedule and either replace the table or keep each run as a dated snapshot, with a retention period. Readers see the old snapshot or the new one, never a partial load.

Template in development
Load
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

05 / 10

  1. Source (used)
  2. Bronze (used)
  3. Silver
  4. Gold
  5. Apps
Group
Load
Works across
Source to Bronze
Reads
The whole source, each run
Writes
A replaced table or dated copies
Slots
6 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What Snapshot does.

Copy a source in full on a schedule and keep dated copies for a set retention period.

What it does

At each run, Snapshot reads the whole source and either replaces the table's contents or adds the run as a dated snapshot. It then removes snapshots older than the retention period.

The change is atomic from a reader's point of view: a query sees the previous snapshot or the new one, never a half-written mix. A run repeated for the same date replaces that date's snapshot instead of adding a second one.

Use it when

  • Small and medium sources with no reliable change tracking.
  • Reference data such as country codes, product lists and price books.
  • Month-end positions and balances that must be kept as they were on that date.

Use another shape when

Slots

Inputs, outputs and slots.

What the Snapshot 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 full source table or extract
  • The run date, or a date column in the source
  • The retention period

Snapshot

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

Out

Outputs

  • A replaced table, or a new dated snapshot beside the earlier ones
  • Snapshots past the retention period, removed
  • A run record: rows copied, snapshots kept and removed

Template slots

6 slots · names and types may change before release

source_table
table
The source to copy in full.
target_table
table
Where the copy or the snapshots live.
mode
enum
Replace in full, or keep one dated snapshot per run.
snapshot_date_column
column
The column that holds each snapshot's date, in dated mode.
snapshot_date
expression
Where the date comes from: the run date or a column of the source.
retention
duration
How long dated snapshots are kept before they are removed.

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 takes snapshots on more than one date, repeats one of them and applies retention, because those are the three moments a snapshot pipeline goes wrong.

  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 Snapshot test checks

  1. Case 01

    Take two snapshots on two dates. Both are kept, each with the right date.

  2. Case 02

    Re-run the second date. Its snapshot is replaced, not duplicated.

  3. Case 03

    Apply a retention period. Only snapshots older than the period are removed, and the boundary day stays.

  4. Case 04

    Interrupt a run midway. Readers still see the previous complete snapshot.

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

Databricks

In development

The Databricks template uses overwrite or partition replace on Delta tables.

  • A Delta write is atomic, so a full overwrite replaces the table's contents in one commit. Readers of the earlier version are not disturbed.
  • A dated snapshot table partitioned or clustered by the snapshot date lets a re-run replace one date with replaceWhere.
  • Delta time travel is not a business history. Log retention, file retention and VACUUM decide how long old versions stay readable, and they exist for recovery. Business retention belongs in the snapshot table.
  • The job's principal needs MODIFY on the table in Unity Catalog to overwrite it.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template uses INSERT OVERWRITE or dated inserts.

  • INSERT OVERWRITE replaces a table's contents in one transaction. For a build-and-swap pattern, ALTER TABLE ... SWAP WITH exchanges two tables atomically.
  • Time Travel retention is a recovery feature with its own setting and a limit set by your edition. It is separate from business retention, which the template applies by date.
  • Storage grows with every dated snapshot, so the retention slot is required, not optional.
  • Unquoted identifiers fold to uppercase, so a snapshot date column is addressed the same way in the DDL and in later queries.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template uses overwrite or partitioned writes to lakehouse Delta tables.

  • A notebook overwrites the Delta table, or only the partition for the snapshot date.
  • Lakehouse table maintenance (OPTIMIZE and VACUUM) controls how long old files remain. Set it for recovery needs, not as a replacement for business retention.
  • In a warehouse, T-SQL deletes the date's rows and inserts the new ones in one transaction.
  • A Data Pipeline schedule runs the snapshot at the time the source is ready, such as after month-end close.
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. A half-written snapshot that readers can see

    A reader who queries mid-load sees part of the new copy and part of the old. The template replaces the data in one atomic step.

  2. Duplicate snapshots for one date after a re-run

    Appending again on the same date doubles every balance. The template replaces the date's snapshot, and its golden test repeats a date on purpose.

  3. Retention that deletes the wrong dates

    An off-by-one in the cutoff removes a snapshot you were required to keep, or keeps one past its limit. The test checks the boundary day.

  4. Treating the platform's time travel as a business history

    Time travel is for recovery and expires on its own schedule. A month-end position you must keep belongs in a dated snapshot.

  5. Storage that grows without limit

    Dated snapshots of a large table add up. The retention period is a slot with no default of forever.

SQL sketch

The idea in SQL.

Replace today's snapshot, then apply retention

Illustrative sketch in generic SQL. The run date is a parameter in practice. Each platform's template uses that platform's overwrite or swap mechanism.

Illustrative sketch · SQL
BEGIN;

-- 1. Replace the snapshot for the run date, so a re-run never duplicates it.
DELETE FROM silver.account_balance_snapshots
WHERE snapshot_date = DATE '2026-10-13';

INSERT INTO silver.account_balance_snapshots (snapshot_date, account_id, balance, currency)
SELECT DATE '2026-10-13', account_id, balance, currency
FROM source.account_balances;

-- 2. Retention: remove snapshots older than 90 days. The boundary day is kept.
DELETE FROM silver.account_balance_snapshots
WHERE snapshot_date < DATE '2026-10-13' - INTERVAL '90' DAY;

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.

04

Sync and history

SCD Type 2

For the history of each record rather than whole-table copies.

Explore SCD Type 2
02

Load

Incremental append

For large tables, when copying everything each run costs too much.

Explore Incremental append
06

Repair

Backfill

To rebuild past dates from the source when a snapshot must be redone.

Explore Backfill

Glossary

Keep reading

FAQ

Questions about Snapshot.

What is a snapshot table?

A snapshot is a full copy of a source as it was at one point in time. A snapshot pipeline reads the whole source on a schedule and either replaces the target or keeps each copy with its date, removing copies older than a retention period.

Snapshot or SCD Type 2?

A snapshot keeps whole-table copies at the moments you take them, and says nothing about what happened between them. SCD Type 2 keeps the exact validity range of every version of every record. Use a snapshot for small tables without change tracking and for positions you must keep as of a date. Use SCD Type 2 when a report needs the value that was true on any date.

How is a snapshot different from Delta time travel or Snowflake Time Travel?

Time travel lets you query an earlier version of a table for a limited period, mainly for recovery, and the platform expires it on its own schedule. A snapshot is data you chose to keep, with a date column you can query across dates, for as long as your retention rule says.

How long should snapshots be kept?

As long as the questions you must answer need them. Daily snapshots are often kept for a short period, with month-end snapshots kept much longer for reporting. The retention period is a slot in the template, so the choice is explicit and tested.

In development · Not released

Test first, then deploy.

The Snapshot 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