Warehouses, Lakes and Lakehouses

Where analytical data lives

The basics are on Data Warehousing and Analytics: a data warehouse is a database built for analysis, with a schema decided before data goes in; a data lake keeps raw files in object storage and applies structure when they are read. This page is about what those choices mean in practice, for the people who build and run them.

A data mart is a smaller analytical store, or a set of tables within a warehouse, for one subject or one department: billing, customer service, the grid. It is often the first thing an organization builds, because one team's questions are easier to model than the whole company's. Kestrel's KPI pack needs exactly one: billing and consumption, owned by Femke Jansen's billing department. The risk of marts is that each one defines "customer" and "active contract" its own way; the shared definitions on Data Modeling for Analytics are what keep several marts consistent.

Files in a lake

Analytical files in a lake are nearly always Apache Parquet: a columnar, compressed format that carries its own column types (see CSV, JSON and Parquet). They are organized in folders by a column that most queries filter on, usually a date. DuckDB 1.5 (the version the examples use) writes that layout directly; the example runs in an empty folder and writes three days of Kestrel's sample readings:

-- Three days of readings as Parquet files, one folder per day
COPY (SELECT * FROM daily_readings WHERE reading_date <= DATE '2026-09-03')
TO 'readings' (FORMAT parquet, PARTITION_BY (reading_date));

SELECT file FROM glob('readings/**/*.parquet') ORDER BY file;

-- Read the folder back: the folder names become a column again,
-- and a filter on that column only opens the matching folder
SELECT reading_date, count(*) AS readings, sum(kwh_import) AS kwh
FROM read_parquet('readings/**/*.parquet', hive_partitioning = true)
WHERE reading_date = DATE '2026-09-02'
GROUP BY reading_date;

┌─────────────────────────────────────────────────┐
│                      file                       │
│                     varchar                     │
├─────────────────────────────────────────────────┤
│ readings/reading_date=2026-09-01/data_0.parquet │
│ readings/reading_date=2026-09-02/data_0.parquet │
│ readings/reading_date=2026-09-03/data_0.parquet │
└─────────────────────────────────────────────────┘
┌──────────────┬──────────┬───────────────┐
│ reading_date │ readings │      kwh      │
│     date     │  int64   │ decimal(38,3) │
├──────────────┼──────────┼───────────────┤
│ 2026-09-02   │       12 │       199.448 │
└──────────────┴──────────┴───────────────┘

The folder names reading_date=2026-09-01 follow the Hive-style partitioning convention, named after Apache Hive, which most engines understand. A query that filters on the partition column skips every other folder without opening it (partition pruning), which is what makes a lake of years of data cheap to query for one month. Pick a partition column with a modest number of values: one folder per day works; one per meter would create 400,000 folders of tiny files, and lots of small files are slow to list and read.

What plain files cannot do

A folder of files is not a table. Nothing records which files together form "the current data", so the storage cannot help when something goes wrong. Here the load for 3 September is rerun, and the rerun appends a new file instead of replacing the old one:

COPY (SELECT * FROM daily_readings WHERE reading_date <= DATE '2026-09-03')
TO 'readings' (FORMAT parquet, PARTITION_BY (reading_date));

-- A rerun for 3 September that appends instead of replacing
COPY (SELECT * FROM daily_readings WHERE reading_date = DATE '2026-09-03')
TO 'readings' (FORMAT parquet, PARTITION_BY (reading_date), APPEND);

SELECT reading_date, count(*) AS readings
FROM read_parquet('readings/**/*.parquet', hive_partitioning = true)
GROUP BY reading_date
ORDER BY reading_date;

┌──────────────┬──────────┐
│ reading_date │ readings │
│     date     │  int64   │
├──────────────┼──────────┤
│ 2026-09-01   │       12 │
│ 2026-09-02   │       12 │
│ 2026-09-03   │       24 │
└──────────────┴──────────┘

Twenty-four readings for twelve meters: the same doubling as the non-idempotent load on Data Pipelines. With plain files the fix is to delete the day's folder and write it again, and that exposes the other gaps:

• No atomic commit. Between "delete the folder" and "write the new file", a dashboard query sees no data for that day. If the job dies in between, the day stays empty.
• No isolation. Two jobs writing the same folder, or a reader listing files while a writer adds them, see a mix.
• No row-level changes. Correcting one reading, or erasing one customer's data on request, means rewriting every file that contains it.
• No history. Once a file is overwritten, last week's version of the table is gone.

Open table formats

An open table format is a published specification for storing a table as data files (usually Parquet) plus metadata files that record exactly which data files make up each version of the table. Writers never change existing files: they write new data files and then a new metadata version, and switching the table to that new version is a single atomic step. Readers always start from a complete version, so they see either all of a change or none of it. Because the specification is open, several engines (Spark, Trino, Flink, DuckDB, and the major cloud warehouses, with varying support) can read and write the same table.

The metadata gives a table in files what a database table has:

• Atomic commits and isolation: concurrent writers are detected, and readers see one consistent snapshot.
• Row-level updates and deletes, written as new files or as delete markers, without editing old files in place.
• Schema evolution: add, rename or drop a column without rewriting the data (in Delta Lake, rename and drop need column mapping switched on first).
• Statistics for pruning: minimum and maximum values per file, so a query can skip files, not just folders.
• History: old snapshots stay readable until they are expired.

Three formats share most of the market. All facts below are as of October 2026.

Apache Iceberg

Apache Iceberg is an open table format of the Apache Software Foundation, created at Netflix. Its specification has three adopted versions; version 2 added row-level updates and deletes, and version 3 added new types (such as variant for semi-structured data and geospatial types), default column values, row lineage and binary deletion vectors. Version 4 is under development and not yet adopted. The Java reference implementation is at release 1.12.0 (30 September 2026), and PyIceberg, the Python library, at 0.12.0. Iceberg is notable for partition evolution: you can change how a table is partitioned (by month, later by day) without rewriting the old data. Databricks agreed in June 2024 to buy Tabular, the company founded by Iceberg's original creators.

Delta Lake

Delta Lake is an open table format created by Databricks and now a Linux Foundation project. A Delta table is a folder of Parquet files plus a _delta_log folder of numbered JSON commit files, with periodic checkpoints. It provides transactions, time travel and schema enforcement; its UniForm feature writes the metadata that lets Iceberg and Hudi readers read a Delta table too. The latest release is 4.4.1 (5 October 2026). Apache Hudi (1.2.1, 24 September 2026) is the third format, with a focus on frequent upserts.

readings_delta/                      readings_iceberg/
├── _delta_log/                      ├── metadata/
│   ├── 00000000000000000000.json    │   ├── v1.metadata.json
│   ├── 00000000000000000001.json    │   ├── v2.metadata.json
│   └── 00000000000000000002.json    │   ├── snap-…-1.avro        (manifest list)
├── reading_date=2026-09-01/         │   └── …-m0.avro            (manifest)
│   └── part-00000-….parquet         └── data/
└── reading_date=2026-09-02/             ├── reading_date=2026-09-01/….parquet
    └── part-00000-….parquet             └── reading_date=2026-09-02/….parquet

Never edit, move or delete files inside such a table with ordinary file tools. The metadata still lists them; the next reader fails or, worse, silently returns wrong results. Use the engine's own commands, such as compaction (merging small files) and snapshot expiry, to clean up.

Time travel

Time travel is querying a table as it was at an earlier version or point in time, by reading an older snapshot. It answers "what did the dashboard show on Monday?", reproduces a report after the data was corrected, and lets you undo a bad load by restoring the previous snapshot. In Spark SQL, both Iceberg and Delta tables accept this syntax (not run: Spark is not installed here):

-- The readings table as it was before Tuesday's load
SELECT reading_date, count(*) AS readings
FROM kestrel.readings TIMESTAMP AS OF '2026-10-05 23:00:00'
GROUP BY reading_date;

-- Or by version number (Delta) or snapshot id (Iceberg)
SELECT count(*) FROM kestrel.readings VERSION AS OF 41;

History is not free: every snapshot keeps its files. Tables are configured to expire snapshots after a set period, and only then are the old files deleted. That matters for privacy: data you "deleted" for a customer is still readable through time travel until the snapshots that contain it expire (see Data Governance and Privacy).

The lakehouse

A lakehouse is a data architecture that keeps all analytical data as open files in object storage, managed through an open table format, and lets several engines run warehouse-style SQL, data science and machine learning on the same tables. It aims to replace the common two-step setup in which raw data lands in a lake and a copy is loaded into a warehouse for reporting. The term was popularized by Databricks, and vendors use it loosely, so ask what a product means by it.

The parts of a lakehouse:

1. Storage: object storage (or, small scale, a file system) holding Parquet files.
2. Table format: Iceberg, Delta Lake or Hudi.
3. Catalog: the service that maps table names to their current metadata and controls access, such as the open source Unity Catalog or a cloud provider's catalog.
4. Engines: whatever reads and writes the tables: Spark for heavy batch jobs, a SQL engine for dashboards, Python for data science.

The benefit is one copy of the data, in an open format, readable by the tool that fits each job, with storage paid for separately from compute. The cost is that you now run, or rent, four components instead of one database, and must understand how they fail. A lakehouse does not make modeling unnecessary either: the tables people query still need facts, dimensions and agreed definitions.

Bronze, silver and gold

The medallion architecture organizes the tables of a lakehouse or warehouse in three layers of increasing quality. Databricks, which named the pattern, describes it as "a data design pattern used to logically organize data in a lakehouse". It is a convention, not a standard: some teams call the layers raw, staging and marts, and some add more.

• Bronze: data exactly as delivered by the source, "as-is", only made queryable (for example, CSV converted to Parquet). It is kept so everything above it can be rebuilt.
• Silver: cleaned and conformed: deduplicated, types fixed, codes standardized, suspicious values flagged, sources joined on common keys. In Databricks' words, "just enough" cleaning to give an enterprise-wide view.
• Gold: consumption-ready tables for one purpose: the fact and dimension tables of a dimensional model and the aggregated KPI tables behind a dashboard. Denormalized and read-optimized.

The example builds the three layers as views over Kestrel's sample data, in which six data-quality problems were planted on purpose (see Data Types and Data Quality), and compares bronze with gold:

-- Bronze: the tables exactly as delivered (here: the sample data as loaded)

-- Silver: cleaned and conformed, one row per meter per day
CREATE VIEW silver_readings AS
SELECT DISTINCT
  meter_id, reading_date, kwh_import, kwh_export,
  kwh_import < 0 AS suspect               -- flagged, not deleted
FROM daily_readings;

CREATE VIEW silver_contracts AS
SELECT c.*
FROM contracts c
JOIN customers cu USING (customer_id);    -- orphans stay behind in bronze

-- Gold: the numbers the KPI pack shows
CREATE VIEW gold_kwh_per_tariff AS
SELECT c.tariff,
       count(DISTINCT r.meter_id) AS meters,
       sum(r.kwh_import)          AS kwh_import
FROM silver_readings r
JOIN meters m USING (meter_id)
JOIN silver_contracts c USING (contract_id)
WHERE NOT r.suspect
GROUP BY c.tariff;

-- Bronze and gold side by side
SELECT g.tariff,
       b.kwh_import AS bronze_kwh,
       g.kwh_import AS gold_kwh
FROM gold_kwh_per_tariff g
JOIN (SELECT c.tariff, sum(r.kwh_import) AS kwh_import
      FROM daily_readings r
      JOIN meters m USING (meter_id)
      JOIN contracts c USING (contract_id)
      GROUP BY c.tariff) b USING (tariff)
ORDER BY g.tariff;

┌───────────┬───────────────┬───────────────┐
│  tariff   │  bronze_kwh   │   gold_kwh    │
│  varchar  │ decimal(38,3) │ decimal(38,3) │
├───────────┼───────────────┼───────────────┤
│ day_night │      1828.518 │      1828.518 │
│ dynamic   │      2057.276 │      2057.276 │
│ fixed     │      1074.240 │      1074.240 │
│ variable  │       597.561 │       372.140 │
└───────────┴───────────────┴───────────────┘

Only the variable tariff changes, by 225.421 kWh, and every part of that difference can be explained: the orphan contract CT-20014 (customer C-10099 does not exist) took 221.324 kWh with it, the duplicate row of meter M-30005 added 8.307 kWh, and leaving out the negative reading of M-30009 adds 4.210 kWh back. A difference you can explain to the last decimal is the sign of a trustworthy layer; a difference nobody can explain is a bug.

Two choices in the silver layer deserve a second look. Dropping the orphan contract hides 221 kWh of energy that a real meter delivered: fine for a per-customer KPI, wrong for the total energy balance, which is why Data Modeling for Analytics maps such rows to an "unknown customer" instead. And the outlier on meter M-30001 (91.300 kWh on 8 September, probably 9.130) passes through untouched, because silver cannot know the true value; it should be flagged for the data owner, not silently "corrected".

Transformation with dbt

dbt (data build tool) is an open-source command-line tool for the transform step of ELT. You write each table or view as a SQL SELECT statement in its own file, a model ("a single file containing a final select statement", in dbt's documentation), and dbt creates the tables in the right order, runs tests, and generates documentation. dbt does not extract or load data; it only transforms what is already in the warehouse or lakehouse.

A model refers to other models with ref() and to raw tables with source(). From those references dbt builds the dependency graph, so you never maintain a run order by hand (not run: dbt is not installed here):

-- models/silver/silver_readings.sql
select distinct
    meter_id,
    reading_date,
    kwh_import,
    kwh_export,
    kwh_import < 0 as suspect
from {{ source('bronze', 'daily_readings') }}

-- models/gold/gold_kwh_per_tariff.sql
select
    c.tariff,
    count(distinct r.meter_id) as meters,
    sum(r.kwh_import) as kwh_import
from {{ ref('silver_readings') }} as r
join {{ ref('silver_meters') }} as m using (meter_id)
join {{ ref('silver_contracts') }} as c using (contract_id)
where not r.suspect
group by c.tariff

Data tests are, in dbt's words, "assertions you make about your models": queries that select the failing rows, so a test passes when it returns nothing. Four generic tests are built in (unique, not_null, accepted_values and relationships) and are declared in YAML next to the models:

# models/silver/schema.yml (simplified)
version: 2

models:
  - name: silver_contracts
    columns:
      - name: contract_id
        data_tests:
          - unique
          - not_null
      - name: tariff
        data_tests:
          - accepted_values:
              arguments:
                values: ["fixed", "day_night", "dynamic", "variable"]
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('silver_customers')
                field: customer_id

Recent dbt versions put test parameters under arguments:; older projects put them directly under the test name. Check the documentation for the version you use. Rules that need more than one column, such as "one reading per meter per day", are written as a SQL file in the tests/ folder. Underneath, every test is a query like these, which run the same checks directly on Kestrel's sample tables:

-- Each test is a query that returns the failing rows; zero rows = pass
SELECT 'unique (meter_id, reading_date)' AS test, count(*) AS failures
FROM (SELECT meter_id, reading_date FROM daily_readings
      GROUP BY meter_id, reading_date HAVING count(*) > 1)
UNION ALL
SELECT 'not_null kwh_import', count(*)
FROM daily_readings WHERE kwh_import IS NULL
UNION ALL
SELECT 'accepted_values tariff', count(*)
FROM contracts WHERE tariff NOT IN ('fixed', 'day_night', 'dynamic', 'variable')
UNION ALL
SELECT 'relationships contracts -> customers', count(*)
FROM contracts c LEFT JOIN customers cu USING (customer_id)
WHERE cu.customer_id IS NULL
UNION ALL
SELECT 'kwh_import >= 0', count(*)
FROM daily_readings WHERE kwh_import < 0
ORDER BY test;

┌──────────────────────────────────────┬──────────┐
│                 test                 │ failures │
│               varchar                │  int64   │
├──────────────────────────────────────┼──────────┤
│ accepted_values tariff               │        0 │
│ kwh_import >= 0                      │        1 │
│ not_null kwh_import                  │        0 │
│ relationships contracts -> customers │        1 │
│ unique (meter_id, reading_date)      │        1 │
└──────────────────────────────────────┴──────────┘

Three of the six planted problems are caught by tests this simple: the duplicate, the orphan and the negative value. The missing day and the outlier need tests that know what to expect (30 days per meter; a reading within a factor of the meter's normal range), and the lower-case city needs a standardization rule. dbt also has snapshots, which record how a source row changes over time as a type 2 slowly changing dimension.

Kestrel's choice

For the KPI pack, Joost Bakker proposed a deliberately small design in ADR-0009, "Analytics store separate from db01 (DuckDB + Parquet files)":

• Bronze: the nightly KBS export and the day's meter readings, converted to Parquet, one folder per day, kept as delivered.
• Silver and gold: SQL models, run by the nightly pipeline (see Data Pipelines) with DuckDB, with the tests above as a gate before publishing.
• Not now: a cloud lakehouse with a table format and a catalog. The ADR names the trigger for revisiting the decision: several teams writing the same tables, or data that no longer fits one server.
• Never: analytics queries on db01, which Hamid Rahimi keeps for the applications.

Without a table format, bronze and gold get their safety from the pipeline instead: every day is written to a new folder and swapped in only when complete, and a failed run leaves yesterday's files in place. That is a reasonable trade for one team and one writer. The ADR, and the reasons it gives, are what let a successor decide when that trade no longer holds (see Documenting Architecture).