ETL Developer Interview Questions UK: 8 Real Questions and What Interviewers Want

Before your ETL Developer interview, it helps to see the questions from the interviewer's side. We have picked eight from our set of 12 and added the reason each one gets asked.

Eight ETL Developer interview questions, and what the interviewer is thinking

1. In SSIS, what is the difference between blocking, semi-blocking and non-blocking transformations, and how do Lookup cache modes affect performance?

Why they ask: SSIS is still widely used in UK organisations, and this separates people who only drag components from those who understand the pipeline.

2. How do you find and fix a performance bottleneck in an ETL job that is running too slowly?

Why they ask: Tuning slow jobs is a core, everyday ETL responsibility.

3. What does a typical day as an ETL Developer involve?

Why they ask: They want to check you understand the combination of build, support and collaboration.

4. How do you design error handling, logging and restartability into an ETL process?

Why they ask: Production ETL must fail gracefully, so they test whether you design for failure rather than success.

5. A supplier file that should arrive at 6am has not turned up, and the downstream reports rely on it. What do you do?

Why they ask: They want to see how you handle dependencies you do not control.

6. How do you validate and cleanse data during ETL, for example handling duplicates, nulls and inconsistent formats?

Why they ask: Data quality handling is where ETL developers protect the downstream business.

7. Your overnight ETL completed successfully, but a business user reports that a number is wrong. How do you investigate?

Why they ask: Silent logic errors are more dangerous than failed jobs, so they test your investigative approach.

8. Tell me about a time you migrated ETL jobs from a legacy tool or platform to a new one.

Why they ask: Migrations are a common project and test planning, testing and stakeholder management.

A sample answer to question 1

In SSIS, what is the difference between blocking, semi-blocking and non-blocking transformations, and how do Lookup cache modes affect performance?

Non-blocking transformations, such as Derived Column or Conditional Split, process each row as it arrives and reuse buffers, so they're cheap. Semi-blocking ones like Merge Join or Union All hold some rows before releasing output. Blocking transformations, Sort and Aggregate, must read all the input before producing any, so they use lots of memory and can break the streaming flow. Wherever possible, I sort in the source query with an ORDER BY and set the IsSorted property, instead of using the Sort component. For Lookups, full cache loads the whole reference set into memory first, which is fastest for small to medium tables. Partial cache queries the database and caches matches as it goes, and no cache hits the database for every row, which is only sensible for very small, rare lookups. With a large reference table, I'd restrict columns and rows in the lookup query, or push the join into the source SQL. I'd also tune buffer sizes and check logs for buffer spooling.

Common mistake to avoid: Using the Sort component by default; Loading entire wide tables into a lookup

Want the rest?

Full model answers, key points and common mistakes for all 12 questions are on the ETL Developer interview questions page. The first five are free, no sign-up needed.