PL-300 Composite model Practice Question
You are a Power BI developer for a financial services company. The company has a large transactional database in Azure Synapse Analytics. The database contains a table 'Transactions' with 2 billion rows. The table includes columns: TransactionID, AccountID, TransactionDate, Amount, Type (Deposit/Withdrawal), Status (Pending/Completed). You need to build a Power BI semantic model that allows executives to analyze monthly trends of completed deposit amounts by account type (e.g., Savings, Checking). The account type is in a separate 'Accounts' table (1 million rows) with columns: AccountID, AccountType, CustomerID. The model must refresh within 2 hours. Due to the large data volume, you cannot import the entire Transactions table. What should you do?
⚠ Common exam trap
The trap is that candidates may think Power BI aggregations (Option B) are sufficient, but they do not reduce the data volume for import; the correct approach is to pre-aggregate at the source (Synapse) and use a composite model for drill-through.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create an aggregated table in Synapse that pre-aggregates data at the month and account type level, then use DirectQuery on the aggregated table, and use a composite model with the detail table for drill-through.
Option D is correct because it pre-aggregates the 2 billion-row Transactions table in Synapse at the month and account type grain, which drastically reduces the data volume Power BI must query, and then uses DirectQuery on that small aggregated table so refreshes complete well within the 2-hour window; the composite model with the detail table preserves drill-through to transaction-level detail when needed. Option A is wrong because mixing incremental refresh (Import) with DirectQuery for older data still requires importing large volumes and does not aggregate the 2 billion rows, so it will not reliably meet the 2-hour refresh. Option B is wrong because Power BI aggregations still depend on importing or querying the underlying detail, and importing only necessary columns from 2 billion rows remains too large for the refresh SLA. Option C is wrong because DirectQuery over the full 2 billion-row Transactions table pushes all aggregation work to Synapse at query time, producing slow executive reports and no pre-aggregation benefit.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use incremental refresh to import only recent data, and use DirectQuery for older data.
Why it's wrong here
This hybrid approach (importing recent data via incremental refresh and streaming older data via DirectQuery) cuts refresh time, but it does little for query performance: any report that needs historical perspective sends large SQL queries across the entire older transaction volume in Synapse. The imported recent partition is small, but the DirectQuery older partition still forces the user, and Synapse, to scan billions of rows on almost every interaction. It also creates an awkward boundary at the refresh date and doubles the semantic complexity (e.g., partition cutoffs) without solving the core problem of excessive data volume.
- ✗
Import only the necessary columns and use Power BI aggregations to pre-aggregate.
Why it's wrong here
Power BI aggregations are stored in the model and can be used for high-level visuals, but the underlying transaction table must still be imported—or accessed via DirectQuery—in full granularity. The 'import only necessary columns' step reduces column width, not row count, so a 5-billion-row table still consumes massive memory and takes a long time to refresh. In contrast, pre-aggregating upstream in Synapse lets the database engine compress billions of rows into a few thousand before they ever reach Power BI, eliminating the need to store or query the raw detail in the model.
- ✗
Use DirectQuery on the full Transactions table and rely on the Synapse query optimizer.
Why it's wrong here
DirectQuery over the complete Transactions table sends every visual query to Synapse as a huge scan, and even with columnstore indexes and query optimizer improvements, latency from network round-trips and row exfiltration will overwhelm the report. Optimizer settings help with query plans, not with fundamental volume: billion-row scans are still slow, and Power BI's query timeouts and default MAX_SELECTED_ROWS limits make visuals brittle. This puts the load on Synapse for every dashboard interaction and offers no cached aggregate, so page loads will frequently time out or return inconsistent results.
- ✓
Create an aggregated table in Synapse that pre-aggregates data at the month and account type level, then use DirectQuery on the aggregated table, and use a composite model with the detail table for drill-through.
Why this is correct
Pre-aggregating in Synapse—for example via CTAS to create a month/account-type grain—shrinks the dataset from tens of billions of rows to a few thousand, so a DirectQuery connection to that aggregate answers all high-level visuals with near-instant response times. The composite model adds the original detail table (imported in a reduced form or also DirectQuery) strictly for drill-through when a user clicks a specific period/account; because drill-through filters to a single account and month first, the detail query touches a modest subset. This is the classic navigation strategy: use aggregated DirectQuery tables for high-cardinality facts, and keep fine-grained details available only when needed, which balances performance, freshness, and interactivity.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 524 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.