The modern stack

Think of a cook who has always used a rice cooker. The rice is perfect every time. Then one day they cook in a kitchen with only a pot and a gas stove. They are not a bad cook. They simply never had to learn how much water, how much heat, and when to stop, because the machine decided for them.

Many data engineers start their careers like this. They join a fast start-up and build a full platform in a few months with managed tools: a tool that copies data from the apps, a cloud warehouse, dbt for the models, and a scheduler. The work is real, and it moves fast. But the tools quietly handle the hardest parts: late data, duplicates and safe re-runs. When these people apply to a company with a big, messy data source, they meet the pot and the gas stove.

Follow Jordan through Esther’s pipeline deep dive at Tidewell Energy. Watch what Jordan knows very well, where the tools hid things from them, and what they do at the edge. The answer is “no” this time. Look closely at why that is still a good result for an engineer with two years of experience.

JE

The candidate

Jordan Ellis

Data engineer, 2 years at a fast-growing start-up, modern data stack

• 2 years as a data engineer
• Fivetran, dbt, Snowflake, Airflow
• First data engineer at a start-up
• Vancouver, BC

• Data Engineer, Pantrybox (2024 to now). Meal-kit subscription start-up that grew from 60 to 220 people. First data engineer, team of two.
• Built the company data platform from zero with the modern data stack: Fivetran (6 sources), Snowflake, dbt (180+ models, 400+ tests), Airflow.
• Built the revenue models that finance uses for monthly reporting. Documented every model in dbt docs.
• Reduced Snowflake costs by 35%.
• Tools: SQL, Python, dbt, Snowflake, Fivetran, Airflow, Dagster, Kafka, Spark, Flink, Looker, Git.
• B.A. Economics (2023). dbt certification (2025).

What Esther sees before the call

Esther reads the CV twice. The first part is good. Jordan built a platform alone, and 400 tests on 180 models shows care. Many data engineers with five years write fewer tests than that.

The tool line worries her a little. Kafka, Spark and Flink appear in a list, but no project uses them. She does not plan to quiz Jordan on the list. She wants to see what Jordan can explain. Her questions:

• How does Jordan’s pipeline behave when it runs twice, or when data arrives late? Do they know, or did the tools decide?
• “Reduced costs by 35%”: what did they change, and how did they measure it?
• On the Tidewell problem, can Jordan reason step by step when no tool does it for them?

Terms on this page

• Fivetran: a managed service that copies data from apps and databases into a warehouse on a schedule.
• dbt: a tool that builds tables in a warehouse from SQL models, with tests and documentation.
• Incremental model: a dbt model that only processes new or changed rows on each run, instead of rebuilding the whole table. A full refresh rebuilds the whole table.
• Event time and arrival time: when a meter took a reading, and when the reading reached our systems. They can be days apart.
• Partition: a part of a table, here one day of readings. Partition overwrite: replace whole partitions with a new version, instead of adding rows.
• MERGE: one SQL statement that updates rows that already exist (matched on a key) and inserts the rows that do not.
• SCD type 2 (slowly changing dimension, type 2): a table that keeps every version of a record, each with the dates when it was valid. A dbt snapshot builds this kind of table.

The interview

Minutes 0 to 5: the opening

Esther(interviewer):

Hi Jordan, good morning. Can you hear me well? I’m Esther, senior data engineer on the Data Platform team. I’m on the billing pipelines, mostly. So, the plan. First you tell me about a pipeline you built, and I ask many questions. Then I give you one of our real problems. At the end, your questions. One thing: no AI tools in this round, please. Paper is fine, and I have a whiteboard we can both draw on.

Jordan(candidate):

Yes, I hear you. Hi! Okay, sounds good. I have paper. I’ll try not to talk too fast, I do that when I’m nervous.

Esther(interviewer):

Laughs a little. That’s fine. I’ll stop you if I get lost.

Minutes 5 to 25: the pipeline, then deeper

Esther(interviewer):

So. Pick one pipeline you know well, and walk me through it.

Jordan(candidate):

Okay. The revenue pipeline at Pantrybox, because finance uses it every month. So, we sell meal kits by subscription. Payments are in Stripe, orders are in our app’s Postgres database. Fivetran copies both into Snowflake, every fifteen minutes I think, maybe thirty for Stripe. Then dbt builds the models: charges, refunds, revenue by day and by plan. Airflow runs dbt every morning at six. And there are tests on everything, unique, not null, and some custom ones, like revenue can’t be negative for a day.

Esther(interviewer):

Mm-hm. How much data?

Jordan(candidate):

Not huge. About 40,000 subscribers. The charges table is maybe two million rows. The biggest thing is app events, I think around 300 million rows, but revenue doesn’t use it.

Esther(interviewer):

Okay. And how do you re-run it?

Jordan(candidate):

Airflow retries each task two times, with five minutes between. If it still fails, I get a Slack message.

Esther(interviewer):

Sorry, I mean something different. Say the revenue for last Tuesday was wrong. You fix the code. How do you run last Tuesday again?

Jordan(candidate):

Ah, okay. Hmm. We don’t really run “last Tuesday”. dbt builds the models from all the data that is in Snowflake now. So I fix the code and run it again, and for incremental models I do a full refresh.

Esther(interviewer):

So there is no “Tuesday” in your pipeline. Every run is “everything until now”.

Jordan(candidate):

Yes. I never thought about it like that, but yes.

What the interviewer is checking

Esther’s first “re-run” question was not clear, and Jordan answered a different question, about automatic retries. She does not judge that. She asks again with a concrete example. Then she learns something important: Jordan’s pipeline has no idea of a day that you can process again. At Pantrybox’s size, that was fine.

Esther(interviewer):

Okay. What was the worst incident on this pipeline?

Jordan(candidate):

Refunds. Last year finance found that March revenue was too high, about 2%. Maybe a bit less. The charges model was incremental, and it picked up new rows by created date. But a refund doesn’t create a new charge, it changes the old one. So the refunded charges never got updated in our model.

Esther(interviewer):

Good. How did you fix it?

Jordan(candidate):

I changed the filter to the updated date, with a lookback of three days. And a unique key on the charge ID, so dbt merges instead of adding. And then a full refresh every Sunday, to be safe.

Esther(interviewer):

Why three days?

Jordan(candidate):

Honestly… I took it from a blog post. It felt safe.

Esther(interviewer):

And the full refresh on Sunday. What is it for, if the incremental is correct?

Jordan(candidate):

Pause. Because we didn’t fully trust it. It’s cheap at our size, so we did both.

Where this goes wrong

A weekly full refresh “to be safe” is common, and at two million rows it costs almost nothing. But it hides a question that Jordan never had to answer: which rows can still change, and when? At Tidewell, the readings table grows by about 115 million rows a day, so about 42 billion rows a year. A full refresh of that every Sunday is not a small safety step. It is a large bill.

Esther(interviewer):

Let me go one level under. When a refund changes a charge in Stripe, how does that change reach Snowflake? And if Fivetran sends the same change two times, do you get two rows?

Jordan(candidate):

Fivetran did that for us. I don’t actually know how. I never saw duplicates, so I think it updates the row by the primary key. But I’m guessing. I never looked.

Esther(interviewer):

That’s an honest answer, thank you. Your guess is right. Fivetran asks the source what changed since the last sync, and it writes by primary key. So if the same change comes twice, it updates the same row again. You got safe re-runs for free, you just didn’t see them.

Jordan(candidate):

Huh. Okay. So the tool was safe to re-run, idempotent, and my model was not, at first.

Why this honesty works

“Fivetran did that for us, I don’t actually know how” is the most useful sentence in this interview. Esther now knows exactly where Jordan’s knowledge stops, so she can trust the rest of what they said. Then Jordan does something better than a guess: they connect Esther’s explanation to their own incident. See Explain your skills honestly.

Esther(interviewer):

Your CV says you cut Snowflake costs by 35%. What did you change?

Jordan(candidate):

Two things, mostly. The warehouses waited ten minutes before they suspended, and I changed that to one minute, because our jobs are short. And I moved two big models from full rebuild to incremental. The bill went from about 6,000 dollars a month to about 4,000. So… honestly it’s more like 33%. The 35 was the best month.

Esther(interviewer):

Laughs. Okay, 33. And did someone own the cost, or was it you?

Jordan(candidate):

Me, because nobody else looked at it. I made a small dashboard of credits by model.

Esther’s last CV question was about Kafka, Spark and Flink. Jordan said directly that they had used them only in an online course and in a small Flink project on their laptop, and that the CV should probably move them to a “learning” line.

A stronger CV line

The tool list made a promise that the interview could not keep. Jordan handled it well when asked. A better CV avoids the question: “Learning: Kafka and Spark (online course, one small project)”. It is true, it is still interesting, and the interviewer can ask about the project instead of checking a claim. See Portfolio anatomy.

Minutes 25 to 40: the Tidewell problem, the re-run

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?

Jordan(candidate):

Okay. Can I ask some questions first? The nightly job reads what arrived yesterday, and adds it to the clean table?

Esther(interviewer):

Yes. It appends. The clean table is split by arrival date.

Jordan(candidate):

And the re-run appended the same day again. So every reading from that day was there two times.

Esther(interviewer):

Yes. And billing summed them.

Jordan(candidate):

Okay. My first idea is what I did with refunds. Make it incremental with a unique key, meter ID plus the reading time, and merge. Then a second run updates the same rows and doesn’t add new ones.

Esther(interviewer):

That works. That’s a correct answer. Now, the clean table has about five years of readings. What does a MERGE cost on that?

Jordan(candidate):

Hmm. It has to find the matching rows for every new reading. In Snowflake I never thought about how it finds them. I don’t know how Delta does it.

Esther(interviewer):

Let me give you a hint. Think about whole days, not single rows. A late reading, which day does it belong to?

Jordan(candidate):

The day the meter measured it. Not the day it arrived. Oh. So the clean table should be split by reading date, not arrival date.

Esther(interviewer):

Mm-hm. And then?

Jordan(candidate):

Then… every night, take the last three reading days, throw them away, and build them again from the raw data. Everything that has arrived for those days until now. Because those are the only days that can still change.

Esther(interviewer):

And what happens if it runs twice?

Jordan(candidate):

The same three days get replaced with the same data. Same result. Pause. Okay, that’s actually simpler than the merge. It’s like my Sunday full refresh, but only for the days that can still change.

Esther(interviewer):

Exactly. We call it partition overwrite. In Delta you say which dates you replace, and it swaps them in one transaction. The other five years, it doesn’t touch.

Reasoning with help

Jordan’s first answer, MERGE on a key, is correct. Esther does not say it is wrong. She asks about cost, and then gives one hint. Jordan does the rest of the reasoning: event time decides the day, only recent days can change, and those days get replaced as a whole. A good hint is not a lost point. What counts is what you do with it. A MERGE that also limits the reading dates is cheap too. Both are correct; overwrite is simpler to reason about.

Esther(interviewer):

One more thing about the raw table. The head-end system sometimes sends the same reading again after a network problem. So raw can have duplicates.

Jordan(candidate):

Then when I rebuild a day, I keep one row per meter and reading time. ROW_NUMBER over meter ID and reading time, ordered by arrival time, newest first, and keep row one. That part I know well.

Esther(interviewer):

Why the newest?

Jordan(candidate):

In case the second one is a correction? But I’m not sure meters send corrections. Do they?

Esther(interviewer):

Sometimes, yes, after a meter is repaired. Good question. And what about readings that come after three days? In a normal week, very few. After a big storm, maybe one or two percent of meters, for up to a week.

Jordan(candidate):

Then maybe the job shouldn’t use a fixed three days. It could look at yesterday’s raw data, see which reading dates are in it, and rebuild only those days. Usually that’s three or four days. After a storm it’s more, but only when needed.

Esther(interviewer):

I like that. The data tells you which days to rebuild.

Minutes 40 to 50: billing, quality and history

Esther(interviewer):

Now, billing runs at 5 a.m. Some readings for yesterday are not there yet. What does billing do?

Jordan(candidate):

I don’t know how billing works with missing readings. Do you estimate them?

Esther(interviewer):

Yes. Billing estimates the missing hours, and corrects on the next bill when the real readings come.

Jordan(candidate):

Then the table needs a flag, actual or estimated. And the bill should remember which version of the data it used. Otherwise nobody can explain the correction to the customer.

Esther(interviewer):

Good. How would you know that a day is complete?

Jordan(candidate):

A test, like in dbt. Count readings per meter per day. It should be 96. Alert when too many meters have less.

Esther(interviewer):

Always 96? Think about the first of November.

Jordan(candidate):

The first of… oh, the time change. The day has 25 hours, so 100 readings. And in March, 23 hours, so 92. So the expected count comes from the number of hours in that local day, not a fixed number.

Esther(interviewer):

Yes. One of our jobs still has 96 written in the code. Small laugh. Okay, last topic. When a meter is swapped, the new meter has a new ID, and the CRM only keeps which meter an account has today. The forecast team sees gaps and jumps.

Jordan(candidate):

Oh, that one I know. A dbt snapshot on the CRM table, every night. Then you have history: account, meter, valid from, valid to. Type 2. And the forecast joins readings to the account for the right dates.

Esther(interviewer):

And the history before the day you turn on the snapshot?

Jordan(candidate):

Pause. It doesn’t exist. The snapshot only starts recording from that day. Is there another place where the swaps are written? Like the work order for the person who swaps the meter?

Esther(interviewer):

Yes, field work orders. Three years of them.

Jordan(candidate):

Then build the past history from the work orders, and the snapshot keeps it going from now. And compare them for a few months, because they will not agree everywhere.

The strongest answer of the day

Here Jordan’s real strengths show. They know SCD type 2 from dbt snapshots, and they see the trap that many people miss: a snapshot only remembers from the day you start it. Then they look for another source of truth and plan to compare the two. This is the same careful thinking as their 400 tests.

Esther asked one more short question, about the customer-service AI assistant. Jordan answered from their own work: at Pantrybox, they had put row-level access rules on customer data in Snowflake, so each support tool saw only one customer’s rows. “I’d want the same here, and a dbt test that tries to read another account and fails.” Esther liked the test idea.

Minutes 50 to 57: Jordan's questions

Jordan(candidate):

I have three. First, the Spark part. If someone like me joined, how would they learn it? Is there pairing?

Esther(interviewer):

Honest answer: some. We pair on new pipelines. But there is no training plan. People learn on the job, and the job is busy.

Jordan(candidate):

Okay. Second. Aisha said on-call is one week in seven. How does a new person start?

Esther(interviewer):

They shadow first, next to the person on call, before they do it alone. Billing runs at 5 a.m., so if it fails, someone wakes up.

Jordan(candidate):

I’ve never been on call. Honestly, that scares me a little. But I’d want to learn it. Last one: who owns the warehouse cost here?

Esther(interviewer):

Laughs. Good question. Nobody, really. It went up a lot this year.

Good questions

Each question gets a real answer that Jordan can use: the learning is on the job, new people shadow on-call before they do it alone, and nobody owns cost. The cost question also reminds Esther that Jordan did own cost at Pantrybox. Questions can show your strengths too. See Interviews go both ways.

Minutes 57 to 60: the close

Esther(interviewer):

Thanks, Jordan. Next steps: Aisha will contact you this week.

Jordan(candidate):

Thank you. I learned a lot in one hour, especially the partition part. I’m going to look at my Sunday full refresh again.

After the call: Esther’s notes

Jordan Ellis, pipeline design deep dive

• dbt, testing, documentation: strong evidence. 400+ tests, documented models, a clear incident (refunds) with a real fix. Saw the dbt snapshot trap without help.
• SQL: strong evidence. Deduplication with ROW_NUMBER, explained in one sentence.
• Re-runs and late data: some evidence. Started with MERGE on a key (correct). Reached partition overwrite by reading date with one hint. Did not know why their own lookback was three days.
• Spark, Kafka, streaming: no evidence. Course level only, and said so.
• Production operations: no evidence. Never on call; honest about it.
• Cost: some evidence. Owned cost at Pantrybox; changes were simple but measured, and corrected their own number.
• Concern: the CV tool list. It claims more than the work shows. Jordan was direct about it when asked.

Recommendation: no for this role. We need someone who can own the reprocessing design and the 5 a.m. on-call in the next few months. Jordan would need six months to a year of close support on Spark and operations, and we do not have that time this quarter. I would be happy to see them again in a year. Their honesty at the edge was the best of the week.

What happened next

Jordan’s rounds with Pierre and Rosa went well, but in the debrief they did not change the main gap, and Graham agreed with Esther. Aisha called Jordan the next week and gave the decision. Two days later, Graham sent a short email with specific feedback, written from Esther’s notes:

• What was strong: testing, the refund incident, the snapshot trap, honesty about Fivetran and the CV.
• What we missed: partitioning by event time, safe re-runs without a full refresh, Spark at volume, and on-call.
• What to learn next: build a small pipeline on a public time-series dataset where some data arrives late. Partition it by event date, rebuild the recent days every night, and prove that running it twice gives the same result. Then read how Delta or Iceberg replaces partitions, and learn the basics of Kafka consumers and offsets.

Jordan answered the same day. They thanked Graham and asked if they could stay in touch and apply again later. Graham said yes. Esther accepted Jordan’s connection request and sent them a link to a talk about late data.

This is a good result for an engineer with two years of experience. Jordan reached a final-stage design round at a company with a hard data problem. They left with a clear list of what to learn, written by the people who would hire them later. And they left a good memory. The vacancy went to a candidate with many more years of production work. That was not a judgement of Jordan’s future. Luck and timing are part of it.

What to take from this

• Learn one level under your tools. Managed tools handle late data, duplicates and re-runs for you. Find out how they do it, so that you can do it without them.
• “I don’t know how the tool does it” is a strong answer when it is true. It shows the interviewer where your edge is, so they trust the rest.
• Know why your numbers are what they are. “Three days, from a blog post” is honest, but you should be able to explain every number in your own pipeline.
• Make your CV match what you can explain. A tool you only met in a course belongs in a “learning” line.
• A hint is not a failure. Jordan reached partition overwrite with one hint, and that is what Esther wrote down.
• A “no” with specific feedback is valuable. Ask for it politely, and ask to stay in touch.

If your profile is like Jordan’s

1. Rebuild one of your pipelines without the managed tool. Load a public dataset by hand into tables, then answer: what happens if this runs twice? What happens if a row arrives three days late? Write the answers in your README.
2. Partition by event time and re-run on purpose. Rebuild the last few days every night, run it twice, and compare the row counts and totals. Deliver a reusable dataset shows how to prove that two runs give the same result.
3. Practise the SQL that deduplicates and reconciles. Window functions in Time and ranking, and data checks as code in Validation rules.
4. Learn the basics of one engine under the tools. Spark or a streaming system: partitions, consumers and offsets, watermarks. One small project is enough to talk about it honestly.
5. Ask to shadow on-call in your current job, even for one week. It is the experience that interviews for this role check most often, and it is often easy to get.
6. Fix the CV. Every tool in the list should connect to a line that you can explain for ten minutes. See Prepare for applications and Explain decisions.