Warehouse, lake, lakehouse

A lakehouse adds a transaction log to cheap, open lake files

Data warehouseBI and SQLreports, dashboardsWarehouse engineone vendor’s SQLManaged storagecolumnar, inside the productschema on write: clean, model, then loadfast, governed SQL; costly for raw dataData lakeSpark, ML, ad hocnotebooks, scriptsAny enginereads the files directlyFiles in object storageCSV, JSON, Parquet, imagesschema on read: land anything, cheaplyno transactions; easily a “swamp”LakehouseBI, SQL, Spark and MLon the same tablesTable formatDelta, Iceberg: a transaction logOpen files in object storageParquetlake storage with warehouse guaranteesACID, versions, schema, one copyA lakehouse keeps cheap, open lake files and adds a transaction log so they behave like warehouse tables.Warehouses load modelled data into their own storage and answer SQL fast. Lakes hold any file in object storage at a fraction of the cost, but afolder of files has no transactions, no versions and no enforced schema. Table formats such as Delta Lake and Apache Iceberg add those, so BI,SQL and machine learning can share one copy of the data. Databricks, Microsoft Fabric and Snowflake all now work this way.

A data warehouse loads modelled data; a data lake stores any file; a lakehouse adds a table format to lake files.

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.

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

Half-written dataa job that fails midway leaves some new files in the folder, and readers see a partial result.
No updateschanging one row means rewriting files by hand while others may be reading them.
No historyan overwrite is final; there is no “as it was yesterday”.
Small filesstreams and frequent jobs leave millions of tiny files, and listing them becomes slower than reading them.
Swampwithout a catalogue, owners and quality checks, nobody knows which folder is trustworthy (lesson 13).
Data is still structured, semi-structured (JSON, XML, logs) or unstructured (images, audio, PDFs). Lakes store all three; warehouses mainly the first.

Storage and compute

Separate compute clusters of any size can share one copy of the data

One copy of the dataobject storageETLLargeBISmallData scienceMediumEach team its own compute, one storageOne query that takes 16 min on X-Small, if it parallelises fullyX-Small16 min · 1 credit/hSmall8 min · 2 credits/hMedium4 min · 4 credits/hLarge2 min · 8 credits/hX-Large1 min · 16 credits/hcost each time: 16/60 of a credit; only the wait shrinksWhen storage and compute are separate, any number of compute clusters of any size can work on one copy of the data.Each team gets its own warehouse or cluster, so a heavy ETL job never slows the dashboards. Clusters start in seconds, suspend when idle and billby the second (Snowflake: 60-second minimum). Each size up doubles the credits per hour; when a query parallelises, it also halves the time, sobigger costs the same and finishes sooner. Scale up for slow queries; add clusters (multi-cluster warehouses) for many users at once.

With storage and compute separated, each workload gets its own virtual warehouse, sized and suspended independently.

Delta Lake and Iceberg

The table is whatever its log of commits says it is now

orders/_delta_log/000…00000.json WRITEadd part-f0a017af…000…00001.json WRITEadd part-2ecd1b5e…000…00002.json UPDATE id = 102add part-ce6c922c…remove part-f0a017af…orders/ (Parquet data files)part-f0a017af…101 delivered102 shippedpart-2ecd1b5e…103 processingpart-ce6c922c…101 delivered102 deliveredversion 1 reads A + B(102 still shipped)version 2 reads B + CA is no longer in the table,but stays on disk for time travelA table format is a log of commits, each listing the files it added and removed; the table is whatever the log says now.A writer adds new Parquet files, then commits by writing the next numbered log entry; that one atomic step is the transaction, so readers see all ofa change or none. Files are never edited: the UPDATE rewrote file A as C. Reading an older version replays the log to that point (VERSION AS OF 1).This log is real: two appends and one update written with the open-source Delta library. Iceberg keeps the same idea in snapshots.

A table format records each commit’s added and removed files in a transaction log; old versions stay readable for 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.

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

OPTIMIZErewrites many small files into fewer large ones (Delta targets about 1 GB), so queries open fewer files.
ClusteringZ-ordering and liquid clustering co-locate rows with similar values in the same files, making min and max skipping effective.
VACUUMdeletes files no longer referenced by any version newer than the retention period, by default 7 days. After it, time travel to older versions fails.

Schema enforcement · Going deeper · 2 of 2

A Delta table refuses a mismatched write unless told to evolve

Appending a batch that has a new column, couponwrite_deltalake("orders", df) # order_id, total → version 0write_deltalake("orders", df_coupon, mode="append")SchemaMismatchError: Cannot cast schema, number of fields does not match: 3 vs 2write_deltalake("orders", df_coupon, mode="append", schema_mode="merge") # → version 1orders, version 1order_idtotalcoupon1011.2NULL1026.5NULL1033.0AUTUMNold files are not rewritten: they read as NULLorders/_delta_log/00000000000000000000.jsonmetaData schema: order_id, totaladd part-….parquet (101, 102)00000000000000000001.jsonmetaData schema: order_id, total, couponadd part-….parquet (103)The schema lives in the log,not in a catalogue: a committhat changes it writes a newmetaData action.The refused write left nocommit behind at all.A Delta table refuses a write whose columns do not match its schema, unless you ask it to evolve.Enforcement is what a folder of Parquet files lacks: there, the odd batch lands silently and breaks readers later. In Spark,evolve with .option("mergeSchema", "true") on the write or MERGE WITH SCHEMA EVOLUTION; replace the schema withoverwriteSchema. Evolution adds columns; renaming or dropping one needs column mapping (delta.columnMapping.mode = 'name').NOT NULL and CHECK constraints are enforced on every write too. Run with the open-source delta-rs library, 1.6.6.

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

Cloud serviceslogin and access control · metadata · query optimiser · transactions · result cacheCompute: virtual warehouses, sized and suspended independentlyLOAD_WHLargeBI_WHSmall, multi-clusterDS_WHMediumStorage: compressed, columnar micro-partitions, each with min and max per columnJan … FebFeb … MarMar … MayMay … JunJun … AugAug … SepSep … NovNov … DecWHERE order_date = '2025-10-14' → the metadata rules out 7 of 8 micro-partitions before any are readSnowflake splits into three layers: a shared brain, any number of compute clusters, and one store of micro-partitions.Cloud services handle security, metadata and optimising, and return a repeated query from the result cache (24 hours) with no warehouse running.Data is stored automatically as immutable micro-partitions of 50 to 500 MB uncompressed; their min and max values let queries prune. Clusteringkeys keep large tables pruned well. Micro-partition ranges here are illustrative.

Snowflake has three layers: cloud services, virtual warehouses for compute, and storage in micro-partitions.

Time Travel

Kept old versions let you read, restore or clone the past

After a row is changed or a table dropped (days, to scale)DefaultFail-safe 7 days → goneTime Travel: 1 dayEnterpriseTime Travel 90 daysFail-safe 7 days → goneSELECT * FROM orders AT(OFFSET => -3600);UNDROP TABLE orders;CREATE TABLE orders_dev CLONE orders;query the past, restore, copy without copyingSELECT * FROM orders VERSION AS OF 1;RESTORE TABLE orders TO VERSION AS OF 1;VACUUM orders; -- keeps 7 daysthe same ideas in Delta LakeBecause old micro-partitions and files are kept, a table can be read, restored or cloned as it was, for a set time.Snowflake Time Travel lasts 1 day by default and up to 90 on Enterprise Edition; you query it with AT or BEFORE and recover with UNDROP. After itcomes 7 days of Fail-safe, which only Snowflake Support can restore from. A clone shares the original’s micro-partitions and costs nothing untileither side changes. Delta tables time travel by version or timestamp until VACUUM removes the old files.

Snowflake Time Travel lasts 1 day by default, up to 90 on Enterprise; Fail-safe adds 7 days only Snowflake can restore.

Snowflake table types · Going deeper

Table type and edition decide how much of the past is kept

Table typeTime TravelFail-safeLives untilUse forPermanent0 to 1 day; 0 to 90 on Enterprise7 daysdroppeddata you cannot rebuildTransient0 or 1 daynonedroppedstaging and ELT work tablesTemporary0 or 1 daynonethe session endsscratch within one sessionExternalnonenonedropped (files stay)reading files in your own bucketCREATE TRANSIENT TABLE stg_orders (…); ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 30;EditionAddsStandardeverything on these pages, with Time Travel of 1 day at mostEnterprise90-day Time Travel, multi-cluster warehouses, materialised views, masking and row access policies, search optimisationBusiness Criticalcustomer-managed keys (Tri-Secret Secure), private connectivity, failover between accounts, for regulated dataVirtual Private Snowflakea separate Snowflake environment that shares no hardware with other accountsTransient and temporary tables skip Fail-safe: cheaper to keep, and Snowflake cannot recover them.You pay for every byte held for Time Travel and Fail-safe, so a staging table rewritten nightly is far cheaper as transient.Retention is set by DATA_RETENTION_TIME_IN_DAYS on the account, database, schema or table; 0 turns Time Travel off. Atemporary table can hide a permanent one of the same name for the length of the session.

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

OneLake: one data lake for the whole organisation (tenant), on Azure Data Lake StorageLakehousefiles + Delta tablesSpark, SQL endpointWarehouseDelta tablesT-SQL, transactionsEventhousestreaming dataKQL queriesSemantic modelDirect LakePower BI reportsstored once, in the open Delta Parquet format, readable by every engineShortcutspoint at data already in ADLS, Amazon S3, Google Cloud Storage or another workspace, without copying itWorkspacesgroup items with their permissions and capacity; Data Factory pipelines and notebooks load the dataMicrosoft Fabric puts every analytics workload on one lake, so the warehouse, Spark and Power BI read the same tables.A Fabric Lakehouse is managed with Spark and also gets a read-only SQL analytics endpoint; a Fabric Warehouse is full T-SQL with multi-tabletransactions. Both store Delta tables in OneLake, and Power BI’s Direct Lake mode reads them without import. Azure Databricks is the otherlarge-scale analytics service DP-900 names: Spark and SQL on Delta, governed by Unity Catalog.

Microsoft Fabric stores every item’s tables once in OneLake as Delta Parquet, so all its engines share them.

Unity Catalog

Every Databricks table has a three-part name and one place of control

metastoreone per regioncatalog mainschema salestable ordersmanagedtable raw_eventsexternalview daily_revenuevolume landingfilescatalog devSELECT * FROM main.sales.orderscatalog.schema.table: three-level namesDROP TABLE main.sales.ordersmanaged: Unity Catalog owns the filesfiles kept 7 days (UNDROP), then purgedDROP TABLE main.sales.raw_eventsexternal: the files live at your own pathonly the table definition is removedEvery object is governed in one place:GRANT SELECT ON TABLE main.sales.orders TO `analysts`;plus lineage, audit and discovery (lesson 13)Unity Catalog names every table as catalog.schema.table and governs it in one place, across all Databricks workspaces.Managed tables keep their files in storage Unity Catalog controls, so dropping one deletes the data (recoverable for 7 days). External tablespoint at a path you manage; dropping one leaves the files. Volumes govern non-tabular files such as CSVs, images and models.

Unity Catalog names objects as catalog.schema.table; dropping a managed table deletes its data, an external table does not.

Analytics services

Every cloud offers object storage, a table catalogue and query engines

NeedAzureAWSGoogle Cloud · multi-cloudWarehouseFabric Warehouse, SynapseRedshiftBigQuery · SnowflakeLake storageADLS Gen2, OneLakeS3Cloud StorageSQL on lake filesFabric SQL endpointAthena, Redshift SpectrumBigQuery (BigLake)Lakehouse, SparkFabric, Azure DatabricksEMR, Glue, S3 TablesDataproc · DatabricksTable catalogueOneLake catalog, PurviewGlue Data CatalogDataplex · Unity CatalogLake permissionsOneLake securityLake FormationBigLake · Unity CatalogEvery cloud offers the same building blocks: object storage, a catalogue of tables, and engines that query them.On AWS, data in S3 is described in the Glue Data Catalog, secured with Lake Formation, and queried by Athena (serverless, pay per data scanned),Redshift Spectrum or Spark; Redshift is the warehouse. S3 Tables store Apache Iceberg tables natively. On Azure, Fabric and Azure Databrickscover lakehouse and warehouse; Synapse Analytics is the older suite. Snowflake and Databricks run on all three clouds.

AWS pairs S3 with the Glue Data Catalog, Athena and Redshift; Azure offers Fabric and Azure Databricks.

Redshift keys · Going deeper

In Redshift, where a row is stored decides how fast it joins

DISTSTYLE KEYrows with the same key share a sliceslice 1c1 c5o o oslice 2c2 c6o oslice 3c3 c7o o o oslice 4c4 c8o oc: customers · o: their orders. Both are onone slice, so the join moves no rowsDISTSTYLE EVENrows dealt out in turnslice 125%any keyslice 225%any keyslice 325%any keyslice 425%any keyeven load, but a join must firstredistribute rows across the networkDISTSTYLE ALLa full copy on every nodenode 1wholetablenode 2wholetablenode 3wholetablenode 4wholetablefor small dimension tables: always local,slower to load, more storageCREATE TABLE sales (sale_id BIGINT, customer_id INT, sale_date DATE, amount DECIMAL(10,2)) DISTSTYLE KEY DISTKEY (customer_id) SORTKEY (sale_date);Slices and counts are illustrative. DISTSTYLE AUTO, the default, lets Redshift choose and change the style as the table grows.The distribution key decides which node holds a row; the sort key decides which blocks a filter skips.Pick as DISTKEY the column your largest joins use, with many evenly spread values; a skewed key overloads one slice. Rowsare stored in sort key order in 1 MB blocks with a min and max each (zone maps), so a date filter skips most blocks. Acompound sort key helps filters on its leading columns; an interleaved one weighs its columns equally. COPY loads files fromS3 in parallel and UNLOAD writes back; Redshift Spectrum queries S3 in place through the Glue Data Catalog. RA3 nodes andRedshift Serverless keep storage separate from compute.

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.

Open in a new tab