The software engineer

Think of a very good plumber who starts to work on a building’s design team. The plumber knows exactly how water moves, where pipes leak and what happens when two taps open at once. But the architects ask other questions: who lives in this room, and how do they use it? The plumber is not wrong. The plumber is looking at a different part of the building.

Backend engineers who move into data engineering are often like this. They have run services in production for years. They know retries, failures and duplicates better than most data people. What is new is the person at the end of the pipeline: an analyst or a data scientist who asks questions with SQL. That person needs tables that are easy to understand and easy to query, not only correct.

Lukas is this candidate. Watch how fast he finds the reliability problems in Tidewell’s pipeline. Then watch what happens when Esther asks about the people who will use the data.

LB

The candidate

Lukas Becker

Backend engineer, 6 years in payments, moving into data engineering

• 6 years Java backend
• Payments
• In Canada 3 years
• Vancouver

• Senior Software Engineer, Larchmont Payments (Vancouver, 2023 to now). Java 21, Spring Boot, PostgreSQL, Kafka. Card payments for about 2,000 merchants.
• Built the daily settlement reconciliation job: matches the banks’ settlement files with our ledger, about 600,000 transactions a day. Idempotent by design.
• Led the fix for duplicate merchant payouts after bank API timeouts: idempotency keys and the outbox pattern. No duplicate payouts since.
• Software Engineer, Spreewerk Zahlungen (Berlin, 2020 to 2023). Payment API, Kafka consumers, PostgreSQL.
• Built data pipelines with Kafka and Spring Batch.
• Skills: Java, Kotlin, Spring, PostgreSQL, Kafka, Docker, Kubernetes, SQL, Python (basic).

What Esther sees before the call

Esther reads the CV twice. “Idempotent by design” is a phrase she likes to see, and the payout story sounds like real production pain. She expects Lukas to be strong on re-runs and duplicates.

But the line “Built data pipelines with Kafka and Spring Batch” can mean a lot or very little. Moving data between services is not the same as building tables for analysts. Her questions:

• Does “idempotent by design” survive real questions: corrections, re-runs, files that change?
• Does he think about the people who query the data, or only about the data moving?
• Can he model history, for example which account had which meter on which day?

Terms on this page

• Idempotent: you can run the job twice, or ten times, and the result is the same as after one run.
• Idempotency key: a unique ID sent with a request, so the server can see a repeated request and not do the work twice.
• Natural key: the columns that identify a real thing, for example meter ID plus reading time.
• At-least-once delivery: every message arrives, but some may arrive more than once. The reader must handle duplicates.
• Grain: what one row in a table means. For example, “one reading of one meter at one 15-minute time”.
• Fact table and dimension table: a fact table holds events or measurements (readings). A dimension table describes things (accounts, meters). Analysts join them.
• SCD type 2 (slowly changing dimension, type 2): a table that keeps every version of a record, with a start and an end time.
• Window function: an SQL function that looks at other rows near the current row, for example the previous reading of the same meter (LAG).
• dbt: a tool that turns SQL SELECT statements into tables in the warehouse, with tests and documentation.

The interview

Minutes 0 to 5: the opening

Esther(interviewer):

Hi Lukas. I’m Esther, senior data engineer on the platform team. It’s four o’clock, my third call today, so if I’m slow, that’s me, not you. So, the plan. You tell me about a pipeline you built, and I ask many questions. Then our real problem. Then your questions. No AI tools in this round. Paper is fine, and I’ll draw on the whiteboard.

Lukas(candidate):

Laughs. Okay, no problem. I have paper here.

Minutes 5 to 25: the pipeline, then deeper

Esther(interviewer):

So, a pipeline you built or own.

Lukas(candidate):

Yes. The settlement reconciliation at Larchmont. So, every night the banks send us a file, a settlement file. It says which card payments they really paid, and the fees. We must check that it matches our ledger. Every payment in our ledger should be in the file, with the same amount.

The job starts around 3 a.m. It downloads the file, parses it, writes it to a staging table in Postgres. Then it matches by the bank reference. What doesn’t match goes to a table, and the operations team looks at it in the morning. How many rows… around 600,000 a day, I think. On Black Friday more, maybe one million.

Esther(interviewer):

Okay. And what happens if it runs twice?

Lukas(candidate):

Nothing. That was the first thing I designed. Every row has the bank reference, and there is a unique constraint on it. We insert with “on conflict do nothing”. So a second run inserts zero rows. The matching is also safe, it only updates the status.

Esther(interviewer):

And if the bank sends a corrected file? Same day, same references, different amounts.

Lukas(candidate):

Pause. Then “do nothing” keeps the old amounts. Hmm. That’s not good. The banks send corrections as a separate file type, adjustments, so in practice we don’t see it. But if a bank sends the same file again with new amounts, we keep the wrong ones. Honestly, I did not think about that.

How Esther goes deeper

Lukas’s first answer is correct for the simple case: a re-run of the same file. Esther changes one thing: the second file is different. “Do nothing on conflict” is idempotent, but it also ignores corrections. An upsert (update on conflict) handles both. Lukas finds the gap himself in a few seconds, which is what Esther hoped to see.

Esther(interviewer):

That’s fine. What was the worst incident with this job?

Lukas(candidate):

The format change. One bank added a new column in the file, in the middle. Without telling us. Our parser read by position, so after the new column, every value was one column to the right. The fee went into the amount. And we found it… not fast. The job was green. Two days later the operations team said, “We have 40,000 mismatches, something is wrong.” After that, I have added two checks. The parser checks the header names, not the position. And the file has a last line with the total count and total amount. We compare it. If it’s different, the job fails, loud.

Why this works

This is a strong incident story. The job “succeeded” and produced wrong data, which is the most dangerous kind of failure in data work. His fix checks the data itself, not only the code: header names, and control totals from the file. Tidewell’s meter vendor also changes its export format without warning, so this experience is directly useful.

Esther(interviewer):

Who uses the reconciliation results? Besides operations.

Lukas(candidate):

Finance, sometimes. They ask, “How many mismatches per bank last quarter?” Then I write a query and send them a CSV. Maybe once a month, it’s not much work.

What the interviewer is checking

Esther’s last question looks small, but it is about the role. At Tidewell, analysts and the forecast team query the data themselves, every day. A person who writes a CSV by hand for each question has not yet had to design tables for other people. Esther writes “analytics users?” in her notes and moves on.

Minutes 25 to 50: the Tidewell problem

Esther(interviewer):

Okay, now our real problem. Our meters send a reading every 15 minutes. Sometimes a meter loses network and the readings come late, up to three days late. Our nightly job reads by arrival date. Last month, after a failed run, we re-ran the job and one customer was billed twice. And the forecast team says their history has gaps. How would you design the meter-readings pipeline so this does not happen again?

Lukas(candidate):

Okay. First question for me is: what happens if it runs twice?

Esther(interviewer):

Laughs. That’s my question. You took it.

Lukas(candidate):

Sorry! So, the re-run appended the same readings again. Every step must be safe to run again. A reading has a natural key: meter ID and the reading time. Not the arrival time. If every write uses this key, a second run cannot create a second reading.

Esther(interviewer):

Okay. Write how? We have Delta tables. Delta has MERGE: match on a key, update if it exists, insert if not.

Lukas(candidate):

Like “on conflict” in Postgres, okay. Then MERGE on meter ID plus reading time. And before the MERGE, remove duplicates inside the new data. Because from Kafka we get at-least-once, so the same reading can come two times in one batch.

Why this works

Lukas reaches the right design in a few turns: a natural key, MERGE instead of append, and deduplication of the new batch first. He also knows why the duplicates exist, because he has run Kafka consumers. This is his strongest area, and he uses it well.

Esther(interviewer):

And the late readings? Three days late.

Lukas(candidate):

The job must not ask “what arrived yesterday” only. It asks: which reading dates are in the new data? Then it updates those days. So a reading from Monday that comes on Thursday goes to Monday.

Esther(interviewer):

Good. Now I’m curious how you’d build it. Draw me the boxes.

Lukas(candidate):

I would write a service. Java, a Kafka consumer. It reads the readings, checks them, removes duplicates, and writes to the database with the upsert. Then billing calls an API on this service: give me the usage for this account, from this date to this date. The database is Postgres, with partitions by… The video freezes for a few seconds.

Esther(interviewer):

Sorry, Lukas, you froze. The last thing I heard was “Postgres, with partitions”.

Lukas(candidate):

Ah, sorry. Is it better now? Postgres with partitions by month. But wait, let me count. 115 million a day, times 365… that’s around 42 billion rows a year. Hmm. That’s too much for our Postgres. Maybe something bigger.

Esther(interviewer):

We already have Spark, Delta tables and dbt. Why not use those?

Lukas(candidate):

I can use Spark. But dbt, it’s SQL with templates, right? I prefer real code. I can write unit tests, I can review it, I can debug it.

Esther(interviewer):

dbt has tests too. Data tests, and unit tests since version 1.8. And the forecast team and the analysts can read SQL. Can they read your Java?

Lukas(candidate):

Pause. No. Okay, that’s a good point. I didn’t think who reads it.

Where this goes wrong

A custom service is the tool Lukas knows best, so it is his first answer. But Tidewell already has a platform. A new Java service means new code to run, new on-call, and logic that the people who use the data cannot read. When you design in an interview, start from what the company already has, and say why you would add something new.

Esther(interviewer):

Okay. So the clean readings are in a table. How will analysts consume this data?

Lukas(candidate):

Each team has its own consumer group. Then every team reads the topic at its own speed, and…

Esther(interviewer):

Sorry, my word was bad. “Consume” is a Kafka word too. I mean the people. An analyst who writes SQL in the warehouse. What do they see?

Lukas(candidate):

Ah, okay. They see the readings table. Meter ID, reading time, the value in kWh. One row is one reading. One meter, one 15-minute time.

Esther(interviewer):

Good, that’s the grain. Now the analyst asks: total usage per account per month, for two years. Where is the account?

Lukas(candidate):

I would add the account ID to the reading when it arrives. Look it up in the CRM, and write it in the row. Then the query is simple, group by account and month.

Esther(interviewer):

Two problems. When a meter is swapped, the CRM changes, and it keeps only the current state. And sometimes the CRM is wrong, and they fix it a week later.

Lukas(candidate):

Hmm. Then the old rows have the wrong account. I must update them… Millions of rows, every time somebody fixes the CRM. That’s not nice. Let me think. Pause. In payments, merchants change their fee plan. We keep each plan with “valid from” and “valid to”. So here also: a table with meter, account, valid from, valid to. Then the reading does not hold the account. We join at query time, by the time.

Esther(interviewer):

Yes. That’s called a slowly changing dimension, type 2. Can you say the join?

Lukas(candidate):

Okay, I knew the idea, not the name. So, readings join links on meter ID, and the reading time between valid from and valid to.

Esther(interviewer):

The swap happens at exactly 10:00, and the old link ends at 10:00. The new one starts at 10:00. Where does the 10:00 reading go?

Lukas(candidate):

BETWEEN includes both ends… so it goes to both accounts. Double count. Then greater or equal to “from”, and less than “to”. The end is not included.

Reasoning from his own world

Lukas did not know the term, but he found the pattern through something he knows: fee plans with validity dates. Then he caught the boundary bug when Esther gave him one exact time. This is good engineering. Notice also that Esther gives the term only after he found the idea. She is checking his thinking, not his vocabulary.

Esther(interviewer):

Last one on SQL. The forecast team wants to find gaps: places where a meter has no readings for more than one hour. How?

Lukas(candidate):

I would read the readings per meter, in order, in code, and compare each one with the one before.

Esther(interviewer):

For 1.2 million meters?

Lukas(candidate):

Okay, in SQL. Group by meter and day, count, and where the count is less than 96. Oh, but this only says how many are missing, not where. Then… a self-join? Each reading with the reading 15 minutes before, and where there is none, it’s a gap start.

Esther(interviewer):

That works. There’s a simpler way. Do you know window functions?

Lukas(candidate):

LAG, I think? I have seen it. I never used it, honestly. In my work the database was for the application, not for questions like this.

A stronger answer at the edge

This is Lukas’s edge: analytical SQL. He was honest, and his self-join works. A stronger answer only needs one window function. LAG gives each row the previous reading of the same meter:

SELECT meter_id, prev_ts AS gap_start, reading_ts AS gap_end
FROM (
  SELECT meter_id, reading_ts,
         LAG(reading_ts) OVER (PARTITION BY meter_id ORDER BY reading_ts) AS prev_ts
  FROM readings
  WHERE reading_date >= DATE '2026-09-01'
) t
WHERE reading_ts - prev_ts > INTERVAL '1' HOUR;

The WHERE reading_date line matters at Tidewell’s size: the table is partitioned by reading date, so the query reads only one month. You do not need to know every function by heart. Know that window functions exist for “compare with the row before”, and say it. Our lesson Time and ranking shows LAG step by step.

Esther then asked two short questions. Does billing need streaming? Lukas said no: billing runs once a day, and correct matters more than fast. In payments, he said, they also settle once a day. For the customer-service AI assistant, he said the account filter must be in the data access layer, not in the assistant’s prompt. “The model must not be able to ask for another account. Not ‘should not’. Must not.” Esther wrote that sentence down.

Then she asked about the waiting time for late readings. Lukas said this is a business decision, like the bank cut-off in payments: “Billing must say how long they wait. After that, they need an estimate, and a correction on the next bill.” Esther agreed.

Minutes 50 to 57: Lukas's questions

Lukas(candidate):

I have two questions. First, the meter vendor. How do the readings come from their system to Kafka? Who owns this part?

Esther(interviewer):

Good question. The head-end system is old, and the vendor changes the export format without telling us, like your bank. The integration is half us, half the platform-infrastructure team. Honestly, it’s not clear, and it breaks.

Lukas(candidate):

Okay. That sounds like my bank file. Second: how much of the work is SQL and dbt, and how much is code like Spark jobs?

Esther(interviewer):

Most is SQL and dbt. I would say 70 percent, maybe more. Spark for the raw layer and the big backfills. Some Python for Airflow.

Questions that tell him something

Both questions give Lukas real information. The first shows a messy integration that is close to his strongest experience. The second tells him the job is about 70% SQL and dbt, his weakest area. Good questions help you decide too. See an interview is a conversation in both directions.

Minutes 57 to 60: the close

Esther(interviewer):

Thanks, Lukas. You made my “runs twice” question easy today. Aisha will write to you about the next steps.

Lukas(candidate):

Thank you. And I will look at LAG tonight.

After the call: Esther’s notes

Lukas Becker, pipeline design deep dive

• Idempotent pipelines, re-runs, duplicates: strong evidence. Raised “runs twice” before I did. Natural key, MERGE, deduplication of the batch, at-least-once from Kafka. Found his own “do nothing on conflict” gap with corrections.
• Late data: strong evidence. Rebuild by reading date, not arrival date. Waiting time is a business decision.
• Production and incidents: strong evidence. Bank format change: header checks and control totals. Very close to our head-end problem.
• Data modelling for analytics: some evidence. Got to the grain with help. Found the validity-period pattern from fee plans; did not know SCD type 2 by name; caught the boundary bug when asked.
• Analytical SQL: concern. First answer was code, not SQL. Knows LAG only by name.
• Tool choice: concern. First design was a new Java service and Postgres, next to an existing Spark and dbt platform. He listened when I asked who reads the code.

Recommendation: no for this role. About 70% of the work is SQL and dbt for analysts, and that is his weakest area today. But he is a strong engineer, and the head-end integration needs exactly his skills: format changes, validation, retries, safe re-runs. I would like Graham to talk to the platform-infrastructure team.

What happened next

Pierre’s modelling round agreed with Esther: good instincts, but star schemas and grain were new to Lukas. In the debrief, Graham decided not to continue for the Data Engineer role.

But Graham did not send only a “no”. He called Lukas himself. He explained the decision in two sentences: the role is mostly analytics modelling in SQL and dbt, and that is not yet his strength. Then he asked if Lukas would talk to the platform-infrastructure team. They own the backend for the meter head-end integration, and they had an open role for a senior backend engineer. Esther’s notes went with the referral.

Lukas said yes. One week later, he had a first interview with that team. His bank-file story was the first thing they asked about.

This is not a failure. The interview showed what Lukas does well, and it found a team where that matters most. An honest “no” with a clear reason helped him more than a vague one.

What to take from this

• Your old habits can be your best answers. “What happens if it runs twice?” came naturally to Lukas from payments. Use the reliability questions from your old field.
• Start from what the company has. Before you design a new service, ask what the team already runs. Then explain why you would add something new, if you would.
• Data has readers. In data engineering, analysts and data scientists use your tables directly. Think about the grain, the names and the joins they will write.
• Reason through patterns you know. Lukas did not know “SCD type 2”, but he knew fee plans with validity dates. Say the pattern in your own words. The interviewer can give you the name.
• A “no” can lead to another role. Say clearly what you are strong at. Then a good company can see where you fit, even if it is not the role you applied for.

If your profile is like Lukas’s

1. Learn dimensional modelling. Read about grain, fact tables and dimension tables. Then take your own system, for example payments, and draw a star schema for one business question. Say the grain of each table out loud.
2. Practise window functions until they feel normal. ROW_NUMBER for deduplication, LAG for gaps, running totals with SUM() OVER. Start with Time and ranking, then solve each problem first in SQL, not in code.
3. Build one small dbt project. Take a public dataset, write staging and mart models, an incremental model with a unique key, and tests. Run it twice and check that nothing changes. This answers “why not dbt?” for you.
4. Practise history in tables. Write an SCD type 2 table for something you know, like fee plans or prices. Test the join at the exact boundary time.
5. Keep your reliability stories ready. Your format-change incident and your idempotency fix are strong. Tell them with numbers, and say what the fix still does not cover. See Explain decisions.
6. Before you apply, ask what share of the work is SQL. If most of the role is analytics modelling, prepare that first. If it is integration and services, a data platform or backend role may fit you better today.