Data Engineer Interview Questions and How to Tackle Them (45+ Questions)

Data Engineer Interview Questions and How to Tackle Them (45+ Questions)

You spent months building pipelines, wrangling schemas, and debugging Spark jobs at 2 a.m. Now someone is going to ask you about all of it in 45 minutes — and half the questions will be ones you never specifically prepared for.

This guide covers 45+ real data engineering interview questions with detailed answers and working code. No padding, no theory for theory's sake. Every answer focuses on what a senior engineer actually wants to hear.

Work through these questions in order. They build on each other.

How Data Engineering Interviews Are Structured

Most data engineering interviews follow a predictable arc:

1. 1SQL and data modeling (almost always present, often first)
2. 2Python and programming fundamentals
3. 3Distributed systems and Spark/Hadoop
4. 4Pipeline design and architecture
5. 5Data quality and observability
6. 6Cloud and infrastructure (AWS, GCP, or Azure)
7. 7System design (a 45-60 minute open-ended question)
8. 8Behavioral (tell me about a time...)

A few companies lead with a take-home assignment or a live coding screen. Prepare for both.

Part 1 — SQL and Data Modeling

SQL is the one skill every data engineering interview tests. Even if you spend most of your day writing PySpark, interviewers use SQL to quickly evaluate how you think about data.

Question 1: What is the difference between a fact table and a dimension table?

What they want to hear: You understand dimensional modeling and can explain it without jargon.

A fact table stores measurable business events — things that happened. Each row is a transaction, a click, an order, a session. Fact tables are wide, append-heavy, and usually contain foreign keys to dimensions plus numeric measures.

A dimension table describes the context around those events — who, what, where, when. Dimension tables are narrower and change less often.

Example: In an e-commerce warehouse:

• orders is a fact table: order_id, customer_id, product_id, date_id, amount, quantity
• customers is a dimension: customer_id, name, country, signup_date
• products is a dimension: product_id, name, category, brand

A star schema puts fact tables at the center with dimension tables radiating outward. A snowflake schema normalizes those dimensions further (e.g., category becomes its own table).

When to use which: Star schemas are faster to query but store more redundancy. Snowflake schemas reduce storage and make updates easier but require more joins.

Question 2: Write a query to find the top 3 customers by revenue in each country.

What they want to hear: Comfortable use of window functions.

WITH ranked AS (
  SELECT
    c.customer_id,
    c.name,
    c.country,
    SUM(o.amount) AS total_revenue,
    RANK() OVER (
      PARTITION BY c.country
      ORDER BY SUM(o.amount) DESC
    ) AS revenue_rank
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.name, c.country
)
SELECT customer_id, name, country, total_revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 3
ORDER BY country, revenue_rank;

Common follow-up: What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

• RANK() — skips numbers after ties (1, 2, 2, 4)
• DENSE_RANK() — no gaps after ties (1, 2, 2, 3)
• ROW_NUMBER() — always unique, arbitrary tiebreak (1, 2, 3, 4)

For "top N per group" questions, use DENSE_RANK() if you want exactly N distinct ranks even with ties, or ROW_NUMBER() if you want exactly N rows.

Question 3: Explain slowly changing dimensions (SCD). What are the types?

What they want to hear: You know at least Type 1, 2, and 3, and you can explain the tradeoffs.

A slowly changing dimension (SCD) is a dimension whose attributes change over time. The challenge: when an attribute changes, do you overwrite history or preserve it?

Type 1 — Overwrite: Simply update the record. No history preserved. Use this when history doesn't matter (fixing a typo in a name).

Type 2 — Add a new row: Preserve history by creating a new row with a validity window. This is the most common pattern.

-- customers dimension, Type 2
CREATE TABLE customers (
  surrogate_key     BIGINT PRIMARY KEY,
  customer_id       VARCHAR(50),   -- natural/business key
  name              VARCHAR(200),
  email             VARCHAR(200),
  country           VARCHAR(100),
  effective_date    DATE NOT NULL,
  expiry_date       DATE,          -- NULL means current
  is_current        BOOLEAN DEFAULT TRUE
);

-- When a customer moves countries:
-- 1. Expire the old row
UPDATE customers
SET expiry_date = CURRENT_DATE - 1,
    is_current  = FALSE
WHERE customer_id = 'cust_123' AND is_current = TRUE;

-- 2. Insert the new row
INSERT INTO customers (surrogate_key, customer_id, name, email, country, effective_date, is_current)
VALUES (nextval('customers_seq'), 'cust_123', 'Ana García', 'ana@example.com', 'Mexico', CURRENT_DATE, TRUE);

Type 3 — Add a column: Keep both the old and new value in the same row (e.g., country and prev_country). Simple but limited — only tracks one historical value.

Type 4 — History table: Keep the current record in the main table and move old records to a separate history table.

When to use Type 2: Whenever business users need to analyze "what did this customer look like at the time of purchase?" That requires the historical row.

Question 4: What is the difference between `WHERE` and `HAVING`?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

-- Wrong: can't use aggregate in WHERE
SELECT country, COUNT(*) AS total
FROM customers
WHERE COUNT(*) > 100  -- syntax error
GROUP BY country;

-- Right: use HAVING for post-aggregation filters
SELECT country, COUNT(*) AS total
FROM customers
GROUP BY country
HAVING COUNT(*) > 100;

-- WHERE and HAVING together
SELECT country, COUNT(*) AS total
FROM customers
WHERE signup_date >= '2024-01-01'   -- filter rows first
GROUP BY country
HAVING COUNT(*) > 100;               -- then filter groups

Question 5: Write a query to detect duplicate records in a table.

-- Find all duplicate rows based on business key columns
SELECT
  email,
  COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Get the full duplicate rows (not just the count)
WITH dupes AS (
  SELECT email
  FROM customers
  GROUP BY email
  HAVING COUNT(*) > 1
)
SELECT c.*
FROM customers c
JOIN dupes d ON c.email = d.email
ORDER BY c.email, c.created_at;

-- Identify which rows to delete (keep earliest, delete the rest)
WITH ranked AS (
  SELECT
    id,
    email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

Question 6: What is a surrogate key and why do data warehouses use them?

A surrogate key is a system-generated, meaningless integer (or UUID) that uniquely identifies a row in a dimension table. It has no business meaning.

Why use them:

• Natural/business keys can change (customer emails, product codes, employee IDs). Surrogate keys never change.
• They are more efficient for joins (integer vs. a long string).
• They handle SCDs — when a Type 2 dimension creates a new row for the same business entity, the surrogate key distinguishes the two versions.
• They isolate your warehouse from upstream source system changes.

Question 7: Explain the difference between normalized and denormalized schemas. When would you choose each?

Normalized (3NF): data split into many tables, minimal redundancy, enforces integrity through relationships.

Denormalized: data collapsed into fewer, wider tables, deliberately redundant for query speed.

| Concern | Normalized | Denormalized |

|---|---|---|

| Storage | Less | More |

| Write performance | Better | Worse (multiple places to update) |

| Read performance | More joins needed | Fewer joins |

| Analytical queries | Slower | Faster |

| OLTP workloads | Better fit | Rarely used |

| OLAP workloads | Possible but slower | Standard approach |

Rule of thumb: Use normalized schemas for operational databases (OLTP). Use denormalized schemas for analytical warehouses (OLAP). When in doubt for a warehouse, denormalize — storage is cheap; query latency is expensive.

Part 2 — Python and Programming

Question 8: What is the difference between a generator and a list comprehension in Python? When would you use a generator in data engineering?

A list comprehension builds the entire list in memory at once. A generator computes values lazily, one at a time.

# List comprehension — entire list in memory immediately
squares_list = [x**2 for x in range(1_000_000)]  # ~8MB in memory now

# Generator — nothing computed until you iterate
squares_gen = (x**2 for x in range(1_000_000))  # near-zero memory until iterated

# The generator only materializes one value at a time
for val in squares_gen:
    process(val)

In data engineering, use generators when:

• Streaming rows from a large file or database cursor without loading all records into memory
• Building ETL pipelines where each step yields one record to the next

def read_large_csv(filepath):
    """Yields one parsed row at a time — safe for files larger than RAM."""
    with open(filepath, 'r') as f:
        header = next(f).strip().split(',')
        for line in f:
            yield dict(zip(header, line.strip().split(',')))

def transform(records):
    for record in records:
        record['amount'] = float(record['amount']) * 1.1
        yield record

def load(records, db_conn):
    batch = []
    for record in records:
        batch.append(record)
        if len(batch) >= 1000:
            db_conn.executemany("INSERT INTO ...", batch)
            batch.clear()
    if batch:
        db_conn.executemany("INSERT INTO ...", batch)

# Compose them into a pipeline — memory stays flat regardless of file size
load(transform(read_large_csv("data.csv")), conn)

Question 9: How does Python's GIL affect multi-threaded data processing? What should you use instead?

The Global Interpreter Lock (GIL) prevents multiple Python threads from executing Python bytecode simultaneously. For CPU-bound work (parsing, transforming, computing), threads in Python do not run in parallel — only one thread runs at a time.

What to use instead:

| Scenario | Solution |

|---|---|

| CPU-bound (transforms, parsing) | multiprocessing or concurrent.futures.ProcessPoolExecutor |

| I/O-bound (API calls, DB queries) | threading or asyncio — GIL released during I/O |

| Large-scale distributed | Spark, Dask, Ray |

from concurrent.futures import ProcessPoolExecutor, ThreadPoolExecutor
import requests

# CPU-bound: use processes (bypasses GIL)
def parse_record(raw):
    # heavy parsing/transformation
    return transform(raw)

with ProcessPoolExecutor(max_workers=8) as executor:
    results = list(executor.map(parse_record, raw_records))

# I/O-bound: threads are fine (GIL released during network wait)
def fetch(url):
    return requests.get(url).json()

with ThreadPoolExecutor(max_workers=20) as executor:
    responses = list(executor.map(fetch, urls))

Question 10: Write a Python function to read a JSON file in chunks and insert into a database efficiently.

import json
import psycopg2
from typing import Iterator

def stream_json_array(filepath: str, chunk_size: int = 1000) -> Iterator[list]:
    """
    Reads a large JSON array file in chunks without loading the full file.
    Assumes the file is a top-level JSON array: [{...}, {...}, ...]
    """
    import ijson  # streaming JSON parser
    
    with open(filepath, 'rb') as f:
        batch = []
        for record in ijson.items(f, 'item'):
            batch.append(record)
            if len(batch) >= chunk_size:
                yield batch
                batch.clear()
        if batch:
            yield batch


def insert_events(conn, records: list) -> int:
    """Bulk-insert using executemany. Returns rows inserted."""
    sql = """
        INSERT INTO events (event_id, user_id, event_type, occurred_at, payload)
        VALUES (%(event_id)s, %(user_id)s, %(event_type)s, %(occurred_at)s, %(payload)s)
        ON CONFLICT (event_id) DO NOTHING
    """
    with conn.cursor() as cur:
        cur.executemany(sql, records)
        return cur.rowcount


def load_events_file(filepath: str, dsn: str) -> dict:
    """Main entry point. Returns stats."""
    stats = {'batches': 0, 'rows_inserted': 0, 'errors': 0}
    
    conn = psycopg2.connect(dsn)
    conn.autocommit = False
    
    try:
        for batch in stream_json_array(filepath, chunk_size=500):
            try:
                rows = insert_events(conn, batch)
                conn.commit()
                stats['batches'] += 1
                stats['rows_inserted'] += rows
            except Exception as e:
                conn.rollback()
                stats['errors'] += 1
                print(f"Batch failed: {e}")
    finally:
        conn.close()
    
    return stats

Question 11: What is the difference between `deepcopy` and `copy` in Python? When does it matter in data pipelines?

copy.copy() creates a shallow copy — a new object, but nested objects are still shared references.

copy.deepcopy() recursively copies everything — a fully independent object.

import copy

original = {'user': {'name': 'Ana', 'scores': [90, 85, 92]}}

shallow = copy.copy(original)
deep = copy.deepcopy(original)

# Mutating nested list affects shallow copy's original
original['user']['scores'].append(100)
print(shallow['user']['scores'])   # [90, 85, 92, 100] — affected!
print(deep['user']['scores'])      # [90, 85, 92]      — safe

In data pipelines this matters when you pass records between transformation stages. If a stage mutates a nested dict or list in place, a shallow copy in an upstream cache may reflect those mutations. When in doubt in a pipeline, use deepcopy — or better yet, design transformations to return new objects rather than mutating inputs.

Question 12: How do you handle schema evolution in a Python-based ETL pipeline?

Schema evolution happens when the upstream source adds, removes, or renames fields. A brittle pipeline crashes. A resilient one degrades gracefully.

from dataclasses import dataclass, fields
from typing import Any, Optional
import logging

@dataclass
class EventRecord:
    event_id: str
    user_id: str
    event_type: str
    occurred_at: str
    amount: Optional[float] = None  # new field — optional with default
    
    @classmethod
    def from_dict(cls, data: dict) -> 'EventRecord':
        """
        Safe constructor: ignores unknown fields, uses defaults for missing ones.
        Never raises on extra or missing optional fields.
        """
        known = {f.name for f in fields(cls)}
        filtered = {k: v for k, v in data.items() if k in known}
        
        # Log unexpected fields so we can update the schema intentionally
        unknown = set(data.keys()) - known
        if unknown:
            logging.warning("Unknown fields in source record: %s", unknown)
        
        try:
            return cls(**filtered)
        except TypeError as e:
            logging.error("Schema mismatch: %s | data: %s", e, data)
            raise

# Test it:
old_record = {'event_id': 'e1', 'user_id': 'u1', 'event_type': 'click', 'occurred_at': '2026-01-01'}
new_record = {'event_id': 'e2', 'user_id': 'u2', 'event_type': 'purchase', 'occurred_at': '2026-01-01', 'amount': 99.99, 'currency': 'USD'}  # new fields

EventRecord.from_dict(old_record)  # works, amount=None
EventRecord.from_dict(new_record)  # works, logs unknown 'currency'

For production-grade schema evolution, use a schema registry (Confluent Schema Registry for Kafka, or Glue Schema Registry for AWS) and enforce compatibility rules (BACKWARD, FORWARD, FULL) at the producer level.

Part 3 — Apache Spark and Distributed Systems

Question 13: Explain the difference between a transformation and an action in Spark.

Spark operations split into two types:

Transformations are lazy — they define what to compute but don't execute. They build a logical plan (DAG).

Examples: map, filter, groupBy, join, select, withColumn

Actions trigger execution — Spark materializes the DAG and runs the job.

Examples: count, collect, show, write, take, foreach

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.getOrCreate()

df = spark.read.parquet("s3://bucket/events/")           # lazy — no execution yet

# All transformations — builds a logical plan
filtered = df.filter(F.col("event_type") == "purchase")  # lazy
enriched = filtered.withColumn("tax", F.col("amount") * 0.21)  # lazy
grouped  = enriched.groupBy("user_id").agg(F.sum("amount").alias("total"))  # lazy

# This ACTION triggers the entire DAG
result = grouped.collect()  # execution happens here

Why this matters in interviews: Lazy evaluation enables Spark to optimize the entire DAG before running anything (predicate pushdown, filter reordering, join reordering). Understanding this is the foundation of debugging slow Spark jobs.

Question 14: What is a shuffle in Spark and why is it expensive?

A shuffle is the redistribution of data across the cluster — data leaves its current partition and moves over the network to land on new partitions.

Shuffles happen on: groupBy, join, distinct, repartition, orderBy/sort

Why it's expensive:

1. 1Data serializes, moves over the network, and deserializes
2. 2Intermediate data is written to disk (shuffle files)
3. 3It creates stage boundaries — no part of the next stage starts until all tasks in the shuffle-write stage finish

# EXPENSIVE: wide transformation — forces a shuffle
df.groupBy("country").agg(F.count("*"))

# AVOID: sorting when you don't need a global order
df.orderBy("created_at")  # full shuffle + sort
# PREFER: local sort if only needed for window functions
df.sortWithinPartitions("created_at")

# AVOID: cartesian product / cross join
df1.crossJoin(df2)  # O(n*m) — almost always a mistake

# REDUCE shuffle: broadcast join when one side is small
from pyspark.sql.functions import broadcast
df_large.join(broadcast(df_small), "user_id")

Rule of thumb: Every groupBy, join, and repartition is a shuffle. Your goal as a data engineer is to minimize their number and their data volume.

Question 15: What is data skew and how do you fix it in Spark?

Data skew is when some partitions have far more data than others. The job runs at the speed of its slowest task (the straggler). One partition with 10x the data of the others causes the other 99 cores to sit idle waiting.

How to detect it: Look at the Spark UI's Stage tab. If task durations vary by 10x+ across tasks in the same stage, you have skew.

Fix 1 — Salting (for group-by skew):

import random

# The problem: user_id '12345' has 5M rows, everyone else has <10k
# Solution: add a random salt to break up the hot key
NUM_SALTS = 20

salted_df = df.withColumn(
    "salt",
    (F.rand() * NUM_SALTS).cast("int")
).withColumn(
    "salted_key",
    F.concat(F.col("user_id"), F.lit("_"), F.col("salt"))
)

# First aggregation with salt
partial = salted_df.groupBy("salted_key", "user_id").agg(
    F.sum("amount").alias("partial_sum")
)

# Second aggregation to recombine
result = partial.groupBy("user_id").agg(
    F.sum("partial_sum").alias("total_amount")
)

Fix 2 — Broadcast join for skewed joins:

# If one table is small (<= a few hundred MB), broadcast it
result = large_df.join(broadcast(small_df), "user_id")

Fix 3 — AQE (Adaptive Query Execution, Spark 3+):

spark = SparkSession.builder \
    .config("spark.sql.adaptive.enabled", "true") \
    .config("spark.sql.adaptive.skewJoin.enabled", "true") \
    .getOrCreate()
# Spark will automatically split skewed partitions

Question 16: Explain the difference between `repartition` and `coalesce`.

Both change the number of partitions, but they work differently:

| | repartition(n) | coalesce(n) |

|---|---|---|

| Direction | Up or down | Only down |

| Shuffle | Full shuffle | Avoids shuffle when possible |

| Partition balance | Even | Potentially uneven (combined partitions) |

| Use case | Increasing partitions, rebalancing | Reducing partitions before write |

# repartition: full shuffle, even distribution — use before a write to
# produce exactly N output files, or before a join to co-locate data
df_repartitioned = df.repartition(200, "country")  # shuffle by country

# coalesce: collapse partitions locally, no shuffle — use when you want
# fewer files and don't need perfect balance
df_coalesced = df.coalesce(10)  # merge 200 -> 10, minimal data movement

Typical pattern before writing to storage:

# After a groupBy, you might end up with many small partitions
result = df.groupBy("date", "country").agg(F.sum("amount"))

# Coalesce to a reasonable number of output files before write
result.coalesce(20).write.parquet("s3://bucket/output/", mode="overwrite")

Question 17: What is a broadcast variable in Spark and when do you use it?

A broadcast variable sends a read-only copy of data to every executor node once, where it stays in memory for the lifetime of the job. Without broadcasting, Spark would serialize and send the variable with every task (potentially thousands of times).

# Without broadcast: lookup dict sent with every task
lookup = {"US": "North America", "MX": "Latin America", "CO": "Latin America"}

def map_region(country_code):
    return lookup.get(country_code, "Unknown")  # lookup serialized per task

# With broadcast: sent once to each executor
broadcast_lookup = spark.sparkContext.broadcast(lookup)

def map_region_broadcast(country_code):
    return broadcast_lookup.value.get(country_code, "Unknown")  # read from local cache

# In DataFrame API, broadcast() in a join does the same thing
result = large_df.join(broadcast(small_dim_df), "country_code")

Use broadcast variables when:

• The variable is used inside a UDF applied to millions of rows
• One side of a join is small enough to fit in executor memory (< 200-300MB typically)

Question 18: What is the difference between DataFrame, Dataset, and RDD in Spark?

| | RDD | DataFrame | Dataset |

|---|---|---|---|

| Type safety | Yes (but runtime) | No (compile-time: Row) | Yes (compile-time, JVM only) |

| Optimization | None (Catalyst can't see in) | Catalyst + Tungsten | Catalyst + Tungsten |

| Language | Scala, Java, Python, R | All | Scala, Java only |

| API level | Low-level | High-level | High-level |

| When to use | Legacy code, custom serialization | Almost always in Python | Scala/Java production code |

The practical answer for Python engineers: Use DataFrames for everything. RDDs bypass Catalyst optimization entirely — they are slower and harder to read. The only reason to use RDDs today is for operations that the DataFrame API cannot express (rare).

Part 4 — Pipeline Design and Architecture

Question 19: What is the difference between ETL and ELT? When would you choose each?

ETL (Extract → Transform → Load): Data is transformed outside the destination store before loading. The transformation happens in an intermediate engine or on-premise.

ELT (Extract → Load → Transform): Raw data lands in the destination first, then transformation runs inside the warehouse using SQL.

| Factor | ETL | ELT |

|---|---|---|

| Destination | Traditional relational DW (Oracle, Teradata) | Cloud DW (BigQuery, Snowflake, Redshift) |

| Raw data availability | No — discarded after transform | Yes — raw always available for re-processing |

| Transformation language | Python, Java, Spark | SQL (dbt, native warehouse SQL) |

| Latency | Higher (transform before load) | Lower for load; transform is separate step |

| Debugging | Harder (data transformed before you can see it) | Easier (raw always available) |

Choose ELT for modern cloud warehouses with abundant compute. The ability to replay transformations against raw data without re-extracting is a major operational advantage.

Question 20: What is idempotency in a data pipeline and how do you achieve it?

An idempotent pipeline produces the same result regardless of how many times you run it. Running it twice should be identical to running it once.

Why it matters: Pipelines fail. You need to re-run them safely without duplicating or corrupting data.

Techniques:

# 1. Overwrite entire partition instead of appending
df.write \
    .mode("overwrite") \
    .partitionBy("date") \
    .parquet("s3://bucket/events/")
# Running this again for the same date overwrites cleanly — no duplicates

# 2. Upsert with a unique key (merge/upsert pattern in SQL)
# dbt merge example (SQL):
"""
MERGE INTO orders AS target
USING staging_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
    UPDATE SET
        target.status = source.status,
        target.updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (order_id, user_id, amount, status, created_at)
    VALUES (source.order_id, source.user_id, source.amount, source.status, source.created_at);
"""

# 3. Hash-based deduplication before write
from pyspark.sql import functions as F

deduped = df \
    .withColumn("row_hash", F.md5(F.concat_ws("|", *df.columns))) \
    .dropDuplicates(["order_id"])  \
    .write.mode("overwrite").parquet("...")

The key rule: A pipeline is idempotent if run(run(data)) == run(data). Test this explicitly.

Question 21: Explain the Lambda architecture. What problem does it solve, and what are its drawbacks?

Lambda architecture splits processing into two layers:

• Batch layer: Processes all historical data periodically (Hadoop, Spark). High latency, high accuracy.
• Speed layer: Processes only recent data in near-real-time (Kafka, Spark Streaming, Flink). Low latency, approximate.
• Serving layer: Merges results from both layers for queries.

The problem it solves: Getting both low-latency results AND historical accuracy — neither layer alone can do both.

Drawbacks:

• You maintain two separate codebases that must produce equivalent results
• Debugging discrepancies between layers is painful
• Complex operational overhead

The modern alternative — Kappa architecture: Use a streaming system (Kafka + Flink) for everything. Store all events in an immutable log. To reprocess history, replay the log. One codebase, one system.

Most teams building new systems today choose Kappa unless they have a strong reason for Lambda (e.g., extremely complex batch jobs that cannot be expressed as streaming).

Question 22: What is a data lakehouse and how does it differ from a data lake or data warehouse?

| | Data Lake | Data Warehouse | Data Lakehouse |

|---|---|---|---|

| Storage | Raw files (S3, GCS) | Proprietary format (Snowflake, Redshift) | Open file format (Parquet + Delta/Iceberg) |

| Schema | Schema-on-read | Schema-on-write | Schema-on-write (enforced) |

| ACID | No | Yes | Yes (Delta Lake, Apache Iceberg) |

| SQL BI support | Limited | Excellent | Excellent |

| Streaming | Awkward | Limited | Native (Delta supports streaming + batch) |

| Cost | Low storage, complex queries | High storage + compute | Low storage, efficient queries |

Delta Lake example (ACID transactions on a data lake):

# Write a Delta table
df.write.format("delta").mode("overwrite").save("s3://bucket/delta/orders/")

# Time travel — read the table as it was 3 versions ago
historical = spark.read \
    .format("delta") \
    .option("versionAsOf", 3) \
    .load("s3://bucket/delta/orders/")

# Upsert (MERGE) with ACID guarantees
from delta.tables import DeltaTable

delta_table = DeltaTable.forPath(spark, "s3://bucket/delta/orders/")
delta_table.alias("target").merge(
    updates_df.alias("source"),
    "target.order_id = source.order_id"
).whenMatchedUpdateAll() \
 .whenNotMatchedInsertAll() \
 .execute()

Question 23: What is Apache Airflow? How does it handle task dependencies?

Airflow is a workflow orchestration platform. You define pipelines as DAGs (Directed Acyclic Graphs) in Python. Each node is a task; edges define dependencies.

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.amazon.aws.operators.glue import GlueJobOperator
from datetime import datetime, timedelta

default_args = {
    "owner": "data-eng",
    "retries": 2,
    "retry_delay": timedelta(minutes=5),
    "email_on_failure": True,
}

with DAG(
    dag_id="daily_orders_pipeline",
    default_args=default_args,
    schedule_interval="0 6 * * *",   # 6am UTC daily
    start_date=datetime(2026, 1, 1),
    catchup=False,
    tags=["orders", "daily"],
) as dag:

    extract = GlueJobOperator(
        task_id="extract_raw_orders",
        job_name="extract-orders-from-rds",
    )

    transform = PythonOperator(
        task_id="transform_orders",
        python_callable=run_dbt_model,
        op_kwargs={"model": "orders_fact"},
    )

    load = PythonOperator(
        task_id="load_to_redshift",
        python_callable=load_orders_to_redshift,
    )

    alert = PythonOperator(
        task_id="send_completion_alert",
        python_callable=send_slack_notification,
    )

    # Dependencies — DAG edges
    extract >> transform >> load >> alert

Key Airflow concepts to know for interviews:

• XCom — mechanism for tasks to exchange small values
• Sensors — tasks that wait for an external condition (file arrival, Hive partition)
• Dynamic task mapping (Airflow 2.3+) — create tasks dynamically at runtime
• Pools — limit concurrency across DAGs
• Connections — encrypted credential storage

Question 24: How would you design a pipeline to handle late-arriving data?

Late-arriving data is a streaming-first problem, but batch pipelines face it too (a source system backfills records for yesterday 2 days later).

In batch pipelines:

# Partition by event date, not load date
# Store raw data with both timestamps:
df = df.withColumn("event_date", F.to_date("occurred_at")) \
       .withColumn("loaded_at", F.current_timestamp())

# Process with a lookback window: always reprocess the last N days
# This way, if late data arrives for yesterday, tomorrow's run picks it up
start_date = datetime.today() - timedelta(days=3)  # 3-day lookback

# Overwrite partition idempotently — safe to run multiple times
df.filter(F.col("event_date") >= start_date) \
  .write \
  .partitionBy("event_date") \
  .mode("overwrite") \
  .parquet("s3://bucket/events/")

In streaming pipelines (Spark Structured Streaming / Flink):

# Watermarking: tell Spark to wait up to 2 hours for late events
# before closing a time window
from pyspark.sql import functions as F

result = df \
    .withWatermark("occurred_at", "2 hours") \
    .groupBy(
        F.window("occurred_at", "1 hour"),
        F.col("event_type")
    ) \
    .agg(F.count("*").alias("event_count"))

The watermark tells Spark: events more than 2 hours late will be dropped. Any event within that window still updates the result.

Question 25: What is dbt and how does it fit into the modern data stack?

dbt (data build tool) is a transformation tool that runs SQL models inside a data warehouse. It handles dependency resolution, testing, documentation, and incremental loading — but only the T in ELT. You still need a separate extraction tool (Fivetran, Airbyte, custom) and a loader.

-- models/marts/orders_daily.sql
-- dbt materializes this as a table or view in your warehouse

{{
  config(
    materialized='incremental',
    unique_key='order_date',
    incremental_strategy='merge'
  )
}}

WITH source AS (
  SELECT * FROM {{ ref('stg_orders') }}
  {% if is_incremental() %}
    -- only process new/updated rows on incremental runs
    WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
  {% endif %}
),

aggregated AS (
  SELECT
    DATE(created_at)           AS order_date,
    COUNT(*)                   AS order_count,
    SUM(amount)                AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM source
  GROUP BY 1
)

SELECT * FROM aggregated

# schema.yml — built-in tests
models:
  - name: orders_daily
    columns:
      - name: order_date
        tests:
          - unique
          - not_null
      - name: total_revenue
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

dbt vs Spark for transforms: Use dbt when your data is already in the warehouse and SQL is sufficient. Use Spark when you need distributed compute outside the warehouse (raw file processing, ML feature engineering, very large joins the warehouse can't handle cost-effectively).

Part 5 — Data Quality and Observability

Question 26: What is data quality and how do you test for it in a pipeline?

Data quality covers: completeness (no missing values), accuracy (correct values), consistency (same value across systems), timeliness (data arrives when expected), uniqueness (no duplicates), and validity (values within expected ranges/formats).

Testing approaches:

# 1. Schema validation with Pydantic
from pydantic import BaseModel, validator
from typing import Optional
from datetime import datetime

class OrderRecord(BaseModel):
    order_id: str
    user_id: str
    amount: float
    currency: str
    status: str
    created_at: datetime
    
    @validator('amount')
    def amount_must_be_positive(cls, v):
        if v < 0:
            raise ValueError(f"amount must be positive, got {v}")
        return v
    
    @validator('currency')
    def currency_must_be_iso(cls, v):
        valid = {'USD', 'EUR', 'MXN', 'COP', 'ARS', 'BRL'}
        if v not in valid:
            raise ValueError(f"unknown currency: {v}")
        return v

# 2. Great Expectations (open-source data quality framework)
import great_expectations as gx

context = gx.get_context()
batch = context.get_batch({"path": "data/orders.parquet"}, "orders_suite")

batch.expect_column_values_to_not_be_null("order_id")
batch.expect_column_values_to_be_unique("order_id")
batch.expect_column_values_to_be_between("amount", min_value=0, max_value=1_000_000)
batch.expect_column_values_to_be_in_set("status", ["pending", "confirmed", "shipped", "cancelled"])
batch.expect_column_pair_values_A_to_be_greater_than_B("shipped_at", "created_at")

results = batch.validate()
if not results.success:
    raise ValueError(f"Data quality check failed: {results}")

In dbt, add schema tests in schema.yml. Run dbt test as a step in your pipeline before downstream consumers see the data.

Question 27: What is data lineage and why does it matter?

Data lineage tracks the origin of data and how it moves and transforms across systems. It answers: "Where did this number come from?" and "If I change this table, what breaks downstream?"

Why it matters in practice:

• Debugging bad data: trace a wrong metric back to its source
• Impact analysis: before changing a column name, know every downstream table that uses it
• Compliance (GDPR, HIPAA): know which tables contain PII and who has access
• Root cause analysis for pipeline failures

Modern tools for lineage: OpenLineage (standard), Marquez, dbt's built-in DAG, Apache Atlas, DataHub, Atlan.

Question 28: How do you monitor a production data pipeline?

A production pipeline needs monitoring at three levels:

Infrastructure level: Is the job running? Did it finish? Memory, CPU, disk.

Data level: Did the right amount of data arrive? Are values in expected ranges?

Business level: Are the downstream metrics reasonable? Did revenue drop 90%?

# Example: lightweight pipeline health check
import boto3
from datetime import date, timedelta

def check_daily_partition_health(s3_bucket: str, prefix: str, date_str: str) -> dict:
    """
    Verifies today's partition exists and has reasonable row count.
    Run as the first task of every downstream DAG.
    """
    s3 = boto3.client('s3')
    
    # Check partition exists
    response = s3.list_objects_v2(
        Bucket=s3_bucket,
        Prefix=f"{prefix}/date={date_str}/"
    )
    
    if response['KeyCount'] == 0:
        raise ValueError(f"Missing partition: {prefix}/date={date_str}")
    
    # Check total size is within expected range (simple heuristic)
    total_bytes = sum(obj['Size'] for obj in response.get('Contents', []))
    expected_min_mb = 50
    
    if total_bytes < expected_min_mb * 1024 * 1024:
        raise ValueError(
            f"Partition too small: {total_bytes/1e6:.1f}MB, expected >= {expected_min_mb}MB. "
            "Possible empty or partial load."
        )
    
    return {"partition": date_str, "size_mb": total_bytes / 1e6, "files": response['KeyCount']}

Key metrics to emit from every pipeline job:

• rows_read, rows_written, rows_rejected
• job_duration_seconds
• last_successful_run_at
• Row count vs. expected range (z-score anomaly detection)

Part 6 — Cloud and Storage

Question 29: What is the difference between row-oriented and column-oriented storage? Why does column-oriented storage matter for analytics?

Row-oriented (PostgreSQL, MySQL): Each row's data is stored contiguously. Fast for reading/writing a complete record (OLTP).

Column-oriented (Parquet, ORC, Redshift, BigQuery): Each column's data is stored together across all rows. Fast for reading a few columns across many rows (OLAP).

Why it matters for analytics:

# Table with 100M rows, 50 columns
# Query: SELECT SUM(amount), COUNT(*) FROM orders WHERE year = 2025

# Row store: reads ALL 50 columns for ALL 100M rows matching the filter
# Column store: reads ONLY the "amount" and "year" columns — ~4% of the data

# Real impact:
# Parquet file, 100M rows, 50 columns: full scan = 40GB
# Parquet file, same data, reading 2 columns = ~1.6GB — 25x less I/O

Additional benefits of columnar formats:

• Better compression (similar values stored together compress better)
• Predicate pushdown — skip entire row groups based on min/max statistics
• Dictionary encoding — repeated string values stored once

Parquet file metadata you should know:

• Row group — horizontal partition of rows (default: ~128MB)
• Column chunk — one column's data within a row group
• Page — smallest unit, holds actual data + encoding

Question 30: What is partitioning in a data lake? What are the common anti-patterns?

Partitioning means organizing files into directories based on column values. Queries that filter on the partition key skip entire directories without reading them.

# Good partitioning structure
s3://bucket/events/
  year=2026/month=09/day=01/part-00000.parquet
  year=2026/month=09/day=02/part-00000.parquet
  ...

# Query benefits from partition pruning:
SELECT * FROM events WHERE year = 2026 AND month = 9
-- Only scans 30 directories instead of the whole lake

Anti-patterns:

1. 1Over-partitioning (too many small files): Partitioning by user_id on a table with 10M users creates 10M directories with tiny files. Each file requires a metadata call to S3. This is the "small file problem."

1. 2Under-partitioning: Partitioning a table with 1TB/day only by year means every query scans an entire year.

1. 3Partitioning on a high-cardinality string: Partitioning by email or session_id creates millions of partitions.

1. 4Wrong partition key for queries: If every query filters by customer_id but you partition by date, your queries never get pruning benefits.

Rule of thumb: Partition by the columns most commonly used in WHERE clauses. For time-series data: year/month/day is almost always right.

Question 31: Explain the CAP theorem. How does it apply to data systems you use?

The CAP theorem states that a distributed system can guarantee only two of these three properties at any time:

• Consistency (C): Every read sees the most recent write (or an error)
• Availability (A): Every request receives a response (not necessarily the most recent data)
• Partition tolerance (P): The system continues operating despite network partitions

Since network partitions are a physical reality you cannot eliminate, the real choice is CP vs AP:

| System | Choice | Why |

|---|---|---|

| Apache Cassandra | AP | Tunable consistency; prefers availability over strict consistency |

| Apache HBase | CP | Strong consistency; may reject reads during partitions |

| Amazon DynamoDB | AP (default) | Eventually consistent reads by default; strongly consistent optional |

| Apache Kafka | CP (for log) | Committed messages are durable; brokers may be temporarily unavailable |

| PostgreSQL | CP | ACID transactions; blocks rather than returning stale data |

Practical answer for interviews: In data engineering, most analytical workloads tolerate eventual consistency (AP systems are fine). Where you need CP: financial transactions, exactly-once processing guarantees, any system where stale reads cause business logic errors.

Question 32: What is Apache Kafka? Explain its core concepts.

Kafka is a distributed event streaming platform. Producers write events to topics. Consumers read from topics at their own pace. Kafka stores events durably on disk, so consumers can re-read them.

Core concepts:

| Concept | What it is |

|---|---|

| Topic | A named, ordered log of events |

| Partition | Ordered sub-log within a topic (enables parallelism) |

| Offset | Each event's position within a partition |

| Producer | Writes events to a topic |

| Consumer Group | Group of consumers that collectively consume a topic (each partition assigned to one consumer) |

| Broker | A Kafka server that stores partitions |

| Retention | How long Kafka keeps events (by time or size) |

from confluent_kafka import Producer, Consumer

# Producer: write events to Kafka
producer = Producer({'bootstrap.servers': 'kafka:9092'})

def delivery_callback(err, msg):
    if err:
        print(f"Delivery failed: {err}")

producer.produce(
    topic="user-events",
    key="user_123",
    value='{"event": "purchase", "amount": 99.99}',
    callback=delivery_callback
)
producer.flush()

# Consumer: read events from Kafka
consumer = Consumer({
    'bootstrap.servers': 'kafka:9092',
    'group.id': 'analytics-pipeline',
    'auto.offset.reset': 'earliest'
})
consumer.subscribe(["user-events"])

while True:
    msg = consumer.poll(timeout=1.0)
    if msg is None:
        continue
    if msg.error():
        raise Exception(msg.error())
    process(msg.value().decode('utf-8'))
    consumer.commit()

Key interview points:

• Kafka guarantees order within a partition, not across partitions
• Message keys route events to the same partition (important for ordering user events)
• Consumer groups enable horizontal scaling — add consumers to parallelize

Part 7 — System Design

Question 33: Design a pipeline to process 10 billion clickstream events per day.

This is a system design question. Interviewers want to see structured thinking, not a specific "right answer."

Step 1 — Clarify scope:

• What are the outputs? (Real-time dashboards? Daily reports? Both?)
• Latency requirements? (Sub-minute? Sub-hour? Next day OK?)
• Exactly-once or at-least-once semantics required?
• Budget/cloud preference?

Step 2 — Estimate scale:

• 10B events/day = ~116K events/second average
• Assume 3x peak = ~350K events/second at peak
• At 500 bytes/event: ~5TB/day raw

Step 3 — Architecture:

Ingestion: Kafka (100 partitions, 3 replicas)
  - Producers: application servers → Kafka via SDK
  - Schema: Avro with Schema Registry (enforces schema evolution)

Stream Processing: Apache Flink or Spark Structured Streaming
  - Deduplicate events (exactly-once sink to Delta/Iceberg)
  - Real-time aggregations with 1-minute tumbling windows
  - Emit to: (a) Kafka topic for downstream consumers, (b) Redis for live dashboards

Batch Processing: Spark (hourly micro-batch or daily full run)
  - Join clickstream with user dimension (SCD Type 2)
  - Build session-ized views (gap of 30min = new session)
  - Write to Parquet partitioned by date/hour on S3

Serving:
  - Real-time: Druid or ClickHouse (sub-second aggregations on raw events)
  - Historical: Redshift/BigQuery (daily/weekly aggregations)
  - Hot path: Redis for live counters

Orchestration: Airflow for batch jobs
Monitoring: Great Expectations for data quality, CloudWatch/Prometheus for infra

Step 4 — Talk through tradeoffs:

• Flink vs Spark Streaming: Flink has lower latency (true streaming); Spark is easier operationally for teams already running Spark
• Delta vs Iceberg: Delta is better integrated with Databricks/Spark; Iceberg is more multi-engine friendly
• Exactly-once: requires idempotent producers + transactional consumers; adds complexity and latency

Question 34: How would you design a slowly changing dimension Type 2 pipeline that updates in real time?

A real-time SCD Type 2 pipeline must:

1. 1Detect changes in source data
2. 2Expire the old row (set expiry_date, is_current = FALSE)
3. 3Insert a new row for the new version
4. 4Never produce duplicates if run twice

# Pattern: CDC (Change Data Capture) → Kafka → Flink/Spark → Delta Table

# In Spark with Delta Lake MERGE:
from delta.tables import DeltaTable
from pyspark.sql import functions as F
from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension") \
    .getOrCreate()

# CDC events from Kafka: {"op": "U", "before": {...}, "after": {...}}
cdc_df = spark \
    .readStream \
    .format("kafka") \
    .option("kafka.bootstrap.servers", "kafka:9092") \
    .option("subscribe", "customers_cdc") \
    .load()

# Parse and prepare SCD2 updates
updates = cdc_df \
    .select(F.from_json(F.col("value").cast("string"), schema).alias("data")) \
    .select("data.*") \
    .filter(F.col("op").isin(["U", "I"])) \
    .withColumn("effective_date", F.current_date()) \
    .withColumn("expiry_date", F.lit(None).cast("date")) \
    .withColumn("is_current", F.lit(True))

def upsert_to_scd2(micro_batch_df, batch_id):
    dim_table = DeltaTable.forPath(spark, "s3://bucket/delta/dim_customers/")
    
    # Step 1: Expire rows that are being updated
    dim_table.alias("target").merge(
        micro_batch_df.alias("source"),
        "target.customer_id = source.customer_id AND target.is_current = TRUE"
    ).whenMatchedUpdate(
        condition="target.email != source.email OR target.country != source.country",
        set={
            "is_current": "false",
            "expiry_date": "source.effective_date - INTERVAL 1 DAY"
        }
    ).execute()
    
    # Step 2: Insert new versions
    micro_batch_df \
        .write \
        .format("delta") \
        .mode("append") \
        .save("s3://bucket/delta/dim_customers/")

updates.writeStream \
    .foreachBatch(upsert_to_scd2) \
    .option("checkpointLocation", "s3://bucket/checkpoints/dim_customers/") \
    .start()

Part 8 — Advanced Topics

Question 35: What is the difference between at-most-once, at-least-once, and exactly-once semantics in streaming?

This is a fundamental tradeoff in distributed streaming systems.

| Semantic | What it means | Data risk | Where used |

|---|---|---|---|

| At-most-once | Events may be dropped, never duplicated | Data loss | Metrics where loss is acceptable |

| At-least-once | Events are always processed, may be duplicated | Duplicates | Most streaming pipelines |

| Exactly-once | Each event processed exactly once | None | Financial, inventory |

Exactly-once is hard because it requires coordination across the source, processor, and sink. In practice:

• Kafka → Kafka: Kafka's transactional API provides exactly-once within the Kafka ecosystem
• Spark Structured Streaming: Exactly-once with supported sources (Kafka) and idempotent sinks
• Flink: Native exactly-once via distributed snapshots (Chandy-Lamport algorithm)

The practical answer: Use at-least-once + idempotent writes. An idempotent sink (upsert by unique key) gives you the business semantics of exactly-once with simpler infrastructure.

Question 36: Explain Apache Iceberg. Why is it becoming the preferred table format?

Apache Iceberg is an open table format for large analytic tables stored as files (Parquet, ORC, Avro) on object storage (S3, GCS). It adds warehouse-like features (ACID, schema evolution, time travel) to files you own.

Key features:

-- Schema evolution: add/drop/rename columns without rewriting data
ALTER TABLE orders ADD COLUMN discount_amount DOUBLE;
ALTER TABLE orders RENAME COLUMN user_id TO customer_id;

-- Partition evolution: change partitioning without rewriting data
ALTER TABLE events REPLACE PARTITION FIELD days(event_time) WITH hours(event_time);

-- Time travel
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2026-08-01 00:00:00';
SELECT * FROM orders FOR VERSION AS OF 42;

-- Row-level deletes (GDPR compliance)
DELETE FROM customers WHERE customer_id = 'cust_123';

-- Incremental read: only new files since snapshot 42
SELECT * FROM orders FOR SYSTEM_TIME BETWEEN TIMESTAMP '2026-01-01' AND TIMESTAMP '2026-02-01';

Why it's winning over Delta Lake in multi-engine setups:

• Engine-agnostic: works with Spark, Flink, Trino, Dremio, Athena, BigQuery Omni
• The spec is open; anyone can implement a reader/writer
• Partition evolution is a massive operational advantage (no need to rewrite 5TB when query patterns change)

Question 37: What is Z-ordering (data skipping) and when should you use it?

Z-ordering is a multi-dimensional data clustering technique that stores correlated rows together in the same Parquet files. It reduces the number of files that need to be read when filtering on multiple columns simultaneously.

# Delta Lake Z-ORDER
from delta.tables import DeltaTable

delta_table = DeltaTable.forPath(spark, "s3://bucket/delta/events/")
delta_table.optimize().executeZOrderBy("user_id", "event_type")

# Now this query only reads files that contain the matching user_id + event_type combos
spark.sql("""
    SELECT * FROM events
    WHERE user_id = 'u_12345'
    AND event_type = 'purchase'
""")

Use Z-order when:

• You frequently filter on two or more columns simultaneously
• Those columns are not partition keys (you already have partition pruning)
• The table is large enough that file-level skipping matters (>10GB)

Do not Z-order on partition keys — Spark already skips partitions; Z-ordering within a partition on the same key gives no additional benefit.

Question 38: Explain partitioning strategies in Apache Kafka. How does partition count affect throughput?

Kafka partitions determine parallelism. Each partition is an ordered log consumed by exactly one consumer within a consumer group.

Producer routing:

• No key → round-robin across partitions
• With key → hash(key) % num_partitions → same key always same partition (ordering guarantee)
• Custom partitioner → you decide

from confluent_kafka import Producer

producer = Producer({'bootstrap.servers': 'kafka:9092'})

# Events with the same user_id go to the same partition
# This guarantees order for that user's events
producer.produce(
    topic="user-events",
    key="user_123",       # hash determines partition
    value=event_json
)

# Events without a key are load-balanced
producer.produce(
    topic="metrics",
    key=None,             # round-robin
    value=metric_json
)

Partition count = max consumer parallelism. If you have 20 partitions, you can have at most 20 consumers in a group processing in parallel. The 21st consumer sits idle.

How many partitions:

• Rule of thumb: max(target throughput / throughput per partition, desired consumer parallelism)
• A single partition handles ~10-100MB/s depending on hardware
• More partitions = more leader elections, more files on broker disk, more metadata overhead
• You can increase partition count later, but you cannot decrease it (changing hash(key) % n breaks ordering)

Question 39: What is a compaction job and why do data lakehouses need it?

Compaction merges many small Parquet files into fewer, larger ones. It improves read performance because reading 1 file of 128MB is faster than reading 1000 files of 128KB (less S3 API overhead, better Parquet statistics, more effective compression).

Why small files accumulate:

• Streaming ingestion writes small files continuously
• Frequent small batch jobs (every 5 minutes) produce small files
• SCD2 pipelines add one row at a time

# Delta Lake: OPTIMIZE command compacts small files
spark.sql("OPTIMIZE events WHERE date >= '2026-09-01'")

# Also: VACUUM to remove old files (orphaned data files, old versions)
spark.sql("VACUUM events RETAIN 168 HOURS")  # keep 7 days of history

# Iceberg: rewrite data files below a size threshold
spark.sql("""
    CALL catalog.system.rewrite_data_files(
        table => 'db.events',
        options => map('min-input-files', '5', 'target-file-size-bytes', '134217728')
    )
""")

# Airflow task: run compaction daily during low-traffic window
def compact_recent_partitions():
    for days_ago in range(1, 8):
        date_str = (datetime.today() - timedelta(days=days_ago)).strftime('%Y-%m-%d')
        spark.sql(f"OPTIMIZE events WHERE date = '{date_str}'")

Question 40: How does Apache Flink differ from Spark Streaming?

| Dimension | Apache Flink | Spark Structured Streaming |

|---|---|---|

| Processing model | True streaming (event-by-event) | Micro-batch (default) or continuous |

| Latency | Sub-millisecond to milliseconds | 100ms+ (micro-batch) |

| State management | Rich, built-in stateful operators | Limited; external state stores |

| Exactly-once | Native, distributed snapshots | Yes, with supported sources/sinks |

| Checkpointing | Async, incremental | Synchronous write-ahead log |

| Windowing | Event time, processing time, ingestion time — all native | Event time and processing time |

| Operational complexity | Higher (separate cluster) | Lower (same Spark cluster) |

| SQL | Flink SQL (full-featured) | Spark SQL (familiar) |

When to choose Flink:

• Sub-second latency requirements
• Complex stateful operations (session windows, CEP — Complex Event Processing)
• High-volume, low-latency event processing at scale

When to choose Spark Streaming:

• Team already uses Spark
• Micro-batch latency is acceptable
• Want to share code between batch and streaming jobs

Part 9 — Behavioral and Situational

Question 41: Tell me about a time a pipeline you built failed in production. What happened and what did you learn?

What they want to hear: Ownership, structured analysis (not blame), and a concrete fix.

Structure your answer (STAR):

• Situation: Describe the pipeline, what it did, what broke, what the downstream impact was
• Task: What was your role in fixing it?
• Action: Walk through the investigation step-by-step (don't skip the debugging process)
• Result: What fix did you ship? What did you put in place to prevent recurrence?

Things that make a strong answer:

• You caught it before the user did (monitoring)
• You had runbooks / incident documentation
• You added an automated test that would have caught the bug
• You made the fix idempotent so re-running was safe

Red flags in a weak answer:

• Blaming upstream teams or the cloud provider with no ownership
• "I just fixed the bug" with no mention of root cause or prevention

Question 42: How do you handle disagreements with stakeholders about pipeline requirements?

What they want to hear: You can translate between business requirements and engineering constraints, and you can say "no" constructively.

Strong answer structure:

1. 1Clarify the actual business need (sometimes the stated requirement isn't the real need)
2. 2Quantify the tradeoff: "That would add 3 weeks of engineering time and reduce reliability by X"
3. 3Propose an alternative that meets the underlying need
4. 4Document the decision and its rationale

Example to use: A stakeholder asks for "real-time" data when their actual need is a dashboard that refreshes every 15 minutes. Real-time streaming infrastructure might cost 10x more and add operational complexity. A 15-minute micro-batch achieves the same result for them at a fraction of the cost.

Question 43: How do you decide when a pipeline needs to be rewritten vs. incrementally improved?

Incremental improvement is usually right when:

• The core architecture is sound, but specific components are slow or brittle
• The team understands the codebase
• The cost of rewrite (migration, downtime, regression risk) exceeds the cost of continued maintenance

Rewrite is worth considering when:

• The pipeline regularly fails in ways that incremental fixes cannot address
• The architecture is fundamentally wrong for the current scale (e.g., single-threaded Python script now processing 100GB/day)
• The codebase is so undocumented and tangled that each fix introduces two new bugs
• The underlying technology is end-of-life

The answer interviewers want: A bias toward incremental improvement (rewrites are almost always underestimated in complexity and overestimated in benefit), with clear criteria for when a rewrite is genuinely justified.

Part 10 — Rapid-Fire Questions

Question 44: What is the difference between `INNER JOIN`, `LEFT JOIN`, and `FULL OUTER JOIN`?

-- INNER JOIN: only rows that match in BOTH tables
SELECT o.order_id, c.name
FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id;
-- Result: only orders with a matching customer record

-- LEFT JOIN: all rows from left table, NULLs for non-matching right rows
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;
-- Result: every customer, including those with 0 orders (order_count = 0)

-- FULL OUTER JOIN: all rows from both tables
SELECT c.name, o.order_id
FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
-- Result: customers with no orders AND orders with no customer (orphaned records)
-- Useful for finding data quality issues

Question 45: What is a CTE and when would you use it over a subquery?

A CTE (Common Table Expression) is a named, temporary result set defined with WITH. It runs once, can be referenced multiple times in the query, and often makes SQL more readable.

-- Subquery (nested, hard to read when complex)
SELECT department, AVG(salary)
FROM (
  SELECT e.*, d.department_name AS department
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  WHERE e.hire_date >= '2023-01-01'
) recent_hires
GROUP BY department;

-- CTE (same logic, more readable)
WITH recent_hires AS (
  SELECT e.*, d.department_name AS department
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  WHERE e.hire_date >= '2023-01-01'
)
SELECT department, AVG(salary)
FROM recent_hires
GROUP BY department;

-- CTEs shine when referenced multiple times
WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  m1.month,
  m1.revenue,
  m2.revenue AS prev_month_revenue,
  (m1.revenue - m2.revenue) / m2.revenue * 100 AS growth_pct
FROM monthly_revenue m1
LEFT JOIN monthly_revenue m2
  ON m1.month = m2.month + INTERVAL '1 month';
-- The CTE is computed once, referenced twice

Question 46: What is the difference between horizontal and vertical scaling? Which applies to Spark?

Vertical scaling: Make one machine bigger (more RAM, more CPU cores). Simple but has a ceiling — there is a limit to how big one machine can get, and it creates a single point of failure.

Horizontal scaling: Add more machines to a cluster. No hard ceiling, fault-tolerant, but requires distributed coordination overhead.

Spark is designed for horizontal scaling. You add executor nodes to handle more data. The driver coordinates work; executors run tasks in parallel. One executor failing does not fail the job — Spark retries failed tasks on other executors.

Question 47: What does ACID stand for, and which data warehouse formats support it?

ACID:

• Atomicity: A transaction either fully completes or fully rolls back. No partial writes.
• Consistency: The database transitions from one valid state to another. Constraints are never violated.
• Isolation: Concurrent transactions don't interfere with each other. One reader doesn't see another writer's in-progress changes.
• Durability: Committed transactions survive system failures.

Support by format:

| Format | ACID | Notes |

|---|---|---|

| Raw Parquet/ORC on S3 | No | Just files — no transaction layer |

| Delta Lake | Yes | Optimistic concurrency control via transaction log |

| Apache Iceberg | Yes | Snapshot isolation via metadata tree |

| Apache Hudi | Yes | Record-level updates via merge-on-read or copy-on-write |

| Snowflake | Yes | Full ACID, managed |

| BigQuery | Yes | Full ACID, managed |

Question 48: How does predicate pushdown work in Parquet?

When Spark reads a Parquet file, it doesn't have to read every row. Parquet files embed statistics (min, max, null count) for each column in each row group. The Spark Parquet reader checks these statistics before reading the actual data.

# This query benefits from predicate pushdown:
df = spark.read.parquet("s3://bucket/events/")
result = df.filter(F.col("amount") > 1000)

# Spark reads the Parquet footer, sees row group statistics:
# Row group 1: amount min=5, max=500  → SKIP (max < 1000)
# Row group 2: amount min=800, max=5000 → READ (may contain values > 1000)
# Row group 3: amount min=1, max=200 → SKIP

# Result: reads only row groups that COULD contain matching rows
# For a well-sorted or clustered column, this skips the vast majority of data

How to maximize predicate pushdown:

• Cluster your data on the columns you filter most (Z-order, sort-within-partitions)
• Use Parquet (not JSON or CSV — those don't have column statistics)
• Push filters as early as possible in your Spark query (Catalyst does most of this automatically)

Question 49: What is a watermark in Spark Structured Streaming?

A watermark tells Spark how long to wait for late-arriving events before closing a time window. Without a watermark, Spark must keep all past windows in state indefinitely (memory leak).

# Without watermark: Spark holds state forever waiting for late events
df.groupBy(F.window("event_time", "1 hour")).agg(F.count("*"))
# MemoryError after running for days

# With watermark: discard events older than 2 hours
result = df \
    .withWatermark("event_time", "2 hours") \
    .groupBy(
        F.window("event_time", "1 hour"),
        F.col("event_type")
    ) \
    .agg(F.count("*").alias("count"))
# Spark finalizes and drops windows older than: max(event_time seen) - 2 hours

Tradeoff: A larger watermark tolerates more late arrivals but increases state size and output latency. A smaller watermark is more memory-efficient but drops more late events.

Question 50: What is the difference between `map` and `flatMap` in Spark (and Python)?

map applies a function to each element and returns one output per input. flatMap applies a function that returns a collection per input, then flattens all collections into one.

# Python example
words = ["hello world", "data engineering", "spark kafka"]

# map: one list per input
mapped = list(map(lambda s: s.split(), words))
# [['hello', 'world'], ['data', 'engineering'], ['spark', 'kafka']]

# flatMap: flatten into one list
flat_mapped = [w for s in words for w in s.split()]
# ['hello', 'world', 'data', 'engineering', 'spark', 'kafka']

# Spark RDD equivalent
rdd = spark.sparkContext.parallelize(words)
rdd.map(lambda s: s.split()).collect()
# [['hello', 'world'], ['data', 'engineering'], ['spark', 'kafka']]

rdd.flatMap(lambda s: s.split()).collect()
# ['hello', 'world', 'data', 'engineering', 'spark', 'kafka']

In data engineering, flatMap is used whenever one input record should produce multiple output records — parsing multi-value fields, exploding arrays, tokenizing text.

# DataFrame equivalent: explode
from pyspark.sql import functions as F

df = spark.createDataFrame([("u1", ["tag1", "tag2", "tag3"]),], ["user_id", "tags"])
df.withColumn("tag", F.explode("tags")).show()
# +-------+----+
# |user_id| tag|
# +-------+----+
# |     u1|tag1|
# |     u1|tag2|
# |     u1|tag3|

The Day Before Your Interview

Three things matter more than any individual question:

1. Know your past work cold. Interviewers will ask "tell me about a pipeline you designed." Have two or three war stories prepared — one where things went wrong, one where you made an architectural decision under uncertainty. Know the numbers (scale, latency, team size).

2. Think out loud in system design. You are not graded on getting to the "right answer." You are graded on how you reason through the problem. State your assumptions. Quantify your estimates. Name the tradeoffs explicitly.

3. Ask clarifying questions before coding. Every coding question has unstated constraints. Ask about edge cases, expected input size, and whether the solution needs to handle failures. This is what senior engineers do.

The questions in this guide cover the concepts that appear most often. Study the ones you found hard. Run the code. Build the intuition.

*Want to practice answering these out loud? That is where most candidates fall short — they understand the concepts but struggle to articulate them under pressure. Verbal practice with real feedback is what closes the gap.*