Step2Study
Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureHard

A company is designing a data warehouse solution using Azure Synapse Analytics. They need to store historical sales data in a relational format that is optimized for analytical queries across petabytes of data. Which type of table distribution should they choose for their fact tables in Azure Synapse Analytics dedicated SQL pool?

  1. AHash-distributed
  2. BRound-robin
  3. CReplicated
  4. DHeap
Show answer & explanation

Correct answer: A. Hash-distributed

For large fact tables in Azure Synapse Analytics dedicated SQL pool, Hash-distributed tables are typically the most performant choice for analytical queries. By distributing rows based on a hash function of one or more columns (often a joining column), it allows for co-located joins and efficient parallel query execution, which is crucial for petabyte-scale data warehousing.

Why the other options are wrong

  • B. Round-robin distributes data evenly but randomly, which is good for initial loading or when no explicit join key exists, but it often leads to data movement during joins, impacting performance on large fact tables.
  • C. Replicated tables copy the entire table to each compute node, suitable for small dimension tables (under 2GB compressed) to avoid data movement, but inefficient for large fact tables due to storage and network overhead.
  • D. Heap tables are suitable for staging data or small tables without a clustered index but are not optimized for analytical queries on large fact tables.

Synapse Dedicated SQL Pool Table Distribution

Azure Synapse Analytics dedicated SQL pool distributes table data across compute nodes to optimize query performance, with different strategies for different table types.

  • Round-robin: even but random distribution
  • Hash-distributed: distributes based on column value, good for large fact tables
  • Replicated: copies entire table to each node, good for small dimension tables
  • Optimized for analytical workloads

Memory trick: Synapse tables: round, hash, or replicate.

Practice this question Study as flashcard All questions
Next question

More Describe how to work with relational data on Azure questions