This post is a part of the DP-600: Implementing Analytics Solutions Using Microsoft Fabric Exam Prep Hub; and this topic falls under these sections: 
Implement and manage semantic models (25-30%) 
    --> Design and build semantic models 
        --> Design and Build Composite Models

---

What Is a Composite Model?

A composite model in Power BI and Microsoft Fabric combines data from multiple data sources and multiple storage modes in a single semantic model. Rather than importing all data into the model’s in-memory cache, composite models let you mix different query/storage patterns such as:

• Import
• DirectQuery
• Direct Lake
• Live connections

Composite models enable flexible design and optimized performance across diverse scenarios.

---

Why Composite Models Matter

Semantic models often need to support:

• Large datasets that cannot be imported fully
• Real-time or near-real-time requirements
• Federation across disparate sources
• Mix of highly dynamic and relatively static data

Composite models let you combine the benefits of in-memory performance with direct source access.

---

Core Concepts

Storage Modes in Composite Models

Storage Mode	Description	Typical Use
Import	Data is cached in the semantic model memory	Fast performance for static or moderately sized data
DirectQuery	Queries are pushed to the source at runtime	Real-time or large relational sources
Direct Lake	Queries Delta tables in OneLake	Large OneLake data with faster interactive access
Live Connection	Delegates all query processing to an external model	Shared enterprise semantic models

A composite model may include tables using different modes — for example, imported dimension tables and DirectQuery/Direct Lake fact tables.

---

Key Features of Composite Models

1. Table-Level Storage Modes

Every table in a composite model may use a different storage mode:

• Dimensions may be imported
• Fact tables may use DirectQuery or Direct Lake
• Bridge or helper tables may be imported

This flexibility enables performance and freshness trade-offs.

---

2. Relationships Across Storage Modes

Relationships can span tables even if they use different storage modes, enabling:

• Filtering between imported and DirectQuery tables
• Cross-mode joins (handled intelligently by the engine)

Underlying engines push queries to the appropriate source (SQL, OneLake, Semantic layer), depending on where the data resides.

---

3. Aggregations and Hierarchies

You can define:

• Aggregated tables (pre-summarized import tables)
• Detail tables (DirectQuery or Direct Lake)

Power BI automatically uses aggregations when a visual’s query can be satisfied with summary data, enhancing performance.

---

4. Calculation Groups and Measures

Composite models work with complex semantic logic:

• Calculation groups (standardized transformations)
• DAX measures that span imported and DirectQuery tables

These models require careful modeling to ensure that context transitions behave predictably.

---

When to Use Composite Models

Composite models are ideal when:

A. Data Is Too Large to Import

• Large fact tables (> hundreds of millions of rows)
• Delta/OneLake data too big for full in-memory import
• Use Direct Lake for these, while importing dimensions

B. Real-Time Data Is Required

• Operational reporting
• Systems with high update frequency
• Use DirectQuery to relational sources

C. Multiple Data Sources Must Be Combined

• Relational databases
• OneLake & Delta
• Cloud services (e.g., Synapse, SQL DB, Spark)
• On-prem gateways

Composite models let you combine these seamlessly.

D. Different Performance vs Freshness Needs

• Import for static master data
• DirectQuery or Direct Lake for dynamic fact data

---

Composite vs Pure Models

Aspect	Import Only	Composite
Performance	Very fast	Depends on source/query pattern
Freshness	Scheduled refresh	Real-time/near-real-time possible
Source diversity	Limited	Multiple heterogeneous sources
Model complexity	Simpler	Higher

---

Query Execution and Optimization

Query Folding

• DirectQuery and Power Query transformations rely on query folding to push logic back to the source
• Query folding is essential for performance in composite models

Storage Mode Selection

Good modeling practices for composite models include:

• Import small dimension tables
• Direct Lake for large storage in OneLake
• DirectQuery for real-time relational sources
• Use aggregations to optimize performance

---

Modeling Considerations

1. Relationship Direction

• Prefer single-direction relationships
• Use bidirectional filtering only when required (careful with ambiguity)

2. Data Type Consistency

• Ensure fields used in joins have matching data types
• In composite models, mismatches can cause query fallbacks

3. Cardinality

• High cardinality DirectQuery columns can slow queries
• Use star schema patterns

4. Security

• Row-level security crosses modes but must be carefully tested
• Security logic must consider where filters are applied

---

Common Exam Scenarios

Exam questions may ask you to:

• Choose between Import, DirectQuery, Direct Lake and composite
• Assess performance vs freshness requirements
• Determine query folding feasibility
• Identify correct relationship patterns across modes

Example prompt:

“Your model combines a large OneLake dataset and a small dimension table. Users need current data daily but also fast filtering. Which storage and modeling approach is best?”

Correct exam choices often point to composite models using Direct Lake + imported dimensions.

---

Best Practices

• Define a clear star schema even in composite models
• Import dimension tables where reasonable
• Use aggregations to improve performance for heavy visuals
• Limit direct many-to-many relationships
• Use calculation groups to apply analytics consistently
• Test query performance across storage modes

---

Exam-Ready Summary/Tips

Composite models enable flexible and scalable semantic models by mixing storage modes:

• Import – best performance for static or moderate data
• DirectQuery – real-time access to source systems
• Direct Lake – scalable querying of OneLake Delta data
• Live Connection – federated or shared datasets

Design composite models to balance performance, freshness, and data volume, using strong schema design and query optimization.

For DP-600, always evaluate:

• Data volume
• Freshness requirements
• Performance expectations
• Source location (OneLake vs relational)

Composite models are frequently the correct answer when these requirements conflict.

---

Practice Questions:

Here are 10 questions to test and help solidify your learning and knowledge. As you review these and other questions in your preparation, make sure to …

• Identifying and understand why an option is correct (or incorrect) — not just which one
• Look for and understand the usage scenario of keywords in exam questions to guide you
• Expect scenario-based questions rather than direct definitions

1. What is the primary purpose of using a composite model in Microsoft Fabric?

A. To enable row-level security across workspaces
B. To combine multiple storage modes and data sources in one semantic model
C. To replace DirectQuery with Import mode
D. To enforce star schema design automatically

✅ Correct Answer: B

Explanation:
Composite models allow you to mix Import, DirectQuery, Direct Lake, and Live connections within a single semantic model, enabling flexible performance and data-freshness tradeoffs.

---

2. You are designing a semantic model with a very large fact table stored in OneLake and small dimension tables. Which storage mode combination is most appropriate?

A. Import all tables
B. DirectQuery for all tables
C. Direct Lake for the fact table and Import for dimension tables
D. Live connection for the fact table and Import for dimensions

✅ Correct Answer: C

Explanation:
Direct Lake is optimized for querying large Delta tables in OneLake, while importing small dimension tables improves performance for filtering and joins.

---

3. Which storage mode allows querying OneLake Delta tables without importing data into memory?

A. Import
B. DirectQuery
C. Direct Lake
D. Live Connection

✅ Correct Answer: C

Explanation:
Direct Lake queries Delta tables directly in OneLake, combining scalability with better interactive performance than traditional DirectQuery.

---

4. What happens when a DAX query in a composite model references both imported and DirectQuery tables?

A. The query fails
B. The data must be fully imported
C. The engine generates a hybrid query plan
D. All tables are treated as DirectQuery

✅ Correct Answer: C

Explanation:
Power BI’s engine generates a hybrid query plan, pushing operations to the source where possible and combining results with in-memory data.

---

5. Which scenario most strongly justifies using a composite model instead of Import mode only?

A. All data fits in memory and refreshes nightly
B. The dataset is static and small
C. Users require near-real-time data from a large relational source
D. The model contains only calculated tables

✅ Correct Answer: C

Explanation:
Composite models are ideal when real-time or near-real-time access is needed, especially for large datasets that are impractical to import.

---

6. In a composite model, which table type is typically best suited for Import mode?

A. High-volume transactional fact tables
B. Streaming event tables
C. Dimension tables with low cardinality
D. Tables requiring second-by-second freshness

✅ Correct Answer: C

Explanation:
Importing dimension tables improves query performance and reduces load on source systems due to their relatively small size and low volatility.

---

7. How do aggregation tables improve performance in composite models?

A. By replacing DirectQuery with Import
B. By pre-summarizing data to satisfy queries without scanning detail tables
C. By eliminating the need for relationships
D. By enabling bidirectional filtering automatically

✅ Correct Answer: B

Explanation:
Aggregations allow Power BI to answer queries using pre-summarized Import tables, avoiding expensive queries against large DirectQuery or Direct Lake fact tables.

---

8. Which modeling pattern is strongly recommended when designing composite models?

A. Snowflake schema
B. Flat tables
C. Star schema
D. Many-to-many relationships

✅ Correct Answer: C

Explanation:
A star schema simplifies relationships, improves performance, and reduces ambiguity—especially important in composite and cross-storage-mode models.

---

9. What is a potential risk of excessive bidirectional relationships in composite models?

A. Reduced data freshness
B. Increased memory consumption
C. Ambiguous filter paths and unpredictable query behavior
D. Loss of row-level security

✅ Correct Answer: C

Explanation:
Bidirectional relationships can introduce ambiguity, cause unexpected filtering, and negatively affect query performance—risks that are amplified in composite models.

---

10. Which feature allows a composite model to reuse an enterprise semantic model while extending it with additional data?

A. Direct Lake
B. Import mode
C. Live connection with local tables
D. Calculation groups

✅ Correct Answer: C

Explanation:
A live connection with local tables enables extending a shared enterprise semantic model by adding new tables and measures, forming a composite model.

---