Answer: month

## Partitioning Strategy Analysis When designing a fact table in Azure Synapse Analytics dedicated SQL pool, partitioning is a critical performance optimization technique that physically separates data into distinct segments. The optimal partitioning column should align with the most frequent and performance-sensitive query patterns. ### Query Pattern Analysis: 1. **Show order counts by week** - Requires grouping by week, but doesn't necessarily benefit from partitioning by week 2. **Calculate sales totals by region** - Requires grouping by region, but region-based partitioning would create too many small partitions 3. **Calculate sales totals by product** - Requires grouping by product, but product-based partitioning would also create many small partitions 4. **Find all the orders from a given month** - This is a range-based query that directly benefits from partition elimination ### Why Month Partitioning is Optimal: - **Partition Elimination**: When querying for orders from a specific month, the query engine can eliminate all other monthly partitions, dramatically reducing I/O and improving performance - **Balanced Partition Size**: Monthly partitions typically provide a good balance between partition count and data distribution across three years of data (36 partitions) - **Query Performance**: The "find all orders from a given month" operation is the most partition-sensitive query, as it can leverage partition elimination directly - **Maintenance Efficiency**: Monthly partitions align well with common data maintenance operations like archiving old data ### Why Other Options Are Less Suitable: - **Product**: Would create too many partitions (potentially thousands), leading to partition management overhead and reduced query performance - **Week**: Creates 156 partitions for three years, which is excessive and can lead to the "many small files" problem - **Region**: Typically has low cardinality, creating too few partitions to be effective for large datasets ### Best Practice Consideration: In Azure Synapse Analytics, partitioning by time-based columns (like month) is generally recommended for fact tables because it aligns with common business reporting cycles and enables efficient data management operations while providing excellent query performance for time-range queries.

Author: LeetQuiz Editorial Team