What is a Data Lakehouse? Definition, Architecture & How to Build One

Data Warehouses: Reliable but Expensive

Traditional data warehouses excel at structured analytics. They provide:

• Strong schema enforcement
• ACID transactions
• Fast query performance
• Mature tooling for BI

But they come with trade-offs: proprietary formats, expensive storage, limited support for unstructured data, and vendor lock-in.

Data Lakes: Cheap but Chaotic

Data lakes swung the pendulum the other way. Dump everything into S3 in whatever format you want—structured, semi-structured, unstructured. Storage is cheap, and you have complete flexibility.

The problem? Data lakes quickly became "data swamps." Without transactions, schema management, or data quality controls, they turned into unreliable dumping grounds. Running a query meant hoping the data was still there and hadn't been corrupted by a concurrent write.

Data Lakehouses: The Convergence

The lakehouse architecture emerged to solve this tension. Keep the cheap, flexible storage of the lake. Add the reliability and performance features of the warehouse. Use open formats to avoid lock-in.

1. Storage Layer

The foundation is object storage—S3, Google Cloud Storage, or Azure Blob Storage. Data lives as immutable files in open columnar formats like Parquet, which provides excellent compression and fast analytical queries.

Why object storage? It's incredibly cheap (fractions of a cent per GB), infinitely scalable, and durable. You're not paying for expensive managed database storage—you're using commodity cloud storage.

2. Table Format Layer

Raw files on object storage aren't enough. You need a table format that adds database-like capabilities on top:

• Transaction logs: Track every change for ACID guarantees
• Metadata management: Know which files contain which data
• Schema evolution: Add columns without rewriting everything
• Time travel: Access any previous version of your data
• Partition pruning: Skip irrelevant files during queries

This is where formats like Apache Iceberg, Delta Lake, and DuckLake come in. They turn a collection of files into something that behaves like a proper database table.

3. Compute Layer

The query engine that actually reads and processes your data. Because lakehouses use open formats, you can use multiple engines—DuckDB for local and cloud development, Spark for heavy batch processing, MotherDuck or Trino for large-scale queries—all against the same underlying data.

This separation of storage and compute is powerful: scale them independently, pay only for what you use, and avoid vendor lock-in.

The Medallion Architecture Pattern

Many lakehouses organize data using the medallion architecture (also called multi-hop architecture), a design pattern that divides data into three quality layers:

• Bronze (Raw): Landing zone for raw, unprocessed data exactly as it arrives from source systems. No transformations, just ingestion.
• Silver (Cleaned): Validated, deduplicated, and conformed data. Schema enforcement happens here, along with basic cleaning and standardization.
• Gold (Curated): Business-level aggregates, feature tables, and analytics-ready datasets optimized for specific use cases.

This pattern provides clear data lineage, enables incremental processing at each layer, and makes it easy to reprocess data when business logic changes. With DuckLake, you can implement medallion architecture using separate schemas or databases for each layer, with full ACID transactions ensuring consistency as data flows through the pipeline.

The Metadata File Explosion

Formats like Apache Iceberg were designed to avoid requiring a database. Noble goal, but the implementation creates its own headaches.

To query an Iceberg table, your engine must:

1. Query a REST catalog to find the current metadata file
2. Read the metadata.json file from object storage
3. Parse manifest lists pointing to manifest files
4. Read multiple manifest files to find actual data files
5. Finally read the data

That's potentially dozens of round trips to object storage before you read a single row of data. Each round trip adds latency. Each metadata file adds complexity.

As tables grow, so does the metadata. Large Iceberg tables can have thousands of manifest files. Compaction jobs become necessary just to keep metadata manageable. You're running infrastructure to manage your infrastructure.

The "Small Files" Problem

Streaming data into a traditional lakehouse creates another headache: small files. Each micro-batch creates new Parquet files. Thousands of tiny files tank query performance. So you need compaction jobs to merge them. More infrastructure, more complexity, more things that can break.

The Concurrency Challenge

File-based locking mechanisms in traditional formats create bottlenecks. When multiple writers try to commit simultaneously, conflict resolution gets messy. Optimistic concurrency control with file-based manifests wasn't designed for high-throughput concurrent writes.

The Key Innovation: SQL-Based Metadata

DuckLake stores all metadata in a standard SQL database instead of files on object storage. Finding which files to read for a query becomes a fast, indexed SQL lookup instead of a slow scan through manifest files.

The difference? Milliseconds versus seconds. A quick indexed lookup versus sequential file reads across the network.

Why This Matters

10-100x faster metadata operations: Database indexes beat file scans. Every time.

No small file problem: DuckLake's data inlining feature stores tiny inserts directly in the metadata database. No micro-Parquet files cluttering your storage. When data accumulates, flush it to Parquet when it makes sense.

Simpler concurrency: Database transactions handle concurrent writes naturally. PostgreSQL can handle thousands of transactions per second—more than enough for even aggressive multi-writer scenarios.

No compaction jobs: Without metadata file proliferation, you don't need constant compaction to keep things manageable.

True ACID across tables: Cross-table transactions are just... transactions. The database handles it.

DuckLake Architecture

DuckLake has three components:

1. Data files: Standard Parquet files on object storage (S3, GCS, Azure)
2. Metadata catalog: A SQL database (PostgreSQL, MySQL, SQLite, DuckDB, or MotherDuck)
3. Compute: DuckDB as the reference implementation, with Spark support coming

The metadata database stores schemas, file pointers, partition information, statistics, and transaction history. The actual data stays in Parquet files on cheap object storage. Compute engines query the catalog to find relevant files, then read directly from storage.

Iceberg vs. Delta Lake vs. DuckLake: How to Choose

Choosing between open table formats? Here's how they compare:

When to choose Iceberg: You need maximum ecosystem compatibility and are comfortable managing catalog infrastructure. Iceberg has the broadest engine support.

When to choose Delta Lake: You're heavily invested in Databricks or Spark, and want tight integration with that ecosystem.

When to choose DuckLake: You want lakehouse benefits without operational complexity. Ideal for teams using DuckDB/MotherDuck who value simplicity over ecosystem breadth.

Option 1: Fully Managed with MotherDuck

The fastest path to a working lakehouse. One command:

Copy code
CREATE DATABASE my_lakehouse (TYPE DUCKLAKE);

That's it. MotherDuck manages both the metadata catalog and object storage. Start creating tables immediately:

Copy code
USE my_lakehouse;

CREATE TABLE events (
    event_id UUID,
    user_id INTEGER,
    event_type VARCHAR,
    properties JSON,
    created_at TIMESTAMP
);

INSERT INTO events VALUES 
    (gen_random_uuid(), 1, 'page_view', '{"page": "/home"}', now()),
    (gen_random_uuid(), 2, 'purchase', '{"amount": 99.99}', now());

Your data is stored as Parquet files with full ACID guarantees, time travel, and all the lakehouse benefits—without managing any infrastructure.

Option 2: Bring Your Own Storage

Want to use your own S3 bucket? Connect it to MotherDuck:

Copy code
CREATE DATABASE my_lakehouse (
    TYPE DUCKLAKE,
    DATA_PATH 's3://my-bucket/lakehouse/'
);

MotherDuck manages the metadata catalog while your data stays in your bucket. Full control over data residency with managed simplicity.

Option 3: Fully Self-Managed

Run everything yourself with DuckDB and PostgreSQL:

Copy code
-- Install the extension
INSTALL ducklake;
LOAD ducklake;

-- Create a DuckLake backed by PostgreSQL
ATTACH 'ducklake:postgres:host=localhost dbname=lakehouse_catalog' 
    AS my_lake (DATA_PATH 's3://my-bucket/data/');

-- Start using it
CREATE TABLE my_lake.events (...);

Use PostgreSQL for production multi-writer scenarios, or SQLite/DuckDB for local development.

Ideal Use Cases

• Analytics platforms: Central repository for business intelligence
• ML feature stores: Versioned, reproducible training data
• Event streaming: Land streaming data with transactional guarantees
• Data sharing: Open formats enable cross-organization collaboration
• Hybrid architectures: Bridge operational and analytical workloads

When to Consider Alternatives

• Pure OLTP workloads: Use a transactional database instead
• Real-time point lookups: Lakehouses optimize for analytical scans, not single-row fetches
• Tiny datasets: If your data fits in memory, a lakehouse is overkill