Merges for DuckDB branches and snapshots

differential 1.1 merges branches cell by cell. Conflicts are resolved with a table, schemas can change, and results go back into main, locally or through S3.

Since the first post about differential, the answer to “how do I get my branch back into main?” has been: you don’t, not really. differential_restore can adopt a branch’s entire state, but that throws away everything main did in the meantime. The 1.0 post said so plainly in its list of limits: No merge. Branches and conflicts are kept, never merged automatically. And the diff post stopped exactly where a merge would begin. You could see what a branch changed, but bringing those changes back was SQL you had to write yourself.

Version 1.1 closes that gap with three-way merges:

• differential_merge_branch merges two branches. It finds their common base in the recorded history and writes the result to a new branch or back into the target branch, fast-forwarding where it can.
• differential_merge_snapshots merges three snapshots you pick yourself, for the cases where automatic base selection can’t help.
• Preview and preflight functions show changes, conflicts and constraint violations before anything is written.
• Conflicts are typed, and you resolve them with an ordinary table of decisions.
• Schema changes merge too, if you opt in: added columns and tables, dropped tables and columns, replaced views.
• It all works for local and single-writer S3 databases. Merge requests survive crashes and can be retried, inspected or aborted.

This post walks through the full lifecycle in SQL: a first merge into main, fast-forwards, conflicts, schema changes, ambiguous history and S3. Then it explains what isn’t supported, and why. That part is long on purpose. A merge that silently produces wrong data is worse than no merge at all, so most of the limits are deliberate refusals rather than missing features.

Getting started#

Version 1.1 is available from the community repository for DuckDB 2.0 on Linux and macOS (Apple silicon). If you have 1.0 installed, FORCE INSTALL fetches the new version:

FORCE INSTALL differential FROM community;
LOAD differential;
SET differential_repository = '/absolute/path/merge-demo';

Repositories written by 1.0 open unchanged. The storage format is the same, and nothing is migrated. From 1.1 on, every snapshot also records its parent snapshots, which is what automatic base selection relies on. Snapshots written by 1.0 don’t have these records, so there are two things to know when upgrading:

• Branches forked from the same snapshot in 1.0 can still be merged automatically, once. The base is proven from 1.0’s branch-creation records. A repeated merge whose history reaches back into 1.0 snapshots needs an explicit base, with a snapshot merge (covered below).
• Don’t go back to 1.0 after 1.1 has written to a repository. 1.0 doesn’t know merge records: its metadata cleanup deletes them, and its export retry can roll back an S3 merge result that is published but not yet finalized locally. If you might need to go back, keep a copy of the repository directory from before the upgrade.

How a merge decides#

A merge has three inputs: the base, the state both sides started from, and the two states to combine, called ours and theirs. The names come from Git. “Ours” is the branch you merge into, “theirs” is the branch you merge from. differential matches the rows of each table across the three snapshots by primary key and decides every cell on its own:

Cell of a row that exists everywhere	Result
Ours equals theirs	That value
Ours equals base	Theirs (only they changed it)
Theirs equals base	Ours (only we changed it)
All three differ	cell conflict

Rows that exist on only some sides follow the same logic:

Row	Result
Inserted on one side	Inserted
Inserted with identical values on both sides	Inserted once
Inserted with different values on both sides	insert_insert conflict
Deleted on both sides, or deleted on one and unchanged on the other	Deleted
Deleted on one side, updated on the other	delete_update conflict

Values are compared in their native types with DuckDB’s IS DISTINCT FROM. NULLs compare as equal, and there are no text conversions or floating-point tolerances. One row can take one column from ours and another from theirs, which makes this a cell-level merge, not a row-level one.

One property explains most of what follows: this is a merge of states, not of operations. A snapshot is a version of the database file, not a log of the statements that produced it. The merge never sees an UPDATE or an ALTER TABLE … RENAME, only what the tables looked like at three points in time. Keep that in mind for the limits at the end.

Merging a branch back into main#

We start with the same orders table as the diff post, plus a snapshot to fork from:

ATTACH 'differential:retail' AS retail;
CREATE TABLE retail.orders (
    order_id INTEGER PRIMARY KEY,
    customer VARCHAR,
    amount   DECIMAL(10, 2),
    status   VARCHAR
);
INSERT INTO retail.orders VALUES
    (1, 'Ada', 40.00, 'paid'),
    (2, 'Lin', 60.00, 'paid'),
    (3, 'Jo',  25.00, 'open'),
    (4, 'Kim', NULL,  'open'),
    (5, 'Max', 15.00, 'paid');
CALL differential_create_snapshot('retail', name => 'baseline');             -- snapshot 1

A data cleanup happens on a branch, so that main stays untouched until someone has reviewed it:

CALL differential_create_branch('retail', 'cleanup', snapshot => 1);
ATTACH 'differential:retail' AS cleanup (BRANCH 'cleanup');
UPDATE cleanup.orders SET amount = 54.00 WHERE order_id = 2;                  -- corrected amount
DELETE FROM cleanup.orders WHERE order_id = 3;                                -- test order removed
UPDATE cleanup.orders SET amount = 20.00, status = 'paid' WHERE order_id = 4; -- missing amount filled
INSERT INTO cleanup.orders VALUES (6, 'Sam', 30.00, 'open');                  -- late order
CALL differential_create_snapshot('cleanup', name => 'cleanup_done');         -- snapshot 2

Meanwhile, main keeps going, with a new order and a refund:

INSERT INTO retail.orders VALUES (7, 'Eve', 12.00, 'open');
UPDATE retail.orders SET status = 'refunded' WHERE order_id = 1;
CALL differential_create_snapshot('retail', name => 'monday');                -- snapshot 3

The history now has two sides that both moved on from snapshot 1:

Restore can’t help here: adopting snapshot 2 would undo order 7 and the refund. That’s the job for a merge.

Preview#

A preview runs the whole comparison and writes nothing:

SELECT table_name,
       base_snapshot_id AS base, ours_snapshot_id AS ours, theirs_snapshot_id AS theirs,
       [ours_inserts, ours_updates, ours_deletes]       AS ours_ins_upd_del,
       [theirs_inserts, theirs_updates, theirs_deletes] AS theirs_ins_upd_del,
       conflicts, validation_state
FROM differential_merge_branch_preview('retail', 'main', 'cleanup', validate => true);

┌────────────┬──────┬──────┬────────┬──────────────────┬────────────────────┬───────────┬──────────────────┐
│ table_name │ base │ ours │ theirs │ ours_ins_upd_del │ theirs_ins_upd_del │ conflicts │ validation_state │
├────────────┼──────┼──────┼────────┼──────────────────┼────────────────────┼───────────┼──────────────────┤
│ orders     │    1 │    3 │      2 │ [1, 1, 0]        │ [1, 2, 1]          │         0 │ valid            │
└────────────┴──────┴──────┴────────┴──────────────────┴────────────────────┴───────────┴──────────────────┘

The preview selected the base on its own: snapshot 1, the fork point, compared with the latest snapshots of both branches. main inserted one row and updated one; cleanup inserted one, updated two and deleted one. There are no conflicts. With validate => true, the preview also built the merged table in a disposable staging copy and checked it against all native constraints, hence valid. Without it, the preview only compares and reports not_checked.

Note what the preview didn’t look at: live data. Merges only ever read published snapshots. Changes on a branch after its latest snapshot aren’t part of the merge.

Preflight and merge#

To merge into main itself, main has to be detached first. A preflight then checks everything the merge would check, including the target:

DETACH retail;
SELECT ready, merge_action, target_state, validation_state,
       base_snapshot_id, ours_snapshot_id, theirs_snapshot_id
FROM differential_merge_branch_preflight('retail', 'main', 'cleanup', 'main');

┌───────┬──────────────┬──────────────┬──────────────────┬──────────────────┬──────────────────┬────────────────────┐
│ ready │ merge_action │ target_state │ validation_state │ base_snapshot_id │ ours_snapshot_id │ theirs_snapshot_id │
├───────┼──────────────┼──────────────┼──────────────────┼──────────────────┼──────────────────┼────────────────────┤
│ true  │ merged       │ clean        │ valid            │                1 │                3 │                  2 │
└───────┴──────────────┴──────────────┴──────────────────┴──────────────────┴──────────────────┴────────────────────┘

ready means every check passed at that moment. It’s not a reservation: someone could publish a new snapshot on cleanup a second later, and the merge would pick it up. That’s what the three expected_*_snapshot_id guards are for. With the reviewed IDs, the merge fails with merge_inputs_changed instead of merging something nobody reviewed:

SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'main', 'cleanup', 'main',
    request_id                  => 'merge-cleanup',
    expected_base_snapshot_id   => 1,
    expected_ours_snapshot_id   => 3,
    expected_theirs_snapshot_id => 2);

┌─────────────┬─────────────┬──────────────┐
│ branch_name │ snapshot_id │ merge_action │
├─────────────┼─────────────┼──────────────┤
│ main        │           4 │ merged       │
└─────────────┴─────────────┴──────────────┘

ATTACH 'differential:retail' AS retail;
FROM retail.orders ORDER BY order_id;

┌──────────┬──────────┬────────┬──────────┐
│ order_id │ customer │ amount │  status  │
├──────────┼──────────┼────────┼──────────┤
│        1 │ Ada      │  40.00 │ refunded │
│        2 │ Lin      │  54.00 │ paid     │
│        4 │ Kim      │  20.00 │ paid     │
│        5 │ Max      │  15.00 │ paid     │
│        6 │ Sam      │  30.00 │ open     │
│        7 │ Eve      │  12.00 │ open     │
└──────────┴──────────┴────────┴──────────┘

Every change from both sides made it: the refund and order 7 from main, and the four cleanup changes. Snapshot 4 is an ordinary public snapshot with two parents, 3 and 2:

Its commit_extra_info records the merge algorithm, the operation and all three inputs with their content roots, so you can always tell where a merge result came from.

A few details matter:

• The target must be detached. A merge into an attached branch fails with active_attachment_or_transaction. The source can stay attached.
• The target must match its latest snapshot. If main had committed writes after snapshot 3, publishing a result built from snapshot 3 would silently drop them. The merge refuses with merge_target_dirty instead. The fix: create a snapshot, detach, merge.
• request_id makes a merge retryable. Running the same call again returns the original result instead of merging twice. A request ID belongs to one set of arguments, though; reusing it with different ones fails.
• To review the result first, merge into a new branch. With an unused name as the destination, say 'main-with-cleanup', the result becomes a new branch, and both inputs stay as they are. The merge_action is then created_branch.

Fast-forward and already up to date#

The cleanup branch is now behind main. Bringing it up to date is a merge in the other direction, with cleanup as both target and destination:

DETACH cleanup;
SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'cleanup', 'main', 'cleanup', request_id => 'update-cleanup');

┌─────────────┬─────────────┬──────────────┐
│ branch_name │ snapshot_id │ merge_action │
├─────────────┼─────────────┼──────────────┤
│ cleanup     │           4 │ fast_forward │
└─────────────┴─────────────┴──────────────┘

Snapshot 2, the head of cleanup, is an ancestor of snapshot 4, so there’s nothing to combine: cleanup moves to the existing snapshot 4, and no new snapshot is created. Merging cleanup into main again should find nothing to do. On the first try it doesn’t get that far, because main is still attached from the previous step:

SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'main', 'cleanup', 'main', request_id => 'noop-attached');
-- Invalid Error: [differential:active_attachment_or_transaction] Merge target must be detached
-- with no transaction holding it

DETACH retail;
SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'main', 'cleanup', 'main', request_id => 'merge-cleanup-again');

┌─────────────┬─────────────┬────────────────────┐
│ branch_name │ snapshot_id │    merge_action    │
├─────────────┼─────────────┼────────────────────┤
│ main        │           4 │ already_up_to_date │
└─────────────┴─────────────┴────────────────────┘

These decisions come from the recorded snapshot graph, which differential_merge_ancestry shows for any two snapshots. Here’s the graph behind the fast-forward, for main’s head 4 and cleanup’s old head 2:

SELECT snapshot_id, kind, parents, reachable_from, selected_base
FROM differential_merge_ancestry('retail', 4, 2);

┌─────────────┬────────┬─────────┬────────────────┬───────────────┐
│ snapshot_id │  kind  │ parents │ reachable_from │ selected_base │
├─────────────┼────────┼─────────┼────────────────┼───────────────┤
│           1 │ root   │ []      │ both           │             2 │
│           2 │ linear │ [1]     │ both           │             2 │
│           3 │ linear │ [1]     │ ours           │             2 │
│           4 │ merge  │ [3, 2]  │ ours           │             2 │
└─────────────┴────────┴─────────┴────────────────┴───────────────┘

Snapshots 1 and 2 are reachable from both heads. The best common ancestor is the one that isn’t an ancestor of another common ancestor: 2. Since that’s one of the two heads itself, one side contains the other, and there’s nothing to merge. Snapshot numbers, timestamps and identical contents never count as ancestry. Only recorded parent edges do.

So a branch merge has four possible outcomes:

merge_action	When	What happens
merged	Both sides changed since the base	A new snapshot with both heads as parents
fast_forward	The target’s head is an ancestor of the source’s	The target moves to the source’s existing snapshot
already_up_to_date	The source’s head is an ancestor of (or equal to) the target’s	Nothing changes
created_branch	The destination is a new name	A new branch holds the result

Fast-forwards and no-ops still run all checks, like supported schemas and retained inputs, so they are cheap, but not free.

Conflicts, and how to resolve them#

Now two changes collide. A pricing branch, forked from main’s latest snapshot (4), adjusts three prices:

CALL differential_create_branch('retail', 'pricing');
ATTACH 'differential:retail' AS pricing (BRANCH 'pricing');
UPDATE pricing.orders SET amount = 18.00 WHERE order_id = 5;
UPDATE pricing.orders SET amount = 13.50 WHERE order_id = 7;
UPDATE pricing.orders SET amount = 33.00 WHERE order_id = 6;
CALL differential_create_snapshot('pricing', name => 'new_prices');           -- snapshot 5

At the same time, main corrects order 5 by hand, cancels order 7 and marks order 6 as paid:

ATTACH 'differential:retail' AS retail;
UPDATE retail.orders SET amount = 16.50 WHERE order_id = 5;
DELETE FROM retail.orders WHERE order_id = 7;
UPDATE retail.orders SET status = 'paid' WHERE order_id = 6;
CALL differential_create_snapshot('retail', name => 'tuesday');               -- snapshot 6

The preview reports two conflicts. Its conflict_details column lists them, with the key, the kind of conflict and the columns involved:

SELECT c.key[1].integer_value AS order_id, c.kind, c.columns
FROM (SELECT unnest(conflict_details) AS c
      FROM differential_merge_branch_preview('retail', 'main', 'pricing'));

┌──────────┬───────────────┬──────────┐
│ order_id │     kind      │ columns  │
├──────────┼───────────────┼──────────┤
│        5 │ cell          │ [amount] │
│        7 │ delete_update │ [amount] │
└──────────┴───────────────┴──────────┘

Order 6 isn’t on the list, although both sides touched it. pricing changed its amount and main its status, so the merge takes each cell from the side that changed it. Orders 5 and 7 are real conflicts: both sides set a different amount for order 5, and main deleted order 7 while pricing updated it.

The details also contain the typed values of all three sides, together with presence flags that tell a missing row apart from a row of NULLs. For a quick look, AT time travel on the three snapshots is often easier to read:

SELECT order_id, b.amount AS base, o.amount AS ours, t.amount AS theirs
FROM (FROM retail.orders AT (VERSION => 4)) AS b
FULL JOIN (FROM retail.orders AT (VERSION => 6)) AS o USING (order_id)
FULL JOIN (FROM retail.orders AT (VERSION => 5)) AS t USING (order_id)
WHERE order_id IN (5, 7)
ORDER BY order_id;

┌──────────┬───────┬───────┬────────┐
│ order_id │ base  │ ours  │ theirs │
├──────────┼───────┼───────┼────────┤
│        5 │ 15.00 │ 16.50 │  18.00 │
│        7 │ 12.00 │  NULL │  13.50 │
└──────────┴───────┴───────┴────────┘

A merge with unresolved conflicts fails and publishes nothing:

DETACH retail;
SELECT * FROM differential_merge_branch('retail', 'main', 'pricing', 'main',
    request_id => 'merge-pricing-unresolved');
-- Invalid Input Error: [differential:merge_conflict] 2 conflicting rows;
-- use differential_merge_snapshots_preview

A table of decisions#

Conflicts are resolved with data, not with a flag. A resolution table has exactly the columns of the target table, plus two control columns: _merge_action says what to do with the row, and _merge_columns maps conflicting cells to a choice. The primary key identifies the conflict.

Conflict	_merge_action	_merge_columns	Effect
cell	cells	every conflicting column → ours, theirs or custom	Resolves those cells; the other cells still merge automatically
insert_insert, delete_update	ours or theirs	NULL	Takes that side’s whole row state, including “deleted”
insert_insert, delete_update	delete	NULL	Omits the row
insert_insert, delete_update	custom	NULL	Uses the row from the resolution table

For order 5, we settle on 17.00, a custom value. For order 7, main wins and the order stays cancelled:

CREATE TABLE price_decisions (
    order_id INTEGER, customer VARCHAR, amount DECIMAL(10, 2), status VARCHAR,
    _merge_action  VARCHAR,
    _merge_columns MAP(VARCHAR, VARCHAR)
);
INSERT INTO price_decisions VALUES
    (5, NULL, 17.00, NULL, 'cells', map {'amount': 'custom'}),
    (7, NULL, NULL,  NULL, 'ours',  NULL);

The table lives in DuckDB’s default in-memory catalog here. It has to be a committed, native table; temporary tables and views aren’t accepted. The preview takes the same resolution_tables mapping and shows whether the decisions cover everything:

SELECT conflicts, resolved_conflicts, validation_state
FROM differential_merge_branch_preview('retail', 'main', 'pricing', validate => true,
    resolution_tables => map {'main.orders': 'memory.main.price_decisions'});

┌───────────┬────────────────────┬──────────────────┐
│ conflicts │ resolved_conflicts │ validation_state │
├───────────┼────────────────────┼──────────────────┤
│         0 │                  2 │ valid            │
└───────────┴────────────────────┴──────────────────┘

Then the merge, under a new request ID, since the first attempt ran without decisions:

SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'main', 'pricing', 'main',
    request_id        => 'merge-pricing',
    resolution_tables => map {'main.orders': 'memory.main.price_decisions'});

┌─────────────┬─────────────┬──────────────┐
│ branch_name │ snapshot_id │ merge_action │
├─────────────┼─────────────┼──────────────┤
│ main        │           7 │ merged       │
└─────────────┴─────────────┴──────────────┘

ATTACH 'differential:retail' AS retail;
FROM retail.orders ORDER BY order_id;

┌──────────┬──────────┬────────┬──────────┐
│ order_id │ customer │ amount │  status  │
├──────────┼──────────┼────────┼──────────┤
│        1 │ Ada      │  40.00 │ refunded │
│        2 │ Lin      │  54.00 │ paid     │
│        4 │ Kim      │  20.00 │ paid     │
│        5 │ Max      │  17.00 │ paid     │
│        6 │ Sam      │  33.00 │ paid     │
└──────────┴──────────┴────────┴──────────┘

Order 5 has the custom amount, order 7 stays cancelled, and order 6 has pricing’s amount together with main’s status. The decisions are frozen into the request the first time it runs: a retry uses exactly these decisions, even if price_decisions changes or is dropped in the meantime. They’re also kept as an audit:

SELECT table_name, key[1].integer_value AS order_id, action, columns
FROM differential_merge_resolutions('retail', 'merge-pricing');

┌────────────┬──────────┬────────┬─────────────────┐
│ table_name │ order_id │ action │     columns     │
├────────────┼──────────┼────────┼─────────────────┤
│ orders     │        5 │ cells  │ {amount=custom} │
│ orders     │        7 │ ours   │ NULL            │
└────────────┴──────────┴────────┴─────────────────┘

There’s deliberately no “theirs always wins” switch. Every conflict needs an explicit decision, and an incomplete table leaves the merge failing. Decisions can’t change a primary key, and the merged result is still checked against all native constraints afterwards.

Requests you can inspect, retry and abort#

Every merge is a durable request in the repository’s metadata, including the ones that failed:

SELECT request_id, state, merge_action, destination,
       base_snapshot_id AS base, ours_snapshot_id AS ours, theirs_snapshot_id AS theirs,
       result_snapshot_id AS result
FROM differential_merge_requests('retail')
ORDER BY created_at;

┌──────────────────────────┬──────────┬────────────────────┬─────────────┬──────┬──────┬────────┬────────┐
│        request_id        │  state   │    merge_action    │ destination │ base │ ours │ theirs │ result │
├──────────────────────────┼──────────┼────────────────────┼─────────────┼──────┼──────┼────────┼────────┤
│ merge-cleanup            │ complete │ merged             │ main        │    1 │    3 │      2 │      4 │
│ update-cleanup           │ complete │ fast_forward       │ cleanup     │    2 │    2 │      4 │      4 │
│ noop-attached            │ pending  │ already_up_to_date │ main        │    4 │    4 │      4 │   NULL │
│ merge-cleanup-again      │ complete │ already_up_to_date │ main        │    4 │    4 │      4 │      4 │
│ merge-pricing-unresolved │ pending  │ merged             │ main        │    4 │    6 │      5 │   NULL │
│ merge-pricing            │ complete │ merged             │ main        │    4 │    6 │      5 │      7 │
└──────────────────────────┴──────────┴────────────────────┴─────────────┴──────┴──────┴────────┴────────┘

The two failed attempts are pending. A failed local attempt changes no branch and holds no data; the record lets you retry the request with the same arguments, which recomputes it from the same frozen input snapshots. The request also records its latest attempt, with a safe error code (active_attachment_or_transaction and merge_conflict here) and a recovery hint. To close a request for good, abort it by its operation ID:

SET VARIABLE stale = (SELECT operation_id
                      FROM differential_merge_requests('retail', request_id => 'merge-pricing-unresolved'));
SELECT state, snapshot_id
FROM differential_abort_merge('retail', getvariable('stale'), request_id => 'abort-unresolved');
-- aborted  NULL

An aborted request is terminal: replaying it fails with merge_aborted. A completed one replays its original result, even after its input snapshots have expired. Live progress of running merges, with their phase and per-table counters, is in differential_merge_status().

Schema changes#

By default, a merge runs in strict mode: all three snapshots must have identical table definitions. Let’s add something worth merging. First, main gets a view and a log table:

USE retail;
CREATE VIEW open_orders AS
    SELECT order_id, customer, amount FROM orders WHERE status = 'open';
CREATE TABLE import_log (batch_id INTEGER PRIMARY KEY, note VARCHAR);
USE memory;
CALL differential_create_snapshot('retail', name => 'reporting');             -- snapshot 8

USE retail makes the view’s orders reference resolve inside the database, without a catalog prefix. That matters for merges, which only support local references. Now a channels branch adds a column and a new table, while main takes a new order:

CALL differential_create_branch('retail', 'channels');
ATTACH 'differential:retail' AS channels (BRANCH 'channels');
ALTER TABLE channels.orders ADD COLUMN channel VARCHAR;
UPDATE channels.orders SET channel = 'web' WHERE order_id IN (1, 2, 4);
CREATE TABLE channels.customers (customer VARCHAR PRIMARY KEY, country VARCHAR);
INSERT INTO channels.customers VALUES ('Ada', 'UK'), ('Lin', 'SG'), ('Max', 'DE');
CALL differential_create_snapshot('channels');                                -- snapshot 9

INSERT INTO retail.orders VALUES (8, 'Ada', 22.00, 'open');
CALL differential_create_snapshot('retail');                                  -- snapshot 10

Strict mode refuses:

Invalid Input Error: [differential:merge_unsupported] schema mismatch: table additions/deletions are unsupported

With schema_mode => 'additive', the preview shows the planned schema changes:

SELECT object_name, schema_changes, table_action, conflicts, validation_state
FROM differential_merge_branch_preview('retail', 'main', 'channels',
    schema_mode => 'additive', validate => true);

┌─────────────┬──────────────────────────────────────────────────────────────────────────────────┬──────────────┬───────────┬──────────────────┐
│ object_name │                                  schema_changes                                  │ table_action │ conflicts │ validation_state │
├─────────────┼──────────────────────────────────────────────────────────────────────────────────┼──────────────┼───────────┼──────────────────┤
│ customers   │ []                                                                               │ add          │         0 │ valid            │
│ import_log  │ []                                                                               │ NULL         │         0 │ valid            │
│ orders      │ [{'column_name': channel, 'source': theirs, 'sql_type': VARCHAR, 'action': add}] │ NULL         │         0 │ valid            │
└─────────────┴──────────────────────────────────────────────────────────────────────────────────┴──────────────┴───────────┴──────────────────┘

DETACH retail;
SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'main', 'channels', 'main',
    schema_mode => 'additive', request_id => 'merge-channels');               -- snapshot 11, merged
ATTACH 'differential:retail' AS retail;
FROM retail.orders ORDER BY order_id;

┌──────────┬──────────┬────────┬──────────┬─────────┐
│ order_id │ customer │ amount │  status  │ channel │
├──────────┼──────────┼────────┼──────────┼─────────┤
│        1 │ Ada      │  40.00 │ refunded │ web     │
│        2 │ Lin      │  54.00 │ paid     │ web     │
│        4 │ Kim      │  20.00 │ paid     │ web     │
│        5 │ Max      │  17.00 │ paid     │ NULL    │
│        6 │ Sam      │  33.00 │ paid     │ NULL    │
│        8 │ Ada      │  22.00 │ open     │ NULL    │
└──────────┴──────────┴────────┴──────────┴─────────┘

Order 8 came from main, which never had the channel column, so its cell is NULL. That’s exactly how additive mode works: an input without an added column contributes a typed NULL for it, and then the ordinary cell rules apply. The customers table came over with its definition, primary key and rows.

Additive mode has firm rules. An added column must be nullable, must not be part of a key and must not have a default. A new table may have defaults, NOT NULL, UNIQUE and CHECK constraints and simple indexes. If both sides add the same column or table, the definitions must be identical; otherwise it’s a schema conflict. Any schema conflict blocks the whole merge, and a resolution table can’t override it, since decisions only apply to rows.

The third mode, catalog, also handles removals and view changes. A tidy branch drops the unused log table and replaces the view to include the new column, while main fills in a channel:

CALL differential_create_branch('retail', 'tidy');
ATTACH 'differential:retail' AS tidy (BRANCH 'tidy');
DROP TABLE tidy.import_log;
USE tidy;
CREATE OR REPLACE VIEW open_orders AS
    SELECT order_id, customer, amount, channel FROM orders WHERE status = 'open';
USE memory;
CALL differential_create_snapshot('tidy');                                    -- snapshot 12

UPDATE retail.orders SET channel = 'store' WHERE order_id = 8;
CALL differential_create_snapshot('retail');                                  -- snapshot 13

SELECT object_type, object_name, table_action, view_change.action AS view_action,
       schema_conflicts, validation_state
FROM differential_merge_branch_preview('retail', 'main', 'tidy',
    schema_mode => 'catalog', validate => true);

┌─────────────┬─────────────┬──────────────┬─────────────┬──────────────────┬──────────────────┐
│ object_type │ object_name │ table_action │ view_action │ schema_conflicts │ validation_state │
├─────────────┼─────────────┼──────────────┼─────────────┼──────────────────┼──────────────────┤
│ table       │ customers   │ NULL         │ NULL        │                0 │ valid            │
│ table       │ import_log  │ drop         │ NULL        │                0 │ valid            │
│ table       │ orders      │ NULL         │ NULL        │                0 │ valid            │
│ view        │ open_orders │ NULL         │ replace     │                0 │ valid            │
└─────────────┴─────────────┴──────────────┴─────────────┴──────────────────┴──────────────────┘

DETACH retail;
SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('retail', 'main', 'tidy', 'main',
    schema_mode => 'catalog', request_id => 'merge-tidy');                    -- snapshot 14, merged
ATTACH 'differential:retail' AS retail;
FROM retail.open_orders;     -- 8  Ada  22.00  store
SHOW TABLES FROM retail;     -- customers, open_orders, orders

The removal rules follow the same three-way logic as cells. A table is removed only if the other side left it exactly as it was in the base: same definition, same rows, compared by key with IS DISTINCT FROM. If the other side changed anything in it, the result is a table_delete_modify conflict. A plain nullable column can be removed under the same condition; otherwise, it’s column_delete_modify. Views merge as whole definitions: if only one side changed a view, that version wins, and if both changed it differently, it’s a view_definition conflict. The merged catalog must still bind, so a view that references a dropped column fails with view_binding. A SELECT * view that would silently gain columns from the other side fails with view_output.

When the base is ambiguous#

Automatic base selection needs one best common ancestor. Usually there is one, but there’s a classic history where there isn’t, the criss-cross merge. Two branches, a and b, each change something. Then their changes are combined twice, once into a new branch ab and once into a new branch ba, and both of those move on:

-- database "crisscross" with snapshot 1 on main
CALL differential_create_branch('crisscross', 'a', snapshot => 1);
CALL differential_create_branch('crisscross', 'b', snapshot => 1);
-- a: refund order 1                -> snapshot 2
-- b: correct the amount of order 2 -> snapshot 3
SELECT * FROM differential_merge_branch('crisscross', 'a', 'b', 'ab');   -- snapshot 4, created_branch
SELECT * FROM differential_merge_branch('crisscross', 'b', 'a', 'ba');   -- snapshot 5, created_branch
-- ab: insert order 3 -> snapshot 6
-- ba: insert order 4 -> snapshot 7

Snapshots 4 and 5 both have the parents 2 and 3. Merging ab with ba now has two best common ancestors, and neither is “better”:

SELECT * FROM differential_merge_branch_preview('crisscross', 'ab', 'ba');
-- Invalid Error: [differential:merge_base_ambiguous] Multiple best common ancestors;
-- use differential_merge_snapshots with an explicit base

SELECT snapshot_id, kind, parents, reachable_from, candidate, selection_state
FROM differential_merge_ancestry('crisscross', 6, 7);

┌─────────────┬────────┬─────────┬────────────────┬───────────┬─────────────────┐
│ snapshot_id │  kind  │ parents │ reachable_from │ candidate │ selection_state │
├─────────────┼────────┼─────────┼────────────────┼───────────┼─────────────────┤
│           1 │ root   │ []      │ both           │ false     │ ambiguous       │
│           2 │ linear │ [1]     │ both           │ true      │ ambiguous       │
│           3 │ linear │ [1]     │ both           │ true      │ ambiguous       │
│           4 │ merge  │ [2, 3]  │ ours           │ false     │ ambiguous       │
│           5 │ merge  │ [3, 2]  │ theirs         │ false     │ ambiguous       │
│           6 │ linear │ [4]     │ ours           │ false     │ ambiguous       │
│           7 │ linear │ [5]     │ theirs         │ false     │ ambiguous       │
└─────────────┴────────┴─────────┴────────────────┴───────────┴─────────────────┘

Git’s “recursive” strategy handles this by first merging the candidates, 2 and 3, into a virtual base. differential doesn’t do that (yet; virtual bases are on the list of deferred work). That inner merge can conflict as well, and then there’s no correct answer to choose automatically. So for now, it refuses and leaves the base to you. differential_merge_snapshots takes three snapshot IDs and always writes a new branch:

SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_snapshots('crisscross', 4, 6, 7, 'combined', request_id => 'combine-ab-ba');
-- combined  8  created_branch

┌──────────┬──────────┬────────┬──────────┐
│ order_id │ customer │ amount │  status  │
├──────────┼──────────┼────────┼──────────┤
│        1 │ Ada      │  40.00 │ refunded │
│        2 │ Lin      │  54.00 │ paid     │
│        3 │ Jo       │  25.00 │ open     │
│        4 │ Kim      │  20.00 │ open     │
└──────────┴──────────┴────────┴──────────┘

Snapshot 4 is the right base here: it is the merge of 2 and 3, which is exactly what a virtual base would contain, and snapshot 5 has the same contents.

With this function, choosing the base is your responsibility. differential doesn’t check that the base you pass is an ancestor of both sides, and a wrong base changes the result. Say one side reverted a change that the other side still has. If you pass a base from before that change, the revert looks like “no change” and is silently lost. Snapshot merges are also the tool when history reaches back into 1.0 snapshots without parent records, when you want to merge specific older snapshots instead of the current heads, and for paginating conflict details over a fixed set of inputs (differential_merge_snapshots_preview).

Merging on S3#

The same functions work for single-writer S3 databases that are registered in your repository. Credentials come from a TYPE s3 secret or the AWS SDK chain, as always:

CREATE SECRET lake (
    TYPE s3,
    PROVIDER credential_chain,
    REGION 'eu-central-1',
    SCOPE 's3://my-bucket/lake/'
);
ATTACH 'differential:sales' AS sales (DATA_PATH 's3://my-bucket/lake/');
CREATE TABLE sales.orders (
    order_id INTEGER PRIMARY KEY, customer VARCHAR, amount DECIMAL(10, 2), status VARCHAR
);
INSERT INTO sales.orders VALUES (1, 'Ada', 40.00, 'paid'), (2, 'Lin', 60.00, 'paid'), (5, 'Max', 15.00, 'paid');
CALL differential_create_snapshot('sales');                         -- 1, exported

CALL differential_create_branch('sales', 'feature');
ATTACH 'differential:sales' AS feature (BRANCH 'feature');
UPDATE feature.orders SET amount = 18.00 WHERE order_id = 5;
CALL differential_create_snapshot('feature');                       -- 2, exported

INSERT INTO sales.orders VALUES (9, 'Uma', 9.00, 'open');
CALL differential_create_snapshot('sales');                         -- 3, exported
DETACH sales;

SELECT ready, merge_action, target_state
FROM differential_merge_branch_preflight('sales', 'main', 'feature', 'main');
-- true  merged  clean

SELECT branch_name, snapshot_id, merge_action
FROM differential_merge_branch('sales', 'main', 'feature', 'main',
    request_id => 'merge-feature',
    expected_base_snapshot_id => 1, expected_ours_snapshot_id => 3, expected_theirs_snapshot_id => 2);
-- main  4  merged

Any other host can attach the moved head right away, here with its own repository:

ATTACH 'differential:s3://my-bucket/lake/heads/sales/main' AS sales_latest;
SELECT order_id, amount FROM sales_latest.orders ORDER BY order_id;
-- 1 40.00 | 2 60.00 | 5 18.00 | 9 9.00

On S3, a merge can’t be one atomic transaction, because there’s no transaction that spans the local SQLite metadata and the bucket. So publication happens in durable steps:

1. The validated result is checkpointed, and its destination name and snapshot number are reserved in one local FULL transaction.
2. The result is exported: packs, index pages, descriptor and commit record, through the same SDK path as every other export.
3. The branch head is written conditionally: with If-Match on the exact ETag of the head the merge started from, or with If-None-Match: * for a new branch. This write is the remote publication point. If another copy of the repository moved the head in the meantime, the merge gets head_conflict and never adopts the other version.
4. One more local transaction exposes the snapshot and branch and completes the request.

This is why S3 merges require a request_id. Without one, the call fails with [differential:merge_request_required] S3 merge requires a request_id. With one, an interrupted merge can always be found and finished. Retrying the same call after a crash recomputes the result before step 1, resumes the prepared result after it (even if the inputs have expired since) and completes local bookkeeping after step 3, without publishing a second time. differential_merge_requests shows where a request stopped (prepared, uploaded, publication_uncertain). differential_abort_merge refuses to discard a result while a head write might still be on its way to S3. Absence of a new head can’t prove that a request from a dead process won’t arrive later.

Inputs that were evicted from local storage are rebuilt from their verified S3 exports into disposable local files for the merge, without changing their eviction state. A merge into an existing target updates only that target’s head; the source is never republished.

Under the hood#

DuckDB still does all the actual work, unmodified. differential prepares the inputs and publishes the result:

1. Select. For a branch merge, both heads and the best common ancestor are resolved in one metadata transaction, and the selection is recorded with the request before any comparison starts.

2. Pin and attach. The three snapshots get reader pins and are attached as read-only images, so expiry or garbage collection can’t remove them mid-merge.

3. Plan. Every catalog is checked against the supported scope, and the schema mode decides the combined catalog. Schema conflicts stop the merge here.

4. Compare. For each table, plain SQL builds a typed changeset. Simplified, it looks like this:

   SELECT k.order_id,
          b.order_id IS NOT NULL AS in_base,       -- presence flags, so a deleted
          o.order_id IS NOT NULL AS in_ours,       -- row is never confused with
          t.order_id IS NOT NULL AS in_theirs,     -- a row of NULLs
          o.amount IS DISTINCT FROM b.amount AS ours_changed_amount,
          t.amount IS DISTINCT FROM b.amount AS theirs_changed_amount
          -- ... one pair of flags per column
   FROM (SELECT order_id FROM base.orders
         UNION SELECT order_id FROM ours.orders
         UNION SELECT order_id FROM theirs.orders) AS k
   LEFT JOIN base.orders   AS b USING (order_id)
   LEFT JOIN ours.orders   AS o USING (order_id)
   LEFT JOIN theirs.orders AS t USING (order_id);

   The cell and row rules from the beginning are CASE expressions over these flags. All tables are reconciled before anything is written.

5. Resolve. Frozen decisions from the resolution tables are applied. Any remaining conflict ends the merge.

6. Stage. ours is forked into a private staging namespace, which only copies a chunk map. Every affected table is dropped, recreated from its planned definition and bulk-loaded with the final rows, and its indexes are rebuilt fresh. Views are recreated in dependency order. Unaffected tables keep their native data untouched.

7. Validate and publish. DuckDB checks the native constraints and checkpoints the staged file, which is then sealed. One SQLite FULL transaction moves the branch head (or creates the new branch), records the snapshot with both parents, stores the provenance and completes the request. For S3, steps 2 and 3 of the publication come first.

Nothing reaches the target before the final transaction. A failure or a cancellation before it leaves the target’s data and head exactly as they were and cleans up the staging files; after a process crash, garbage collection removes them. (On S3, a result that was already prepared keeps the target reserved until you retry or abort the request.) Binding, PREPARE and EXPLAIN of a merge function do nothing at all; the work starts when the function is scanned, and merges require autocommit.

What it costs#

A merge compares table contents, not files. The chunk maps behind each snapshot know which 256 KiB blocks of the database file changed, but not which rows changed: that’s inside DuckDB’s compressed columnar storage, which the extension deliberately doesn’t interpret. So tables are compared by scanning them in the input snapshots, and every affected table is rebuilt completely, even if only a few rows changed. The rebuild is what lets DuckDB validate the final table with fresh constraints and indexes.

On my M2 Mac (8 cores, 16 GB), a merge of an orders table with sparse, non-conflicting updates on both sides took:

Rows in the table	Rows changed per side	Comparison	Application	Publication
1,000,000	100	207 ms	346 ms	2 ms
3,000,000	300	446 ms	831 ms	1 ms

The cost grows with the size of the table, not the size of the change. These are single runs on one machine, not a benchmark. Each merge reports these timings in its result, plus selection_ms, target_check_ms and, for evicted S3 inputs, materialization_ms.

Resource limits keep large merges from surprising you. By default, a merge reads at most 10 million rows and 1 GiB across its input snapshots, writes at most 1 GiB to staging and stops after 10 minutes. All of these are per-call options, and changing them is allowed on a retry. SET differential_merge_max_concurrent (default 2) limits concurrent merges per process; DuckDB’s own memory_limit, temp_directory and spilling apply as usual.

What’s supported, what isn’t, and why#

Here’s the scope of 1.1 at a glance:

Supported	Not supported
Local and single-writer S3 databases in the same repository, including local payload pools and evicted S3 inputs	Shared-writer (WRITERS 'shared') databases, merges across databases or repositories
Tables in the main schema with a declared primary key, including composite keys	Keyless tables (even empty ones), other schemas
Booleans, all integer types up to UHUGEINT, FLOAT, DOUBLE, DECIMAL up to precision 38, VARCHAR (default collation), BLOB, UUID, dates, times, all six timestamp types, INTERVAL	JSON, VARIANT, BIT, BIGNUM, GEOMETRY, ENUM and other custom types, non-default collations
STRUCT, LIST, ARRAY, MAP, UNION columns, merged as whole values	Field- or element-level merging of nested values, nested primary keys
PRIMARY KEY, UNIQUE, NOT NULL, CHECK, simple ART indexes, defaults from a safe expression allowlist	Foreign keys, generated columns, sequences, macros, expression indexes, comments
Added nullable columns and new tables (additive), dropped tables and plain columns, whole-view changes (catalog)	Type changes, renames, changed defaults or constraints on existing columns, removing constrained columns
Views with local joins, filters, projections and count/sum/min/max/avg	Views with CTEs, subqueries, window functions, set operations, table functions or external references
Merging into a new branch, or into a detached, clean ours branch	Merging into an attached branch or one with unsnapshotted writes
A unique best common ancestor, or an explicit base	Automatic virtual bases for criss-cross histories

Nothing outside this scope is skipped silently. An unsupported object stops the preview and the merge with merge_unsupported, and the preflight lists it as an issue, naming the first such object per input. Here’s the reasoning behind the bigger items.

Every table needs a primary key. A three-way merge needs to know which row in one snapshot is which row in another. The diff post ran into the same limit with EXCEPT: without a key, an updated row is indistinguishable from a deleted row plus an inserted one, and then “both sides changed row 5” can’t even be stated. Inventing an identity from row positions or full-row hashes would make the merge guess, so keyless tables are rejected, empty ones included.

Renames are a removal plus an addition. The merge only sees states, so it has no way to know that a table or column was renamed instead of dropped and recreated. In catalog mode, a rename therefore shows up as an independent removal and addition. If the other side edited the old name, that’s a conflict with the removal, which is the safe outcome. Rename tracking would need an operation log that snapshots don’t have.

No counter arithmetic. If both sides increment a counter from 0 to 1, the merge result is 1, not 2. Both sides agree on the final value, and that’s all a state-based merge can know. Whether it should be 2 depends on what the column means.

Nested values merge as a whole. If one side changes settings.theme and the other settings.language in the same STRUCT column, that’s one cell conflict, not a combined struct. For lists, the cases go beyond that: an appended element, a reordered list and a replaced list can all produce the same final value, and there’s no single right way to combine them. The merge uses native equality on the complete value, and you resolve the conflict by supplying the whole value in a resolution table.

Schema evolution is additive first. An added column must be nullable and have no default, because an input that doesn’t have the column contributes NULL for it. If the column had a default, the rows that existed when the column was added would get the default, while rows inserted on the other branch would get NULL. With NOT NULL, they would violate the constraint. Type changes, changed defaults and constraints on existing columns would each need their own conversion and conflict rules, so they’re not merged yet.

Removals must not lose the other side’s work. Dropping a table or column that the other side modified would throw away their changes without anyone deciding to. That’s why a removal only goes through if the other side left the object exactly as in the base. Otherwise it’s a table_delete_modify or column_delete_modify conflict, and you decide in the input snapshots.

Foreign keys, sequences and friends aren’t supported yet. Foreign keys need dependency-aware validation and application across several tables, which is a separate problem from merging each table on its own. A sequence is a counter, not a set of rows: if two branches draw from it, they hand out the same values, and there’s no state rule that merges two counters. Generated columns, macros, custom types and extra schemas each need their own rules and tests.

Defaults, CHECK constraints and views use an allowlist of expressions. The merge recreates tables in staging and re-evaluates these expressions there. A retried request must produce the same result as the first attempt, which is also why it freezes the session’s TimeZone and Calendar settings. Volatile functions like random() or now(), sequences, macros and user-defined functions would make the result depend on when, where or by whom the merge runs. View SQL is limited for a similar reason: every view is re-bound against the combined catalog to check that it still works and still returns the same columns, and only a subset of SQL is checked that way so far.

Collations and extension types aren’t plain values. With a case-insensitive collation, 'Ada' and 'ADA' would compare as equal, so “did this cell change?” would depend on a collation, not on the value. JSON texts can mean the same document with different formatting, and types like GEOMETRY come with their own semantics from extensions. The merge only uses comparisons it can trust to be native and exact.

The target must be detached and clean. differential doesn’t modify DuckDB, so it can’t swap a database file under an attached catalog: DuckDB holds that catalog, its buffers and its WAL in memory. It also doesn’t detach anyone else’s connection. And since a merge reads published snapshots, a target with later committed writes would lose them. The extension compares the content root of the detached image with that of its latest snapshot, and rejects leftover WAL files. That’s conservative: a logically unchanged database whose file changed can be rejected too, but committed work is never silently overwritten.

Ambiguous bases are refused, not guessed. A virtual base, a tie-break by snapshot number or the newest common ancestor would all produce a result. Only one of them is correct, and the merge can’t know which. The same goes for history it can’t see: if a better base might hide in history without parent records, as with snapshots from 1.0, selection fails with merge_base_unavailable instead of picking an older ancestor.

One repository, one writer per database. Ancestry records, request records and the request lock all live in the repository: in its SQLite metadata store and as OS file locks. They’re not a portable history in S3. A merge in a shared-writer database would need cross-host request recovery and a remote ancestry graph, and those are separate protocols that don’t exist yet. Shared databases keep differential_resolve_conflict for diverged branches. Clones, imports and recovered exports start a new history of their own, so there’s no ancestry to merge across databases either.

Wrapping up#

With 1.1, a branch is no longer a dead end:

• differential_merge_branch merges branches back into main or into a new branch, with an automatically selected base, fast-forwards and no-ops.
• preview, preflight and the expected_*_snapshot_id guards make sure that what you reviewed is what gets merged.
• Conflicts are typed, and resolving them is a table of decisions that is frozen with the request and kept as an audit.
• additive and catalog modes merge added columns and tables, removals and view changes, under rules that never drop the other side’s work.
• differential_merge_snapshots takes an explicit base for criss-cross histories and older snapshots.
• Merge requests are durable, retryable and abortable, locally and on single-writer S3 databases.

If you work with coding agents, the agent skill covers merging too, including the preflight-then-guarded-merge workflow. Try it on a copy of your own data, and let me know where it falls short on GitHub, especially if you hit a refusal that you think should be a merge.

References#

• duckdb-differential on GitHub
• Branch merging: semantics, scope, recovery and every error code
• SQL reference: all merge functions and their columns
• Merge validation: piloting merges on your own data
• Diffing DuckDB branches and snapshots with plain SQL (the previous post)
• Versioned DuckDB databases, locally or replicated through S3
• Differential storage for DuckDB