Lakehouse – Phase 1 Metadata Catalog
Every lakehouse has two layers: data files (Parquet files containing actual rows) and metadata (which files exist, what columns they have, what value ranges each file contains). The metadata layer is what separates a lakehouse from a pile of Parquet files. Apache Iceberg stores this metadata as a tree of manifest files (JSON/Avro) on object storage. DuckLake takes a different approach: it stores metadata in a relational database (Postgres, MySQL, or SQLite). This is not just a different storage location — it fundamentally changes how the entire system works.
In this series, we build a small lakehouse engine step by step in Go to understand how open table formats work under the hood. The approach mirrors DuckLake’s architecture: Postgres for metadata, Parquet for data, and a clean separation between the two.
Here is the roadmap for the phases to come:
- Phase 1: Metadata catalog
- Phase 2: Parquet data files
- Phase 3: Ingest pipeline
- Phase 4: Scanning and query
- Phase 5: Snapshots and time travel
- Phase 6: Schema evolution
- Phase 7: Deletes and updates
- Phase 8: Storage and access
Full Source Code
The code referenced in this post can be found in https://gitlab.com/kimserey.lam/lake-learn.
Why a Database Instead of Manifest Files (catalog/store.go)
In Iceberg, metadata is a tree of files: a metadata.json points to a manifest list, which points to manifests, which list individual data files with their statistics. Every query starts by downloading and parsing this chain of files.
DuckLake replaces this entire manifest tree with tables in a relational database. The “which files should I scan?” question becomes a SQL query with JOINs and WHERE clauses. The database handles indexing, concurrency, and atomic commits natively.
The consequences cascade through every part of the system:
File pruning becomes SQL. Instead of parsing manifest files to find which data files might match a WHERE clause, the engine queries the stats table: WHERE max_value >= '100'.
Atomic commits are free. Iceberg achieves atomicity by writing new manifest files and performing an atomic pointer swap (rename). With a database, atomicity is just BEGIN and COMMIT.
Schema history is queryable. Want to know what columns existed at snapshot 5? That is a SQL query with a snapshot filter.
The tradeoff is real. You now depend on a running database instance. Iceberg can operate with just a filesystem.
In DuckLake’s C++ source, DuckLakeMetadataManager (src/storage/ducklake_metadata_manager.cpp) handles all interactions with the metadata database.
Our Go equivalent wraps a Postgres connection pool:
1
2
3
4
5
6
7
type MetadataStore struct {
pool *pgxpool.Pool
}
func NewMetadataStore(pool *pgxpool.Pool) *MetadataStore {
return &MetadataStore{pool: pool}
}
All write operations use a WithTransaction method that accepts a closure. This pattern ensures the transaction is always committed or rolled back:
1
2
3
4
5
6
7
8
9
10
11
12
func (s *MetadataStore) WithTransaction(ctx context.Context, fn func(tx pgx.Tx) error) error {
tx, err := s.pool.Begin(ctx)
if err != nil {
return fmt.Errorf("begin transaction: %w", err)
}
defer tx.Rollback(ctx)
if err := fn(tx); err != nil {
return err
}
return tx.Commit(ctx)
}
The Eight Metadata Tables (catalog/schema.go)
The metadata database contains eight tables. Together they describe the complete state of the lakehouse at any point in time.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
┌─────────────────────────────────────────────────────────────────────┐
│ METADATA DATABASE (Postgres) │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌──────────────────┐ │
│ │ ducklake_schema │───▶│ ducklake_table │───▶│ ducklake_column │ │
│ │ (namespaces) │ │ (table defs) │ │ (column defs) │ │
│ └─────────────────┘ └────────┬────────┘ └──────────────────┘ │
│ │ │
│ ┌─────────────▼──────────────┐ │
│ │ ducklake_data_file │ │
│ │ (Parquet file registry) │ │
│ └─────────────┬──────────────┘ │
│ │ │
│ ┌───────────────────┼───────────────────┐ │
│ ▼ ▼ │
│ ┌───────────────────────────┐ ┌──────────────────────────┐ │
│ │ ducklake_file_column_stats│ │ ducklake_delete_file │ │
│ │ (min/max/null) │ │ (positional deletes) │ │
│ └───────────────────────────┘ └──────────────────────────┘ │
│ │
│ ┌─────────────────────┐ ┌─────────────────────────────┐ │
│ │ ducklake_snapshot │───▶│ ducklake_snapshot_changes │ │
│ │ (version history) │ │ (change descriptions) │ │
│ └─────────────────────┘ └─────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────┘
ducklake_snapshot
Version history. Each mutation creates a new snapshot with a monotonically increasing ID. The next_catalog_id and next_file_id fields are counters used to allocate unique IDs for new objects.
1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS ducklake_snapshot (
snapshot_id BIGSERIAL PRIMARY KEY,
snapshot_time TIMESTAMPTZ NOT NULL DEFAULT now(),
schema_version BIGINT NOT NULL DEFAULT 0,
next_catalog_id BIGINT NOT NULL DEFAULT 1,
next_file_id BIGINT NOT NULL DEFAULT 1
)
ducklake_table
Table definitions with snapshot-based visibility. Setting end_snapshot means the table was dropped.
1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS ducklake_table (
table_id BIGINT PRIMARY KEY,
schema_id BIGINT NOT NULL,
name TEXT NOT NULL,
begin_snapshot BIGINT NOT NULL,
end_snapshot BIGINT
)
ducklake_column
Column definitions with permanent field IDs for schema evolution. A column’s field_id never changes even if the column is renamed. Old name rows get end_snapshot set; new name rows get the same field_id with a new begin_snapshot.
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE IF NOT EXISTS ducklake_column (
column_id BIGINT PRIMARY KEY,
table_id BIGINT NOT NULL,
field_id BIGINT NOT NULL,
column_order INT NOT NULL,
name TEXT NOT NULL,
type TEXT NOT NULL,
nullable BOOLEAN NOT NULL DEFAULT true,
begin_snapshot BIGINT NOT NULL,
end_snapshot BIGINT
)
ducklake_data_file
Registry of every Parquet data file. Each file is associated with a table and a snapshot range.
1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS ducklake_data_file (
file_id BIGSERIAL PRIMARY KEY,
table_id BIGINT NOT NULL,
path TEXT NOT NULL,
record_count BIGINT NOT NULL,
file_size_bytes BIGINT NOT NULL,
begin_snapshot BIGINT NOT NULL,
end_snapshot BIGINT
)
ducklake_file_column_stats
Per-file, per-column statistics: minimum value, maximum value, and null count. This is the table that makes file pruning possible.
1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS ducklake_file_column_stats (
file_id BIGINT NOT NULL,
field_id BIGINT NOT NULL,
null_count BIGINT NOT NULL DEFAULT 0,
min_value TEXT,
max_value TEXT,
PRIMARY KEY (file_id, field_id)
)
ducklake_delete_file
Tracks positional delete files. When rows are deleted from a data file, a small Parquet file is written listing the deleted row positions.
1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS ducklake_delete_file (
delete_file_id BIGSERIAL PRIMARY KEY,
table_id BIGINT NOT NULL,
data_file_id BIGINT NOT NULL,
path TEXT NOT NULL,
delete_count BIGINT NOT NULL,
begin_snapshot BIGINT NOT NULL,
end_snapshot BIGINT
)
The Go implementation creates all eight tables through InitializeSchema, which iterates over the DDL definitions with IF NOT EXISTS so it is safe to call on an already-initialized database.
Snapshot Visibility: The Universal Filter
Nearly every metadata query includes the same filter pattern:
1
WHERE begin_snapshot <= $1 AND (end_snapshot IS NULL OR end_snapshot > $1)
This is the mechanism behind time travel. Every metadata row has a lifespan defined by begin_snapshot (when it was created) and end_snapshot (when it was retired, or NULL if still active). A query at snapshot N sees only rows whose lifespan includes N.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Snapshot timeline:
0 1 2 3 4 5 6 (snapshot IDs)
│ │ │ │ │ │ │
├────┤ │ │ │ │ │ file_001: begin=0, end=1 (replaced)
│ ├────┴────┴────┤ │ │ file_002: begin=1, end=4 (compacted)
│ │ ├────┴────┤ file_003: begin=4, end=6
│ │ ├─────────┴─────────┴── file_004: begin=2, end=NULL (still active)
Query at snapshot 3:
file_001: begin=0 <= 3, end=1 > 3? NO → invisible
file_002: begin=1 <= 3, end=4 > 3? YES → visible
file_003: begin=4 <= 3? NO → invisible (not yet created)
file_004: begin=2 <= 3, end=NULL → visible
This design means that dropping a table never deletes metadata rows. It sets end_snapshot on the table row, its columns, and its data files. The data remains accessible via time travel at older snapshots.
In Go, the filter is defined as a reusable constant:
1
const visibilityFilter = `begin_snapshot <= $1 AND (end_snapshot IS NULL OR end_snapshot > $1)`
Every metadata query interpolates this fragment:
1
2
3
4
5
6
7
8
9
10
func (s *MetadataStore) GetTable(ctx context.Context, snap int64, schemaID int64, name string) (*TableDef, error) {
var td TableDef
err := s.pool.QueryRow(ctx, `
SELECT table_id, schema_id, name, begin_snapshot, end_snapshot
FROM ducklake_table
WHERE schema_id = $2 AND name = $3 AND `+visibilityFilter,
snap, schemaID, name,
).Scan(&td.TableID, &td.SchemaID, &td.Name, &td.BeginSnapshot, &td.EndSnapshot)
// ...
}
The Type System (catalog/types.go)
The catalog defines the logical types supported by the lakehouse. These map to DuckLake’s type system in src/include/common/ducklake_types.hpp:
1
2
3
4
5
6
7
8
9
10
11
type LogicalType int
const (
TypeInteger LogicalType = iota // 32-bit signed integer
TypeBigInt // 64-bit signed integer
TypeFloat // 32-bit IEEE 754
TypeDouble // 64-bit IEEE 754
TypeVarchar // variable-length string
TypeBoolean // true/false
TypeTimestamp // timestamp with timezone
)
Every column gets a unique field ID at creation time. Field IDs are written into Parquet files as schema metadata, and they are what the reader uses to map file columns to catalog columns. This is the key to schema evolution:
1
2
3
4
5
6
7
8
9
10
type FieldID int64
type ColumnDef struct {
FieldID FieldID
Name string
Type LogicalType
Nullable bool
BeginSnapshot int64
EndSnapshot *int64 // nil = still active
}
Metadata rows with optional end_snapshot use *int64 in Go. A nil pointer means NULL (the row is still active). This maps cleanly to Postgres NULL semantics.
Comparison: Manifest Files vs Database Catalog
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Iceberg scan planning: DuckLake scan planning:
1. Read metadata.json pointer 1. SELECT df.path, cs.min_value,
2. Fetch manifest-list.avro cs.max_value
3. Parse manifest list → manifest paths FROM ducklake_data_file df
4. Fetch manifest-1.avro JOIN ducklake_file_column_stats cs
5. Parse manifest → file list + stats ON df.data_file_id = cs.data_file_id
6. Fetch manifest-2.avro WHERE df.table_id = $1
7. Parse manifest → more files AND df.begin_snapshot <= $2
8. In-memory: filter files by stats AND (df.end_snapshot IS NULL
9. Return pruned file list OR df.end_snapshot > $2)
AND cs.column_id = $3
Network round-trips: 4+ file fetches AND cs.max_value >= $4
Parse overhead: Avro deserialization
Network round-trips: 1 SQL query
Parse overhead: pgx result scanning
The database approach trades a dependency on a running database for fewer moving parts in every operation.
How Postgres Provides ACID for Metadata
The metadata database gives us four properties for free that manifest-based formats must engineer from scratch:
Atomicity. An INSERT that writes a Parquet file and registers it in the catalog happens inside a single Postgres transaction. If the metadata INSERT fails, the entire transaction rolls back.
Consistency. NOT NULL constraints prevent invalid metadata states. A manifest file has no such enforcement.
Isolation. Two concurrent transactions creating snapshots are serialized by Postgres. Iceberg achieves this with optimistic concurrency — write your manifest, attempt an atomic rename, retry if someone else committed first.
Durability. Once COMMIT returns, the metadata is persisted to Postgres’s WAL.
