Implement and manage semantic models Study Guide — DP-600

Implement and Manage Semantic Models

This domain accounts for 27% of the DP-600 exam — the single largest domain. It tests your ability to design, build, and optimize enterprise-scale semantic models in Microsoft Fabric. Expect questions on storage architecture decisions, DAX authoring, performance tuning, and advanced model features such as calculation groups and incremental refresh. Conceptual understanding and scenario-based reasoning are both required; memorizing limits and defaults is equally important.

Storage Modes: Direct Lake, DirectQuery, Import, and Composite

Core Concepts

Fabric semantic models support four storage modes. Choosing the wrong one is a frequent exam trap.

Direct Lake Deep Dive

Direct Lake is the default and preferred mode for semantic models built on Fabric Lakehouses and Warehouses. It reads Delta Parquet files directly from OneLake without loading data into the model's memory at query time. Data is transcoded into VertiPaq format in memory on first query (a process called framing).

Two attachment points for Direct Lake:

• OneLake shortcut / Lakehouse endpoint — model attaches directly to the Delta tables in OneLake. Full Direct Lake capability.
• SQL analytics endpoint — model attaches to the SQL endpoint of a Lakehouse or Warehouse. When a query cannot be satisfied natively (e.g., unsupported DAX → SQL translation), it falls back to DirectQuery against the SQL endpoint.

Fallback Behavior

Fallback occurs when Direct Lake cannot service a query natively and switches to DirectQuery mode against the SQL analytics endpoint.

Fallback is triggered by:

• Calculated columns or calculated tables in the model
• Row-level security (RLS) defined using certain DAX expressions
• Complex DAX that cannot be translated
• Data types or relationships not supported in Direct Lake

DirectLakeBehavior property (set in model settings):

• Automatic (default) — falls back to DirectQuery when needed
• DirectLakeOnly — disables fallback; queries fail instead of falling back (use when fallback latency is unacceptable)

Direct Lake Limits by Fabric SKU

Note: When model data exceeds SKU limits, the entire table falls back to DirectQuery.

Common Traps

• Assuming Direct Lake requires a scheduled refresh — it does not; it reads the latest Delta snapshot automatically.
• Confusing "framing" with "refresh" — framing is an in-memory transcoding at query time, not a background data load.
• Thinking Direct Lake attached to a SQL endpoint never falls back — it always can fall back unless DirectLakeOnly is set.
• Assuming RLS always causes fallback — only certain RLS patterns trigger it.

Worked Scenario

Requirement: A Fabric F4 capacity hosts a Lakehouse with a 500-million-row sales table. Users report slow query performance. What is the most likely cause?

Reasoning: The F4 SKU caps Direct Lake framing at 300 million rows per table. The 500-million-row table exceeds this limit, so the entire table falls back to DirectQuery against the SQL analytics endpoint, causing slower performance. The solution is to upgrade to F8 or partition/aggregate the table to stay within limits.

Star Schema, Bridge Tables, and Many-to-Many Relationships

Star Schema Fundamentals

A star schema consists of a central fact table connected to dimension tables via single-direction (or bi-directional) relationships. The exam expects you to recognize and build correct relationship configurations.

Bridge Tables

A bridge table resolves a many-to-many relationship between two tables that cannot be directly joined. It contains the foreign keys of both tables plus no extra measures.

Example: Students enroll in multiple Courses; Courses have multiple Students. A StudentCourse bridge table holds StudentID and CourseID.

Critical rule: When using a bridge table with bidirectional cross-filtering, enable bidirectional filtering cautiously — it can cause ambiguous filter paths and performance degradation.

CROSSFILTER DAX function allows you to override relationship filter direction within a measure.

Many-to-Many Patterns

Two types of M:M on the exam:

1. Weak M:M (no bridge): Power BI allows a direct M:M relationship between two tables. Filters propagate in a limited way. Best avoided in enterprise models.
2. Strong M:M (bridge table): Preferred pattern. Explicit bridge table provides full control over filter propagation.

TREATAS is the key DAX function for applying a virtual relationship or propagating a filter across tables without a physical relationship.

Decision flow for filter direction in star schema relationships.

Common Traps

• Placing measures in dimension tables — measures always belong in fact tables or dedicated measure tables.
• Enabling bidirectional filtering on all relationships by default — only enable when explicitly required.
• Forgetting that a direct M:M relationship in Power BI still requires both tables to have a common key column.
• Confusing CROSSFILTER (relationship override in DAX) with changing the model relationship property permanently.