A Local Lakehouse on Your Laptop: DuckDB + Iceberg + Trino for Zero-Cloud Dev

local lakehouse means the whole modern data stack — an open table format, object storage, and one or more query engines — running on your machine with nothing rented from a cloud provider. The pieces that used to require an S3 bucket, a Glue catalog, and a Spark cluster now install as a Python wheel, a single binary, and a Docker container. You land Parquet on your own disk, register it as an Apache Iceberg table in a catalog you host, and query it from DuckDB in the same process or from Trino over a socket — all offline, all free, all deterministic.

That shape matters because the slowest part of data engineering is rarely the query; it is the loop. Every change you make to a transform, a partition spec, or a merge key normally has to round-trip through a shared dev warehouse: push, wait for the orchestrator, burn warehouse credits, discover the schema drifted, repeat. A lakehouse you can spin up on a laptop collapses that loop to seconds and makes it reproducible in CI. This guide is a hands-on walkthrough of the four moving parts an interviewer — or your own future test suite — will actually probe: DuckDB reading and writing Parquet, a local Iceberg catalog on the filesystem or MinIO, Trino querying the exact same tables, and partitioning plus compaction done locally. Each section pairs the concepts 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 ETL practice library →, tune scans and file layout on the optimization practice set →, and rehearse table layout on the partitioning practice set →.

---

On this page

• Why a local lakehouse beats cloud dev loops in 2026
• DuckDB: reading and writing Parquet on the local filesystem
• A local Iceberg catalog on the filesystem or MinIO
• Trino querying the same Iceberg tables
• Partitioning and compaction on your laptop
• Cheat sheet — local lakehouse recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why a local lakehouse beats cloud dev loops in 2026

A lakehouse is three swappable layers — table format, storage, engine — and every one of them now runs on a laptop

The one-sentence invariant: a lakehouse is an open table format over plain files, plus a catalog that names those files as tables, plus any engine that speaks the format — and none of those three layers requires a cloud account to run. Once you internalise that separation, "run it locally" stops being a hack and becomes the obvious default for development.

The three layers, decoupled.

• Table format (Apache Iceberg). Iceberg is a specification, not a service: a table is a tree of JSON and Avro metadata files (metadata.json → manifest list → manifests) that point at immutable Parquet data files. Nothing about that tree needs the cloud; it can sit in any directory.
• Storage. The data and metadata files live somewhere with a path. That "somewhere" can be your local filesystem (file:///Users/you/warehouse) or a local S3-compatible server like MinIO (s3://warehouse). The format does not care which.
• Engine. DuckDB, Trino, Spark, and pyiceberg are all clients that read the same Iceberg metadata and Parquet files. You pick the engine per task — DuckDB for a fast in-process scan, Trino for distributed SQL, pyiceberg for writes — and they agree because they share one catalog.

The zero-cloud loop.

• Land Parquet on disk. A generator, a pandas/pyarrow frame, or a COPY statement writes columnar files to a folder. No upload, no credentials.
• Register and query in-process. DuckDB reads those files with zero server; pyiceberg or Trino registers them as an Iceberg table so schema, snapshots, and partitioning become first-class.
• Iterate at memory speed. Because everything is local, a full write-query-verify cycle is milliseconds to seconds, not the minutes a shared warehouse round-trip costs.

Why interviewers and CI both care.

• Determinism. A fixed local dataset plus Iceberg snapshot isolation means a test reads exactly the rows it expects, every run, with no "someone else's job changed the shared table" flakiness.
• Offline and free. CI runners need no cloud secrets, no network egress, and no per-query bill; the lakehouse spins up inside the job and is torn down with it.
• Fidelity. Because the format is identical to production Iceberg, a local test exercises the real partition spec, the real merge semantics, and the real compaction — not a sqlite stand-in that behaves differently.

What an interviewer listens for.

• Do you separate "table format vs storage vs engine" cleanly, rather than treating "the lakehouse" as one product? — senior signal.
• Do you place DuckDB as an in-process reader and Trino as the distributed engine, using the same tables? — required framing.
• Do you reach for a local Iceberg catalog for tests instead of mocking the warehouse? — the whole point of this post.

Worked example — the whole loop in two DuckDB statements

Detailed explanation. Before any catalog or engine setup, the smallest possible proof that a lakehouse loop needs no cloud is a single DuckDB session that writes Parquet to local disk and reads it straight back. DuckDB runs in-process — there is no server to start, no port to bind, no credentials — so this is the innermost loop every later layer wraps.

Question. Using only DuckDB, write two order rows to a local Parquet file and read them back as an aggregate, with no server and no cloud storage.

Input.

order_id	customer	amount	order_date
1	ada	42.50	2026-01-05
2	linus	17.00	2026-02-11

Code.

-- duckdb (in-process; `duckdb` CLI or the Python module — no server)
COPY (
    SELECT * FROM (VALUES
        (1, 'ada',   42.50, DATE '2026-01-05'),
        (2, 'linus', 17.00, DATE '2026-02-11')
    ) AS t(order_id, customer, amount, order_date)
) TO 'lake/orders.parquet' (FORMAT PARQUET);

SELECT customer, sum(amount) AS total
FROM 'lake/orders.parquet'
GROUP BY customer
ORDER BY total DESC;

Step-by-step explanation. COPY (...) TO 'lake/orders.parquet' (FORMAT PARQUET) materialises the query result as a single columnar Parquet file on your local filesystem — DuckDB creates the lake/ directory content and writes typed columns with statistics. The second statement reads that file directly by naming its path in the FROM clause; DuckDB's Parquet reader opens the file, applies the GROUP BY, and returns rows. There is no CREATE TABLE, no connection string, and no object store — the file is the table.

Output.

customer	total
ada	42.50
linus	17.00

Rule of thumb. If a step in your pipeline can be expressed as "read Parquet, transform, write Parquet," it can run locally with zero cloud — the catalog and Trino layers you add later only make those files behave like managed tables.

---

2. DuckDB: reading and writing Parquet on the local filesystem

read_parquet, COPY ... PARTITION_BY, and the iceberg extension make DuckDB the fast local reader and Parquet producer

DuckDB is the engine you reach for first in a local lakehouse because it is an embedded, columnar SQL engine with a first-class Parquet reader and writer and an optional iceberg extension. It has no server, starts in milliseconds, and pushes filters and projections down into Parquet so a scan touches only the bytes it needs.

Reading Parquet.

• Glob paths. read_parquet('lake/orders/**/*.parquet') reads a whole tree; you can also just put the path string in FROM. DuckDB unifies the files into one relation.
• Projection pushdown. Selecting three columns from a hundred-column file reads only those column chunks — Parquet is columnar, and DuckDB honours it.
• Predicate pushdown + statistics. WHERE amount > 100 uses each row group's min/max stats to skip groups that cannot match, so a filtered scan reads a fraction of the file.

Writing Parquet.

• Single file. COPY tbl TO 'out.parquet' (FORMAT PARQUET) writes one file with a chosen compression (CODEC 'zstd').
• Hive-partitioned dataset. COPY tbl TO 'lake/orders' (FORMAT PARQUET, PARTITION_BY (event_date)) writes lake/orders/event_date=2026-02-11/data_0.parquet, encoding the partition value in the directory name.
• Row-group sizing. ROW_GROUP_SIZE controls how many rows per group, which trades scan granularity against file overhead.

The iceberg extension.

• Install once. INSTALL iceberg; LOAD iceberg; adds Iceberg support to a DuckDB session.
• Scan a table. SELECT * FROM iceberg_scan('lake/warehouse/lake.db/orders', allow_moved_paths => true) reads an Iceberg table by pointing at its metadata directory, resolving the current snapshot and its Parquet data files.
• Attach a REST catalog. Recent DuckDB can ATTACH '' AS ice (TYPE ICEBERG, ENDPOINT 'http://localhost:8181/catalog') and then SELECT * FROM ice.lake.orders, so DuckDB reads the same catalog Trino uses. Iceberg writes from DuckDB are still newer/preview, so most local setups author tables with pyiceberg or Trino and use DuckDB as the fast reader.

Worked example — write a partitioned Parquet dataset, then read one partition

Detailed explanation. The everyday DuckDB pattern is to write a Hive-partitioned Parquet directory and then read back only the partitions you need. Because the partition value is encoded in the folder path, a filter on that column lets DuckDB skip whole directories without opening their files — the local equivalent of partition pruning in a cloud warehouse.

Question. Write three orders partitioned by event_date, then read back only 2026-02-11 and show that DuckDB pruned the other partition.

Input.

order_id	amount	event_date
10	42.5	2026-01-05
11	17.0	2026-02-11
12	9.9	2026-02-11

Code.

-- write a Hive-partitioned dataset
COPY (
    SELECT * FROM (VALUES
        (10, 42.5, DATE '2026-01-05'),
        (11, 17.0, DATE '2026-02-11'),
        (12,  9.9, DATE '2026-02-11')
    ) AS t(order_id, amount, event_date)
) TO 'lake/orders' (FORMAT PARQUET, PARTITION_BY (event_date));

-- read back only one partition
SELECT count(*) AS rows, sum(amount) AS revenue
FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning => true)
WHERE event_date = DATE '2026-02-11';

Step-by-step explanation. The COPY ... PARTITION_BY (event_date) writes two directories — event_date=2026-01-05/ and event_date=2026-02-11/ — each holding the rows for that date. The read uses hive_partitioning => true, so DuckDB reconstructs event_date from the directory names as a real column. The WHERE event_date = DATE '2026-02-11' is evaluated against those directory values first, so DuckDB never opens the January file — it prunes the partition before any Parquet is read.

Output.

rows	revenue
2	26.9

Rule of thumb. Partition by the column you filter on most (usually a date), read with hive_partitioning => true, and DuckDB turns a directory layout into free partition pruning — the same idea Iceberg formalises with hidden partitioning.

DuckDB interview question on Parquet pushdown

Question. You have a directory lake/events of Parquet files partitioned by event_date, one hundred columns wide and billions of rows. An analyst needs sum(amount) for a single month, 2026-02. Write the DuckDB query and explain, concretely, which bytes DuckDB avoids reading and why.

Solution Using hive partitioning with projection and predicate pushdown

Code.

INSTALL parquet;   -- bundled; explicit for clarity
LOAD parquet;

SELECT sum(amount) AS feb_revenue
FROM read_parquet('lake/events/**/*.parquet', hive_partitioning => true)
WHERE event_date >= DATE '2026-02-01'
  AND event_date <  DATE '2026-03-01';

Step-by-step trace.

stage	what DuckDB inspects	what it skips
1. partition prune	directory names event_date=...	every folder outside Feb 2026
2. row-group stats	Feb files' min/max for event_date, amount	row groups whose range misses the filter
3. projection	only the amount (and partition) column chunk	the other 98 columns' chunks
4. scan	matching amount chunks in surviving row groups	everything else on disk

1. DuckDB resolves hive_partitioning => true first, reading event_date from the folder path, so the month predicate eliminates all non-February directories before a single Parquet byte is opened.
2. Inside the surviving files, DuckDB reads each row group's footer statistics and drops groups whose event_date min/max cannot satisfy the range.
3. Because the query only references amount, DuckDB's projection pushdown reads just that column's chunks — the other columns' bytes are never fetched from disk.
4. Only the qualifying amount chunks are decompressed and summed, so the scan cost is proportional to one month of one column, not the whole dataset.

Output:

feb_revenue
128934.50

Why this works — concept by concept:

• Partition pruning — encoding event_date in the directory path lets DuckDB eliminate whole folders from the plan using metadata alone, turning a full scan into a targeted one.
• Row-group statistics — Parquet stores per-group min/max, so even within a partition DuckDB skips groups that cannot match the predicate; this is why sorted or clustered data scans faster.
• Projection pushdown — columnar storage means selecting one column reads one column's bytes; a hundred-column table costs the same as a one-column table for this query.
• In-process execution — no network hop to a storage service means the only cost is local disk I/O for the surviving bytes, which is what makes the loop feel instant.
• Cost — I/O is O(qualifying row groups × selected columns), typically a tiny fraction of the dataset, versus O(all files × all columns) for a naive full read.

ETL
Topic — etl
Local extract-and-load Parquet pipeline problems

Practice →

Pipelines Topic — pipelines Pipeline-design and file-layout problems

Practice →

---

3. A local Iceberg catalog on the filesystem or MinIO

The catalog is what turns a folder of Parquet into a table — SQLite for pure-filesystem, a REST catalog for sharing

A pile of Parquet files is not a table; it becomes one only when a catalog records the current metadata pointer, the schema, the snapshots, and the partition spec. The catalog is the single piece that makes DuckDB, Trino, and pyiceberg agree on what "the orders table" is. Locally you have two good choices, and picking between them is the decision an interviewer probes.

What a catalog actually stores.

• The metadata pointer. For each table, the catalog holds the path to the current metadata.json. That file references the manifest list, which references manifests, which reference the Parquet data files — the whole Iceberg tree hangs off the pointer the catalog owns.
• Atomic commits. A write produces a new metadata.json; the commit is the catalog atomically swapping the pointer from the old file to the new one. That swap is why Iceberg gives snapshot isolation — readers see the old snapshot until the pointer moves.
• Namespaces and identity. The catalog namespaces tables (lake.orders) and is the authority every engine consults, so "same table" means "same catalog entry," not "same folder."

Option A — SqlCatalog on SQLite (pure filesystem).

• Zero infrastructure. pyiceberg's SqlCatalog stores the pointers in a local SQLite file and writes data + metadata to a file:// warehouse directory. No server, no Docker — perfect for a single-process test or a notebook.
• Trade-off. SQLite is single-writer and not a network service, so it is ideal when one process owns the catalog but awkward when several tools must share it live.

Option B — a REST catalog (lakekeeper / iceberg-rest) on MinIO.

• A shared metadata plane. The Iceberg REST catalog is a standard HTTP API; run lakekeeper (a Rust REST catalog) or the reference iceberg-rest image in Docker and every engine points at one URL. This is how you get DuckDB, Trino, and pyiceberg reading and writing the same table.
• MinIO as local S3. MinIO is an S3-compatible object server you run in Docker (minio server /data). Point the warehouse at s3://warehouse with the MinIO endpoint, path-style access, and dummy minioadmin keys — the exact S3 code path you use in production, exercised offline.

Worked example — create an Iceberg table with pyiceberg on a SQLite catalog

Detailed explanation. The fastest way to get a real Iceberg table on a laptop is pyiceberg's SqlCatalog backed by SQLite, writing to a file:// warehouse. You create a namespace, define a schema (or hand it a PyArrow table and let it infer), create the table, and append an Arrow batch. Everything lands on local disk as a proper Iceberg tree.

Question. Using pyiceberg, create a lake.orders Iceberg table on a local SQLite catalog and append two rows, with no cloud and no server.

Input. A two-row PyArrow table of orders.

Code.

import pyarrow as pa
from pyiceberg.catalog.sql import SqlCatalog

catalog = SqlCatalog(          # pointers in SQLite, data in a local folder
    "local",
    uri="sqlite:///warehouse/catalog.db",
    warehouse="file://warehouse",
)

catalog.create_namespace_if_not_exists("lake")

rows = pa.table({
    "order_id": [1, 2],
    "customer": ["ada", "linus"],
    "amount":   [42.50, 17.00],
})

table = catalog.create_table_if_not_exists("lake.orders", schema=rows.schema)
table.append(rows)

print(table.scan().to_arrow().num_rows)   # -> 2

Step-by-step explanation. SqlCatalog(...) opens (or creates) warehouse/catalog.db as the pointer store and sets the warehouse root to the local warehouse/ folder. create_namespace_if_not_exists("lake") registers the namespace. create_table_if_not_exists("lake.orders", schema=rows.schema) writes the first metadata.json under warehouse/lake.db/orders/metadata/ and records its path in SQLite. table.append(rows) writes a Parquet data file, a new manifest and manifest list, a new metadata.json, and atomically updates the SQLite pointer — one Iceberg snapshot. The final scan() reads the current snapshot back as Arrow.

Output.

what pyiceberg created	value
catalog store	warehouse/catalog.db (SQLite)
table root	warehouse/lake.db/orders/
data files	one Parquet file under .../data/
snapshots	1 (the append)
rows in current snapshot	2

Rule of thumb. Use SqlCatalog + SQLite when one process owns the table (unit tests, notebooks); switch to a REST catalog the moment a second engine needs to read or write the same table live.

Iceberg interview question on catalog choice

Question. On a laptop, a pyiceberg job writes lake.orders and a Trino instance must query it at the same time. A teammate suggests "just point both at the same warehouse folder — skip the catalog." Why is that wrong, and what do you set up instead?

Solution Using a shared REST catalog backed by MinIO

Code.

services:                      # docker-compose.yml — local control + storage plane
  minio:                       # local S3
    image: minio/minio
    command: server /data --console-address ":9001"
    environment:
      MINIO_ROOT_USER: minioadmin
      MINIO_ROOT_PASSWORD: minioadmin
    ports: ["9000:9000", "9001:9001"]

  lakekeeper:                  # Iceberg REST catalog
    image: quay.io/lakekeeper/catalog:latest
    environment:
      LAKEKEEPER__PG_DATABASE_URL_READ_WRITE: postgres://catalog:catalog@pg/catalog
    ports: ["8181:8181"]
    depends_on: [pg, minio]

  pg:
    image: postgres:16
    environment:
      POSTGRES_USER: catalog
      POSTGRES_PASSWORD: catalog
      POSTGRES_DB: catalog

from pyiceberg.catalog.rest import RestCatalog   # same URL for writer + reader

catalog = RestCatalog(
    "shared",
    uri="http://localhost:8181/catalog",
    warehouse="demo",
    **{
        "s3.endpoint": "http://localhost:9000",
        "s3.access-key-id": "minioadmin",
        "s3.secret-access-key": "minioadmin",
        "s3.path-style-access": "true",
    },
)

Step-by-step trace.

approach	who moves the metadata pointer	concurrent reader sees
shared folder, no catalog	nobody — no atomic commit	half-written metadata, races
REST catalog (lakekeeper)	the catalog, atomically	old snapshot until commit lands
REST catalog + MinIO	catalog for metadata, S3 for files	consistent snapshot every read

1. Iceberg's correctness depends on an atomic pointer swap to the current metadata.json; a bare folder has no component that performs that swap, so two processes writing or reading mid-commit can see a partial or inconsistent tree.
2. A REST catalog (lakekeeper) owns that swap: the writer's append is only visible once the catalog commits the new pointer, giving readers snapshot isolation instead of a race.
3. Pointing the warehouse at MinIO (s3.path-style-access=true, endpoint :9000) exercises the real S3 code path locally, so the data files are addressed exactly as they would be in the cloud.
4. Because every engine — pyiceberg writer, Trino reader, DuckDB ATTACH — hits the same URL, "the same table" is defined by one catalog entry, not by whoever happened to look at the folder.

Output:

component	local endpoint	role
lakekeeper	http://localhost:8181/catalog	atomic metadata pointer
MinIO	http://localhost:9000 (s3://warehouse)	data + metadata files
result	one lake.orders table	shared, snapshot-isolated

Why this works — concept by concept:

• Catalog as source of truth — the catalog, not the folder, defines a table; only it can atomically move the current-metadata pointer that every engine reads.
• Atomic commit — the pointer swap is the transaction boundary; without it there is no isolation, and concurrent read/write over a shared folder corrupts what readers see.
• REST is engine-neutral — the Iceberg REST protocol is a standard, so DuckDB, Trino, and pyiceberg all speak it and converge on identical table state.
• MinIO fidelity — running the real S3 API locally means the storage layer behaves like production (path-style, keys, endpoints), so tests catch S3-specific bugs offline.
• Cost — a commit is O(1) metadata pointer update plus O(changed files) manifest writes, independent of table size, which is why commits stay cheap as data grows.

Warehouse
Topic — data-warehouse
Lakehouse and warehouse-modelling problems

Practice →

ETL Topic — etl Catalog-driven load and register problems

Practice →

---

4. Trino querying the same Iceberg tables

Trino's iceberg connector reads the catalogued table directly — same metadata, distributed SQL, time travel

Once a table lives in a REST catalog, Trino queries it with its iceberg connector — no copy, no import, just a catalog properties file that points Trino at the same REST endpoint and the same MinIO storage the writer used. This is what makes the local lakehouse a lakehouse and not just "DuckDB on Parquet": full ANSI SQL, joins across tables, snapshot time travel, and DDL, all over the exact bytes pyiceberg wrote.

Wiring Trino to the catalog.

• Catalog properties file. Drop etc/catalog/iceberg.properties with connector.name=iceberg and iceberg.catalog.type=rest; Trino exposes it as the iceberg catalog you reference in SQL.
• REST endpoint. iceberg.rest-catalog.uri=http://lakekeeper:8181/catalog and iceberg.rest-catalog.warehouse=demo tell Trino which catalog and warehouse to use.
• Local S3 filesystem. fs.native-s3.enabled=true, s3.endpoint=http://minio:9000, s3.path-style-access=true, plus the minioadmin keys, let Trino read the data files from MinIO exactly as pyiceberg wrote them.

Querying and writing.

• Plain SQL. SELECT * FROM iceberg.lake.orders reads the current snapshot; joins, window functions, and aggregates all work.
• CTAS and inserts. CREATE TABLE iceberg.lake.daily AS SELECT ... and INSERT INTO create and grow Iceberg tables from Trino, committed through the same catalog.
• Metadata tables. SELECT * FROM iceberg.lake."orders$snapshots" and "...$files" expose snapshots, manifests, and file lists for debugging.

Time travel and isolation.

• By timestamp. SELECT * FROM iceberg.lake.orders FOR TIMESTAMP AS OF TIMESTAMP '2026-02-01 00:00:00 UTC' reads the snapshot current at that instant.
• By snapshot id. FOR VERSION AS OF <snapshot_id> pins an exact snapshot — invaluable for a deterministic test that must read a known state.
• Snapshot isolation. A running SELECT sees one snapshot even as a writer commits new ones, so readers never see a half-written table.

Worked example — point Trino at the shared catalog and query it

Detailed explanation. With lakekeeper and MinIO running, Trino needs exactly one properties file to see the table pyiceberg created. After that, SHOW TABLES and SELECT work as if the table were native, because to Trino it is native Iceberg.

Question. Write the Trino iceberg.properties for the local REST catalog + MinIO, then a query that returns per-customer revenue from lake.orders.

Input. The lake.orders table from Section 3 (columns order_id, customer, amount), already committed to the catalog.

Code.

connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=http://lakekeeper:8181/catalog
iceberg.rest-catalog.warehouse=demo
fs.native-s3.enabled=true
s3.endpoint=http://minio:9000
s3.region=us-east-1
s3.path-style-access=true
s3.aws-access-key=minioadmin
s3.aws-secret-key=minioadmin

-- trino
SELECT customer, sum(amount) AS revenue
FROM iceberg.lake.orders
GROUP BY customer
ORDER BY revenue DESC;

Step-by-step explanation. The properties file registers a Trino catalog named iceberg whose type=rest makes Trino fetch table metadata from lakekeeper at :8181; the fs.native-s3 and s3.* keys let Trino open the Parquet data files from MinIO with path-style addressing. In SQL, iceberg.lake.orders resolves as catalog.schema.table — Trino asks lakekeeper for the current snapshot, reads the manifests, and scans the data files. The GROUP BY runs in Trino's engine and returns aggregated rows.

Output.

customer	revenue
ada	42.50
linus	17.00

Rule of thumb. Trino needs no data movement to read an Iceberg table — one properties file pointing at the shared catalog and storage is the entire integration, because the table format is the contract.

Trino interview question on cross-engine consistency and time travel

Question. pyiceberg wrote lake.orders at 10:00 (snapshot A) and appended more rows at 11:00 (snapshot B). A test must assert Trino reads exactly snapshot A's three rows, regardless of later writes. Write the query and explain why it is deterministic.

Solution Using Iceberg snapshot time travel in Trino

Code.

-- find the snapshots (newest first)
SELECT snapshot_id, committed_at
FROM iceberg.lake."orders$snapshots"
ORDER BY committed_at DESC;

-- pin the exact snapshot the test expects (snapshot A = 10:00)
SELECT count(*) AS rows_at_A
FROM iceberg.lake.orders
FOR TIMESTAMP AS OF TIMESTAMP '2026-02-01 10:30:00 UTC';

-- or pin by explicit id for full determinism
SELECT count(*) AS rows_at_A
FROM iceberg.lake.orders
FOR VERSION AS OF 4823174097 /* snapshot A id */;

Step-by-step trace.

clock	snapshot current	FOR TIMESTAMP AS OF 10:30 resolves to	rows seen
10:00	A committed	—	3
11:00	B committed	still A (10:30 < 11:00)	3
now	B is latest	A, because 10:30 predates B	3

1. The orders$snapshots metadata table lists every commit with its snapshot_id and committed_at, so the test can discover or hard-code the snapshot it wants.
2. FOR TIMESTAMP AS OF TIMESTAMP '... 10:30 ...' tells Trino to resolve the snapshot that was current at 10:30 — that is snapshot A, because B did not commit until 11:00.
3. FOR VERSION AS OF <snapshot_id> skips the timestamp resolution entirely and reads an exact snapshot, which is the most deterministic form for CI.
4. Because Iceberg snapshots are immutable, snapshot A's three rows can never change no matter how many later appends land, so the assertion holds on every run.

Output:

rows_at_A
3

Why this works — concept by concept:

• Immutable snapshots — every commit creates a new snapshot and never mutates an old one, so a pinned snapshot is a frozen, reproducible view of the table.
• Time travel — FOR TIMESTAMP AS OF / FOR VERSION AS OF let a query address a past state by clock or id, which is exactly what a deterministic test needs.
• Shared catalog — Trino resolves the snapshot through the same catalog pyiceberg wrote to, so "snapshot A" means the same thing to both engines.
• Snapshot isolation — a reader is bound to one snapshot for the whole query, so concurrent writes never leak partial rows into the result.
• Cost — time travel is O(1) to resolve the pointer plus O(scanned files) for that snapshot; it reads an old state at no extra bookkeeping cost because the files already exist.

Optimization
Topic — optimization
Query-planning and scan-optimization problems

Practice →

Partitioning Topic — partitioning Partition-pruning and table-layout problems

Practice →

---

5. Partitioning and compaction on your laptop

Hidden partitioning plus EXECUTE optimize fixes the small-files problem locally — the same maintenance you run in production

The last piece that makes a local lakehouse behave like production is table maintenance: partitioning data so scans prune, and compacting the many small files that iterative local writes produce. Iceberg does both with declarative DDL and table procedures, and you run the identical operations locally that you would run against a cloud warehouse — so a test can prove your partition spec and compaction actually help.

Hidden partitioning.

• Declare a spec, not a column. WITH (partitioning = ARRAY['day(order_ts)']) partitions by a derived value; you query on order_ts and Iceberg prunes without you materialising a day column. That is "hidden" partitioning.
• Transforms. day(ts), month(ts), hour(ts), bucket(N, id), and truncate(N, col) are the built-in partition transforms; bucket(16, user_id) spreads a high-cardinality key into 16 even buckets.
• Evolvable. You can change the partition spec later without rewriting history — old data keeps its old spec, new data uses the new one.

The small-files problem.

• Why it appears. Each write commits at least one data file per partition; a test loop or a streaming append produces thousands of tiny Parquet files, and every query pays per-file open and metadata overhead.
• The symptom. Scans that should be fast crawl, because the engine spends its time opening files and reading footers rather than scanning data.

Compaction and housekeeping (Trino procedures).

• Compact. ALTER TABLE iceberg.lake.orders EXECUTE optimize rewrites small files into fewer, right-sized ones; EXECUTE optimize(file_size_threshold => '128MB') only compacts files below the threshold.
• Expire snapshots. ALTER TABLE ... EXECUTE expire_snapshots(retention_threshold => '7d') drops old snapshots and the data files only they referenced, reclaiming space.
• Remove orphans. ALTER TABLE ... EXECUTE remove_orphan_files(retention_threshold => '7d') deletes files no live snapshot references — the leftovers of failed writes.

Why it matters even locally.

• CI stays fast and deterministic. Compacting fixtures keeps test scans quick; expiring snapshots keeps the local warehouse from growing unbounded across CI runs.
• You test the maintenance itself. Running optimize locally lets you assert file counts dropped and query time fell — you are validating the production maintenance job, not guessing.

Worked example — a partitioned table, then compact it

Detailed explanation. The clearest local demonstration is to create a partitioned Iceberg table in Trino, write to it a few times so it accumulates small files, then run EXECUTE optimize and watch the file count collapse while the row count stays identical. Nothing leaves your laptop.

Question. Create lake.events partitioned by day(event_ts) and bucket(8, user_id), then compact it after several small appends. Show the file count before and after.

Input. Three appends of ~1,000 rows each, landing many small files across partitions.

Code.

-- trino: create with hidden partitioning
CREATE TABLE iceberg.lake.events (
    user_id   BIGINT,
    event_ts  TIMESTAMP(6),
    amount    DOUBLE
)
WITH (
    partitioning = ARRAY['day(event_ts)', 'bucket(8, user_id)'],
    format = 'PARQUET'
);

-- ... three separate INSERT INTO iceberg.lake.events SELECT ... appends ...

-- inspect small files, then compact
SELECT count(*) AS data_files FROM iceberg.lake."events$files";
ALTER TABLE iceberg.lake.events EXECUTE optimize(file_size_threshold => '128MB');
SELECT count(*) AS data_files FROM iceberg.lake."events$files";

Step-by-step explanation. The partitioning = ARRAY['day(event_ts)', 'bucket(8, user_id)'] clause records a partition spec of a date-day transform plus an 8-way hash bucket, so data is physically grouped by day and user-bucket. Each INSERT commits at least one file per touched partition, so three appends across many partitions leave lots of small files, which events$files counts. EXECUTE optimize(file_size_threshold => '128MB') rewrites all files below 128 MB within each partition into fewer, larger files in a new snapshot, without changing any row. The second count shows the collapse.

Output.

stage	data files	rows
after 3 appends	42	3000
after EXECUTE optimize	9	3000

Rule of thumb. Partition by what you filter on, bucket high-cardinality join keys, and schedule EXECUTE optimize after bursty writes — locally you can prove the file count dropped, so you ship the maintenance job with confidence.

Iceberg interview question on local compaction and snapshot hygiene

Question. A local CI job writes a fixture in a loop and leaves 10,000 tiny files plus dozens of stale snapshots; Trino scans have become slow and the warehouse folder keeps growing between runs. Fix both the scan speed and the growth, and justify why this matters in CI, not just production.

Solution Using EXECUTE optimize plus expire_snapshots

Code.

-- 1) compact small files into right-sized ones (new snapshot)
ALTER TABLE iceberg.lake.events
EXECUTE optimize(file_size_threshold => '128MB');

-- 2) drop snapshots older than the retention window and their
--    now-unreferenced data files
ALTER TABLE iceberg.lake.events
EXECUTE expire_snapshots(retention_threshold => '7d');

-- 3) sweep files no live snapshot references (failed-write leftovers)
ALTER TABLE iceberg.lake.events
EXECUTE remove_orphan_files(retention_threshold => '7d');

Step-by-step trace.

step	procedure	effect on files	effect on snapshots
1	optimize	10,000 small → ~40 sized	+1 (compaction snapshot)
2	expire_snapshots	deletes files only old snapshots used	drops stale snapshots
3	remove_orphan_files	deletes unreferenced leftovers	none

1. EXECUTE optimize rewrites the 10,000 tiny files into a handful of ~128 MB files, so every subsequent scan opens dozens of files instead of ten thousand — the scan-speed fix.
2. Compaction itself adds a new snapshot and leaves the old small files still referenced by earlier snapshots, so space does not drop yet; that is expected.
3. EXECUTE expire_snapshots removes snapshots past the retention window and physically deletes the data files that only those expired snapshots referenced — this is where the folder finally shrinks.
4. EXECUTE remove_orphan_files deletes files on storage that no live snapshot points to at all (typically from interrupted writes), reclaiming the last of the wasted space.

Output:

metric	before	after maintenance
data files	10,000	~40
live snapshots	60+	a few (within 7d)
warehouse size	bloated	lean
Trino scan time	slow	fast

Why this works — concept by concept:

• Compaction — optimize trades a one-time rewrite for cheap steady-state scans by replacing many small files with few right-sized ones, eliminating per-file open overhead.
• Snapshot retention — old snapshots pin their data files alive; expire_snapshots is what actually frees space, because compaction alone only adds a snapshot.
• Orphan cleanup — remove_orphan_files reclaims files no snapshot references, which accumulate from failed or interrupted local writes during a test loop.
• CI relevance — a fixture that bloats every run makes CI slower and flakier over time; running the real maintenance locally keeps runs fast and validates the production job.
• Cost — optimize is O(rewritten bytes) once; the payoff is O(files) reduced per query forever after, and the housekeeping procedures are O(expired files) to delete.

Partitioning
Topic — partitioning
Partition-spec and file-sizing problems

Practice →

Optimization Topic — optimization Compaction and scan-cost optimization problems

Practice →

---

Cheat sheet — local lakehouse recipes

DuckDB — write and read Parquet.

COPY tbl TO 'lake/orders' (FORMAT PARQUET, PARTITION_BY (event_date));
SELECT * FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning => true)
WHERE event_date = DATE '2026-02-11';

DuckDB — read an Iceberg table.

INSTALL iceberg; LOAD iceberg;
SELECT * FROM iceberg_scan('warehouse/lake.db/orders', allow_moved_paths => true);
-- or attach the shared REST catalog:
ATTACH '' AS ice (TYPE ICEBERG, ENDPOINT 'http://localhost:8181/catalog');
SELECT * FROM ice.lake.orders;

pyiceberg — create + append on a SQLite catalog.

from pyiceberg.catalog.sql import SqlCatalog
cat = SqlCatalog("local", uri="sqlite:///warehouse/catalog.db", warehouse="file://warehouse")
cat.create_namespace_if_not_exists("lake")
t = cat.create_table_if_not_exists("lake.orders", schema=arrow_table.schema)
t.append(arrow_table)

Trino — Iceberg REST catalog on MinIO.

connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=http://lakekeeper:8181/catalog
iceberg.rest-catalog.warehouse=demo
fs.native-s3.enabled=true
s3.endpoint=http://minio:9000
s3.path-style-access=true

Trino — partitioned table + time travel.

CREATE TABLE iceberg.lake.events (user_id BIGINT, event_ts TIMESTAMP(6), amount DOUBLE)
WITH (partitioning = ARRAY['day(event_ts)', 'bucket(8, user_id)']);
SELECT * FROM iceberg.lake.events FOR TIMESTAMP AS OF TIMESTAMP '2026-02-01 10:30:00 UTC';

Trino — compaction and housekeeping.

ALTER TABLE iceberg.lake.events EXECUTE optimize(file_size_threshold => '128MB');
ALTER TABLE iceberg.lake.events EXECUTE expire_snapshots(retention_threshold => '7d');
ALTER TABLE iceberg.lake.events EXECUTE remove_orphan_files(retention_threshold => '7d');

Docker — the storage + catalog plane.

docker run -p 9000:9000 -p 9001:9001 minio/minio server /data --console-address ":9001"
docker run -p 8181:8181 quay.io/lakekeeper/catalog:latest   # Iceberg REST catalog

Engine roles at a glance.

Component	Role in the local lakehouse
DuckDB	in-process reader + Parquet producer
pyiceberg	Python writer, catalog + table author
lakekeeper / iceberg-rest	shared Iceberg REST catalog
MinIO	local S3-compatible object storage
Trino	distributed SQL, DDL, compaction, time travel

---

Frequently asked questions

What is a local lakehouse?

A local lakehouse is a full lakehouse stack — an open table format (Apache Iceberg), object or file storage, and query engines — running entirely on your own machine with no cloud account. You land Parquet files on disk or in a local MinIO bucket, register them as Iceberg tables in a catalog you host (a SQLite SqlCatalog or a REST catalog like lakekeeper), and query them from DuckDB or Trino. Because the table format is identical to production Iceberg, local dev and CI exercise the real semantics, not a stand-in.

Can DuckDB write Iceberg tables?

DuckDB's iceberg extension has mature read support — iceberg_scan(...) reads a table by its metadata path, and recent versions can ATTACH an Iceberg REST catalog and query its tables. Iceberg write support from DuckDB is newer and still maturing, so most local setups author and mutate Iceberg tables with pyiceberg or Trino and use DuckDB as a fast reader and a Parquet producer. DuckDB writing plain Parquet, by contrast, is fully supported and is how you feed data into the lakehouse.

Do I need MinIO, or is the local filesystem enough?

For a single process — a unit test or a notebook — a file:// warehouse with a SQLite SqlCatalog is enough and needs no Docker at all. Add MinIO when you want to exercise the real S3 code path (path-style addressing, endpoints, credentials) or when multiple engines must share storage. MinIO is S3-compatible, so the same s3:// configuration you test locally works unchanged against real S3 in production.

How do DuckDB, Trino, and pyiceberg share the same Iceberg table?

They share a catalog. The catalog holds the current metadata pointer for each table, and every engine that points at the same catalog URL sees the same table state. Run an Iceberg REST catalog (lakekeeper or the reference iceberg-rest) and configure pyiceberg's RestCatalog, Trino's iceberg.catalog.type=rest, and DuckDB's ATTACH ... (TYPE ICEBERG, ENDPOINT ...) to the one URL. Sharing a folder without a catalog does not work, because only the catalog performs the atomic commit that gives snapshot isolation.

How do I compact small files in a local Iceberg table?

Run ALTER TABLE iceberg.lake.events EXECUTE optimize(file_size_threshold => '128MB') in Trino to rewrite small files into fewer right-sized ones. Follow it with EXECUTE expire_snapshots(retention_threshold => '7d') to drop old snapshots and reclaim the space their data files held, and EXECUTE remove_orphan_files(...) to sweep leftovers from failed writes. Compaction alone does not shrink storage — you must expire snapshots for the old files to be deleted.

Why run a lakehouse locally instead of a dev cloud account?

Speed, determinism, and cost. A local loop of write-query-verify is seconds, not the minutes a shared warehouse round-trip costs, and it needs no credentials or network. Iceberg snapshot isolation plus a fixed local dataset make tests reproducible, so CI stops being flaky from shared-state collisions. And because CI runners spin the lakehouse up inside the job, there is no cloud bill, no secret management, and no egress — while still testing the exact table format, partitioning, and compaction you run in production.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every local-lakehouse idea above, from DuckDB Parquet pushdown to the shared Iceberg REST catalog, Trino time travel, and `EXECUTE optimize` compaction, maps to a hands-on practice room where you build the load against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you make this scan prune and this table stay compact?" holds up under a senior interviewer's depth probes.

Practice ETL problems now →
Optimization drills →