Write modes and partitioning

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 for how datasets are stored.

Choose a write mode

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:

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.

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:

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:

target.customer_id = source.customer_id AND target.company_id = source.company_id

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

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.

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.

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:

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