nerdexam
Microsoft

70-467 · Question #10

You are designing a partitioning strategy for a large fact table in a Manufacturing data warehouse. Tens of millions of new inventory fact records are loaded into the data warehouse weekly, outside…

The correct answer is B. Partition the inventory fact table by day, and compress each partition. Partitioning the inventory fact table by day with compression aligns partition boundaries with the primary query granularity and reduces I/O for a large, frequently loaded dataset.

Design a data warehousing solution

Question

You are designing a partitioning strategy for a large fact table in a Manufacturing data warehouse. Tens of millions of new inventory fact records are loaded into the data warehouse weekly, outside of business hours. Most queries against the database are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.

Options

  • APartition the inventory fact table by month, and compress each partition.
  • BPartition the inventory fact table by day, and compress each partition.
  • CPartition the inventory fact table by year.
  • DPartition the inventory fact table by week.

How the community answered

(24 responses)
  • A
    8% (2)
  • B
    79% (19)
  • C
    4% (1)
  • D
    8% (2)

Why each option

Partitioning the inventory fact table by day with compression aligns partition boundaries with the primary query granularity and reduces I/O for a large, frequently loaded dataset.

APartition the inventory fact table by month, and compress each partition.

Monthly partitions are too coarse for frequent day-level queries because the engine cannot eliminate sub-month data within a partition, forcing scans across an entire month's rows when only a single day is requested.

BPartition the inventory fact table by day, and compress each partition.Correct

Partitioning by day directly matches the most frequent query pattern - day-level data access - enabling the query engine to eliminate irrelevant partitions and scan only the targeted day's data. Enabling compression on each partition reduces the physical storage and I/O cost associated with tens of millions of weekly records, benefiting both report queries and cube processing jobs.

CPartition the inventory fact table by year.

Yearly partitions provide no useful partition elimination for day-level or month-level queries, causing the engine to scan an entire year's data for most workloads and offering no I/O benefit.

DPartition the inventory fact table by week.

Weekly partitions do not align with either the primary day-level or secondary month-level query patterns, preventing effective partition elimination and leaving the most frequent access granularity unoptimized.

Concept tested: SQL Server fact table partitioning granularity and compression

Source: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes

Topics

#table partitioning#fact table#data compression#query performance

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice