Five question families in UK mid-level data engineering interviews

Mid-level data engineering interviews in the UK are more predictable than they feel. Most questions fall into five families. Companies phrase and weight them differently, but the underlying ask is the same.

This post covers each family, how to prepare, and what a strong answer looks like. The sample questions are typical, not transcripts from any particular company.

1. SQL on the spot

Almost every process includes live SQL, either in a shared editor with a real database or in a document where you talk through the query. Either way, the interviewer wants to see that SQL is a tool you reach for without hesitation.

How to prepare

• Practise writing queries by hand, without autocomplete, for a couple of weeks before interviews.
• Be fast and correct on joins, GROUP BY with HAVING, window functions, CTEs and date handling.
• Be able to explain what happens to row counts when joins fan out.
• Know how you would find duplicates, the latest record per key, and gaps in a sequence.

Sample question

"Given a table of orders with customer_id, order_date and amount, return each customer's most recent order and the total they have spent."

Strong-answer outline

Talk as you write. State your assumptions. Then build the query in steps.

WITH ranked AS (
  SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_spent
  FROM orders
)
SELECT customer_id, order_date, amount, total_spent
FROM ranked
WHERE rn = 1;

Then mention the edge cases unprompted: ties on order_date, null amounts, and whether cancelled orders count. That separates a mid-level answer from a junior one.

2. Data modelling

Modelling questions test whether you can turn a business situation into tables that are easy to query and maintain. They are usually conversational.

How to prepare

• Be fluent in the basics of dimensional modelling: facts, dimensions, grain, surrogate keys, slowly changing dimensions.
• Understand when a wide denormalised table is fine and when it is not.
• Know the trade-offs between a star schema and a more normalised model in a modern warehouse.
• Have a view on how modelling changes with tools like dbt, where models are layered and tested.

Sample question

"We run a subscription product. Design the tables an analytics team would use to report on monthly active subscribers, churn and revenue."

Strong-answer outline

Start with the grain. Say what one row of the fact table means. For a subscription product, a daily or monthly subscription-status fact per customer is a sensible start.

Then name the dimensions: customer, plan, date. Explain how you would handle a customer changing plan and whether that history matters. If it does, that is a type 2 slowly changing dimension, and you should say so.

Finish with how the model answers the three questions, and what you would clarify with the analytics team before building.

3. Pipeline design and failure handling

This family separates engineers who have run things in production from those who have only built them. Interviewers want to hear how you think about reliability, idempotency, monitoring and recovery.

How to prepare

• Pick two pipelines you know well and be able to draw them from source to consumer.
• For each, know what happens when a source is late, a schema changes, a job runs twice, or a job fails halfway.
• Have clear definitions for idempotency, backfill and late-arriving data.
• Be comfortable talking about orchestration: dependencies, retries, alerting, SLAs.

Sample question

"Design a daily pipeline that loads supplier files into our warehouse. The files sometimes arrive late and are occasionally resent with corrections. How do you make it reliable?"

Strong-answer outline

Lay out the stages: landing, validation, staging, transformation, publish. Then address the problems in the question.

• Late files: do not couple the schedule to an assumed arrival time. Use a file-arrival sensor, and alert if the file has not appeared by the deadline.
• Resent files: make the load idempotent. Partition by business date and replace the partition, or merge on a stable key. Keep the raw file so you can reprocess.
• Validation: check row counts, schema and key uniqueness before anything reaches the warehouse. Quarantine bad files rather than failing silently.
• Observability: log every run, record row counts in and out, and alert on deviations.

Mention what you would do on day one versus later. That shows judgement, not just knowledge.

4. The past-project deep dive

At some point an interviewer will pick a project from your CV and dig in for twenty minutes or more. They want to know how much of it was really yours, what decisions you made, and what you learned.

How to prepare

• Choose two or three projects from your CV and prepare each in depth: the business problem, the architecture, your contribution, the hard parts, and what you would do differently.
• Be honest about the boundaries of your role. "I built the ingestion layer; a colleague owned the modelling" is a fine answer. Claiming the whole thing and then struggling on detail is not.
• Know the numbers on your CV and how you measured them.

Sample question

"Tell me about the pipeline rebuild on your CV. What was the original problem and what did you change?"

Strong-answer outline

Give the context in two sentences. Then the problem as the business experienced it. Then the decision points: what options you considered, which you chose, and why.

Spend the most time on the hardest part. Interviewers remember specifics. Explain one thing that went wrong and how you handled it.

Close with the result and one thing you would change with hindsight. A candidate who cannot name anything to improve usually has not reflected on the work.

5. Behavioural STAR questions

UK employers, particularly larger organisations and the public sector, lean heavily on competency questions. They want examples they can score. The STAR format (Situation, Task, Action, Result) exists because it makes scoring easy.

How to prepare

• Write down six to eight stories from your work. Cover conflict, failure, pressure, influencing without authority, learning quickly, and improving a process.
• For each, write a STAR version you can deliver in about two minutes.
• Keep the Action section the longest. That is the part about you.
• Practise saying them out loud.

Sample question

"Tell me about a time you disagreed with a technical decision. What did you do?"

Strong-answer outline

Situation: a team was choosing between two approaches to a shared transformation layer.
Task: I thought the chosen option would make testing harder and wanted to raise it without derailing the plan.
Action: I wrote up the concern with a small worked example, shared it before the next design review, and proposed trying both on one model. I made clear I would support whichever was chosen.
Result: the team adjusted the approach where testing mattered most and kept the original elsewhere. The compromise held up.

Note the tone. The strong answer is about raising a concern well and accepting the outcome, not about winning.

Putting it together

With a week before an interview, split it roughly like this: two days on SQL, one on modelling, one on pipeline design, one on your own projects, and one on STAR stories. Then rest.

You will not be asked everything. But if you have prepared across all five families, nothing in the room will surprise you.

Next step

Pick one sample question above and answer it out loud, timed. The gap between what you know and what you can say clearly is the thing to close first.