The domain expert

Think of a mechanic who learns to write software for garages. She does not know the code as well as the developers do. But she knows which warning lights lie and which problems customers forget to mention. The developers need years to learn that.

Data engineering has people like this too. They worked in billing or field operations, saw the data go wrong every day, and learned SQL and Python in the evenings to fix it. When they apply, they often think: “I am not a real engineer.” Our lesson Your experience is a head start explains why they bring more than they think.

But a serious interview checks both sides: what you know about the data, and what you can build at the company’s scale. Follow Nicole through Esther’s pipeline design deep dive. Watch how her questions change the design, and what she does at the edge of what she knows.

NT

The candidate

Nicole Tremblay

Meter operations coordinator, 7 years at a Quebec utility, learned SQL and Python in the evenings

• 7 years in meter operations
• SQL and Python, about 4 years (evenings)
• French first, English second
• Vancouver, BC (since 2025)

• Meter Operations Coordinator, Nordet Énergie (2021 to 2025). Planned meter swaps and field visits for about 180,000 meters in one region. Worked with field crews, billing and customer service.
• Meter Data Technician, Nordet Énergie (2018 to 2021). Checked estimated and missing readings before billing.
• Built an automated data pipeline that finds meters with missing readings. Reduced meters without readings for 30+ days by 40%.
• Python (pandas), SQL (PostgreSQL), Spark (basic), Git.
• Online courses in SQL, Python and data engineering (2022 to 2026).

What Esther sees before the call

On the main lines of the job description, Nicole is short: no years as a data engineer, no Kafka, no Spark in production, no dbt. “Spark (basic)” probably means a tutorial. Read as a checklist, the CV would not get a call.

But Graham wrote one sentence on it: “She knows meters better than all of us.” Tidewell’s open problems are late readings, meter swaps and daylight-saving days. Those are meter problems before they are Spark problems. Esther writes down what she wants to find out:

• What did Nicole really build? Where does it run, and what happens when it fails?
• “Reduced by 40%”: how does she know that her pipeline caused it?
• On the Tidewell problem, does her domain knowledge become design decisions?
• Where does her engineering stop? Esther expects the edge at volume: 115 million readings a day.

Terms on this page

• Interval reading: the energy that a meter measured in a short period, here 15 minutes. 96 of them make a normal day.
• Register read: the total on the meter, like the kilometres on a car. It only goes up. The difference between two register reads is the energy used between them.
• Estimated read: a value that the utility calculates when the real reading is missing, for example from the same period last year. A later real reading corrects it.
• Meter swap: a field crew replaces a meter. The new meter has a new ID and a new register.
• Head-end system: the meter vendor’s system that collects readings from the meters over the network.
• VEE (validation, estimation and editing): the usual utility process that checks meter data and fills gaps before billing.
• Idempotent: if you run the job twice with the same input, you get the same result as running it once.
• MERGE (or upsert): a write that updates a row if its key already exists and inserts it if not.
• SCD Type 2 (slowly changing dimension, type 2): a table that keeps the history of a link, with a “valid from” and a “valid to” date on each row, instead of only the current value.

The interview

Minutes 0 to 5: the opening

Esther(interviewer):

Hi Nicole, can you hear me okay? I’m Esther, senior data engineer on the Data Platform team. So, the plan. First a pipeline you built, and I will ask a lot of questions. Then a real problem we have. Then your questions. No AI tools in this round, please. Paper is fine.

Nicole(candidate):

Yes, I hear you. Sorry, my camera was slow to start. I have paper. And just so you know, French is my first language. If I say something strange, please ask me.

Esther(interviewer):

Sure. I grew up with Yoruba and English, so I know the feeling. Let’s go.

Minutes 5 to 25: the missing-readings pipeline, then deeper

Esther(interviewer):

So, tell me about the pipeline.

Nicole(candidate):

Okay. So, at Nordet, my region had about 180,000 meters. Every morning the coordinators looked for meters with no readings, because no reading means an estimated bill, and then customers call. Before, it was a report in Excel, very slow. We found the problem after one or two months.

What I did is a Python script. Every morning it takes the export from our meter data system, a CSV with the daily register read for each meter. Only daily, because IT said the 15-minute intervals are too big for my database. The script puts it in PostgreSQL. Then SQL finds the meters with no reading for three days or more, and makes a list for the field crews.

Esther(interviewer):

Mm-hm. And if the readings for one day come late, what happens?

Nicole(candidate):

The export has always the last 14 days. So each morning I delete the last 14 days in my table and I load them again. If a reading arrives late, it’s in the next export, and it goes in the right day.

Esther(interviewer):

And what happens if it runs twice?

Nicole(candidate):

Twice… it deletes again and loads again. So, the same table. Yes. The same result.

Esther(interviewer):

Are the delete and the load in one transaction?

Nicole(candidate):

Hmm. No, I don’t think so. So if it fails in the middle, I have 14 days missing. It happened one time, I think. The list was empty. The crews were happy that day.

How Esther goes deeper

Nicole made her load idempotent without knowing the word: she replaces a window of 14 days, so late readings land on the right day and a second run changes nothing. Then Esther checks one step further. Without one transaction, a failure in the middle leaves a hole. Nicole sees it herself. In her tool it is a small gap. On a billing table, it can cost money.

Esther(interviewer):

Okay. How did you know the list was correct? That it found the right meters?

Nicole(candidate):

The crews. After a visit, they write what they found: a dead modem, a broken meter, nobody living there. At the start the list was bad. Maybe 300 meters a day, and many were nothing. Because of the zeros.

My first version said: no consumption for three days, it’s a problem. But in January, half of my list was chalets. Hmm, cottages? Summer houses in the forest. Nobody is there in winter, the meter reads zero. It’s a true zero. So I sent crews to empty cottages in the snow. They were not happy with me.

Esther(interviewer):

Laughs. I believe it. So what did you change?

Nicole(candidate):

I separated the zeros. A zero can be real, like the cottage. A zero can be a meter that we disconnected by remote. Also real, but billing must know. And sometimes the system writes a zero when there is no reading. That one is not a zero, it’s missing. So I used the flags in the export, and the customer type. After, the list was more like 100, 120 a day. And the crews said most were real problems. Maybe seven in ten.

Three kinds of zero

This is domain knowledge that becomes a rule in the data. A data engineer without it sees one number, 0, and treats a real zero, a disconnected meter and a filled-in missing value the same way.

Esther also asks about the 40% on the CV. Nicole is honest: the same year, a program replaced old modems in the meters, so the 40% is for the whole region, and she does not know her own part. “The crews went faster, that I’m sure. But 40% is not all me. I should write it better.” Then Esther asks about the worst day. After an upgrade of the meter data system, two columns in the CSV changed order, and nobody told Nicole. Her script read columns by position, so the list was wrong for two days, and crews made about 40 visits to the wrong meters. Now the script reads columns by name and stops with an email if one is missing. “I prefer no list to a wrong list,” she says. On cost, she is honest: the server belonged to IT, and she never saw a bill. Nobody reviewed her code. She has a few pytest tests for the zero rules, none for the loading part.

Where this goes wrong

Here the gap is production engineering. The tool ran on one server, with no review, few tests and one person who knew it. That was fine for one team. Tidewell’s billing job runs at 5 a.m. for 1.2 million meters, with a person on call. Esther does not mark Nicole down for working alone, but she notes that Nicole has never worked in a shared codebase with reviews.

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?

Nicole(candidate):

Okay. Can I ask about billing first? When readings are late on the bill day, does billing wait, or estimate? And do you bill from the intervals or from the register read?

Esther(interviewer):

Good questions. Billing runs at 5 a.m. from our dbt models, with the intervals. If readings are missing, the billing system estimates. Register reads… I think the head-end sends one per day, but we don’t use it. It just sits in the raw table.

Nicole(candidate):

Okay, interesting, I come back to that. So, first thing. You store the reading by the time it was measured, not the time it arrived. The relève… sorry, the reading from Monday is a Monday reading, even if it arrives on Thursday.

Esther(interviewer):

Draws a box on the whiteboard. Event time. So the table is partitioned by reading date. Then what does the nightly job do?

Nicole(candidate):

Like my 14 days? Each night it takes the last days again and replaces them. Three days, or more. After the ice storm in 2023, some of our meters were one week late.

Esther(interviewer):

We had the same after a windstorm last winter. But think about our size. 115 million readings a day. If we replace 14 days every night, how much is that?

Nicole(candidate):

115 times 14… wait. About 1,600 million? So 1.6 billion rows, every night. Hmm. That’s a lot, just for a few late meters. So… in PostgreSQL I would do an upsert. Insert, and on conflict, update. The key is the meter and the time of the reading. Then only the new readings move. And if it runs twice, the second time nothing changes, because the keys are already there.

Esther(interviewer):

On Delta tables that’s a MERGE, same idea. And that key also fixes our double billing. The re-run appended the same rows again. With a MERGE on meter and timestamp, a second run only updates the same rows.

What the interviewer is checking

Esther checks whether Nicole can move from her small tool to Tidewell’s size. Nicole does the multiplication aloud, sees the cost, and moves to a write by key. Meter ID plus reading timestamp is exactly the key that stops the double billing. She gets there from PostgreSQL, a tool she knows.

Nicole(candidate):

Can I ask, is the timestamp in UTC? I hope yes. Because of the time change.

Esther(interviewer):

Why does that matter?

Nicole(candidate):

In UTC every day has 96 readings, good. But the customer’s day is local. In March, when the clock goes forward, the local day has 23 hours, so 92 readings. In November, 25 hours, so 100. If a check says “every meter must have 96 per day”, on those two days every meter is wrong. At Nordet, every year, the same email: “why are all the meters in error?”

Esther(interviewer):

Short laugh. Yes, UTC. And we have exactly one job that assumes 96. Last March it flagged every meter we have. Okay. Now the expensive part. The MERGE goes into a table with five years of history. How do you stop it from reading the whole table every night?

Nicole(candidate):

Can I have a minute?

Esther(interviewer):

Take your time.

Nicole(candidate):

About 40 seconds of silence. Nicole writes on her paper.

Okay. Honestly, this is where I stop. I never used Spark on real data. I did the tutorial in the free Databricks, maybe one million rows. It was fast, and I learned nothing about this. In PostgreSQL I would have an index on the key. Here I don’t know how Spark finds the rows without reading everything. How many machines, what is slow… I don’t know. I prefer to say it.

A stronger answer at the edge

Nicole was honest, and that is good. She could also reason a little further with what she already knows. She does not need Spark for this:

“I don’t know Spark, but let me think. The late readings are only for the last few days. So the new batch has maybe three or four reading dates, sometimes a week. If the table is split by reading date, can the MERGE look only at those dates? Then the cost depends on the days in the batch, not on five years of history. Is that possible in Delta?”

It is possible. If the MERGE condition also limits the reading dates, Delta skips the other date partitions. This is called partition pruning. (For new tables, Databricks now recommends liquid clustering instead of date partitions; a date filter in the MERGE helps there too.) Nicole does not need the name; the reasoning gets her most of the way.

Esther(interviewer):

That’s fine, thank you for being clear. Short version, because it’s useful: if the MERGE also says which reading dates to look at, Delta skips the other dates. Partition pruning.

Nicole(candidate):

Okay. Good to know.

Esther(interviewer):

Second problem: meter swaps. The new meter has a new ID. The account-to-meter link is in a CRM table that only keeps the current state, so the forecast team sees gaps and jumps. For the history, we would use the date on the field work order.

Nicole(candidate):

Hmm. Careful with that date. Which date is it? Usually it’s the date the work order was closed. Not the day the meter was changed. The crew changes the meter on Tuesday, and closes all the orders on Friday. Sometimes one week later.

Esther(interviewer):

Mm-hm. So where is the real date?

Nicole(candidate):

From the meters. The old one stops sending, the new one starts. Often the head-end sends a swap event too. And the CRM link changes only when the order is closed, so you have days where the old meter is gone and the new one is not linked yet. That’s your gap. If the meter date is far from the work order, you trust the meter, and you tell the field team.

Disagreeing well

Esther said the team would use the date on the field work order. Nicole questions it with what she has seen in the field: that date is when the crew closed the order, which can be days after the real swap. She does not say “you are wrong”. She asks which date it is, explains, and gives a better source. Esther changes her design. See Interviewers are not always right.

Esther(interviewer):

Oh. I thought the work-order date was the real one. Okay. And the jumps?

Nicole(candidate):

The register. The old meter was at, say, 48,000 kWh. The new one starts near zero. If someone calculates usage from register differences across the swap, they get a huge negative number. So: the final read of the old meter, the first read of the new one. Two meters, and you join them for the account, with dates.

Esther(interviewer):

So a history with from and to dates, an SCD Type 2 table. Okay. Last user: since the summer, a customer-service AI assistant reads a customer’s usage to explain a high bill. What could go wrong for the assistant?

Nicole(candidate):

Hmm, let me think. Old data, wrong data, sure. Pause. Oh. The same history problem. When a tenant moves out and a new one moves in, the meter stays. Same meter, two accounts. If the assistant reads usage by meter, the new tenant can see the old tenant’s usage. When they were home, when they were away. So it must read by account, with the dates the account had that meter.

Esther(interviewer):

Pause. We have not tested move-outs. I’m writing that down.

One table, three problems

Nicole connects three problems that looked separate: the forecast gaps, the jumps at a swap, and a privacy risk in the AI assistant. One table, the account-to-meter history with real dates, solves all three. That comes from seven years of planning meter swaps.

In the last minutes, Esther asks how Nicole would know the data is correct before billing. Nicole lists checks from her VEE work: readings per meter per local day (92, 96 or 100), long runs of zeros by customer type, negative values. Then she adds one Esther had not thought of: compare the sum of a day’s intervals with the difference between two register reads. “If they do not match, one is wrong, and billing should know before 5 a.m.” She also asks what the customer sees when an estimated bill is corrected later: “The next bill is different, and they must see why, or they call.”

Minutes 50 to 60: Nicole's questions and the close

Nicole(candidate):

I have three. First: who owns the account-to-meter table? If the CRM team changes it, do they tell you?

Esther(interviewer):

Honest answer: no. We have no agreement with them. They renamed a column last spring, and we found out from a broken model.

Nicole(candidate):

Like my CSV. Second: Aisha told me about the on-call. What does a bad week look like, after a storm?

Esther(interviewer):

Long. After the windstorm, readings came late for a week, and the person on call checked the late days every morning before billing. Most weeks are quiet. That one was not.

Nicole(candidate):

Okay, good to know. Last one: if I join and I don’t know Spark, how do people learn it here? Is there time at work, or only evenings?

Esther(interviewer):

New people pair with a senior for the first months. But to be honest, the team is busy, and Spark at our size takes time to learn. Graham can tell you more. And we are almost at time. Thank you, Nicole. Really. I have a list of things to check tomorrow because of you. Next is data modelling with Pierre, and Aisha will write to you.

Nicole(candidate):

Thank you. And I will put the delete and the load in one transaction. Tonight, maybe.

Questions that find what the posting does not say

Nicole’s first question finds a fact that the job description does not say: there is no data contract with the CRM team. Her second asks what on-call really looks like, not only how often. Her third asks how she would learn, which is her real question. This is the interview as a conversation in both directions.

After the call: Esther’s notes

Esther writes her notes that evening, against the job description.

Nicole Tremblay, pipeline design deep dive

• Meter and billing domain: strong evidence. Found the DST bug and the problem with the work-order swap date without help. Found the move-out privacy risk from one question. Proposed the interval-versus-register check.
• Data quality: strong evidence. Three kinds of zero, column checks after an incident, “no list is better than a wrong list”.
• Pipeline design: some evidence. Event time, a window for late data, an upsert on meter and timestamp. Reached them from her own tool, with small help.
• SQL and Python: some evidence. A real tool in daily use for about two years. The coding round will tell more.
• Spark, Kafka, scale: no evidence. Tutorial level. Said so clearly. Did not know how to avoid a full-table MERGE.
• Production engineering: concern. No code review, few tests, delete and load not in one transaction, never on call.

Recommendation: no for this role, now. We need someone who can own the nightly Spark jobs soon, and she cannot yet. But we should not lose her. Her checks would have caught the double billing and the DST alert. I will talk to Graham about billing data quality.

What happened next

Pierre’s data modelling round said the same thing in another way. Nicole modelled the account-to-meter history very well, and she struggled to estimate table sizes and costs. Graham agreed with Esther: the one Data Engineer vacancy needed someone ready for the Spark jobs now, and it went to another candidate.

But Rosa Delgado, the Director of Billing Operations, had met Nicole in the values round. She had also read the double-billing complaint from Kamloops herself. Graham and Rosa created a new role in Rosa’s team: Data Analyst, Billing Data Quality. The job is to own the checks on the billing tables, the account-to-meter history, and the reconciliation between intervals and register reads. Nicole would work every day with the Data Platform team, and pair with Esther for the first months.

Aisha called Nicole and explained honestly: not the Data Engineer role, but a new role built around what she showed. The offer was CAD 95,000. Nicole asked two things: a written plan to move into data engineering in 18 months, with Spark training and on-call shadowing in the second year, and a check with Graham at 6 and 12 months. She also asked for CAD 100,000. Tidewell agreed to the plan in writing and to CAD 98,000. She accepted.

This is one realistic ending, not a promise. Many companies would simply say no. Tidewell made a role because the timing was right: a recent billing incident, and a director with budget. Luck is part of it.

What to take from this

• Domain knowledge wins when it becomes a rule or a design. Nicole did not say “I know meters”. She said: this date is when the crew closed the order, this zero is real, this day has 92 readings.
• Ask your domain questions early, and correct the interviewer about your field when you must. Use what you saw, ask a question first, and offer a better source.
• Reason from the tools you know. Nicole reached a key-based write through PostgreSQL’s upsert. At the Spark edge, the same approach could have taken her further.
• Fix your CV numbers before the interview. “40%” had another cause too. Nicole was honest when asked, but it is better to write it correctly.
• A “no” can come with a different offer. The interview showed her strengths so clearly that the company made a role for them.

If your profile is like Nicole’s

1. Write down your domain rules as data checks. For each rule you know (“a zero in a cottage in January is real”), write the SQL query that finds the rows that break it. Validation rules and Profile before changing show how.
2. Make your own tool safe to run twice. Put deletes and loads in one transaction, or use a key-based upsert. Then explain it aloud with the word “idempotent”. See Reproducible extraction.
3. Get your code reviewed and tested. Add tests for the loading part, not only the rules (Focused tests). If nobody at work can review your code, publish a copy without private data and ask a developer in a community to review it (Read and review diffs).
4. Learn one level of scale above your tool. Load a public dataset of tens of millions of rows into Spark on a free platform. Write the same upsert as a Delta MERGE, then look at what it reads with and without a date condition. Then you can say clearly where you stop.
5. Turn your field stories into design decisions. For each story, say the problem, why it is expensive, and what the pipeline should do. Your experience is a head start and Domain knowledge in practice help you write them down. Explain decisions helps you say them aloud.