Data Engineer Practice Problems
Data engineer practice problems are the tasks a data engineering interview asks you to solve live: SQL over messy tables, Python that reshapes raw records, data models with a stated grain, pipeline designs that survive late data and reruns, and Spark jobs that have to scale. The best ones run against hidden data and come with the edge case that fails most first attempts, so this guide works 1 real problem from each family through to the answer and shows how to practice the rest.
What data engineer practice problems cover
A data engineering interview loop asks for 5 kinds of work, and practice problems are worth your time only when they train those 5. SQL comes first and appears in nearly every loop: queries over tables that hold duplicates and NULLs, with ties and late rows mixed in, where the interviewer checks that the result is right for every row, not only the sample. Python is the second coding round, and in a data engineering loop it is about records rather than algorithms: parsing and deduplicating them, then grouping and reshaping them with the standard library.
Data modeling problems ask you to design tables for a business process: pick the grain and the keys, and decide how each dimension keeps history. Pipeline design problems ask how data gets from a source to a consumer on time and stays correct when jobs rerun or events arrive late, and when a source changes. Spark problems show up at companies that run Spark, and they test how the engine shuffles and partitions data and how it handles joins and skew.
DataDriven holds {COUNT:total} problems across these rounds: {COUNT:sql} in SQL, {COUNT:python} in Python, {COUNT:data_modeling} in data modeling and {COUNT:pipeline_architecture} in pipeline architecture, all asked in real data engineering interviews. Each section on this page works 1 of them through to an answer and names the edge case that fails most first attempts.
Know data engineer practice problems the way the interviewer who asks it knows it.
SQL practice problem: keep the latest row per key
For each account status in a users table, return the most recently signed-up user with their id, username, email and signup date, keeping the larger user_id when 2 accounts tie on the latest date. This shape is the most common SQL problem in data engineering because CDC feeds and event logs need it before anyone can use the data, and so does every slowly changing source. The pattern is a window function that numbers each status's rows newest first, then a filter that keeps row 1. GROUP BY account_status with MAX(signup_date) looks shorter, but the other columns then come from no particular row, so the username can belong to a different user than the date.
The edge case is the tie. When 2 rows for 1 key share the same timestamp, ROW_NUMBER() still gives them 1 and 2, but which row gets 1 is up to the engine, so the query can return a different row on each run. The PostgreSQL documentation defines the behaviour: rows equal on the ORDER BY columns are peers, rank() gives every peer the same rank, and row_number() simply counts. Swap in RANK() and a tied key comes back twice. The fix is a tiebreaker that is unique within the key, user_id DESC here, so the order is total and the answer never changes.
The same query also handles exact duplicate rows: 2 identical rows for 1 key still number 1 and 2, so only 1 survives. When an interviewer asks why you chose ROW_NUMBER() over RANK() or DENSE_RANK(), the answer is this pair of cases. The RANK vs DENSE_RANK vs ROW_NUMBER practice drills the difference, and the SQL practice problems cover the patterns that build on it.
How a tiebreaker settles the latest row per key
WITH ranked AS (
SELECT
customer_id,
city,
updated_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC, update_id DESC
) AS rn
FROM customer_updates
)
SELECT customer_id, city, updated_at
FROM ranked
WHERE rn = 1
ORDER BY customer_id;The same technique on a small change log: customer_updates holds 7 rows for 3 customers, customer 103 has 2 updates at the same minute and customer 102 has 1 row loaded twice. Remove update_id DESC from the window to see the tie become unstable, or swap ROW_NUMBER for RANK to see customer 103 return twice.
SQL practice problem: find streaks of consecutive days
Given a logins table, return each user's streaks of consecutive login days with the start and length of each. This is gaps and islands, and a data engineer meets it in many forms: consecutive days active, unbroken runs of sensor readings, periods a subscription stayed paid. A self join or a loop gets there slowly. The pattern interviewers look for takes 1 window function.
Number each user's login days in order, then subtract the row number from the date. Within an unbroken run the date rises by 1 per row and so does the row number, so the difference stays the same; after a gap the date jumps while the row number does not, so the difference changes. For a user who logs in on days 1 to 3 and again on days 5 and 6, the difference is 0 for the first 3 days and 1 for the last 2, which splits the 5 logins into a 3 day streak and a 2 day streak at the missing day 4.
Call that difference the anchor. Group by it and each group is a streak. In PostgreSQL, subtracting an integer from a date gives a date, so the anchor is a real date and MIN(login_date) names the start of each streak:
WITH days AS (
SELECT DISTINCT user_id, login_at::date AS login_date
FROM logins
),
islands AS (
SELECT
user_id,
login_date,
login_date - CAST(ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS int) AS anchor
FROM days
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_days
FROM islands
GROUP BY user_id, anchor
ORDER BY user_id, streak_start;The edge case is the DISTINCT in the first step. A user who logs in twice on 1 day gets 2 row numbers for 1 date, the difference shifts by 1 and the streak breaks in 2. Deduplicate to 1 row per user and day before numbering, and cast timestamps to dates in the time zone the business reports in. The SQL window functions practice has more problems built on this trick.
The same idea finds runs of rows that share a state, not only runs of dates. Number every health check per service, number the healthy ones again, and the difference between the 2 numbers stays fixed through each unbroken healthy run. Try it on the longest healthy streak across a set of services:
Longest Uptime Streak
> We monitor a fleet of services that each log periodic health checks, and we want to crown the single steadiest one: the service with the longest unbroken run of 'healthy' checks, where casing doesn't matter. A run ends the moment a service logs anything other than healthy, and if two services tie for the longest run, the one whose name comes first alphabetically takes the title. Return that service's name and the length of its streak.
Python practice problem: group events into sessions
Click events arrive as (user_id, timestamp) pairs in no particular order, and each event needs a session number, where a session ends after 30 minutes with no activity. It is a typical data engineering Python round, with no clever algorithm but a dozen ways to get the details wrong. The solution sorts by user and time, walks each user's events once, and starts a new session whenever the gap since the previous event passes the limit.
from datetime import timedelta
from itertools import groupby
from operator import itemgetter
GAP = timedelta(minutes=30)
def sessionize(events):
"""Yield (user_id, ts, session) for (user_id, ts) pairs in any order."""
ordered = sorted(events, key=itemgetter(0, 1))
for user_id, rows in groupby(ordered, key=itemgetter(0)):
session, last_ts = 0, None
for _, ts in rows:
if last_ts is not None and ts - last_ts > GAP:
session += 1
yield user_id, ts, session
last_ts = tsTake 1 user with events at 09:00, 09:10, 09:55, 10:05. The gaps run 10 minutes, then 45, then 10, so only the 45 minute gap passes 30, and the 4 events form 2 sessions. Write the example out loud before coding; interviewers weigh the reasoning as much as the code, and the example is where you agree on what the boundary means.
The edge cases are the ones an interviewer will ask about next. A gap of exactly 30 minutes stays in the session because the test is >; say which you chose and why. Input that arrives unsorted is why the function sorts. A file too large for memory changes the answer: let the warehouse or an external sort order the data, and the generator then holds 1 event at a time, so memory stays flat however many events stream through it.
The Python interview questions cover the follow-ups this round brings, from sorting cost to generators, and the Python practice problems drill parsing, grouping and deduplicating records.
Data modeling practice problem: a ride-sharing warehouse
Design the warehouse tables for a ride-sharing app so analysts can report trips and cancellations, plus fares, by city and by the driver's rating band at the time of each trip. Interviewers judge data modeling answers on 3 decisions, in this order: the grain of the fact table, the keys that join it to each dimension, and how each dimension keeps history.
State the grain in 1 sentence before drawing a table: 1 row for every trip request whether it completed or was cancelled. That choice decides everything after it. A grain of completed trips makes the cancellation rate impossible to compute, and a grain of trip events (requested, accepted, started, ended) makes every fare sum a filter away from double counting.
The phrase "at the time of each trip" is the history requirement. A driver's rating band changes, and a report on last quarter must use the band the driver had then. That makes dim_driver a type 2 slowly changing dimension. Each change adds a row with a new surrogate driver_key, and valid_from and valid_to bound that version. The fact table stores the key of the version that was current when the trip was requested. Riders and cities change rarely enough to overwrite in place, and a date dimension serves calendar reporting.
The checks to run aloud before you call the design done: can every question in the prompt be answered with 1 join per dimension, does any fare get counted twice, and what happens to a trip whose driver changes band the same day. The data modeling interview questions rehearse defending those choices, and the data modeling practice problems are answered on the same schema canvas an interview uses.
Pipeline practice problem: an incremental load that loses rows
An hourly job copies new and changed rows from the orders table in the application database to the warehouse by reading every row whose updated_at is later than the watermark the last run stored. Analysts report that a few orders a day never reach the warehouse. Find the cause and fix it without reloading the table every hour.
The cause is the gap between when a row is stamped and when it becomes visible. A transaction stamps updated_at = 09:58 but commits at 10:03. Run 1 at 10:00 cannot see the uncommitted row and stores a watermark of 10:00. Run 2 at 11:00 reads only rows after 10:00, so the row stamped 09:58 is never read by any run. Clock skew between application servers and rows updated by a backfill script that preserves old timestamps lose rows the same way.
The fix has 2 parts that only work together. Read with an overlap: start each run at the watermark minus a lookback of 15 minutes, longer than the longest transaction you expect, so run 2 reads from 09:45 and finds the late row. Then write with a MERGE on the primary key, so the rows read twice update in place instead of loading as duplicates. Overlap without the merge double counts; the merge without the overlap still loses rows. When the lookback cannot be bounded, the answer is change data capture from the database log, which records each change in commit order.
Interviewers like this problem because it tests the 2 properties every pipeline answer needs: no row is lost, and a rerun changes nothing. The pipeline architecture practice problems are built around the same failures: late data and duplicate deliveries, plus backfills and reruns.
Keep Most Recent Record
> Our users table records each account's status and signup date, and we only want one representative per status. For each account status, return the single most recently signed-up user with their id, username, email, and signup date, keeping the larger user id if two accounts tie on the latest date.
Pipeline design practice problem: clickstream to a dashboard
Design the pipeline that turns web and app click events into a product analytics dashboard showing sessions and conversion by page. The dashboard can lag live traffic by at most 15 minutes. Some events arrive hours late from phones that were offline, and malformed events must not stop the pipeline.
A strong answer starts from the requirements. 15 minutes of freshness rules out a nightly batch but doesn't require per-event processing, so a stream into durable storage plus a micro-batch every few minutes meets it at lower cost. Late events mean sessions must be recomputed for the hours a late event touches, which is a reason to partition the session table by hour and rebuild partitions instead of appending. Malformed events go to a dead letter queue with the reason attached, so 1 bad client release cannot block every other event.
Name the guarantees out loud: the queue retains events long enough to replay a day, the raw layer keeps every event as received so any table can be rebuilt, each load is idempotent so a retry rewrites the same partition, and a quality gate stops a broken batch before it reaches the dashboard. A design that states its freshness target and how it handles replay and failures beats a longer list of tools every time.
The data pipeline interview questions ask for these guarantees out loud, and the data pipeline practice problems are designed on the pipeline canvas, where each answer is checked for freshness, replay and failure handling.
A clickstream pipeline with a 15 minute freshness target
Events land in Kafka and are parsed into a raw Iceberg table, with malformed events routed to a dead letter queue. A Spark micro-batch rebuilds the hourly session partitions a batch touches, so late events update the right hour and a retry rewrites the same partition; a quality gate holds a broken batch back from the dashboard.
Spark practice problem: a join stuck on 1 task
A PySpark job joins a 100 GB events table to customers on customer_id. 199 of the 200 tasks in the join stage finish in a minute and 1 runs for an hour. Explain why and fix it. The cause is skew: 1 enterprise customer owns 30 GB of the events, and a sort-merge join sends every row for that customer to the same partition.
Put numbers on it. With the default of 200 shuffle partitions, the other 70 GB spread to a median of about 360 MB per partition, while the hot partition holds 30 GB, about 85 times the median. The Spark performance tuning guide describes the first fix, which costs nothing: adaptive query execution, on by default since Spark 3.2.0, treats a partition as skewed when it is larger than 5 times the median and larger than 256 MB, and splits it across several tasks. This partition passes both tests, so on a current Spark version check the plan for a skew join before changing any code.
If customers is small, the second fix removes the shuffle: a broadcast join copies the small table to every executor, and Spark does it by itself for tables under the 10 MB default of spark.sql.autoBroadcastJoinThreshold. When both sides are large and AQE is off or not enough, salt the key by hand. Add a random salt from 0 to 15 to each event, copy each customer row once per salt, and join on both columns:
from pyspark.sql import functions as F
SALTS = 16
events = events.withColumn("salt", (F.rand() * SALTS).cast("int"))
salts = spark.range(SALTS).withColumnRenamed("id", "salt")
customers = customers.crossJoin(salts)
joined = events.join(customers, ["customer_id", "salt"])The hot key now spreads over 16 partitions of about 1.88 GB each, and the cost is a customers table 16 times larger. Interviewers follow up with exactly that trade: salt only the keys you know are hot, or the copy of the small side outgrows the problem it solved.
A common version of this problem has a dimension just over the broadcast threshold, where the fix is 1 explicit broadcast() hint rather than salting. Diagnose and fix one here:
Fix Skewed Viewing Events Pipeline
> You are the on-call data engineer at a streaming company and the nightly `viewing_engagement` Spark job just paged you: it normally finishes in 45 minutes but has been running for over two hours and is still stuck. The job joins a large `event_data` table (800M rows/day of viewing, playback, and interaction events) against a small `users` dimension (2M subscribers) on `user_id`, then produces daily engagement counts by event type and account status, and its SLA is 60 minutes. Read the Spark UI evidence to find the root cause and fix the job so it meets SLA.
The PySpark interview questions ask about shuffles, joins and skew the way interviewers follow up on a slow job, and the PySpark practice problems run your code on a real Spark engine.
How to practice data engineering problems so it carries into the interview
Solve under the conditions of the round. Set a timer of about 20 minutes for a SQL or Python problem and 40 for a design, talk through your plan before you write, and do not run the code until you believe it is right. An interview rarely gives you 10 runs to converge on an answer, and the habit of checking your own logic first is what the round measures.
Before you submit, run the edge cases in your head: an empty input, a NULL in the join key, 2 rows tied on the sort column, a duplicate row, a key with 1 row. Most wrong answers in data engineering rounds return plausible numbers on the sample and wrong ones on real data, and these 5 cases find nearly all of them.
Read a reference solution only after you have a working answer or have spent the full time, and then compare approaches rather than syntax. Come back to the problems you missed 3 or 4 days later and solve them again from a blank editor. A pattern you can rebuild from nothing is one you will recognise in a new problem. The SQL interview questions pair well with this: they ask you to explain the same patterns in words, which is the other half of a SQL round.
More data engineer practice problems with answers
Return the top 3 products by revenue in each category, keeping ties.
Aggregate revenue per product first, then apply DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) and keep rows with a rank of 3 or less. DENSE_RANK keeps every product tied at a position without skipping the next rank; ROW_NUMBER would cut ties arbitrarily, and RANK would skip ranks after a tie, so a category could return fewer than 3 distinct revenue levels.
Compute each user's 7 day rolling count of orders when some days have no orders.
Join the users to a date spine first, so every user has a row for every day with a count of 0 where nothing happened, then apply SUM(order_count) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Without the spine, the 6 preceding rows are the last 6 days that had orders, which can stretch over weeks.
Find customers who placed an order in January but none in February.
Use an anti join: select January customers, then drop anyone with a February order, using NOT EXISTS or a LEFT JOIN on customer_id that keeps rows where the February side is NULL. Avoid NOT IN against a subquery that can return a NULL customer_id, because 1 NULL makes NOT IN return an empty result.
Flatten nested JSON records into rows with dotted column names.
Write a recursive function that walks each dict and joins the keys with a dot, so {"user": {"id": 7}} becomes {"user.id": 7}. Decide up front what a list does: explode it into 1 row per element, or keep it as a JSON string. Collect the union of keys across records, because records missing a key must still produce the column, filled with None.
Deduplicate a stream of events that can arrive more than once, without holding the whole stream in memory.
Keep a set of event ids seen within a bounded window, such as the last hour of event time, and evict ids older than the window as time advances. Memory is bounded by the events per window, not the stream. Say the trade aloud: a duplicate that arrives after the window passes through, so the window length comes from how late duplicates arrive in practice.
Model orders where 1 order holds many products and each product can appear in many orders.
Set the fact table's grain at 1 row per order line keyed by order_id and line_number. Its foreign keys go to the product and customer dimensions and to a date dimension. Order level values such as shipping cost either go in a separate order fact or are allocated across the lines by a stated rule, because repeating them on every line double counts them in any sum.
When would you choose a type 1 dimension over a type 2?
Type 1 overwrites the attribute, so history is lost and every report shows today's value; it suits corrections and attributes nobody reports on historically, like a fixed typo in a name. Type 2 adds a version row per change, so facts keep the value they had at the time; it suits attributes that reports slice by, like a customer's region or plan.
A daily job failed for 3 days. How do you backfill it safely?
Make each run process exactly 1 date partition chosen by a parameter, not by the current clock, and make it overwrite that partition rather than append. Then rerun the 3 dates oldest first and check row counts per partition against the source. A job written this way can be rerun any number of times with the same result.
Why does a Spark job write thousands of tiny files, and how do you stop it?
Each task writes at least 1 file per output partition it holds, so 200 shuffle partitions writing into 50 date partitions can produce up to 10,000 files. Repartition by the output partition column before the write so each date is written by few tasks, or coalesce to a sensible count, and compact tables that receive small appends over time.
How to tell you are ready for the interview
Count patterns, not problems. A data engineering SQL round draws on about 8 patterns. Deduplication and top N per group belong on that list, as do running totals with moving windows. So do gaps and islands and sessionization, and the rest are pivots and anti joins, plus period over period change. You are ready for the round when you can read a problem you have never seen, name its pattern within 2 minutes, and finish in about 20 with the edge cases handled.
The same test works for the other rounds. For Python, it's writing a parser and a deduplicator, then a group-by, from a blank file without looking anything up. For data modeling, it is stating the grain and the history rule of any business process in 2 sentences. For pipeline design, it's explaining how your design behaves on a rerun or a late event, and during a backfill, before the interviewer asks.
When the patterns hold, move from single problems to full rounds, because pacing across 4 or 5 rounds in 1 day is its own skill. The data engineer interview prep guide lays out each round of the loop and what it checks, so you can see which of your patterns still need work.
Data engineer practice problems FAQ
Where can I find data engineer practice problems?+
Are LeetCode problems enough for a data engineer interview?+
How many practice problems should a data engineer solve before interviewing?+
What SQL topics come up most in data engineer interviews?+
Do data engineer interviews include Spark problems?+
How do I practice data modeling and pipeline design problems?+
The candidate who gets the offer
- 01
Reading a solution is not the same as writing one
Every engineer who has frozen on a query they had read a dozen times knows the gap. The only preparation that closes it is producing the answer yourself, under time, before the interview does it for you
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
5 problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition
Related guides
SQL practice problems
SQL problems for data engineers, run against hidden data
SQL interview questions
The SQL questions data engineering interviews ask, answered
Data engineer interview questions
The questions each round of the loop asks, with the traps that cost points
Data engineer interview prep
Every round of the loop and how to prepare for it
Pipeline architecture practice problems
Design problems on late data and on reruns and backfills
SQL window functions practice
Ranking with ROW_NUMBER and RANK, plus LAG and running totals, practiced
The weekly data challenge
Dirty, production-shaped data, scored blind each week.