# What to study first for a data engineering interview

Source: https://www.techinterview.org/post/3233476965/what-to-study-first-data-engineering-interview/
Updated: 2026-08-01 · techinterview.org

SQL is the filter. In most data engineering loops the first technical screen is a set of query problems, and if you write a join that quietly doubles your row count or a window function with the wrong frame, nothing else you prepared gets a chance to count. That's where prep starts, and everything else builds out from it.

The loop has settled into a familiar shape. A recruiter call, one or two SQL-heavy coding screens, a data modeling conversation, a pipeline or system design round, and a behavioral session. Smaller teams compress this into three meetings; a larger data org at a Snowflake-and-Databricks shop will run the full five, sometimes with a take-home in front. Knowing which version you're walking into changes how you budget your time.

## What each round is really checking

The titles on your calendar invite tell you the format, not the intent. A SQL screen is testing whether you notice a fan-out before the interviewer does. A system design round for data engineering is rarely about QPS and load balancers; it's about where data lands, how it gets transformed, and what happens when a job reruns. Here's the shape of a full loop and what sits behind each round.

| Round | Usual format | What they're testing | A question the way it's actually asked |
| --- | --- | --- | --- |
| Recruiter screen | 30-minute call | Fit, level, a clear story of past work | "Walk me through a pipeline you owned end to end." |
| SQL / coding screen | 45-60 min, shared editor | Query correctness, grain awareness, dedup, window logic | "Keep only the latest row per order_id from this raw table." |
| Data modeling | 45 min, whiteboard or doc | Dimensional design, grain, handling change over time | "Model orders, customers, and products for analytics. What's the grain of your fact table?" |
| Pipeline / system design | 45-60 min | Idempotency, backfills, late data, orchestration, failure handling | "Ingest clickstream events and make them queryable within five minutes. Where does it break?" |
| Behavioral | 45 min | Ownership, incident handling, working with analysts and PMs | "Tell me about a data quality issue you caught, or one you missed." |

## SQL: practice until fan-out is obvious

Two patterns account for most rejections at the query screen. The first is a many-to-many join that inflates a count, and the fix is knowing the grain of each table before you join anything. The second is reaching for a nested subquery when a window function is cleaner, or writing that window with the wrong partition or frame.

Deduplication comes up constantly because real source data is messy. The canonical version: you're handed a raw orders table with repeated rows from an at-least-once ingestion, and asked to keep the latest per key.


```
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM raw_orders
) t
WHERE rn = 1;
```


If that reads as routine, you're most of the way there. Push on the neighbors: running totals with SUM() over an ordered frame, ranking with RANK versus DENSE_RANK versus ROW_NUMBER and why the choice changes your output, sessionization where a new session starts after thirty minutes of inactivity, and gaps-and-islands for finding consecutive streaks. Practice these on a real dataset instead of reading about them. The skill you want is spotting the pattern inside a vague prompt, not reciting syntax.

CTEs matter for a reason that has little to do with correctness: interviewers read your query as you write it, and a short stack of named CTEs is far easier to follow than one deeply nested pile. Narrating "first I'll get one row per session, then aggregate" while your CTEs mirror exactly that is half the score.

## Data modeling: be ready to say why

Most candidates who fail the modeling round can draw a star schema. What they can't do is defend the grain. When someone asks you to model orders, customers, and products, the first thing out of your mouth should be the grain of the fact table, one row per order line, and every later decision follows from that. Get the grain wrong and the whole model wobbles.

The follow-up that separates people is change over time. A customer moves, or gets recategorized from SMB to Enterprise. If your reporting should still attribute old orders to the old segment, you need a slowly changing dimension that versions rows rather than overwriting them. Know type 2 well enough to sketch the effective-date columns and explain the tradeoff against a simpler type 1 overwrite. Analytics-heavy shops probe this hard, because it's where naive models quietly produce wrong history.

Star versus snowflake, fact versus dimension, additive versus semi-additive measures, when denormalization earns its storage cost against a normalized source. You don't need academic completeness here. You need opinions you can back with a concrete reporting query the model has to answer.

## Pipeline design: the questions are about failure

The design round for data engineering is a failure-handling interview wearing an architecture costume. Anyone can draw boxes for ingest, transform, and serve. The signal is in the follow-ups, and they're almost always about what happens when something goes wrong.

Idempotency is the idea they're circling. "You need to reprocess nine months of data. How do you avoid double-counting?" The answer lives in writes that are safe to repeat: partition the output by day and overwrite whole partitions on rerun, or use a merge keyed on a natural identifier, so running the job twice lands the same result as running it once. If your instinct is a plain append, you'll double your numbers, and the interviewer is waiting to see whether you catch it.

Late and out-of-order data is the next probe. Events for Tuesday show up on Thursday. Do you reprocess Tuesday's partition, hold a window open, or accept the lag? There's no single right answer, and naming the tradeoff out loud is the point. Same with upstream schema drift: "An API field flips from an integer to a string overnight and your job starts failing. What now?" Strong answers separate detection, schema checks or data contracts at the boundary, from response, quarantine the bad records and alert rather than silently coercing the type.

Orchestration tools come up by name now. Airflow is still the default reference, Dagster and its asset-based model show up more each cycle, and dbt owns the transformation layer at most modern shops. You should be able to talk about how retries and backfills work in whichever tool you've actually used, and be candid about which one that is.

## The system design round, sized for data

A typical prompt: ingest clickstream events and make them queryable within a few minutes. Sketch the path. Events land through a log like Kafka, a consumer writes raw records to object storage or a warehouse staging area, a transformation layer builds cleaned and modeled tables, and a serving layer answers queries. Then defend the hard parts. How do you partition so queries stay fast and reruns stay cheap. Where does deduplication happen when the source is at-least-once. How do you catch a day where the numbers look wrong before a dashboard shows them to an executive.

Batch versus streaming will come up. Resist the reflex to reach for streaming because it sounds impressive. Most reporting tolerates minutes of latency, batch is cheaper and simpler to reason about, and saying "I'd start batch and move only the two tables that need freshness to streaming" reads as judgment rather than resume-driven design.

## How to spend the weeks before the loop

Front-load SQL. For the first two weeks, do query problems daily until deduplication and window logic feel automatic, because that's the round that ends interviews earliest. Give the third week to modeling: take three products you know well and model each, then say out loud why the grain is what it is and how a changed attribute flows through the history. Spend the fourth week on pipeline and design questions, working through idempotency and backfills on paper until the failure follow-ups stop surprising you. Keep two or three behavioral stories warm the entire time, at least one about a data quality incident, since that question is close to guaranteed.

If mapping that onto your actual calendar is the hard part, the [study plan generator](/study-plan/) will spread the topics across the weeks you have and the hours you can spare. The sequence matters more than the hours: SQL that's still shaky in week four is a round you're going to lose, no matter how sharp your system design became.