Microsoft
70-467 · Question #21
70-467 Question #21: Real Exam Question with Answer & Explanation
The correct answer is D: Partition the fact table by day, and compress each partition.. Partitioning by day aligns with the most frequent query granularity, and compression reduces I/O overhead for the large weekly data loads.
Question
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level. You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- APartition the fact table by month, and compress each partition.
- BPartition the fact table by week.
- CPartition the fact table by year.
- DPartition the fact table by day, and compress each partition.
Explanation
Partitioning by day aligns with the most frequent query granularity, and compression reduces I/O overhead for the large weekly data loads.
Common mistakes.
- A. Monthly partitioning is too coarse for day-level queries because partition elimination cannot narrow a scan to a single day without reading the entire month's partition.
- B. Weekly partitioning does not align with either the day-level or month-level query patterns described, so partition elimination is inefficient for both common access types.
- C. Yearly partitioning is far too coarse and provides almost no partition elimination benefit for day-level or month-level queries, forcing near-full partition scans for every report.
Concept tested. SQL Server fact table partitioning granularity for data warehouse query optimization
Reference. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
Community Discussion
No community discussion yet for this question.