70-463 · Question #129
You are designing an extract, transform, load (ETL) process for loading data from a SQL Server 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 B. Partition the destination fact table by date. Although all answer choices in this question are worded identically (a known exam formatting issue), the correct strategy is to partition the destination fact table by date. Date-based partitioning satisfies all requirements: (1) New data can be loaded fastest via partition…
Question
You are designing an extract, transform, load (ETL) process for loading data from a SQL Server 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.
- Ensure that data loads correctly.
- 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 destination fact table by date.
- BPartition the destination fact table by date.
- CPartition the destination fact table by customer.
- DPartition the destination fact table by date.
How the community answered
(38 responses)- A16% (6)
- B68% (26)
- C11% (4)
- D5% (2)
Explanation
Although all answer choices in this question are worded identically (a known exam formatting issue), the correct strategy is to partition the destination fact table by date. Date-based partitioning satisfies all requirements: (1) New data can be loaded fastest via partition switching, which is a metadata-only operation. (2) Data older than 36 months can be removed efficiently by switching out the oldest partition rather than running row-level DELETEs. (3) Partition switching minimizes record locking since it operates at the metadata level. (4) Switching partitions in/out generates minimal transaction log activity compared to bulk INSERT or DELETE operations. Partitioning by customer (option C) does not address the time-based archival requirement.
Topics
Community Discussion
No community discussion yet for this question.