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.

• ✗

  Change to round-robin distribution

  Why it's wrong here

  Round-robin distribution assigns rows to tablespaces in a round-robin fashion, without considering any data relationship. For a fact table queried heavily by CustomerID, this scatters rows for the same customer across all compute nodes, forcing the query engine to shuffle and move data between nodes to recombine results. That data movement substantially slows down the warehouse queries, so although round-robin is simple for loading data, it undermines the goal of collocating data with filters.

• ✓

  Change the distribution column to CustomerID

  Why this is correct

  Changing the distribution column to CustomerID makes Azure Synapse hash-distribute the rows based on that column, so all rows belonging to the same customer land on the same compute node. When a query filters on CustomerID, each node can evaluate only its local data, avoiding cross-node data shuffling and reducing query latency. This also enables colocated joins if you distribute a related dimension table on the same column, making it the correct choice for filter-heavy analytics.

• ✗

  Use rowstore instead of columnstore

  Why it's wrong here

  Columnstore indexes in Synapse are the default and optimal choice for analytical workloads because they compress data and read only the columns needed for aggregation and filtering. Switching to rowstore would require reading entire rows and would increase I/O, especially for large fact tables. While rowstore can help with single-row point lookups and frequent updates, it degrades the bulk scan performance typical of data warehouse queries, so it is not the right fix.

• ✗

  Replicate the fact table to all compute nodes

  Why it's wrong here

  Replicating a table copies the entire contents to every compute node, which is designed for small, slow-changing dimension tables so joins can be performed locally without data movement. A fact table in a data warehouse is typically large and frequently updated, so replicating it would consume massive storage, increase maintenance overhead, and slow down load operations. Synapse explicitly recommends replicated tables only when they are under a certain size (e.g., a few GB), making this option impractical for a fact table.