Data Engineer Interview FAQs Most Asked Questions in 2026

Question:
Find the second-highest salary per department.

WITH ranked AS (
  SELECT
    department_id,
    employee_id,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rnk
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE rnk = 2;

Why this is a strong answer:
- DENSE_RANK handles ties correctly
- PARTITION BY department is explicit
- The CTE makes the intent readable
- You can explain what happens with ties

Question:
Clean a CSV and return monthly revenue per region.

import pandas as pd

def load_and_clean(path):
    df = pd.read_csv(path, parse_dates=["order_date"])
    df = df.drop_duplicates()
    df = df.dropna(subset=["order_date", "revenue"])
    df = df[df["revenue"] >= 0]
    return df

def monthly_revenue(df):
    df = df.copy()
    df["month"] = df["order_date"].dt.to_period("M").astype(str)
    return (
        df.groupby(["month", "region"], as_index=False)
          .agg(revenue=("revenue", "sum"))
          .sort_values(["month", "region"])
    )

if __name__ == "__main__":
    df = load_and_clean("orders.csv")
    print(monthly_revenue(df).head())

Why this is a strong answer:
- cleaning is a separate function
- nulls and duplicates handled explicitly
- vectorised groupby, not a row loop
- readable and reusable

Question:
What makes a pipeline production-ready?

A strong answer covers five things:

1. Idempotency
   Running the same job twice does not duplicate data.
   Write to partitions, merge on keys, or overwrite.

2. Retries
   The pipeline can retry a failed step without
   manual intervention, with a backoff strategy.

3. Data quality checks
   Row counts, null checks, referential checks
   before promoting data to the next layer.

4. Monitoring and alerts
   Row counts, job duration and failures are tracked.
   Alerts fire on anomalies, not only on crashes.

5. Documentation and ownership
   Every table has an owner and a short description.
   Schema and freshness are documented.

Question:
Why is your Spark job slow?

A strong answer diagnoses, not guesses:

Step 1 — Check the Spark UI
- Which stage is slow?
- Is the time spent in shuffle?
- Are there skewed partitions?

Step 2 — Common causes
- Large shuffle joins where one side
  could be broadcast
- Skew: one key dominates the partition
- Too few or too many partitions
- Python UDFs instead of native functions
- Reading far more columns than needed

Step 3 — Fixes
- broadcast() for small dimension tables
- salting or repartitioning for skewed keys
- select only the columns you need
- push filters down to the source
- tune spark.sql.shuffle.partitions

The interviewer wants to see how you
narrow down the problem, not that you
memorised a settings list.

Question:
How would you model a sales pipeline?

Strong answer structure:

1. Define the grain first
   "One row per order line, per order, per day."

2. Identify the fact table
   fact_sales:
   - order_id
   - product_id
   - customer_id
   - store_id
   - order_date
   - quantity
   - revenue

3. Identify dimensions
   dim_product, dim_customer, dim_store, dim_date

4. Explain trade-offs
   - Star schema keeps queries simple
   - Snowflake reduces duplication but adds joins
   - Choose star schema when analysts run
     many ad-hoc queries

5. Mention slowly changing dimensions
   "If customer region changes over time,
   I would use a Type 2 dimension so
   historical reports stay accurate."

Interviewers want to see how you think,
not that you memorised a textbook schema.

Question:
How would you organise a data lake?

Layered design:

raw / bronze
- exact copy of source data
- no transformations
- append-only where possible

staged / silver
- cleaned, typed, deduplicated
- joins to reference data
- validated against schema

modelled / gold
- business-ready tables
- one per reporting need
- aggregated where useful

General rules:
- partition by date
- avoid small files
- store as Parquet or similar
- keep raw data recoverable
- document each layer briefly

Trade-off to mention:
Raw data costs storage but makes
reprocessing possible. Do not skip it.

Question:
Walk me through a pipeline you would build.

Strong answer structure:

1. Source
   "Raw files land in object storage from
   an application, or a database produces
   a CDC stream."

2. Ingestion
   "Scheduled job copies the data into
   a raw bucket, partitioned by date."

3. Transformation
   "A Spark or SQL job cleans, deduplicates
   and joins to reference data, writing
   to a staged layer."

4. Modelling
   "Another job builds business-ready
   tables for reporting."

5. Orchestration
   "Airflow (or managed equivalent) runs
   the jobs in order with retries."

6. Quality
   "Checks run between layers: row counts,
   null checks, referential checks."

7. Monitoring
   "Dashboards track freshness, failures
   and job durations. Alerts fire on anomalies."

8. Security
   "Roles and policies enforce least privilege.
   Secrets live in a managed secret store."

Mention one thing you would improve
in the current version if you rebuilt it.

Question:
How do you control cloud costs?

Strong answer covers five ideas:

1. Know where money is spent
   Cost dashboards by service and team.
   Untagged resources are the first problem.

2. Right-size compute
   Avoid oversized clusters. Use
   serverless or auto-scaling where it
   fits the workload.

3. Storage lifecycle
   Move older partitions to cold storage.
   Delete raw data only when the team agrees
   it is no longer needed.

4. Watch the biggest jobs
   A single Spark job can cost more than
   the rest of the pipeline. Track duration
   and data scanned.

5. Budget alerts
   Alerts on unusual daily spend catch
   mistakes early. A misconfigured job
   can double a bill overnight.

Bonus: mention what you would NOT cut
even if it saves money — backups and
monitoring usually pay for themselves.

STAR answer template:

Situation
- One sentence of context.
- Keep it short. The interviewer cares
  about what YOU did.

Task
- What you were responsible for.
- The constraint (time, data quality,
  unclear requirements).

Action
- The specific steps you took.
- Mention a trade-off you considered.

Result
- The outcome, with a number if you can.
- Include what you would do differently.

Example:
"The daily order pipeline was late two days
in a row. I was on call. I traced it to a
upstream schema change that dropped a column.
I added a schema check before the transformation
step and an alert on row counts. It has not
been late since. In hindsight, I would have
added the check when the source team
announced the change."

Interview preparation checklist:

SQL and Python:
□ Practise on real datasets, not just puzzles
□ Talk through queries out loud
□ Know window functions and CTEs cold

Pipelines:
□ Explain two pipelines you built end to end
□ Know what production-ready means
□ Have one story of a failure and a fix

Spark:
□ Know shuffle, broadcast and skew basics
□ Be able to diagnose a slow job

Modelling:
□ Define grain before columns
□ Know star schema and slowly changing dimensions
□ Have a layered data lake answer ready

Cloud:
□ Explain one end-to-end pipeline on your platform
□ Know cost control and secrets management basics

Behavioural:
□ Prepare 4-5 STAR stories
□ Include at least one failure
□ Practise saying the answers out loud

Portfolio:
□ Two documented projects
□ README for each
□ Can explain any line if asked