Openingloading the content…

Loadingone moment…

Prep chapter 01

The Recruiter & Hiring-Manager Screen

The first filter: motivation, level fit, and a quick read on whether your projects match the role before any code is written.

What this round really tests

Whether you can explain what you have built, why you want this team, and at what level you operate — concisely and without rambling. Some companies (Amazon, Palantir) attach one substantive question here (a Leadership Principle or a mission-fit probe).

How to prepare

Have a 60-second project story: problem, scale, your decisions, outcome.
Then prepare the long version. Many loops add a project deep-dive, where the interviewer asks about one project for 20 to 45 minutes. The worked examples below show the order and the follow-ups.
Map your resume to the role; know the team and why it interests you.
Prepare comp expectations, notice period, and location up front.
For Amazon, prep one Leadership Principle story even for the screen.

Walk me through a project

The 60-second version opens the call. The long version is a round of its own at several companies. Say it in the same order each time, so you never lose your place.

Say it in this order. About two minutes in all.

Purpose. Why does it exist, and who uses it?
Source and size. Where does the data come from, and how much is there?
The steps. What happens to a row, from the source to the end?
Destination and schedule. Where does it land, and when does it run?
Failure and rerun. What happens when it fails, and when it runs twice?
One decision. What did you choose, what else could you have chosen, and why?
Result. What changed, in numbers?
What you would change. What is the weakest part, and what is next?

Three worked examples

The numbers are made up. These are examples written for teaching, not real projects. Copy the shape, then use your own project and your own numbers.

Daily orders into the warehouseA batch load, with Airflow and dbt

Purpose: Finance needed yesterday's paid revenue by country on a dashboard by 07:00. Before this, an analyst rebuilt it by hand in a spreadsheet each morning.
Source and size: The shop's Postgres orders table. About 400,000 rows are new or changed each day, out of about 90 million in total.
The steps: An Airflow task reads the rows changed since the last run, using an updated_at watermark, and writes them as Parquet files in S3, one folder per day. A second task loads that day into a staging table. A MERGE on order_id applies it to the orders table. Then dbt builds the revenue-by-country table and runs its tests.
Destination and schedule: A table called daily_revenue in the warehouse. The job runs at 05:30. The dashboard reads the table at 07:00.
Failure and rerun: Each run covers one data interval. It replaces that day's staging data, and the MERGE is by key, so a second run gives the same table. I checked it by running one day twice and comparing the row count and the total revenue. They did not change.
One decision: I used the updated_at watermark and not change data capture. The source team would not turn on the database log. The cost is that a watermark cannot see hard deletes, so we agreed that the app sets an is_deleted flag and never deletes rows.
Result: The table is ready by about 06:10 on a normal day. Before, it was ready around 10:30. After a one-month check against the finance ledger, the totals matched within 0.1 percent.
What you would change: There is no alert yet when the row count drops. I would add a check that fails the run if yesterday has fewer than half the rows of a normal day, and a freshness alert on the final table.

Clickstream sessions in SparkA Spark batch job over event data

Purpose: The product team wanted daily sessions per user, to measure how many users come back.
Source and size: The app sends events as JSON. A connector writes them to S3 every few minutes. That is about 120 million events and 60 GB of JSON a day.
The steps: A Spark job reads yesterday's files with an explicit schema. Rows that do not parse go to a reject folder. It removes duplicates by event_id. Then it sorts each user's events by time and starts a new session after a gap of 30 minutes, using a window function with lag. It writes Parquet files, partitioned by date.
Destination and schedule: A Parquet table that the SQL engine reads. The job runs at 02:00. It also reads the first three hours of today, because some of yesterday's events arrive late. It then keeps only the events whose event time is yesterday.
Failure and rerun: The job overwrites only the date partition it is working on. Running it again for the same day replaces that day and leaves the others alone.
One decision: One task took 40 minutes while the rest took 2. In the Spark UI task table, that task had far more records than the others. A few bot accounts owned a large share of the events. The security team already kept a list of them, and the product team did not want that traffic, so I removed it before the window step. Otherwise I would have salted the key.
Result: The run time fell from 95 minutes to 22. The output went from thousands of small files to about 200 files, because I repartitioned to about 200 partitions before the write.
What you would change: I would agree a schema with the app team, so a changed field fails early and clearly. I would also add tests for the session rule on a tiny fixture: two events 29 minutes apart, and two events 31 minutes apart.

From a nightly rebuild to an incremental loadA cost and run-time fix

Purpose: A nightly job rebuilt a 600-million-row table from scratch. It took four hours and was starting to miss its 06:00 deadline.
Source and size: A source table in the warehouse. About 300,000 rows change on a normal day.
The steps: I changed the model to read only the rows with an updated_at after the latest one already in the target, minus a two-day lookback. A MERGE on the primary key writes them. Once a week, a full refresh corrects any drift.
Destination and schedule: The same target table, with the same columns, so nothing downstream changed. It still runs at 01:00.
Failure and rerun: Because of the lookback and the MERGE by key, a rerun reads the same rows and writes the same result. A row that arrives up to two days late is still picked up.
One decision: I chose a two-day lookback. A shorter one reads less data but can lose a late row without any error. A longer one costs more. Two days covered the latest arrival we saw in a month of data. Before I switched, I ran the new load next to the old rebuild for 30 days and compared row counts and sums.
Result: The run time went from four hours to 25 minutes. The compute bill for that job fell by about four fifths.
What you would change: The lookback is a guess based on past data. I would add a check that counts rows arriving outside the window, so we hear about it the first time it is too short.

Then they pull a thread

A good answer invites these. Each one has a short answer, the answer that fails, and the follow-up in the question bank.

Project: walk me through it The opening question.
Project: a rerun without duplicates Asked as soon as you say "daily".
Project: the source changed a field Asked as soon as you say "from an API" or "from another team".
Project: how do you know it is right Asked as soon as you say "dashboard" or "report".
Project: how did you test it Asked of anyone who has not mentioned tests.
Project: a script that takes hours Asked when you mention a run time.
Project: ten times the data Asked near the end, to see how you reason about limits.
Project: the source is down Asked when the source is a service you do not own.
Project: where do the secrets live Asked when you mention a database or an API key.
Project: how is it deployed Asked when you say "it runs every night".
Project: your worst incident The story that tests ownership.
Project: what would you change The closing question: what you would change.

Common mistakes

Reciting responsibilities instead of decisions and impact.
No clear "why this company / team" answer (Palantir filters hard here).
Being vague about scale or your specific contribution.
Knowing the happy path of your project but not what happens on a rerun, a schema change or ten times the data.

Now rehearse it

This round has no question bank behind it — it is judged on how you tell the story, not on recall.

See all six rounds and the plans, or build something and have it reviewed: a daily orders pipeline or a marketplace model.

Back to Interview Experiences