Data lakes and the lakehouse

Not everything Keiko wanted to analyze was a tidy table. Marketing had three years of website clickstream logs in JSON files. Grace Oduya's team had door-count exports in CSV. The weather service the company subscribed to delivered daily files in Parquet. Supplier price lists arrived as spreadsheets. Loading all of that into warehouse tables first would mean designing schemas before anyone knew which questions would be asked. At the clinic, Nikolai introduced the idea that has changed analytics over the last decade: store everything first, cheaply, in its own format, and decide how to shape it when you need it.

The data lake

A data lake is a large store of files in their native formats: structured files such as CSV and Parquet, semi-structured files such as JSON and logs, and unstructured files such as images and documents. It uses low-cost object storage that scales almost without limit. In Azure, data lakes are built on Azure Data Lake Storage Gen2, which is Blob storage with the hierarchical namespace turned on, as lesson 1 described. In Microsoft Fabric, every workspace stores its data in OneLake, a single logical data lake for the whole organization built on the same technology.

Data lakes follow a schema-on-read approach. A warehouse uses schema-on-write: data must fit a defined table structure before it is loaded. A lake accepts files as they arrive and applies a structure only when a job reads them. That makes it easy to keep raw data that may be useful later, and to bring in new sources quickly. It also suits data science and machine learning, which often need raw, detailed data rather than summaries.

The risk in the last row is real. A lake with no organization, no quality checks and no catalogue becomes what people call a data swamp: plenty of files, but nobody trusts them or can find the right one. Lakes need structure, naming conventions, access control and governance, for example with Microsoft Purview, to stay useful.

File formats for analytics

MSAZ-120 introduced common file formats. For analytics in a lake, three matter most. CSV is simple and readable but has no data types and is slow to scan at scale. JSON handles nested and varying structures but is verbose. Parquet is a columnar file format: it stores each column's values together with their data types and compresses them, so analytical engines can read only the columns a query needs. Parquet is the standard format for analytical data in lakes. Avro and ORC are other formats you may meet.

# Why columnar layout helps: compare the bytes a query must read (illustrative)
rows = 1_000_000
columns = {"date": 4, "store": 2, "product": 4, "qty": 2, "amount": 8, "cashier": 4, "notes": 40}
row_bytes = sum(columns.values())
print("Row-based file, read everything:", rows * row_bytes // 1_000_000, "MB")
needed = ["date", "store", "amount"]          # total sales by store and date
print("Columnar file, read 3 columns:", rows * sum(columns[c] for c in needed) // 1_000_000, "MB")

Row-based file, read everything: 64 MB
Columnar file, read 3 columns: 14 MB

The lakehouse

For years, companies ran both a lake and a warehouse: raw files in the lake for data science, and a copy of the important data loaded into the warehouse for reports. Keeping two copies in sync was expensive and error-prone. The lakehouse combines them. It stores data as files in the lake, but in an open table format that adds the features warehouses provide: tables with schemas, ACID transactions so that readers never see half-written data, the ability to update and delete rows, and time travel to query a table as it was at an earlier point.

The table format used by Microsoft Fabric and Azure Databricks is Delta Lake. A Delta table is a folder of Parquet files plus a transaction log that records every change. Engines read the log to know which files make up the current version of the table. Because the data stays in open Parquet files in the lake, many engines can read the same table: Spark for data engineering, SQL for analysts, and Power BI for reports. In Fabric, a lakehouse item stores Delta tables in OneLake and automatically provides a SQL analytics endpoint so that analysts can query the tables with T-SQL without moving the data.

Medallion architecture

Lakehouses are often organized in layers known as the medallion architecture. The bronze layer holds raw data exactly as it arrived, so it can be reprocessed if something goes wrong later. The silver layer holds cleaned and conformed data: duplicates removed, data types fixed, codes standardized, sources joined on common keys. The gold layer holds business-ready tables, often star schemas or summary tables, shaped for reports and dashboards. Each layer is built from the one before by a pipeline or notebook.

Nikolai sketched the Finch Data Hub this way. Bronze would hold nightly StockLine extracts, the mirrored loyalty data, clickstream files, door counts and weather files, untouched. Silver would hold cleaned sales lines, a single list of stores and products, and website sessions linked to loyalty members where possible. Gold would hold the sales star schema from lesson 5, plus a daily store summary that combines sales, door counts and weather. Keiko's reports would read gold; data scientists could go back to silver or bronze when they needed detail.

# A miniature bronze -> silver -> gold flow with plain Python
bronze = [  # raw door-count rows, as received
    "brandon,2026-11-09,142", "Brandon ,2026-11-09,211", "store-12,2026-11-09,64",
    "store-12,2026-11-09,", "STORE-12,2026-11-09,98",
]
silver = []
for line in bronze:
    store, day, count = [x.strip() for x in line.split(",")]
    if not count:                        # drop rows with missing counts
        continue
    silver.append((store.lower(), day, int(count)))
gold = {}
for store, day, count in silver:         # daily total per store
    gold[(store, day)] = gold.get((store, day), 0) + count
for k, v in sorted(gold.items()):
    print(k, v)

('brandon', '2026-11-09') 353
('store-12', '2026-11-09') 162

The script shows the spirit of each layer: bronze keeps everything, silver fixes inconsistent store names and drops a broken row, and gold totals the counts per store and day.

Shortcuts and mirroring

A lakehouse does not always need to copy data. In Fabric, a shortcut is a pointer in OneLake to data stored elsewhere, such as another lakehouse, an Azure Data Lake Storage Gen2 account or another cloud's storage, so it can be queried as if it were local. Mirroring continuously replicates a database, such as Azure SQL Database or Azure Cosmos DB, into OneLake as Delta tables, so analysts get near real-time copies without building pipelines. Keiko's loyalty data would arrive by mirroring from Hana's Cosmos DB account, and the existing Data Lake Storage account that held clickstream logs would be connected by shortcut.

Choosing between warehouse, lake and lakehouse

In practice the choice is less about technology than about people and data. Teams who mostly write SQL and want a relational experience often start with a warehouse. Teams with varied data, data science work or a lot of files usually choose a lakehouse. Many organizations use both on the same platform: in Fabric, warehouse and lakehouse items both store Delta tables in OneLake, so they can read each other's data. Harrow & Finch chose a lakehouse for bronze and silver and was still deciding whether the gold layer should be a lakehouse or a warehouse; Keiko's SQL skills pointed to the warehouse, but the SQL analytics endpoint meant she could query lakehouse tables in T-SQL anyway.

Time travel and why it matters

Keiko's favourite lakehouse feature turned out to be time travel. Because a Delta table's transaction log records every change, an engine can read the table as it was at an earlier version or time. When a store manager says that last Tuesday's report showed a different figure from today's, an analyst can query the table as it was on Tuesday and see exactly what changed. When a faulty pipeline run writes bad data, engineers can restore the table to the version before the run. Time travel also supports audits and reproducible machine learning, because a model can be retrained on exactly the data it originally saw.

History is not kept forever. Old file versions take storage, so lakehouses periodically remove files that are no longer needed by any recent version, a process Delta Lake calls vacuuming, after a retention period the team chooses. Nikolai planned seven days for most tables and longer for the gold sales tables, which finance would want to audit.

Governing the lake

Lesson 6 warned about data swamps. The Finch Data Hub's governance plan had four parts. Naming and structure: one workspace each for bronze, silver and gold, with consistent table names such as silver_sales_lines. Ownership: every table has a named owner who answers questions about it, recorded in its description. Access: engineers can write to bronze and silver, analysts can read silver and gold, and most report readers never touch the lake at all, seeing only Power BI reports built on gold. Cataloguing and protection: Microsoft Purview scans the lakehouse, records lineage from source to report, and applies sensitivity labels to tables with personal information, such as loyalty members' postal codes.

Keiko had expected governance to be a burden. Nikolai's view was the opposite: governance is what makes people trust the numbers, and a trusted figure in a report saves hours of argument in meetings.

The SQL analytics endpoint

Every Fabric lakehouse automatically gets a SQL analytics endpoint, a read-only SQL interface to its Delta tables. Analysts can connect to it with the same tools they use for SQL Server, write T-SQL queries and create views, without moving data out of the lake. This is what made the lakehouse acceptable to Keiko: she could keep writing SQL while the engineers used Spark on the same tables.

SELECT s.StoreName, SUM(f.Amount) AS Sales
FROM gold_fact_sales AS f
JOIN gold_dim_store AS s ON f.StoreKey = s.StoreKey
WHERE f.SaleDate >= '2026-11-01'
GROUP BY s.StoreName
ORDER BY Sales DESC;

Lakes, warehouses and cost

Storing a terabyte in lake storage costs far less than storing it in a dedicated database, which is one reason lakes and lakehouses spread so quickly. Compute is billed separately and only while queries or jobs run, so large volumes of rarely used history can sit cheaply until someone needs them. Nikolai estimated that the Finch Data Hub's bronze layer, with seven years of sales lines, three years of clickstream and the weather history, would be well under two terabytes in compressed Parquet, a small fraction of the space the same data took in SQL Server.