ETL Assessment Test

Jaylo is hired as Data warehouse engineer at Affflex Inc. Jaylo is tasked with designing an ETL process for loading data from SQL server database into a large fact table. Here are the specifications of the system:

1. Orders data from SQL to be stored in fact table in the warehouse each day with prior day’s order data
2. Loading new data must take as less time as possible
3. Remove data that is more then 2 years old
4. Ensure the data loads correctly
5. Minimize record locking and impact on transaction log

Which of the following should be part of Jaylo’s ETL design?

A: Partition the destination fact table by date
B: Partition the destination fact table by customer
C: Insert new data directly into fact table
D: Delete old data directly from fact table
E: Use partition switching and staging table to load new data
F: Use partition switching and staging table to remove old data

Silverman Sachs is a trading firm and deals with daily trade data for various stocks. They have the following fact table in their data warehouse:

Table: Trades
Indexes: None
Columns: TradeID, TradeDate, Open, Close, High, Low, Volume

Here are three common queries that are run on the data:

Dhavid Polomon is hired as an ETL Developer and is tasked with implementing an indexing strategy for the Trades fact table. Here are the specifications of the indexing strategy:

- All three common queries must use a columnstore index
- Minimize number of indexes
- Minimize size of indexes

Which of the following strategies should Dhavid pick:

A: Create three columnstore indexes: 
1. Containing TradeDate and Close
2. Containing TradeDate, High and Low
3. Container TradeDate and Volume

B: Create two columnstore indexes:
1. Containing TradeID, TradeDate, Volume and Close
2. Containing TradeID, TradeDate, High and Low

C: Create one columnstore index that contains TradeDate, Close, High, Low and Volume

D: Create one columnstore index that contains TradeID, Close, High, Low, Volume and Trade Date