Azure Data Explorer partitioning policy

Question

If I have a column with 100,000 cardinality. Shouldn't the maximum number of partitions be 100,000? Isn't it true that ADX should construct a partition for each different value in the column? Why is it even necessary to fill out the MaxPartitionCount property?

Answer 1

The end objective isn't to have a different partition for each individual value of the partition key in suggested cases (described in the doc you linked to).

Having a large number of partitions (100k in your case, or billions in the event of a unique device ID) can result in a large number of little data shards, which isn't ideal.

Even with "only" 128 partitions as the maximum, and default built-in indexing (regardless of explicit data partitioning), the ability to narrow the full data set down to a small number of partitions/shards at query planning time can result in significant resource utilisation and execution time savings.