DP-700: how to design and implement dimensional schemas for analytics

In this guide I will teach the skill of designing and implementing dimensional schemas (star and snowflake) for analytics solutions in the context of DP-700. This skill matters because dimensional models improve query performance, ease reporting in Power BI and are often required in Data Warehouse and Lakehouse architectures.

What you need to know

A dimensional schema organizes data into fact tables and dimension tables. The fact table contains numeric measures (e.g.: sales_amount, quantity) and keys to dimensions; the dimension tables contain descriptive attributes (e.g.: product_name, customer_segment). The most common format is the star schema (central fact table linked to multiple dimensions), and the snowflake variant normalizes dimensions into multiple tables.

Simple example:

FactSales
- SalesID (PK)
- DateKey (FK -> DimDate.DateKey)
- ProductKey (FK -> DimProduct.ProductKey)
- CustomerKey (FK -> DimCustomer.CustomerKey)
- SalesAmount

DimDate
- DateKey (PK)
- Date
- Year
- Month

DimProduct
- ProductKey (PK)
- ProductName
- Category

DimCustomer
- CustomerKey (PK)
- CustomerName
- Region

In this model, typical analytical queries (e.g.: total sales by month and by category) join the fact table to the dimensions by numeric keys, which is efficient.

How it works

Practical steps to design and implement a dimensional schema in a Fabric solution (OneLake, Delta tables, Power BI):

1. Identify business processes and metrics — start by listing the main indicators (KPIs) the reports will need: revenue, units_sold, margin, etc.
2. Define granularities — the fact table should reflect the lowest granularity required (e.g.: per transaction, per order line, per day). Incorrect granularity forces complex aggregations at consumption time.
3. Model the dimensions — create dimensions for entities that describe the facts: customer, product, time, location. Avoid duplicating attributes that clearly belong to a single dimension.
4. Choose surrogate keys — prefer integer surrogate keys (ProductKey, CustomerKey) instead of natural keys (SKU, NIF) to maintain integrity and performance.
5. Implement in Fabric — create Delta tables in OneLake (or Lakehouse tables):

   -- Exemplo simplificado T-SQL / SQL para criar tabelas Delta no Notebook SQL
   CREATE TABLE lakehouse.dbo.DimDate (
     DateKey INT PRIMARY KEY,
     Date DATE,
     Year INT,
     Month INT
   ) WITH ( LOCATION = '.../DimDate' );
   
   CREATE TABLE lakehouse.dbo.FactSales (
     SalesID BIGINT,
     DateKey INT,
     ProductKey INT,
     CustomerKey INT,
     SalesAmount DECIMAL(18,2)
   ) USING DELTA;
   

6. Ingestion and maintenance — use pipelines (Dataflows, Synapse Pipelines or Fabric pipelines) to load facts and dimensions. For slowly changing dimensions (SCD) implement the appropriate strategy (Type 1 or Type 2). For example, for SCD Type 2 add effective_date and end_date columns and generate a new row when the attribute changes.
7. Physical optimization — indexing in MPP systems is not direct; in Fabric optimize with proper sort order and partitions (e.g.: partition by DateKey). For queries by category, consider clustering by Category or ProductKey.

In practice — a step-by-step use case

Objective: create a simple star schema for sales reports by month and category.

1. Identify fields: SalesDate, ProductSKU, CustomerID, Quantity, Amount, Category.
2. Create DimDate with DateKey = YYYYMMDD; populate the dimension with a daily pipeline.
3. Create DimProduct with ProductKey (surrogate), ProductSKU, ProductName, Category. Resolve duplicates in the ETL process and generate ProductKey with a sequence.
4. Create FactSales with SalesID, DateKey, ProductKey, CustomerKey, Quantity, Amount. In the ingestion pipeline convert ProductSKU -> ProductKey using a lookup on DimProduct.
5. Partition FactSales by YearMonth (e.g.: 202601) for period queries, and sort by ProductKey for product analysis.
6. Test typical queries (GROUP BY Month, Category) with sample data and measure times; adjust partitioning/sorting as needed.

Common mistakes

• Incorrect granularity: defining the fact table with granularity that is too aggregated or too fine that does not match analytical requirements; both cause performance issues or complications in aggregations.
• Using natural keys as PKs: textual or composite keys (e.g.: SKU) degrade performance and complicate changes; prefer surrogate integer keys.
• Dimensions without history when needed: treating historical changes as Type 1 when history is required (Type 2) leads to incorrect reports and loss of traceability.

How to practice

Practice by implementing a star schema in a Fabric environment or in a sandbox: create DimDate, DimProduct, FactSales; implement simple pipelines to populate tables and build reports in Power BI on that model. For exam preparation, use the OFFICIAL and free Microsoft Practice Assessment and the official Study Guide (both free) — these resources help validate knowledge in that domain without resorting to dumps. Look on the DP-700 exam page for links to the practice assessment and the study guide.

Summary

• Model facts and dimensions based on KPIs and the required granularity.
• Use surrogate keys, partitions and sorting to optimize analytical queries.
• Implement appropriate SCDs to preserve history when needed.
• Test real queries and adjust physical parameters (partitions/clustering) in Fabric before publishing for consumption.