DP-900 Practice Question: Identify considerations for relational data on Azure

Answer analysis

Option-by-option breakdown

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

• ✓

  Create a clustered columnstore index on the table.

  Why this is correct

  A clustered columnstore index is the optimal choice for an analytical warehouse table because it physically stores each column separately, allowing the query engine to read only the columns needed for aggregations (e.g., ProductID, Month, SalesAmount). This columnar layout enables higher compression ratios (often 5-10x) and uses vectorized batch mode execution, which accelerates scans and SUM/COUNT/GROUP BY operations dramatically compared to row-based storage. The fact that it is clustered means the entire table is organized as a columnstore, eliminating rowstore lookups and making full-scan analytical queries extremely efficient.

• ✗

  Create a nonclustered index on the ProductID column.

  Why it's wrong here

  A nonclustered index on ProductID alone would only accelerate lookups filtering by that column, but the queries aggregate by product and month, requiring a covering index that includes both columns to avoid key lookups into the full table. It is tempting because nonclustered indexes are commonly used to speed up equality searches on a single column, and in a scenario where queries filtered solely by ProductID, this index would be the correct choice.

• ✗

  Create a filtered index for the most recent month's data.

  Why it's wrong here

  A filtered index on the current month's data is a rowstore B-tree index that only covers a subset of rows, so it is useful for queries that explicitly filter to that single month. However, analytical reporting typically aggregates across many months or the entire historical data set; when a query scans multiple months, the filtered index cannot be used and the query engine must fall back to scanning the heap or clustered index. Additionally, even for the covered subset, a rowstore index still lacks the columnar compression and batch-mode processing of a columnstore, so it will not provide the same level of aggregation performance.

• ✗

  Create a clustered rowstore index (default) and rely on database compression.

  Why it's wrong here

  A clustered rowstore index (default heap/B-tree) is designed for OLTP workloads with point lookups and small-range scans; for analytical queries that scan massive row counts, it forces the storage engine to read entire rows from data pages, even when only a few columns are needed. Database compression does reduce the physical I/O by shrinking stored rows, but it is a generic technique that cannot match the column-wise compression and segment elimination of a columnstore index. Moreover, rowstore indexes do not support batch-mode execution, so aggregation queries still run in row mode, leaving significant performance on the table.

Go deeper

Related to this question