DP-900 Describe an analytics workload on Azure Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Clustered columnstore index

  Why this is correct

  A clustered columnstore index stores data column-by-column instead of row-by-row, allowing the dedicated SQL pool to read only the columns needed for the aggregation (e.g., ProductID, Region, and measure columns), which dramatically reduces I/O. Each column segment stores min/max metadata, enabling the engine to skip entire rowgroups that fall outside the queried time range. It also uses batch-mode execution and high compression, both of which make full-table scans and large aggregations—the dominant pattern in this fact-table workload—extremely efficient.

• ✗

  Clustered index on the primary key

  Why it's wrong here

  A clustered index on the primary key (typically SalesID) organizes rows as a B-tree keyed by that column, which is optimal for point lookups such as `WHERE SalesID = 12345`. For an aggregation grouped by ProductID and Region across many months, this rowstore layout forces a full scan of the entire table because every row must be visited; the B-tree provides no way to skip data based on ProductID or date. It also compresses less effectively and executes in row mode, so it is inherently slower for large analytic scans and is not the recommended index type for dedicated SQL pool fact tables.

• ✗

  Hash-distributed table on SalesID

  Why it's wrong here

  Hash distribution determines how rows are spread across the 60 distributions—it is a physical placement strategy across compute nodes, not an index structure within each distribution. Distributing by SalesID co-locates rows belonging to the same sale, which helps joins and point lookups on SalesID, but it provides no metadata or ordering that accelerates a scan grouped by ProductID and Region. Because every distribution still contains arbitrary ProductID/Region combinations, the query must scan rows across all distributions; the index remains unchanged and therefore does not solve the underlying scan-and-aggregation bottleneck.

• ✗

  Non-clustered index on (ProductID, Region)

  Why it's wrong here

  A non-clustered index on (ProductID, Region) fails because it does not address the fundamental issue of scanning the entire table for time-series data. Dedicated SQL pools benefit significantly from data partitioning, typically by date, to physically organise data and eliminate scanning irrelevant months. While tempting as `ProductID` and `Region` are the aggregation columns, this index type primarily speeds up lookups or aggregations on those specific columns *after* the relevant data subset has been identified. It would be appropriate if the table was already partitioned by date, and the bottleneck was solely on the product/region aggregation within that pre-filtered partition.

Go deeper

Related to this question