Design a dimensional model in a fixed sequence: define the business process and the grain of one fact row; identify facts that are valid at that grain; create conformed dimensions; replace unstable source identifiers with durable surrogate keys; choose the required history behavior for each attribute; and publish the resulting star schema as a governed analytical serving layer. In a Microsoft Fabric lakehouse or Warehouse, that order matters more than the choice of authoring engine. It prevents joins, aggregations, and history handling from silently changing what a row means.

Use denormalization deliberately to make common analytical paths simple, not to flatten every source table indiscriminately. Implement the physical facts and dimensions with the write tools appropriate to the selected Fabric item, then expose them as the gold layer for SQL and Power BI consumption. This article stops at that physical serving boundary: relationship cardinality, filter direction, bridge tables, and other Power BI semantic-model decisions belong in DP-600: Designing Power BI Semantic Models and Relationships in Microsoft Fabric.

Architecture Overview

A star schema organizes measurable business events in fact tables and descriptive business context in dimension tables. The fact table forms the center of the model. Its foreign keys identify the applicable dimension rows, while its numeric facts capture values such as quantity, amount, duration, or balance. Dimensions provide the attributes by which consumers group, filter, and label those facts.

This design is different from reproducing an operational application's normalized schema. Operational schemas are commonly optimized to validate transactions and avoid update anomalies. An analytical schema is optimized to answer repeated business questions at declared grains. The Fabric dimensional-modeling guidance therefore recommends fact and dimension tables for Fabric Warehouse workloads, while the Power BI star-schema guidance explains why clearly separated fact and dimension roles support usable analytical models.

In a medallion design, raw source-shaped data usually enters a bronze layer, validated and conformed data becomes silver, and consumption-ready facts and dimensions form the gold layer. These are architectural responsibilities rather than mandatory item names. The gold layer should be the point at which grain, keys, history, and business meaning are stable enough for reusable SQL analysis and downstream semantic models.

Design the Model from Business Process to Grain

The first deliverable is not a table diagram. It is a precise grain statement for each business process.

Identify one business process

Start with an activity that the organization measures, such as an order line, shipment, support interaction, or daily inventory position. Do not start by merging every available source table. A source system can contain several processes at different grains, and placing them in one fact table makes measures ambiguous.

For each process, identify:

  • The event or state being measured.
  • When a fact row is created or updated.
  • The dimensions that describe the event.
  • The measures that are valid for that event.
  • Whether consumers need transaction detail, a periodic snapshot, or an accumulating view of milestones.

Separate fact tables are appropriate when processes have different grains. Orders and shipments, for example, should not be forced into one row merely because they share customer and product dimensions. They can use conformed dimensions and still remain distinct measurable processes.

Declare the grain in plain language

A useful grain statement names exactly what one row represents. Examples include “one row per order line,” “one row per product and warehouse at the end of each day,” or “one row per support case with milestone dates.” The statement must be true before any measures are assigned.

Then test candidate columns against it:

  1. Does each combination of the grain-defining identifiers produce at most one fact row?
  2. Is every measure meaningful at that same level of detail?
  3. Does joining a dimension preserve the number of fact rows?
  4. Can a reload reproduce the same grain without creating duplicates?

If an order header amount is copied to every order line, summing it will overstate the result. If a daily balance is mixed with individual movements, time aggregation becomes misleading. Resolve such conflicts by changing the measure, allocating it according to an explicit business rule, or placing it in a different fact table.

Classify facts by aggregation behavior

Measures need an aggregation contract as well as a data type. Additive facts can be summed across all relevant dimensions. Semi-additive facts can be summed across some dimensions but not across others; an account balance, for example, should not normally be summed across snapshot dates. Non-additive facts such as ratios should generally be calculated from their additive components at query time rather than stored and summed.

Retain the components that allow correct recomputation. Storing revenue and cost supports a margin calculation across different grouping levels; storing only a row-level margin percentage does not. This physical decision makes later analytical behavior predictable without defining semantic-model measures here.

Build Dimensions with Durable Keys and Useful Attributes

Dimensions translate source codes into stable analytical entities. A customer dimension can contain a customer name, segment, geography, and other descriptive attributes; a date dimension can contain calendar and fiscal attributes; a product dimension can contain category and brand. Each dimension row needs an identifier that remains usable even when source systems change.

Separate business keys from surrogate keys

A business key is the identifier supplied by a source, such as a customer number. Preserve it for traceability, matching, and reconciliation, but do not assume that it is globally unique or permanently stable. Different systems can reuse the same value, and a source can reassign or change an identifier.

A surrogate key is a warehouse-controlled identifier for one dimension row. It decouples fact foreign keys from mutable source identifiers and allows multiple historical versions of the same business entity. A practical dimension commonly stores both:

  • A surrogate key used by facts.
  • The source business key, often paired with a source-system identifier.
  • Descriptive attributes used for analysis.
  • History-management columns when historical versions are required.

Generate and resolve surrogate keys in the controlled loading process for the lakehouse or Warehouse. Do not derive them from a row's arrival order if retries or parallel loads can change that order. Whatever generation method the implementation uses, it must produce a unique, repeatable lookup target and prevent two current rows from accidentally representing the same business key.

Handle missing and late-arriving members explicitly

A fact can arrive before its dimension record. Dropping the fact loses a valid event, while leaving an unresolved null key makes reconciliation and analysis inconsistent. One common design is a durable “unknown” dimension member whose surrogate key can be assigned temporarily. The load process records the unresolved business key, creates or updates the real dimension member when authoritative data arrives, and then applies the documented correction strategy.

Different unknown conditions may need different members—for example, not provided, not applicable, and lookup failed—when that distinction has business value. Do not create extra categories merely for convenience; define them as governed data-quality semantics.

Denormalize for analytical navigation

Dimensions often denormalize related descriptive attributes into one table. Bringing product category and subcategory attributes into the product dimension removes chains of joins and presents a clearer analytical path. This is useful when the attributes share the same business entity and history rules.

Denormalization is not a license to combine unrelated entities or repeat high-volume transactional data. Keep separate dimensions when they have independent keys, ownership, reuse, or history. Preserve a source-aligned silver layer so that the curated gold dimension can be rebuilt when its shape or rules change.

Choose Slowly Changing Dimension Behavior

A slowly changing dimension (SCD) policy determines what happens when a descriptive attribute changes. Choose the policy per attribute according to the business question, not simply per table.

Use current-state overwrite when history has no analytical value

Type 1 behavior updates the existing dimension row. It is appropriate for corrections or attributes for which reports should always use the latest known value. Correcting a misspelled customer name is a typical example. Historical facts then appear under the corrected current value because they continue to reference the same dimension row.

The tradeoff is deliberate loss of the previous value. A Type 1 update should therefore be idempotent, auditable through the ingestion process where required, and restricted to attributes whose past state is not needed for analysis.

Create a new row when analysis must preserve history

Type 2 behavior closes the current dimension version and inserts a new row with a new surrogate key. The dimension normally carries the business key plus effective-from and effective-to values and a current-row indicator, or an equivalent bounded validity design. Facts resolve to the dimension version valid for the event according to the documented business time rule.

This preserves statements such as “revenue by the customer segment in effect when the sale occurred.” It also introduces responsibilities: validity ranges must not overlap for the same business key, exactly one appropriate current version must exist, and late data must be matched using the relevant effective time rather than blindly using today's row.

Not every changing attribute deserves Type 2 history. Retaining versions increases row counts and load complexity. Apply it where historical classification changes the interpretation of facts; use Type 1 for corrections and current-only attributes. If another pattern is required, document its business meaning and query implications rather than using a type label without an implementation contract.

Implement the Physical Star Schema in Fabric

The dimensional design is portable, but its write and serving paths depend on whether the gold model resides in a lakehouse or a Warehouse. Microsoft documents both as analytical stores in Fabric, with different primary development experiences.

Implement facts and dimensions in a lakehouse

A Fabric lakehouse stores managed tables in Delta Lake format and supports engineering with Spark while providing a SQL analytics endpoint for relational querying. Build the dimensional tables through a supported write path such as notebooks, Dataflow Gen2, or orchestrated data pipelines. Use the transformation process to deduplicate source records, generate or look up surrogate keys, apply SCD rules, and validate grain before publishing the gold tables.

The SQL analytics endpoint is a read-oriented surface over lakehouse tables, not the place to assume Warehouse-style write behavior. This distinction is important: use the engineering or integration layer to change lakehouse data, then use the endpoint for supported SQL analysis and consumption. The lakehouse overview describes the combined Spark and SQL access model.

Lakehouse implementation is a strong fit when the same OneLake data needs code-first preparation, Delta-based engineering, and analytical serving. Keep intermediate silver tables separate from the published dimensional contract so a partially completed history update or failed key lookup cannot become visible as a trusted gold result.

Implement facts and dimensions in a Warehouse

A Fabric Warehouse provides a SQL-first relational experience over Fabric-managed storage. Use supported Transact-SQL (T-SQL), Dataflow Gen2, pipelines, or other documented ingestion and transformation paths to create and load the fact and dimension tables. Views can present stable relational projections, but the persisted dimensional tables should own the grain, keys, and history required for predictable serving.

The Fabric Data Warehouse documentation identifies Warehouse as a relational analytical store and the dimensional-modeling guidance provides specific design practices for its fact and dimension workloads. Warehouse is a natural choice when the team and consuming workloads are SQL-centered and relational transformations own the gold model.

Do not let implementation convenience change the logical design. A fact table still requires one declared grain, and a Type 2 dimension still requires non-overlapping versions, whether the load is written in T-SQL, coordinated by a pipeline, or prepared by another supported tool.

Publish a stable gold-layer serving contract

The Fabric medallion architecture guidance places curated dimensional models in the gold layer for reporting and analysis. A production gold contract should specify:

  • The grain and business meaning of every fact table.
  • The surrogate and business keys of every dimension.
  • The aggregation behavior of each stored measure.
  • The SCD policy and effective-time rule for changing attributes.
  • Refresh or load completion boundaries.
  • Data-quality checks and reconciliation expectations.
  • Which tables and columns are supported for downstream consumption.

Publish only after the complete load unit passes its checks. Consumers should not see a new fact partition before the corresponding dimension keys are resolvable, nor a new dimension version before its validity range is internally consistent. The exact transaction and orchestration mechanism depends on the selected supported tools, but the observable result should be a coherent serving state.

Once that physical contract is stable, SQL consumers can query it and Power BI can build a semantic model over it. The semantic layer may add business measures, hierarchies, and relationship behavior, but it should not have to repair an ambiguous physical grain or inconsistent dimension history.

Validate the Model Before Serving It

Validation should prove the model's meaning, not only that a job completed. For each load, test the following:

  1. Fact uniqueness matches the declared grain.
  2. Every fact foreign key resolves to an allowed dimension member, including governed unknown members.
  3. Joins to dimensions do not multiply fact rows unexpectedly.
  4. Measures reconcile to a trusted source at equivalent scope and grain.
  5. Type 2 validity ranges do not overlap and current-row rules hold.
  6. Reloading the same input does not create duplicate facts or dimension versions.
  7. Gold tables become visible together at the intended completion boundary.

Also test representative analytical questions. Group a measure by current and historical dimension attributes, inspect late-arriving records, and compare detailed totals with higher-level aggregates. A model can pass schema checks yet still answer the wrong business question because its grain or effective-time rule is incorrect.

Design Considerations

A conformed dimension uses a consistent key and attribute definition across multiple fact tables. Shared date, customer, product, or geography dimensions allow orders, shipments, inventory, and other processes to be compared without rebuilding business meaning in each workload. Conformance requires governance: two tables with the same name are not conformed if they apply different classifications or history rules.

Keep transformations upstream of the serving contract

The curated model should receive clean, typed, reconciled inputs. The preceding guide, DP-600: Transforming and Cleaning Data in Microsoft Fabric, explains how Dataflow Gen2, pipelines, notebooks, and T-SQL can own that preparation. This article uses those tools to materialize a dimensional target; it does not duplicate the general tool-selection and data-cleansing workflow.

Choose the store before optimizing its implementation

Lakehouse and Warehouse can both serve dimensional data, but their primary engineering surfaces and workload fit differ. Select the analytical store based on data shape, engine, team skills, and consumers before deciding how to implement keys or history. DP-600: Choosing Microsoft Fabric Analytics Stores: Lakehouse, Warehouse, Eventhouse, and Semantic Models provides that earlier architecture decision.

Control schema evolution

Adding a nullable descriptive attribute is different from changing a fact's grain or redefining a business key. Treat grain, key, and history changes as contract changes: assess downstream impact, backfill or migrate deliberately, validate reconciliations, and coordinate the cutover. Retain enough source and silver-layer data to rebuild the gold model when business rules change.

Common Mistakes

Choosing grain after selecting measures

This produces columns that cannot be aggregated consistently. Declare one fact row first, then admit only measures valid at that grain.

Joining facts directly on source identifiers

Source identifiers can collide or change and cannot distinguish historical dimension versions. Retain them as business keys, but resolve facts to governed surrogate keys.

Applying Type 2 behavior to every change

Unnecessary versions increase processing and confuse consumers. Preserve history only when a prior attribute state changes the interpretation of historical facts.

Flattening every entity into one table

A wide table can duplicate attributes, mix grains, and make history rules contradictory. Denormalize dimensions for useful analytical navigation while keeping distinct processes and independently governed entities separate.

Publishing silver data as if it were gold

Clean data is not automatically a dimensional serving model. Gold publication requires declared grain, stable keys, history policy, reconciliation, and a supported consumer contract.

Repairing physical ambiguity in the semantic model

Relationships and measures cannot reliably compensate for duplicate dimension keys, mixed fact grains, or overlapping Type 2 versions. Correct those defects in the physical lakehouse or Warehouse model. The subsequent semantic-model design should add business behavior rather than conceal storage defects.

Certification Relevance

The current DP-600 study guide includes transforming data into a star schema and preparing facts and dimensions for analytical use. The transferable skill is to move from a business process to a valid physical model: state the grain, classify facts, build dimensions, resolve surrogate keys, choose history behavior, and publish a tested serving layer.

For hands-on practice, model one transaction process and one periodic snapshot. Implement a current-state attribute and a historically tracked attribute, introduce a late-arriving dimension record, and prove that a rerun does not duplicate rows. Then reconcile totals through both SQL and a downstream consumer without changing the physical grain.

Conclusion

A reliable Fabric dimensional model begins with the business process and fact grain, not with a preferred tool. Facts must be valid at that grain; dimensions need durable surrogate keys and deliberate history; denormalization should simplify analytical navigation without mixing processes; and the complete model should be published as a validated gold-layer contract.

Implement that contract through the supported write path of the chosen lakehouse or Warehouse, and keep the physical model responsible for row meaning, key resolution, and historical validity. Leave relationship propagation and bridge-table behavior to the Power BI semantic-model layer that consumes these trusted tables.

Authoritative Sources