The BI developer

Think of the person in a company who builds the reports that the finance team opens every Monday. When a number looks strange, people call this person first. Over the years, they learn where every number comes from, which tables lie, and which business words mean three different things. This is a BI developer (business intelligence developer).

Many data engineers start here. A BI developer already writes SQL all day, designs warehouse tables and tests numbers against the source. What is often new is streaming, very large volumes, and tools like Kafka and Spark. So they apply with a question: “Will they see me as only a report person?”

Fatima is this candidate. English is her third language, after Arabic and French. Watch how little that matters to the interview, and how much her experience with history in data matters. Watch also the end of her story: she decides too, not only Tidewell.

FZ

The candidate

Fatima Zahra El Idrissi

BI developer and analytics engineer, 9 years, moving into data engineering

• 9 years in BI and data
• SQL Server, dbt, Snowflake
• In Canada 5 years
• Vancouver

• Analytics Engineer, Cedarline Retail (Vancouver, 2024 to now). Moved about 140 reports from SQL Server to dbt and Snowflake. Own the nightly sales models for about 300 stores.
• Rebuilt the daily sales load after a double-count incident: incremental merge on the order line key, a 3-day window for late store uploads, reconciliation tests against the POS totals.
• BI Developer, Assurances Belvédère (Montreal, 2021 to 2024; Casablanca office, 2017 to 2021). SSIS packages and a SQL Server warehouse for policies and claims. Power BI for the actuarial and finance teams.
• Designed the policy dimension with full history (SCD type 2), used by the actuaries for 6 years of pricing studies.
• Skills: SQL (expert), dbt, Snowflake, SQL Server, SSIS, Power BI, Python (pandas), Airflow (basic), Kafka (familiar).
• Languages: Arabic, French, English.

What Esther sees before the call

Esther Adeyemi reads the CV next to the job description. The SQL and modelling evidence is strong. One line makes her smile: a double-count incident, fixed with a merge. Tidewell had almost the same incident last month.

The gaps are also clear: no Spark, and much smaller volumes than 115 million readings a day. And “Kafka (familiar)” can mean many things. Esther writes down what she wants to find out:

• How deep is the double-count story? Does she know why the fix works, and what it still does not cover?
• Does her experience with history (SCD type 2) turn into a design for the meter-swap problem?
• What does “familiar” with Kafka mean? Where does her knowledge stop, and what does she do there?

Terms on this page

• Idempotent: you can run the job twice, or ten times, and the result is the same as after one run.
• MERGE (upsert): one SQL statement that updates a row if its key already exists and inserts it if not.
• Natural key: the columns that identify a real thing, for example meter ID plus reading time. Not a number that the database invents.
• Partition: a part of a table, often one day of data, stored separately so a query can read only the days it needs. Kafka also has partitions; they are different, and we explain them in the interview.
• Offset: in Kafka, the position number of a message inside one partition.
• SCD type 2 (slowly changing dimension, type 2): a table that keeps every version of a record, each with a start date and an end date, so you can ask “what was true on 3 March?”
• dbt incremental model: a dbt model that processes only new or changed rows on each run, instead of rebuilding the whole table.
• Delta table: a table format on cloud storage (used by Databricks) that adds transactions, MERGE and time travel to files.

The interview

Minutes 0 to 5: the opening

Esther(interviewer):

Hi Fatima, good afternoon. I’m Esther, senior data engineer on the platform team. So, the plan. First you tell me about 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, but paper is fine, and I’ll use the whiteboard a lot. I like boxes.

Fatima(candidate):

Okay, good. One thing before we start. English is my third language. Sometimes I think in French and I need a second to find the word. I will tell you if that happens.

Esther(interviewer):

That’s completely fine. Take the time you need. I listen for the thinking, not the words.

Saying it at the start

Fatima does not apologise. She tells Esther what may happen and what she will do. Now a pause to find a word looks like what it is: a pause.

Minutes 5 to 25: the pipeline, then deeper

Esther(interviewer):

So, tell me about a pipeline you built or own.

Fatima(candidate):

Okay. At Cedarline, the daily sales load. We have around 300 stores. Each store sends a file from the cash registers every night, all the sales lines of the day. It lands in a stage in Snowflake, and then dbt builds the sales tables, and finance reads the dashboard Monday morning.

The problem was… we had a bad Monday. Sunday sales were double. Six million dollars instead of three. The finance director saw it before us. That was not a good morning.

Esther(interviewer):

Laughs. I know that feeling. What caused it?

Fatima(candidate):

The model was incremental, but append only. Airflow passed the business date, and the model inserted all lines for that date. Sunday night, a task after it failed. Someone cleared the whole run and started it again. So the model inserted Sunday a second time.

What the interviewer is checking

Esther wants to see if Fatima understands the cause, not only the symptom. “A re-run appended the same data again” is exactly Tidewell’s double-billing incident. Fatima explains it in four short sentences, with the real mechanism: an append, a date filter, and a person who re-ran the whole thing.

Esther(interviewer):

And what did you change?

Fatima(candidate):

I changed it to merge. The unique key is the order line ID from the register. So if the same line comes again, it updates, it does not insert. And the filter takes the last three days, not only yesterday, because some stores send late. Small stores in the north, sometimes the network is down. One time a store sent a whole week at once.

Esther(interviewer):

So with a three-day window, that week…

Fatima(candidate):

Yes, the old days are outside the window. The model does not see them. We have a test for this. Each morning it compares the total per store and per day in our table with the total in the raw files. If it is different, the test fails and I get a message. Then I run a backfill for that store, by hand. With the merge, the backfill is safe, I can run it two times, nothing happens.

Esther(interviewer):

Okay. One detail on the merge. What if the same order line is two times in the new data? Not in the table, in the new file.

Fatima(candidate):

Ah, yes, that happened! If the line is already in the table, the merge fails. Snowflake says, how do you say, “duplicate row detected”… something like that. But if it’s a new line, it’s worse. It inserts both, no error. So before the merge I keep one line per key. I take the latest one, with ROW_NUMBER, by the file time.

Esther asked about size and cost. Fatima thought the sales table had “around two million lines a day, more in December”, and about one and a half billion rows in total, but she was not sure. Her manager had asked about cost: the merge first scanned the whole table, so she added the date to the merge condition. Snowflake then read only the recent days, and the run went from about 25 minutes to 4 or 5.

Why this works

This is a strong pipeline story, and not because it is big. Every part has a reason:

• a merge on a natural key, for re-runs;
• a 3-day window, for late files;
• a reconciliation test, for what the window misses;
• deduplication before the merge, and a date in the merge condition, for cost.

She also knows the limit of her fix: late files outside the window still need a person.

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?

Fatima(candidate):

Smiles. This is my Monday morning, but with electricity.

Esther(interviewer):

Yes, a bit. How big is it? We have 1.2 million readings a day. No, sorry, 1.2 million meters. The readings are about 115 million a day.

Fatima(candidate):

Okay, 115 million. So, first, the date. Reading by arrival date is the problem. The reading has its own time, from the meter. I would partition the clean table by the reading date, not the arrival date. Then a late reading goes to its real day. And the nightly job reads everything that arrived since the last run. Then it looks which reading dates are inside. Mostly yesterday, but also some days before. And it rebuilds only those days. With a merge on meter ID and reading time, like my order line.

Esther(interviewer):

Merge, or overwrite the whole day?

Fatima(candidate):

Hmm. Both can work. Overwrite the day is simple, and it’s idempotent. But then the job must have all the readings of that day, from the raw table, not only the new ones. If I overwrite with only the new ones, I lose the old ones. Merge is safer if the input is only the new rows. I think I’d choose merge, and put the reading date in the condition, like for cost.

Why this works

Fatima sees the trap in “overwrite the partition”: it is only safe if you rebuild the day from complete input. Both answers are correct with the right input. Choosing one and saying why is what Esther wants.

Esther(interviewer):

Good. Now the raw side. Before the nightly job, readings come from Kafka. A streaming job reads Kafka and writes the raw Delta table. Your CV says Kafka “familiar”. What does familiar mean?

Fatima(candidate):

Laughs. It means I did a course on the weekend, and a small test at home. I never ran it in production. Honestly, I know the idea: producers write messages, consumers read. But the details, no.

Esther(interviewer):

Okay, thanks for being clear. Then let me ask it like this. Imagine the streaming job crashes in the middle. When it starts again, how does it know where to continue?

Fatima(candidate):

I don’t know how Kafka does this. Can you explain it a little? Then I try to reason.

Esther(interviewer):

Sure. A Kafka topic is split into partitions. Each partition is a log, like a file where you only add at the end. We use the meter ID as the key, so all readings of one meter go to the same partition. Every message in a partition has a number, the offset. 0, 1, 2, and so on. The consumer saves the last offset it finished. After a crash, it starts from the saved offset.

Fatima(candidate):

Okay. So, a bookmark in a book. Pause. Then the order is important. If it writes to Delta first, and then crashes before it saves the bookmark… after the restart, it reads the same messages again. So we have duplicates.

Esther(interviewer):

Yes. And the other order?

Fatima(candidate):

It saves the bookmark first, then crashes before the write. Then… the readings are lost? No, wait. They are still in Kafka. But the job thinks it finished them, so it never reads them again. For us it’s lost. And nobody knows. So I would write first. Duplicates I can find, and the merge on meter and time removes them anyway. A lost reading I cannot find, because I don’t know it exists. Only later, when the customer has a gap.

The edge, and what Fatima does there

This is the edge of Fatima’s knowledge. She does not invent an answer. She asks for an explanation, finds an everyday picture (a bookmark), and reasons to the correct rule: write first, save the position after. That gives you at-least-once delivery, so the next step must remove duplicates. Esther learned more here than from a quick correct definition.

Esther(interviewer):

That’s right. In our job, Spark keeps the offsets in its own checkpoint folder, and the Delta write and the checkpoint work together, so a normal restart is safe. But our re-run last month was the nightly batch job, not streaming. Kafka can’t help there.

Fatima(candidate):

Okay. One question. The late readings, from a meter without network. In the Kafka partition, where are they? In the right place by time?

Esther(interviewer):

No. They are at the end, when they arrive.

Fatima(candidate):

So the order in Kafka is the arrival order. We must always take the time from inside the reading. Never from Kafka, never from the file.

A stronger answer at the edge

Fatima reasoned well. To go one step further, she could connect it to the size of the table and to waiting:

“If late readings can come three days later, the nightly job must look back at least three days of reading dates. That is about 115 million times 3, so around 350 million rows in the merge window. That’s big for me, much bigger than my sales table. I would ask how long the Spark job takes today, before I promise anything.”

This shows the cost of her own design, and it names her gap in a useful way.

Esther(interviewer):

Now billing. Billing runs at 5 a.m. If readings are three days late, what does billing do?

Fatima(candidate):

Billing must close the bill at some point. It cannot wait forever. So… sorry, I lost my point. Let me start again.

Esther(interviewer):

Sure, no problem.

Fatima(candidate):

Billing needs a rule: wait how many days? Three, five? After that, it uses an estimate for the missing readings, and it marks them as estimated. When the real readings come later, the next bill corrects the difference. But this is a business rule. I would ask the billing team, not decide it myself. They know what the regulator says.

Esther(interviewer):

Good. That’s Rosa’s area; you meet her later. One more. The forecast team sees gaps and jumps when a meter is swapped. The new meter gets a new ID. The link between account and meter is in a CRM table, and the CRM keeps only the current state.

Fatima(candidate):

So when the meter changes, the old link is overwritten? Then this is a history problem. It’s my SCD type 2 from the insurer. The policy changed, and the actuaries needed to know what was true on each date. Here, one row per link: account, meter, valid from, valid to. When a meter is swapped, we close the old row and open a new one. Then the readings join to the account where the reading time is between from and to.

Esther(interviewer):

How do you build that history if the CRM doesn’t keep it?

Fatima(candidate):

We take a snapshot every day. In dbt there are snapshots for this. But two problems. First, the history starts the day we start. Before that, we must find it somewhere else. Is there a system for the field work, the swap orders?

Esther(interviewer):

Yes, field work orders. They have a date and a time. But the time is not always the real swap. Sometimes it’s when the crew closed the order.

Fatima(candidate):

Still better than the snapshot. A daily snapshot only knows the day. And… we have the readings. The old meter stops sending, the new one starts. So the readings can tell us the real time, and we compare with the work order. If they don’t agree, someone checks.

Why this is the best part of the interview

Esther never said “slowly changing dimension”. Fatima heard “only current state” and saw a history problem at once, because she has solved one before. Then she found both weak points of a daily snapshot herself: no history before the first day, and only day precision. And she used the data itself as a second source: the readings show when the old meter stopped.

Esther(interviewer):

Last thing, quickly. How would you know the table is correct every morning? Before billing.

Fatima(candidate):

Like my sales test. Per meter per day, we expect 96 readings. Fewer means a gap, more means duplicates. Also no negative values, and the reading time not in the future.

Esther(interviewer):

Always 96?

Fatima(candidate):

Oh. No. The time change. In March the day has 23 hours, so 92. In November 25 hours, 100. So the test must count by the local day and know the two special days. In UTC every day is 96, but billing thinks in the local day.

Esther then asked briefly about the customer-service AI assistant. Fatima thought first about the customer on the phone: the assistant should show “estimated” next to estimated readings, and use the same business definitions as the bill, “or the customer hears two different numbers”. Esther asked if billing needs streaming. Fatima said no: billing runs once a day, and correct matters more than fast. She was less sure about the assistant: “Maybe one hour old is fine? I would ask the customer service team.”

Minutes 50 to 57: Fatima's questions

Fatima(candidate):

I have three questions. First, the on-call. Aisha said one week in seven. What does a bad week look like?

Esther(interviewer):

Billing runs at 5 a.m., so if the nightly job fails, the phone rings at 4. I was on call for the double billing. I worked until noon, then we called the customer. Honestly, most weeks are quiet. Some are not.

Fatima(candidate):

Thank you, that’s important for me. Second: the CRM team. Do you have an agreement with them, about the format? Or can they change the table when they want?

Esther(interviewer):

Pause. No agreement. They change it, and we find out when something breaks. It’s on our list.

Fatima(candidate):

Okay. And the last one, more practical. What is the timeline for the decision? I am in another process also, and they are at the end.

Esther(interviewer):

Thanks for telling me. I don’t know exactly. You still have the modelling round with Pierre and the conversation with Rosa. Please tell Aisha about the other process today. She can try to move faster.

Questions that decide something

Each question gives Fatima a fact that she needs to decide: what on-call really means, a team with no data agreement, and a timeline. The last question is not rude. Telling the company that you have another process is normal and useful for both sides. An interview is a conversation in both directions.

Where this could go wrong

Fatima asked about the timeline only at the end of the third interview. If she had asked Aisha in the recruiter call, Tidewell could have planned the rounds faster. Ask about the timeline early, and say early if another company is ahead.

Minutes 57 to 60: the close

Esther(interviewer):

Thank you, Fatima. I enjoyed this, really. The bookmark, I will use that with the new people.

Fatima(candidate):

Laughs. You can. Thank you for explaining Kafka. I will read more tonight.

After the call: Esther’s notes

Esther writes her notes the same afternoon, next to the job description.

Fatima Zahra El Idrissi, pipeline design deep dive

• SQL and data modelling: strong evidence. Saw the meter-swap problem as SCD type 2 before I named it. Found both weak points of a daily snapshot herself, and proposed the work orders, checked against the readings.
• Idempotent pipelines and re-runs: strong evidence. Led a real double-count fix: merge on a natural key, deduplication before the merge, a date in the merge condition, reconciliation tests. Knows what the fix does not cover.
• Late data: strong evidence. Partition by reading date, rebuild the affected days, a window plus a test for what the window misses.
• Data quality: strong evidence. 96 per meter per day, and corrected herself on daylight saving time.
• Kafka and streaming: no evidence of experience. Said so clearly. With an explanation, reasoned correctly to “write first, save offset after”.
• Spark at our volume: no evidence. Her largest table is about 2 million rows a day. She will need time and support here.
• Business sense: strong evidence. Wants billing and customer service to set the waiting rule and freshness. Asked the right questions about on-call and the CRM.

Recommendation: strong yes. The gap is Spark and Kafka. Those are easier to teach than her modelling judgement. Pair her with someone on the streaming job for the first two months. Note: she is in another process at the final stage. Please move fast.

What happened next

Fatima told Aisha about the other process that same evening. Pierre did the modelling round two days later and wrote “the best grain discussion this year”. The conversation with Rosa was booked for the next week, the first time Rosa was free.

But Tidewell was not only waiting for Rosa. Graham had one vacancy and several strong finalists, and one of them still had two rounds left. He wanted to compare everyone before he decided.

Four days before Rosa’s interview, the other company made Fatima an offer: a data engineer role at a logistics company in Burnaby, CAD 152,000, with an answer needed in three days. Aisha asked Graham if Tidewell could decide earlier. Graham said he could not decide fairly in three days. Fatima accepted the other offer. She wrote to Aisha and Esther:

“Dear Aisha, dear Esther, thank you for the interviews. I learned a lot, especially about Kafka. I accepted another offer today, because they could decide earlier and the role is a very good fit for me. This was not an easy choice. I really liked the team and the meter problem. I hope we meet again. Fatima”

Esther answered with one line: “Good luck, and please stay in touch. The bookmark is now in our onboarding notes.” Two weeks later, Tidewell made its one offer to another finalist.

Fatima did nothing wrong. She chose a good offer, at the time it came. Tidewell lost a strong person because its process was slower. Both sides decide, and both sides can say no. See an interview is a conversation in both directions.

What to take from this

• Your old field is often the answer. Fatima solved the meter-swap problem with a pattern from insurance. Look for the problems in the new company that you have already solved somewhere else.
• A small pipeline with clear reasons beats a big one with none. Her table was 50 times smaller than Tidewell’s. It did not matter, because every decision had a reason she could explain.
• At the edge, ask, then reason. “Can you explain it a little? Then I try to reason” is a strong sentence. It turns a gap into a conversation.
• Say what is not your decision. The waiting time for billing is a business rule. Saying “I would ask the billing team” shows judgement, not weakness.
• Manage your own timeline. Tell the company early if you are in another process, and ask how long their process takes. Then you can decide well when an offer comes.
• Your English can be simple. Esther wrote down Fatima’s ideas, not her grammar.

If your profile is like Fatima’s

1. Write one pipeline story with every reason in it. For your biggest load, answer: what happens if it runs twice? If two runs overlap? If data comes late? If the same key is twice in one batch? How do you know it is correct each morning? See Explain decisions.
2. Learn Kafka by the questions interviewers ask. You do not need production experience to understand partitions, keys, consumer groups and offsets. Run a small local Kafka in Docker, kill your consumer in the middle, and see the duplicates with your own eyes.
3. Try Spark at a bigger size. Load a public dataset of a few hundred million rows, partition it by date, and run a MERGE on the last three days. Write down how long it takes.
4. Turn your history work into a general pattern. Practise SCD type 2 on a new domain: meters, employees, prices. Write the join with “valid from” and “valid to”, and test the edges of the time range. Our lesson on time and ranking covers the window functions you will use.
5. Practise data quality rules out loud. For any table, say the expected count, the impossible values and the exceptions, like the daylight saving days. See Validation rules.
6. Ask about the timeline in the first call. Our lesson Prepare for applications helps you keep track of several processes at once.