PL-300 Prepare the data Practice Question
You are developing a Power BI semantic model that uses a large fact table from Azure Synapse Analytics. You need to optimize the model for performance. Which THREE actions should you take?
⚠ Common exam trap
Test-takers frequently think combining tables into a wide table simplifies the model, but this actually degrades performance by breaking star schema design principles, which are critical for efficient query processing in Power BI.
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
✓
Disable auto-date/time feature for the model.
Option A is correct because disabling the Auto date/time feature prevents Power BI from creating hidden date tables for every date column, which reduces model size and memory consumption and speeds up processing and refresh. Option B is correct because integer surrogate keys compress far better in the VertiPaq engine than string keys, lowering memory usage and improving join and relationship performance. Option E is correct because removing unnecessary columns from the fact table reduces the model's memory footprint and improves compression and query performance. Option C is not appropriate because combining the fact table with dimensions into a single wide table denormalizes the star schema, increases storage and redundancy, and typically degrades performance rather than optimizing it. Option D is not appropriate because calculated columns are computed at refresh time and consume memory, whereas transformations in Power Query are performed during load and are generally preferred for performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Disable auto-date/time feature for the model.
Why this is correct
Disabling the auto-date/time feature prevents Power BI from generating hidden date tables for every date column in the model. These hidden tables add significant memory and storage overhead, especially when you have many date columns. By turning the feature off, you reduce the model's size and refresh time, and you can instead create a single explicit date table if you need date logic.
- ✓
Use integer surrogate keys instead of string keys for dimensions.
Why this is correct
Integer surrogate keys are smaller and faster than string keys when used in relationships and joins. In the VertiPaq columnar engine, integer values store more efficiently and are quicker to compare and aggregate than text strings, which helps reduce both storage footprint and query latency. Surrogate keys also avoid potential issues with slow-changing string values and improve relationship propagation speed.
- ✗
Combine the fact table with dimension tables into a single wide table.
Why it's wrong here
Combining fact and dimension tables into one wide table is an anti-pattern for Power BI modeling because it increases the row width and the amount of data stored, leading to slower refresh and higher memory usage. It also prevents the query engine from leveraging the optimized star-schema relationships and role-playing dimensions, which can degrade filter and aggregation performance. Keeping a normalized star schema with separate dimensions and facts improves compression and query performance by reducing redundant data.
- ✗
Create calculated columns in the fact table instead of in Power Query.
Why it's wrong here
Calculated columns are evaluated during data load and are stored as physical columns in the in-memory store, which increases model size and can slow down refresh times. In contrast, transformations performed in Power Query are applied before the data enters the model and can benefit from query folding and more efficient processing. It is better to create derived columns in Power Query or to use measures for aggregations, because calculated columns in the fact table can bloat the model unnecessarily.
- ✓
Remove unnecessary columns from the fact table.
Why this is correct
Removing unnecessary columns reduces the number of columns stored in the VertiPaq columnar store, which directly reduces memory consumption and refresh time. Since the columnar engine scans each column independently, dropping unneeded columns also speeds up query execution by reducing the amount of data that must be read. This is a simple but effective optimization that makes the model leaner and more performant.
Go deeper
Related to this question
About these practice questions
One of 524 original PL-300 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.