The fastest way to lose a data engineering screen in 2026 is to answer a backfill question with a plain INSERT. Rerun that job after a downstream timeout and you’ve billed every customer twice. The person across the table isn’t checking whether you can define Spark. They want to know whether your pipeline stays correct when it runs again, out of order, on last week’s partition.
Plenty of question banks still open with Hadoop, Hive, and MapReduce internals. Cloud-native teams stopped asking years ago. The rounds at Snowflake-and-dbt shops now test SQL you’d actually write, pipeline judgment, and dimensional modeling that holds up when the business changes its mind. Tools get named directly: Airflow or Dagster for orchestration, dbt for transforms, Iceberg or Delta sitting on object storage, Snowflake or BigQuery as the warehouse.
The rounds you’ll actually sit
A typical loop runs four or five stages. A recruiter screen to confirm you’ve shipped pipelines and know the stack. A live SQL round, usually 45 minutes with two or three problems that escalate from a join into window functions. Then either a take-home or a pipeline-design conversation where you talk through an ingestion job end to end. A data modeling round that looks like system design pointed at a warehouse. And a behavioral round where “tell me about a pipeline that broke in production” is doing more screening than it looks.
The questions are phrased plainly. “Given an events table, find users active three days in a row.” “Here’s a dimension whose values change over time. How do you store the history?” “Design the job that loads Stripe charges into the warehouse, and tell me what happens when it dies halfway through.” None of these have a single right answer. They have a set of follow-ups the interviewer keeps pulling until you hit the edge of what you actually know.
SQL that goes past a simple join
The SQL round weeds out more people than any other stage, and it’s rarely about join syntax. It’s window functions, deduplication, and the date arithmetic behind sessionization. A standard opener: you have a users table with multiple rows per user and you want the latest record for each.
select *
from (
select *,
row_number() over (
partition by user_id
order by updated_at desc
) as rn
from raw.users
) ranked
where rn = 1;If you reach for distinct or a self-join on max(updated_at), you’ll survive, but row_number() is the answer they’re listening for because it generalizes. The follow-up is usually the consecutive-days question, which is the gaps-and-islands pattern in disguise: subtract a row_number() from the date, group by the difference, and count the runs. Candidates who’ve seen it once solve it in three minutes. Everyone else reinvents it live, which is exactly the signal the interviewer wants.
Expect at least one question where the naive query is correct but slow, and the real ask is why. Know that a where filter on a partitioned table should prune partitions, that a join on a high-cardinality key can explode a row count, and that count(distinct ...) across a shuffle is where your Spark job goes to die.
Idempotency, or why your backfill can’t double-count
This is the question that separates people who’ve run pipelines from people who’ve only built them. Phrased as “what happens if this job runs twice,” it’s really asking whether your writes are idempotent. Append-only INSERT fails the test. A MERGE keyed on a natural or surrogate id passes, because the second run updates rows it already wrote instead of duplicating them.
merge into analytics.orders t
using staging.orders s
on t.order_id = s.order_id
when matched then update set amount = s.amount, status = s.status
when not matched then insert (order_id, amount, status)
values (s.order_id, s.amount, s.status);The other pattern they want to hear is partition overwrite: process one day at a time, and on rerun replace the whole day’s partition rather than adding to it. Both make backfills safe, which matters because every pipeline gets backfilled eventually, whether from a bug, a late schema change, or a source that resent six months of data. Parameterize the job by date or watermark, make reruns replace rather than append, and cap concurrency so a wide backfill doesn’t take down the warehouse you share with the analysts.
Modeling: SCD Type 2 and the grain you keep re-explaining
The modeling round starts with grain. Before you draw a single table, say what one row means: one order, one order line, one user-day. Get this wrong and every downstream number is wrong in a way that’s painful to trace. Interviewers probe it because juniors skip it and seniors lead with it.
Then comes slowly changing dimensions, the most reliably asked modeling topic in the field. A customer moves from the free tier to paid. You need last quarter’s revenue attributed to the tier they held at the time, not the one they hold now. Type 2 is the standard answer: keep every version of the row with effective_date, expiry_date, and an is_current flag, and join fact rows to the version that was live on the event date.
| SCD strategy | What a change does to the dimension row | History preserved? | When to use it |
|---|---|---|---|
| Type 0 (fixed) | Ignores later updates; the original value is retained forever | No changes tracked | Immutable attributes such as date of birth or original signup source |
| Type 1 (overwrite) | Overwrites the old value in place with the new one | No, the prior value is lost | Corrections and typo fixes where the old value carries no analytic meaning |
| Type 2 (add new row) | Inserts a new versioned row and closes the old one with an expiry date and current flag | Yes, full history | Attributes you report on as-of a point in time, such as plan tier or region |
| Type 3 (add new column) | Keeps a “previous value” column beside the current one | Partial, one prior value only | When you only ever compare against the immediately prior state |
The trap in the Type 2 answer is the join. A fact table joined to a Type 2 dimension on the natural key alone fans out across every version of that dimension member. You either join on the surrogate key captured at load time, or you join on the natural key with a between effective_date and expiry_date predicate. Say which and why, and you’ve cleared the round.
Pipeline design and the follow-ups that sort levels
The design question sounds open: load Stripe charges into the warehouse. The junior answer stops at “pull the API, write to a table.” The senior answer plans for the ways it goes wrong. Late-arriving data, so you process by event time with a watermark and a grace window instead of trusting arrival order. Schema evolution, because Stripe will add a field and you’d rather widen a column than get paged. Idempotent writes, so a retry costs nothing. Monitoring that checks row counts and freshness rather than just the job’s exit code.
Delivery semantics come up here. At-least-once delivery plus an idempotent sink gives you effectively-once results without the cost of true exactly-once, and saying that out loud tells a senior interviewer you’ve felt the difference in production. Where you put the logic matters too. Business transformations belong in dbt models you can test and version, not buried inside an Airflow operator where nobody can find them later. The DAG should orchestrate and stay thin.
The lakehouse questions that replaced Hadoop trivia
The storage questions moved from HDFS block sizes to columnar formats and table formats. Know why Parquet is columnar: analytic queries touch a few columns out of many, and column layout lets the engine read only those, then push the where filter down so it skips row groups whose min and max can’t match. Know the small-files problem, where a streaming job that commits every few seconds leaves thousands of tiny files that wreck read performance until you compact them. Over-partitioning by a high-cardinality column creates the same mess.
Iceberg and Delta come up because they fix what raw Parquet on a bucket cannot. Snapshot isolation so readers never see a half-written commit, time travel to query the table as of an hour ago, and schema evolution that adds columns and widens types without rewriting files. On Snowflake the equivalent conversation is micro-partitions and clustering keys, and whether a query prunes partitions or scans the whole table. A candidate who can read a query plan and point to where the scan happens has usually debugged a slow one for real.
Data quality closes a lot of loops. dbt tests for uniqueness, non-null, and referential integrity, plus freshness checks on source tables, are the baseline interviewers expect, because a pipeline that runs green while emitting wrong numbers is worse than one that fails loudly. The engineer they want is the one whose job they can rerun at three in the morning without stopping to think about what it will do the second time.
Drill the patterns next:
