Adaface Sample ETL Questions

A data engineer is tasked with merging and transforming data from two sources for a business analytics report. Source 1 is a SQL database 'Employee' with fields EmployeeID (int), Name (varchar), DepartmentID (int), and JoinDate (date). Source 2 is a CSV file 'Department' with fields DepartmentID (int), DepartmentName (varchar), and Budget (float). The objective is to create a summary table that lists EmployeeID, Name, DepartmentName, and YearsInCompany. The YearsInCompany should be calculated based on the JoinDate and the current date, rounded down to the nearest whole number. Consider the following initial SQL query:

Which of the following modifications ensures accurate data transformation as per the requirements?

A: Change FLOOR to CEILING in the calculation of YearsInCompany.
            B: Add WHERE e.JoinDate IS NOT NULL before the JOIN clause.
            C: Replace JOIN with LEFT JOIN and use COALESCE(d.DepartmentName, 'Unknown').
            D: Change the YearsInCompany calculation to YEAR(CURRENT_DATE) - YEAR(e.JoinDate).
            E: Use DATEDIFF(YEAR, e.JoinDate, CURRENT_DATE) for YearsInCompany calculation.

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

In an ETL process designed for a retail company, a complex SQL transformation is applied to the 'Sales' table. The 'Sales' table has fields SaleID, ProductID, Quantity, SaleDate, and Price. The goal is to generate a report that shows the total sales amount and average sale amount per product, aggregated monthly. The following SQL code snippet is used in the transformation step:

What specific function does this SQL code perform in the context of the ETL process, and how does it contribute to the reporting goal?

A: The code calculates the total and average sales amount for each product annually.
            B: It aggregates sales data by month and product, computing total and average sales amounts.
            C: This query generates a daily breakdown of sales, both total and average, for each product.
            D: The code is designed to identify the best-selling products on a monthly basis by sales amount.
            E: It calculates the overall sales and average price per product, without considering the time dimension.

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