Lakehouse, Delta Lake and Medallion Architecture

A lakehouse combines the flexible storage of a data lake with structured querying and relational features associated with a data warehouse. In Fabric, it supports both Spark and a SQL analytics endpoint.

Lakehouse Areas

• Files: raw or semi-structured files such as CSV, JSON, images and Parquet
• Tables: Delta tables with metadata, ACID transactions and schema support
• SQL analytics endpoint: read-only T-SQL access to lakehouse Delta tables
• Spark notebooks: engineering, advanced transformation and machine-learning workloads
• Semantic model: relationships, measures and business-ready reporting definitions

Common Confusion

• The SQL analytics endpoint is read-only for lakehouse tables
• A Fabric Warehouse supports read-and-write transactional T-SQL

A Delta table is a schema abstraction over Parquet data files plus a transaction log. The _delta_log folder records changes and enables reliable table operations.

Why Delta Matters

• ACID transactions protect consistency during concurrent operations
• Schema enforcement prevents incompatible structures from being written
• CRUD operations support insert, update and delete semantics
• Time travel allows access to previous table versions
• Batch and streaming workloads can use the same table
• Open storage keeps Parquet-based data accessible to multiple engines

Optimising Delta Tables

• Optimize Write reduces small files during writes and is generally enabled by default
• OPTIMIZE compacts existing small files after large loads or fragmentation
• V-Order improves read efficiency for frequently queried reporting tables
• VACUUM removes obsolete unreferenced files, but can prevent time travel to older versions
• Partitioning enables data skipping for very large tables with logical filter columns

The medallion pattern organises data into progressive layers of quality.

Bronze, Silver, Gold

• Bronze: raw source-aligned landing data with minimal modification
• Silver: validated, cleaned, deduplicated and integrated data
• Gold: business-ready, enriched or aggregated data for reporting and analytics

Choosing the Right Tool per Layer

• Use Dataflows Gen2 for simpler low-code transformations
• Use notebooks for large-scale or complex transformations
• Use pipelines to orchestrate movement between layers

Design Principle

• The medallion pattern does not replace dimensional modelling; the Gold layer can still contain fact and dimension structures