Exam DP-203 topic 1 question 9 discussion

The answer is correct. The Dims are under 2gb so no point in use hash. Common distribution methods for tables: The table category often determines which option to choose for distributing the table. Table category Recommended distribution option Fact -Use hash-distribution with clustered columnstore index. Performance improves when two hash tables are joined on the same distribution column. Dimension - Use replicated for smaller tables. If tables are too large to store on each Compute node, use hash-distributed. Staging - Use round-robin for the staging table. The load with CTAS is fast. Once the data is in the staging table, use INSERT...SELECT to move the data to production tables. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview#common-distribution-methods-for-tables

Took the exam today, this question came out. Ans: All the Dim tables --> Replicated Fact Tables --> Hash Distributed

Replicated Replicated Replicated Hash Dist

Dim_customers,Dim_Employees,Dim_time are replicated and Fact_DailyBooking table is hash distributed

The answer is correct.

this explains better https://www.youtube.com/watch?v=seiJ2xpW0h8

Dear Community, I would like to express my heartfelt gratitude for the thoughtful mock questions that have been shared. Your generosity in providing these valuable resources has been immensely helpful. As we engage in discussions and learn together, I am reminded of the strength and camaraderie that exists within our community. To everyone who has contributed, whether by creating questions, participating in discussions, or simply offering encouragement, thank you. Your collective efforts make this community a vibrant and supportive place for learning and growth. Let us continue to share knowledge, support one another, and celebrate our shared passion for learning

All Dim Tables, if less than 2 GB, will be REPLICATED. For Fact Table, if major than 10 GB, will be HASH DIST.

REplicated,REplicated,REplicated and hash

Replicated / Replicated / Replicated / Hash

Ans: All the Dim tables --> Replicated Fact Tables --> Hash Distributed is correct

https://learn.microsoft.com/en-us/training/modules/design-multidimensional-schema-to-optimize-analytical-workloads/3-create-tables

https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-best-practices#batch-jobs-structure Dimension tables: Replicated distribution since data movement is absent here. Fact Table: Hash distribution to improve performance of moving data.

Dim_*: Replicated Since dimension tables are less likely to get frequent updates and are usually smaller in size, replicating them across all partitions makes logical sense. Also, Tables less than 2gb size should be replicated. Fact_*: Hash Distributed Since Fact tables are huge and have frequent insert/delete/updates going on, hash distribution is the perfect distribution candidate. Also, Tables greater than 2gb size should be hash distributed.

correct

Just a better link that explains the decisions. Also watch the video, it's cool. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/massively-parallel-processing-mpp-architecture

The answer is correct.