Dimensional Modeling in Microsoft Fabric Warehouse

If you walked into Fabric expecting “SQL Server in the sky,” you probably already tripped over something sharp. Fabric Warehouse is not your old on-prem friend with a fresh coat of cloud paint. It is a SaaS warehouse that stores tables as Parquet with Delta Lake transaction logs in OneLake, and it layers Microsoft’s V-Order layout on top for read speed. That changes how you model and how you load, and it changes what actually matters for performance.

This is exactly where Microsoft Fabric consulting pays dividends. The fastest wins come from leaning into Fabric’s strengths, not trying to drag your favorite Synapse or SQL Server playbook across the finish line. In this guide, we will show you the dimensional modeling approaches that are still used, what has changed in Fabric Warehouse, and how to dodge the common potholes with clear, working patterns.

Why Does Dimensional Modeling in Microsoft Fabric Warehouse Feel Different?

Dimensional modeling in Fabric feels different because the ground rules have shifted. You’re still designing facts and dimensions, but the engine under the hood is not SQL Server or Synapse — it’s a cloud-native warehouse built on Delta Lake and Parquet, tuned with V-Order, and wrapped in a SaaS delivery model. That combination strips away some of the knobs you’re used to turning and introduces new performance levers you can’t ignore.

How Does Fabric’s SaaS Architecture Change Traditional Dimensional Design?

Traditional warehouses asked you to juggle indexes, partitions, resource classes, and all the knobs. Fabric Warehouse removes those knobs on purpose. Your user tables live as Parquet files with Delta Lake logs, exposed through a relational surface that accepts T-SQL for queries and DDL. The physical reality matters because your model and your loads translate into files on storage that engines must scan.

Fabric adds V-Order, a write-time layout that reorders Parquet to favor blazing reads under Microsoft’s compute engines. Power BI and the SQL engine benefit directly, and Spark also sees improvements that average around ten percent, with some scenarios up to fifty percent. Direct Lake depends on V-Order, so if you care about snappy semantic models, you care about writing V-Ordered files.

There is another twist. Some features you may expect are either limited or off the table in Warehouse. Table constraints are metadata only and not enforced. Identity columns are not supported. MERGE exists as preview syntax in Warehouse now, which is progress, but you still want a robust fallback for SCD handling.

What Are the Key Differences Between Fabric Warehouse and Azure Synapse for Dimensional Models?

When you stack Fabric Warehouse against Synapse, the marketing gloss makes them sound similar. They’re not. Fabric strips away knobs and levers you were used to in Synapse, and that changes how you design. The differences that actually matter look like this:

• Storage and layout: Fabric Warehouse data is Parquet with Delta logs and V-Order, served as relational tables. Synapse Dedicated SQL pools store data in proprietary formats and rely more on indexes and distributions. Fabric removes most physical tuning from your hands and expects you to design for scan efficiency and layout.
• Constraints: In Fabric Warehouse, PRIMARY, FOREIGN, and UNIQUE require NOT ENFORCED. They help the engine reason about models, but they do not block bad loads. Your ETL must protect referential integrity.
• MERGE: Synapse has long supported MERGE. Fabric Warehouse now exposes MERGE as preview. Treat it as a convenience where available, and keep a tested two-step UPSERT in your pocket.
• Partitioning: Synapse gives you explicit table partitioning. Fabric Warehouse does not support table partitions today, so you design for efficient reads using star schemas, smaller column payloads, and healthy file sizing.

What Star Schema Design Principles Still Apply in Microsoft Fabric?

Short answer: Almost all of them. Long answer: The star is more important than ever because Fabric’s Direct Lake semantic models in Power BI want simple, denormalized dimension tables joined to narrow fact tables on integer keys. The more snowflaking you do, the more joins and scans you incur. The platform will let you get cute. Your users will not thank you for it.

How Do You Design Fact Tables for Optimal Performance in Fabric Warehouse?

Keep fact tables laser-focused.

• Narrow and numeric: store measure columns and integer surrogate keys. Push descriptions and labels to dimensions.
• Lightweight types: prefer INT and BIGINT for keys, and fixed precision numerics for measures.
• No identity: Warehouse does not support IDENTITY. Generate surrogate keys in your ingestion layer, or assign them inside Warehouse with safe set-based patterns.
• Load in batches: fewer, larger writes are better than a hailstorm of tiny commits because they create healthier files.

Example: simple fact table skeleton

CREATE TABLE dbo.FactSales

(

    SalesKey    BIGINT   NOT NULL,   — populated by ETL, not IDENTITY

    DateKey     INT      NOT NULL,

    ProductKey  INT      NOT NULL,

    CustomerKey INT      NOT NULL,

    SalesAmount DECIMAL(18,2) NOT NULL,

    Quantity    INT      NOT NULL

);

— Optional metadata-only constraints to document intent

ALTER TABLE dbo.FactSales

  ADD CONSTRAINT FK_FactSales_Product

  FOREIGN KEY (ProductKey) REFERENCES dbo.DimProduct(ProductKey) NOT ENFORCED;

ALTER TABLE dbo.FactSales

  ADD CONSTRAINT FK_FactSales_Customer

  FOREIGN KEY (CustomerKey) REFERENCES dbo.DimCustomer(CustomerKey) NOT ENFORCED;

Those foreign keys will not be enforced. They still signal design intent and can help keep your semantic model consistent. Your ETL must validate keys before the load.

What’s the Best Approach for Dimension Table Design With Delta Lake Storage?

Dimensions do the heavy descriptive lifting. Keep them stable, expressive, and easy to filter.

• Business key plus surrogate: carry the natural business key for traceability, and join on a numeric surrogate for performance.
• Attribute curation: descriptive text and groupings live here, not in facts.
• SCD discipline: decide explicitly which attributes require history. Only do Type 2 where the analysis truly needs it.
• No identity: again, assign surrogate keys outside of IDENTITY. Use a safe allocator or a deterministic mapping in your pipeline.

Example: dimension skeleton

CREATE TABLE dbo.DimProduct

(

    ProductKey  INT        NOT NULL,   — assigned by your ETL

    ProductCode NVARCHAR(50)   NOT NULL,   — business key

    ProductName NVARCHAR(200)  NULL,

    Category    NVARCHAR(100)  NULL,

    SubCategory NVARCHAR(100)  NULL,

    EffectiveDate   DATE       NULL,   — SCD2 bookkeeping

    ExpirationDate  DATE       NULL

);

ALTER TABLE dbo.DimProduct

  ADD CONSTRAINT PK_DimProduct PRIMARY KEY NONCLUSTERED (ProductKey) NOT ENFORCED;

How Do You Handle Fabric’s Current Limitations in Dimensional Modeling?

Fabric isn’t shy about its rough edges. Warehouses don’t enforce keys, MERGE is still finding its footing, and features like partitioning are off the table for now. None of that means you can’t build solid dimensional models — it just means you need to know where the potholes are and how to steer around them. Let’s start with one of the biggest headaches: handling slowly changing dimensions without MERGE.

What Workarounds Exist for Slowly Changing Dimensions Without MERGE Support?

Slowly changing dimensions are where Fabric’s limits really show. With no fully supported MERGE (yet) and no enforced constraints, you’ve got to pick your strategy carefully. There are really two practical ways to handle it:

A. MERGE, when available
Fabric Warehouse exposes MERGE as a preview feature. Where it is enabled and you accept the preview risk, you can express your SCD Type 2 logic concisely. Validate behavior in non-prod and watch release notes.

B. Portable two-step UPSERT
This works everywhere, today. It is explicit, debuggable, and plays nicely with Warehouse.

— Assume stage.DimCustomer_Staging contains latest source snapshot

— 1) Close out current rows that are changing

UPDATE tgt

SET ExpirationDate = @asOfDate

FROM   dbo.DimCustomer AS tgt

JOIN   stage.DimCustomer_Staging AS src

    ON src.CustomerCode = tgt.CustomerCode   — business key

WHERE  tgt.ExpirationDate IS NULL

  AND (   ISNULL(src.CustomerName,”) <> ISNULL(tgt.CustomerName,”)

    OR ISNULL(src.Segment,”)  <> ISNULL(tgt.Segment,”) );

— 2) Insert new current versions

INSERT INTO dbo.DimCustomer

(

    CustomerKey, CustomerCode, CustomerName, Segment,

    EffectiveDate, ExpirationDate

)

SELECT

    NextKey.CustomerKey,                     — provided by your allocator

    src.CustomerCode, src.CustomerName, src.Segment,

    @asOfDate, NULL

FROM stage.DimCustomer_Staging AS src

JOIN dbo.CustomerKeyAllocation AS NextKey

   ON NextKey.CustomerCode = src.CustomerCode

LEFT JOIN dbo.DimCustomer AS tgt

   ON tgt.CustomerCode = src.CustomerCode

     AND tgt.ExpirationDate IS NULL

WHERE tgt.CustomerCode IS NULL;

How Do You Implement Proper ETL Patterns When Foreign Key Constraints Aren’t Enforced?

If the engine will not protect you, your pipeline must.

• Dimension-first loads: land and validate dimension tables before any facts.
• Key validation step: join staged facts to dimensions on business keys to fetch surrogate keys, and count rejects.
• Late arriving dimensions: reserve an “unknown” surrogate for each dimension and backfill later.
• Document with metadata constraints: declare FOREIGN KEY … NOT ENFORCED on facts to reflect intent.

What’s the Optimal Integration Strategy Between Fabric Warehouse and Power BI?

Connecting Fabric Warehouse to Power BI isn’t just about pointing a semantic model at some tables. The way you design the warehouse dictates how fast, flexible, and stable your reports will be. Fabric’s tight integration with Power BI is its biggest selling point, but only if you design with that end in mind. One of the biggest decisions you’ll face is how to take advantage of Direct Lake.

How Do Direct Lake Semantic Models Change Your Dimensional Design Decisions?

Direct Lake lets Power BI semantic models read data straight from OneLake, without import cycles. That is an enormous speed lever, but only when the underlying tables are model-friendly. Expect the best results when:

• You deliver clean star schemas with narrow facts and curated dimensions.
• Your keys are integers, not long strings or GUIDs, for better compression and join efficiency in the model.
• Your file layout is healthy, so Verti-Scan reads can stay fast. Direct Lake relies on V-Order to achieve near in-memory access patterns.

What Performance Considerations Matter Most for Star Schemas in Fabric?

Since you are not hand-tuning indexes and partitions in Warehouse, the biggest wins are simple.

• Right-size files: avoid a sea of tiny Parquet files. Aim for hundreds of MB per file.
• Use V-Order wisely: it is a huge read accelerator and is a dependency for Direct Lake.
• Minimize snowflaking: Power BI guidance is consistent. Star schemas are easier to understand and faster to query.
• Know today’s limits: table partitioning is not supported in Warehouse right now.

How Do You Implement a Practical Dimensional Model in Microsoft Fabric Warehouse?

Building a dimensional model in Fabric isn’t about reinventing the wheel — it’s about adapting proven Kimball patterns to a SaaS warehouse that plays by different rules. The fundamentals are familiar, but the execution shifts because you don’t have identity columns, enforced constraints, or manual partitioning. That means your implementation needs to be deliberate, from how you assign surrogate keys to how you size files for performance.

What’s the Step-By-Step Process for Building Your First Fabric Dimensional Model?

Here is the field-tested path we follow on projects.

1. Model the business first.
2. Create Warehouse tables.
3. Stand up a key allocation approach.
4. Ingest dimensions first.
5. Stage facts.
6. Check file health.
7. Build the semantic model in Direct Lake.
8. Iterate in small slices.

Example: surrogate key assignment without IDENTITY

— Dimension with explicit surrogate, no IDENTITY

CREATE TABLE dbo.DimDate

(

    DateKey   INT     NOT NULL,  — assigned by pipeline

    Date      DATE    NOT NULL,

    CalendarYear  INT     NULL,

    MonthName NVARCHAR(20) NULL,

    CONSTRAINT PK_DimDate PRIMARY KEY NONCLUSTERED (DateKey) NOT ENFORCED

);

— Typical load: pipeline computes DateKey and inserts rows

INSERT INTO dbo.DimDate (DateKey, Date, CalendarYear, MonthName)

SELECT DateKey, Date, CalendarYear, MonthName

FROM stage.DimDate_Load;

How Do You Plan Your Migration Strategy From Existing Data Warehouses to Fabric?

Migrating to Fabric isn’t a lift and shift — you can’t just lift your Synapse or SQL Server scripts and expect them to run unchanged. Fabric plays by different rules, so the smartest path is an incremental one that balances quick wins with long-term stability. Here are the key principles that make a migration stick without derailing your business in the process:

• Triage by value
• Rewrite the brittle bits
• Respect the storage reality
• Embrace the star
• Run side by side

Fabric Guidance With P3 Adaptive

Fabric has some technical constraints, limitations, and quirks that shape how you should design. If you ignore them, you burn time and money. If you design with them in mind, your dimensional models get simpler, your Power BI semantic models in Direct Lake get faster, and your team spends more time shipping and less time babysitting indexes that don’t even exist here.

That is the difference between reading the documentation and getting results. This is why Microsoft Fabric consulting matters for mid-market teams that need enterprise outcomes without enterprise drama. You want a star schema that is clean, keys that are stable, loads that are explicit, and files that are healthy.

Ready to cut through Fabric’s quirks and build dimensional models that perform? P3 Adaptive helps teams design, optimize, and deliver Fabric warehouses that do not just look good on paper; they work in the real world. Get started with P3 Adaptive today!