Tag: Data Engineering

A candidate can write a neat SQL query and still struggle when yesterday’s numbers change, a job fails halfway through, or finance asks where a total came from. That is the gap a data engineer assessment needs to explore: can this person build data that other people can trust?

You do not need an enormous take-home project to find out. A small, realistic task can lead to a much better conversation—provided you explain the business problem, leave room for questions, and look beyond the final answer.

This guide follows one example from an order feed to a reporting table. Along the way, we’ll look at the SQL, the recovery plan, and the evidence that helps a hiring team make a fair decision.

Start with the job, before you design the test

Before choosing questions, finish this sentence: “The person we hire will be responsible for…” Be specific enough that another engineer could recognise the work.

For example: “Keep the daily orders dataset accurate, including cancellations and late corrections, and help finance investigate differences.” That tells you far more about the assessment you need than a long list of technologies.

A role like this needs careful SQL, an understanding of how records change, and a sensible response when processing fails. A role building a streaming platform needs additional evidence about event timing, state, and replay. One exercise should not pretend to establish both.

Write down the essential skills and the ones someone can learn after joining. If your team uses Airflow, familiarity helps. But a candidate who understands safe retries in another scheduler may be better prepared than someone who knows every Airflow term but cannot explain why a report doubles after a rerun.

There is a useful lesson in Monzo’s published analytics-engineering interview guide. It explains the work first, then describes how candidates can show their reasoning and discuss trade-offs. Analytics engineering is not identical to every data-engineering role, but that connection between the actual job and the assessment is worth applying.

Give the candidate a problem they can understand

Imagine a retailer whose reporting team needs the current value of confirmed orders. The source system sends updates rather than a tidy final table. An order can appear more than once, and its status or amount can change.

This is a fictional assessment example. Use synthetic records and state the rules upfront: keep the highest valid version of each order; count a confirmed order’s latest amount; give a cancelled order a value of zero. All amounts below are in paise, in one currency, for the same business date.

Ask the candidate to produce two things: a current-state orders table with one row per order, and a summary with the order count, confirmed-order count, and total confirmed value. This introduces an important idea without hiding it behind jargon: what does one row represent? Data teams call this the table’s grain.

Share the format, time allowance, and evaluation criteria before the session. A 75-minute discussion could give ten minutes to clarify the brief, 25 to work through the SQL, and the remaining time to explore recovery, checks, and decisions. Treat that as a starting format, and adapt it to the role and the candidate’s access needs.

Also explain whether documentation and AI tools are allowed. If they are, ask the person to explain the result and handle a changed requirement. You are looking for understanding and ownership, not a contest in producing polished code quickly.

The path in the diagram is deliberately short. Source records arrive, questionable records are set aside for investigation, and accepted records become a useful business dataset. Each step gives you a different part of the candidate’s reasoning to discuss.

Use six records to uncover the important decisions

Here is the complete input. The version number comes from the source and increases whenever that order changes.

The final table should contain three orders. A contributes zero because its latest version is cancelled. B contributes 25,000 paise once, and C contributes its corrected value of 9,000 paise.

The summary is therefore three orders, two confirmed orders, and 34,000 paise—₹340—in confirmed order value. It is a simplified reporting measure, not a complete accounting definition of revenue.

Now ask why a simple sum of confirmed input rows is wrong. It includes A before its cancellation, counts B twice, and includes both amounts for C. The arithmetic is easy; recognising which records belong in the answer is the useful engineering work.

Then ask what happens if an old event arrives after a newer one. Arrival time alone should not overrule the source version in this example. And if two records have the same order and version but different amounts, the rules no longer identify a trustworthy winner: the candidate should raise that conflict rather than quietly pick one.

Read the SQL together, rather than grading it in isolation

Once exact duplicates have been removed and conflicting versions have been handled, a window function can rank the remaining versions for each order. The following PostgreSQL example keeps the latest one and calculates the summary.

-- Input assumption: duplicate/conflict validation is complete.
WITH ranked AS (
  SELECT order_id, event_version, status, amount_paise,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY event_version DESC
         ) AS version_rank
  FROM validated_distinct_events
)
SELECT COUNT(*) AS order_count,
       COUNT(*) FILTER (WHERE status = 'confirmed') AS confirmed_count,
       COALESCE(SUM(CASE WHEN status = 'confirmed'
                         THEN amount_paise ELSE 0 END), 0) AS confirmed_paise
FROM ranked
WHERE version_rank = 1;

In plain language, PARTITION BY puts each order’s versions together, and ORDER BY puts the newest version first. The outer query counts and sums only the first row in each group. The PostgreSQL window-function tutorial explains this behaviour in more detail.

The query is only one part of the solution. Ask the candidate to walk from each input record to its effect on the final total. Someone who explains why C’s earlier amount disappears has shown more than someone who merely recognises ROW_NUMBER().

Keep the business assumptions visible too. If the next requirement introduces refunds, multiple currencies, or reporting across dates, the brief needs new rules. Recognising that the question has changed is a strength, not an excuse for failing to finish.

Next, make the pipeline fail

The total is correct. Now introduce a realistic interruption: the job writes its result, but the scheduler loses the success message and runs it again. Would the same order value appear twice?

This is where idempotency becomes useful. It means repeating the same operation does not keep changing the intended result. The candidate might propose replacing a defined reporting partition or updating records by a stable key, depending on the storage system.

Airflow’s best-practice guidance makes the same practical point: retries should produce a consistent result, and inputs should refer to a specific slice of data. If a retry reads whatever happens to be “latest,” it may no longer be repeating the same job.

Ask the candidate to describe what readers see while the new output is being built. Ideally, there is a clear boundary between the last accepted result and the next accepted result. A half-written table should not quietly become the finance team’s morning report.

Use the recovery diagram to slow the conversation down. Which input version are we processing? What is checked before publication? What tells us the result was committed? The exact implementation can differ, but those questions should have clear answers.

Finally, add a correction for yesterday’s orders. Ask how the team will revisit affected data without accidentally rebuilding unrelated periods or hiding the change from its users. A good answer connects the technical reprocessing plan to the person who relies on the report.

Ask how the team will know when the numbers are wrong

A green job status tells you that code ran. It does not tell you that the numbers make sense. For this example, the candidate should propose checks tied to the promised output.

Useful checks include one row per order, a valid order ID, an allowed status, and agreement between the accepted input and the published summary. Freshness matters as well: a technically correct report that is two days late may still be unusable.

dbt’s data-test documentation describes tests as assertions about data, including uniqueness, missing values, allowed values, and relationships. The hiring question is what the engineer would do when one of those assertions fails.

Would they stop publication, isolate affected records, or alert an owner while allowing the rest through? There is no helpful answer without considering the impact. A conflicting order value and a missing optional comment deserve different treatment.

Ask for the alert message they would send. “Pipeline failed” leaves the next person guessing. “Orders for 17 October were not published because two versions conflict; the previous accepted report is still available” gives the recipient a starting point.

Turn the discussion into a fair hiring decision

Without shared criteria, interviewers can leave the same session with very different impressions. A scorecard helps them discuss what the candidate demonstrated, especially when one person values quick coding and another values careful questions.

The weights below are an example you can adapt. Score each area from zero to four, then multiply that fraction by its weight; three out of four in a 20-point area gives 15 points. They are a discussion aid, not a hiring-performance benchmark.

Have interviewers write their notes before comparing scores. “Identified the conflicting source versions and proposed an investigation path” is useful evidence. “Seemed senior” is much harder for a hiring panel to evaluate.

Agree the essential requirements before interviewing. A high overall total should not hide a gap in a responsibility the person will own immediately. Equally, do not reject someone for needing a reference page for syntax when their reasoning and result are sound.

Match the result to the responsibility you can offer

A junior engineer may need a clear task and a reviewer. A senior engineer may need to resolve ownership, challenge an unclear data definition, and plan a migration without disrupting consumers. Assess the responsibility you are actually hiring for.

For a contractor, carry the assessment into a first-deliverable brief: reproduce the orders dataset, demonstrate a safe rerun, add an agreed validation, and leave recovery notes another engineer can follow. This gives the engagement a practical starting point.

If you are considering IT staff augmentation, share that brief alongside the job description. If you need a team to own the wider implementation, AppZime’s software development services are a separate scoping conversation.

The next step is small: choose one dataset your business depends on and create a synthetic version of its awkward edge cases. If you need help defining the role around that work, talk to AppZime about the responsibility and expected outcome. That is a more useful place to start than another list of interview questions.

Sources reviewed on 18 October 2026. The dataset, session format, and scoring weights are illustrative.