The veteran

Think of a bus driver with 25 years on the road who moves to a city with new electric buses. The screen is different, the doors open in a new way, and the young drivers use words that he does not use. But in his first week, he sees the cyclist in the mirror before anyone else does. He knows what happens on a wet road at 6 p.m. That knowledge did not come from the bus.

Many experienced data engineers are in the same position. They built warehouses on Teradata or Oracle, wrote ETL in Informatica, and ran Hadoop clusters. Many of them also led the move to the cloud. Some of them are over 50, and they carry a quiet worry: “Will they see my age, and my old tools, and stop listening?” The worry is real. In British Columbia, the Human Rights Code protects job applicants from discrimination because of age, and good teams hire on evidence. But a law does not remove the worry. Evidence does.

Follow Vikram through Esther’s pipeline deep dive at Tidewell Energy. He never mentions his age, and he does not need to. Watch how his experience turns into precise answers, where his words are older than the team’s, and how he politely corrects the interviewer.

VR

The candidate

Vikram Rao

Data engineering lead, 24 years in banking data, led a move from Teradata to a cloud lakehouse

• 24 years in data
• Teradata, Informatica, Hadoop
• Led a lakehouse migration
• Surrey, BC

• Lead Data Engineer, Cloud Migration, Westmark Financial (2023 to now). Led a team of 6 that moved the finance and risk warehouse from Teradata and Hadoop to a cloud lakehouse (Spark, Iceberg, Airflow, Kafka for card events).
• Ran the old and new platforms in parallel for 4 months, with daily reconciliation to the cent. Switched off Teradata in 2025.
• Data Warehouse Architect, then ETL Lead, Westmark Financial (2006 to 2023). Daily loads for card transactions, general ledger and regulatory reports, with Informatica PowerCenter, Teradata and Hive.
• Designed the control-totals framework used by 40+ daily feeds.
• Led the data on-call rotation for 10 years.
• ETL Developer, consulting company, Pune (2002 to 2006).

What Esther sees before the call

Four lines on the CV interest Esther: the parallel run, “to the cent”, the control totals, and ten years of on-call. Tidewell bills customers from this data, and the last incident was a double bill.

She also notices a thought in her own head: “Is he too senior for this pay band? Will he be bored?” She stops it there. That is Vikram’s decision, not hers, and the question often hides a bias about age. Her job is to find out what he can do. She writes down three questions:

• After three years of leading, is he still hands-on? Can he still write and read the code?
• How does his reconciliation work in practice, and does it fit meter data?
• His tools are older than ours. Does he know the ideas under the new names?

About age and older tools

A good interviewer does not ask your age, and does not guess it from your CV. What they can check is whether your knowledge is current enough for the work. Older tool names are not a problem when you can explain the idea under them. The problem only appears when a candidate talks in tool names that the interviewer does not know.

Terms on this page

• Control totals: numbers that you compute at the source and again after the load, such as a row count and the sum of an amount. If they do not match, something was lost or added.
• Reconciliation: comparing two systems or two tables until every difference has a reason.
• Business date (or event time): the date that a record belongs to, not the date that it was loaded.
• High-water mark: the latest timestamp or ID that a load has already read, so the next run starts after it.
• Watermark (in streaming): how long a streaming job waits for late events before it closes a time window.
• Partition overwrite: replacing whole partitions of a table (here, whole days) with a new version.
• MERGE: one SQL statement that updates rows that already exist, matched on a key, and inserts the others.
• Kafka exactly-once: Kafka features (an idempotent producer and transactions) that stop duplicates when a producer retries, and let a job that reads from Kafka and writes to Kafka do both as one unit. The idempotent producer is on by default in recent versions, so “turning on exactly-once” usually means transactions.
• Checkpoint: a record that a streaming job keeps of what it has already processed, so it can restart where it stopped.

The interview

Minutes 0 to 5: the opening

Esther(interviewer):

Hi Vikram, good afternoon. Sorry, I’m two minutes late, my last meeting was long. So, I’m Esther, senior data engineer on the Data Platform team. I work mostly on billing data. The plan: first a pipeline you built or own, and I’ll go deep with questions. Then one of our real problems. Then your questions. And no AI tools in this round, please. Paper is fine, and there’s a whiteboard if you want to draw.

Vikram(candidate):

No problem at all. Good, I always think better with paper. I have it here.

Minutes 5 to 25: the pipeline, then deeper

Esther(interviewer):

So, tell me about a pipeline you know well.

Vikram(candidate):

I will take the card transactions load at Westmark, because I owned it for many years, on the old platform and the new one. So. Every night, the card system sends a file with the day’s transactions. Around 15 million rows, more in December. We load it, check it, and the next morning finance, risk and the regulatory reports use it.

On the old platform, Informatica read the file and loaded Teradata. The mapping had a lookup on the account dimension, then an update strategy transformation, then…

Esther(interviewer):

Sorry, can I stop you? I don’t know Informatica well. What did it do, in simple words?

Vikram(candidate):

Ah, sorry. Yes. In simple words: read the file, check each row has a valid account, and write the rows for that business date into the table. And before that, check the file trailer. The card system writes the row count and the total amount at the end of the file. We compare with what we loaded. If it is different by one cent, the load stops.

Where this goes wrong

Vikram started to explain his work through the tool: mappings and transformations in Informatica. Esther does not know the tool, so she cannot judge the work. He fixes it quickly when she asks. If you have deep experience with older tools, describe the idea first (“read, check, write by business date”). Name the tool only if the interviewer asks.

Esther(interviewer):

Okay, that’s clear. And if the job fails halfway, how do you re-run it?

Vikram(candidate):

We always delete the whole business date and load it again. Never append. I have learned that in 2011, in a painful way.

Esther(interviewer):

Tell me about 2011.

Vikram(candidate):

The card system had a problem, and they sent the same day’s file again, with a new file name. Our job saw a new file and appended it. So that day was in the table two times. Interest was calculated on it, for about three thousand accounts, I think. Maybe a bit more.

The control totals caught it the next morning, before statements went out to customers. But one regulatory report had already gone. We had to send a corrected report, with a letter. Not a good week.

Esther(interviewer):

Quiet for a second. I was on call for our double billing last month. I know that feeling.

Vikram(candidate):

Then you know. After that, two changes. One, a load replaces the full business date. Two, we keep a register of every file, with a checksum. If the same content comes again, with any name, we stop and ask a person.

Why this works

This is an incident story with everything Esther needs. What happened, how it was found (control totals), what the damage was, and what changed so it cannot happen again. “Replace the full business date” is partition overwrite. Vikram knew the idea 15 years before the new name.

Esther(interviewer):

Okay. Now the migration. How did you know the new platform was correct?

Vikram(candidate):

We ran both for four months. Every morning, a job compared each table, per business date: row count, sum of amounts, and some counts by product. Any difference went on a list, and somebody had to give a reason.

Esther(interviewer):

What kind of differences?

Vikram(candidate):

Mostly small. The famous one was one cent. One Spark job read amounts as double, not decimal. Smiles. The engineer who found it was very happy. We fixed the type and added a check on column types.

Esther(interviewer):

And were you writing the code yourself? Or leading?

Vikram(candidate):

Both. The reconciliation jobs, I wrote myself in PySpark. In the last year, less code, more reviews and planning. I’m honest, I am slower in PySpark than in SQL. SQL I have written for 24 years.

Esther(interviewer):

Okay, that’s fair.

Esther also asked about cost after the move from Teradata. The first cloud bill had surprised them: many queries read every file in the table, because the analysts did not filter by date. The team partitioned by business date and asked people to always filter on it. The bill went down by “a bit more than half, I think”. Vikram did not remember the exact number, and said so.

Minutes 25 to 40: 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?

Vikram(candidate):

One question first. “Billed twice”. The readings were two times in the table, or the bill was generated two times?

Esther(interviewer):

The readings. The re-run appended the same arrival date again, and billing summed them.

Vikram(candidate):

Okay. So, same as my 2011. The job thinks in load date, but billing thinks in business date. Here the business date is the reading date. So the clean table is by reading date, and every night we rebuild the reading dates that can still change. Delete and load the full day. Never append.

Esther(interviewer):

Mm-hm. How do you know which reading dates can still change?

Vikram(candidate):

For each source, we keep a high-water mark. Then…

Esther(interviewer):

A watermark, you mean? Like in Spark streaming?

Vikram(candidate):

Hmm. Maybe not the same thing. Let me explain what I mean, then you tell me. For me, the high-water mark is the latest arrival time that we have already loaded. The next run reads everything after it. Then I look at which reading dates are in those new rows, and I rebuild those dates.

Esther(interviewer):

Ah, okay. Then it’s different. In Spark streaming, a watermark says how long a job waits for late events before it closes a window. Our stream doesn’t use one. It only appends raw readings. Sorry, I jumped.

Vikram(candidate):

No, it’s good, now I know your word. So mine is “what have I already read”, and yours is “how long do I wait”. For billing we need both, I think. But the waiting can be in the batch.

Two words that sound the same

A high-water mark tracks progress by arrival: “I have read everything up to here”. A streaming watermark is about event time: “I stop waiting for events older than this”. They sound alike and are related, but they answer different questions. Esther assumed they were the same. Vikram did not argue about the word. He explained what he meant, and they found the difference together. When you are not sure that a term means the same thing to both people, describe it in plain words.

Esther(interviewer):

Okay. And for the rebuild itself. In Delta, would you use MERGE or overwrite the days?

Vikram(candidate):

I have not written MERGE on Delta. On Teradata, many times. I expect the idea is the same: match on meter ID and reading time, update if it exists, insert if not. But here I prefer to replace the days. Easier to reason about. After the job, the day is exactly what raw says, after dedup. With MERGE, if the source has the same key two times, it goes wrong. On Teradata it was an error, I think. Here I don’t know.

Esther(interviewer):

Only if the key is already in the table. Then it says multiple source rows matched. If the key is new, both rows go in, and nobody sees it.

Vikram(candidate):

Then that’s worse. So, dedup first, always: one row per meter and reading time.

Esther(interviewer):

Okay. Now, something we are discussing. The platform team wants to turn on exactly-once in Kafka. Then the duplicates go away, end to end, I think. So maybe we don’t need the dedup later.

Vikram(candidate):

Hmm. Can I disagree a little?

Esther(interviewer):

Please.

Vikram(candidate):

Kafka’s exactly-once is about Kafka. The producer does not write the same message twice when it retries. And with transactions, a job that reads from Kafka and writes back to Kafka can do both together. It’s good, we used it. But our duplicate last month, where did it come from?

Esther(interviewer):

From the nightly batch re-run.

Vikram(candidate):

Yes. That job is not in Kafka at all. Exactly-once cannot see it. And the head-end. If it loses connection and sends the same reading again, for Kafka these are two different messages. Kafka cannot know they are the same reading. Only the key knows: meter and reading time.

Esther(interviewer):

And the stream from Kafka into the raw Delta table? That one is exactly-once, with the checkpoint.

Vikram(candidate):

Yes, while the checkpoint is safe. If someone deletes the checkpoint and starts the job again from an earlier offset, so that nothing is lost, it writes the same rows again. At the bank, this happened once, after a cluster change. So I would say: exactly-once in Kafka is good to have. But the table must protect itself. Dedup or MERGE by key, every time we write.

Esther(interviewer):

Pause. Okay. You’re right. I was thinking only about the streaming part, from Kafka into raw. For billing, the key is what protects us. I’ll say that to the platform team too.

Disagreeing well

Esther made a claim that many engineers believe: Kafka exactly-once removes duplicates “end to end”. Vikram asked first if he could disagree. Then he did not lecture. He asked where last month’s duplicate came from, and let Esther answer. His points are correct:

• Kafka’s guarantees cover writes inside Kafka.
• A reading that the head-end sends again is a new message.
• A batch re-run is outside Kafka.
• A table is only safe if every write uses the key.

He ended with what to do, not with “you are wrong”. Esther agreed, and said exactly where her thinking stopped. See Interviewers are not always right.

Vikram then asked how many readings come after three days. Very few, Esther said, except after a big storm: maybe one or two percent of meters, for up to a week. Then billing should not wait for them, he said. Bill with what we have, estimate the missing hours, flag them as estimated, and correct on the next bill. He hoped that billing operations, not the data team, decides the rules for the estimate. Esther confirmed it: Rosa’s team.

Minutes 40 to 50: reconciliation and the edge

Esther(interviewer):

And how would you know that the data is right before billing runs at 5 a.m.?

Vikram(candidate):

Control totals, like the bank. Give me one minute, I want to draw it.

He writes on paper for about a minute. Esther waits.

Vikram(candidate):

Okay. Three points to check, every night, per reading date. Raw, clean, and the billing input. At each point, two numbers: the count of readings and the total kWh.

Esther(interviewer):

And between raw and clean?

Vikram(candidate):

The count goes down only by the duplicates and the rejected rows, and we log both numbers. Clean to billing must match exactly. And a third check: expected readings. 1.2 million meters times 96, so about 115 million a day. Except the two clock-change days.

Esther(interviewer):

92 and 100.

Vikram(candidate):

Yes. If the difference has no reason, billing waits, and a person decides. Billing data must reconcile to the cent. At the bank we had this every day, and I would not run billing without it.

Esther(interviewer):

The duplicates and the rejects, as numbers every day. I like that. If it jumps, something upstream changed.

Vikram(candidate):

Exactly. That number told us twice that the card system had a problem, before they told us.

Why this is the strongest answer of the day

Vikram turns “is the data correct?” into checks that run every night. Every number has a reason to change, and a difference without a reason stops billing. The daily count of removed duplicates would have caught last month’s incident. This is what “24 years” means in practice.

Esther(interviewer):

One more on the stream. The forecast team asked us for hourly totals directly from the stream, with a three-day wait. What happens to the state in Spark?

Vikram(candidate):

Sorry, can you say the question again? The state of what?

Esther(interviewer):

The state the streaming job keeps. The open window for each meter and hour, while it waits for late readings.

Vikram(candidate):

Ah. Honestly, I don’t know Spark’s streaming state well. I have not run Structured Streaming in production. Our streaming at the bank was only from Kafka into raw tables. My guess: it keeps every open window until the watermark passes, so three days of windows for 1.2 million meters. I’d want to measure how big that gets before I trust it.

Esther(interviewer):

That’s roughly right. It keeps the open windows in a state store, and it can get big.

A stronger answer at the edge

Vikram was honest at his edge, and his guess was close. He could go one step further with what he already knows:

“Do we need that state at all? If the stream only appends raw readings, and the nightly batch rebuilds the reading dates that changed, the late data is handled in batch. Then the stream does not wait three days for anything. I would keep the stream simple, and put the waiting where it is cheap.”

When you reach a tool feature that you do not know, ask if the design needs it at all.

Esther then asked about the meter swaps. Vikram saw a history problem at once: at the bank, he kept account history as a type 2 dimension, with valid-from and valid-to dates. Because the CRM keeps no history, he asked for another record of swaps, and Esther named the field work orders. On the AI assistant, he asked one question: “Will its numbers match the bill? If the assistant says 410 kilowatt-hours and the bill says 412, the customer calls Rosa’s team.” He suggested that the assistant read from the same checked table as billing.

Minutes 50 to 57: Vikram's questions

Vikram(candidate):

I have three. First: when the head-end changes its export format, how do you hear about it?

Esther(interviewer):

Small laugh. Usually when the job fails. The vendor doesn’t tell us.

Vikram(candidate):

Okay. That I have seen before. Second: at 5 a.m., if the checks fail, who can decide to delay billing? The engineer on call, or someone in billing?

Esther(interviewer):

Good question. Today… it’s not written down. Last month I called Rosa at home.

Vikram(candidate):

I would write that down early. The person on call should not decide alone. Last question. What does the team need most in the next year?

Esther(interviewer):

Honestly? Someone who has run billing-grade data before. We are good engineers, but most of us came from analytics.

Questions from experience

Vikram’s questions come from incidents he has lived through, and each answer tells him something real: no warning on format changes, and nobody owns the 5 a.m. decision. His last question lets Esther say what the team needs. Her answer matches his experience, and he did not have to say it himself.

Minutes 57 to 60: the close

Esther(interviewer):

Thank you, Vikram. Really. And thanks for the Kafka point, I mean it. Aisha will contact you about the next rounds.

Vikram(candidate):

Thank you. And good luck with the platform team. Smiles. Show them the batch re-run first.

After the call: Esther’s notes

Vikram Rao, pipeline design deep dive

• Re-runs and late data: strong evidence. Replaces full business dates since 2011, after a real incident. Rebuilds only the reading dates that changed. Dedup by key before any write.
• Data quality and reconciliation: strong evidence. Control totals at three points, an expected count with DST, the counts of removed duplicates and rejected rows as a daily signal. Best answer on this topic so far.
• Kafka and streaming: some evidence. Corrected me, correctly, on exactly-once. Has not run Spark Structured Streaming in production and said so.
• Current tools: some evidence. Hands-on PySpark for the reconciliation jobs. Has not used Delta MERGE, knows the pattern well. Some older words (high-water mark), explained clearly.
• Operations and on-call: strong evidence. Ten years leading on-call. Asked who can delay billing at 5 a.m.: a gap we have.
• Cost: some evidence. Partitioning and filters after a cloud bill surprise. Did not remember the number, and said so.

Recommendation: strong yes. He brings what we lack most: billing-grade discipline. The Delta and streaming details he will learn in weeks. The judgement took him 24 years.

What happened next

Pierre’s data modelling round was strong. Vikram drew the account-to-meter history as a type 2 dimension without help and asked good questions about the grain of the forecast table. Rosa’s round was a clear yes. She wrote: “He talks about the customer’s bill to the cent. And he asked who decides at 5 a.m. before I did.”

In the debrief, one person asked, carefully, whether Vikram would be happy in a role without people management. Graham said that the team should not guess. Aisha asked Vikram directly on the next call. He said that he wanted to be hands-on again, and that he had chosen this level on purpose.

There was one vacancy, and Graham made the offer to Vikram: Data Engineer, CAD 142,000 plus bonus. Vikram thanked him and asked for one change: the title Senior Data Engineer. He gave his reasons. He would own the reconciliation design, lead incident reviews, and support the on-call rotation from the start. Those are senior responsibilities at Tidewell. Graham checked with HR and agreed. He also moved the pay to CAD 148,000, still inside the posted range. Vikram accepted.

He asked for what matched the work, and explained it with what the team needed. This is one realistic ending, not a promise. Another company might have read the same CV differently. Luck is part of it.

What to take from this

• Your experience is evidence, not age. Vikram never talked about his years. He talked about the 2011 incident, the parallel run and the one-cent difference. The years showed in the answers.
• Describe the idea, then the tool. “Replace the full business date” makes sense to everyone. “An update strategy transformation” makes sense to few people today.
• When a word may mean something different, explain yours. The high-water mark and the watermark were different ideas, and plain words found that in two turns.
• Be precise at the edge. “I have not written MERGE on Delta; on Teradata many times” is honest and useful at the same time.
• Negotiate with the work, not with yourself. Vikram asked for the title because of the responsibilities, and he said which ones.

If your profile is like Vikram’s

1. Make a small dictionary from your old words to the new ones. Business date and event time; delete-and-reload and partition overwrite; high-water mark and incremental cursor; control totals and data quality checks. Practise saying the idea first, in plain words.
2. Do one hands-on project with a current table format. Write a MERGE and a partition overwrite on Delta or Iceberg, run them twice, and compare the totals with checks written as code. Then you can say “I have done it” instead of “I know the pattern”. See Deliver a reusable dataset and Validation rules.
3. Learn where streaming guarantees stop. Read how Kafka transactions and a streaming checkpoint work, and write one paragraph about what they do not protect. Most interviewers will not expect this, and it shows judgement.
4. Turn your incidents into short stories. What happened, how you found it, the damage, what changed. Two or three are enough. See Explain decisions.
5. Keep your CV focused on the last 10 to 15 years. Put early roles in one line. This is not hiding; it puts your strongest evidence first. See Portfolio anatomy and Your experience is a head start.