70-467 · Question #33
You are designing an extract, transform, load (ETL) process for loading data from a SQL Azure database into a large fact table in a data warehouse each day with the prior day's sales data. The ETL…
The correct answer is C. Partition the fact table by date. Partitioning the fact table by date satisfies all ETL requirements by enabling fast partition-switching loads, efficient removal of aged partitions, and minimal logging and locking.
Question
You are designing an extract, transform, load (ETL) process for loading data from a SQL Azure database into a large fact table in a data warehouse each day with the prior day's sales data. The ETL process for the fact table must meet the following requirements:
- Load new data in the shortest possible time.
- Remove data that is more than 36 months old.
- Minimize record locking.
- Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- APartition the fact table by date.
- BPartition the fact table by customer.
- CPartition the fact table by date.
- DPartition the fact table by date.
How the community answered
(31 responses)- A6% (2)
- B13% (4)
- C77% (24)
- D3% (1)
Why each option
Partitioning the fact table by date satisfies all ETL requirements by enabling fast partition-switching loads, efficient removal of aged partitions, and minimal logging and locking.
Although the text is identical to C, this choice is not the designated best answer in the context of this question's answer key.
Partitioning by customer does not isolate data by age, so removing records older than 36 months would still require scanning and deleting rows across all customer partitions rather than switching out a single date-based partition.
Date-based table partitioning allows the ETL process to use partition switching, which is a metadata-only operation that loads new daily data nearly instantaneously with minimal transaction log activity. Data older than 36 months can be eliminated by switching out and truncating entire date partitions rather than issuing row-level DELETE statements, avoiding record locking and large log writes. This directly satisfies every stated requirement: load speed, data retention enforcement, locking minimization, and log impact reduction.
Although the text is identical to C, this choice is not the designated best answer in the context of this question's answer key.
Concept tested: Date-based table partitioning for ETL efficiency
Source: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
Topics
Community Discussion
No community discussion yet for this question.