> Documentation index: https://docs.beetl.io/llms.txt

# Write modes and partitioning

> Append, overwrite and merge, and how output lands in a dataset.

Source: https://docs.beetl.io/pipelines/write-modes-and-partitioning/
Last updated: 2026-10-03

The destination section of the editor decides what a run does with the last stage's result: which
dataset it writes, how the rows combine with what is already there, and how the dataset is
partitioned. Every run commits as one transaction, so a failed run leaves the destination as it was.
See [Datasets and tiers](https://docs.beetl.io/concepts/datasets-and-tiers) for how datasets are stored.

## Choose a write mode

| Mode                  | Each run                                      | Use it when                                                   |
| --------------------- | --------------------------------------------- | ------------------------------------------------------------- |
| Append                | Adds the result rows                          | Each run produces rows that are new, such as daily snapshots  |
| Overwrite, full table | Replaces the whole dataset with the result    | The last stage recomputes everything, such as a summary table |
| Overwrite, predicate  | Replaces only the rows that match a predicate | Each run recomputes one slice, such as one day                |
| Merge                 | Updates matching rows and inserts the rest    | Rows have a key and change over time, such as customers       |

Overwrite and Merge are safe to rerun with the same input. Append is not: running it twice adds
the rows twice.

## Append

Append adds the result to the destination and never changes or removes existing rows. This stage
records a balance snapshot each time the pipeline runs:

```sql
SELECT account_id, balance, DATE '{{ fn.today() }}' AS snapshot_date
FROM accounts
```

## Overwrite the full table

Choose Overwrite, then **Full table** under **Replace scope**. Each run replaces the entire dataset
with the result, so the destination always holds exactly what the last run produced.

```sql
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
```

## Overwrite with a predicate

Choose Overwrite, then **Predicate** under **Replace scope**. Pick a column, an operator (`=`,
`!=`, `>=`, `<=`, `>` or `<`) and a value. The editor shows the predicate it builds, for example
`dt = '{{ run.dt }}'`. A run deletes the destination rows that match the predicate and writes the
result in their place; all other rows stay.

Every row in the result must match the predicate. If one does not, the run fails without writing,
so filter the last stage on the same condition:

```sql
SELECT order_id, customer_id, amount, dt
FROM orders
WHERE dt = DATE '{{ run.dt }}'
```

The predicate can use any column, but it works best on a partition column, so that each run
replaces whole partitions. A predicate the builder cannot represent, such as one written by the
chat assistant, appears as a **Partition predicate** text field that you edit directly.

## Merge

Choose Merge and add one or more **merge keys**. Each key pairs a destination column
(`target.`) with a result column (`source.`), and the keys combine with `AND`:

```sql
target.customer_id = source.customer_id AND target.company_id = source.company_id
```

For each result row, a run does one of three things:

| Case                                    | What happens                                                 |
| --------------------------------------- | ------------------------------------------------------------ |
| A destination row matches               | That row's columns are replaced with the result row's values |
| No destination row matches              | The result row is inserted                                   |
| A destination row matches no result row | It stays unchanged                                           |

If 2 or more result rows match the same destination row, the run fails without writing. Result rows
that match nothing are all inserted, duplicates included. Make the last stage return one row per
key; see [Deduplicate records](https://docs.beetl.io/pipelines/deduplicate-records).

```sql
SELECT customer_id, company_id, name, email, updated_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id, company_id
           ORDER BY updated_at DESC, email
         ) AS rn
  FROM customers_raw
) ranked
WHERE rn = 1
```

The match predicate can also be a custom expression, set through chat or MCP, that the key builder
cannot show as pairs; the editor then shows it as an editable **Match predicate**. Through chat or
MCP a merge can also delete matched rows, apply its actions only under a condition, or delete
destination rows that no result row matches. The editor keeps those settings when you save.

## Partition columns

Partition columns split the destination's storage by value, so a predicate or filter on them reads
and replaces only the parts it needs. They are set under **Partition columns** in the destination
section:

* After you preview the last stage, the editor lists its output columns with their types; tick the
  ones to partition by.
* Without a preview, type the names separated by commas.
* In the Deployed view the list is locked.

Each partition column must be in the last stage's output, and each can appear once. Order counts.
Good partition columns have few distinct values and match how you filter and replace, typically a
date such as `dt`, sometimes a tenant or company column as well.

### Partitioning is fixed once the dataset exists

The first successful run creates the destination with the partition columns you chose. After that,
leaving the list empty keeps the existing partitioning, and listing the same columns in the same
order works. A different list makes the run fail without writing. To change partitioning, write to
a new destination name.

### Partitions and triggers

When a pipeline with **Trigger on update** reads this destination, the partitions a run writes
decide what runs downstream: one run per written partition, each receiving that partition's values
through its run mapping. An unpartitioned destination starts one downstream run with no values. If
downstream pipelines work per day, partition by the day and overwrite by a predicate on it, so each
run touches one partition. See [Trigger on update](https://docs.beetl.io/pipelines/deploy-and-trigger#trigger-on-update).

## Schema compatibility

The first run creates the destination with the last stage's columns and types. Every later run must
produce the same column names with the same types; column order and nullability do not matter.

If a column is added, missing, renamed or retyped, the run fails before writing and the destination
stays as it was. Destinations do not evolve their schema. To change the columns, write to a new
destination name and point downstream pipelines, queries and charts at it.

Deploy's dry run does not compare the result with an existing destination, so a mismatch first
shows up as a failed run. Over MCP, `run_pipeline` reports whether the result fits the destination
before you deploy.

To keep types stable when a source's types drift, cast them in the last stage:

```sql
SELECT
  CAST(order_id AS VARCHAR)        AS order_id,
  CAST(amount AS DECIMAL(12, 2))   AS amount,
  CAST(ordered_at AS TIMESTAMP)    AS ordered_at,
  dt
FROM cleaned_orders
```

## Related

- [Template variables](https://docs.beetl.io/pipelines/template-variables): The `run.*` and `vars.*` values used in predicates.

- [Deploy and trigger](https://docs.beetl.io/pipelines/deploy-and-trigger): When runs happen and what starts them.

- [Datasets and tiers](https://docs.beetl.io/concepts/datasets-and-tiers): Schema rules shared by every producer.