Data lakes, warehouses and lakehouses

Two kinds of workload

	OLTP (transactions)	OLAP (analytics)
Purpose	Run the application	Understand the business
Queries	Many small reads and writes of single rows	Few large scans and aggregations over many rows
Storage layout	Row oriented	Column oriented
Examples	PostgreSQL, MySQL for an order system	A warehouse for revenue by region and month

Running heavy analytics on the production database slows the application, so analytical data is copied into systems built for it.

Columnar storage and file formats

Analytical queries typically read a few columns across millions of rows. Storing data by column means:

• A query reads only the columns it needs.
• Values in one column are similar, so they compress extremely well.
• Engines can skip chunks using min/max statistics.

Parquet and ORC are the standard open columnar file formats. Compared with CSV or JSON they are much smaller and faster to query.

Three architectures

100%

Figure SDA-BDP-03-1 · Three architectures · © forcsguy.com

Data warehouse

A curated, structured store optimised for SQL analytics, with a defined schema (often a star schema: a central fact table of events such as sales, surrounded by dimension tables such as customer, product and date). Reliable and fast, but historically costly and rigid with raw or unstructured data.

Data lake

Raw files of any format kept cheaply in object storage, with schema applied when read. Flexible and cheap, and good for machine learning and unstructured data. Risks: no transactions, inconsistent data and "data swamps" nobody trusts.

Lakehouse

The two combined: files in open formats on object storage, with a table format adding the warehouse features.

• Delta Lake, Apache Iceberg, Apache Hudi: a transaction log or metadata layer that provides ACID transactions, schema evolution, time travel (query data as it was), and efficient updates and deletes.
• One copy of data serves BI, SQL, streaming and machine learning, using different engines (Spark, Trino, warehouses) on the same tables.

Moving data: ETL and ELT

• ETL: extract, transform, then load. Transformation happens before the data reaches the warehouse.
• ELT: extract, load raw, then transform inside the warehouse or lake engine. Common today, since storage is cheap, compute is elastic, and you keep the raw data to reprocess.
• Change data capture (CDC) streams database changes into the lake continuously, instead of nightly dumps.

Organising data: layers

A common pattern, sometimes called medallion layers:

1. Raw (bronze): exactly as received.
2. Cleaned (silver): deduplicated, typed, validated.
3. Business-ready (gold): modelled tables and aggregates for reports.

Good practice

• Partition large tables (by date, for example) so queries scan less.
• Keep files a sensible size (hundreds of MB); many tiny files slow everything.
• Enforce data quality checks and schemas at ingestion.
• Provide a catalogue (what data exists, who owns it, lineage) and access controls (row, column and table level).
• Track cost: scanning unpartitioned petabytes is expensive.
• Separate storage from compute so different teams scale their engines independently.

Real-world reading