Etl Interview Questions

84 real Etl questions asked in live technical interviews — each with a model answer. Updated weekly.

🎤 Auto-captured by Assisting AI during live interviews

These Etl interview questions were captured from real interviews by candidates using Assisting AI. Each links to a full model answer. For real-time help during your own interview, get Assisting AI from ₹500/day.

Explain source-to-target mapping.🟡 Medium · Conceptual · Asked 54×Can you dig deeper and explain what technologies you have used, what processes you follow, and what kind of data transformations you have dealt with on a daily basis?🟡 Medium · Behavioral · Asked 27×Can you tell me how you perform data reconciliation?🟡 Medium · Conceptual · Asked 22×Target's PI and target type. How do you troubleshoot a source-to-target mismatch issue?🟡 Medium · Debugging · Asked 11×In your current project, what is the source and what is the target? What type of data are you dealing with, and which ETL tools are you currently using? Is the source an external database or a print‑mix database? Which system is the target?🟡 Medium · Conceptual · Asked 1×Tell me about the different types of Slowly Changing Dimensions.🟡 Medium · Conceptual · Asked 1×A CSV file is around 10 GB. How would you read, transform, and load it?🟡 Medium · Conceptual · Asked 1×If a CSV file has a large number of rows, how would you process it?🟡 Medium · Conceptual · Asked 1×After performing transformation, do you check whether the destination target has all data present?🟡 Medium · Conceptual · Asked 1×Explain how to manage data handling, logging, and data reconciliation in ETL jobs.🟡 Medium · Conceptual · Asked 1×Do you handle incremental data loading instead of full data loads?🟡 Medium · Conceptual · Asked 1×Will you explain the architecture of the Enterprise ETL pipeline you have designed?🟡 Medium · Conceptual · Asked 1×Suppose you have built a data model in the gold layer, and over time the solution has expanded into multiple tables, relationships, and complex logic. An enhancement requires changes to the gold layer. How would you approach this?🟡 Medium · Conceptual · Asked 1×When performing data transformations and cleaning, do you typically handle them in separate stages or within the same pipeline?🟡 Medium · Conceptual · Asked 1×Source data is in the bronze layer. We need to implement SCD type 2 in the target layer. If data arrives daily in batch and the bronze layer is refreshed each day, what logic would you use between the bronze and target layers to achieve SCD type 2?🟡 Medium · Conceptual · Asked 1×How would you implement logic for three ordered tiers (Bronze, Silver, Gold) in a target table?🟡 Medium · Conceptual · Asked 1×Explain how to implement Change Data Capture type 2 using effective start time, end time, and current flag fields. The current flag indicates whether a record is active; the start time indicates when it became active, and the end time indicates when it became inactive. These fields are required. How would you implement this in software?🟡 Medium · Conceptual · Asked 1×In a scenario where you need to build a type 2 dimension table in Snowflake, assume you have a product table (or a sales table). How would you read and implement a type 2 table, including handling target dimension, fact table, and audit files?🟡 Medium · Conceptual · Asked 1×How do you configure a full refresh for a data pipeline?🟢 Easy · Conceptual · Asked 1×How would you configure incremental loading for five tables while the remaining five tables use full refresh?🟡 Medium · Conceptual · Asked 1×When executing a Firetron job, if it fails due to a platform issue, what steps would you consider to recover the data?🟡 Medium · Conceptual · Asked 1×When ingesting data, if the source table has no primary key, is it mandatory to externally define primary key columns in the target, or can we ingest without them?🟡 Medium · Conceptual · Asked 1×Can you explain ETL transformation?🟢 Easy · Conceptual · Asked 1×Let's say a data load has occurred but the data was loaded incorrectly. How would you identify and correct the issue?🟡 Medium · Debugging · Asked 1×Given employee ID, name, department, salary, and location, process the data in Spark and handle invalid records.🟡 Medium · Coding · Asked 1×Can you describe how you build ingestion logic and transformation, perform validation checks, leverage APIs, and trigger actions based on comparisons?🟡 Medium · Conceptual · Asked 1×In a scenario where you perform eight to nine transformations on data, write the result to a CSV file, then apply one or two more transformations before writing to a downstream table, how would you structure your Spark job to avoid unnecessary recomputation?🟡 Medium · Conceptual · Asked 1×From which source are you getting these files?🟢 Easy · Conceptual · Asked 1×In which part are you mainly focused? In the current project, what do you exactly do? Is your role extracting data or transforming data?🟡 Medium · Conceptual · Asked 1×Can you tell me about the data pipeline you are using in your current project and your role in it?🟡 Medium · Conceptual · Asked 1×Can you tell the difference between a full load and an incremental load?🟡 Medium · Conceptual · Asked 1×While performing ETL operations and transformations, how will you handle PII data?🟡 Medium · Conceptual · Asked 1×Are there multiple popular ETL tools in the market such as Informatica, Talend, AWS Glue, and Azure Data Factory? Tech giants like Amazon, Google, Microsoft, IBM, and Deloitte use these ETL products to create scalable data pipelines. Can you explain the ETL process?🟡 Medium · Conceptual · Asked 1×Explain the ETL process in data engineering. What do the steps Extract, Transform, and Load entail?🟢 Easy · Conceptual · Asked 1×Data capture, which is TDC. Only inserts, updates, deletes need to be written into Snowflake. Can you come up with a pipeline that will help us do this?🟡 Medium · System Design · Asked 1×Given the services mentioned, here's a scenario: We have data in either an RDS instance or DynamoDB. We need to load it into Snowflake at regular intervals in an incremental manner. How would you do this?🟡 Medium · System Design · Asked 1×What is the goal layer when building dimensions? Suppose we have an e-commerce company that sells products on Flipkart.🟡 Medium · Conceptual · Asked 1×What are alternatives to Glue? If redesigning a pipeline and Glue is costly, what would you pick instead?🟡 Medium · Conceptual · Asked 1×Why did you choose Glue for the project, given that there are other services available?🟢 Easy · Conceptual · Asked 1×I want to know what data engineering is all about. I come from a non‑tech background. Please explain it in two or three brief lines.🟢 Easy · Conceptual · Asked 1×What is the difference between a normal ATL pipeline and a DLT pipeline?🟡 Medium · Conceptual · Asked 1×As a data engineer, would you choose a batch job?🟢 Easy · Conceptual · Asked 1×As a data engineer, how would you retrieve complete data?🟡 Medium · Conceptual · Asked 1×Suppose a data pipeline has retrieved 8 million records and then stopped. How would you recover to retrieve the remaining 2 million records?🔴 Hard · Debugging · Asked 1×What is incremental loading?🟢 Easy · Conceptual · Asked 1×Within an ETL pipeline, how do you handle error calls?🟡 Medium · Conceptual · Asked 1×How do you handle error rows in a VGL pipeline?🟡 Medium · Conceptual · Asked 1×If you have worked on an ETL pipeline, can you explain which critical pipeline you have designed and describe its architecture?🔴 Hard · Conceptual · Asked 1×In a scenario where a file is written into S3, how would you trigger an AWS Glue job that takes the file as an input argument? What steps would you follow?🟡 Medium · Conceptual · Asked 1×Let me give a simple scenario: we have a data display that needs to load a CSV from the market into a smooth play. Can you describe the process?🟡 Medium · Conceptual · Asked 1×Can you explain any of the recent projects you have worked on that involved water extraction?🟡 Medium · Behavioral · Asked 1×Have you built managed data pipelines from start to finish in a big data ETL project? If so, describe the process.🟡 Medium · Behavioral · Asked 1×Suppose we have some records that do not match the expected schema, containing null or invalid price values. How would you separate bad records to ensure only valid data is loaded for valuation?🟡 Medium · Conceptual · Asked 1×Can you explain a PySpark transformation you personally developed? What were the input DataFrames? What type of joins did you use? Did you save the input and output as views?🟡 Medium · Coding · Asked 1×Have you worked on any cloud architecture designs, particularly for basic S3 ETL purposes?🟡 Medium · System Design · Asked 1×Do you have experience designing end-to-end pipelines in AWS?🟡 Medium · Behavioral · Asked 1×What is the difference between AWS Glue and Apache Spark?🟡 Medium · Conceptual · Asked 1×Could you explain more about the data flow from source to downstream and the technical issues involved?🟡 Medium · Conceptual · Asked 1×Provide code for SCD Type 2.🟡 Medium · Coding · Asked 1×Describe how you build an end‑to‑end data pipeline.🔴 Hard · System Design · Asked 1×In a data warehouse project, can you explain an example of a slowly changing dimension?🟡 Medium · Conceptual · Asked 1×What are the advantages of CDC?🟢 Easy · Conceptual · Asked 1×How would you design an ETL pipeline that uses AWS DMS CDC to capture change data from an Oracle database, stores the raw change data as JSON in a raw zone, and then processes it into a target system?🔴 Hard · Conceptual · Asked 1×We already have a dataset that we want to validate against the source and target. Why are we reading it again using change data capture?🟡 Medium · Conceptual · Asked 1×In a customer CRM scenario, how would you handle data that needs to be inverted or transformed from the source format first?🟡 Medium · Conceptual · Asked 1×When pulling JSON payload from an API, how do you ensure you don't retrieve the same data again? For example, if you pull data today, tomorrow you only want data that has changed on the source side.🟡 Medium · Conceptual · Asked 1×What would be an efficient approach to move data from MySQL to Redshift?🟡 Medium · System Design · Asked 1×Describe a pipeline that picks data from a MySQL database and loads incremental data into AWS Redshift based on a defined CID.🟡 Medium · System Design · Asked 1×Have you used Glue Studio?🟢 Easy · Conceptual · Asked 1×Why might data quality checks fail silently?🟡 Medium · Debugging · Asked 1×What are other options for moving data from the West region to Amazon Redshift?🟡 Medium · Conceptual · Asked 1×Mention the steps you took to move data from source to the bronze layer.🟡 Medium · Conceptual · Asked 1×How do you move data from one service to another?🟡 Medium · Conceptual · Asked 1×What techniques do you use to move data from one service to another, such as from X3 to Redshift?🟡 Medium · Conceptual · Asked 1×What is AWS Glue about? Why use it? What are its advantages?🟡 Medium · Conceptual · Asked 1×What type of resource were you using to transform raw files from bronze to silver to gold layers?🟡 Medium · Conceptual · Asked 1×What is the gold layer in a data lake architecture, and what transformations occur to it?🟡 Medium · Conceptual · Asked 1×I understand the complete pipeline again. I'm asking the same question: Is there an optimized solution to avoid dropping a completion status file to an S3 folder, and instead trigger a second Glue function from the first one?🟡 Medium · Conceptual · Asked 1×How would you find missing records in a new system, find mismatched records (amount, date, etc.), and perform row count comparison by day?🟡 Medium · Conceptual · Asked 1×These are two tables: all_system_orders and new_system_orders. Data has been transferred; how would you validate that the data has been correctly imported? Write queries to validate the data.🟡 Medium · Coding · Asked 1×Design a data pipeline.🟡 Medium · System Design · Asked 1×I have experience building end‑to‑end solutions. For example, if I have around three sources, Oracle, consider only two sources, Oracle SQL, around 60 odd tables that need to be loaded daily into the system. For example, in Azure Synapse SQL, what would be the approach to load these tables daily?🟡 Medium · Conceptual · Asked 1×Suppose you are creating ETL pipelines that perform incremental loads. You have access to an S3 bucket where files are placed at regular intervals. How would you design the pipeline so that it automatically triggers on the latest file without performing a full load?🟡 Medium · Conceptual · Asked 1×Data is loading again. How do you prevent data from being duplicated if the same file is loaded multiple times? What mechanisms can you implement to delete or ignore duplicate data after it has been loaded?🟡 Medium · Conceptual · Asked 1×

🎤 Get Etl questions answered in real-time

Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.

Get Assisting AI — Starts at ₹500

Browse Other Topics