Home » Microsoft » DP-200 v.2 » Which type of table should you use?
You plan to create a dimension table in Azure Synapse Analytics that will be less than 1 GB
You need to create the table to meet the following requirements:
Provide the fastest query time.
Minimize data movement.
Which type of table should you use?
A. hash distributed
B. heap
C. replicated
D. round-robin
Correct Answer: D
Explanation/Reference:
Explanation:
Usually common dimension tables or tables that doesn’t distribute evenly are good candidates for round-robin distributed table.
Note: Dimension tables or other lookup tables in a schema can usually be stored as round-robin tables. Usually these tables connect to more than one fact tables and optimizing for one join may not be the best idea. Also usually dimension tables are smaller which can leave some distributions empty when hash distributed. Round-robin by definition guarantees a uniform data distribution.
References:
https://blogs.msdn.microsoft.com/sqlcat/2015/08/11/choosing-hash-distributed-table-vs-round-robin-distributed-table-in-azure-sql-dw-service/
Correct answer C: , since data size is under 2GB
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-guidance-for-replicated-tables
“If you already have round-robin tables, we recommend converting them to replicated tables if they meet the criteria outlined in this article. Replicated tables improve performance over round-robin tables because they eliminate the need for data movement. A round-robin table always requires data movement for joins.”