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.

• ✓

  Replicate the Store dimension table

  Why this is correct

  Replicating the Store dimension table is the correct approach because a replicated table is physically copied to every distribution in the dedicated SQL pool. When the fact table joins with the Store table on StoreID, the join is performed locally on each distribution, completely eliminating data movement between distributions. This is ideal for small dimension tables (under 1 GB) that are frequently used in joins and rarely updated, making query performance significantly faster.

• ✗

  Change the distribution of the fact table to round-robin

  Why it's wrong here

  Changing the fact table distribution to round-robin is incorrect because round-robin spreads rows arbitrarily across distributions without any key-based colocation. Every subsequent join—whether with Store, Product, or any other table—will require the engine to reshuffle and redistribute data across all distributions, greatly increasing data movement and slowing down queries. Round-robin is only suitable for staging or loading tables, not for fact tables that are heavily joined in star schema queries.

• ✗

  Change the distribution key of the fact table to StoreID

  Why it's wrong here

  Changing the distribution key of the fact table to StoreID is not the right fix, because hash distributing on StoreID would only colocate the fact table with the Store dimension on that specific key. However, the fact table also joins with the Product dimension; if Product is not replicated or distributed on ProductID, the Product join will still cause data movement and performance degradation. Additionally, rebuilding the table to change the distribution key requires a costly CTAS operation and a full table scan, making this option both disruptive and insufficient for solving the broader join problem.

• ✗

  Add a nonclustered index on the StoreID column in the fact table

  Why it's wrong here

  Adding a nonclustered index on StoreID in the fact table will not address the root cause of slow joins, because in a dedicated SQL pool, query performance is dominated by data distribution and movement, not by traditional row-based indexing. The default clustered columnstore index is already optimized for large fact tables, and a nonclustered index only helps point lookups or small row retrievals—it does not change how rows are distributed across the MPP nodes. Since the join slowness stems from data shuffling during join execution, the index provides no benefit and leaves the core performance issue unresolved.

Go deeper

Related to this question