70-467 · Question #22
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. Date-based partitioning enables partition switching for near-instant loads, metadata-only archival of old data, and minimal transaction log and locking impact.
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
(45 responses)- A4% (2)
- B62% (28)
- C24% (11)
- D9% (4)
Why each option
Date-based partitioning enables partition switching for near-instant loads, metadata-only archival of old data, and minimal transaction log and locking impact.
Partitioning by date allows the ETL process to stage new daily records in a separate table and then switch that partition into the fact table as a metadata-only operation, loading data in the shortest possible time. Old partitions beyond 36 months can be switched out or truncated without scanning the full table, satisfying the archival requirement with minimal locking. Partition-level bulk operations also produce far less transaction log activity than row-by-row inserts or full-table deletes.
Partitioning by customer provides no natural time boundary, making it impossible to efficiently identify and remove records older than 36 months, and it does not align with the daily date-driven ETL loading pattern.
Concept tested: ETL partition switching for date-partitioned data warehouse fact table loading
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.