Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 37 discussion

Actual exam question from Microsoft's DP-203

Question #: 37
Topic #: 1

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?

A. 40
B. 240
C. 400
D. 2,400

Show Suggested Answer

Suggested Answer: A 🗳️

by Aslam208 at Dec. 11, 2021, 6 p.m.

Comments

Submit Cancel

Aslam208

Highly Voted 3 years, 6 months ago

correct

upvoted 26 times

...

AZLearn111

Highly Voted 2 years, 4 months ago

No of automatic Distributions is 60. So each distribution will have 2.4 B / 60 = 40M. For a good performance each partition within a distribution ( some time called buckets of data ) should have 1M rows per bucket. So 40M / 1M = 40 partitions.

upvoted 19 times

zekescookies

2 years, 2 months ago

Another way to think about this: The number of records for the period stated = 2.4 billion Number of underlying ("automatic") distributions: 60 2.4 billion / 60 distributions = 40 million rows 40 million / 40 partitions = 1 million rows As stated, 1 million rows per distribution are optimal for compression and performance. Divide the 40 million rows with the other partitioning options and you have too few rows per distribution -> suboptimal.

upvoted 5 times

...

JustImperius

5 months, 2 weeks ago

Thank you for your comment. I do not agree. Your logic conflates rowgroups with table partitions. You don't define partitions specifically for each distribution. The 60 distributions operate automatically, regardless of how many partitions you define.Table partitions are logical divisions, and Azure Synapse automatically handles the data distribution within each partition across its 60 distributions. If you only define 40 partitions, each partition would hold 2.4 billion ÷ 40 = 60 million rows.2.4 billion ÷ 40 = 60 million rows. This would make partitions very large and reduce the effectiveness of partition elimination, especially for date-based queries. 240 partitions aligns better with the guideline of 1–10 million rows per partition, allowing better granularity and performance for large-scale data.

upvoted 1 times

JustImperius

5 months, 2 weeks ago

Upon further review of the documentation it is clearly stated...Guess I was wrong. "If you partition your data, each partition will need to have 1 million rows to benefit from a clustered columnstore index. For a table with 100 partitions, it needs to have at least 6 billion rows to benefit from a clustered columns store (60 distributions 100 partitions 1 million rows). If your table doesn't have 6 billion rows, you have two main options. Either reduce the number of partitions or consider using a heap table instead. It also may be worth experimenting to see if better performance can be gained by using a heap table with secondary indexes rather than a columnstore table.?" https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool

upvoted 1 times

...

samirarian

Most Recent 4 months, 3 weeks ago

Selected Answer: D

1 million in every partition is the best performance

upvoted 1 times

...

iam_momo88

6 months, 1 week ago

Selected Answer: B

240 partitions balances the data across partitions, with each partition handling approximately 10 million rows: Rows per partition = 2.4 billion rows 240 partitions = 10 million rows per partition . Rows per partition= 240 partitions 2.4 billion rows =10 million rows per partition

upvoted 2 times

...

Parakkal

6 months, 1 week ago

Selected Answer: B

Isn't the question about finding the total number of partitions, not partitions per distribution. Dividing the total rows (2.4B) by 10M gives 240 partitions, which falls within the optimal range of 1–60M rows per partition. Am I missing something? The most voted answer and the other comments are to find partition within each distribution.

upvoted 2 times

...

moize

7 months ago

Selected Answer: D

Les index columnstore fonctionnent de manière optimale lorsqu'une partition contient environ 1 million de lignes par segment de colonne. Vous avez 2,4 milliards d'enregistrements. Diviser ces données pour que chaque partition contienne au moins 1 million de lignes donne : Nombre optimal de partitions = 2 , 4 milliards 1 million = 2400 Nombre optimal de partitions= 1million 2,4milliards =2400 2400 partitions garantiront que chaque partition est suffisamment compacte pour une compression efficace et des performances optimales.

upvoted 2 times

...

e56bb91

12 months ago

Selected Answer: A

2.4B = 2400M 2400/60 = 40

upvoted 1 times

...

Danweo

1 year ago

Selected Answer: A

A is correct

upvoted 1 times

...

hassexat

1 year, 10 months ago

Selected Answer: A

2,400,000,000 / 60,000,000 = 40

upvoted 2 times

...

kkk5566

1 year, 10 months ago

Selected Answer: A

A is correct

upvoted 1 times

...

akhil5432

1 year, 11 months ago

Selected Answer: A

OPTION A

upvoted 1 times

...

vrodriguesp

2 years, 4 months ago

Selected Answer: A

Considering that: Having too many partitions can reduce the effectiveness of clustered columnstore indexes if each partition has fewer than 1 million rows. Dedicated SQL pools automatically partition your data into 60 databases So a table with no partiton (or just one partition) has 60Milion of records I have use this logic, simple proportion: 1 partion : 60M = x = 2.4 B ==> 1 : 60 M = x : 2400 M ==> x = 2400 / 60 ==> x = 40 partitions

upvoted 3 times