Warehouse, lake, lakehouse
A lakehouse adds a transaction log to cheap, open lake files
A data warehouse loads modelled data; a data lake stores any file; a lakehouse adds a table format to lake files.
Analytical data lives in one of three kinds of place. A data warehouse: data is cleaned and modelled, then loaded into the product's own storage, and SQL on it is fast and trustworthy. A data lake: any file, any format, dropped into cheap object storage, read later by whatever engine you like. Cheap and flexible, but a folder of files has no transactions and no history. A lakehouse keeps the open files in object storage and adds a table format, Delta Lake or Iceberg, with a transaction log. Now the files behave like warehouse tables, and BI, SQL and machine learning share one copy.
Warehouse, lake, lakehouse · Going deeper
Schema on write, schema on read, and why lakes turn into swamps
Schema on write checks data as it loads; schema on read applies a structure only when a query reads it.
Schema on write checks data as it loads; schema on read applies a structure only when a query reads it. Two ways to apply a schema. Schema on write (warehouses) data must match the table’s columns and types before it is accepted. Bad rows fail at load time, and every reader can trust the result. The cost is work up front, and raw detail you did not model is gone. Schema on read (lakes) files land as they come; each query says how to interpret them. Fast to ingest and nothing is thrown away, but every reader repeats the interpretation, and a renamed field silently breaks reports. What goes wrong in a plain data lake. Data is still structured, semi-structured (JSON, XML, logs) or unstructured (images, audio, PDFs). Lakes store all three; warehouses mainly the first.
Two ways to apply a schema
- Schema on write (warehouses) data must match the table’s columns and types before it is accepted. Bad rows fail at load time, and every reader can trust the result. The cost is work up front, and raw detail you did not model is gone.
- Schema on read (lakes) files land as they come; each query says how to interpret them. Fast to ingest and nothing is thrown away, but every reader repeats the interpretation, and a renamed field silently breaks reports.
What goes wrong in a plain data lake
Storage and compute
Separate compute clusters of any size can share one copy of the data
With storage and compute separated, each workload gets its own virtual warehouse, sized and suspended independently.
Modern platforms keep the data in one place and the computers that query it in another. The ETL job gets a large cluster, the dashboards a small one, the data scientists a medium one. They never compete, and each stops when idle. Size matters in a useful way. On Snowflake each size up doubles the credits per hour. If the query parallelises, it also halves the time: 16 minutes, then 8, 4, 2, 1. The bill is the same. Only the wait changes.
Delta Lake and Iceberg
The table is whatever its log of commits says it is now
A table format records each commit’s added and removed files in a transaction log; old versions stay readable for time travel.
Here is a real Delta Lake table after three operations. Commit zero writes orders 101 and 102 into file A. Commit one appends 103 in file B. Commit two updates order 102 to delivered. Parquet files are never edited, so the writer copies file A with the change into a new file C, and the commit says: add C, remove A. Writing that one numbered log entry is the atomic step, so readers see the whole change or none of it. Version two of the table is B and C. File A is still on disk, so version one can still be read: time travel.
Delta Lake and Iceberg · Going deeper · 1 of 2
Iceberg, small files and keeping tables fast
Apache Iceberg tracks files through snapshots and manifests; OPTIMIZE compacts small files and VACUUM deletes old ones.
Apache Iceberg tracks files through snapshots and manifests; OPTIMIZE compacts small files and VACUUM deletes old ones. Delta Lake and Apache Iceberg. Delta Lake a _delta_log folder of numbered JSON commits, with a Parquet checkpoint every so often so readers need not replay thousands of entries. Default format on Databricks and in Microsoft Fabric. Apache Iceberg a metadata file points to the current snapshot; a snapshot’s manifest list points to manifests; manifests list data files with their column statistics. A catalogue holds the pointer to the current metadata file and swaps it atomically. Supported by Snowflake, AWS (Athena, Glue, S3 Tables), BigQuery and others. Both ACID commits, time travel, schema evolution (add or rename columns safely), and file-level statistics for data skipping. Delta UniForm and similar features let one table be read as either. Housekeeping.
Delta Lake and Apache Iceberg
- Delta Lake a _delta_log folder of numbered JSON commits, with a Parquet checkpoint every so often so readers need not replay thousands of entries. Default format on Databricks and in Microsoft Fabric.
- Apache Iceberg a metadata file points to the current snapshot; a snapshot’s manifest list points to manifests; manifests list data files with their column statistics. A catalogue holds the pointer to the current metadata file and swaps it atomically. Supported by Snowflake, AWS (Athena, Glue, S3 Tables), BigQuery and others.
- Both ACID commits, time travel, schema evolution (add or rename columns safely), and file-level statistics for data skipping. Delta UniForm and similar features let one table be read as either.
Housekeeping
Schema enforcement · Going deeper · 2 of 2
A Delta table refuses a mismatched write unless told to evolve
Schema enforcement rejects a batch with an unexpected column; schema evolution (mergeSchema) adds the column in a new commit.
Snowflake architecture
Snowflake splits into shared services, many warehouses, one storage
Snowflake has three layers: cloud services, virtual warehouses for compute, and storage in micro-partitions.
Snowflake is built in three layers. Cloud services, the brain: logins, permissions, metadata, the optimiser, and a result cache that answers a repeated query with no compute running at all. Compute: virtual warehouses, one per workload, each sized, started and suspended on its own. Storage: every table is cut automatically into micro-partitions, columnar and compressed, and the metadata records the minimum and maximum of each column in each one. A query for one October day skips every micro-partition whose range cannot contain it.
Time Travel
Kept old versions let you read, restore or clone the past
Snowflake Time Travel lasts 1 day by default, up to 90 on Enterprise; Fail-safe adds 7 days only Snowflake can restore.
Because data files are never overwritten, the past is still there for a while. On Snowflake, by default, you can query or restore a table as it was up to a day ago. After that, seven days of Fail-safe, which only Snowflake Support can recover from. On Enterprise Edition, Time Travel can be set as long as ninety days. Query the past with AT, bring back a dropped table with UNDROP, and clone a table instantly: the clone shares the same micro-partitions until either side changes. Delta Lake does the same by version number, until VACUUM removes old files.
Snowflake table types · Going deeper
Table type and edition decide how much of the past is kept
A transient or temporary table has no Fail-safe and at most 1 day of Time Travel; 90 days needs Enterprise Edition.
Microsoft Fabric
Fabric puts warehouse, Spark and Power BI on one lake, OneLake
Microsoft Fabric stores every item’s tables once in OneLake as Delta Parquet, so all its engines share them.
Microsoft Fabric is Microsoft's lakehouse platform. Each team works in workspaces holding items: a lakehouse for Spark and files, a warehouse for T-SQL, an eventhouse for streaming data, and Power BI semantic models. Underneath, every one of them stores its tables once, in one lake for the whole organisation, OneLake, in the open Delta Parquet format. Shortcuts point at data already sitting in Azure, Amazon S3 or Google storage, so it can be used without copying.
Unity Catalog
Every Databricks table has a three-part name and one place of control
Unity Catalog names objects as catalog.schema.table; dropping a managed table deletes its data, an external table does not.
On Databricks, Unity Catalog is where every table, view and file area is registered. A metastore per region holds catalogs, catalogs hold schemas, schemas hold tables, views, volumes and functions. So every table has a three-part name. A managed table keeps its files in storage Unity Catalog controls. Drop it and the data goes too, recoverable for seven days. An external table points at a path you manage. Drop it and only the definition goes; the files stay. Permissions are granted on these names, in one place, for every workspace.
Analytics services
Every cloud offers object storage, a table catalogue and query engines
AWS pairs S3 with the Glue Data Catalog, Athena and Redshift; Azure offers Fabric and Azure Databricks.
The same building blocks appear everywhere. Storage: S3, Azure Data Lake Storage and OneLake, Google Cloud Storage. A catalogue that says which files make up which table: the Glue Data Catalog, Unity Catalog, Purview. Engines: Athena runs SQL straight on S3 and charges by the data scanned, Redshift is AWS's warehouse, BigQuery is Google's, Fabric and Databricks cover lakehouse and warehouse on Azure. Snowflake and Databricks run on all three clouds.
Redshift keys · Going deeper
In Redshift, where a row is stored decides how fast it joins
A distribution key puts rows that join on the same slice; a sort key orders them so zone maps skip blocks.
Lesson complete
You finished “Warehouses, lakes and lakehouses”
Nicely done.