DP-203 Certification Practice Question #31

Question

You are designing a partition strategy for a fact table in an Azure Synapse Analytics dedicated SQL pool. The table has the following specifications:
✑ Contain sales data for 20,000 products.
Use hash distribution on a column named ProductID.

✑ Contain 2.4 billion records for the years 2019 and 2020.
Which number of partition ranges provides optimal compression and performance for the clustered columnstore index?

Accepted Answer

The question asks only for the number of partition ranges needed to keep clustered columnstore compression and performance healthy. Microsoft Learn states that each partition should have at least 1 million rows per distribution, and dedicated SQL pool always has 60 distributions. With 2.4 billion rows total, each distribution gets 40 million rows; dividing that into 40 partitions leaves about 1 million rows per distribution per partition, which aligns with the guidance. Higher partition counts would fragment the data too much and degrade columnstore quality.

More DP-203 practice questions