Flattening related tables into one wide, denormalized shape

Denormalizing data means combining data from multiple normalized tables into fewer, wider tables optimized for analytical read performance, typically by pre-joining dimension and fact data. In Microsoft Fabric, this is commonly done using Dataflows Gen2, notebooks (PySpark/Spark SQL), or pipeline Copy/Data Flow activities when preparing data for the silver or gold layer in a lakehouse or warehouse.

1 · Learn the must-know

• 1Denormalization trades storage space and redundancy for query performance by reducing the number of joins needed at query time, which is especially valuable for Power BI direct query and analytical workloads.
• 2In the medallion architecture, denormalization typically happens when moving data from the silver (cleansed, normalized) layer to the gold (business-ready, aggregated/denormalized) layer.
• 3Common techniques in Fabric include using Spark notebooks with DataFrame joins, Dataflows Gen2 Merge queries, or T-SQL JOINs in a Fabric Warehouse to flatten star-schema fact/dimension tables into a single wide table.
• 4A key gotcha is that denormalized tables increase storage and can introduce data update anomalies, so they are best suited for read-heavy reporting layers rather than transactional/OLTP-style processing.
• 5When denormalizing in Spark notebooks, broadcast joins can be used to efficiently join a large fact table with smaller dimension tables and avoid costly shuffles.
• 6Denormalized outputs are often materialized as Delta tables in the lakehouse (or tables in the warehouse) so downstream consumers like Power BI can query them directly without needing to perform joins.

2 · Check your understanding