Data Engineer Take-Home Examples

The NYC Department of City Planning data engineering challenge gives 48 hours and asks for no more than 5 hours of work. You load the last 7 days of 311 service requests handled by the housing agency into sample_311 and the 2020 Neighborhood Tabulation Areas into ntas_2020. From those you build an hourly table of created_date_hour and complaint_type, plus count, and assign each request to its neighborhood with spatial SQL. The export is a CSV and a shapefile, along with a line chart and a choropleth map. PostgreSQL and DuckDB are both allowed.

The grading list is unusually explicit. It covers project layout and reproducibility on another machine. It asks for clean, readable code with documented functions and a README with instructions, and it wants the project run through issues and pull requests. Command-line parameters and a Docker image earn a bonus. Nothing in it rewards a clever query that only runs on your laptop.

The phrase "last 7 days" causes the most trouble. Computed from the clock at run time, it gives the reviewer a different dataset from yours, so your numbers cannot be checked. Take the window's end date as a parameter that defaults to today, write the date you used into the README, and keep the raw download in data/ so the aggregation can be rerun on the same rows. For the spatial join, check that both layers share a coordinate reference system before joining, and count the requests that fall in no neighborhood instead of dropping them silently.

SpotOn's prompt is the classic ingestion take-home. You pull 4 files of the Brazilian e-commerce dataset from the Kaggle API, customers and products plus orders and their order items, land them in object storage such as MinIO or S3, and load each into its own Postgres table with Singer or plain Python. The acceptance test is 1 command: docker-compose up must build the whole thing, and the tables must be queryable on localhost:5432.

The deliverable that separates submissions is nextsteps.md, where you say how you would deploy the pipeline and what changes at scale. The prompt allows shortcuts as long as you write them down with the production alternative, which is the right instinct for every take-home: hard-code the bucket name if you must, and say that a real deployment would read it from configuration.

The failure to plan for is a first run on a clean machine. The Kaggle API needs credentials, Postgres needs a moment to accept connections, and a loader that starts before the database is ready fails on the reviewer's first try. Pass credentials through an environment file you document but never commit, wait for Postgres to be healthy before loading, and make the load safe to run twice so a second docker-compose up does not double every row.

Upshot's prompt asks for a few hours of work, submitted within 1 week as a git bundle. You extract competitions and their teams from the football-data.org API and save the raw responses to a partitioned folder. Then you load dim_teams into PostgreSQL alongside dim_competitions and fact_competitions, and export a CSV of team counts per competition in descending order, all behind Docker Compose.

It names what it judges. It ranks readable, maintainable code over performance and asks how the pipeline would be deployed and integrated. The rest of its list is about you: how well you analysed the requirements and how clearly you write, with simplicity counted too. It expects error handling and logging backed by tests, and it says any tool is acceptable as long as you can explain why you chose it.

The API's free tier allows 10 calls per minute, so a loop that asks for the teams of every competition hits the limit partway through. Space the calls to stay under it and back off and retry on HTTP 429. Write each raw response to disk before parsing it, so a crash halfway through resumes from the files you already have instead of calling the API again. That 1 decision gives you the rerun and the partitioned raw layer the prompt asks for, and it is most of the error handling too.

Streamlabs splits its take-home in 2. The first problem gives a schema of desktop streaming events and asks for MySQL 5.7 functions that return the numbers behind 4 reports. Monthly and daily active users come from stream_start and app_start events; the other 2 are daily new users and cohort retention. The functions return the aggregated data behind each report, not formatted reports.

Every one of those metrics hides a definition you must choose and write down. Is a user active on a day with only an app_start? Is a new user someone whose first event of any kind falls on that day, or whose first stream_start does? Does retention in week 2 mean active in exactly week 2, or at any point after it? State each rule in a comment above the function, because the reviewer judges the reasoning as much as the SQL.

MySQL 5.7 is a real constraint here: it has no window functions and no common table expressions, both of which arrived in MySQL 8.0. A first-event-per-user query must use GROUP BY with MIN() and a join back, which is exactly what the prompt is checking you can do. The README also has to say how long the work took and what was hardest or unclear, along with whether the difficulty felt right, so keep notes as you go.

An analytics and automation team publishes a take-home capped at 2 hours. Amazon musical instrument reviews sit in S3 as CSV and JSON. You read them into PySpark or an AWS Glue DynamicFrame, drop rows that repeat an earlier row's reviewerId together with its asin and reviewTime, and write 2 tables. 1 is partitioned by hour and relates helpfulness and rating to the time of day. The other has 1 row per product, giving average and median rating alongside unique reviewers and summary length. Both must be queryable in Athena.

The prompt says the follow-up conversation weighs your approach and decisions above finishing every feature, which tells you how to spend a short budget. Get the deduplication exactly right, because every number downstream depends on it, then build the product table, then the hourly one. Decide which copy of a duplicate survives, and say why: identical rows make the choice free, but rows that share a key and differ in a rating need a rule, such as the latest load wins.

The same keep-1-row-per-key pattern is written in SQL as ROW_NUMBER() over the key, ordered by the load time, and in PySpark as a window with row_number() or as dropDuplicates() on the key columns when any surviving copy will do. More problems of this shape are in the ETL practice problems.

Some take-homes ask for a design instead of, or as well as, running code. Streamlabs' second problem is 1 of them: move data from 3 separate databases, paid_user_subscriptions and stream_logs plus 1 for donations, into a warehouse built to analyse why paid users churn. The deliverables are the job scripts and an architecture diagram, along with the cloud tools you would use.

A design exercise is judged on whether each component answers a requirement you stated. Start from the question the warehouse must answer, here which paid users cancel and what their streaming and donation activity looked like beforehand, and derive the grain from it: 1 row per user per month, with subscription state sitting beside that month's hours streamed and donations received. Then work backwards to how often each source must be copied, where raw copies land so a bad transform can be replayed, and where a check stops a broken load before it reaches the churn model.

The same method answers a feature-specific architecture prompt, such as the data flow behind a feature that shows renters how a listing's price changed over time: name the question the feature answers, fix the grain that answers it, then decide where that grain's data comes from and how it is stored, and how fresh it has to be. Rehearse this shape of answer with the data pipeline interview questions, which ask for the same trade-offs out loud.

The README is read before any code, often by someone deciding in a few minutes whether to open the rest. Lead with 1 paragraph that says what the pipeline does and what it produces, then the exact commands to run it and its tests from a clean clone, with the versions you used. If the reviewer has to guess a step, the submission already reads as unfinished.

Then give the decisions. List every assumption the prompt left open (the date window, how duplicates are resolved, how a refund is dated) with the rule you chose. Name 1 or 2 trade-offs, such as SQLite over a server database at this size, and what would change your mind. Close with what a production version would add, such as scheduling with retries, monitoring and secrets handling, and how the pipeline would scale. That's exactly what SpotOn's nextsteps.md and Upshot's deployment question ask for.

Keep it honest about gaps. A section that names the unfinished requirement and what you would do next reads far better than a reviewer finding the gap on their own, especially if it also says what you tried.

Use the tools the prompt names, then the smallest ones that do the job. A few million rows fit on a laptop in pandas or polars, or in DuckDB, and run in seconds. Spark brings a JVM and its configuration, plus minutes of startup that the reviewer pays for on every rerun. Reach for Spark when the prompt asks for it, as the 2 hour reviews prompt does, and otherwise say in the README where the design would move to Spark as the data grows.

A take-home is not the place to show every tool you know. Adding an orchestrator and a message queue, with a container per step, turns a 3 hour task into a setup problem for the reviewer. A single Compose file with 1 entry point and 1 test command shows more judgment. If a production version would need an orchestrator, describe it in the README along with the quality gates and monitoring it would add; the data observability guide covers the checks worth naming.

Follow the prompt's rule on AI coding assistants, and if it has none, ask. Whatever you use, the follow-up conversation will go through the code, so keep only what you can explain and change on the spot.

A take-home rewards the same skills as the live rounds, practised without a clock in your face. You need SQL that handles duplicates and dates and Python that loads and validates data, and the result should be a rerunnable pipeline over a schema with a stated grain. The fastest preparation is to build 1 of the published prompts in its stated time, then read your own README as the reviewer would.

For the pieces, work through data pipeline practice problems, which exercise ingestion and deduplication plus the incremental load logic these prompts test. The take-home assignment guide covers where the take-home sits in the interview loop and how the follow-up conversation about it runs.