Updating data with MERGE INTO statement doesn't store just delta of the changed rows but creates copy of all new data

[onnimonni]

What happens?

Hey,

I would want to efficiently track only the delta of public datasets in ducklake. I asked about it in #392 where @guillesd recommended to use the MERGE INTO statement.

I didn't include the geometry column into this example because of #461 and I wanted this example simpler to understand.

Again it might have been just my poor communication and this got misunderstood.

To Reproduce

ATTACH 'ducklake:osm.ducklake'
AS osm;

INSTALL spatial; LOAD spatial;
LOAD httpfs;

CREATE OR REPLACE MACRO load_osm(area_with_country_and_date) AS TABLE
    SELECT
        kind::varchar as kind,
        id,
        tags
    FROM ST_ReadOSM('https://download.geofabrik.de/' || area_with_country_and_date || '.osm.pbf');

-- Initiate the monaco data from January 2025
CREATE TABLE osm.monaco AS
FROM load_osm('europe/monaco-250101');

-- Update the monaco data from September 2025
BEGIN;

-- FIXME: This step is very very inefficient for the storage
-- Step 1: UPDATE old rows
MERGE INTO osm.monaco
    USING load_osm('europe/monaco-250901')
    USING (kind, id)
WHEN MATCHED THEN UPDATE;

-- Step 2: Add completely new rows
MERGE INTO osm.monaco
    USING load_osm('europe/monaco-250901')
    USING (kind, id)
WHEN NOT MATCHED THEN INSERT;

-- Step 3: Delete rows which were not in the new set
WITH missing_rows AS (
    SELECT kind, id FROM osm.monaco
    ANTI JOIN load_osm('europe/monaco-250901') USING(kind, id)
)
DELETE FROM osm.monaco current_rows USING missing_rows
WHERE current_rows.id = missing_rows.id AND current_rows.kind = missing_rows.kind;

COMMIT;

After we have run the SQL above let's look at the results:

$ tree -h osm.ducklake.files/
[  96]  osm.ducklake.files/
└── [  96]  main
    └── [ 192]  monaco
        ├── [403K]  ducklake-019971a3-b894-7c2c-8e19-21cfcca25b1a.parquet <-- Initial data set here
        ├── [584K]  ducklake-019971a3-e18c-72f2-bf16-b04dd18365a8.parquet <-- Step 1: Updated rows
        ├── [177K]  ducklake-019971a3-e19c-73db-a9e2-74e783ceb4bf-delete.parquet <-- Step 3: Deleted rows
        └── [ 24K]  ducklake-019971a3-f12f-7f69-949d-84e5944cd1fe.parquet <-- Step 2: New rows

3 directories, 4 files

When you look at the parquet file which contains updated rows I assume it should be smaller?

Using the load_osm MACRO we can see that almost all rows were exactly same between both datasets and only 10% of the data changed between:

WITH changed_rows AS (
      SELECT *, COUNT(*) as count
      FROM (
          FROM load_osm('europe/monaco-250101')
          UNION ALL
          FROM load_osm('europe/monaco-250901')
      )
      GROUP BY kind, id, tags
  )
  SELECT
      CASE count
          WHEN 1 THEN 'only-in-other-dataset'
          WHEN 2 THEN 'in-both-datasets'
      END as row_exists,
      COUNT(*) as count
  FROM changed_rows
  GROUP BY row_exists;
┌───────────────────────┬───────┐
│      row_exists       │ count │
│        varchar        │ int64 │
├───────────────────────┼───────┤
│ only-in-other-dataset │  4186 │
│ in-both-datasets      │ 44038 │
└───────────────────────┴───────┘

I would have assumed that the parquet file which contains only the updated rows would have been ~10% of the size from the initial import.

I might be holding it in a wrong way? What is the recommended way to track data like this in ducklake where only small portion of it changes? Ideally I would only want to store the changed rows or even better would be just the columns which changed.

My goal would be to save money for the storage by just storing the delta and still be able to go back in history to certain date. Thanks in advance 🙇 . If this is not a bug but my misunderstading I hope the conversation will at least teach others.

OS:

MacOS 15.6.1 aarch64

DuckDB Version:

v1.4.0 (Andium) b8a06e4a22

DuckLake Version:

45788f0

DuckDB Client:

CLI

Hardware:

Macbook Pro M1 Pro

Full Name:

Onni Hakala

Affiliation:

Freelancer

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[guillesd]

Hi @onnimonni this is indeed not a bug. It is expected behaviour. In general, time traveling (or keeping history) in an efficient way (not keeping a large amount of small files) is not usually the most cost efficient way. Let me explain how each of your operations work:

• Update -> This creates a delete file and an update file with the new records. This is the most efficient way to deal with this write operation and that's why intially we do it like this. Then, to optimize the current view of your data, you can run some operations like rewrite_data_files(https://ducklake.select/docs/stable/duckdb/maintenance/rewrite_data_files) and merge_adjacent_files(https://ducklake.select/docs/stable/duckdb/maintenance/merge_adjacent_files) to make reading more efficient.
• Delete -> This is the same as an update but only generates a delete file by default. Again you can use the function described above to compact the data (only rewrite is necessary).
• Insert -> This generates new files by default that can then compacted with merge_adjacent_files. This would theoretically reduce your data size but, since compaction jobs do create a new snapshot, the old files are not cleaned up to keep "time travel" functionality.

The basic idea here is: keeping history for all changes in your data lake and keeping it read performant means that you just store a tone of data. The whole idea of using a data lake to keep history of tables is that object storage is cheap. In the future we may add some functionality to merge snapshots (for example for compaction jobs). Right now the only way you have to clean up data is to expire snapshots (therefore removing the ability to go back in history to certain versions).

I hope this explains it well. I'm gonna close the issue since this is expected behaviour but feel free to answer here and I will try to clarify as best as I can.

Cheers!

[onnimonni]

Hey!

Thanks a milloin times for teaching me about it and I have now much better understanding of the intended behaviour 🙇 😍!

I'm still wondering if there's room for improvement in cases where data changes very little and I explored using a UPDATE step like this:

-- Update existing rows only if tags column has changed
MERGE INTO osm.monaco old
    USING load_osm('europe/monaco-250901') new
    ON (
        old.kind = new.kind AND
        old.id = new.id AND
        old.tags != new.tags
    )
WHEN MATCHED THEN UPDATE;

Which produces now only 81Kb parquet file instead of the 584Kb because it needs to write only small fraction of the overall data. For this example it's a small difference anyway but I have many datasets in mind where this would be tens of terabytes worth of difference in a year and thus I'm very interested in finding ways to be frugal here.

It's easy to do in this example because the kind and id columns will not change so there's only one column tags which changes.

Let's say there are 20 columns. I can still write query like:

    ON (
        old.kind = new.kind AND
        old.id = new.id AND (
          old.tags != new.tags OR
          old.column2 != new.column2 OR
          old.column3 != new.column3 OR
          ...
          old.columnX != new.columnX
    )

But this gets pretty nasty to maintain when columns change.

Does duckdb have comparison pattern where all columns from 1 row from table A and 1 row from table B could be diffed easily? Something like this:

SELECT kind, id FROM osm.monaco old
JOIN load_osm('europe/monaco-250901') new USING(kind, id) 
WHERE COLUMNS(old.*) IS DISTINCT FROM COLUMNS(new.*);

Or is there a way to efficiently calculate a checksum from the row and compare those checksums?

This way I and others could postpone the rewrite_data_files and merge_adjacent_files much later to the future.

Having a separate new keyword like UPDATE CHANGED ONLY for this behaviour would be absolutely fantastic but I do understand that it's currently out of scope for you:

MERGE INTO osm.monaco
    USING load_osm('europe/monaco-250901')
    USING (kind, id)
WHEN MATCHED THEN UPDATE CHANGED ONLY;

[dkrieger]

Hey!

Thanks a milloin times for teaching me about it and I have now much better understanding of the intended behaviour 🙇 😍!

I'm still wondering if there's room for improvement in cases where data changes very little and I explored using a UPDATE step like this:

-- Update existing rows only if tags column has changed
MERGE INTO osm.monaco old
USING load_osm('europe/monaco-250901') new
ON (
old.kind = new.kind AND
old.id = new.id AND
old.tags != new.tags
)
WHEN MATCHED THEN UPDATE;
Which produces now only 81Kb parquet file instead of the 584Kb because it needs to write only small fraction of the overall data. For this example it's a small difference anyway but I have many datasets in mind where this would be tens of terabytes worth of difference in a year and thus I'm very interested in finding ways to be frugal here.

It's easy to do in this example because the kind and id columns will not change so there's only one column tags which changes.

Let's say there are 20 columns. I can still write query like:

ON (
    old.kind = new.kind AND
    old.id = new.id AND (
      old.tags != new.tags OR
      old.column2 != new.column2 OR
      old.column3 != new.column3 OR
      ...
      old.columnX != new.columnX
)

But this gets pretty nasty to maintain when columns change.

Does duckdb have comparison pattern where all columns from 1 row from table A and 1 row from table B could be diffed easily? Something like this:

SELECT kind, id FROM osm.monaco old
JOIN load_osm('europe/monaco-250901') new USING(kind, id)
WHERE COLUMNS(old.) IS DISTINCT FROM COLUMNS(new.);
Or is there a way to efficiently calculate a checksum from the row and compare those checksums?

This way I and others could postpone the rewrite_data_files and merge_adjacent_files much later to the future.

Having a separate new keyword like UPDATE CHANGED ONLY for this behaviour would be absolutely fantastic but I do understand that it's currently out of scope for you:

MERGE INTO osm.monaco
USING load_osm('europe/monaco-250901')
USING (kind, id)
WHEN MATCHED THEN UPDATE CHANGED ONLY;

I found this issue searching for exactly this^. @onnimonni did you ever find such supporting syntax (ideally without resorting to correlated subqueries etc)? I'll note another reason this would be beneficial: currently the ducklake table_changes function's output is littered with no-op updates for a snapshot unless such conditions are placed on UPDATE

At present, I do something like the following to pre-filter rows present in the target table out of the source table in the USING clause:

MERGE INTO bar AS target
USING ((SELECT * FROM foo) EXCEPT (SELECT * FROM bar)) AS source
ON bar.id = foo.id
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSERT;

[onnimonni]

Hey @dkrieger Did you try these?

WHEN NOT MATCHED BY SOURCE ...
WHEN NOT MATCHED BY TARGET ...

They are not super well documented but they are mentioned here: https://duckdb.org/docs/stable/sql/statements/merge_into

[onnimonni]

I think in the end you need to use the EXCEPT and that's the best I've found so far. But it might be that my brain is just limited.

I have not explored this lately and please teach me (and the rest of the internet) if you find something that's better

[guillesd]

In DuckDB you have a COLUMNS expression, which can take a regex. I.e. SELECT COLUMNS('column\d') FROM t. https://duckdb.org/docs/stable/sql/query_syntax/select