Pipeline design, SQL depth, modelling and correctness under late-arriving data - practise the rounds a data engineering loop runs.
Data engineering loops test correctness under messy reality rather than raw algorithmic skill. The pipeline design round almost always turns on the same three questions: what happens when a record arrives late, what happens when the job runs twice, and how do you backfill without corrupting what is already there. Designs that assume clean, on-time, exactly-once input get dismantled quickly.
The SQL round goes considerably deeper than product analytics SQL. Expect window functions, correlated subqueries, deduplication with tie-breaking, gaps-and-islands problems and questions about why a query plan chose a particular join - along with what you would change about the table to make it faster.
Modelling questions test judgement about the consumer. Star schema versus wide denormalised table, partition and clustering keys, slowly changing dimensions, and where to draw the boundary between raw, cleaned and serving layers.
45-60 min
Window functions, dedup, gaps and islands, and query plan reasoning.
60 min
Batch or streaming ingestion, idempotency, late data, backfill and monitoring.
45 min
Schema for a given consumer, partitioning, and slowly changing dimensions.
45 min
Python or Scala data manipulation, sometimes a Spark transformation.
Deeper than most candidates expect. Window functions are assumed, not tested. The discriminating questions are deduplication with a tie-break rule, gaps-and-islands over event streams, and explaining why a plan chose a hash join over a merge join and what you would change.
Both, but know the seam. The questions that separate candidates are about event time versus processing time, watermarks, and what your pipeline does when a record arrives an hour late. Those come up whichever paradigm the role centres on.
Lightly. There is usually a coding round, but it leans toward practical data manipulation in Python or Spark rather than hard algorithmic puzzles. Time is better spent on SQL depth and pipeline correctness.
Design a pipeline that survives late data, replays and backfills.