Data Engineer Interview Questions and Answers

Idempotency means running the job twice produces the same table as running it once. I get there by writing to a deterministic partition or key and using a merge or a delete-then-insert rather than a blind append. For batch jobs I key the run by business date, so a backfill of the same date overwrites rather than duplicates. For streaming I store offsets or watermarks in the same transaction as the output where possible, so a restart resumes exactly where it stopped. Then I test it, because idempotency claims fail in practice: I run the same job twice against the same input and compare row counts and checksums. I also make sure the business logic is deterministic, because a function that depends on the current time or a random value breaks the guarantee.

First I confirm the discrepancy with a query I write myself, because reports are often wrong for a simpler reason such as a filter someone added. Then I trace backward from the final table, comparing row counts and sums at each layer to find the first stage where the numbers diverge. The usual suspects are a join that fans out because the key is not unique on one side, duplicated rows from a non-idempotent load, late-arriving records that missed the window, or a filter on a timestamp column in the wrong timezone. I isolate the smallest input that reproduces the error and fix the transformation. Finally I add a test asserting the invariant I relied on, such as uniqueness of the join key, so the same class of bug fails the pipeline instead of reaching a dashboard.

I separate event time from processing time, because data that arrives today can describe an event from last week. I define an allowed lateness window with the business owner, and the pipeline reprocesses that window on every run rather than only the newest partition. For a daily batch job that means recomputing the last few days instead of only yesterday, which costs more but keeps the numbers stable. For streaming I use a watermark and either emit updates or maintain a corrected view. I make the reprocessing idempotent so a correction overwrites rather than duplicates. Finally I tell consumers what to expect, including whether today's figure can still move and by how much, because an unexplained revision destroys trust in the dashboard.

I start with attribution, because cost is invisible until you can see which queries and tables generate it. Most warehouses expose query history with bytes scanned, so I rank by total scan volume. The largest wins usually come from partitioning and clustering so queries read less data, removing SELECT star from scheduled jobs, and fixing dashboards that refresh every few minutes against a full table scan. Then I look for repeated expensive logic and materialize it once as a shared model, so ten dashboards do not each recompute the same join. I set budgets per team and review the top consumers with them, because a dashboard owner optimizes faster than a central team. I also check whether incremental models can replace full refreshes, since that is often the single largest saving.

I start from the decision the data supports. If the value is a daily report or a weekly cohort analysis, batch is simpler, cheaper and easier to reprocess, and streaming would add operational burden for no visible benefit. If the use case is fraud detection, live pricing or an operational alert, latency is part of the product and streaming is justified. I also weigh backfill: streaming pipelines are harder to replay, so I keep raw events in durable storage and treat the stream as a fast path that can be rebuilt. Before committing I check whether the consumer can tolerate a few minutes of delay, because micro-batch often delivers most of the benefit at a fraction of the complexity. I choose the simplest design that meets the real requirement.

I put tests where they catch the problem earliest. At the source I check that the expected files or topics arrived and match a schema. In the transformation layers I test invariants that must always hold, such as uniqueness of a primary key, referential integrity between facts and dimensions, accepted values in an enum column, and a non-negative amount. I prefer invariants to arbitrary thresholds, because a threshold like row count above a million breaks on a holiday and then gets muted. When a test fails I decide between failing the pipeline, which stops bad data from propagating, and quarantining the partition, which keeps partial data available. Either way the alert goes to the owning team with the failing row sample, not to a channel everyone ignores. I also track test coverage of core tables as a first-class metric.

I start from the questions the table needs to answer, because that determines the grain and the columns. I state the grain explicitly, for example one row per order per day, and I make sure every column is true at that grain; mixing grains in one table is what produces double counting later. I use conformed dimensions so the same customer or product key means the same thing across tables, and I keep the fact table additive so sums work without special cases. I avoid ambiguous column names and put the definition in the documentation and, where possible, in the column comment. I also think about the common filters and add partitioning or clustering on the columns analysts use most. Then I query it the way an analyst would, because a table that is clean but slow to use will simply get re-derived.

I start with discovery, because you cannot protect data you have not classified. I inventory the source systems and tag columns that contain personal or regulated data. Then I apply minimization: if an analyst needs an aggregate, I do not copy the raw identifier into the warehouse at all. Where the raw value is genuinely needed, I mask or tokenize it and keep the join key in a controlled dimension table, so the analytics layer never stores the direct identifier. Access is role-based with least privilege, and queries against sensitive tables are logged so we can answer who saw what. I also make sure deletion requests can propagate, which means knowing every derived table a record flows into. Privacy is easier to design in at the start than to retrofit after data has spread.

The first thing I did was not rewrite it. I looked at the run history and found that most failures came from two things: a source file that sometimes arrived late, and a join that broke when a new product code appeared. I fixed the late file by making the job wait on a data-arrival sensor instead of a fixed schedule, and I fixed the join by adding a default dimension row so unknown keys no longer drop facts. Then I added tests and alerting so the next failure notified the owner with the failing rows. After that I wrapped the riskiest transformation in a test that pins current behavior, so I could refactor it safely. Over a quarter the failure rate dropped and the business never lost a day of data, which a rewrite would almost certainly have cost us.

Add the tests and checks you built, since a pipeline that produces wrong numbers silently is worse than one that fails loudly.

Focus on the tables, pipelines and contracts you own, and mention dashboards only as consumers, because building charts is a different role.

Include a bytes-scanned, warehouse-spend or freshness metric, because these are the constraints platform owners are judged on.