Lakehouse

What it is#

The lakehouse stores table data as zstd-compressed Parquet in your object storage and keeps the transactional catalog — schemas, table manifests, snapshots — in a Postgres the deployment provides. Every request opens an ephemeral DuckDB, attaches that shared catalog and storage, runs, and closes. Nothing lives on a single machine's disk, so the lakehouse scales exactly like the app tier.

• Warehouse SQL — full DuckDB: joins, window functions, CTEs, SUMMARIZE, vectorised columnar execution.
• ACID rows — INSERT/UPDATE/DELETE/MERGE commit through the catalog; every commit is a snapshot you can time-travel-query.
• NL→SQL — ask in plain language; the draft is shown for review and runs through the same governed path as typed SQL.
• Imports — any platform dataset becomes a lakehouse table with inferred types.

Working in the Lakehouse#

1. 1

   Create a schema

   Schemas are the unit of ownership and sharing. New schema in the sidebar; share it from Admin → IAM.
2. 2

   Add tables

   Define columns, or import a platform dataset (uploads, prep outputs, connector-synced tables, samples).
3. 3

   Query

   Type SQL (Ctrl+Enter runs) or ask in plain language. Results are a virtualized grid with CSV export.
4. 4

   Inspect and time-travel

   The table view shows columns, a live preview, and the snapshot history — click a version to query the table as it was.

Querying your data lake#

Mount data lake turns a crawled object-storage source into a read-only schema — one view per dataset, reading the files in place. Nothing is copied, and you can join lake files against lakehouse tables in one query (FROM analytics.orders_rollup r LEFT JOIN raw_lake.orders o …). The file-reading calls live inside server-authored view bodies, so user SQL never names a path, and each mount's credential is scoped to its own bucket. Mounts are read-only — writes are refused with a message saying why. Crawl the source in the Data Catalog first; that is where the dataset list comes from.

Making queries fast#

Partition on a table's toolbar picks up to four columns; DuckLake then writes one file set per partition value, so a query filtering on those columns opens only the matching files. Pick columns with few distinct values — a date, a region, a tenant — never a high-cardinality id, which writes a file per row and makes everything slower. It applies to files written from then on, so run maintenance or rewrite the table to re-lay what already exists. The badge reads back from DuckLake's own catalog, so partitioning applied from the SQL editor shows up here too.

Partitioning decides which file a new row goes to; clustering decides the order of what is already there. Layout on a table's toolbar shows what DuckLake's own per-file statistics say — for every column, how many of the table's files a lookup on it opens — beside which columns this week's queries filtered on, and advises: cluster by the column people filter on whose files overlap, compact when files are small, rewrite again when files landed since the last rewrite. Rewrite now puts the files in key order — rows ranged on the first key at row-count quantiles, one range per target-sized file, each range sorted by all keys — in one transaction that rolls back whole on any failure, with time travel to the previous snapshot intact. A filter on the key then opens only the files whose range matches, and the toast says how many it opened before and after. The target file size is per table (default LAKEHOUSE_CLUSTER_FILE_BYTES, else 128 MiB, never capped). Keep clustered hands the table to the hourly maintenance pass, which rewrites it again when files were written since and leaves it out of file merging, since merging would fold the ranges back together.

A repeated SELECT is served from memory and marked cached in the toolbar and in history. The cache key includes the catalog snapshot id, so any write invalidates it automatically — no TTL to tune, and no way to read a stale answer after an insert. It is keyed per user and consulted after the access check, so a revoked grant cannot read a warm result. The cache lives in each replica's memory: behind a load balancer the same query may be a hit on one replica and a miss on another, which changes timing but never the answer.

Explain runs EXPLAIN ANALYZE and shows the plan the engine chose alongside what it cost — rows scanned, engine time, rows returned. Rows scanned is the number to watch: if a filtered query scans close to the whole table, the filter isn't matching a partition key. Only SELECTs can be profiled, because EXPLAIN ANALYZEexecutes the statement.

Reading remote Parquet costs a footer round trip per file per query, so the engine caches that metadata — measured about 20% faster across different queries over the same files, the case the result cache doesn't cover. It stays correct because DuckLake never rewrites a data file: a write adds new paths and the catalog decides which are live, so what sits behind a cached path can't change. Files behind a lake mount can be overwritten, and the engine validates those.

Memory and threads are editable under Admin → Developer runtime → Data platform, which wins over the environment variables below — so a running deployment can be retuned without a redeploy, and neither is capped by the app.

Each engine gets LAKEHOUSE_MEMORY_LIMIT (default 2GB) and spills past it to disk bounded by LAKEHOUSE_SPILL_LIMIT (default 20GB). Both are set together deliberately: with a memory limit and no spill directory, DuckDB fails a query rather than spilling, so a large GROUP BY would error instead of just running slower. Set the memory limit to roughly half a container's RAM, and give each replica real scratch disk rather than a tmpfs.

Materialized views#

A query whose answer is worth keeping becomes a table. Save as view in the query editor stores the result and rebuilds it on a schedule — manual, hourly, daily or weekly — so a dashboard reads stored rows instead of recomputing. What you get is an ordinary lakehouse table: queryable, joinable, partitionable, and governed by the same chokepoint as everything else.

Three properties decide how it behaves when something goes wrong. A rebuild is one commit (CREATE OR REPLACE TABLE … AS query), so anyone querying mid-rebuild sees the old rows or the new ones, never a half-built table. A failed rebuild keeps the previous data — stale rows you can see and diagnose beat an empty table — and the error is recorded on the view. And the definition is re-checked at every rebuild, not just when it was saved, so a grant revoked since then stops the refresh and a definition edited into a write is refused rather than executed.

Rebuilds run as the view's owner, since a schedule has no session behind it, and ride the same sweep as BI refreshes and ETL schedules — with the same compare-and-set claim, so every replica can run the sweep without any view being rebuilt twice. Removing a view forgets the definition and leaves the table: deleting your data because you removed a schedule would be the wrong default.

Row and column security#

A grant gives someone a whole schema; a policy narrows what they see inside one table. The Security button on a table sets which rows a reader gets and which column values are hidden — blanked, or scrambled to a digest that stays groupable and joinable but unreadable. Two placeholders make one rule serve everyone: @me becomes the reader's email and @user_id their id, so owner_email = @me gives each person exactly their own rows.

Only the schema owner sees the button or the rule — showing a reader the filter would tell them precisely what they are denied. The owner is never filtered themselves, because a rule its author can't see through would be impossible to check. And a policed table is read-only for everyone else: a reader who sees part of a table must not be able to update or delete the parts hidden from them.

Enforcement rewrites the reader's SELECT before it runs, turning each reference to a policed table into a subquery that carries the filter and the masks. The rewrite is applied to the AST DuckDB itself produced, not to the SQL text — text rewriting can be defeated by comments, casing, aliases or a CTE, while the parser sees through all of them. If the rewrite can't be completed, the query is refused rather than run unfiltered. Filters are checked against the real table when you save, so a typo surfaces then rather than by blocking every reader at once.

Policies by tag#

A policy names one table; a tag policy is one rule written once, applied wherever the tag is. Tag columns and tables in the Data Catalog (a column's tags sit in the asset drawer's Columns table and survive re-crawls), then under the lakehouse page's Tag policies button say that every column tagged pii is blanked or scrambled, or that every table tagged restricted shows only rows where a condition holds. At read time the rules are folded into the same per-table policy the rewrite enforces — masks union, filters AND, a blank beats a scramble — so a table with no policy of its own but a tagged column gets one. The owner is never filtered.

Concurrent writes#

Two replicas writing at once is the case a shared catalog has to get right, so it was measured rather than assumed. Concurrent appends to one table both commit — each writes its own Parquet files and the catalog just orders the snapshots. Concurrent writes to the same rows are different: one commits and the other's commit fails, and the failed one applies nothing (verified with a 500-row insert bundled into the losing transaction, of which zero rows survived).

That atomicity is what makes recovery safe. Because one statement per request runs in autocommit, a failed commit means the statement did not happen — so re-running it applies it exactly once, never twice. A losing write is retried automatically on a fresh connection, since retrying on the snapshot that just lost would simply lose again, with exponential backoff and jitter so two replicas that collided don't line up and collide again. Every retry is counted in query history, so a contended table shows rising retry counts long before anyone sees a failure. If retries run out you get a plain message saying the statement was rolled back and nothing was applied.

Maintenance and compaction#

An hourly pass keeps things fast and small: flush rows still inlined in the catalog into Parquet, merge adjacent small files (the biggest lever on scan speed), expire snapshots older than 7 days, then delete the files only those snapshots referenced. Steps are independent — one failing is logged and the rest still run.

When the catalog and the object store disagree#

A table here is two things: rows of metadata in the catalog Postgres, and Parquet objects in your object storage. Nothing keeps them together. Replace the object store, empty a bucket, or restore a catalog backup from a different day, and the catalog goes on describing files that are gone.

So the Lakehouse checks. After the table list loads it lists the object store once, compares it against the data files the catalog claims, and marks any affected table — showing how many unreadable rows are behind the missing files instead of the metadata count. It runs after the page renders and never blocks it.

Two deliberate limits: superseded files are ignored, since they are supposed to disappear after compaction and flagging them would mark every compacted table broken; and if the listing hits its ceiling the check reports nothing rather than guessing. There is no automatic repair, because there is no correct one — the rows are gone. Re-import the table from its source, or drop it.

Governance and access#

DuckDB has no per-user ACLs, so the server enforces everything before SQL reaches the engine, at one chokepoint. One statement per request, classified select / DML / DDL — anything else (ATTACH, COPY, SET, INSTALL, transactions) is refused. Every SELECT is parsed to an AST and each table it reads must resolve to a schema you own or hold a grant on; writes must be schema-qualified into an accessible schema. Every statement — refusals included — lands in your query history and the platform audit trail.

Across the ecosystem#

The lakehouse registers as a warehouse connection (Integrations → Data Sources → AgentSwarms Lakehouse — no credentials, it runs under your schema grants). That one connection wires it into everything:

A shared connection always runs as its owner: a dashboard or agent using it reads what the owner can read, never the viewer's own grants — the standard the rest of the platform's connections follow.

Sharing tables outside the platform#

A grant shares a schema with someone who has an account here. Shares hand tables to people who do not, over the Delta Sharing protocol — the delta-sharing Python package, Spark, Power BI. On the lakehouse page, Shares: create a share, add the tables you own (each with an optional row filter and masked columns on top of the table's own policy), mint a recipient token and give them the profile it shows once.

1. 1

   What a recipient receives is a governed snapshot, never your files

   The lakehouse's Parquet keeps deleted rows and a presigned URL bypasses every policy, so each read serves a SELECT through the same policy rewrite as any reader here, written to Parquet beside the lake with deletes applied. An unchanged table is written once; a change bumps the version the client sees.
2. 2

   The recipient's side

   delta_sharing.load_as_pandas("finance.share#finance.analytics.revenue") — files arrive through presigned URLs signed for LAKEHOUSE_S3_PUBLIC_ENDPOINT, valid for SHARE_URL_EXPIRY_SECONDS. Revoking a token stops the next request; every read is audited under the token's label.

Iceberg interop#

The lakehouse speaks Apache Iceberg in both directions through the engine's iceberg extension, so a table built here is readable by Spark, Trino, Flink, Snowflake or Databricks, and a table they own is queryable here without a copy.

• Register a catalog. Under Lakehouse → Iceberg, add an Iceberg REST catalog: its endpoint, the warehouse it serves, and how to authenticate (none, a bearer token, or OAuth2 client credentials) given as secret names from Integrations → Secrets. Lakekeeper, Apache Polaris, Nessie, Glue, Unity Catalog and Snowflake Open Catalog speak this protocol. The catalog is attached and asked for its namespaces before it is saved; registered catalogs attach when the engine boots, and one that fails is marked on its row and skipped, then tried again every five minutes.
• Mount a namespace. A namespace becomes a lakehouse schema: one read-only view per table, owned by you, shareable through IAM, read through the per-user statement guard. Nothing is copied. Refresh brings the views level with the namespace. A statement can never name an attached catalog directly; the only way to an Iceberg table is a mount you can see.
• Publish a table. On a table tab, Publish to Iceberg writes a copy into a catalog namespace as an Iceberg table; replace drops and recreates, refuse keeps an existing one. Import is the reverse: an Iceberg table copied into a schema you created, as a real lakehouse table.
• Audited: catalog definitions through the iceberg_catalog row trigger; lakehouse.iceberg.mount, lakehouse.iceberg.refresh, lakehouse.iceberg.publish and lakehouse.iceberg.import with the catalog, namespace, table and row counts.

Running a query on Spark#

Every query runs on DuckDB inside one app worker — fast per core, spilling to disk, but never spanning machines. When the deployment has a Spark engine (the endpoint or per-job Kubernetes provider ETL pipelines use, under Admin → Developer runtime), the Query tab offers a second place to run a SELECT: Spark cluster. The statement is governed exactly as on DuckDB, the catalog's inlined rows are flushed and a snapshot pinned, and every table it reads is resolved to that snapshot's files. A sandbox then builds one view per table on the cluster straight from those files — deletes applied by position, the catalog's internal columns dropped — runs the statement in Spark's SQL dialect, and the rows land in the same grid with a spark badge. The cluster never opens a catalog session.

Scaling and limits#

Stateless by construction: replicas need no coordination, and writes serialise through the catalog's ACID commits — a conflicting commit fails cleanly and is retried for you (see Concurrent writes). All replicas share the same LAKEHOUSE_* config and can reach the catalog Postgres and object store. The ceilings are the same single-node honesty as ETL — one query's working set lives on one replica (vectorised execution and file pruning are the speed story, not a cluster) unless it is sent to Spark, and cold reads pay object-storage latency. Small inserts are held inlined in the catalog until flushed, so a fresh table can show real row counts with little Parquet yet written.

Use cases#

A plain-language question, with the SQL kept#

1. 1

   Pick the table, open Query

   The search box filters schemas and tables. Type the question in the ask-in-plain-language box — total amount by customer, largest first — and the generated SQL is shown and run. Edit it like any statement.
2. 2

   Read the plan when something is slow

   Show the plan the engine chose and what it actually cost. Results served from the result cache are marked, and the cache is invalidated by any write, so a cached answer is never stale.

A table whose files are gone#

1. 1

   The schema list marks it: Missing data files

   Integrity, above, explains what the marker means and what can still be recovered.
2. 2

   Open the table tab and choose Drop

   A dialog asks to confirm the drop of that exact table. It proceeds through the catalog even though the files cannot be read — the catalog is what says a table exists. Rows that are still needed come back from a backup, not from the catalog.

Answer as of last week#

1. 1

   Open the trace of the original answer under Traces

   Its Provenance section names the lakehouse snapshot that was current when the answer was given.
2. 2

   Use the Replay control

   The recorded reads run again as of that snapshot — the result must match the recorded fingerprint — and against today, where a difference means the data moved on. The History tab lists the snapshots a table has been through.

Back it up and prove the backup#

The catalog and the Parquet are two things; a backup of one without the other is a lakehouse that cannot be read. npm run backup captures both plus the application database, and npm run restore -- backups/<timestamp> --drill restores them into scratch targets, compares, cleans up and prints DRILL PASSED. The full runbook is in the self-hosting guide.