duckicelake reference

The deep reference for duckicelake — the full feature inventory, governance internals, endpoint/config tables, and repository layout. Start with the README for the what-and-why.

Requirements

Object storage is S3. Any S3-compatible backend works for data I/O. Storage-credential scoping comes in two flavors:

• Backends with STS AssumeRole + session policies (AWS, MinIO): the proxy vends temporary credentials scoped to one table’s (or one masked export’s) prefix. For real AWS, see Running against real AWS S3 + STS below.
• Backends with no STS at all (e.g. Hetzner Object Storage): set DUCKICELAKE_STS_ENDPOINT=none and the proxy switches to remote signing — every S3 request is authorized against governance and SigV4-signed server-side, so root keys never leave the proxy. DuckLake-direct DuckDB clients (which can’t remote-sign) use static per-principal keys scoped by generated bucket policies. See the Hetzner Object Storage section in OPERATIONS.md.

The pixi dev stack bundles MinIO purely so you can spin the whole thing up locally in one command; for production, point DUCKICELAKE_S3_* at your real backend.

PostgreSQL is required as the DuckLake metastore. The proxy talks to DuckLake’s catalog tables through a psycopg pool (this is true regardless of the eager hook). The hybrid write model additionally relies on PostgreSQL-specific machinery — LISTEN / NOTIFY, an AFTER INSERT trigger on ducklake_snapshot, and pg_try_advisory_lock for single-listener election. DuckLake itself supports other backends (SQLite, MySQL, DuckDB), but those would forgo the eager path: writes are still visible through the lazy LoadTable materialisation, just without the ~1s warm-S3 guarantee. Switching backends is not currently wired through the proxy’s config.

Architecture at a glance

  Iceberg REST client (PyIceberg, DuckDB iceberg ext, Trino, Spark, …)
              │  HTTP (Iceberg OpenAPI v3)
              ▼
       FastAPI proxy (duckicelake.server)  ──▶ Prometheus /metrics
       │     │     │                       ──▶ /healthz /readyz
       │     │     │                       ──▶ JSON logs
       │     │     │
       │     │     │  STS AssumeRole (per-table session policy)
       │     │     ▼
       │     │   S3 STS     ──▶ vended creds (s3.access-key-id, …)
       │     │
       │     │  SQL via DuckDB+ducklake (write conn + read pool)
       │     ▼
       │  Postgres (psycopg pool)
       │     ├── ducklake_*       — schemas, tables, columns, snapshots, stats, deletes
       │     └── duckicelake_*    — properties, tags, branches, partition-spec sidecar,
       │                            nan_value_count cache, format-version override
       │
       │  S3 direct (object I/O)
       ▼
   data/<ns>/<tbl>/                       ── Parquet data files (DuckLake)
   data/<ns>/<tbl>/                       ── Parquet position-delete files (v2)
   data/<ns>/<tbl>/                       ── eq-delete & v3 Puffin DV (.puffin)
   data/<ns>/<tbl>/metadata/
        ├── vN.metadata.json              ── TableMetadata, versioned per commit
        ├── version-hint.text             ── Hive-style pointer to vN
        ├── snap-<id>-<uuid>.avro         ── manifest list (one per snapshot)
        ├── <id>-<uuid>-m0-data.avro      ── data manifest (stats + row_id + key_metadata)
        └── <id>-<uuid>-m1-deletes.avro   ── delete / DV manifest (when applicable)

Everything runs out of a single pixi environment — no Docker.

What’s in the box

Iceberg REST surface

• All catalog ops: /v1/config, namespace CRUD, table CRUD, rename, views CRUD.
• Multi-catalog: POST /v1/catalogs provisions an isolated per-tenant catalog (own PG metadata schema + S3 prefix); every /v1/{prefix}/… route — including commits and credential vending — resolves through the registry with account-scoped authorization.
• LoadTable returns inline TableMetadata + (optionally) vended STS creds via X-Iceberg-Access-Delegation: vended-credentials.
• LoadTable?snapshot-id=N pins a historical snapshot for time-travel.
• Format-version 2 by default; v3 writes work end-to-end through the pyiceberg_v3 shim — see the v3 section below.
• Full Iceberg commit-table action set:

  Action	Translation
  add-snapshot (append/overwrite/replace/delete)	ducklake_add_data_files() + tombstone via UPDATE ducklake_data_file.end_snapshot
  position-delete file (content=1)	INSERT INTO ducklake_delete_file; for v3 tables, materialize rewrites these into a Puffin file with one deletion-vector-v1 blob per data file
  equality-delete file (content=2)	per-file scan via read_parquet(..., file_row_number=true) → emit Iceberg position-delete Parquets scoped to files with begin_snapshot < commit_snap (Iceberg-spec sequence-number scoping)
  add-schema + set-current-schema	diff by field-id → ALTER TABLE ADD/DROP COLUMN
  add-partition-spec (identity / year / month / day / hour / bucket)	ALTER TABLE … SET PARTITIONED BY (…)
  add-partition-spec (truncate[N])	sidecar duckicelake_table_partition_field; per-file values synthesised from source min_value at emit time
  add-sort-order	direct INSERT into ducklake_sort_info + ducklake_sort_expression
  set-properties / remove-properties	sidecar duckicelake_table_property
  set-snapshot-ref type=tag	sidecar duckicelake_table_tag (ref_type='tag')
  set-snapshot-ref type=branch (non-main)	sidecar entry as read-only branch pointer; writes targeting a non-main branch 501
  remove-snapshot-ref / remove-snapshot	sidecar delete / ducklake_expire_snapshots
  upgrade-format-version to 2 or 3	sidecar duckicelake.format-version property; materialize emits matching Avro schemas
  assign-uuid / add-statistics / remove-statistics	accepted no-ops (we derive UUID + synthesise stats from DuckLake)
  set-location	501 (DuckLake owns layout via attach-time DATA_PATH)
  void partition transform	501 (drop the field from a fresh spec instead)

Snapshot chain + per-file metadata

• One Iceberg snapshot per DuckLake commit, linked via parent-snapshot-id, with summary.operation enriched from ducklake_snapshot_changes.changes_made (append/delete/overwrite/replace).
• snapshot-log[], metadata-log[], refs.main always tracking DuckLake HEAD; tags + read-only branches via sidecar.
• Per-file column stats: value_counts, null_value_counts, nan_value_counts (exact, computed via read_parquet(... WHERE isnan(col)) and cached in duckicelake_file_nan_count), and lower_bounds / upper_bounds with Iceberg-spec binary encoding (LE for ints/floats/timestamps, BE two’s-complement for decimals, UTF-8 strings, 16-byte BE UUIDs).
• Row lineage (v3): first_row_id per manifest entry, last-row-id
  • row-lineage: true in TableMetadata.
• key_metadata surfaced from ducklake_data_file.encryption_key (null on unencrypted catalogs).

Partition pruning end-to-end

Per-file partition values land in the manifest Avro with Iceberg-correct semantics:

• identity, bucket[N] — DuckLake’s stored values pass through (the Murmur3 hash aligns with Iceberg’s (murmur3 & INT_MAX) % N).
• year / month / day / hour — recomputed server-side from source column min_value because DuckLake’s semantics differ.
• truncate[N] — synthesised from source min_value via iceberg_transforms.apply_truncate (DuckLake has no native truncate).

Verified: PyIceberg pushdown prunes country='US' from 3 files → 2 read; ts >= 2026-04-22 UTC from 3 files → 1 read.

Iceberg v3 writes (via the shim)

PyIceberg 0.11.1 still raises Cannot write manifest list for table version: 3. The fix PR upstream (iceberg-python#3070) stalled in March 2026.

pyiceberg_v3.install() vendors that PR’s essentials as a monkey-patch: ManifestWriterV3 / ManifestListWriterV3 subclasses, write_manifest / write_manifest_list factory dispatch, SUPPORTED_TABLE_FORMAT_VERSION bumped to 3 (in both pyiceberg.table.metadata and pyiceberg.table.update), DataFile.from_args rewired so default arg resolves dynamically (else V2-shape records flow into V3 writers and IndexError), client-side gates patched in Transaction.upgrade_table_version + _apply_table_update for UpgradeFormatVersionUpdate (seeds next_row_id=0) + AddSnapshotUpdate (synthesises first_row_id).

Call once before any RestCatalog operation:

from duckicelake.pyiceberg_v3 import install
install()

The same shim also adds the v3 primitive types (variant, geometry, geography) that PyIceberg’s pydantic validator otherwise rejects.

The proxy itself accepts upgrade-format-version to 3 and re-materialises manifests + manifest-list in V3 Avro shape (with first_row_id field) when the table’s format-version is 3.

v3 Puffin deletion vectors

For format-version 3 tables, the proxy rewrites position-delete Parquets into a single Puffin file per snapshot containing one deletion-vector-v1 blob per affected data file:

• Roaring64 portable serialisation (Iceberg-spec compatible: 8B little-endian count of 32-bit bitmaps, then per-bucket key + CRoaring portable bytes).
• Magic D1 D3 39 64, big-endian length + CRC-32 framing per spec.
• Footer: PFA1 magic + UTF-8 JSON FileMetadata + size + flags + magic.
• Manifest entry carries file_format=puffin, content_offset, content_size_in_bytes, referenced_data_file, and record_count (= cardinality).

V2 tables keep the legacy Parquet position-delete shape — readers that only understand v2 still work.

v3 type wiring

PyIceberg and the demo show v3 types loading + reading round-trip; DuckDB’s iceberg ext currently surfaces variant / geometry as UNKNOWN (upstream gap in duckdb-iceberg 1.5.x).

OAuth2 + RBAC

• POST /v1/oauth/tokens issues HMAC-signed JWTs; middleware enforces Authorization: Bearer on every /v1/* route except /v1/config, /v1/oauth/tokens, /healthz, /readyz, /metrics, /openapi.json.
• Scope grammar embedded in the JWT: ns:<name>:<cap> (per-namespace) or * (superuser). cap ∈ {r, w, rw, *}. Catalog-level writes (create / drop namespace) require a wildcard-namespace scope.
• Configure via DUCKICELAKE_OAUTH_CLIENTS="id:secret|scope,id2:sec2|scope2" or DUCKICELAKE_OAUTH_CLIENTS_FILE=<path> (JSON).
• DUCKICELAKE_REQUIRE_AUTH=1 → server refuses to start if no clients configured. Production safety belt.
• PyIceberg consumes via credential="id:secret", DuckDB via CREATE SECRET (TYPE ICEBERG, TOKEN '<token>').

STS credential vending

X-Iceberg-Access-Delegation: vended-credentials triggers a real STS AssumeRole against the object store, with a session policy scoped to the table’s data-file keys + its metadata/* prefix. Returns s3.access-key-id / s3.secret-access-key / s3.session-token / s3.credentials-expiration in the LoadTable config map.

Root keys are not embedded by default. Response configs carry only endpoint/region/url-style; clients are expected to use vended credentials (the delegation header, or GET …/ducklake-credentials for DuckLake-direct). Dev stacks that want the old convenience set suppress_root_creds = false in duckicelake.toml (or DUCKICELAKE_SUPPRESS_ROOT_CREDS=0).

Throughput / scale

• Postgres ConnectionPool (psycopg-pool) — most LoadTable work hits PG directly (info-schema queries moved off DuckDB to bypass the write-conn lock).
• DuckDB read pool (separate from the write conn) for parallel scans during equality-delete handling.
• Single boto3 S3 client per process — built once at startup, thread-safe, pools its own HTTPS connections.
• Single Postgres transaction per commit (commit_transaction() context with contextvars-driven shared cursor).
• In-process LRU metadata cache keyed on (ns, table) → (snap_id, metadata), bounded via DUCKICELAKE_CACHE_MAX (default 1024).
• Eager materialise at commit time so post-commit reads hit cache.
• Per-snapshot S3 writes parallelised via a thread pool; head_object before put_object skips re-uploads of byte-identical content.
• Per-file equality-delete scans run in parallel across the read pool.
• Endpoints are sync def (FastAPI runs them in its threadpool, so blocking I/O doesn’t pin the event loop). pixi run serve-hi boots 4 uvicorn workers.

Measured: ~349 req/s on cache-hit LoadTable at concurrency 32 on one machine.

Observability

• /metrics — Prometheus exposition. Per-endpoint latency histograms, request counts by status class, commit outcomes, in-process cache size + hit/miss counters, PG pool in-use / idle.
• /healthz — liveness (always 200 if the process is up).
• /readyz — readiness (200 only when Postgres responds to SELECT 1).
• JSON-formatted logs by default (DUCKICELAKE_LOG_FORMAT=json); flip to text for dev. Configurable level via DUCKICELAKE_LOG_LEVEL.

Admin

• DELETE /v1/{prefix}/.../tables/{tbl}?purgeRequested=true — DROP TABLE plus delete every S3 object under the table prefix (data, delete files, manifests, metadata JSON).
• POST /v1/{prefix}/admin/namespaces/{ns}/tables/{tbl}/compact — wraps ducklake_merge_adjacent_files + ducklake_cleanup_old_files. Idempotent; safe to cron.

Tests + CI

• 180+ pytest tests covering the REST surface, cache LRU, metrics endpoint, Puffin writer byte-level structure, the eager-materialisation listener, config-file loading, multi-catalog isolation (per-tenant PG schemas / S3 prefixes / reader roles, account-scoped routing, per-catalog commits), the fail-closed regression suite (multi-worker DDL race, planning-error denial, strict mode), STS vending unit coverage (endpoint sentinels, session-policy size degradation, AssumeRole retry paths), the no-STS remote-signing path end-to-end against MinIO (including PyIceberg’s own S3V4RestSigner client class and file-layer masking enforced per signed request), and the governance layer end-to-end — including the byte-level proofs that a masked principal’s vended credentials cannot read base Parquet (403) while masked copies, the RLS-governed reader role, and the shadow Iceberg metadata all serve masked rows; a privileged principal reads cleartext throughout.
• GitHub Actions workflow at .github/workflows/ci.yml runs backends-up + pytest + the full duckdb-client demo on every push.

Governance reference

The README covers the governance model and the byte-level tier; this is the full authoring/composition/hardening detail.

How row-access policies work

A masking policy rewrites a column; a row-access policy drops whole rows. Its body is a boolean keep-predicate over the row — a row survives only if the predicate is true. Attach it to a table (naming the columns it reads) or to a tag on the table/schema.

They stack with AND. Add a second policy and a row must satisfy both. With eu_only (country = 'EU') and min_mrr (mrr >= 2000) attached, the proxy nests them ahead of any masking (predicates joined in policy-name order):

SELECT "id", left("email", 2) || '***' AS "email", …
FROM (SELECT * FROM "analytics"."customers"
      WHERE (country = 'EU') AND (mrr >= 2000)) AS "customers"

The filter sees RAW values, before masking. Because the predicate runs in the inner subquery, you can filter on a column whose output is masked — e.g. a policy body of email LIKE '%@work.net' keeps Carol’s row even though her email comes back as ca***. (This matches how a SQL engine’s row policies see unmasked data.)

Bypass is per-policy. Each policy carries its own unmasked-roles, so a principal can hold the row filter’s bypass yet still be column-masked, or vice-versa — analyst_eu above is unmasked but still EU-only.

Working with tags & policies

Change a policy — just re-POST it. Names are the key; a repeat POST is an upsert. Widen who bypasses mask_email, or swap its body, with no delete:

curl -sX POST $P/masking-policies -H $H -d '{"name":"mask_email","signature":"(val VARCHAR)","body":"regexp_replace(val, ''[^@]+'', ''***'')","unmasked-roles":["pii_reader","support"]}'

The masking view / pre-masked export carries a signature of the mask shape, so it rotates automatically — the next read picks up the new body; the stale view/export is garbage-collected. Re-POSTing an object-tag or a policy-attachment upserts it the same way.

Delete — detach first (references block the delete). A policy that’s still attached, or a tag that’s still in use, returns 409; remove the references, then delete:

# detach the mask from its tag, THEN delete the policy
curl -sX DELETE $P/policy-attachments  -H $H -d '{"policy-kind":"masking","policy-name":"mask_email","target-kind":"tag","tag-namespace":"pii","tag-name":"email"}'
curl -sX DELETE $P/masking-policies/mask_email          # 409 while still attached
curl -sX DELETE $P/object-tags         -H $H -d '{"object-kind":"column","schema":"analytics","object":"customers","column":"email","tag-namespace":"pii","tag-name":"email"}'
curl -sX DELETE $P/tags/pii/email                       # 409 while still assigned/attached
curl -sX DELETE $P/role-grants         -H $H -d '{"role":"pii_reader","principal":"analyst_eu"}'
curl -sX DELETE $P/object-grants       -H $H -d '{"object-kind":"table","schema":"analytics","object":"customers","privilege":"select","role":"analysts"}'

Detaching a mask, untagging a column, or revoking a grant flips the affected reads back to cleartext on the next request, and the proxy drops the now- stale masking views / pre-masked exports for those tables automatically.

How policies compose

• Can one tag carry multiple policies? Yes — a tag can hold several attachments (a masking policy and a row-access policy, or masks for different columns). But a column resolves to exactly one masking policy: attaching a second mask that would reach an already-masked column (directly or via a tag) is rejected with 409. Row-access policies have no such limit — they stack with AND.
• Tag cascade accumulates. Tags at schema → table → column union (a column-level tag doesn’t erase a broader one); a column is governed by every policy on any tag it carries plus any direct column attachment — subject to the one-mask rule.
• Masking + row-access compose in one view. Row filters run first on raw rows; the surviving rows are projected through the column masks. Bypass is evaluated independently per policy.
• A principal’s effective policy = their roles (JWT claim ∪ sidecar grants) resolved against the table’s tags/policies → at most one mask per column + the AND of all non-bypassed row filters. Inspect it live: GET …/governance/effective-policies?table=analytics.customers&principal=… returns both the derived set and the enforced plan (masked columns, row filter, the generated view SQL).

The model. Object tags (pii.email, hierarchical cascade schema → table → column), masking policies + row-access policies (SQL bodies with a declarative unmasked-roles bypass), policy attachments (to a tag or directly to columns/tables), roles + grants, and a complete audit trail — all stored in duckicelake_* Postgres sidecars and authored over REST (/v1/{prefix}/governance/*).

Iceberg-REST enforcement. At LoadTable the proxy resolves the caller’s principal + roles (JWT roles claim ∪ sidecar grants) into a per-table enforcement plan and stamps the returned metadata — column doc annotations, duckicelake.mask.<col> expressions, iceberg.row-filter (the Trino/Spark fast-path key), and the generated masking-view SQL. Every decision is audited.

Executed masking views, both read paths. The engine materialises one physical DuckLake view per (table, mask-signature) — __mask_{table}__{sig} — that executes the mask:

• View-capable REST engines (PyIceberg / Trino / Spark) load it via GET …/views/…; LoadTable advertises the caller’s view name via the duckicelake.masking-view-name property.
• DuckLake-direct DuckDB clients call GET /v1/{prefix}/namespaces/{ns}/ducklake-credentials?table=… and get the PG DSN + ATTACH statement, read-only prefix-scoped STS creds (files committed after vending stay readable), their masked view name, and post_attach_sql that makes masking transparent for unqualified queries (SET search_path onto a __masked_{sig} schema).
• Policy or schema changes rotate the signature; stale views are garbage-collected. Per-table opt-out via the duckicelake.masking-disabled property.

Per-principal reader roles + row-level security. ducklake-credentials vends a per-principal, per-vend Postgres LOGIN role (duckicelake_p_<sub>_<sha8>_<nonce>, random password, VALID UNTIL = the STS expiry, READ_ONLY attach) instead of the owning role — a fresh role per vend, so concurrent vends for one principal never invalidate each other’s secret; all map to the same principal and are GC’d by expiry. Row-level security on the ducklake_* catalog tables enforces visibility: explicit select object-grants flip a table to allowlist (ungranted principals can’t even see it), and the same machinery hides base file rows for file-layer-masked tables. The RLS check is a set-membership test (table_id NOT IN (SELECT … hidden …)) Postgres evaluates once per scan and hashes — not a per-row function call — so it stays cheap on large catalogs.

File-layer masking — byte-level, every engine. Set "file-layer-masking": true on a masking policy and the proxy materialises the mask physically — per-(table, mask-signature) current-state Parquet exports under data/__masked__/… (deletes and row filters applied by construction, Iceberg field-ids stamped), eagerly refreshed on every DuckLake commit. Masked principals then get:

• DuckLake-direct: the masking view reads the pre-masked Parquet, creds cover only the masked prefix (base bytes physically 403), and RLS hides base file rows — the base table is empty even by name.
• Iceberg REST: LoadTable returns shadow Iceberg metadata built over the export + read-only masked-prefix STS — so even the DuckDB iceberg extension (no views, no scan planning) transparently scans masked bytes.

This is the only byte-level mechanism available for engines that read Parquet directly: DuckDB has no per-column Parquet encryption and no REST scan-planning client, so pre-masked copies are the path. The default stays catalog-level only.

Failure posture is tiered: the airtight surfaces — file-layer masking and RLS credential vending — always fail closed (an internal error denies the read/vend rather than leaking base bytes or the owner DSN), while the cooperative catalog-level tier fails open by default (a governance error degrades to unmasked-with-audit, never a broken read) — flip DUCKICELAKE_GOVERNANCE_FAIL_CLOSED=1 to make governance errors deny there too. Root S3 keys are no longer embedded in responses by default: clients see only vended credentials.

Hardening & boundaries (audited by two adversarial review passes):

• Reserved governance state can’t be forged or disabled by a client. set/remove-properties on any duckicelake.* key is rejected (403) — a write token can’t flip off duckicelake.file-layer-masking to disable RLS file-hiding. __mask_* table/view names are rejected at create and rename so user objects can’t collide with masking-view plumbing.
• Masked principals never reach base bytes. Vended creds scope to the masked-signature prefix only; namespace-level vends add explicit IAM Deny on every file-layer table’s base prefix — derived from the policy, so a table that was authored but never yet exported is still denied. On no-STS backends (Hetzner) there is no scoped session token, so masked- principal creds come from a confined static key (bucket policy Deny base
  • Allow only the masked-sig export); a plain static key fails closed. See file_layer_no_sts.md.
• File-layer masking is current-state only — and says so. The masked Parquet export tracks the live snapshot; there is no per-historical-snapshot masked export. A time-travel LoadTable (?snapshot-id=N for an older N) on a file-layer table is therefore denied (501), never silently served the current snapshot under the requested id. A masked export dir is retained until any STS creds that could still be reading it have expired (a grace window above the credential TTL, not just a count cap), so a slow reader’s in-flight scan isn’t swept out from under it.
• Injection-safe SQL. Identifiers and string literals (table/column names, S3 paths) are escaped in the export COPY / read_parquet / FIELD_IDS paths, which run as the owning role with root creds.
• Catalog hygiene. The reader role is never granted the tables that would bypass or undermine RLS: inlined-data payloads (raw rows, no table_id to police), ducklake_snapshot_changes (leaks hidden tables' names), and ducklake_files_scheduled_for_deletion (base data-file paths of hidden / file-layer tables). RLS coverage re-arms automatically if a new ducklake_* system table appears after startup. Governance sidecar DDL runs once per process, not per request. Known residual disclosure (low): a reader can still see ducklake_schema (namespace names) and ducklake_view (view definitions, incl. masking-view SQL) — the DuckLake extension needs both to resolve and execute the masking views; neither exposes row data. RLS-filtering those is tracked follow-up.
• Policies follow the catalog, not stale names. Governance rows key on (schema, table, column), so a rename or drop would otherwise orphan them — a mask silently lapsing (leak) or a recreated name inheriting a stale mask. rename_table carries the table’s tags/attachments/grants to the new name; an add-schema column rename (same field-id, new name) carries the column’s tag + column-target masks to the new name; drop_table purges them; all resync the masking views/exports. And an attachment the resolver would ignore (masking→table, row-access→column) is rejected at attach (400) so a no-op can’t masquerade as protection.
• Honest about the dev/prod gap. The catalog-level masking views are cooperative (a client with the base table name + base creds reads raw); file-layer masking is the airtight tier for tables that opt in. The dev stack runs Postgres trust auth, so RLS authentication is only real under production scram-sha-256 + TLS — the pg_hba recipe is in OPERATIONS.md. The owning role authenticates by socket trust in dev or cert/ident in prod; for managed Postgres that needs a scram password, set DUCKICELAKE_PG_PASSWORD (it flows into every owner connection and is redacted from logs). Disabling RLS (DUCKICELAKE_RLS=0) vends the owner DSN — now including that password — to clients, so keep RLS on in production.

Try it against a running stack:

./scripts/governance_demo.sh    # author tags/policies/roles via REST + SQL proof
./scripts/lakesh_demo.sh        # lakesh: unmasked REST read vs vended masked view
pixi run pytest -q tests/test_governance.py tests/test_governance_phase3.py tests/test_governance_phase4.py

The LLM-agent story this is built for: give the agent’s token a role without the unmasked-roles bypass (or no roles at all), point it at the proxy — REST or ducklake-credentials — and it reads al*** where a human analyst holding pii_reader reads alice@example.com, through the same API, with every access audited.

What’s verified

Beyond the full pytest suite, the governance layer has been exercised end-to-end against a live local stack (proxy + MinIO + Postgres). All of the following are confirmed working:

• Byte-level masking on every read engine. With a file-layer policy active, a masked principal’s vended credentials get 403 on the base Parquet keys and 200 on the masked copies; the masked values come back through the raw S3 path, the DuckDB iceberg extension (iceberg_scan over the shadow metadata), and PyIceberg — none of them DuckDB-specific, all reading al*** instead of the real address.
• DuckLake-direct masking. lakesh and a plain DuckDB session reading through the vended reader-role DSN see masked rows via the materialized view (transparent for unqualified queries).
• The adversarial cases hold. A write token cannot disable governance (set/remove-properties on duckicelake.* → 403); __mask_* table/view names are rejected (400); a namespace-level credential vend denies the base bytes of a file-layer table even if it was never exported.
• RLS enforces visibility and stays cheap. An ungranted principal can’t see an allowlisted table at all; a granted one and the owner can; the policy check plans as a once-per-scan hashed sub-plan, not a per-row function call.
• Concurrency & lifecycle. 10 simultaneous credential vends for one principal yield 10 independent roles that all authenticate (no password-rotation race); a post-vend write shows up masked in an already open session within ~1s (eager refresh); schema changes rotate the mask signature and DROP TABLE … purgeRequested=true removes the masked copies.
• Performance. Export materialization and masked scans are on par with base scans on a 1M-row table; re-vending reuses the existing export.
• Multi-worker stability. serve-hi (4 uvicorn workers) boots with zero startup-DDL races (advisory-lock-serialized ensure_rls / sidecar / trigger DDL), all workers arm RLS, and credential vends return 200 from every worker — including after killing a worker mid-run (the respawn re-arms cleanly; a worker that misses startup arming self-heals on its first vend).
• Multi-catalog isolation. A provisioned tenant catalog’s namespaces, tables, commits, and vended credentials are fully disjoint from the default catalog (own PG metadata schema + S3 prefix); a tenant reader role is denied the default catalog’s metadata with InsufficientPrivilege; an add-schema commit against the tenant lands in its schema and leaves the default catalog’s same-named table untouched.

Architectural decisions

See ARCHITECTURE.md for full rationale — short version:

• Single-writer invariant: every commit ends as rows in DuckLake’s Postgres tables inside one transaction. No two writers race on catalog state. DuckLake’s ducklake_add_data_files allocator serialises file additions; our register_delete_files / tombstone_data_files mirror the same pattern with explicit snapshot allocation under a PG lock.
• Lazy materialisation, content-addressed cache: keyed on DuckLake snapshot id. UniForm had to go eager because HMS gave them no lazy hook; we own the REST surface so we cache + invalidate cleanly.
• Iceberg snapshot-id == DuckLake snapshot-id, deterministic. No random int64s like UniForm — direct correlation makes ops debuggable.
• Equality deletes are spec-scoped: per-file scan + emit, scoped to files with begin_snapshot < commit_snap. Files added after the delete are never retro-deleted.
• Read-only branches over no branches: DuckLake has no native branching. We expose named refs as read-only pointers (covers pinning + release labelling); writes targeting a non-main branch 501.

Sample data & querying

Load the optional demo dataset (idempotent — re-running never duplicates):

pixi run seed            # analytics.customers (8 rows) + analytics.orders (15 rows, 2 snapshots)
pixi run seed-governed   # ...and author the demo mask: pii.email on customers.email,
                         # bypassed by role pii_reader (granted to principal 'alice')

Then query it from any of the three common clients (all examples verified against the dev stack):

pyiceberg

from pyiceberg.catalog.rest import RestCatalog

cat = RestCatalog(
    "lake", uri="http://127.0.0.1:8181", warehouse="lake",
    **{
        "s3.endpoint": "http://127.0.0.1:9000",
        "s3.access-key-id": "minioadmin",
        "s3.secret-access-key": "minioadmin",
        "s3.region": "us-east-1",
        "s3.path-style-access": "true",
    },
)
t = cat.load_table("analytics.customers")
print(t.scan().to_arrow().to_pandas())

# time-travel: orders has ≥2 snapshots — scan the first one with data
o = cat.load_table("analytics.orders")
first = next(s.snapshot_id for s in o.metadata.snapshots
             if int(s.summary.get("added-records", 0)) > 0)
print(o.scan(snapshot_id=first).to_arrow().num_rows)   # 8, current is 15

(If auth is enabled, add "credential": "client_id:client_secret".)

DuckDB iceberg extension

INSTALL httpfs; LOAD httpfs; INSTALL iceberg; LOAD iceberg;
CREATE OR REPLACE SECRET ice_s3 (
    TYPE S3, KEY_ID 'minioadmin', SECRET 'minioadmin',
    REGION 'us-east-1', ENDPOINT '127.0.0.1:9000',
    USE_SSL false, URL_STYLE 'path');
ATTACH 'lake' AS ice (
    TYPE ICEBERG, ENDPOINT 'http://127.0.0.1:8181',
    AUTHORIZATION_TYPE 'none', ACCESS_DELEGATION_MODE 'none');

SELECT c.country, sum(o.amount) AS revenue
FROM ice.analytics.customers c
JOIN ice.analytics.orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.country ORDER BY revenue DESC;

-- time-travel by snapshot id
SELECT count(*) FROM ice.analytics.orders AT (VERSION => <snapshot-id>);

(ACCESS_DELEGATION_MODE 'none' matters — see the iceberg-extension notes.)

lakesh (the companion SQL shell)

lakesh config init       # writes ~/.config/lakesh/config.toml

default = "local"
[profiles.local]
uri       = "http://127.0.0.1:8181"
warehouse = "lake"
[profiles.local.s3]
endpoint   = "http://127.0.0.1:9000"
region     = "us-east-1"
access_key = "minioadmin"
secret_key = "minioadmin"
path_style = true

lakesh doctor                                             # connectivity check
lakesh exec -q 'SELECT count(*) FROM analytics.customers' # one-shot query
lakesh                                                    # REPL: \l, \d analytics, SELECT …

With pixi run seed-governed, contrast the governed views: GET /v1/lake/governance/effective-policies?table=analytics.customers&principal=bob shows email masked, while principal=alice (holds pii_reader) reads it clear — the full walkthrough is ./scripts/governance_demo.sh.

Multi-catalog: isolated per-tenant catalogs

One proxy can serve many isolated DuckLake catalogs from a shared Postgres database and S3 bucket — one Postgres metadata schema + one S3 data prefix per catalog, selected by the Iceberg REST {prefix} path segment. The default catalog (DUCKICELAKE_CATALOG, prefix lake) needs no registration; existing single-catalog deployments are unchanged.

# control-plane: register a tenant catalog (admin-scope token when auth is on)
curl -X POST localhost:8181/v1/catalogs -H 'content-type: application/json' -d '{
  "catalog_id": "acme", "metadata_schema": "dl_acme__main",
  "data_prefix": "acme/main/", "account_id": "acme-corp"}'

# data-plane: same Iceberg REST surface, per-tenant prefix
curl localhost:8181/v1/acme/namespaces

• Isolation: the tenant’s ducklake_* metadata lives in its own PG schema (METADATA_SCHEMA on the DuckLake attach), its Parquet under its own prefix; per-catalog RLS reader groups mean a vended tenant reader can’t even see another tenant’s metadata rows.
• Account scoping: with auth enabled, a catalog provisioned with an account_id is only reachable by tokens carrying the matching account claim (4th field in DUCKICELAKE_OAUTH_CLIENTS: id:secret|scope|roles|account) or an admin-scope token; a cross-account prefix answers the same 404 as an unknown one.
• Writes too: table create/commit/drop, views, and ducklake-credentials vending all route through the resolved catalog; reader RLS is armed lazily on a tenant’s first vend, fail-closed.

Design details: docs/multi_catalog_isolation.md.

Endpoint summary

Layout

duckicelake/
├── pixi.toml                     # one-env stack (Postgres, MinIO, Python, deps)
├── pyproject.toml
├── README.md / ARCHITECTURE.md / OPERATIONS.md / MISSING.md
├── .github/workflows/ci.yml      # CI: backends-up + pytest + demo
├── scripts/
│   ├── pg.sh                     # Postgres lifecycle
│   ├── minio.sh                  # MinIO lifecycle
│   ├── governance_demo.sh        # author tags/policies/roles via REST + SQL proof
│   ├── lakesh_demo.sh            # lakesh against the governed catalog (vended creds)
│   ├── seed_events.py            # idempotent analytics.events seed for the demos
│   ├── sql_proof.py              # masked-vs-unmasked rows from the live policy SQL
│   └── probe_searchpath.py       # DuckDB search_path transparency probe (file-layer transparent routing)
├── duckicelake.toml.example      # config-file template (env ↔ TOML key map)
├── docs/
│   └── multi_catalog_isolation.md # multi-catalog design: isolation model + phasing
├── src/duckicelake/
│   ├── config.py                 # settings: env > .env > duckicelake.toml
│   ├── auth.py                   # OAuth2 + JWT + scope grammar + roles claim
│   ├── catalog.py                # DuckLake wrapper: PG pool + DuckDB read/write split
│   │                             #   + sidecar tables + LRU metadata cache + S3 client
│   ├── governance.py             # governance sidecars: tags/policies/roles/grants/audit
│   ├── governance_api.py         # REST authoring router (/v1/{prefix}/governance/*)
│   ├── policies.py               # policy engine: per-principal plan, mask signature,
│   │                             #   masked-view SQL, metadata stamping
│   ├── masking_views.py          # ad-hoc DuckLake masking views: materialise/GC/transparent
│   ├── masked_export.py          # file-layer masking: masked Parquet exports + shadow metadata
│   ├── pg_rls.py                 # per-principal PG reader roles + RLS on ducklake_*
│   ├── registry.py               # multi-catalog registry: per-tenant contexts + provisioning
│   ├── notify.py                 # eager materialisation listener (LISTEN/NOTIFY + election)
│   ├── types.py                  # Iceberg ↔ DuckDB ↔ DuckLake type translation
│   ├── bounds.py                 # Iceberg binary bound encoders
│   ├── iceberg.py                # TableMetadata scaffold
│   ├── manifest.py               # Iceberg v2/v3 Avro writers (data + delete + DV manifests)
│   ├── partition_sort.py         # Iceberg ↔ DuckLake partition / sort translation
│   ├── iceberg_transforms.py     # day/month/year/hour/bucket/truncate value computation
│   ├── puffin.py                 # v3 Puffin writer for deletion-vector-v1 blobs
│   ├── pyiceberg_v3.py           # client-side shim: v3 types + v3 manifest writers
│   ├── materialize.py            # full snapshot-chain materialiser (lazy + cached)
│   ├── read_manifest.py          # parses client-supplied manifest chains on commit
│   ├── sts.py                    # S3 STS AssumeRole + session policies (file/prefix scoped)
│   ├── observability.py          # Prometheus metrics + JSON logging
│   ├── models.py                 # Pydantic REST request/response models
│   ├── server.py                 # FastAPI app: endpoints + middleware + handlers
│   ├── bootstrap.py              # `pixi run ducklake-init`
│   ├── seed.py                   # `pixi run seed[-governed]` — optional sample data
│   ├── smoke.py                  # catalog-only smoke
│   └── duckdb_client.py          # full demo with assertions across all features
└── tests/
    ├── conftest.py               # session-scoped uvicorn + clean-state fixtures
    ├── test_catalog_surface.py   # REST surface smoke
    ├── test_cache_and_observability.py
    ├── test_config.py            # config-file loading + precedence + root-key suppression
    ├── test_governance.py        # authoring, audit, plan, LoadTable stamping
    ├── test_governance_lifecycle.py # one-mask-per-column, delete/detach/revoke, rename/drop carry
    ├── test_governance_phase3.py # masking views, ducklake-credentials, STS scoping, RLS, fail-open
    ├── test_governance_phase4.py # file-layer masking: exports, byte proofs, shadow metadata
    ├── test_governance_security.py # fail-closed regressions: DDL race, planning errors, strict mode
    ├── test_catalog_registry.py  # multi-catalog registry: provision/resolve/cache
    ├── test_multi_catalog.py     # per-tenant PG-schema + S3-prefix + row isolation
    ├── test_multi_catalog_http.py# multi-catalog REST: routing, authz, per-catalog commits
    ├── test_notify_materialise.py# eager materialisation end-to-end
    └── test_puffin.py            # byte-level Puffin writer tests

Configuration

Every DUCKICELAKE_* setting can come from, in precedence order:

1. real environment variables,
2. a .env file in the working directory (DUCKICELAKE_* keys only),
3. ./duckicelake.toml — or the file DUCKICELAKE_CONFIG_FILE points at.

See duckicelake.toml.example for the TOML key map: top-level key ↔ DUCKICELAKE_KEY, [section] key ↔ DUCKICELAKE_SECTION_KEY (so [s3] endpoint is DUCKICELAKE_S3_ENDPOINT), booleans as true/false. File values are injected at startup without overriding the real environment, so they also feed auth, logging, and the notify listener. duckicelake.toml and .env are gitignored — they may carry secrets.

Running against real AWS S3 + STS

The STS vending path is AWS-shaped by construction — regional STS endpoint, configurable role ARN, session-policy size handling, duration semantics — but has not yet been run against live AWS (MISSING.md). What you need:

[s3]
endpoint   = "https://s3.eu-central-1.amazonaws.com"
region     = "eu-central-1"
bucket     = "my-lakehouse"
root_key   = "…"                  # the proxy's BASE credentials
root_secret = "…"                 # (IAM user or role-derived keys)
path_style = false

[sts]
endpoint = "aws"                  # -> https://sts.eu-central-1.amazonaws.com
role_arn = "arn:aws:iam::123456789012:role/duckicelake-vend"

• Trust policy: the vending role’s trust policy must allow the proxy’s base-credential principal to sts:AssumeRole it.
• Intersection semantics: a session policy can only narrow the role’s own permissions, so the role’s permission policy must be a superset of everything the proxy vends — grant it s3:* on arn:aws:s3:::{bucket} and arn:aws:s3:::{bucket}/{prefix}*; each vend then narrows to one table/export prefix.
• DurationSeconds: AWS rejects values above the role’s MaxSessionDuration (default 3600) where MinIO clamps; the proxy retries once at 3600 and logs a pointer at the role setting. DUCKICELAKE_STS_MAX_DURATION caps what clients may request.
• Session-policy size: AWS packs inline session policies to 2048 chars. Per-file read scoping degrades automatically to a table-prefix scope past ~1900 chars (audited as sts_degraded); governance Deny carve-outs are never dropped — an unshrinkable policy fails the vend instead (fail closed).

DuckDB iceberg extension: configuration notes

Three things worth knowing (all handled automatically by duckdb_client.py::_iceberg_client_con):

• Attach with ACCESS_DELEGATION_MODE 'none'. Without this, the iceberg extension builds its own S3 secret from the REST config with a path-scoped lifetime, and a use_ssl/path-style-access conflation in its config parser produces signatures MinIO rejects on delete-file HEAD. With 'none', the extension uses the regular CREATE SECRET (TYPE S3, ...) like any other httpfs operation.
• Don’t set allow_moved_paths=true on iceberg_scan. It engages a debug path-joiner (IcebergUtils::GetFullPath) that mangles absolute s3:// URIs.
• Snapshot timestamp-ms must be ≤ client’s transaction-start. IcebergTableEntry::GetSnapshot uses transaction-start time as the snapshot-lookup anchor. We backdate DuckLake snapshots by 1s on write to win the race.

What’s left out

See MISSING.md for the punch list. The Iceberg spec surface is effectively complete; remaining gaps are:

• Architectural (DuckLake-blocked): true divergent branches, per-table set-location, real KMS envelope encryption.
• Governance: tags/RBAC/masking, catalog row-level security, and file-layer (byte-level) masking are implemented and tested; remaining gaps are the LLM-agent convenience layer, a per-principal aggregated transparent schema, and debounced re-export for hot-write tables.
• Upstream (other-project-blocked): Spark v3-format writes (Spark 3.x), DuckDB iceberg-ext v3 features, DuckDB session TZ shifting timestamp stats.
• Production-readiness ops (deployment work, not code): HA backends, TLS / ingress, secret management, backup automation, distributed tracing, shipped Grafana dashboards, audit log table, Spark / Trino integration tests, sustained-load + chaos benchmarks, multi-platform CI.