Deloitte ETL Developer Interview Questions and Answers

Q1. What is the SQL query using PIVOT to transform the given input table into the expected output?
Ans. 
Using PIVOT in SQL to transform input table into expected output
• Use the PIVOT keyword followed by the aggregation function and column to pivot on

• Specify the values to pivot on as columns in the output table

• Include the FOR clause to specify the values to pivot on

• Example: SELECT * FROM input_table PIVOT (SUM(value) FOR category IN ('A', 'B', 'C')) AS output_table
Answered by AI
Add your answer
Q2. What is the process flow for implementing an incremental load?
Ans. 
Incremental load process flow involves identifying new/updated data, extracting, transforming, and loading it into the target system.
• Identify the source data that has changed since the last load

• Extract only the new/updated data from the source system

• Transform the data as needed (e.g. applying business rules, data cleansing)

• Load the transformed data into the target system, either appending to existing data or updating e
Answered by AI
Add your answer
Q3. What are the steps involved in query optimization?
Ans. 
Query optimization involves steps to improve the performance of database queries.
• Identify slow queries using tools like query logs or profiling.

• Analyze query execution plans to understand how queries are being processed.

• Optimize queries by adding indexes, rewriting queries, or restructuring data.

• Consider factors like data distribution, join types, and query complexity.

• Test and benchmark optimized queries to ensure perf
Answered by AI
Add your answer
Q4. What is the process flow of Slowly Changing Dimension Type 2 (SCD 2), and what are the associated post commands?
Ans. 
SCD Type 2 is used to track historical changes in data by creating new records for changes.
• Identify changes in source data

• Insert new record with updated data

• Update end date of previous record

• Add post commands like updating flags or triggers

• Example: If a customer changes their address, a new record is created with the updated address while the previous record is marked as expired.
Answered by AI
Add your answer
Q5. Difference Between dimension tables and fact tables.Which one loaded firs?
Ans. 
Dimension tables store descriptive attributes while fact tables store quantitative data. Dimension tables are loaded first.
• Dimension tables contain attributes like customer name, product category, etc.

• Fact tables contain quantitative data like sales revenue, quantity sold, etc.

• Dimension tables are typically loaded first as they provide context for the quantitative data in fact tables.
Answered by AI
Add your answer

On the sql, python and other puzzles

Q1. Stored Procedure to get user input
Ans. 
Use a stored procedure to prompt user for input in ETL process.
• Create a stored procedure with parameters to accept user input.

• Use the parameters in the stored procedure to filter or manipulate data.

• Prompt the user for input values when calling the stored procedure.
Answered by AI
Add your answer
Q2. Use error handling method with Stored Procedure
Ans. 
Use TRY...CATCH block in SQL to handle errors in Stored Procedures
• Enclose the code inside a TRY block

• Use a CATCH block to handle any errors that occur

• Use RAISEERROR or THROW statement to raise custom error messages

• Use @@ERROR or @@ROWCOUNT to check for errors or affected rows
Answered by AI
Add your answer

Q1. Details about company and previous Projects
Add your answer