DP-600: implement denormalized/reference tables in semantic models

I will show how to implement denormalized reference (lookup/denormalized reference tables) tables in a Power BI/Fabric semantic model. This skill is important for the DP-600 exam because it demonstrates the ability to optimize models and, in practice, reduces query complexity and improves report performance.

What you need to know

A denormalized reference table consolidates information that, in operational sources, is spread across several related tables. In the context of semantic models (import mode or DirectQuery), denormalizing some entities can:

• Reduce the number of runtime joins;
• Simplify the model for end users and for DAX measures;
• Improve report performance by reducing cardinality and relationship traversals.

Simple example: instead of linking a fact_vendas table to a cliente table and an address table via keys, you can build a cliente_denormalized table that includes cliente_id, nome_cliente, cidade, país. Thus, reports that only need city and country query the denormalized table directly, without multiple joins.

How it works (step by step)

I will explain a practical flow to create and integrate a denormalized reference table into Fabric/Power BI Desktop:

1. Identify denormalization candidates:

   • Look for queries or reports with many joins or filters over scattered attributes.
   • Prioritize entities with high cardinality or joins that appear on critical report pages.

2. Choose granularity and attributes:

   Include only the fields necessary for reports and measures. Avoid volatile fields that change frequently (this increases maintenance).

3. Build the reference table in the correct layer:

   Preferably build it in the ETL/ELT process (Synapse, Data Factory, Power Query) before loading into the model. However, it can be done in Power Query if the volume is small.

4. Example in Power Query (Power BI Desktop):

   let
     Clientes = Sql.Database("servidor", "db"){[Schema="dbo",Item="Clientes"]}[Data],   Moradas = Sql.Database("servidor", "db"){[Schema="dbo",Item="Moradas"]}[Data],   Merge = Table.NestedJoin(Clientes, {"cliente_id"}, Moradas, {"cliente_id"}, "Morada", JoinKind.LeftOuter),   Expanded = Table.ExpandRecordColumn(Merge, "Morada", {"cidade","pais"}, {"cidade","pais"}),   SelectCols = Table.SelectColumns(Expanded, {"cliente_id","nome_cliente","cidade","pais"}) in   SelectCols

   This script performs a join between Clientes and Moradas and expands only the necessary columns, creating a denormalized table.

5. Load into the model and create relationships:

   Replace multiple relationships between the fact and several dimensions with a single relationship between the fact and the denormalized table. Maintain one-to-many relationships with correct cardinality and appropriate filter direction.

6. Update measures and avoid ambiguity:

   Review DAX measures that used the original tables; adjust column names if necessary. Avoid duplicate columns with the same meaning.

7. Document and manage updates:

   Record the denormalization decision and define an update process (ETL/Incremental Refresh) to keep the data consistent.

In practice — tips and best practices

Some practical guidance when implementing these tables:

• Do not denormalize everything: performance improvement comes with maintenance cost and larger dataset size.
• For DirectQuery, avoid denormalization that creates very large aggregated rows; it can harm remote execution.
• Use natural keys or surrogate keys according to your environment standard; maintain uniqueness and integrity.
• If the source is updated frequently, consider creating the denormalized table in the Data Warehouse/OneLake to centralize logic and share it with other teams.

Common mistakes

Some typical pitfalls when denormalizing:

• Denormalizing volatile fields: including attributes that change frequently (e.g., marital status) implies high maintenance overhead.
• Duplicating without managing keys: creating duplication of values without a unique key can lead to incorrect counts and sums in measures.
• Denormalizing indiscriminately to reduce cardinality: sometimes cardinality increases (e.g., joining tables with inadvertent many-to-many) and degrades performance.

How to practice

To practice this skill safely and aligned with the certification:

• Do the official Microsoft practice assessment labs (free) for DP-600 — they focus on the skills measured and are the recommended resource for hands-on training.
• Consult the official Microsoft study guide (free) for DP-600 to see the context of the skills measured and suggested exercises.
• In a development environment (Power BI Desktop, Fabric), create a small dataset with fact_vendas, clientes and moradas and implement the denormalized table using Power Query or ELT; measure the difference in refresh time and page rendering before/after.

Summary

• Denormalized reference tables reduce joins and simplify the model, improving the user experience.
• The decision to denormalize should balance performance, maintenance and data update frequency.
• Build these tables preferably in the ETL/ELT layer and document the process to ensure consistency.
• Practice with official resources: the Practice Assessment and the Microsoft study guide are free and recommended for DP-600 preparation.