Exam DP-203 topic 1 question 78 discussion

Clustered Column Store will by default have 60 partitions. And to achieve best compression we need at least 1 Million rows per partition, hence Option D 60 Millions (1M per partition)

Correct Answer: D

D is correct-:Partitioning in a dedicated SQL pool in Azure Synapse Analytics is typically used to manage very large tables, and the recommendation is to start considering partitions when the table contains 60 million rows or more. Partitioning helps optimize both data compression and query performance by allowing the system to process smaller subsets of data more efficiently. However, partitioning comes with overhead, and if you partition tables that are too small, it can actually degrade performance. Clustered columnstore indexes are designed to provide efficient compression and query performance for large datasets. Partitioning further helps with large fact tables, but for smaller tables (e.g., fewer than 60 million rows), partitioning is usually not necessary and might even be counterproductive.

"When creating partitions on clustered columnstore tables, it is important to consider how many rows belong to each partition. For optimal compression and performance of clustered columnstore tables, a minimum of 1 million rows per distribution and partition is needed. Before partitions are created, dedicated SQL pool already divides each table into 60 distributions." https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#partition-sizing

1M * 60 = 60M

it is 1 Million. the question is clearly asking for minimum number of rows before creating partitions. and in Microsoft document they stated that 1 million rows is the minimum number of rows before partitioning a table

1 Million (Option C) is correct. You need the minimum number of rows to create a optimized partition. A single optimal partition requires 1 Million rows.

I think the correct answer should be 120 million rows. Since splitting the data up into 2 partitions would result in 1 million rows per distribution and partition for 120 million rows

even without knowing how many partitions you're going to create, you know that each partition should contain at least 1million rows. 60million rows are the only case enabling to use partitions.

Cluster columnstore tables begin to achieve optimal compression once there is more than 60 million rows. For small lookup tables, less than 60 million rows, consider using HEAP or clustered index for faster query performance. -- Microsoft

To optimize data compression and query performance for Table in Azure Synapse Analytics dedicated SQL pool, you should create partitions when the table contains at least 60 million rows. Partitioning tables can improve query performance by reducing the amount of data that needs to be scanned. It can also improve data compression by allowing each partition to be compressed separately. In general, you should consider partitioning a table when it contains a large amount of data and queries frequently filter on a specific column or set of columns

Question says "What is the minimum number of rows that Table1 should contain before you create (add/new/extra) partitions?" As per microsoft documentation, each partition will contain 1Million records. So, if there atleast 1million records, we can go for partitioning. Here is the link for documentation https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16

60m, see https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition

A 100mil is correct >> From the answers to this Q, I see that MS has done a bad job because people don't understand what distributions or partitions are. My explanation: Each table with column store index is auto divided into 60 distributions, on each of these distributions there is auto 1 partition. For good performance (with column store) each partition must have at least 1Mil rows. The question was: "What is the minimum number of rows that Table1 should contain before you create (add/new/extra) partitions?" So there is no point in creating partitions with 60M rows, because then you divide this into 0.5Mil per partition. At least 120Mil would be ideal, but 100Mil already starts.

60 million

is correct

should be D