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 during queries.
Which type of table should you use?
Click on the arrows to vote for the correct answer
A. B. C. D.D
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.
https://blogs.msdn.microsoft.com/sqlcat/2015/08/11/choosing-hash-distributed-table-vs-round-robin-distributed-table-in-azure-sql-dw-service/In Azure Synapse Analytics, there are four types of tables that can be used for storing data, namely heap, hash-distributed, round-robin, and replicated. Each type has its own advantages and disadvantages, and the most appropriate table type depends on the specific use case and requirements.
In this scenario, the requirement is to create a dimension table that is less than 1 GB in size and provides the fastest query time while minimizing data movement during queries. Based on these requirements, the most appropriate table type would be a replicated table (option C).
A replicated table is a type of table in which the data is replicated across all nodes in the distributed database. This means that each node has a complete copy of the table, and queries can be executed on any node without the need for data movement. Replicated tables are typically used for small dimension tables, reference tables, or lookup tables that are frequently accessed by queries. Because the data is replicated across all nodes, replicated tables provide fast query performance and low latency.
On the other hand, a hash-distributed table (option A) is a type of table in which the data is partitioned across nodes based on a hash function applied to a specified column. This ensures that related rows are stored together, which can improve query performance by reducing the need for data movement. However, hash-distributed tables can be more complex to manage and can result in higher data movement during queries compared to replicated tables.
A heap table (option B) is a type of table in which the data is stored in an unordered heap structure. Heap tables do not have any defined order or organization, which can result in slow query performance and high data movement during queries.
A round-robin table (option D) is a type of table in which the data is distributed across nodes in a round-robin fashion. This means that each row is assigned to a different node, which can result in even distribution of data across nodes. However, round-robin tables can result in high data movement during queries and may not provide optimal query performance compared to replicated or hash-distributed tables.
In summary, based on the given requirements, a replicated table is the most appropriate type of table to use for a dimension table that is less than 1 GB in size. Replicated tables provide fast query performance and low latency while minimizing data movement during queries.