What I Wish Every Data Engineer Candidate Knew Before Walking In

FIRST QUESTIONS I OFTEN USE IN EACH ROUND

Screening:
  "Pick one pipeline on your resume. Where does the data
   come from and who uses the output?"

SQL:
  "Find each customer's second most recent order."

Spark:
  "What happens when you call groupBy on a DataFrame?"

Design:
  "Our app sends click events. Product wants a daily
   active users report. Where do you start?"

None of these are tricks. Each opens a conversation.

BEFORE THE INTERVIEW, IT IS FINE TO ASK:

- How many rounds are there, and what is each one?
- Is the SQL round live coding or on a shared document?
- Which SQL dialect or warehouse does the team use?
- Is there a take-home task? How long should it take?
- Will the design round use a whiteboard or a diagram tool?
- Is the team more batch or streaming focused?

These questions show organisation, not weakness.

-- Second highest salary in each department (ties allowed)
WITH ranked AS (
    SELECT department_id,
           employee_id,
           salary,
           DENSE_RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE salary_rank = 2;

-- Customers with no orders: NOT EXISTS is NULL-safe
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

-- Daily revenue, change from previous day, and 7-day running total
WITH daily AS (
    SELECT order_date, SUM(amount) AS revenue
    FROM orders
    GROUP BY order_date
)
SELECT order_date,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY order_date) AS change_vs_prev,
       SUM(revenue) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_day
FROM daily
ORDER BY order_date;

-- Say aloud: "ROWS BETWEEN counts rows, not calendar days.
-- If some dates have no orders, I would join to a calendar
-- table first so missing days count as zero."

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("daily_sales").getOrCreate()

orders = spark.read.parquet("/lake/raw/orders/")      # lazy
stores = spark.read.parquet("/lake/dim/stores/")      # small table

daily = (orders
         .filter(F.col("status") == "DELIVERED")         # narrow
         .join(F.broadcast(stores), "store_id")          # no shuffle of orders
         .groupBy("order_date", "city")                  # wide: shuffle
         .agg(F.sum("amount").alias("revenue")))

(daily
 .repartition("order_date")
 .write.mode("overwrite")
 .partitionBy("order_date")
 .parquet("/lake/mart/daily_sales/"))                 # action: runs the plan

"Nothing runs until the write at the end.

 The filter is narrow, so each partition handles it alone.
 The stores table is small, so I broadcast it to every
 executor. That avoids shuffling the large orders table
 for the join.

 The groupBy is wide: all rows for the same date and city
 must end up in the same partition, so Spark shuffles.

 Before writing, I repartition by order_date so each date
 folder gets fewer, larger files instead of many tiny ones.

 If I wanted to confirm the plan, I would call
 daily.explain() and look for BroadcastHashJoin."

SEVEN DAYS BEFORE THE INTERVIEW

Day 1  SQL traps: NULLs, LEFT JOIN filters, COUNT rules
Day 2  Window functions: ranking, LAG/LEAD, running totals
Day 3  Spark: lazy plans, shuffles, broadcast, skew
Day 4  Your projects: explain each one aloud, record it
Day 5  One full design case, timed, spoken aloud
Day 6  Mock interview with a friend or trainer
Day 7  Light review only, prepare questions to ask

Each day: 20 minutes of SQL practice regardless of topic.

CAN YOU ANSWER THESE ALOUD IN UNDER A MINUTE EACH?

1. Why can NOT IN return zero rows unexpectedly?
2. RANK vs DENSE_RANK vs ROW_NUMBER: when to use each?
3. What makes a pipeline rerun safe?
4. What is a shuffle, and which operations cause one?
5. How would you deal with a skewed join key?
6. Parquet vs CSV: why does it matter for analytics?
7. Batch or streaming for a daily report? Why?
8. What data quality checks run on your pipeline?

ON THE DAY

[ ] Laptop charged, IDE or SQL editor tested
[ ] Stable internet and a backup hotspot for online rounds
[ ] Notepad and pen for sizing and diagrams
[ ] Resume printed or open, every line explainable
[ ] Two or three questions for each interviewer
[ ] Arrive or log in ten minutes early

During each round:
  Restate the question. Ask one clarifying question.
  Think aloud. Test your answer on an edge case.