BenchMark Quizzes by MDFT Pro

Choose The Correct Distribution Type

You work for MDFT Pro, a well-known training agency that operates a comprehensive data warehouse in Azure Synapse Analytics dedicated SQL pool. Mark, the Business Intelligence Manager, has asked you to design a dimensional model for analyzing student enrollments, course completions, and trainer assignments.

The data warehouse will include multiple fact tables such as Enrollments, Assessments, and CourseCompletions, all of which need to join with a DimCourses dimension table for analysis. Mark emphasizes that query performance is critical, and he wants you to minimize data movement across compute nodes during join operations.

Which distribution type should you recommend for the DimCourses table to minimize data movement?

Choose the correct answer from the options below.

Explanations for each answer:

HASH is incorrect. HASH distribution would distribute the date dimension across nodes, requiring data movement when joining with fact tables, which increases query time.
REPLICATE is correct. REPLICATE distribution creates a copy of the date dimension on each compute node, eliminating data movement when fact tables join with it, which minimizes data movement.
ROUND_ROBIN is incorrect. ROUND_ROBIN distribution would distribute data randomly across nodes, requiring data movement during joins, which doesn't minimize data movement.
HEAP is incorrect. HEAP is a table structure type, not a distribution type. It refers to tables without a clustered index and doesn't address distribution strategy.

Learn more about Replicated Tables In Azure Synapse Analytics:

Replicated Tables In Azure Synapse

Practice Exam

Choose The Correct Distribution Type

Discuss this question on social media: