ETL Developer Interview Questions and Answers

Questions and model answers

9 questions you are likely to hear.

1. 1

   What is the difference between ETL and ELT?

   Why they ask: This tests understanding of modern data architecture choices.

   Sample answer: In ETL, data is extracted, transformed in a separate engine like Informatica or SSIS, and then loaded into the warehouse. In ELT, raw data is loaded into the target first, often a cloud warehouse like Snowflake, BigQuery or Synapse, and transformed there with SQL, often using dbt. ELT uses the warehouse's scalable compute and keeps raw data available for reprocessing. ETL still makes sense when data must be cleansed or masked before landing, or when the target has limited compute. In my last project we moved from Informatica ETL to ADF with ELT in Snowflake, which simplified many transformations.

2. 2

   How do you implement an incremental load?

   Why they ask: Incremental loading is core to efficient ETL.

   Sample answer: I use a watermark, such as a last modified timestamp or an increasing ID, stored in a control table. Each run extracts rows changed since the last successful watermark, with a small overlap to catch late commits, then loads them into staging and applies a MERGE or update strategy into the target. The watermark is updated only after a successful load, so failed runs simply retry. When sources have no reliable timestamp, I use CDC from the database log or compare hashes of rows. For a sales table with 50 million rows, incremental loading cut the run from hours to about 15 minutes.

3. 3

   How would you implement SCD Type 2 in your ETL tool?

   Why they ask: SCD Type 2 is a standard data warehouse pattern tested in most ETL interviews.

   Sample answer: In Informatica, I look up the current dimension row by business key and compare tracked attributes, usually with a hash or checksum. A Router splits rows into new records, changed records and unchanged ones. New records are inserted with a new surrogate key, start date and current flag. For changed records, an Update Strategy expires the old row by setting the end date and current flag to false, and inserts a new version. Unchanged rows are dropped. In SSIS I use a similar design with a Lookup and Conditional Split rather than the built-in SCD wizard, which performs poorly on large dimensions.

4. 4

   How do you handle bad or rejected records?

   Why they ask: Error handling determines whether pipelines are trustworthy.

   Sample answer: I validate data at staging against rules like mandatory fields, data types, valid codes and referential integrity. Records that fail go to an error table with the error reason, source file or batch ID and timestamp, rather than failing the whole load, unless the error rate passes a threshold, in which case the job fails and alerts the team. Business users get a daily summary of rejects to fix at the source. Reprocessing picks corrected records from the error table. On a healthcare project this approach kept loads running while making data issues visible to source owners.

5. 5

   A nightly ETL job failed halfway. How do you make it restartable?

   Why they ask: Restartability is critical for production support.

   Sample answer: I design jobs so each step is idempotent: loads into staging truncate and reload, and target loads use MERGE or delete and insert by batch ID, so rerunning does not create duplicates. A control table tracks the status of each step and batch, so a restart skips completed steps and resumes from the failed one. In SSIS, checkpoints can help, and in ADF I structure pipelines with dependencies and rerun from the failed activity. In one project, adding batch IDs and step tracking turned a three-hour full rerun into a 20-minute restart from the failed step.

6. 6

   How do you tune a slow ETL mapping or package?

   Why they ask: Performance tuning is a key skill for meeting load windows.

   Sample answer: First I find the bottleneck: source query, transformation or target load, using session logs or execution timings. For sources, I push filters and joins into the source SQL and ensure indexes exist. For transformations, I cache lookups properly, use sorted input for aggregators and joiners, and avoid row-by-row processing. Pushdown optimisation can move logic into the database. For targets, I use bulk loading, drop and rebuild indexes on large loads and partition sessions to run in parallel. In one Informatica workflow, these changes reduced run time from about four hours to 70 minutes.

7. 7

   How do you validate that data loaded correctly?

   Why they ask: Reconciliation builds trust with business users.

   Sample answer: I reconcile row counts between source, staging and target for each batch, accounting for rejects. I compare totals for key measures, such as sum of sales amount by day, and check for duplicate business keys and nulls in mandatory fields. These checks run automatically after each load and are logged in an audit table, with alerts on mismatches. For major releases, I also do sample record comparisons with the business. In a finance project, automated daily reconciliation caught a currency conversion issue in the first week before month-end reporting.

8. 8

   How do you manage job dependencies and scheduling?

   Why they ask: ETL environments often have hundreds of interdependent jobs.

   Sample answer: I define dependencies explicitly in the scheduler, such as Control-M, Autosys, ADF triggers or Airflow, rather than relying on fixed start times. Dimension loads run before facts, and jobs wait for source availability signals like trigger files or source completion flags. I set SLAs and alerts for long-running or late jobs. Critical paths are documented so support teams know impact when something fails. When I joined one team, jobs were time-based and often started before upstream data arrived; switching to event-based dependencies eliminated most of the morning data gaps.

9. 9

   Tell me about an ETL improvement you delivered.

   Why they ask: This shows ownership and measurable impact.

   Sample answer: At an insurance client, the nightly policy data warehouse load took around nine hours and frequently missed the 6 AM deadline. I analysed run times and found several full loads of large tables and uncached lookups. I converted the five biggest loads to incremental with watermarks, added cached lookups, enabled partitioning and moved some heavy joins to the source database. I also added restartability with batch control tables. The load window dropped to about three hours, and missed SLAs became rare, which business teams noticed immediately in their morning reports.

Keep going.

• Data Engineer Interview Questions and Answers
• SQL Developer Interview Questions and Answers
• Data engineering job support
• SQL Job Support Online for Developers and Analysts
• Snowflake Job Support Online for Data Engineers
• AI mock interviews