catalog.sql
On this page
Catalog: where your data lives
Phase: Catalog | Order: 4 | Required: Yes
One ATTACH statement tells OndatraSQL where to store metadata and Parquet data.
Backends
SQLite (default)
ATTACH 'ducklake:sqlite:ducklake.sqlite' AS lake
(DATA_PATH 'ducklake.sqlite.files');
-- With cloud data path
ATTACH 'ducklake:sqlite:ducklake.sqlite' AS lake
(DATA_PATH 's3://my-bucket/data/');
DuckDB
ATTACH 'ducklake:duckdb:ducklake_catalog.duckdb' AS lake
(DATA_PATH 'ducklake.duckdb.files');
PostgreSQL
ATTACH 'ducklake:postgres:dbname=ducklake_catalog host=${PG_HOST} port=5432 user=ondatra' AS lake
(DATA_PATH 's3://my-bucket/data/');
Requires postgres in extensions.sql. Environment variables (${PG_HOST}) are expanded before parsing.
Leave the password out of the connection string and export PGPASSWORD instead. The postgres extension connects through libpq, which reads it from the environment. A password written into the string — password=${PG_PASSWORD} works too — becomes part of the SQL, and a failed ATTACH echoes that SQL in its error. OndatraSQL masks credentials in the errors DuckDB returns to it, and in the errors and warnings a run prints (password=[REDACTED]), but that is a pattern match on text — a password that is never in the statement cannot leak from it at all.
If a variable the file uses is not set, it expands to an empty string, and the error that follows names it: host=${PG_HOST} with PG_HOST unset becomes host= port=5432, libpq reports that it cannot resolve port=5432, and the message starts with not set in the environment: PG_HOST;.
Backend Comparison
| SQLite | DuckDB | PostgreSQL | |
|---|---|---|---|
| Setup | Zero config | Zero config | Requires a PostgreSQL server |
| Read access | OndatraSQL only | OndatraSQL only | Any PostgreSQL client |
| Multi-machine | No | No | Yes |
| Cloud data | Yes (S3/GCS) | Yes (S3/GCS) | Yes |
| Sandbox | File copy | File copy | CREATE DATABASE TEMPLATE |
DuckLake version and DuckDB 1.5
A DuckLake catalog records the format version it was created with, and compatibility only runs one way:
| Catalog | Created by | DuckDB 2.0 (OndatraSQL) | DuckDB 1.5 |
|---|---|---|---|
| DuckLake 1.0 | DuckDB 1.5, or 2.0 with DUCKLAKE_VERSION '1.0' | Reads and writes it, and leaves it at 1.0 | Reads and writes it |
| DuckLake 1.1 | DuckDB 2.0 by default, including ondatrasql init | Reads and writes it | Refuses to attach it (catalog version mismatch) |
An existing catalog keeps its version: running OndatraSQL on DuckDB 2.0 against a catalog made under 1.5 does not upgrade it, so tools still on 1.5 can keep reading it. A new catalog gets the current DuckLake format, which 1.5 cannot read.
To create a new catalog that 1.5 can read, ask for the older format when it is first attached:
ATTACH 'ducklake:sqlite:ducklake.sqlite' AS lake
(DATA_PATH 'ducklake.sqlite.files', DUCKLAKE_VERSION '1.0');
The option only matters on the attach that creates the catalog, and an attach that asks for 1.0 fails on a catalog that is already 1.1 (Cannot downgrade a DuckLake catalog).
Do not add AUTOMATIC_MIGRATION to the ATTACH. It upgrades the catalog in place to the newest format, there is no way back, and from then on DuckDB 1.5 cannot attach it.
PostgreSQL Architecture
MetabaseSandbox creates a temporary catalog copy via CREATE DATABASE TEMPLATE. This requires exclusive access to the primary, so all other connections are terminated during the fork. Read replicas are unaffected.
Options
Data Inlining
CALL lake.set_option('data_inlining_row_limit', 20); -- default: 10
CALL lake.set_option('data_inlining_row_limit', 0); -- disable
Deletion Vectors
CALL lake.set_option('write_deletion_vectors', true);
Encryption
ATTACH 'ducklake:sqlite:ducklake.sqlite' AS lake
(DATA_PATH 'ducklake.sqlite.files', ENCRYPTED);
Each file gets a unique encryption key stored in the catalog. Decryption is transparent.