This is a common Power BI (Storage Modes) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Question What is a "Composite model" in Power BI, and give a scenario where it would be useful.

Answer & Explanation

What a Composite model is

In Power BI a composite model is a single data model that can contain tables that use different storage modes.
Instead of being forced to make every table either an Import (data is loaded into the .pbix file) or a DirectQuery (queries are sent to the source at run‑time), you can mix them side‑by‑side and still create relationships, calculated columns, measures, and visuals that span all the tables.

Key points that make a composite model distinct:

Mixed storage – any table can be set to Import, DirectQuery, or even Dual (which lets Power BI decide at query time).
Unified relationships – you can join an imported fact table to a DirectQuery dimension table, or join two DirectQuery tables from different sources.
Single report canvas – the end‑user sees one report; Power BI automatically routes each visual’s query to the appropriate source(s).
Calculated objects – you can still create calculated columns, measures, and hierarchies that reference tables regardless of their storage mode.

Why it matters

Performance – Large, relatively static tables can be imported for lightning‑fast aggregation.
Freshness – Small, rapidly changing tables can stay in DirectQuery so the report always shows the latest data without a scheduled refresh.
Data size limits – Import is limited by the .pbix file size (≈1 GB for Pro, larger for Premium). By keeping only the essential data in import you stay within those limits.
Source diversity – You can combine an on‑premises SQL Server (DirectQuery) with a cloud Azure Data Lake (Import) in the same model, avoiding the need to copy everything into one place.

Typical scenario where a composite model shines

Scenario: A sales performance dashboard that needs both historic trends and up‑to‑the‑minute inventory status.

Historical sales – The company has five years of sales transactions stored in a data warehouse. The data changes only when a new batch is loaded (once a day). You import this table into Power BI. Because it’s in memory, slicers, time‑intelligence calculations, and large aggregations run instantly.
Current inventory – Inventory levels are updated every few minutes in an on‑premises ERP system. You add the Inventory table as a DirectQuery connection. Every time a visual that uses inventory is refreshed, Power BI sends a live query to the ERP, guaranteeing the numbers are current.
Combined view – You create a relationship between the imported Sales table (by Product ID) and the DirectQuery Inventory table. A visual that shows “Sales last month vs. current stock” automatically pulls sales data from the in‑memory cache and inventory data from the live source, delivering both speed and freshness in a single report.

Other practical examples

Finance reporting – Import yearly budget data (static) and DirectQuery the daily cash‑flow feed for real‑time cash position.
Customer analytics – Import a large customer master file, DirectQuery a small “active sessions” table that records live website activity.
Multi‑source blending – Import a CSV file stored in Azure Blob (static reference data) and DirectQuery a SQL Server database (operational data) in the same model.

How you would build it (high‑level steps)

Open Power BI Desktop and load the large, stable tables using Get Data → Import.
Add the small, volatile table using Get Data → DirectQuery (or set an existing imported table to DirectQuery via the table’s Storage mode property).
Verify that relationships are correctly defined; Power BI will automatically create them if column names match, otherwise create them manually.
Build measures and calculated columns as needed – they work across storage modes.
Test a visual that uses fields from both types of tables; you’ll see Power BI generates a combined query that pulls the imported data from the in‑memory cache and the DirectQuery data from the source.
Publish to the service. If you need larger import sizes or DirectQuery‑only features, consider a Premium capacity workspace.

Bottom line

A composite model lets you get the best of both worlds: the speed of imported data for the bulk of your model and the immediacy of DirectQuery for the pieces that must stay current. Knowing when to split your data by volatility, size, and source, and then wiring those pieces together in a single Power BI model, is a core skill for building scalable, responsive dashboards. Use it whenever a single storage mode would force you to sacrifice either performance or data freshness.