Exam DP-201 topic 1 question 36 discussion

Actual exam question from Microsoft's DP-201

Question #: 36
Topic #: 1

HOTSPOT -
You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytic in a star schema.
You plan to have a fact table for website visits. The table will be approximately 5 GB.
You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance.
What should you recommend? To answer, select the appropriate options in the answer area
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

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

by Pairon at March 23, 2021, 11:21 a.m.

Comments

Submit Cancel

dbdev

Highly Voted 4 years ago

The answer is straightforward and correct. Even, there is no need to put a comment here.

upvoted 8 times

...

aksoumi

Most Recent 4 years, 2 months ago

How should we decide if it is clustered or a non clustered index? Usually if there are over a million rows (or more than 60 million rows) we use clustered index, but these aren't mentioned in question.

upvoted 1 times

Apox

4 years, 1 month ago

Clustered indexes and non-clustered indexes only outperform clustered columnstore indexes when a single row needs to be quickly retrieved with extreme speed. So, for highly selective filters this is the right choice. Since this is a star schema where filters may vary, clustered columnstore indexes are the better choice as this generally provides the best overall query performance (and is best for large tables).

upvoted 8 times