nerdexam
Microsoft

70-467 · Question #88

You need to implement a strategy for efficiently storing sales order data in the data warehouse. What should you do?

The correct answer is C. Create daily partitions in the factOrders table. Table partitioning on a single factOrders table provides efficient storage and query performance for sales order data without the management overhead of physically separate tables.

Design a data warehousing solution

Question

You need to implement a strategy for efficiently storing sales order data in the data warehouse. What should you do?

Options

  • ASeparate the factOrders table into multiple tables, one for each month that has orders, and
  • BSeparate the factOrders table into multiple tables, one for each day that has orders, and use
  • CCreate daily partitions in the factOrders table.
  • DCreate monthly partitions in the factOrders table.

How the community answered

(45 responses)
  • A
    4% (2)
  • B
    4% (2)
  • C
    80% (36)
  • D
    11% (5)

Why each option

Table partitioning on a single factOrders table provides efficient storage and query performance for sales order data without the management overhead of physically separate tables.

ASeparate the factOrders table into multiple tables, one for each month that has orders, and

Splitting data into separate physical tables per month requires UNION ALL queries across tables, increases schema complexity, and forfeits SQL Server's built-in partition elimination optimization.

BSeparate the factOrders table into multiple tables, one for each day that has orders, and use

Separate tables per day multiplies schema objects dramatically, making maintenance and querying across date ranges far more complex than native partitioning.

CCreate daily partitions in the factOrders table.Correct

Creating daily partitions on the factOrders table uses SQL Server's built-in partitioned table feature, which stores data in discrete filegroups aligned to a partition function while presenting a single logical table to queries. Daily partitions allow partition elimination to dramatically reduce I/O for date-range queries common in sales reporting. This approach also enables efficient partition switching for fast data loads and archiving.

DCreate monthly partitions in the factOrders table.

Monthly partitions are less granular than daily partitions, which limits partition elimination efficiency for queries that filter on specific days or short date ranges typical in sales order reporting.

Concept tested: SQL Server table partitioning for data warehouse fact tables

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

Topics

#table partitioning#factOrders#partition granularity#data warehouse storage

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice