Support per-schema DATA_PATH (hot/cold storage placement within one lake)

[esenilsson]

Use case

We run a dbt + DuckLake warehouse: Postgres catalog, data on S3-compatible object storage (Hetzner), compute on a VPS next to the catalog. The lake has the usual medallion shape — staging and intermediate schemas are written once per weekly load and rarely read interactively, while the mart and reporting schemas serve BI and ad-hoc queries all day.

Live footprint per schema:

schema	live size	access pattern
intermediate	312 GB	write-heavy, rarely read after load
mart	195 GB	interactive, latency-sensitive
staging + raw	41 GB	load-time only
reporting	< 1 MB	interactive

Interactive query latency is dominated by S3 round-trips over a 1 Gbit link. We'd like the hot schemas (mart, reporting) to live on the VPS's local NVMe while the cold schemas stay on object storage — same catalog, same lake, different storage roots per schema.

What the spec already models

The metadata model is already hierarchical: data_path → ducklake_schema.path ducklake_table.path → ducklake_data_file.path, each with path_is_relative. So a schema rooted somewhere other than data_path is expressible in the catalog today — e.g. setting ducklake_schema.path to an absolute file:///... path with path_is_relative = false. But no SQL surface exposes this, and hand-editing the catalog is presumably unsupported.

The ask

Something like:

CREATE SCHEMA lake.mart (DATA_PATH 'file:///data/lake/mart/');
-- and/or
ALTER SCHEMA lake.mart SET DATA_PATH 'file:///data/lake/mart/';

with explicit semantics that ALTER ... SET DATA_PATH affects newly written files only — existing files keep their recorded (absolute-resolved) paths and remain readable, analogous to how SET PARTITIONED BY only applies to new writes.

Related

• Hot/cold storage tiering via metadata path rewrites — dangerous? #937 asks the adjacent post-hoc question — mutating existing ducklake_data_file.path entries to absolute paths for hot/cold tiering — and is unanswered. Per-schema DATA_PATH would cover the forward-looking half of that use case cleanly (new writes land in the right tier); migrating existing files would remain Hot/cold storage tiering via metadata path rewrites — dangerous? #937's territory (or "rewrite through a relayout copy").
• Can't use `OVERRIDE_DATA_PATH` with frozen ducklakes #602 (OVERRIDE_DATA_PATH and frozen ducklakes) touches the single-root assumption from another angle.

Workaround we're considering meanwhile

A second DuckLake catalog with a local data_path for mart/reporting, attached alongside the S3-rooted one, relying on DuckDB cross-database queries. It works but splits snapshots/time-travel and transactional boundaries across two catalogs, and every consumer must know to attach both — which is exactly what a per-schema path inside one lake would avoid.