Data Warehouse, Data Lake and Lakehouse

An application records orders, a CRM records contacts, a marketing tool records campaigns. To answer “which channel brings in the most loyal customers?”, you need to bring this data together in one place, historize it and make it easy to query, without slowing down the production databases. That is the role of the analytical data platform. This course teaches you how to model it; this first lesson sets out the vocabulary.

The data warehouse

A data warehouse is a database designed for analysis. The data in it is structured (typed tables and columns), integrated (a customer has the same identifier whatever the source), historized (past states are kept) and organized for aggregation queries over large volumes. Product examples: BigQuery, Snowflake, Amazon Redshift, Azure Synapse, or PostgreSQL for modest volumes. Most store data by column: a query that reads three columns of a fifty-column table only reads those three columns.

The schema is applied at write time (schema on write): data that doesn’t respect the expected type is rejected at load.

The data lake

A data lake is low-cost file storage, often object storage (Amazon S3, Azure Data Lake Storage, Google Cloud Storage). You drop everything in as is: CSV exports, JSON events, Parquet files, images, logs. The schema is applied at read time (schema on read): the query engine (Spark, Trino, etc.) interprets the files.

Advantage: you keep everything, at low cost, including what you don’t yet know the use of. Risk: without a catalog or rules, the lake becomes a “swamp” where nobody knows which files are reliable.

The lakehouse

The lakehouse combines the two: the data stays in open files (often Parquet) on object storage, but a table format (Delta Lake, Apache Iceberg, Apache Hudi) adds on top what the lake was missing: transactions, a controlled schema, row updates and deletions, version history. Several engines can read the same tables.

	Warehouse	Lake	Lakehouse
Data	structured	any format	structured and semi-structured
Schema	on write	on read	controlled by the table format
Querying	SQL	various engines	SQL and various engines

The choice depends on volumes, uses (reporting, data science) and the team’s skills; the modeling you are about to learn is the same in all three cases.

ETL or ELT

Historically, you would extract, transform on a dedicated server, then load the result (ETL). With powerful, usage-billed warehouses, you tend to do ELT instead: you load the raw data first, then transform it inside the warehouse, in SQL. Tools like dbt (course DE2) organize these transformations.

The layered architecture

Transformations are arranged in successive layers. Names vary by team:

1. Raw (bronze, raw): a faithful, timestamped copy of the sources, never modified. You can always recompute everything from it.
2. Cleaned (silver, staging): correct types, harmonized column names, normalized values, load duplicates removed. One table per source, with no business logic.
3. Business (gold, marts): analysis-ready models, typically star schemas, with the business rules (“a cancelled order doesn’t count in revenue”).

Here is a typical transformation from the raw layer to the cleaned layer. The source is loaded several times a day: the same order may appear in two loads; we keep the most recent version.

WITH classees AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY _loaded_at DESC) AS rang
    FROM raw_commandes
)
SELECT CAST(id AS INTEGER)       AS commande_id,
       lower(trim(client))       AS client,
       CAST(montant AS REAL)     AS montant,
       upper(devise)             AS devise
FROM classees
WHERE rang = 1;

Notice that the cleaned layer does not filter incomplete rows (a missing amount stays NULL): it makes them visible. Deciding that an order with no amount is excluded from a metric is a business rule, which belongs to the next layer.