Which technology should you use?

You implement an Azure SQL Data Warehouse instance.

You plan to migrate the largest fact table to Azure Synapse Analytics. The table resides on Microsoft SQL Server on-premises and is in 10 terabytes (TB) in size.

Incoming queries use the primary key Sale Key column to retrieve data as displayed in the following table:

You need to distribute the large fact table across multiple nodes to optimize performance of the table.

Which technology should you use?
A . hash distributed table with clustered ColumnStore index
B . hash distributed table with clustered index
C . heap table with distribution replicate
D . round robin distributed table with clustered index
E . round robin distributed table with clustered ColumnStore index

View Answer

Answer: A

Explanation:

Hash-distributed tables improve query performance on large fact tables.

Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes.

Incorrect Answers:

D, E: Round-robin tables are useful for improving loading speed.

Reference:

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-distribute

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance