Queries for UPDATE/DELETE in specification?

[markandpathak]

The specification documents queries used on metadata database for reading and writing data with creating table and inserting data, but what about UPDATE and DELETE operations? How metadata is updated in such operations?

[gahtan-syarif]

A DELETE operation in ducklake involves creating a delete file on object storage that specifies which rows of the data file is to be ignored. So the query for updating the metadata is done by updating the ducklake_delete_file table to point to the new delete file, updating the ducklake_table_stats and ducklake_table_column_stats if there are any changes to the table statistics, and updating the ducklake_snapshot and ducklake_snapshot_changes tables to create the new snapshot information.

UPDATE operations in lakehouse systems dont exist as the data files are immutable by design. So an UPDATE operation in ducklake is actually implemented as a DELETE + INSERT operation where you delete the row you want to change and you insert the updated row to the table. So the metadata changes are the same as you would if you performed a DELETE followed by an INSERT operation on the ducklake.

[markandpathak]

Fantastic! Was looking for that information only. I think this details should be added in to the specification documentation. Specification also lacks few important details, like how ACID is guaranteed. Also how small file problem is avoided, and how merge operations are reflected in metadata store. I am assuming this all works similar to iceberg, or are there going to be any difference? (except metadata is in a SQL db)

[gahtan-syarif]

Ducklake works exactly the same as Iceberg when it comes to the data layout (specifically, it uses Iceberg V2 positional delete data format). The difference between Iceberg and ducklake is that ducklake stores its metadata in an sql database. By putting the metadata in an SQL database it solves a few problems that iceberg has:

1. Iceberg experiences performance degradation due to accumulation of small metadata files. Ducklake of course does not experience this since it doesn't store metadata in files.
2. Storing metadata in files means that query engines have to implement the transaction logic themselves to make sure ACID property is maintained, this makes writing to iceberg notoriously difficult to implement (which is why duckdb to this day does not have iceberg write capabilities). By storing the metadata in an sql database, ducklake fixes this problem as query engines can now rely on the database's ACID transaction capabilities instead of implementing their own.
3. Frequent small changes made in iceberg tables leads to the accumulation of small data files which can degrade performance. Ducklake fixes this by caching small changes inside the sql database before flushing it out to the object storage as parquet files. This concept is called data inlining.
4. In iceberg, both metadata and data files are located in object storage while in ducklake, metadata and data are seperated. This allows full data encryption by storing the encryption key for the parquet files in the sql metadata database.
5. Storing all the metadata in a centralized sql database makes certain features easy to implement, such as multi-table transactions, which i believe iceberg has not yet implemented.

[gahtan-syarif]

As for merge operations, its the same as iceberg and other data lakehouse systems where the merge is interpreted as a DELETE operation done on rows with matching keys followed by an INSERT operation on rows with non-matching keys.