# Materialization

> How do I materialize DuckLake tables in Windmill pipelines? Managed writes, SCD2 history, time-travel, schema capture, data tests and manual mode.

A DuckDB script can declare that it **produces** a managed [DuckLake](../11_persistent_storage/ducklake.mdx) table with `// materialize`. This is the canonical, most powerful way to build a pipeline step (see [the canonical stack](./index.mdx#the-canonical-stack-duckdb--ducklake--materialize)). Instead of writing the table yourself, you write the query for one *slice* and Windmill owns the write: it is idempotent, captured as a DuckLake snapshot, and tracked. A **slice** is one [partition](./partitioning.mdx) when the script is also `// partitioned`, or the **whole table** when it isn't — `materialize` works either way (`// partitioned` is optional and independent; see [Strategy and unit](#strategy-and-unit) below).

```sql
-- pipeline
-- materialize ducklake://main/orders_daily key=order_id
-- partitioned daily

ATTACH 'ducklake://main' AS dl;

SELECT order_id, amount, created_at
FROM dl.orders
WHERE created_at::date = '{partition}'
```

The script is *setup statements (ATTACH, SET, CREATE TEMP …) followed by one trailing `SELECT`*. Windmill wraps that SELECT in the write — creating the table on first run, partitioning it (when `// partitioned`), and reconciling the slice — then records the produced snapshot id and row count. Re-running the same slice is safe by construction (that is also how a backfill or a failed-run retry works). For a partitioned target the slice is tracked in a managed `_wm_partition` column appended to the table (it is also the Hive partition key of the parquet files on the underlying storage), so your SELECT never has to project the partition itself.

The trailing `SELECT` may reference SQL arguments declared with `-- $name (type)` (for example `-- $cutoff (date)` then `WHERE created_at < $cutoff`); they bind at run time like any other DuckDB script argument, including `s3object` arguments translated to `s3://` URIs.

## Filtering the source to the active partition

A `// partitioned` materialize runs once per [partition](./partitioning.mdx) and its `SELECT` should return only that slice. The [`{partition}` token](./partitioning.mdx#the-partition-token) substitutes to the partition's *identity string* as a quoted SQL literal - the same value stored in `_wm_partition`: `'2026-07-05'` (daily), `'2026-07-05T23'` (hourly), `'2026-W27'` (weekly) or `'2026-07'` (monthly).

Only the daily identity is a valid DuckDB `DATE`/`TIMESTAMP`, so a naive `WHERE ts = TIMESTAMP {partition}` parses for daily but raises a `Conversion Error` on the other grains. To filter a timestamp column regardless of grain, use the `wm_partition(<ts_col>)` macro, which Windmill injects automatically as the first setup statement of every time-partitioned materialize:

```sql
-- pipeline
-- materialize ducklake://main/orders_hourly
-- partitioned hourly

ATTACH 'ducklake://main' AS dl;
SELECT order_id, amount, created_at
FROM dl.orders
WHERE wm_partition(created_at) = {partition}
```

`wm_partition(ts)` renders `ts` with the same format the resolver used to stamp `{partition}`, so the equality both parses and selects the whole bucket (every row in the slice, not just the boundary instant) on any time grain. It honours a custom `format=` [override](./partitioning.mdx#options-by-kind) and is timezone-agnostic (it formats `ts` as given): when a non-UTC `tz=` is set, express `ts` in that zone yourself.

`dynamic` partitions get **no** macro - their identity is a caller-supplied key, not a timestamp - so filter on your key column directly with `WHERE tenant_id = {partition}`.

## Grammar

```
// materialize ducklake://<name>/<table> [append] [key=<col>]                          # managed (default)
// materialize ducklake://<name>/<table> key=<col> history [track=<c1,…>] [deletes=close]  # managed SCD2 history
// materialize ducklake://<name>/<table> … on_schema_change=warn|ignore|fail|sync      # any managed form: how a schema change is handled
// materialize manual ducklake://<name>/<table>                                        # track-only escape hatch
```

- **Managed (default)** — Windmill generates the write. DuckDB-only; validated at deploy (a script that is not a single trailing SELECT is rejected with a clear error). The **strategy** decides how each slice is reconciled:
  - *neither option* → **replace**: the slice becomes exactly what the SELECT returned. The whole table is rebuilt with `CREATE OR REPLACE` (so changing the `SELECT`'s columns between runs just works); a partition is reconciled with `DELETE` + `INSERT`.
  - `key=<col>` → **merge**: upsert the slice on `<col>` (rows absent from the SELECT are left in place).
  - `append` → **insert-only**: for immutable event logs. Re-running duplicates rows, so use only for append-only sources. (`append` wins over `key=` if both are given.)
  - `key=<col> history` → **SCD2 history**: keep every version of every row instead of overwriting on change. See [SCD2 history](#scd2-history-slowly-changing-dimensions).
  - `on_schema_change=<warn|ignore|fail|sync>` combines with any strategy and controls what a run does when the SELECT's columns diverge from the target's stored schema, from warning downstream consumers (`warn`, the default) to aborting the run (`fail`) or auto-syncing the table (`sync`). See [on_schema_change](#on_schema_change).
- **`manual`** — the escape hatch: your script writes its own DDL and Windmill only records that the slice was materialized (no snapshot capture, no idempotency guarantee). Rare; explicit. See [Manual materialization](#manual-materialization-track-only).

`// materialize` is for DuckDB SQL. From **Python or TypeScript**, use the `wmill.ducklake` helpers (`upsert_partition`, `append_partition`, `read`) which give the same managed, idempotent write from arbitrary code.

## Manual materialization (track-only)

`// materialize manual` is the escape hatch for a write the managed wrapper cannot express: multi-statement DDL, a `MERGE` you want to own, a reshape the strategies don't cover. The single-trailing-`SELECT` rule does not apply - the script is ordinary DuckDB that creates and writes the target itself - and Windmill only records that the slice was materialized.

```sql
-- pipeline
-- on ducklake://main/stg_payments
-- partitioned daily
-- materialize manual ducklake://main/payment_totals

ATTACH 'ducklake://main' AS dl;

CREATE TABLE IF NOT EXISTS dl.payment_totals (
  day DATE, method TEXT, total DECIMAL(18, 2)
);

-- You own idempotency: delete-then-insert makes re-runs and backfills safe.
DELETE FROM dl.payment_totals WHERE day = $partition::DATE;

INSERT INTO dl.payment_totals
SELECT created_at::DATE, method, sum(amount)
FROM dl.stg_payments
WHERE created_at::DATE = $partition::DATE
GROUP BY 1, 2;
```

Note `$partition`, not `{partition}`: the [`{partition}` token](./partitioning.mdx#the-partition-token) is substituted only in managed materialize scripts (and the injected `wm_partition` macro is managed-only too), but the `partition` argument is auto-declared for every `// partitioned` DuckDB script, so a manual script binds `$partition` like any other argument.

What the track-only edge records, and what it gives up:

- Recorded: the asset and partition are marked materialized (or failed) with the producing job id and time, so the [partition-status grid](#partition-status-and-backfill) fills in, [backfill](#partition-status-and-backfill) enumerates and re-runs the script per partition, and the write fires downstream subscribers through the [cascade](./index.mdx#triggering-a-pipeline) like any other output.
- Not recorded: no snapshot id and no row count - the run result is your query's own output rather than the managed summary, and consumers' [Upstream snapshots](#upstream-snapshots) panels skip the asset. Idempotency is whatever your own SQL does (the delete-then-insert above).
- Inert: [data tests](#data-tests) are rejected on a manual target, no [schema capture](#schema-capture) happens (so consumers get no [schema-contract](#schema-contracts) warnings against it), and [`on_schema_change`](#on_schema_change) has nothing to act on - the deploy logs a warning saying so.

If replace, `key=` merge or `append` can express the write, prefer the managed form: `manual` trades the whole safety net for full control of the SQL.

## Strategy and unit

`materialize`, `partitioned`, `append` and `key` sit on **two independent axes**, and `materialize` runs the chosen strategy over the chosen unit:

- **Strategy — *how* a slice is reconciled.** `append` / `key=` are options on the `// materialize` line (mutually exclusive); they have no meaning without `// materialize`.
- **Unit — *what* a slice is.** `// partitioned` is a separate, optional annotation (it also drives the [cascade](./index.mdx#triggering-a-pipeline) and scheduling). With it, a slice is one partition; without it, a slice is the whole table.

The two combine:

| | replace (default) | `key=<col>` (merge) | `append` |
| --- | --- | --- | --- |
| **`// partitioned`** | replace the partition | upsert within the partition | append to the partition |
| **whole table** (no `// partitioned`) | replace the whole table | upsert the whole table | append to the whole table |

A whole-table materialization is just `// materialize` with no `// partitioned` — e.g. a dimension table rebuilt each run:

```sql
-- pipeline
-- materialize ducklake://main/customer_dim key=customer_id

ATTACH 'ducklake://main' AS dl;
SELECT customer_id, name, tier FROM dl.customers
```

The `history` strategy is the exception: it is whole-table only, and combining it with `// partitioned` is rejected at deploy.

:::info `key=` merge is a cross-run upsert, not a within-run de-duplicator

`key=<col>` reconciles this run's SELECT against the rows **already in the target**: existing keys are updated, new keys inserted, and keys absent from the SELECT left untouched. That reconciliation is across runs, not within one - it does not collapse duplicate keys *inside* a single SELECT. If the SELECT returns the same key twice in one run, both rows are inserted as-is (the merge does not pick a winner), so the SELECT must already be unique on `<col>`. De-duplicate in the query when the source can emit a key more than once, for example with `QUALIFY row_number() OVER (PARTITION BY <col> ORDER BY updated_at DESC) = 1` or a `GROUP BY`.

:::

## SCD2 history (slowly changing dimensions)

Adding `history` to a keyed materialization turns the SCD1 merge (overwrite on change) into a type-2 slowly changing dimension: the runtime keeps every version of every row. When a tracked column changes, the prior version is closed and a new one is opened, so an entity's full version history stays queryable as rows. The leading keyword `scd2` is a recognized alias (`// materialize scd2 ducklake://… key=id`).

```sql
-- pipeline
-- on ducklake://analytics/stg_customers
-- materialize ducklake://analytics/dim_customer key=id history track=name,tier
-- data_test not_null id

ATTACH 'ducklake://analytics' AS dl;

SELECT id, name, tier          -- the current snapshot, one row per key
FROM dl.stg_customers;
```

The `SELECT` returns the current desired state, one row per key - exactly like `merge`. You never write validity columns; the runtime appends and manages three:

| Column | Type | Meaning |
| --- | --- | --- |
| `valid_from` | `TIMESTAMP` | when this version became effective |
| `valid_to` | `TIMESTAMP` | when it was superseded; `NULL` = still open |
| `is_current` | `BOOLEAN` | `true` for the live version of each key |

Options on the `// materialize` line:

- `key=<col>` (required) - the natural/business key that identifies an entity across versions.
- `track=<c1,c2,…>` (optional) - the columns whose change opens a new version. Default: all non-key columns. Bare comma list with no spaces (`track=name,tier`).
- `deletes=close` (optional) - a key that disappears from the snapshot has its current version closed. Default is soft delete: absent keys stay current. A closed key that later reappears opens a fresh version, leaving a validity gap.

Each run diffs the snapshot against the live rows: a changed tracked column closes the prior version (`valid_to` set, `is_current = false`) and opens a new one; a new key opens as current; a change to an untracked column is ignored; an unchanged snapshot writes 0 rows and advances no snapshot (idempotent). Within a run, the closed version's `valid_to` equals the new version's `valid_from`, so versions are contiguous and gap-free.

Each run also maintains a companion view `<table>_current` containing only the `is_current` rows, so the common "give me the latest version" case needs no filter:

```sql
-- pipeline
-- on ducklake://analytics/dim_customer_current
ATTACH 'ducklake://analytics' AS dl;
SELECT * FROM dl.dim_customer_current;
```

Read `<table>_current` (not the base table) whenever you want exactly one row per key - the live version - without an `is_current` filter or an as-of join. Declare it as an input with `// on ducklake://…/<table>_current`, as above, so the read is a first-class edge of the pipeline graph.

:::info Lineage to the base dimension

The graph traces a `<table>_current` read to the companion view, but does not yet automatically draw the edge back through it to the base `history` dimension. Until that link is inferred, add an explicit `// on ducklake://…/<table>` (the base dimension) alongside the `_current` read if you want the lineage edge to the base table shown. This is annotation-only and forward-compatible: the extra `// on` stays correct once the view-to-base link is inferred, so you can add it now and remove it later, or leave it in place.

:::

The payoff over [time-travel](#versioning-and-time-travel) (which is keyed by commit, not business-effective time) is the effective-dated as-of join - for each fact row, the dimension version that was current at that moment:

```sql
-- pipeline
-- on ducklake://analytics/dim_customer
-- on ducklake://analytics/stg_orders
-- materialize ducklake://analytics/fct_orders
ATTACH 'ducklake://analytics' AS dl;
SELECT o.order_id, o.amount, o.ordered_at,
       d.tier AS tier_at_order_time
FROM dl.stg_orders o
ASOF JOIN dl.dim_customer d
  ON o.customer_id = d.id AND o.ordered_at >= d.valid_from;
```

Constraints:

- Whole-table only: `// partitioned` + `history` is rejected at deploy.
- `valid_from`, `valid_to` and `is_current` are reserved column names (a SELECT projecting one fails at run time), and the `<table>_current` suffix is reserved for the companion view.
- The schema is frozen at first run: an append-only history cannot reshape closed versions, so changing the SELECT's projected columns later fails and needs a manual rebuild.

Built-in [data tests](#data-tests) on an SCD2 target are scoped to current rows (`is_current`), so `unique(<key>)` keeps passing as closed versions accumulate in the history.

## What a run returns

A managed run returns a small summary — the asset it produced, the row count of the slice, and the DuckLake `snapshot_id` — and the run's **Result** panel renders a live, read-only preview of the materialized table beneath it. For a `// partitioned` target the preview adds a **This partition / Whole table** toggle, and the summary's row count is the slice (partition) the run wrote. The same values are recorded as the asset's [materialization state](#partition-status-and-backfill).

To run a partitioned script manually (e.g. from the editor's **Test** panel), fill the **partition** field — Windmill surfaces it automatically for `// partitioned` DuckDB scripts, as a date picker for date kinds. Selecting the producer in the pipeline graph opens a richer [partition picker](./partitioning.mdx#the-partition-picker) with materialization and upstream-data hints. In a pipeline the [cascade](./index.mdx#triggering-a-pipeline) injects it for you.

## Versioning and time-travel

Every managed write is a DuckLake commit, so versioning is free — you never annotate for it. Each run records the snapshot it produced, which means you can read a table as of a past snapshot (`FROM dl.orders_daily AT (VERSION => 42)`), roll back, and reproduce a past run. This is what `// materialize` gives you over hand-writing the same SQL.

Snapshots are kept forever by default. If [scheduled maintenance](../11_persistent_storage/ducklake.mdx#scheduled-maintenance) is enabled on the lake, snapshots older than its retention window are expired and can no longer be read with `AT (VERSION => n)`, so size the retention window to the history you need to query.

## Upstream snapshots

Snapshots are also recorded on the *read* side of the [cascade](./index.mdx#triggering-a-pipeline). When a run is dispatched because an upstream asset was written, Windmill captures the current DuckLake snapshot id of each of the run's direct upstream assets at dispatch time and stores it on the job. The run detail page then shows a read-only **Upstream snapshots** panel: one row per upstream asset (`<asset> @ <snapshot_id>`, plus the partition when the upstream is [partitioned](./partitioning.mdx)), with a copy button per row that puts the matching time-travel clause on your clipboard.

![Upstream snapshots panel on a cascade-dispatched run](./upstream_snapshots_panel.png 'Upstream snapshots panel on a cascade-dispatched run')

This answers "what data did this run actually see?" long after the upstream tables have moved on. When a nightly report comes out wrong, open its run, read `ducklake://main/raw_orders @ 34 · partition 2026-07-04`, and query the input exactly as the run saw it by pasting the copied clause into any DuckDB script:

```sql
SELECT * FROM dl.raw_orders AT (VERSION => 34)
```

Notes on semantics:

- It is a debugging record, not a pin: the run still reads the latest data, and recording changes nothing about execution.
- Only direct upstream assets with a materialized snapshot appear. Non-materialized upstreams (for example plain `s3://` objects) are skipped, and manual or non-cascade runs show no panel.
- The raw record is stored in the job's `trigger` argument under `upstream_snapshots`, so it is also visible in the run's inputs table and through the API, wherever job arguments surface.
- Recorded snapshots are subject to the lake's retention: if [scheduled maintenance](../11_persistent_storage/ducklake.mdx#scheduled-maintenance) expires a snapshot, its time-travel clause can no longer be queried.

## Partition status and backfill

Selecting a materialized `ducklake://` asset in the pipeline graph shows its **partition-status grid** — which partitions are materialized, their snapshot id, row count, and time — along with the producer's latest run and its data-test results.

![Partition-status grid for a materialized asset](./partition_status_grid.png 'Partition-status grid for a materialized asset')

From there, **Backfill** re-runs the materialization for a range of partitions; range backfill is an [Enterprise Edition](/pricing) feature (single-partition runs with an explicit `partition` argument remain available in all editions).

The Backfill button opens a range picker that previews the status of every partition in `[from, to]` - missing, failed or materialized - so the missing/failed set is the backfill worklist. A toggle (on by default) restricts the run to missing and failed partitions; turn it off to re-run the whole range. Launching runs the producing script once per partition with an explicit `partition` argument, sequentially (concurrent materializations of one DuckLake table would contend on the catalog commit); each run is idempotent, and a failed partition does not stop the rest. Progress streams in the dialog and, when it is closed, in the asset drawer header, and the grid refreshes as each partition lands.

Range backfill enumerates time [partition kinds](./partitioning.mdx#partition-kinds) only (`daily`, `hourly`, `weekly`, `monthly`), honours the spec's `start` anchor (partitions before it are not enumerated), and caps a single backfill at 1000 partitions. `dynamic` partitions cannot be enumerated over a range - backfill them one at a time with an explicit `partition` argument.

![Backfill range picker previewing missing, failed and materialized partitions](./backfill_range_preview.png 'Backfill range picker with partition-status preview')

The same backfill works headlessly from the CLI by passing an explicit partition per run: `wmill pipeline run <folder> --partition 2026-06-30` (see [local development](./index.mdx#local-development)).

## Schema capture

After a managed materialize run, Windmill captures the table's output schema (column names and types) and stores it as **versioned asset metadata**. Selecting a materialized `ducklake://` asset shows a **Schema** tab listing each captured version. A new version is recorded only when the column set actually changes (a column added, dropped, retyped, or reordered), so the tab is a compact schema-evolution history rather than one row per run.

The captured schema also doubles as a contract for downstream consumers - see [Schema contracts](#schema-contracts).

## Schema contracts

The [captured schema](#schema-capture) is read back as a contract: when a consumer script is saved or deployed, Windmill checks every `ducklake://` reference it makes against the latest captured schema of that asset, and surfaces mismatches as **warnings - never errors**. Renaming a column upstream no longer breaks downstream consumers silently at run time; but because a deliberate upstream reshape should not fail every consumer save either, the consumer-side contract never blocks a deploy - it warns only. How a *producer* reconciles a change to its own schema (from warning downstream to a strict abort or an automatic `ALTER`) is a separate, producer-side control - see [on_schema_change](#on_schema_change).

The same check shows up in three places:

- **Save-time warnings** - right after a successful deploy, mismatches are listed in a warning toast. This is the authoritative check, run server-side against the deployed content.
- **Editor squiggles** - the check also runs live on the open buffer, underlining the offending column read, `// column` line or `// data_test` line with a warning squiggle as you type.
- **Schema-aware autocomplete** - on annotation lines, typing `.` after a `ducklake://…` reference completes column names (with their captured types), so a broken reference never gets typed in the first place.

{/* TODO screenshot: schema_contract_squiggle.png - consumer script in the editor reading a column that was renamed upstream, showing the warning squiggle and its hover message citing the captured schema version and the surviving columns */}

{/* TODO screenshot: schema_contract_autocomplete.png - Monaco completion popup after typing `.` following a ducklake:// reference on a `-- column` annotation line, listing captured column names with their types */}

What is checked (for `ducklake://` assets only, the substrate with [schema capture](#schema-capture)):

- Columns your script reads from or writes to the asset that are missing from its captured schema.
- `// column` [lineage annotations](#column-level-lineage) whose source column no longer exists.
- `// data_test relationships` references: a referenced column that is missing, and a type difference between the two join columns when both sides are captured (reported as a difference, not a failure - the runtime probe coerces types, so the test may still pass).

Comparison follows DuckDB semantics: column names compare case-insensitively, `{partition}` tokens are stripped before lookup, and the managed `_wm_partition` column is always accepted. A `<table>_current` reference is checked against its [SCD2](#scd2-history-slowly-changing-dimensions) base table's capture (the view has the same columns by construction). An asset with no capture at all - never materialized, `// materialize manual`, or a non-DuckLake target - produces no warnings. Each warning cites the schema version and capture time it was checked against, so you can tell when a warning is due to a stale capture rather than an actual mismatch.

### on_schema_change

`on_schema_change` is a producer-side option on the `// materialize` line that controls what a managed run does when the trailing SELECT's column set diverges from the target table's stored schema. It mirrors [dbt's option](https://docs.getdbt.com/docs/build/incremental-models#what-if-the-columns-of-my-incremental-model-change) of the same name:

| Value | Behavior |
| --- | --- |
| `warn` (default) | The write proceeds and the new schema is captured; downstream consumers get a [schema-contract](#schema-contracts) warning on their next save. |
| `ignore` | Same as `warn` but no downstream warning - for a table whose schema is deliberately unstable (a raw landing table tracking an external payload). |
| `fail` | The run aborts before writing when the SELECT's column set diverges from the stored schema. |
| `sync` | Windmill `ALTER`s the target table to match the SELECT (adding and dropping columns) and writes with `INSERT BY NAME`, so column order no longer has to match. |

`warn` and `ignore` only govern the downstream [contract warning](#schema-contracts), so they apply to every strategy. `fail` and `sync` are write-time guardrails and apply **only to the persist-and-mutate strategies**: partitioned replace, `key=` merge and `append`. Those create the table once and then write into that fixed schema **by column position**, so a drifted SELECT silently lands values in the wrong column. Whole-table replace (a non-partitioned default materialize) rebuilds the table from scratch each run, and an [SCD2 `history`](#scd2-history-slowly-changing-dimensions) target has no positional write, so on both there is nothing to guard and `fail`/`sync` fall back to `warn`.

Drift is detected on the **set of column names**, so `fail` catches an added, dropped or renamed column but not a pure reordering of same-named columns (`SELECT b, a` into an `(a, b)` table keeps the same set, so `fail` does not fire and the positional write swaps the values). Reorder-safety is exactly what `sync` adds: `INSERT BY NAME` maps by name, so a SELECT whose column order is not pinned to the table's should use `sync`, not `fail`.

Unknown values keep the default (`warn`). On `// materialize manual` the option is inert since nothing is captured; the deploy logs a warning saying so.

Declare `ignore` on a raw landing table whose columns track an external payload:

```sql
-- pipeline
-- materialize ducklake://main/raw_events append on_schema_change=ignore

ATTACH 'ducklake://main' AS dl;
SELECT * FROM read_json('s3://landing/events/*.json')
```

Consumers of that asset then get a single informational note instead of per-column warnings. Use `fail` instead when a schema change should stop the pipeline for review, and `sync` when the target should follow the source's columns automatically - an evolving ingestion where a new source column should just appear in the table without a manual `ALTER` (compare the [schema-drift recovery options](./ingestion.mdx#schema-drift-on-load) for the merge/append strategies).

## Column-level lineage

On top of asset-to-asset lineage, Windmill tracks **column-to-column** lineage for DuckLake pipelines: which source columns each output column is derived from. For DuckDB scripts this is **inferred automatically from the SQL** — Windmill walks the query's projection and maps every output column to the source columns its expression reads, both passthroughs and computed columns (`amount + tax AS order_total` records both `amount` and `tax` as sources). No annotation is needed in the common case.

The deployed pipeline graph shows a **columns ×N** badge on the write edge into a materialized asset; hovering it lists every mapping, and selecting the asset opens a column-to-column diagram in the details pane.

![Column-lineage badge on a pipeline write edge](./pipeline_graph_columns.png 'A columns badge on a write edge')

![Column-to-column lineage diagram in the asset details pane](./column_lineage_view.png 'Column-to-column lineage diagram')

When inference cannot reach a mapping — a polyglot transform (Python/TS/Bash has no SQL AST), dynamic SQL (`${sql.raw(...)}`), or a mis-inferred edge — declare it explicitly with `// column`:

```
// column <out_col> <- <asset-uri>.<col>[, <asset-uri>.<col> …]
```

Each line maps one output column to the source columns it derives from. Annotated and inferred lineage merge per output column, with the annotation winning. Column lineage is pure metadata — it drives the graph view and never runs a probe. The example below shows the annotation form on a plain SELECT for clarity; in practice you only annotate what inference can't derive (this particular mapping would be inferred automatically).

```sql
-- pipeline
-- materialize ducklake://warehouse/orders_enriched
-- column order_total <- ducklake://warehouse/orders.amount, ducklake://warehouse/orders.tax

ATTACH 'ducklake://warehouse' AS dl;
SELECT order_id, amount + tax AS order_total FROM dl.orders
```

:::info Inference is server-side

SQL-AST inference runs when a pipeline is deployed, so column lineage shows on **deployed** members. An unsaved draft shows `// column` annotations only.

:::

## Data tests

A materialized asset can declare **data tests** that run against the freshly-materialized slice and **fail the pipeline run** on violation — propagating through the cascade like any other step failure. They are the pipeline equivalent of dbt/Dagster data tests. Whether the data written by a failing run stays visible depends on the edition - see [Failing tests: publish or roll back](#failing-tests-publish-or-roll-back-ce-vs-ee).

```
// data_test unique <col>
// data_test not_null <col>
// data_test accepted_values <col> = a,b,c
// data_test relationships <col> -> <asset-uri>.<col>
// data_test <script_path>
```

- **`unique`** / **`not_null`** — column constraints.
- **`accepted_values <col> = a,b,c`** — the column may only contain the listed values.
- **`relationships <col> -> <asset-uri>.<col>`** — referential integrity: every value must exist in the referenced asset's column.
- **`<script_path>`** — a custom DuckDB test script (the escape hatch), e.g. `// data_test f/tests/orders_amount_sane`.

`// data_test` lines accumulate (a script can declare several). A malformed line (one whose right-hand side doesn't parse) still drops fail-safe rather than registering a broken check, but the deploy now surfaces a warning naming it, so a typo can't silently disable a data-quality assertion. For a `// partitioned` target the built-in checks are scoped to the slice just written, and for an [SCD2 `history`](#scd2-history-slowly-changing-dimensions) target to the current rows (`is_current`). Data tests require managed `// materialize` (they are rejected on `// materialize manual` or a non-DuckLake target). The producer→asset edge shows a test-count badge, and the run result lists each test with a pass/fail outcome; failed tests also include a [sample of their violating rows](#violating-row-samples).

```sql
-- pipeline
-- materialize ducklake://main/orders_daily key=order_id
-- partitioned daily
-- data_test not_null order_id
-- data_test unique order_id
-- data_test accepted_values status = paid,pending,refunded

ATTACH 'ducklake://main' AS dl;
SELECT order_id, status, amount FROM dl.orders WHERE created_at::date = '{partition}'
```

### Custom data tests

When the four built-in checks are not enough, point `// data_test` at a script path instead of a keyword:

```
// data_test <script_path>
```

The referenced script is a plain DuckDB script whose body is a **single `SELECT` that returns the violating rows** - one row per violation, in whatever shape you choose. The test passes when the SELECT returns no rows and fails otherwise, exactly like a built-in check: a failure fails the run and stops the [cascade](./index.mdx#triggering-a-pipeline), and the returned rows become the test's [violating-row sample](#violating-row-samples).

To scaffold one, the pipeline editor's "+" script creator has a **Data test** output kind (DuckDB only) that generates a correct starter body - a single `SELECT * FROM _wm_target.<table> WHERE <condition>` plus the `-- data_test <this-script-path>` line you paste into the producing script.

Authoring contract:

- **Read the freshly-materialized target through the internal `_wm_target` schema.** Windmill attaches the slice this run just wrote as `_wm_target`, so reference the target as `_wm_target.<table>` (the table name is the last segment of the `ducklake://` URI). Referencing the bare table name instead is the most common mistake, and is rejected at deploy. Reading the target this way sees this run's slice, not stale committed data - on Enterprise Edition it is the pre-commit write being audited (see [write-audit-publish](#failing-tests-publish-or-roll-back-ce-vs-ee)). You do not `ATTACH` the target yourself.
- **A single `SELECT` returning the offending rows** - one statement, no setup statements, writes or DDL. The body is embedded as a subquery, so it must be one `SELECT` (a `WITH … SELECT` CTE or DuckDB's FROM-first form both count); a multi-statement body (e.g. `SET …; SELECT …`) is rejected. Cross-table joins are still fine as long as the other source is referenceable inline (a file path, or a catalog already attached in the target's connection).
- **Zero rows = pass, any rows = fail.** The count of returned rows is the violation count.
- Scope matches the built-ins: for a `// partitioned` target `_wm_target.<table>` is the partition just written, and for an [SCD2 `history`](#scd2-history-slowly-changing-dimensions) target it is the current rows.

Each of these rules is enforced at deploy: a malformed custom test fails codegen with an error that names the exact violation (empty body, multiple statements, non-`SELECT`, or a body that never references `_wm_target`) and appends a copyable one-line example, so the fix is discoverable without leaving the editor.

Example - a custom test `f/tests/orders_amount_sane` that flags any order with a non-positive amount, referenced from the `orders_daily` producer with `// data_test f/tests/orders_amount_sane`:

```sql
-- Violating rows: orders whose amount is not strictly positive.
-- An empty result means the test passes.
SELECT order_id, amount
FROM _wm_target.orders_daily
WHERE amount <= 0;
```

### Violating-row samples

When a test fails, the result does not just report a count: each failed test also captures a **sample of its violating rows**, so you can see which rows broke the check without re-running the SELECT by hand. In the run view, each failed test expands into a table of its sample rows, with CSV download.

![Failed data tests with one test expanded into its violating-rows sample table](./data_test_violating_rows.png 'A failed test expanded into its violating-rows sample')

Samples are bounded and best-effort:

- At most **20 rows and 50KB** of serialized data per test. A sample over the size cap is dropped entirely, never truncated.
- A sample can never change a test's outcome: pass/fail is decided from the violating-row count alone, and a missing sample just means that test shows counts only.
- Which rows land in the sample is not deterministic (it is a sample of the violating rows, not the full set).
- `unique` samples the duplicated values as `{value, count}` pairs rather than raw rows, matching how its count is defined (number of duplicated values). On partitioned targets the managed `_wm_partition` column is excluded from samples.

The error *message* of a failed run stays counts-only (`✗ unique(id) — 3 violating row(s)`), so run descriptions and alerts built from the message carry no row data. The samples live in the structured result of the failed job, under `error.data_tests` (a list of `{test, violating, sample?}`). An [error handler](../10_error_handling/index.mdx) that formats only the error message keeps alerting with counts; one that wants the offending rows can read them from `error.data_tests` in the job result it receives.

On Enterprise Edition, where a violating run is rolled back before it commits (see [below](#failing-tests-publish-or-roll-back-ce-vs-ee)), the violating rows no longer exist to sample: an EE failed run shows the per-test checklist with counts only.

### Failing tests: publish or roll back (CE vs EE)

What happens to the data written by a failing run depends on the edition:

- On the Community Edition, materialization is commit-then-test, like dbt: the write is committed first, then the tests run. A failing test fails the run and stops the cascade, but the slice it wrote is already published - BI tools and ad-hoc readers see the failing data until the next successful run.
- On Enterprise Edition, materialization uses write-audit-publish (WAP): the same checks are also evaluated inside the write transaction, just before commit. Any violation aborts the transaction before it commits, so the write is rolled back - readers keep the previous version and no DuckLake snapshot is created. This is automatic on EE; no configuration or annotation change is needed.

| On a failing data test | CE (commit-then-test) | EE (write-audit-publish) |
| --- | --- | --- |
| Freshly written slice | Published: committed before the tests run, so readers see the failing data | Rolled back: readers keep the previous version |
| DuckLake snapshot | New snapshot created (time-travel can inspect the bad write) | No snapshot created |
| First run of a brand-new asset | Table created, filled with the failing data | No table created at all |
| Job result, error message, cascade | Run fails with the per-test pass/fail breakdown; downstream cascade stops | Identical |
| [Violating-row samples](#violating-row-samples) | Failed tests include a sample of their violating rows | Counts only: the rolled-back rows no longer exist to sample |

Passing runs behave identically in both editions, and the run result shows the same per-test breakdown either way. The difference is whether the data written by a failing run is visible afterwards - and, as a consequence, whether its violating rows are still there to sample.

## Materialization in workspace forks (dev data)

Getting an isolated dev-data environment for a pipeline is just [forking the workspace](../../advanced/20_workspace_forks/index.mdx#fork-creation) - no separate setup. The fork's id is always `wm-fork-<name>` (the prefix is what lets Windmill route its data to an isolated namespace), and in a fork, materialization does not touch the parent workspace's tables. By default each lake resolves to a fork-scoped namespace (separate metadata schema and bucket prefix), and tables the fork has not materialized yet are read from the parent through read-only defer views, so you can iterate on one node without rebuilding upstream. The pipeline graph marks each DuckLake asset with a `deferred` (reading parent data) or `fork` (fork-materialized) chip. No annotation changes are needed: the same `ducklake://` URIs transparently target the fork's environment, and a per-lake option at fork creation lets you opt out and share the parent's lake instead.