DP-600: configure and optimize Partitions in semantic models
I will teach you the skill of configuring and optimizing partitions in semantic models — a practical DP-600 skill that significantly improves refresh time and query performance in scenarios with large data volumes. Knowing how to share, manage and optimize partitions is useful both for the exam and for real implementations in Power BI and Fabric.
What you need to know
Partitions split a table in the model into logical segments, allowing incremental refreshes, parallelism and data management at different granularities. In tabular models (Power BI / Fabric), a table can have multiple partitions, each corresponding to a range of values (e.g.: by day, month, year). The engine processes and loads partitions independently, which reduces the work in each refresh and can improve data reading if combined with aggregations and storage modes.
Example: an invoices table with 5 years of data can be partitioned by month. Thus, when refreshing only the last month (incremental), it is not necessary to reload 59 months of history.
How it works
Essential concepts:
- Partition keys: the column used to split the data (e.g.: InvoiceDate).
- Range-based partitions: continuous ranges (days, months, years).
- Processing: refresh operations can be applied to individual partitions (process full, process add, process replace).
- Storage: each partition can be in-memory (Import) or queried via DirectQuery / federated mode, depending on the storage mode of the model/table.
In practice
Here is a practical step-by-step to create and manage partitions in a development environment (Tabular Editor / XMLA / Power BI Desktop with partition support for connected models):
Choose the partition key
Identify a column with good temporal or logical granularity that allows dividing the data into balanced blocks (e.g.: OrderDate by month).
Define the partitioning strategy
Decide ranges and retention policy. Ex.: monthly partitions for the last 24 months; yearly partitions for older history.
Create partitions in Tabular Editor (simplified example)
// Exemplo conceptual em Tabular Editor TMSL/JSON { "createPartitions":{ "table":"Sales", "partitions":[ {"name":"Sales_2024_01","query":"SELECT * FROM Sales WHERE OrderDate >= '2024-01-01' AND OrderDate < '2024-02-01'"}, {"name":"Sales_2024_02","query":"SELECT * FROM Sales WHERE OrderDate >= '2024-02-01' AND OrderDate < '2024-03-01'"} ] } }Note: adapt the syntax to your method (Tabular Editor, PowerShell, XMLA). In Power BI Desktop, editing partitions requires connecting the model via external tools.
Implement incremental refresh
Configure the refresh pipeline to only process recent partitions (process add/replace for the last month's partition). Keep historical partitions intact to speed up refresh.
Manage partition lifecycle
Automate merging of old partitions to reduce the total number (e.g.: combine old months into quarters/years) and apply archival policies when necessary.
Monitor and adjust
Use processing and query duration metrics to identify problematic partitions. Rebalance ranges if there are very large or empty partitions.
Common mistakes
Choosing an inappropriate partition key: using a column with many null values or low cardinality (e.g.: status) results in unbalanced partitions; prefer temporal columns or fields with uniform distribution.
Too many small partitions: creating thousands of very small partitions increases management overhead and can degrade performance; group ranges reasonably (monthly vs daily) depending on the use case.
Ignoring retention policies: keeping all partitions without cleanup leads to unnecessary model growth; plan retention and archival.
How to practice
Practice by creating partitions in a test model using Tabular Editor or deploying via XMLA to a Fabric/Power BI workspace. Test various scenarios: monthly partitions, merging old partitions and incremental refresh. To validate that you are following best practices and training for the exam, use the official Microsoft Practice Assessment FREE and the official study guide (both free). These resources help you identify gaps in the skills measured without resorting to prohibited material.
In summary
- Partitions split tables to allow incremental refreshes and parallel processing.
- Choose an appropriate partition key (usually temporal) and define balanced ranges.
- Avoid many very small partitions and implement retention/merge policies.
- Use tools like Tabular Editor and XMLA to create and manage partitions and test with real data.