Hotspot Question You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema. You plan to have a fact table for website visits.

Question

Exhibit

Answer Area

• Distribution:
  HashRound robinReplicated
• Index:
  Clustered columnstoreClusteredNonclustered

Explanation

This question tests knowledge of Azure Synapse Analytics dedicated SQL pool distribution and index strategies for a 5 GB fact table in a star schema, optimizing for query performance.

Approach. For distribution type, 'Round-Robin' is recommended for a 5 GB table. Although hash distribution is ideal for large fact tables (typically >60 GB) to avoid data movement during joins, a 5 GB table is considered small enough that round-robin distribution avoids skew and provides adequate performance. For index type, 'Clustered Columnstore Index (CCI)' is the correct choice because it provides the best compression and query performance for analytical workloads in Synapse Analytics, enabling efficient batch processing, column pruning, and predicate pushdown - making it the default and preferred index for fact tables in data warehouses.

Concept tested. Azure Synapse Analytics dedicated SQL pool distribution strategies (Round-Robin vs Hash vs Replicated) and index types (Clustered Columnstore Index vs Clustered Index vs Heap) based on table size and workload patterns. Key rule: tables under ~60 GB benefit from Round-Robin or Replicated distribution, while Clustered Columnstore Index is the gold standard for analytical query performance.

Reference. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

No community discussion yet for this question.