Data Engineering Interview Prep — What to Expect and How to Win

Data engineering interviews are a different beast from software engineering interviews. You will not be asked to invert a binary tree or implement a linked list. Instead, you will write complex SQL queries, design data pipelines on a whiteboard, debate the trade-offs between Spark and warehouse SQL, and explain how you would handle billions of late-arriving events.

This guide covers what to expect at each stage and provides concrete examples of the questions you will face.

Interview structure

Most data engineering interview processes follow this pattern:

1. Recruiter screen (30 min)
   └── Resume review, motivation, salary range

2. Technical phone screen (45-60 min)
   └── SQL live coding + data engineering concepts

3. Take-home project (2-4 hours)
   └── Build a small pipeline or data model

4. On-site / virtual loop (3-5 hours)
   ├── SQL deep dive (60 min)
   ├── Python coding (60 min)
   ├── System design (60 min)
   ├── Data modeling (45 min)
   └── Behavioral (45 min)

5. Hiring manager chat (30 min)
   └── Team fit, career goals, questions

Some companies skip the take-home and add another live coding round. FAANG companies tend to include more algorithm-style questions. Startups lean heavier on system design and practical experience.

SQL interview questions

SQL is the most important skill for data engineering interviews. You will be tested on it in every round, directly or indirectly.

Window functions

The number one topic. If you master window functions, you handle 50% of SQL interview questions.

Question: Find each customer’s most recent order.

-- Common approach: ROW_NUMBER
WITH ranked AS (
    SELECT
        customer_id,
        order_id,
        amount,
        ordered_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY ordered_at DESC
        ) AS rn
    FROM orders
)
SELECT customer_id, order_id, amount, ordered_at
FROM ranked
WHERE rn = 1;

Question: Calculate running total of revenue by month.

SELECT
    DATE_TRUNC('month', ordered_at) AS month,
    SUM(amount) AS monthly_revenue,
    SUM(SUM(amount)) OVER (
        ORDER BY DATE_TRUNC('month', ordered_at)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY month;

Question: Find users whose spending increased month over month for 3 consecutive months.

WITH monthly_spending AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', ordered_at) AS month,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', ordered_at)
),
with_lag AS (
    SELECT
        customer_id,
        month,
        total_spent,
        LAG(total_spent, 1) OVER (
            PARTITION BY customer_id ORDER BY month
        ) AS prev_month,
        LAG(total_spent, 2) OVER (
            PARTITION BY customer_id ORDER BY month
        ) AS prev_prev_month
    FROM monthly_spending
)
SELECT DISTINCT customer_id
FROM with_lag
WHERE total_spent > prev_month
  AND prev_month > prev_prev_month
  AND prev_prev_month IS NOT NULL;

CTEs and readability

Interviewers love readable SQL. CTEs show you write maintainable code:

Question: Find the top 3 products by revenue in each category.

WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(o.amount) AS total_revenue
    FROM order_items o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category, p.product_name
),
ranked AS (
    SELECT
        category,
        product_name,
        total_revenue,
        RANK() OVER (
            PARTITION BY category
            ORDER BY total_revenue DESC
        ) AS revenue_rank
    FROM product_revenue
)
SELECT category, product_name, total_revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 3
ORDER BY category, revenue_rank;

Self-joins

Question: Find pairs of customers who ordered the same product on the same day.

SELECT DISTINCT
    a.customer_id AS customer_1,
    b.customer_id AS customer_2,
    a.product_id,
    a.order_date
FROM order_items a
JOIN order_items b
    ON a.product_id = b.product_id
    AND a.order_date = b.order_date
    AND a.customer_id < b.customer_id  -- Avoid duplicates and self-pairs
ORDER BY a.order_date, a.product_id;

The a.customer_id {'<'} b.customer_id condition is crucial — it eliminates both self-pairs (A, A) and duplicate pairs ((A, B) and (B, A)). Interviewers look for this.

Query optimization

Question: This query is slow. How would you optimize it?

-- Slow query
SELECT *
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE segment = 'Enterprise'
)
AND o.ordered_at >= '2024-01-01';

Discuss these optimizations:

1. Replace IN with JOIN — IN subqueries can be slower than JOINs on some databases.
2. Add indexes — on orders(customer_id, ordered_at) and customers(segment).
3. Partition orders by date — if the table has billions of rows, partition pruning avoids full scans.
4. Select specific columns — SELECT * reads unnecessary data.
5. Check the execution plan — EXPLAIN ANALYZE shows where time is spent.

-- Optimized
SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    o.ordered_at
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id
WHERE c.segment = 'Enterprise'
  AND o.ordered_at >= '2024-01-01';

Python coding questions

Python questions for data engineering focus on practical tasks, not algorithms.

Data manipulation

Question: Read a CSV, clean it, and output summary statistics.

import pandas as pd
from pathlib import Path

def process_sales_data(input_path: str) -> dict:
    """Read sales CSV, clean data, return summary stats."""
    df = pd.read_csv(input_path)

    # Clean: remove nulls in required fields
    df = df.dropna(subset=['order_id', 'amount', 'customer_id'])

    # Clean: fix data types
    df['amount'] = pd.to_numeric(df['amount'], errors='coerce')
    df['ordered_at'] = pd.to_datetime(df['ordered_at'], errors='coerce')

    # Remove invalid amounts
    df = df[df['amount'] > 0]

    # Remove duplicates
    df = df.drop_duplicates(subset=['order_id'])

    return {
        'total_orders': len(df),
        'total_revenue': df['amount'].sum(),
        'avg_order_value': df['amount'].mean(),
        'unique_customers': df['customer_id'].nunique(),
        'date_range': {
            'start': df['ordered_at'].min().isoformat(),
            'end': df['ordered_at'].max().isoformat(),
        },
        'top_customers': (
            df.groupby('customer_id')['amount']
            .sum()
            .nlargest(10)
            .to_dict()
        ),
    }

API data ingestion

Question: Write a function that fetches paginated data from an API with retry logic.

import requests
import time
from typing import Generator

def fetch_paginated_api(
    base_url: str,
    headers: dict = None,
    max_retries: int = 3,
    page_size: int = 100,
) -> Generator[dict, None, None]:
    """Fetch all pages from a paginated API with exponential backoff."""
    page = 1
    total_fetched = 0

    while True:
        params = {'page': page, 'per_page': page_size}

        for attempt in range(max_retries):
            try:
                response = requests.get(
                    base_url,
                    headers=headers,
                    params=params,
                    timeout=30,
                )
                response.raise_for_status()
                break
            except requests.RequestException as e:
                if attempt == max_retries - 1:
                    raise
                wait = 2 ** attempt  # Exponential backoff: 1s, 2s, 4s
                print(f"Retry {attempt + 1}/{max_retries} after {wait}s: {e}")
                time.sleep(wait)

        data = response.json()
        records = data.get('results', [])

        if not records:
            break

        for record in records:
            yield record
            total_fetched += 1

        # Check for next page
        if not data.get('has_next', False):
            break

        page += 1

    print(f"Fetched {total_fetched} records across {page} pages")

File processing

Question: Process large JSON files that do not fit in memory.

import json
from typing import Callable

def process_large_jsonl(
    input_path: str,
    output_path: str,
    transform: Callable[[dict], dict],
    filter_fn: Callable[[dict], bool] = None,
    batch_size: int = 10000,
):
    """Process a large JSONL file line-by-line without loading into memory."""
    processed = 0
    written = 0
    errors = 0

    with open(input_path, 'r') as infile, open(output_path, 'w') as outfile:
        for line in infile:
            processed += 1
            try:
                record = json.loads(line)

                if filter_fn and not filter_fn(record):
                    continue

                transformed = transform(record)
                outfile.write(json.dumps(transformed) + '\n')
                written += 1

            except (json.JSONDecodeError, KeyError, ValueError) as e:
                errors += 1
                if errors <= 10:  # Log first 10 errors
                    print(f"Error on line {processed}: {e}")

            if processed % batch_size == 0:
                print(f"Processed {processed:,} | Written {written:,} | Errors {errors:,}")

    print(f"Done: {processed:,} processed, {written:,} written, {errors:,} errors")

System design questions

System design is the most open-ended and highest-signal part of the interview. You are evaluated on your ability to think through trade-offs, not on having a perfect answer.

Framework for answering

1. Clarify requirements — ask about data volume, latency, users, budget.
2. Define the high-level architecture — sources, ingestion, storage, transformation, serving.
3. Discuss trade-offs — batch vs stream, SQL vs Spark, managed vs self-hosted.
4. Address edge cases — late data, duplicates, schema changes, failures.
5. Talk about monitoring — how do you know it is working?

Example: Design a real-time analytics pipeline for an e-commerce platform

Requirements clarification:

• 10M orders per day, 500K concurrent users
• Dashboard should show metrics within 5 minutes of events
• Must handle Black Friday (10x spike)
• Budget: mid-range (not unlimited, not shoestring)

Architecture:

Web/Mobile App
    ↓ Events (clicks, orders, cart updates)
Kafka (event streaming)
    ↓
    ├── Stream processing (Flink or Spark Streaming)
    │   ├── Real-time aggregations → Redis (live dashboards)
    │   └── Write to data lake (S3/GCS Parquet)
    │
    └── Batch processing (runs hourly)
        ├── Spark/dbt transforms → Data warehouse
        └── Quality checks → Alerts

Key trade-offs to discuss:

• Kafka vs Kinesis: Kafka for flexibility and multi-consumer, Kinesis for simplicity on AWS.
• Flink vs Spark Streaming: Flink for true per-event processing, Spark Streaming for micro-batch (simpler but higher latency).
• Lambda vs Kappa architecture: Lambda runs batch and stream in parallel (more complex, more accurate). Kappa uses stream processing for everything (simpler, harder to reprocess).

Data modeling questions

Question: Design a star schema for a ride-sharing application.

Walk through the process:

1. Identify the business process: rides (completed trips)
2. Declare the grain: one row per completed trip
3. Identify dimensions: rider, driver, pickup location, dropoff location, date/time, vehicle, payment method
4. Identify facts: fare amount, tip amount, distance, duration, surge multiplier

-- Fact table
fct_rides (
    ride_id, date_key, rider_key, driver_key,
    pickup_location_key, dropoff_location_key,
    vehicle_key, payment_key,
    fare_amount, tip_amount, total_amount,
    distance_miles, duration_minutes,
    surge_multiplier, rider_rating, driver_rating
)

-- Key dimensions
dim_rider (rider_key, name, signup_date, tier, city)
dim_driver (driver_key, name, signup_date, vehicle_type, rating)
dim_location (location_key, latitude, longitude, zone, city, state)
dim_date (date_key, full_date, day_of_week, month, quarter, year, is_holiday)

Follow-up: How would you handle Slowly Changing Dimensions for driver ratings?

• SCD Type 1 if you only care about current rating (overwrite).
• SCD Type 2 if you need historical analysis (“what was the driver’s rating when this trip happened?”). Add valid_from, valid_to, and is_current columns.
• SCD Type 3 if you only need current and previous (add previous_rating column).

Behavioral questions

Data engineering behavioral questions focus on collaboration, handling ambiguity, and incident response.

“Tell me about a time a pipeline broke in production.”

Use the STAR framework (Situation, Task, Action, Result):

• Situation: “Our daily revenue pipeline started producing 0 revenue for the APAC region on Monday morning.”
• Task: “I was on-call and needed to identify the root cause, fix it, and backfill the missing data.”
• Action: “I checked lineage and traced the issue to a source table schema change — the currency column was renamed. I fixed the staging model, added a schema contract test, and backfilled 3 days of data.”
• Result: “Revenue data was corrected within 2 hours. The schema test has since caught two more upstream changes before they reached production.”

“How do you handle disagreements with analysts about data definitions?”

Demonstrate collaboration: “I schedule a meeting with the analyst and a business stakeholder. We align on the exact definition, document it in our dbt YAML files, and add a test to enforce it. The goal is a single source of truth, not being right.”

“How do you prioritize when multiple stakeholders need different things?”

Show structured thinking: “I evaluate based on business impact and urgency. A broken revenue dashboard for the CFO takes priority over a feature request for a new dimension. I communicate timelines transparently and document priorities in our project tracker.”

Take-home project tips

Many companies include a take-home assignment. Here is what interviewers look for:

What impresses

Good README.md
├── Clear setup instructions
├── Architecture decisions explained
├── Trade-offs discussed
└── What you would do with more time

Clean code
├── Modular functions (not one giant script)
├── Error handling
├── Type hints (Python)
└── Comments explaining WHY, not what

Testing
├── At least basic unit tests
├── Data quality checks
└── Edge case handling

Production thinking
├── Logging
├── Configuration (not hardcoded values)
├── Idempotent operations
└── Handles partial failures gracefully

What to avoid

• Over-engineering: Do not build a Kubernetes cluster for a take-home. Keep it simple and focused.
• No README: If the reviewer cannot run your code in 5 minutes, you have already lost.
• No tests: Even two or three tests show you think about quality.
• Hardcoded paths and secrets: Use environment variables or config files.

Sample project structure

take-home-project/
├── README.md
├── requirements.txt
├── config.py                # Configuration, no hardcoded values
├── src/
│   ├── extract.py           # Data extraction
│   ├── transform.py         # Transformation logic
│   ├── load.py              # Loading to destination
│   └── quality_checks.py    # Data validation
├── tests/
│   ├── test_transform.py
│   └── test_quality.py
├── data/
│   └── sample_input.csv     # Sample data for testing
└── output/
    └── .gitkeep

Preparation resources

SQL practice

• LeetCode Database problems — start with Medium difficulty.
• HackerRank SQL — good for timed practice.
• Mode Analytics SQL tutorial — window functions focus.
• StrataScratch — real interview questions from tech companies.

System design

• “Designing Data-Intensive Applications” by Martin Kleppmann — the bible for data system design.
• “The Data Warehouse Toolkit” by Ralph Kimball — essential for modeling questions.
• Seattle Data Guy (YouTube) — practical data engineering content.
• DataExpert.io — Zach Wilson’s free data engineering bootcamp.

Hands-on practice

• Build a complete ELT pipeline with Airflow + dbt + a cloud warehouse.
• Contribute to an open-source data tool.
• Write about what you learn — a blog post about “how I built X” impresses interviewers more than a certification.

Certifications (if your company values them)

• Databricks Certified Data Engineer Associate
• AWS Data Analytics Specialty
• Google Cloud Professional Data Engineer
• SnowPro Core Certification

Certifications do not replace experience, but they demonstrate foundational knowledge and initiative.

Next steps

• dbt Fundamentals — the most in-demand data engineering tool.
• Spark Fundamentals — essential for big data roles.
• Dimensional Modeling Deep Dive — ace the data modeling round.
• Data Pipeline Testing — show you think about quality.