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.)
A.
Partition the destination fact table by date. Insert new data directly into the fact table and delete old data
directly from the fact table.
B.
Partition the destination fact table by date. Use partition switching and staging tables both to remove old
data and to load new data.
C.
Partition the destination fact table by customer. Use partition switching both to remove old data and to load
new data into each partition.
D.
Partition the destination fact table by date. Use partition switching and a staging table to remove old data.
Insert new data directly into the fact table.