DuckLake: DuckDB's SQL-Native Lakehouse Format vs Iceberg & Delta

DuckLake is the lakehouse format that made a heretical observation out loud: the open table formats everyone standardised on already need a database to be correct, so instead of encoding metadata into a maze of files on object storage and then bolting a catalog database on top for the one atomic thing files cannot do, put all the metadata in the SQL database and leave only the Parquet data on storage. The result is a table format whose entire catalog and table metadata — schemas, snapshots, file lists, statistics, partition values — lives as ordinary rows in a transactional SQL database, while the data stays as immutable Parquet on a blob store. It is a genuinely different answer to the same problem Apache Iceberg and Delta Lake solve, and the trade-offs it changes — concurrency, small files, planning latency, operational surface — are exactly what a senior interviewer wants you to reason about.

This guide is the senior-data-engineering walkthrough of that format — what a lakehouse built on a SQL catalog actually is, and how it stacks up against the file-based metadata designs of Iceberg and Delta. It covers why the format exists at all (the mutable-table-on-blob-storage problem and how the incumbents answered it), the three-part DuckDB + catalog-DB + Parquet architecture with snapshots-as-rows and cross-table ACID, a head-to-head on where metadata lives and how commits, small files, and query planning differ across the three formats, how you actually drive it from DuckDB with ATTACH, transactions, and time travel, and how you choose a catalog database, keep it compacted, migrate in and out, and decide when DuckLake is the right pick. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the data engineering system design library →, rehearse the file-and-format work on the data processing practice library →, and sharpen the planning-latency axis with the query optimization practice library →.

---

On this page

• Why DuckLake exists — the lakehouse metadata problem
• DuckLake architecture — catalog, Parquet, snapshots, ACID
• DuckLake vs Iceberg & Delta — metadata, concurrency, small files
• Using DuckLake from DuckDB — attach, transactions, time travel
• Choosing the catalog, migration & interop, and when to pick
• Cheat sheet — DuckLake recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why DuckLake exists — the lakehouse metadata problem

The mutable-table problem — open Parquet is easy; changing it safely on blob storage is not

The one-sentence invariant: DuckLake exists because the hard part of a lakehouse was never storing columns — Parquet solved that a decade ago — it was making a table out of a pile of Parquet files that you can safely append to, update, delete from, and read a consistent version of while other writers are changing it, and the incumbent formats (Iceberg, Delta) solved that with elaborate file-based metadata on object storage plus a catalog database for the one atomic operation blob stores cannot do, so DuckLake's move is to notice that a database was already in the loop and put all the metadata there. Get that framing right in an interview and everything else — concurrency, small files, planning latency — falls out of it.

What a table format has to provide (that raw Parquet does not).

• Atomicity. A write either fully appears or does not; a reader never sees half a commit. On a blob store, where you cannot atomically rename or swap across many files, this is the whole game.
• Snapshot isolation and time travel. A reader pins a consistent version of the table for the length of its scan, and can ask for the table "as of" an earlier version.
• Schema evolution. Add, drop, and rename columns without rewriting the data, tracked so old files still read correctly.
• Efficient planning. Given a query with filters, list only the files that could match — using partition values and per-file statistics — instead of scanning the whole prefix.

How Iceberg and Delta answered it — metadata as files.

• Iceberg. A tree of files on storage: a root metadata.json (holding every snapshot and schema), a manifest list, and manifest files (Avro) that point at the Parquet data. Reading the current table means walking that chain of files.
• Delta Lake. A _delta_log directory of ordered JSON commit files (plus periodic Parquet checkpoints) that you replay to reconstruct the current set of files; updates are copy-on-write with newer deletion-vector support.
• The catch on blob storage. Finding the latest version atomically is hard on object stores with weak consistency, so both formats add a catalog service — backed by a database — that holds the single "current pointer" row and lends its transactionality to the commit.

The DuckLake insight — the database was already there, so use it fully.

• The formats already require a DB. Iceberg's REST/Glue/Nessie catalogs and Delta's Unity/commit-coordinator all lean on a database for the atomic pointer swap. The file-based metadata layers are then redundant complexity around a database that already exists.
• So move all metadata into SQL. DuckLake defines the format as a set of relational tables and pure-SQL transactions over them; the catalog is the metadata store. Data stays as immutable Parquet on storage; there are no Avro or JSON metadata files.
• What that buys. Cross-table transactions (many tables commit atomically), millions of cheap snapshots (a snapshot is a few rows), single-query planning (one catalog query returns the file list), and dramatically fewer small files (no per-commit manifest/log churn).

What interviewers listen for.

• Do you say the incumbents already put a database in the catalog and frame DuckLake as removing the redundant file layer? — senior signal.
• Do you name the four differences that matter — where metadata lives, the concurrency/commit model, small-file behaviour, and catalog/operational complexity? — required answer.
• Do you note that data is still Parquet on storage and only the metadata moves into SQL? — required answer.
• Do you flag the ecosystem trade-off — Iceberg/Delta have broad multi-engine support; DuckLake is newer and DuckDB-centric — rather than declaring a winner? — senior signal.

Worked example — the four-axis format decision table

Detailed explanation. The most useful artifact for a table-format interview is a memorised mapping of axis → how each format behaves. Every senior discussion converges on the same four axes, and being able to place DuckLake, Iceberg, and Delta on each is what separates "I read a blog post" from "I have run this."

• The axes. Where metadata lives; the concurrency/commit model; small-file behaviour; catalog and operational complexity.
• The tension. File-based metadata is engine-agnostic and battle-tested but slow to plan and heavy on small files; SQL-catalog metadata is fast and simple but ties you to a running database and a narrower engine set.
• The rule. Name the axis the question is really probing, then answer for all three formats on that axis.

Question. For each of the four axes, state in one line how DuckLake, Iceberg, and Delta each behave.

Input.

Axis	DuckLake	Iceberg	Delta Lake
Where metadata lives	rows in a SQL catalog DB	metadata.json + Avro manifests on storage	_delta_log JSON + Parquet checkpoints
Concurrency / commit	one SQL transaction in the catalog	optimistic atomic swap via a catalog	log commit + protocol, catalog-coordinated
Small files	none per commit; optional inlining	new manifest(s) per commit	new log entry per commit
Catalog / ops	the catalog is the DB you run	separate catalog service + storage	log on storage (+ Unity for governance)

Code.

Pick-the-axis drill — what the question is really asking
=======================================================

"Why is DuckLake faster to plan a query?"
  -> AXIS: where metadata lives.
     DuckLake: one SQL query to the catalog returns the pruned file list.
     Iceberg/Delta: chase metadata.json -> manifest list -> manifests
                    (or replay the log) across storage = many round-trips.

"Can I commit changes to two tables atomically?"
  -> AXIS: concurrency / commit model.
     DuckLake: yes — one catalog transaction spans many tables.
     Iceberg/Delta: per-table commits; multi-table atomicity is not native.

"My streaming appends create thousands of tiny files."
  -> AXIS: small files.
     DuckLake: no manifest/log churn; small rows can inline into the catalog.
     Iceberg/Delta: each commit adds metadata files; needs compaction/expiry.

"I need Spark, Trino, and Flink to read it."
  -> AXIS: catalog / ecosystem.
     Iceberg/Delta: broad engine support today.
     DuckLake: DuckDB-centric today (data files are Iceberg-compatible).

Step-by-step explanation.

1. Every table-format question maps to one of the four axes. The senior habit is to name the axis first ("this is really a query-planning question") so your answer is structured instead of a grab-bag of features.
2. On the metadata-location axis, DuckLake resolves a read with a single query to the catalog DB, which does schema-, partition-, and statistics-based pruning and hands back a file list; Iceberg and Delta reconstruct that state by reading a chain of files from storage.
3. On the commit axis, DuckLake's commit is one SQL transaction, so multi-table atomicity and rapid concurrent commits come from the database; Iceberg and Delta commit per table via an optimistic swap coordinated by their catalog.
4. On the small-files axis, DuckLake writes no new metadata files per commit (and can inline tiny changes into the catalog), while Iceberg and Delta accumulate manifests/log entries that later need compaction and expiry.
5. On the catalog/ecosystem axis, the trade flips: Iceberg and Delta are read and written by many engines today, whereas DuckLake is DuckDB-centric — though its data and positional-delete files are Iceberg-compatible, which keeps a migration door open.

Output.

Question really probes	Weak answer	Senior answer
Planning latency	"DuckLake is just faster"	"one catalog query vs a file-read chain"
Multi-table change	"use a transaction"	"DuckLake spans tables; Iceberg/Delta don't natively"
Tiny-file explosion	"run compaction"	"no metadata churn + inlining vs manifest/log growth"
Engine choice	"they're all open"	"breadth today favours Iceberg/Delta; DuckLake is DuckDB-first"

Rule of thumb. Answer any table-format question by naming which of the four axes — metadata location, commit model, small files, catalog/ecosystem — it probes, then place all three formats on that axis. DuckLake trades multi-engine breadth for SQL-catalog simplicity, speed, and cross-table transactions.

Worked example — what interviewers actually probe about DuckLake

Detailed explanation. The senior lakehouse interview has a predictable escalation: an ambiguous opener ("we're on Iceberg; why would we look at DuckLake?"), then progressive narrowing to test whether you understand the metadata problem, the commit model, and the operational cost. The candidates who volunteer "the catalog already needed a database" score highest.

• Ambiguous opener. "What even is DuckLake — another table format?"
• Follow-up 1. "Why is it faster to plan a small query?" — probes metadata location.
• Follow-up 2. "What's the catch operationally?" — probes catalog SPOF / running a DB.
• Follow-up 3. "Could we migrate off Iceberg?" — probes interop.

Question. Draft a 5-minute senior answer that pre-empts these follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
What is it	"a DuckDB thing"	"metadata in SQL, data in Parquet — a lakehouse format"
Why exists	"it's newer"	"Iceberg/Delta already need a DB in the catalog"
Why fast	"DuckDB is fast"	"one catalog query plans the read; no file-chain walk"
The catch	"nothing"	"you must run a catalog DB; ecosystem is narrower today"
Migration	"rewrite everything"	"data files are Iceberg-compatible; metadata-only paths"

Code.

Senior DuckLake answer template (5 minutes)
===========================================

Minute 1 — name the format precisely
  "DuckLake is a lakehouse table format: it keeps the DATA as immutable
   Parquet on object storage, but stores ALL the METADATA — schemas,
   snapshots, file lists, stats — as rows in a SQL catalog database."

Minute 2 — why it exists
  "Iceberg and Delta encode metadata into files on storage, but they
   STILL need a database in the catalog for the atomic current-version
   swap. DuckLake's point: the DB is already there, so put all metadata
   in it and drop the redundant Avro/JSON file layers."

Minute 3 — what it buys
  "One catalog query plans a read (no manifest-chain round-trips),
   commits are one SQL transaction (so cross-table atomicity and high
   commit rates), and there's no per-commit small-file churn."

Minute 4 — the honest catch
  "You must operate a catalog DB (Postgres, say) — that's a component to
   make highly available and back up. And the engine ecosystem is
   narrower than Iceberg/Delta today; it's DuckDB-first."

Minute 5 — migration / interop
  "The Parquet data and positional-delete files DuckLake writes are
   Iceberg-compatible, so moving between them can be metadata-only rather
   than a full data rewrite. It's a pick-per-workload decision."

Step-by-step explanation.

1. Minute 1 defines the format on the axis that matters — metadata in SQL, data in Parquet — so the interviewer knows you understand it is a table format, not "a DuckDB feature."
2. Minute 2 delivers the core insight: the incumbents already depend on a database for the atomic pointer, so the file-based metadata is redundant complexity. Saying this unprompted is the strongest single signal.
3. Minute 3 lists the concrete wins — single-query planning, one-transaction cross-table commits, no small-file churn — each tied back to because the metadata is in a transactional SQL database.
4. Minute 4 is the maturity tell: you volunteer the costs (you now run and must protect a catalog DB; the ecosystem is narrower) instead of pretending there is no trade-off.
5. Minute 5 closes on interop, which reframes the decision from "rip and replace" to "pick per workload, migrate cheaply" — the framing a platform owner actually needs.

Output.

Grading criterion	Weak score	Senior score
Defines metadata-in-SQL precisely	rare	mandatory
"The catalog already needed a DB"	rare	senior signal
Names single-query planning	occasional	mandatory
Volunteers the operational catch	rare	senior signal
Frames migration as metadata-only	rare	senior signal

Rule of thumb. The senior DuckLake answer is a 5-minute monologue: define it (metadata in SQL, data in Parquet), explain why it exists (the catalog already needed a DB), list what it buys (planning, commits, small files), volunteer the catch (run a catalog DB, narrower ecosystem), and close on cheap Iceberg-compatible migration. Rehearse it once; deploy it every interview.

Worked example — why "just use Iceberg" is not always the answer

Detailed explanation. A common trap is treating Iceberg as the default and DuckLake as a toy. The senior answer picks by workload: how many engines must read the table, how chatty the writes are, how much operational surface you want, and whether cross-table atomicity matters. Walk the comparison for two teams with the same data.

• The Iceberg-shaped team. A large org running Spark, Trino, and Flink against shared tables, with a managed governance catalog (Unity/Glue/Polaris) already in place.
• The DuckLake-shaped team. A data team standardised on DuckDB/MotherDuck, doing frequent small appends, wanting cheap ops and cross-table transactions, with no need for five query engines.
• The decision. Breadth and existing governance pull toward Iceberg/Delta; DuckDB-centricity, chatty writes, and operational simplicity pull toward DuckLake.

Question. Contrast when Iceberg is the right default and when DuckLake wins, on engine breadth, write pattern, and operational cost.

Input.

Dimension	Favours Iceberg / Delta	Favours DuckLake
Query engines	many (Spark/Trino/Flink)	DuckDB-centric
Write pattern	large batch commits	frequent small appends
Cross-table atomicity	rarely needed	needed
Ops appetite	have a platform team + catalog	want the least moving parts
Governance	managed catalog required	SQL DB you already run

Code.

Decision sketch — same data, two teams
======================================

Team A: 5 engines, petabyte batch, existing Unity/Glue catalog
  -> Iceberg (or Delta). Breadth + managed governance dominate.
     DuckLake's single-engine focus would be a step back here.

Team B: DuckDB/MotherDuck, minutely appends, 2 tables kept in sync,
        one small Postgres already running
  -> DuckLake. One catalog query plans reads; minutely appends don't
     spawn manifest churn; the two tables commit atomically; ops is
     "a Postgres you already run" instead of a catalog service + manifests.

The tell of a weak answer: "Iceberg is the industry standard, so Iceberg."
The tell of a senior answer: "which engines, how chatty, how much ops — then choose."

Step-by-step explanation.

1. Team A's constraint is engine breadth plus an existing managed governance catalog; Iceberg/Delta's mature multi-engine support and catalog integrations dominate, and DuckLake's DuckDB focus would narrow their access.
2. Team B's constraint is write chattiness and operational simplicity: minutely appends would pile up Iceberg manifests or Delta log entries needing compaction, while DuckLake commits them as rows with no metadata-file churn.
3. Team B also needs cross-table atomicity (two tables kept consistent), which DuckLake gives natively via one catalog transaction and neither incumbent provides without extra machinery.
4. The operational surface differs sharply: Team B already runs a small Postgres, so DuckLake adds essentially no new component, whereas Iceberg would add a catalog service and a metadata-file lifecycle to manage.
5. The senior move is not "Iceberg is standard" but "match the format to the workload": breadth and governance → Iceberg/Delta; DuckDB-centric, chatty, ops-light, cross-table → DuckLake. It is a per-workload decision, not a fashion.

Output.

Constraint	Right pick	Wrong pick (common mistake)
Five engines must read it	Iceberg / Delta	DuckLake (narrow ecosystem)
Minutely small appends	DuckLake	file-format with manifest churn, uncompacted
Two tables must stay atomic	DuckLake	per-table Iceberg commits, hope for the best
Already have Unity/Glue governance	Iceberg / Delta	rebuild governance on a new format

Rule of thumb. Do not default to Iceberg reflexively. Pick by workload: engine breadth and managed governance favour Iceberg/Delta; DuckDB-centric access, chatty small writes, cross-table atomicity, and a thirst for fewer moving parts favour DuckLake. It is a per-workload decision, and the formats can even interoperate.

Senior interview question on the lakehouse metadata problem

A senior interviewer might open with: "We store analytics tables as Parquet on S3 and coordinate changes with Iceberg plus a catalog. Someone proposes DuckLake. Explain to the team what problem table formats solve on blob storage, why Iceberg and Delta ended up needing a catalog database anyway, what DuckLake changes by moving all metadata into SQL, and the honest trade-offs — so we can decide, not just follow a trend."

Solution Using the metadata-in-SQL framing, the four axes, and a workload-fit decision

-- 1. The problem: a "table" over Parquet on blob storage needs atomicity,
--    snapshot isolation, schema evolution, and efficient file pruning —
--    none of which raw Parquet provides.

-- 2. Incumbent answer (Iceberg/Delta): encode metadata as files on storage...
Iceberg:  metadata.json -> manifest list -> manifest files (Avro) -> Parquet
Delta:    _delta_log/00001.json, 00002.json, ... (+ checkpoint.parquet) -> Parquet
--    ...but the atomic "current version" swap is unsafe on blob stores,
--    so BOTH add a catalog backed by a DATABASE for that one atomic step.

-- 3. DuckLake's answer: the metadata IS relational tables in that database.
--    (Illustrative catalog schema — the format is defined as SQL tables.)
--    snapshots, tables, files, stats, partitions — all rows, all transactional.
SELECT snapshot_id, snapshot_time
FROM ducklake_snapshot;                    -- every version is a row

SELECT table_name, path
FROM ducklake_data_file                    -- the file list, pruned by SQL
WHERE table_id = 42;                        -- one query returns what to read

# 4. The four axes, decided per workload.
where_metadata:  SQL rows (DuckLake) | Avro manifests (Iceberg) | JSON log (Delta)
commit_model:    one catalog txn, cross-table (DuckLake) | per-table optimistic swap
small_files:     none per commit + inlining (DuckLake) | manifest/log churn (others)
ecosystem:       DuckDB-first (DuckLake) | many engines (Iceberg/Delta)

decision:
  many engines / managed governance  -> Iceberg / Delta
  DuckDB-centric / chatty / cross-table / ops-light -> DuckLake
  (data files are Iceberg-compatible, so migration can be metadata-only)

Step-by-step trace.

Layer	Iceberg / Delta	DuckLake
Data	Parquet on storage	Parquet on storage (same)
Metadata	files on storage (Avro/JSON)	rows in a SQL catalog DB
Atomic commit	catalog DB swaps a pointer	catalog DB runs one transaction
Plan a read	walk file chain / replay log	one catalog query
Multi-table	not native	one transaction spans tables
Redundancy	file metadata and a DB	just the DB

Walking the team through it: raw Parquet is not a table, so you need a format for atomicity, isolation, evolution, and pruning; Iceberg and Delta provide that with file-based metadata but still lean on a catalog database for the atomic version swap; DuckLake observes that the database is therefore already mandatory and moves all metadata into it, so the read path is a single catalog query, commits are one transaction (spanning tables), and there is no per-commit small-file churn — at the cost of running that catalog DB and a narrower engine ecosystem today.

Output:

Concern	Iceberg + catalog	DuckLake
Components to run	catalog service + storage + metadata files	one SQL catalog DB + storage
Read planning	multiple file round-trips	one catalog query
Small-append cost	new manifest(s) per commit	rows (optionally inlined)
Cross-table atomic change	not native	native (one transaction)
Engine breadth	broad	DuckDB-centric (Iceberg-compatible files)

Why this works — concept by concept:

• Metadata in SQL, data in Parquet — the format keeps bulk data as immutable Parquet on cheap object storage while moving only the metadata into a transactional database, so you get storage economics and database transactionality at once.
• The catalog was already a database — Iceberg and Delta already require a DB in the catalog for the atomic version swap, so DuckLake removes the redundant file-metadata layer rather than adding a new dependency.
• Single-query planning — because the file list, partitions, and statistics are relational rows, one catalog query does all the pruning and returns the files to read, eliminating the sequential file-read chain that bounds Iceberg/Delta read latency.
• Cross-table, low-churn commits — a commit is one SQL transaction, so many tables can change atomically and small appends add rows (or inline) instead of spawning manifest/log files that later need compaction.
• Cost — you operate one catalog database and store Parquet, versus a catalog service plus a growing tree of metadata files. The eliminated cost is the file-metadata lifecycle and multi-round-trip planning — O(1) catalog query versus O(files) reads to reconstruct state — traded against a narrower engine ecosystem today.

Design
Topic — design
Design problems on lakehouse and table-format architecture

Practice →

Data processing Topic — data-processing Data processing problems on Parquet and file layouts

Practice →

---

2. DuckLake architecture — catalog, Parquet, snapshots, ACID

A SQL catalog over immutable Parquet; a commit is one transaction, a snapshot is a few rows

The mental model in one line: DuckLake splits a lakehouse into three clean parts — a compute engine (DuckDB) that reads and writes, a transactional SQL catalog database that holds all metadata as relational tables, and immutable Parquet data files on object storage — so a write is "stage the Parquet files, then run one SQL transaction in the catalog," a snapshot is just a few new rows, and ACID comes for free from the catalog database's own transactions and referential integrity, including cross-table atomicity that file-based formats cannot natively offer. Everything else about the format is a consequence of that three-part split.

The three parts and their jobs.

• Compute (DuckDB + the ducklake extension). Plans and executes queries, stages Parquet files to storage on write, and issues the metadata transaction. Compute is stateless and horizontally scalable — many nodes can talk to the same catalog and storage.
• Catalog database. Any ACID SQL database with primary keys — a local DuckDB file for dev, or Postgres/MySQL for a shared, multi-writer deployment. It stores the entire metadata as tables and lends its transactions to every commit.
• Object storage. Holds the immutable Parquet data files (and positional-delete files). DuckLake never modifies a file in place or reuses a filename, which is what makes storage-side consistency a non-issue.

What the catalog actually stores.

• ducklake_snapshot. One row per version of the whole lake, with a snapshot id and timestamp — this is what time travel reads.
• ducklake_table / ducklake_schema / ducklake_column. The logical objects and their evolving schema, versioned so old snapshots read with their schema.
• ducklake_data_file (+ delete-file and stats tables). The list of Parquet files per table, with per-file and per-column statistics and partition values — this is the pruning index used to plan reads.
• Consequence. Because these are relational tables with foreign keys, the database enforces consistency (the "C" in ACID) — no duplicate snapshot ids, no orphaned file rows within a transaction.

How a write commits.

• Step 1 — stage data. Compute writes any new Parquet files to storage (immutable, new names). No metadata is visible yet.
• Step 2 — one transaction. Compute runs a single SQL transaction that inserts a new ducklake_snapshot row and the ducklake_data_file rows (and removes/marks superseded files). Commit is atomic in the catalog DB.
• Snapshots are cheap. A snapshot is a few rows, and a snapshot can even reference parts of a Parquet file — so a lake can hold millions of snapshots without pruning them proactively.
• Small changes can inline. With data inlining, tiny inserts are stored directly in the catalog and flushed to Parquet later — so a trickle of small writes need not create any files at all.

The failure modes senior engineers pre-empt.

• Treating the catalog as a bottleneck by default. The catalog runs only metadata transactions — orders of magnitude smaller than the data — so a modest Postgres handles thousands of commits per second. Mitigation: size the catalog for metadata QPS, not data volume; scale compute independently.
• Skipping maintenance. Immutable files plus updates/deletes accumulate small and superseded files over time. Mitigation: schedule compaction (merge_adjacent_files), snapshot expiry, and orphan-file cleanup.
• Assuming the catalog is disposable. The catalog is the source of truth for metadata; lose it and the Parquet files are just anonymous bytes. Mitigation: make the catalog highly available and back it up like the primary database it is.

Common interview probes on DuckLake architecture.

• "Where does ACID come from?" — the catalog database's own transactions and foreign-key integrity; a commit is one SQL transaction.
• "What is a snapshot physically?" — a few rows in ducklake_snapshot (and referenced file rows), not a file.
• "How does it plan a read?" — one query against the catalog prunes by partition/statistics and returns the file list.
• "Can two tables change atomically?" — yes; one catalog transaction can span tables.

Worked example — trace the SQL a single INSERT emits

Detailed explanation. The clearest way to see the architecture is to follow what happens in the catalog when you insert into an otherwise empty table. Data goes to Parquet; metadata becomes rows; the whole thing is one transaction.

• The action. INSERT INTO orders VALUES (...) on a DuckLake-attached table.
• On storage. One new immutable Parquet file is staged.
• In the catalog. A new snapshot row plus a data-file row (with stats), committed atomically.

Question. Describe, as catalog SQL, what a single-row insert produces — the staged file and the metadata transaction.

Input.

Piece	Value
Statement	INSERT INTO orders VALUES (1, 'EU', 4200)
Storage effect	one new part-*.parquet (immutable)
Catalog effect	1 snapshot row + 1 data-file row (+ stats)
Atomicity	one transaction in the catalog DB

Code.

-- What you write (ordinary SQL against the attached DuckLake):
INSERT INTO orders VALUES (1, 'EU', 4200);

-- What DuckLake does under the hood — ILLUSTRATIVE catalog transaction:
BEGIN;
  -- (a) a new version of the whole lake
  INSERT INTO ducklake_snapshot (snapshot_id, snapshot_time)
  VALUES (7, now());

  -- (b) the Parquet file that was just staged to storage, with pruning stats
  INSERT INTO ducklake_data_file
      (data_file_id, table_id, path, record_count, file_size_bytes, snapshot_id)
  VALUES (91, 42, 's3://bucket/data/orders/part-0007.parquet', 1, 812, 7);

  -- (c) per-column min/max so future reads can prune this file
  INSERT INTO ducklake_file_column_stats
      (data_file_id, column_id, min_value, max_value, null_count)
  VALUES (91, 2, 'EU', 'EU', 0);         -- region column
COMMIT;

Step-by-step explanation.

1. The user statement is ordinary SQL — DuckLake tables behave like tables. The lakehouse mechanics are hidden behind the DuckDB extension.
2. Before the transaction, compute stages the row into a new, immutable Parquet file on storage. Nothing about the table has changed yet because no metadata references that file.
3. Inside one catalog transaction, (a) records a new snapshot (a new version of the lake), (b) registers the staged Parquet file against the table with its size and record count, and (c) records per-column min/max so later queries can prune this file without opening it.
4. The COMMIT is the atomic moment: until it lands, no reader sees the new file; after it lands, every reader planning against snapshot 7 (or later) sees it. Atomicity is the database's, not a fragile blob-store rename.
5. Because this is all rows in a database with foreign keys, the catalog cannot end up with a data-file row pointing at a non-existent snapshot or a duplicate snapshot id — consistency is enforced structurally.

Output.

Effect	Where	Detail
New Parquet file	object storage	immutable, new filename
New snapshot	ducklake_snapshot	one row (version 7)
File registered	ducklake_data_file	path + size + count
Pruning stats	ducklake_file_column_stats	min/max/nulls per column
Visibility	atomic at COMMIT	all-or-nothing

Rule of thumb. A DuckLake write is "stage immutable Parquet, then one catalog transaction that inserts a snapshot row and file rows with stats." The data lives on storage; the truth about the table lives in the catalog — which is why atomicity, consistency, and pruning are all just database properties.

Worked example — snapshots and time travel via the catalog

Detailed explanation. Because every version is a snapshot row, listing history and querying the past are ordinary reads. Use ducklake_snapshots() to see versions and AT (VERSION => n) to read one.

• List. ducklake_snapshots('my_lake') returns every version with its id and time.
• Read a version. ... AT (VERSION => 3) or AT (TIMESTAMP => '...').
• Why it is cheap. A snapshot is rows; keeping thousands costs almost nothing.

Question. Show how to list snapshots and read a table as of an earlier version, and explain why DuckLake can keep so many snapshots.

Input.

Operation	Syntax
List versions	FROM ducklake_snapshots('my_lake')
Read by version	FROM orders AT (VERSION => 3)
Read by time	FROM orders AT (TIMESTAMP => '2026-08-01')
Cost of a snapshot	a few catalog rows

Code.

-- Every version of the lake is a row — listing history is a plain query.
FROM ducklake_snapshots('my_lake');
-- snapshot_id | snapshot_time        | schema_version | changes
-- 5           | 2026-08-25 09:00:00  | 3              | {inserted into orders}
-- 6           | 2026-08-25 09:05:00  | 3              | {inserted into orders}
-- 7           | 2026-08-25 09:07:00  | 3              | {inserted into orders}

-- Time travel by version id: read the table AS OF snapshot 5.
SELECT count(*) FROM orders AT (VERSION => 5);

-- Time travel by timestamp: DuckLake resolves it to the snapshot in effect then.
SELECT count(*) FROM orders AT (TIMESTAMP => '2026-08-25 09:05:30');

-- You can even diff two versions of a metric in one query.
SELECT
  (SELECT sum(amount) FROM orders AT (VERSION => 7)) AS now_total,
  (SELECT sum(amount) FROM orders AT (VERSION => 5)) AS earlier_total;

Step-by-step explanation.

1. ducklake_snapshots('my_lake') is a table function that reads ducklake_snapshot, so browsing the full history of the lake is just a SELECT — no log replay, no file walking.
2. AT (VERSION => 5) tells the planner to resolve the table's file list as recorded in snapshot 5, so the read sees exactly the files that were current then — snapshot isolation by construction.
3. AT (TIMESTAMP => '...') resolves the timestamp to whichever snapshot was in effect at that moment, which is convenient when you know "around 9:05" but not the version id.
4. Diffing two versions is a single query because both versions are addressable at once — you do not have to restore anything; you just scan two file lists the catalog already knows.
5. Snapshots are cheap because each is a handful of rows and can even reference parts of a Parquet file, so retaining thousands (or millions) of versions is a metadata cost, not a data-duplication cost — you expire them for storage hygiene, not because the catalog forces you to.

Output.

Query	Reads	Cost
ducklake_snapshots(...)	ducklake_snapshot rows	one catalog query
AT (VERSION => 5)	file list as of v5	one catalog query + Parquet scan
AT (TIMESTAMP => ...)	resolves to a snapshot	same
keep N snapshots	N sets of rows	negligible metadata

Rule of thumb. Time travel in DuckLake is a catalog read: ducklake_snapshots() lists versions, AT (VERSION => n) / AT (TIMESTAMP => ...) reads one. Snapshots are rows (sometimes referencing parts of a file), so keep as many as you like and expire them for storage hygiene, not because the format struggles with history.

Worked example — a cross-table atomic transaction

Detailed explanation. The feature file-based formats cannot natively match is multi-table atomicity. Because a DuckLake commit is one catalog transaction, you can change several tables and have them appear together or not at all. Move a row from staging to orders atomically.

• The need. Two tables must stay consistent — a reader must never see the delete without the insert.
• The mechanism. BEGIN; ... ; COMMIT; — one catalog transaction spanning both tables.
• The guarantee. One snapshot covers both changes.

Question. Write a transaction that inserts into orders and deletes from staging atomically, and explain why file-based formats struggle to match it.

Input.

Aspect	Single-table format	DuckLake
Change 2 tables atomically	two separate commits	one transaction
Reader sees a half-state	possible between commits	impossible
Snapshots produced	one per table	one covering both
Rollback	manual/compensating	ROLLBACK

Code.

-- Promote a staged order into the live table — both tables, one snapshot.
BEGIN;
  INSERT INTO orders  SELECT * FROM staging WHERE id = 1001;
  DELETE FROM staging WHERE id = 1001;
COMMIT;   -- ONE catalog transaction -> ONE snapshot covering BOTH tables

-- If anything fails mid-way, the whole thing rolls back:
BEGIN;
  INSERT INTO orders  SELECT * FROM staging WHERE id = 2002;
  -- ... a later statement errors ...
ROLLBACK;  -- neither table changed; no snapshot is created

Step-by-step explanation.

1. The BEGIN ... COMMIT block groups both statements into one catalog transaction, so DuckLake stages any Parquet files, then commits one snapshot that records both the insert into orders and the delete from staging.
2. Because both changes land in a single snapshot, there is no window in which a reader could see the orders insert without the staging delete (or vice versa) — cross-table snapshot isolation.
3. If any statement in the block fails, ROLLBACK (or the error) discards the whole transaction: no snapshot is created, and neither table is touched — the same all-or-nothing guarantee a normal SQL database gives.
4. A single-table format (Iceberg/Delta) commits each table independently, so promoting the row is two commits with a visible half-state between them, and coordinating atomicity across them requires an external mechanism the format does not provide.
5. This is a direct payoff of the architecture: because all metadata is in one transactional database, a transaction naturally spans as many tables as you like — the database was already good at exactly this.

Output.

Step	orders	staging	Snapshot
before	no 1001	has 1001	v10
within txn	(staged)	(marked)	—
after COMMIT	has 1001	no 1001	v11 (both)
on ROLLBACK	unchanged	unchanged	none

Rule of thumb. Wrap multi-table changes in BEGIN ... COMMIT and DuckLake commits them as one snapshot — atomic across tables, invisible until commit, fully rolled back on failure. This cross-table atomicity is a native consequence of keeping all metadata in one transactional catalog, and it is the thing single-table file formats cannot match without extra machinery.

Senior interview question on DuckLake architecture and ACID

A senior interviewer might ask: "Walk me through DuckLake's architecture end to end. Where does the data live versus the metadata, what exactly happens on storage and in the catalog when I commit a write, how does the format give me ACID and time travel, how can two tables change atomically, and what operational responsibilities does putting all metadata in a database create?"

Solution Using the three-part split, a one-transaction commit, and catalog-backed ACID

-- 1. The three-part split.
--    compute (DuckDB + ducklake ext)  -> plans/executes, stages Parquet, commits metadata
--    catalog DB (DuckDB/Postgres/...)  -> ALL metadata as rows; lends its transactions
--    object storage                    -> immutable Parquet data + delete files

-- 2. What a commit does: stage files, then ONE catalog transaction.
BEGIN;
  INSERT INTO ducklake_snapshot (snapshot_id, snapshot_time) VALUES (12, now());
  INSERT INTO ducklake_data_file (data_file_id, table_id, path, record_count, snapshot_id)
  VALUES (200, 42, 's3://bucket/data/orders/part-0012.parquet', 5000, 12);
  -- + column stats rows for pruning
COMMIT;               -- atomic; readers see v12 all-at-once

-- 3. ACID + time travel + cross-table, all from the catalog DB.
FROM ducklake_snapshots('lake');            -- history is a query
SELECT * FROM orders AT (VERSION => 11);    -- snapshot isolation / time travel
BEGIN;                                       -- cross-table atomicity
  INSERT INTO orders SELECT * FROM staging WHERE id = 1;
  DELETE FROM staging WHERE id = 1;
COMMIT;                                       -- one snapshot, both tables

# 4. Operational responsibilities the design creates.
catalog_is_source_of_truth: back it up + make it HA (lose it -> anonymous Parquet)
size_for_metadata_qps:      commits are small metadata txns, not data volume
maintenance:                merge_adjacent_files (compact) + expire_snapshots + cleanup

Step-by-step trace.

Layer	Component	Responsibility
Compute	DuckDB + ducklake ext	plan, stage Parquet, issue metadata txn
Metadata	catalog DB (SQL)	snapshots, tables, files, stats as rows
Data	object storage	immutable Parquet + delete files
Atomicity	catalog transaction	commit is all-or-nothing
History	ducklake_snapshot rows	time travel = a catalog read
Cross-table	one transaction	many tables, one snapshot

Walking it end to end: data is Parquet on storage and metadata is rows in the catalog; a write stages immutable Parquet then runs one catalog transaction inserting a snapshot and its file rows, so the commit is atomic and consistent by the database's own guarantees; time travel reads an older snapshot's file list; a single transaction can change several tables and produce one snapshot; and because the catalog is now the source of truth for the table, you must run it highly available and back it up, size it for metadata QPS, and schedule compaction, snapshot expiry, and orphan cleanup.

Output:

Property	How DuckLake provides it
Atomicity	one catalog transaction per commit
Consistency	foreign keys / PKs in the catalog schema
Isolation	read a pinned snapshot's file list
Durability	committed catalog rows + durable Parquet
Time travel	AT (VERSION/TIMESTAMP) over snapshot rows
Cross-table atomicity	a transaction spanning tables

Why this works — concept by concept:

• Three-part split — separating stateless compute, a transactional catalog DB, and immutable Parquet lets each scale and be reasoned about independently, and makes "stage files then commit metadata" the whole write protocol.
• One-transaction commit — because a commit is a single SQL transaction inserting a snapshot and file rows, atomicity and consistency are the database's, not a fragile multi-file blob-store dance.
• Snapshots as rows — a version is a handful of rows (optionally referencing parts of a file), so history is a query and retaining millions of snapshots is a metadata cost, not data duplication.
• Catalog-backed ACID and cross-table atomicity — the same transaction that gives ACID naturally spans multiple tables, delivering multi-table atomicity that single-table file formats cannot match natively.
• Cost — you run one catalog database sized for small metadata transactions plus immutable Parquet on cheap storage, versus a catalog service and a growing metadata-file tree; the trade is that the catalog becomes a source of truth you must make HA, back up, and maintain — O(1) metadata commit versus O(files) metadata churn.

Design
Topic — design
Design problems on storage engines and snapshot isolation

Practice →

Data validation Topic — data-validation Data validation problems on ACID and consistency guarantees

Practice →

---

3. DuckLake vs Iceberg & Delta — metadata, concurrency, small files

One catalog query vs a file-read chain; one transaction vs an atomic pointer swap

The mental model in one line: the difference between DuckLake, Iceberg, and Delta Lake is entirely about where the metadata lives and everything that follows from it — DuckLake keeps the file list and statistics as rows in a SQL catalog so planning a read is one query and committing is one transaction, Iceberg keeps them in a chain of metadata.json and Avro manifest files so planning walks that chain and commits swap a pointer, and Delta keeps them in a _delta_log of JSON commits (plus Parquet checkpoints) that a reader replays — which is why they diverge on planning latency, concurrent-commit behaviour, and small-file amplification even though all three store the same Parquet data underneath. Compare the metadata path, not the data, and the differences become obvious.

Where the metadata lives.

• DuckLake. Relational rows in a SQL catalog DB: snapshots, tables, files, stats, partitions. Reading the current table is a query.
• Iceberg. A metadata.json root (every snapshot + schema) → a manifest list → manifest files (Avro) → Parquet. A separate catalog (REST/Glue/Nessie/Hive) holds the atomic current-metadata pointer.
• Delta. A _delta_log directory of ordered JSON commit files, periodically compacted into Parquet checkpoints, that you replay to compute the live file set; a catalog (e.g. Unity) adds governance.

Query planning — the round-trips.

• DuckLake. One catalog query does schema/partition/statistics pruning and returns the exact files to read — no storage round-trips for metadata.
• Iceberg. Read metadata.json, then the manifest list, then one or more manifest files, then the data — several sequential object-store requests before the first data byte.
• Delta. Read the latest checkpoint plus any newer JSON commits to reconstruct the file set — again multiple reads, though checkpoints bound how many.

Concurrency and commits.

• DuckLake. A commit is one catalog transaction; the database de-conflicts concurrent writers, so commit rates are bounded by "how many transactions the catalog can do" — thousands per second on ordinary Postgres.
• Iceberg / Delta. Optimistic concurrency: a writer prepares new metadata and atomically swaps the pointer; conflicting writers must retry. Time spent in the critical path (writing/reading metadata files) widens the conflict window.

Small files and maintenance.

• DuckLake. No new manifest or log file per commit; a small change is a few rows, and tiny inserts can inline into the catalog — so metadata small-file churn is essentially eliminated. Data files still need periodic compaction.
• Iceberg / Delta. Each commit writes metadata (manifests or a log entry); frequent small writes create many metadata files, so you schedule compaction, manifest rewrites/checkpointing, and snapshot expiry to keep planning fast.

Ecosystem and interop.

• Breadth. Iceberg and Delta are read/written by many engines (Spark, Trino, Flink, Snowflake, etc.) today; DuckLake is DuckDB-centric and newer.
• Interop. DuckLake's data and positional-delete files are Iceberg-compatible, enabling metadata-only migration paths in and out — you are not trapped by picking it.

Common interview probes on the comparison.

• "Why is DuckLake's planning faster?" — one catalog query vs a chain of metadata-file reads.
• "How do commits differ?" — one DB transaction vs an optimistic atomic pointer swap with retries.
• "Which explodes small files?" — Iceberg/Delta add metadata per commit; DuckLake does not (and can inline).
• "Why might I still choose Iceberg?" — multi-engine breadth and managed governance catalogs.

Worked example — count the round-trips to plan a read

Detailed explanation. The cleanest way to feel the metadata-location difference is to count the sequential requests each format needs before it can read the first data byte. Plan a selective query (WHERE region = 'EU') in each.

• DuckLake. One catalog query returns the pruned file list.
• Iceberg. metadata.json → manifest list → manifest(s) → data.
• Delta. checkpoint + newer commits → data.

Question. For a selective read, count the metadata round-trips each format makes before scanning data, and explain the latency consequence.

Input.

Format	Metadata reads before data	Bounded by
DuckLake	1 (catalog query)	catalog latency
Iceberg	3+ (root → list → manifests)	number of manifests
Delta	1 checkpoint + K commits	commits since checkpoint

Code.

Plan `SELECT ... FROM orders WHERE region = 'EU'`  — metadata path only
=======================================================================

DuckLake:
  (1) one SQL query to the catalog:
      SELECT path FROM ducklake_data_file f
      JOIN ducklake_file_column_stats s USING (data_file_id)
      WHERE table_id = 42 AND s.column = 'region'
        AND 'EU' BETWEEN s.min_value AND s.max_value;
  -> returns the exact Parquet files. THEN read data. (1 metadata round-trip)

Iceberg:
  (1) GET current metadata.json      (from the catalog pointer)
  (2) GET manifest list
  (3) GET manifest file(s)           (prune by partition/stats here)
  -> returns data files. THEN read data. (3+ sequential object-store reads)

Delta:
  (1) GET latest _delta_log checkpoint.parquet
  (2) GET any newer 000NN.json commits, replay to get the live file set
  -> returns data files. THEN read data. (1 + K reads)

Step-by-step explanation.

1. DuckLake pushes all pruning into one SQL query: the catalog joins the file list to per-column stats and returns only files whose [min,max] for region could contain 'EU' — a single round-trip whose latency is the catalog's.
2. Iceberg must read the root metadata.json, then the manifest list, then the relevant manifest files to apply partition and column-stats pruning — each a separate sequential object-store request, so latency grows with the metadata tree and is exposed to storage throttling/retries.
3. Delta reads the most recent checkpoint and replays any commits added since it to compute the current set of files; checkpoints bound K, but you still pay a checkpoint read plus the tail of the log.
4. The consequence is a floor on how fast a small query can start: DuckLake's floor is one catalog query; Iceberg's and Delta's floors are several storage reads, which is why they cache metadata aggressively and why tiny queries feel disproportionately slow without it.
5. The same property makes DuckLake commits cheaper in the critical path: less time spent doing metadata file IO means a narrower window for concurrent-write conflicts, which ties directly into the concurrency comparison.

Output.

Format	Metadata round-trips	Latency floor for a tiny query
DuckLake	1	one catalog query
Iceberg	3+	several sequential storage reads
Delta	1 + K commits	checkpoint + log tail
effect	fewer = faster start	DuckLake starts soonest

Rule of thumb. Count the metadata round-trips before the first data byte: DuckLake needs one catalog query, Iceberg needs a metadata.json → manifest-list → manifest walk, and Delta needs a checkpoint plus log replay. Fewer round-trips means a lower latency floor for small queries and a narrower conflict window for commits.

Worked example — concurrent-writer conflict behaviour

Detailed explanation. Two writers append to the same table at the same time. How each format resolves it reveals the commit model. DuckLake leans on the catalog DB's transactions; Iceberg/Delta use optimistic concurrency with retries.

• DuckLake. Both commits are catalog transactions; the DB serialises them, so both succeed quickly (append-append does not truly conflict).
• Iceberg / Delta. Each writer prepares metadata and tries to swap the pointer / append the log; the loser detects the change and retries.
• The lever. Less critical-path work → fewer/cheaper conflicts.

Question. Two concurrent appends hit one table. Describe how DuckLake versus Iceberg/Delta resolve it and why DuckLake conflicts less.

Input.

Aspect	DuckLake	Iceberg / Delta
Commit unit	catalog transaction	metadata swap / log append
Conflict handling	DB serialises transactions	optimistic; loser retries
Critical-path work	one small txn	write+read metadata files
Append-append	commits fine	may retry under contention

Code.

Two writers append to `events` at the same instant
===================================================

DuckLake:
  W1: BEGIN; INSERT snapshot+file rows; COMMIT;   \  the catalog DB
  W2: BEGIN; INSERT snapshot+file rows; COMMIT;   /  serialises these two
  -> both land as consecutive snapshots. Append-append rarely conflicts;
     throughput ~ "transactions/sec the catalog can do" (thousands on Postgres).

Iceberg / Delta:
  W1: stage data -> prepare new metadata -> atomically swap pointer  (wins)
  W2: stage data -> prepare new metadata -> swap FAILS (pointer moved)
      -> re-read latest metadata, re-apply, retry the swap
  -> correctness preserved, but time in the critical path (metadata IO)
     widens the window where W2 must retry.

Step-by-step explanation.

1. In DuckLake, each append is a short catalog transaction; the database serialises W1 and W2 into consecutive snapshots. Two appends do not logically conflict (they add different files), so both commit without a retry loop.
2. Commit throughput is therefore "how many transactions the catalog can commit per second" — thousands on an ordinary Postgres — and because the critical-path work is one tiny transaction, the window for genuine conflicts (e.g. compaction vs append) is small.
3. In Iceberg/Delta, each writer stages data, prepares new metadata files, and then atomically swaps the catalog pointer (or appends to the log). The first to swap wins.
4. The loser detects that the current metadata moved, re-reads the latest state, re-applies its change, and retries the swap. This is correct, but the time spent writing and reading metadata files is the conflict window — more critical-path IO means more retries under contention.
5. The upshot is not "Iceberg/Delta are broken" but "DuckLake's commit is cheaper in the critical path," so it sustains higher concurrent-commit rates before conflicts bite — a direct consequence of metadata being one transactional query instead of a set of files to write and re-read.

Output.

Scenario	DuckLake	Iceberg / Delta
2 concurrent appends	both commit (serialised)	one retries
commit rate ceiling	catalog txn/sec	limited by metadata IO
conflict window	one small txn	write+read metadata
compaction vs append	short overlap	wider overlap

Rule of thumb. DuckLake resolves concurrency with the catalog database's transactions, so append-append rarely conflicts and commit throughput is the catalog's transactions-per-second; Iceberg/Delta use optimistic pointer swaps where the loser retries, and the metadata IO in the critical path widens the conflict window. Less critical-path work means fewer, cheaper conflicts.

Worked example — small-append file amplification

Detailed explanation. Streaming or minutely appends are where file-based metadata hurts: every commit writes metadata. Compare what 1,000 small appends produce in each format.

• DuckLake. 1,000 snapshots = rows; optionally zero new data files if changes inline; data files compacted later.
• Iceberg. Each commit adds manifest(s) and a manifest list entry; metadata files accumulate.
• Delta. Each commit adds a JSON log entry; periodic checkpoints compact them.

Question. For 1,000 small appends, compare the metadata-file amplification and the maintenance each format then requires.

Input.

Format	Per-commit metadata	After 1,000 appends	Maintenance
DuckLake	catalog rows (+ optional inline)	rows; few/no new files	compact data files, expire snapshots
Iceberg	manifest(s) + list	many metadata files	compact data + rewrite manifests + expire
Delta	one JSON log commit	1,000 log entries	checkpoint + compact + vacuum

Code.

1,000 minutely appends to one table
====================================

DuckLake:
  metadata: 1,000 snapshot rows (+ file rows) in the catalog.
  data:     with data-inlining, tiny rows can live in the catalog and be
            flushed to Parquet in batches -> possibly FAR fewer than 1,000 files.
  cleanup:  merge_adjacent_files (compact) + expire_snapshots when desired.

Iceberg:
  metadata: ~1,000 commits, each adding manifest file(s) + a manifest-list.
  data:     1,000 small Parquet files unless you compact.
  cleanup:  rewrite_data_files (compact) + rewrite_manifests + expire_snapshots
            + remove_orphan_files.

Delta:
  metadata: 1,000 _delta_log/000NN.json entries; checkpoints every ~10.
  data:     1,000 small Parquet files unless you OPTIMIZE.
  cleanup:  OPTIMIZE (compact) + checkpoint + VACUUM.

Step-by-step explanation.

1. In DuckLake, 1,000 appends are 1,000 snapshot rows plus file rows — cheap metadata. Crucially, with data inlining the tiny inserts can be stored in the catalog and flushed to Parquet in batches, so you may write far fewer than 1,000 data files.
2. In Iceberg, each of the 1,000 commits writes new manifest file(s) and a manifest-list; the metadata tree grows with the commit count, and read planning slows until you rewrite manifests and expire snapshots.
3. In Delta, each commit appends a JSON log entry; checkpoints periodically fold them into Parquet, but you still accumulate 1,000 commit files and 1,000 small data files until OPTIMIZE compacts and VACUUM removes the tombstoned ones.
4. All three then need data-file compaction regardless — small Parquet files are bad for scan efficiency everywhere — but only Iceberg/Delta additionally accumulate metadata files that themselves need rewriting/checkpointing.
5. The senior point: DuckLake removes the metadata small-file problem (and can soften the data small-file problem via inlining), so its maintenance is "compact data + expire snapshots," while Iceberg/Delta add "rewrite manifests / checkpoint the log" on top — fewer moving parts to keep planning fast.

Output.

After 1,000 appends	DuckLake	Iceberg	Delta
Metadata artifacts	rows	many manifest files	1,000 log entries
New data files	few/none (inlined)	~1,000	~1,000
Extra maintenance	compact + expire	+ rewrite manifests	+ checkpoint + vacuum
Planning stays fast?	yes	after manifest rewrite	after checkpoint

Rule of thumb. Chatty small writes are DuckLake's sweet spot: commits are rows and inlining can avoid tiny files entirely, so maintenance is "compact data + expire snapshots." Iceberg and Delta add metadata-file churn (manifests / log entries) that itself needs rewriting or checkpointing, so budget that extra maintenance if your write pattern is small and frequent.

Senior interview question on choosing between DuckLake, Iceberg, and Delta

A senior interviewer might ask: "We ingest with frequent small appends and read with selective, latency-sensitive queries, but two other teams query the same tables from Spark and Trino. Compare DuckLake, Iceberg, and Delta on where metadata lives, planning round-trips, concurrent-commit behaviour, and small-file amplification — then tell me which you'd pick and how you'd hedge the ecosystem risk."

Solution Using the metadata-path comparison and an interop-hedged decision

-- 1. Where metadata lives -> everything else follows.
DuckLake: rows in a SQL catalog  -> plan = 1 query;  commit = 1 txn
Iceberg:  metadata.json -> manifest list -> manifests (Avro) -> catalog pointer
Delta:    _delta_log JSON commits (+ Parquet checkpoints) -> replay

-- 2. Score the four axes for THIS workload (small appends, selective reads,
--    but multi-engine consumers).
planning_latency:  DuckLake 1 round-trip  > Iceberg/Delta multi-read     (favours DuckLake)
concurrency:       DuckLake catalog txns  > optimistic swap + retries    (favours DuckLake)
small_files:       DuckLake rows/inlining > manifest/log churn           (favours DuckLake)
ecosystem breadth: Iceberg/Delta many engines > DuckLake DuckDB-first    (favours Iceberg)

-- 3. Decision + hedge.
if multi-engine access is a HARD requirement today:
    -> Iceberg (broad support), accept slower planning + manifest maintenance
else (DuckDB-centric, chatty, selective, ops-light):
    -> DuckLake, and HEDGE the ecosystem risk:
       DuckLake data + positional-delete files are Iceberg-compatible,
       so migration to Iceberg can be metadata-only, not a data rewrite.

-- 4. The concrete win for this workload, in one query (DuckLake planning):
SELECT path
FROM ducklake_data_file f
JOIN ducklake_file_column_stats s USING (data_file_id)
WHERE f.table_id = 42
  AND s.column_name = 'event_date'
  AND DATE '2026-08-25' BETWEEN s.min_value::date AND s.max_value::date;
-- one catalog query returns exactly the files to scan.

Step-by-step trace.

Axis	DuckLake	Iceberg	Delta	Winner (this workload)
Metadata location	SQL rows	Avro manifests	JSON log	DuckLake
Plan round-trips	1	3+	1 + K	DuckLake
Concurrent appends	serialised txns	optimistic + retry	optimistic + retry	DuckLake
Small-file churn	rows / inlining	manifests	log entries	DuckLake
Engine breadth	DuckDB-first	broad	broad	Iceberg/Delta
Migration hedge	Iceberg-compatible files	—	—	DuckLake

Reasoning for the team: the workload — frequent small appends and selective, latency-sensitive reads — favours DuckLake on three of four axes (planning, concurrency, small files), because metadata is one transactional query rather than a file tree. The one axis it loses is ecosystem breadth, which matters because Spark/Trino teams read the tables. The hedge is interop: DuckLake writes Iceberg-compatible data and positional-delete files, so if multi-engine access becomes a hard blocker you migrate to Iceberg with a metadata-only conversion, not a full rewrite — so picking DuckLake now is a reversible bet, not a lock-in.

Output:

Metric	Iceberg (as default)	DuckLake (+ hedge)
Tiny-query planning	several storage reads	one catalog query
Concurrent small commits	optimistic retries	serialised catalog txns
Metadata after 1k appends	many manifest files	rows (+ inlining)
Multi-engine access today	broad	DuckDB-first
Cost of changing your mind	rewrite/convert	metadata-only migration

Why this works — concept by concept:

• Metadata location decides everything — putting the file list and stats in SQL makes planning one query and commits one transaction, which is why DuckLake wins planning latency, concurrency, and small files in one stroke.
• Optimistic swap vs serialised transactions — Iceberg/Delta resolve concurrent writers by retrying a pointer swap, and their metadata IO widens the conflict window, whereas DuckLake's catalog serialises short transactions and append-append rarely conflicts.
• Small-file amplification — file-based formats add metadata per commit (manifests / log entries), so chatty writes need extra rewriting/checkpointing, while DuckLake commits rows and can inline tiny changes, removing the metadata small-file problem.
• Ecosystem hedge via Iceberg-compatible files — DuckLake's data and positional-delete files match Iceberg's layout, so choosing DuckLake is reversible through a metadata-only migration rather than a data rewrite — the risk mitigation that makes the pick safe.
• Cost — for small-append, selective-read, DuckDB-centric workloads DuckLake is cheaper on planning, concurrency, and maintenance, and the breadth gap is bridgeable by interop; the trade is running a catalog DB and accepting a narrower engine set today — O(1) query planning versus O(files) metadata reads, with a cheap exit.

Optimization
Topic — optimization
Optimization problems on query planning and file pruning

Practice →

Design Topic — design Design problems comparing table formats and metadata layers

Practice →

---

4. Using DuckLake from DuckDB — attach, transactions, time travel

Install, attach a catalog, run ordinary SQL — and every transaction is a queryable snapshot

The mental model in one line: driving DuckLake from DuckDB is three moves — INSTALL ducklake then ATTACH 'ducklake:<catalog>' (DATA_PATH '<storage>') to connect (a local DuckDB file as the catalog for dev, or Postgres/MySQL for a shared deployment), ordinary DDL/DML where each statement or explicit transaction becomes a snapshot, and time-travel reads with AT (VERSION => n) / AT (TIMESTAMP => ...) plus the ducklake_snapshots() and ducklake_table_changes() functions — so from the user's seat it is just SQL, and the lakehouse mechanics (staging Parquet, committing metadata) happen underneath. If you can use DuckDB, you can use DuckLake.

Connecting with ATTACH.

• The shape. ATTACH 'ducklake:<metadata-location>' (DATA_PATH '<data-location>'). The DATA_PATH is only required when creating a new lake; when reconnecting it is loaded from the catalog.
• Dev catalog. ATTACH 'ducklake:my_lake.ducklake' uses a local DuckDB file as the catalog, with data defaulting alongside it — zero infrastructure for prototyping.
• Shared catalog. ATTACH 'ducklake:postgres:dbname=ducklake host=...' (DATA_PATH 's3://bucket/data/') uses Postgres as the catalog and S3 for data — the multi-writer production shape.
• Secrets and read-only. Connection details can live in a CREATE SECRET (TYPE ducklake, ...); (READ_ONLY) attaches without write intent; (SNAPSHOT_VERSION n) / (SNAPSHOT_TIME ...) pins the whole connection to a past version.

Writing — every transaction is a snapshot.

• DDL/DML is normal. CREATE TABLE, INSERT, UPDATE, DELETE, ALTER TABLE ADD COLUMN all work as in DuckDB; each auto-committed statement produces a snapshot.
• Explicit transactions. Wrap several statements in BEGIN ... COMMIT to make them one snapshot (and, as in section 2, span multiple tables atomically).
• Schemas and USE. USE my_lake; sets the attached lake as default so you can write unqualified table names.
• Inlining. With DATA_INLINING_ROW_LIMIT set, small inserts land in the catalog first and flush to Parquet later — fewer tiny files.

Reading the past — time travel and the change feed.

• By version. SELECT * FROM t AT (VERSION => 3) reads the table as of snapshot 3.
• By time. SELECT * FROM t AT (TIMESTAMP => '2026-08-01 00:00:00') resolves to the snapshot in effect then.
• List history. FROM ducklake_snapshots('my_lake') returns every version.
• Incremental scan. FROM ducklake_table_changes('my_lake', 'main', 't', 3, 5) returns just the rows that changed between snapshots 3 and 5 — a built-in change feed for incremental pipelines.

The failure modes senior engineers pre-empt.

• Forgetting DATA_PATH on create. Creating a new lake without a DATA_PATH (and no default) fails or writes to an unintended location. Mitigation: always specify DATA_PATH on first ATTACH; it is remembered thereafter.
• Assuming a dev file catalog is production. A local DuckDB-file catalog is single-writer and not HA. Mitigation: use Postgres/MySQL for shared, concurrent, or production lakes.
• Unbounded snapshot growth. Every write is a snapshot; without expiry, old snapshots pin old files. Mitigation: schedule expire_snapshots and cleanup (covered in section 5).

Common interview probes on DuckDB usage.

• "How do you connect?" — INSTALL ducklake; ATTACH 'ducklake:<catalog>' (DATA_PATH ...).
• "Dev vs prod catalog?" — DuckDB file for dev; Postgres/MySQL for shared multi-writer.
• "How do you time travel?" — AT (VERSION => n) / AT (TIMESTAMP => ...) and ducklake_snapshots().
• "How do you read only what changed?" — ducklake_table_changes(...) between two snapshots.

Worked example — end-to-end: attach, create, insert, query on a local catalog

Detailed explanation. The fastest way to internalise the workflow is a zero-infrastructure run: a local DuckDB-file catalog, a table, a couple of inserts, and a read. This is the whole loop in miniature.

• Catalog. my_lake.ducklake (a local DuckDB file).
• Data. defaults next to the catalog (or set DATA_PATH).
• Loop. attach → create → insert → query.

Question. Write the full sequence to stand up a local DuckLake, create a table, insert rows, and query it.

Input.

Step	Command
install	INSTALL ducklake; LOAD ducklake;
attach	ATTACH 'ducklake:my_lake.ducklake' AS lake
use	USE lake;
create/insert/query	standard SQL

Code.

-- 1. Install and load the extension (once per DuckDB).
INSTALL ducklake;
LOAD ducklake;

-- 2. Attach a local DuckDB-file catalog; data defaults to my_lake.ducklake.files.
--    (For explicit control: ATTACH 'ducklake:my_lake.ducklake' AS lake (DATA_PATH 'data/');)
ATTACH 'ducklake:my_lake.ducklake' AS lake;
USE lake;

-- 3. Ordinary DDL/DML — each statement is a snapshot.
CREATE TABLE orders (id INTEGER, region VARCHAR, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, 'EU', 42.00);   -- snapshot v2
INSERT INTO orders VALUES (2, 'US', 19.90);   -- snapshot v3

-- 4. Read it back — just SQL.
SELECT region, sum(amount) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;

Step-by-step explanation.

1. INSTALL ducklake; LOAD ducklake; pulls in the extension that teaches DuckDB the ducklake: attach protocol — a one-time step per DuckDB installation.
2. ATTACH 'ducklake:my_lake.ducklake' AS lake creates the lake if it does not exist, using the local DuckDB file as the catalog; because no DATA_PATH is given, data defaults to a sibling directory — perfect for a laptop prototype.
3. USE lake; makes the attached lake the default catalog so table names need no prefix; CREATE TABLE writes a snapshot recording the new (empty) table and its schema.
4. Each INSERT stages a Parquet file and commits a snapshot, so after the two inserts the lake has versions v2 and v3 (v1 being the create) — all as catalog rows.
5. The final SELECT is a completely ordinary DuckDB query; the extension resolves orders to its current file list via one catalog query and scans the Parquet — the lakehouse is invisible at the SQL layer.

Output.

After step	Snapshot	State
CREATE TABLE	v1	empty orders
INSERT (1,EU)	v2	1 row
INSERT (2,US)	v3	2 rows
SELECT	—	EU 42.00, US 19.90

Rule of thumb. The DuckLake loop is INSTALL/LOAD ducklake → ATTACH 'ducklake:<catalog>' (DATA_PATH ...) → USE → ordinary SQL. A local DuckDB-file catalog needs zero infrastructure for dev; everything you write is standard DuckDB SQL, and each statement is a snapshot.

Worked example — a multi-statement transaction and a time-travel diff

Detailed explanation. Combine explicit transactions with time travel: make a batch of changes as one snapshot, then compare the table before and after that snapshot in a single query — no restore needed.

• The change. An UPDATE and an INSERT in one transaction → one snapshot.
• The check. Diff the metric across the version boundary with AT (VERSION => ...).
• The payoff. Auditability: any past version is queryable.

Question. Apply a two-statement transaction, then write one query that compares the total before and after the resulting snapshot.

Input.

Aspect	Value
Transaction	UPDATE + INSERT, one COMMIT
Before version	v4
After version	v5
Check	sum(amount) at v4 vs v5

Code.

-- Suppose the table is at snapshot v4. Apply a batch as ONE snapshot (v5).
BEGIN;
  UPDATE orders SET amount = amount * 1.10 WHERE region = 'EU';  -- FX adjustment
  INSERT INTO orders VALUES (3, 'EU', 55.00);                    -- a new order
COMMIT;   -- both changes -> snapshot v5

-- Confirm which versions exist.
FROM ducklake_snapshots('lake');   -- ... v4 (before), v5 (after)

-- One query diffs the metric across the version boundary — no restore.
SELECT
  (SELECT sum(amount) FROM orders AT (VERSION => 4)) AS before_total,
  (SELECT sum(amount) FROM orders AT (VERSION => 5)) AS after_total,
  (SELECT sum(amount) FROM orders AT (VERSION => 5))
  - (SELECT sum(amount) FROM orders AT (VERSION => 4)) AS delta;

Step-by-step explanation.

1. The BEGIN ... COMMIT block groups the UPDATE and INSERT into one transaction, so both land in a single snapshot (v5) — a reader never sees the update without the insert.
2. ducklake_snapshots('lake') confirms the version boundary: v4 is the state before the batch, v5 the state after — the audit trail is just catalog rows.
3. The diff query reads orders twice, once AT (VERSION => 4) and once AT (VERSION => 5); each AT clause resolves the file list for that snapshot, so the two sub-selects scan two consistent historical states in the same query.
4. No restore, copy, or branch is needed to compare versions — because both snapshots are permanently addressable, "what did this metric look like before that batch?" is a WHERE-free time-travel read.
5. This is how DuckLake makes auditing and debugging cheap: any regression ("revenue jumped at 09:07") is investigated by diffing the snapshot before and after the suspect commit, directly in SQL.

Output.

Column	Source	Example
before_total	orders AT (VERSION => 4)	61.90
after_total	orders AT (VERSION => 5)	120.20
delta	v5 − v4	+58.30
snapshots	ducklake_snapshots	v4, v5

Rule of thumb. Group related changes in BEGIN ... COMMIT so they form one auditable snapshot, then investigate any change by diffing AT (VERSION => before) against AT (VERSION => after) in a single query. Time travel turns "what changed and when" into an ordinary SQL read, no restore required.

Worked example — incremental scan with the change feed

Detailed explanation. Downstream pipelines usually want only what changed since the last run. ducklake_table_changes() returns the row-level changes between two snapshots, so an incremental job reads deltas instead of rescanning the table.

• The need. Process only rows changed since the last processed snapshot.
• The tool. ducklake_table_changes('lake', 'main', 'orders', from, to).
• The pattern. Store the last snapshot id; read changes to the current one.

Question. Write an incremental read that processes only the rows changed between the last-processed snapshot and the latest.

Input.

Aspect	Value
Function	ducklake_table_changes(lake, schema, table, from, to)
Last processed	snapshot 5
Latest	snapshot 8
Output	inserted/updated/deleted rows with change type

Code.

-- What snapshots exist? Grab the latest id.
SELECT max(snapshot_id) AS latest FROM ducklake_snapshots('lake');   -- e.g. 8

-- Read ONLY the changes between the last processed snapshot (5) and latest (8).
FROM ducklake_table_changes('lake', 'main', 'orders', 5, 8);
-- change_type | id | region | amount   -> insert/update/delete rows only

-- Use it to drive an incremental upsert into a downstream mart.
INSERT INTO mart.orders_daily
SELECT region, date_trunc('day', now()) AS d, sum(amount) AS revenue
FROM ducklake_table_changes('lake', 'main', 'orders', 5, 8)
WHERE change_type IN ('insert', 'update')
GROUP BY region;

-- Persist the new watermark (8) as the last processed snapshot for next run.

Step-by-step explanation.

1. The job first reads the latest snapshot id from ducklake_snapshots — this is the high-water mark it will process up to and remember for next time.
2. ducklake_table_changes('lake', 'main', 'orders', 5, 8) returns only the rows that were inserted, updated, or deleted between snapshot 5 and 8, each tagged with its change_type — the built-in change feed, computed from the catalog's snapshot deltas.
3. Because it returns deltas, the incremental job scans a tiny fraction of the table instead of rescanning everything — the whole point of an incremental pipeline.
4. The example folds the inserted/updated rows into a downstream mart; a real job would apply deletes too, but the pattern is the same: read the change feed, apply, advance the watermark.
5. Persisting the new watermark (8) means the next run reads changes(8, latest) — a clean, restartable incremental contract driven entirely by snapshot ids, with no external CDC system.

Output.

Piece	Value
watermark before	snapshot 5
changes read	rows changed in (5, 8]
work done	delta-sized, not full scan
watermark after	snapshot 8

Rule of thumb. For incremental pipelines, store the last-processed snapshot id and read ducklake_table_changes(lake, schema, table, last, latest) to get just the inserted/updated/deleted rows, then advance the watermark. DuckLake's snapshots give you a built-in, restartable change feed without a separate CDC stack.

Senior interview question on operating DuckLake from DuckDB

A senior interviewer might ask: "Show me, in DuckDB, how you'd stand up a DuckLake for a shared team: connecting with the right catalog for production, writing data so related changes are atomic, letting analysts query historical versions, and feeding a downstream incremental pipeline — and call out the choices that separate a dev prototype from a production setup."

Solution Using a Postgres catalog, transactional writes, time travel, and the change feed

-- 1. Production connect: Postgres catalog (shared, multi-writer) + S3 data path.
INSTALL ducklake; LOAD ducklake;
CREATE SECRET pg_cat (TYPE ducklake,
  METADATA_PATH 'postgres:dbname=ducklake host=pg-host user=svc',
  DATA_PATH 's3://lake-bucket/data/');
ATTACH 'ducklake:pg_cat' AS lake;      -- vs a dev 'ducklake:my_lake.ducklake' file
USE lake;

-- 2. Atomic related writes -> one snapshot spanning tables.
BEGIN;
  INSERT INTO orders   SELECT * FROM staging_orders WHERE batch_id = 42;
  UPDATE  ingest_state SET last_batch = 42 WHERE pipeline = 'orders';
COMMIT;                                -- orders + state consistent, one version

-- 3. Analysts query history without restoring anything.
FROM ducklake_snapshots('lake');                       -- browse versions
SELECT * FROM orders AT (TIMESTAMP => '2026-08-25 09:00:00');  -- as-of read

-- 4. Downstream incremental pipeline consumes the change feed by snapshot id.
FROM ducklake_table_changes('lake', 'main', 'orders', :last_snapshot, :latest_snapshot);
-- apply deltas downstream, then persist :latest_snapshot as the new watermark.

Step-by-step trace.

Concern	Dev prototype	Production setup
Catalog	local DuckDB file	Postgres (shared, HA)
Data path	alongside the file	s3://... object storage
Credentials	inline	CREATE SECRET
Atomic writes	single statements	BEGIN ... COMMIT across tables
History access	AT (VERSION)	AT (TIMESTAMP) for analysts
Incremental feed	manual	ducklake_table_changes + watermark

Walking it through: production attaches a Postgres catalog (so many writers can share the lake and the catalog is HA) with an S3 DATA_PATH, credentials tucked into a secret; related writes are wrapped in a transaction so orders and the ingest-state table move together as one snapshot; analysts read any historical version with AT (TIMESTAMP => ...) without restoring; and a downstream job consumes ducklake_table_changes between its stored watermark and the latest snapshot, then advances the watermark — a restartable incremental contract. The dev/prod difference is almost entirely the catalog choice and the discipline of transactional writes and maintenance.

Output:

Capability	How it is achieved
Shared multi-writer	Postgres catalog + S3 data path
Atomic related changes	BEGIN ... COMMIT (one snapshot, many tables)
Historical queries	AT (VERSION/TIMESTAMP) + ducklake_snapshots()
Incremental consumption	ducklake_table_changes() + snapshot watermark
Secret management	CREATE SECRET (TYPE ducklake, ...)

Why this works — concept by concept:

• ATTACH with the right catalog — a local DuckDB file is a zero-setup dev catalog, while a Postgres/MySQL catalog is the shared, multi-writer, highly-available choice for production; the DATA_PATH points data at object storage and is remembered after creation.
• Transactional writes — wrapping related statements in BEGIN ... COMMIT makes them one snapshot spanning tables, so downstream readers never observe a partial batch and the ingest state stays consistent with the data.
• Time travel as a read — AT (VERSION => n) and AT (TIMESTAMP => ...) plus ducklake_snapshots() make any historical version queryable in place, so audits and debugging are ordinary SQL rather than restores.
• Built-in change feed — ducklake_table_changes() between two snapshot ids returns row-level deltas, giving incremental pipelines a restartable, watermark-driven contract without a separate CDC system.
• Cost — the entire workflow is DuckDB SQL over a catalog you already know how to run; the only production-specific costs are choosing a real catalog DB (HA + backups) and scheduling maintenance — O(1) planning and delta-sized incremental reads versus full rescans and external CDC.

Data processing
Topic — data-processing
Data processing problems on DuckDB SQL and analytical queries

Practice →

ETL Topic — etl ETL problems on incremental loads and change feeds

Practice →

---

5. Choosing the catalog, migration & interop, and when to pick

Pick a SQL catalog you already run, keep it compacted, and choose DuckLake when breadth isn't the constraint

The mental model in one line: operating DuckLake well is three decisions — choosing the catalog database (a DuckDB file for dev, Postgres/MySQL for shared multi-writer, made highly available and backed up because it is now a source of truth), running the maintenance loop that keeps a lakehouse fast (merge_adjacent_files to compact, expire_snapshots to drop old versions, and cleanup of orphaned files), and making the pick-or-not call against Iceberg/Delta Lake — where DuckLake wins on DuckDB-centricity, cheap ops, cross-table ACID, and low-latency planning, and Iceberg/Delta win on multi-engine breadth and managed governance — with the safety net that DuckLake's Iceberg-compatible data files make migration in or out a metadata-only operation. These are the calls that turn a prototype into a platform.

Choosing the catalog database.

• DuckDB file. Zero-infrastructure, single-writer — ideal for dev, testing, and single-node analytics. Not for shared concurrent production.
• Postgres / MySQL. The shared, multi-writer choice: any number of compute nodes commit metadata transactions against it. Pick the one your org already operates.
• Requirements. The only hard requirements are ACID and primary-key support with standard SQL; the DuckLake schema is intentionally simple to maximise compatibility.
• Because it is a source of truth. Make the catalog highly available and back it up on the same footing as any primary database — losing it orphans the Parquet.

The maintenance loop.

• Compaction. merge_adjacent_files rewrites many small Parquet files into fewer large ones so scans stay efficient — the data-side small-file fix every format needs.
• Snapshot expiry. expire_snapshots drops versions older than a retention window so old files can be released; snapshots are cheap, but retaining them forever pins storage.
• Cleanup. Removing files that expiry made unreferenced (orphans) reclaims storage; DuckLake tracks files scheduled for deletion so cleanup is safe.
• Cadence. Compact and expire on a schedule tuned to write chattiness; heavy small-append workloads compact more often.

Migration and interop.

• Iceberg-compatible files. The Parquet data and positional-delete files DuckLake writes match Iceberg's layout, so converting between DuckLake and Iceberg can be a metadata-only operation rather than a full data rewrite.
• Adopting existing Parquet. Existing Parquet datasets can be registered/added into a DuckLake rather than recopied.
• DuckDB-to-DuckLake. Existing DuckDB tables migrate into a lake with a documented path — a natural on-ramp for DuckDB shops.
• The strategic point. Interop makes DuckLake a reversible bet: you can start on it and move to Iceberg (or vice versa) without a disruptive rewrite.

When to pick DuckLake — and when not to.

• Pick DuckLake when. Your access is DuckDB-centric, writes are frequent and small, you want cross-table atomicity and low-latency planning, and you value few moving parts (a catalog DB you already run).
• Pick Iceberg/Delta when. Many engines (Spark/Trino/Flink) must read the tables today, or you need a managed governance catalog (Unity/Glue/Polaris) and its ecosystem.
• The maturity caveat. DuckLake is newer; weigh operational maturity, tooling, and team familiarity, not just the format's design elegance.
• Hedge either way. Because of interop, the decision is not permanent — pick for today's workload and keep the migration door open.

The failure modes senior engineers pre-empt.

• A single non-HA catalog. One DuckDB file (or an unreplicated Postgres) as the catalog is a single point of failure for the whole lake. Mitigation: HA + backups for the catalog DB.
• No compaction. Chatty writes plus no merge_adjacent_files degrade scans over time. Mitigation: scheduled compaction and expiry.
• Assuming universal engine support. Building a five-engine platform on DuckLake today underestimates the ecosystem gap. Mitigation: check engine support against real consumers; use Iceberg where breadth is mandatory.

Common interview probes on operating DuckLake.

• "Which catalog DB?" — DuckDB file for dev; Postgres/MySQL for shared prod; HA + backups.
• "How do you keep it fast?" — compact (merge_adjacent_files), expire snapshots, clean up orphans.
• "Can I migrate off it?" — yes; Iceberg-compatible files make it metadata-only.
• "When would you not use it?" — multi-engine breadth or managed governance is mandatory.

Worked example — choose the catalog DB for three deployment profiles

Detailed explanation. The catalog choice follows the deployment: single laptop, small shared team, large multi-writer platform. Place each on the right catalog and justify it.

• Profile A. Solo analyst on a laptop.
• Profile B. A five-person team sharing a lake.
• Profile C. A platform with many concurrent writers.

Question. For each profile, pick the catalog database and state the availability posture.

Input.

Profile	Writers	Catalog	Availability
A: solo/dev	1	DuckDB file	local backup
B: small team	few	Postgres (managed)	replica + backups
C: platform	many concurrent	Postgres/MySQL (HA)	multi-AZ + PITR

Code.

Catalog choice by deployment profile
=====================================

A) Solo analyst / dev / CI
   ATTACH 'ducklake:local.ducklake' (DATA_PATH 'data/');
   -> DuckDB-file catalog. Single-writer, zero infra. Back up the file.

B) Small shared team (a few writers)
   ATTACH 'ducklake:postgres:dbname=lake host=managed-pg' (DATA_PATH 's3://.../data/');
   -> Managed Postgres catalog. Concurrent metadata commits; enable a read
      replica + automated backups. Right-sized and cheap.

C) Platform: many concurrent writers, strict SLAs
   ATTACH 'ducklake:postgres:dbname=lake host=ha-pg' (DATA_PATH 's3://.../data/');
   -> HA Postgres (multi-AZ, point-in-time recovery). It only runs SMALL
      metadata transactions, so even high commit rates fit a modest instance —
      but it is a source of truth, so treat its availability like a primary DB.

Step-by-step explanation.

1. Profile A is single-writer and disposable, so a local DuckDB-file catalog is perfect — no server to run — and the only durability step is backing up the file (and the data directory).
2. Profile B introduces concurrent writers, which a single-file catalog cannot serve safely, so it moves to a managed Postgres; a replica and automated backups cover availability without much operational weight.
3. Profile C has many concurrent writers and SLAs, so the catalog must be genuinely HA (multi-AZ, point-in-time recovery) — because if it is down, the whole lake is unwritable and its metadata is at risk.
4. The reassuring part for C is that the catalog handles only metadata transactions, which are orders of magnitude smaller than the data, so even thousands of commits per second fit a modest Postgres — you size for metadata QPS, not data volume.
5. Across all three, the constant is "the catalog is a source of truth": the availability posture scales with how many writers depend on it and how strict the SLA is, exactly as you would treat any operational database.

Output.

Profile	Catalog	Why
A: solo	DuckDB file	single-writer, zero infra
B: team	managed Postgres	concurrent writers, light ops
C: platform	HA Postgres/MySQL	many writers, source-of-truth SLA
all	back it up	catalog loss orphans Parquet

Rule of thumb. Match the catalog to the deployment: a DuckDB file for solo/dev, managed Postgres for a shared team, HA Postgres/MySQL (multi-AZ + PITR) for a multi-writer platform. Size it for metadata QPS, not data volume — but back it up and make it available like the source of truth it now is.

Worked example — a maintenance routine (compact, expire, cleanup)

Detailed explanation. A lake with frequent writes needs a scheduled loop to stay fast and cheap: compact small files, expire old snapshots, remove orphaned files. Sketch the routine and its order.

• Compact. merge_adjacent_files folds small Parquet into large.
• Expire. expire_snapshots drops versions past retention.
• Cleanup. delete files that expiry unreferenced.

Question. Write a scheduled maintenance routine and explain why the order (compact → expire → cleanup) matters.

Input.

Step	Operation	Effect
1	merge_adjacent_files	fewer, larger data files
2	expire_snapshots	drop versions past retention
3	cleanup	delete now-orphaned files
cadence	scheduled	tuned to write rate

Code.

-- Nightly maintenance for a chatty DuckLake table (illustrative calls).

-- 1. COMPACT: merge many small Parquet files into fewer large ones.
CALL merge_adjacent_files('lake', 'main', 'orders');

-- 2. EXPIRE: drop snapshots older than the retention window (e.g. 7 days).
CALL expire_snapshots('lake', older_than => now() - INTERVAL '7 days');

-- 3. CLEANUP: physically delete files that expiry left unreferenced.
CALL cleanup_old_files('lake', cleanup_all => true);

-- Sanity: snapshots retained and current file count.
SELECT count(*) AS snapshots FROM ducklake_snapshots('lake');
SELECT count(*) AS files     FROM ducklake_data_file WHERE table_id = 42;

Step-by-step explanation.

1. Compaction runs first: merge_adjacent_files rewrites the many small Parquet files a chatty workload produced into fewer large ones, which is what keeps scans efficient — the data-side small-file fix.
2. Expiry runs second: expire_snapshots drops versions older than the retention window. It must come after compaction so that the freshly compacted state is the one retained, and so old snapshots referencing the pre-compaction small files become eligible for removal.
3. Cleanup runs last: once expiry has removed the snapshots that referenced the old (now superseded) files, those files are orphans, and cleanup_old_files physically deletes them to reclaim storage — DuckLake tracks files scheduled for deletion so this is safe.
4. The order matters because each step creates the garbage the next step collects: compaction supersedes small files, expiry unreferences them, cleanup deletes them. Reordering risks either deleting files a live snapshot still needs or leaving orphans behind.
5. Cadence follows write chattiness: a minutely-append table compacts and expires nightly (or more often), while a slow table needs it rarely — you tune the schedule to how fast small files and snapshots accumulate.

Output.

After routine	Before	After
Small data files	many	few (compacted)
Snapshots	unbounded	within retention
Orphan files	present	removed
Scan efficiency	degrading	restored

Rule of thumb. Run maintenance in order — compact (merge_adjacent_files) → expire (expire_snapshots) → cleanup orphans — on a cadence tuned to write chattiness. Each step produces what the next collects, so the order is not optional; done regularly, it keeps a DuckLake's scans fast and its storage bill flat.

Worked example — the pick-DuckLake-or-not decision under constraints

Detailed explanation. The final judgement is a constraint check: given real consumers and requirements, is DuckLake the right format, or is Iceberg/Delta? Run three scenarios through the decision.

• Scenario 1. DuckDB-centric team, chatty writes, cross-table needs.
• Scenario 2. Five engines must read the tables; governance catalog required.
• Scenario 3. DuckDB today, but multi-engine "maybe next year."

Question. For each scenario, decide DuckLake or Iceberg/Delta and justify it, including how interop affects the call.

Input.

Scenario	Hard constraint	Pick
1: DuckDB-centric, chatty, cross-table	ops-light, atomic multi-table	DuckLake
2: 5 engines + Unity/Glue governance	breadth + managed catalog	Iceberg / Delta
3: DuckDB now, maybe multi-engine later	reversibility	DuckLake now, interop hedge

Code.

Decision under constraints
==========================

Scenario 1: DuckDB-centric, minutely appends, two tables kept in sync, small Postgres exists
  binding constraints: cross-table atomicity + ops-light + fast planning
  -> DuckLake. All three are native strengths; the catalog is a Postgres you already run.

Scenario 2: Spark + Trino + Flink + Snowflake read the tables; Unity governance mandated
  binding constraint: multi-engine breadth + managed governance catalog (HARD today)
  -> Iceberg (or Delta). DuckLake's ecosystem can't satisfy this now; don't fight it.

Scenario 3: DuckDB today; leadership says "we might add Spark next year"
  binding constraint: reversibility, not breadth-today
  -> DuckLake NOW (it fits today's workload), and rely on interop:
     DuckLake data + positional-delete files are Iceberg-compatible, so IF Spark
     becomes real, migrate to Iceberg metadata-only rather than rewriting data.
     Don't pay Iceberg's complexity today for a requirement that may not arrive.

Step-by-step explanation.

1. Scenario 1's binding constraints — cross-table atomicity, low ops, fast planning — are exactly DuckLake's native strengths, and the catalog is a Postgres the team already runs, so DuckLake is the clear pick.
2. Scenario 2's binding constraint is multi-engine breadth plus a mandated governance catalog, which DuckLake cannot satisfy today; the senior move is to not fight the ecosystem reality and pick Iceberg/Delta.
3. Scenario 3 is the interesting one: the current workload fits DuckLake, and the future multi-engine need is speculative, so paying Iceberg's complexity now would be premature.
4. Interop resolves Scenario 3: because DuckLake writes Iceberg-compatible data and delete files, adopting it now is reversible — if Spark becomes a real requirement, you migrate to Iceberg with a metadata-only conversion rather than rewriting petabytes.
5. The through-line is "decide on binding constraints, not fashion, and use interop to keep speculative requirements from forcing premature complexity" — the judgement that separates an architect from a checklist-follower.

Output.

Scenario	Decision	Deciding factor
1	DuckLake	cross-table + ops-light + planning
2	Iceberg / Delta	breadth + governance (hard today)
3	DuckLake + interop hedge	reversibility beats speculative breadth
all	choose on binding constraints	interop keeps the door open

Rule of thumb. Decide by binding constraints: cross-table atomicity, ops-light, and fast planning point to DuckLake; mandatory multi-engine breadth or a managed governance catalog point to Iceberg/Delta. For speculative future breadth, pick DuckLake now and lean on Iceberg-compatible interop to migrate later — don't buy complexity today for a requirement that may never arrive.

Senior interview question on rolling out and operating DuckLake

A senior interviewer might ask: "You're standing up DuckLake for a growing team. How do you choose and protect the catalog database, what maintenance keeps the lake fast and cheap over time, how do you handle migration in from existing Parquet and the risk of needing other engines later, and under what constraints would you not choose DuckLake at all?"

Solution Using a right-sized HA catalog, a maintenance loop, and interop-hedged format selection

-- 1. Catalog choice + protection (source of truth).
dev/CI:     DuckDB-file catalog (single-writer, disposable)
shared/prod: Postgres/MySQL catalog, HA (multi-AZ) + backups + PITR
sizing:      metadata QPS, NOT data volume (commits are small txns)

-- 2. Scheduled maintenance loop (order matters).
CALL merge_adjacent_files('lake','main','orders');                 -- compact
CALL expire_snapshots('lake', older_than => now() - INTERVAL '7 days');  -- expire
CALL cleanup_old_files('lake', cleanup_all => true);               -- cleanup orphans

-- 3. Migration / interop (reversible bet).
adopt existing Parquet -> register/add files into the lake (no recopy)
DuckDB tables          -> documented DuckDB-to-DuckLake migration
DuckLake <-> Iceberg   -> data + positional-delete files are Iceberg-compatible
                          => metadata-only migration in or out

-- 4. When NOT to pick DuckLake.
many engines (Spark/Trino/Flink) must read TODAY   -> Iceberg / Delta
a managed governance catalog is mandated           -> Iceberg / Delta
otherwise (DuckDB-centric, chatty, cross-table)    -> DuckLake, hedged by interop

Step-by-step trace.

Concern	Decision	Rationale
Catalog (dev)	DuckDB file	zero infra, single-writer
Catalog (prod)	HA Postgres/MySQL	multi-writer, source of truth
Sizing	metadata QPS	commits are small txns
Maintenance	compact → expire → cleanup	each step feeds the next
Migration in	register Parquet / DuckDB path	no recopy
Ecosystem risk	Iceberg-compatible files	metadata-only exit
When not to	multi-engine / governance mandated	breadth wins

The rollout in narrative: pick a DuckDB-file catalog for dev and an HA Postgres/MySQL for production, sized for small metadata transactions and protected like a primary database; keep the lake fast with a scheduled compact → expire → cleanup loop whose order matters because each step creates the garbage the next collects; migrate in cheaply by registering existing Parquet or using the DuckDB-to-DuckLake path, and treat the whole choice as reversible because DuckLake's Iceberg-compatible files allow a metadata-only migration if you ever need Iceberg's ecosystem; and decline DuckLake only when broad multi-engine access or a managed governance catalog is a hard requirement today.

Output:

Metric	Naive rollout	Operated DuckLake
Catalog availability	single file / unreplicated	HA + backups + PITR
Scan speed over time	degrades (small files)	stable (compaction)
Storage growth	unbounded snapshots	bounded (expire + cleanup)
Migration cost	full rewrite feared	metadata-only (interop)
Format fit	"DuckLake everywhere"	chosen on binding constraints

Why this works — concept by concept:

• Catalog as source of truth — sizing the catalog for small metadata transactions keeps it cheap, but making it HA and backing it up is non-negotiable because losing it orphans the Parquet data it describes.
• Ordered maintenance loop — compact then expire then cleanup works because compaction supersedes small files, expiry unreferences the old ones, and cleanup deletes the resulting orphans; the order prevents both data loss and orphan accumulation.
• Cheap migration in — registering existing Parquet and the DuckDB-to-DuckLake path let you adopt DuckLake without recopying data, lowering the cost of trying it.
• Iceberg-compatible interop as a hedge — because DuckLake's data and positional-delete files match Iceberg's layout, the format choice is reversible via a metadata-only migration, so speculative future breadth never forces premature complexity.
• Cost — you run one catalog DB and a scheduled maintenance loop, and you keep an interop exit; the trade against Iceberg/Delta is a narrower engine ecosystem today, bought back by lower ops and reversibility — choose on binding constraints, not fashion.

ETL
Topic — etl
ETL problems on migration and lakehouse ingestion

Practice →

Data transformation
Topic — data-transformation
Data transformation problems on compaction and file rewrites

Practice →

---

Cheat sheet — DuckLake recipes

• The one-line model. DuckLake is a lakehouse table format that keeps data as immutable Parquet on object storage and all metadata (schemas, snapshots, file lists, statistics) as rows in a transactional SQL catalog database. A commit is one SQL transaction; a snapshot is a few rows. It exists because Iceberg/Delta already require a database in their catalog, so DuckLake drops the redundant file-metadata layer.
• The three-part split. compute (DuckDB + ducklake extension) → catalog DB (metadata, ACID) → object storage (immutable Parquet). Compute is stateless and scalable; the catalog is the source of truth; storage never mutates a file in place.
• ATTACH templates. Dev: ATTACH 'ducklake:my_lake.ducklake' (DuckDB-file catalog, data alongside). Prod: ATTACH 'ducklake:postgres:dbname=lake host=...' (DATA_PATH 's3://bucket/data/'). Secret: CREATE SECRET (TYPE ducklake, METADATA_PATH '...', DATA_PATH '...') then ATTACH 'ducklake:secret'. DATA_PATH is only needed on create; (READ_ONLY) and (SNAPSHOT_VERSION n) pin the connection.
• Write = snapshot. Ordinary CREATE/INSERT/UPDATE/DELETE; each auto-committed statement is a snapshot. Wrap related changes in BEGIN ... COMMIT for one snapshot spanning multiple tables atomically — the cross-table ACID file formats can't match. Set DATA_INLINING_ROW_LIMIT so tiny inserts inline into the catalog instead of spawning files.
• Time travel + change feed. FROM ducklake_snapshots('lake') lists versions; SELECT * FROM t AT (VERSION => n) / AT (TIMESTAMP => '...') reads history in place; FROM ducklake_table_changes('lake','main','t', from, to) returns row-level deltas between snapshots — a built-in, watermark-driven change feed for incremental pipelines.
• vs Iceberg/Delta — where metadata lives decides everything. DuckLake: SQL rows → plan = 1 catalog query, commit = 1 transaction, no metadata small-file churn. Iceberg: metadata.json → manifest list → Avro manifests → Parquet, plus a catalog pointer → multi-read planning, optimistic-swap commits, manifest churn. Delta: _delta_log JSON commits + Parquet checkpoints → replay planning, optimistic commits, log churn. Iceberg/Delta win on multi-engine breadth.
• Concurrency. DuckLake serialises short catalog transactions, so append-append rarely conflicts and commit rate ≈ catalog transactions/sec (thousands on Postgres). Iceberg/Delta use optimistic concurrency; the loser retries, and metadata IO in the critical path widens the conflict window.
• Maintenance loop (order matters). merge_adjacent_files (compact small Parquet) → expire_snapshots (drop versions past retention) → cleanup orphaned files. Each step creates what the next collects; run on a cadence tuned to write chattiness.
• Catalog choice. DuckDB file for dev/CI (single-writer, zero infra); Postgres/MySQL for shared multi-writer prod. Only hard requirements: ACID + primary keys + standard SQL. Size for metadata QPS, not data volume — but make it HA and back it up: lose the catalog and the Parquet is anonymous bytes.
• Interop / migration. DuckLake's data and positional-delete files are Iceberg-compatible, so migrating in or out is metadata-only, not a data rewrite. Existing Parquet can be registered; there's a documented DuckDB-to-DuckLake path. This makes DuckLake a reversible bet.
• When to pick. DuckLake for DuckDB-centric access, frequent small writes, cross-table atomicity, low-latency planning, and fewest moving parts. Iceberg/Delta when many engines must read today or a managed governance catalog is mandated. Decide on binding constraints, not fashion — and lean on interop for speculative future needs.

Frequently asked questions

What is DuckLake and how is it different from Iceberg and Delta?

DuckLake is an open lakehouse table format that stores your table data as immutable Parquet files on object storage — exactly like Iceberg and Delta — but stores all the metadata (schemas, snapshots, file lists, per-column statistics, partition values) as ordinary rows in a transactional SQL catalog database rather than as files on storage. Iceberg encodes that metadata in a chain of metadata.json and Avro manifest files, and Delta encodes it in a _delta_log of JSON commits with periodic Parquet checkpoints; both then still require a catalog backed by a database for the atomic "current version" pointer. DuckLake's core insight is that since a database is already mandatory, you should put all metadata in it and drop the redundant file layers — which makes read planning one catalog query, commits one SQL transaction, and cross-table atomicity native. The main trade-off is ecosystem breadth: Iceberg and Delta are read by many engines today, while DuckLake is DuckDB-centric and newer.

Where does DuckLake store metadata and data?

Two places, cleanly separated. The data lives as immutable Parquet files (plus positional-delete files) on whatever object storage you point DATA_PATH at — S3, GCS, Azure, or a local directory — and DuckLake never modifies a file in place or reuses a filename, which is what makes storage-side consistency a non-issue. The metadata — every snapshot, table, schema, data-file entry, and the statistics used to prune reads — lives as rows in a SQL catalog database, which can be a local DuckDB file for development or Postgres/MySQL for a shared, multi-writer deployment. This split is the whole design: cheap immutable bulk storage for data, and a transactional database for the metadata that needs atomicity and fast querying. Because the catalog is now the source of truth for what a table is, you back it up and make it highly available like any primary database.

Is DuckLake ACID, and how does concurrency work?

Yes. DuckLake gets ACID directly from its catalog database: a commit is a single SQL transaction that inserts a new snapshot row and the associated data-file rows, so atomicity and durability are the database's, and consistency is enforced by the catalog schema's primary keys and foreign keys (for example, no duplicate snapshot ids and no file rows pointing at a missing snapshot). Isolation comes from reading a pinned snapshot's file list, giving snapshot isolation and time travel. Concurrency is handled by the database serialising short metadata transactions, so two appends — which don't logically conflict — both commit as consecutive snapshots, and the sustained commit rate is essentially "how many transactions per second the catalog can do," which is thousands on an ordinary Postgres. A distinctive benefit is cross-table atomicity: because all metadata is in one transactional database, a single transaction can change several tables and produce one snapshot, something single-table file formats cannot do natively.

Which catalog database should I use for DuckLake?

Match it to your deployment. For development, testing, CI, or single-node analytics, use a local DuckDB file as the catalog — it needs zero infrastructure and is single-writer, so it's perfect for prototyping but not for shared production. For a shared team or a platform with concurrent writers, use Postgres or MySQL (managed is ideal): any number of compute nodes can commit metadata transactions against it. The only hard requirements are ACID support, primary keys, and standard SQL, because the DuckLake catalog schema is intentionally simple to maximise compatibility. Critically, size the catalog for metadata throughput, not data volume — commits are small transactions, so even high commit rates fit a modest instance — but because the catalog is now a source of truth for your tables, treat its availability and backups exactly as you would a primary database: lose it, and your Parquet files are anonymous bytes.

Can I migrate between DuckLake and Iceberg?

Yes, and cheaply, which is what makes DuckLake a reversible bet rather than lock-in. The Parquet data files and positional-delete files that DuckLake writes are compatible with Apache Iceberg's layout, so converting a table between DuckLake and Iceberg can be a metadata-only operation — you re-describe the same physical files in the other format's metadata rather than rewriting terabytes of data. You can also register existing Parquet datasets into a DuckLake instead of recopying them, and there's a documented path for migrating existing DuckDB tables into a lake, which makes adoption easy for DuckDB shops. Practically, this means you can start a workload on DuckLake for its planning speed, cheap ops, and cross-table transactions, and if you later discover you genuinely need Iceberg's broad multi-engine ecosystem, you migrate with a metadata conversion instead of a disruptive data rewrite.

When should I pick DuckLake over Iceberg or Delta Lake?

Pick DuckLake when your access is DuckDB-centric, your writes are frequent and small, you want cross-table atomicity and low-latency query planning, and you value having the fewest moving parts — the catalog is just a SQL database you already know how to run. Its metadata-in-SQL design wins on three axes: planning is one catalog query instead of a multi-file read chain, commits are serialised transactions that rarely conflict on appends, and there's no per-commit metadata small-file churn (tiny inserts can even inline into the catalog). Pick Iceberg or Delta instead when many engines — Spark, Trino, Flink, Snowflake — must read the same tables today, or when a managed governance catalog like Unity, Glue, or Polaris is mandated, because that ecosystem breadth is where the incumbents currently dominate and DuckLake is newer and narrower. Decide on your binding constraints rather than fashion, and remember the interop hedge: because DuckLake's files are Iceberg-compatible, choosing it now doesn't foreclose moving later.

Practice on PipeCode

• Drill the data engineering system design library → for the lakehouse, table-format, and metadata-layer design problems that DuckLake, Iceberg, and Delta make concrete.
• Rehearse the file-and-format mechanics on the data processing practice library → for the Parquet, DuckDB SQL, and analytical-query patterns underneath every lakehouse format.
• Sharpen the planning-latency axis with the query optimization practice library → for the file-pruning, statistics, and metadata-round-trip trade-offs that separate one-query planning from a file-read chain.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the snapshot-isolation, ACID, and incremental-change-feed patterns against real graded inputs — DuckDB SQL, Parquet layouts, compaction, and migration.

Lock in lakehouse-format muscle memory

Docs explain DuckLake, Iceberg, and Delta. PipeCode drills explain the decision — when `metadata` belongs in a SQL catalog instead of files, when one transaction beats an optimistic pointer swap, when small appends demand `data inlining` over manifest churn, and when Iceberg's breadth outweighs DuckLake's simplicity. Pipecode.ai is Leetcode for Data Engineering — lakehouse and table-format practice tuned for the production trade-offs senior data engineers actually face.

Practice system design problems →
Practice data processing problems →