Search test library by skills or roles
⌘ K

Adaface Sample ETL Questions

Here are some sample ETL questions from our premium questions library (10273 non-googleable questions).

Skills

🧐 Question

Medium

Data Merging
Data Merging
Conditional Logic
Solve
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:
 image
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.

Medium

Data Updates
Staging
Data Warehouse
Solve
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

Medium

SQL in ETL Process
SQL Code Interpretation
Data Transformation
SQL Functions
Solve
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:
 image
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.

Medium

Trade Index
Index
Solve
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:
 image
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
🧐 Question🔧 Skill

Medium

Data Merging
Data Merging
Conditional Logic

2 mins

ETL
Solve

Medium

Data Updates
Staging
Data Warehouse

2 mins

ETL
Solve

Medium

SQL in ETL Process
SQL Code Interpretation
Data Transformation
SQL Functions

3 mins

ETL
Solve

Medium

Trade Index
Index

3 mins

ETL
Solve
🧐 Question🔧 Skill💪 Difficulty⌛ Time
Data Merging
Data Merging
Conditional Logic
ETL
Medium2 mins
Solve
Data Updates
Staging
Data Warehouse
ETL
Medium2 mins
Solve
SQL in ETL Process
SQL Code Interpretation
Data Transformation
SQL Functions
ETL
Medium3 mins
Solve
Trade Index
Index
ETL
Medium3 mins
Solve

Trusted by recruitment teams in enterprises globally

Amazon Morgan Stanley Vodafone United Nations HCL PayPal Bosch WeWork Optimum Solutions Deloitte Microsoft NCS Doubtnut Sokrati J&T Express Capegemini

We evaluated several of their competitors and found Adaface to be the most compelling. Great library of questions that are designed to test for fit rather than memorization of algorithms.


Swayam Narain, CTO, Affable

hashtag image heart icon Swayam
customers across world
Join 1500+ companies in 80+ countries.
Try the most candidate friendly skills assessment tool today.
g2 badges
Ready to streamline your recruitment efforts with Adaface?
Ready to streamline your recruitment efforts with Adaface?
logo
40 min tests.
No trick questions.
Accurate shortlisting.
ada
Ada
● Online
Previous
Score: NA
Next
✖️