Data engineering interview questions in Spark & Data Eng

Data engineering interview questions in Spark & Data Eng

When you're preparing for a senior data engineering role or conducting interviews on your team, the questions that separate solid engineers from great ones are those grounded in production reality. Data engineering interview questions in Spark & Data Eng tend to focus less on trivia and more on the architectural and operational decisions you make daily. We'll walk through four critical topics that come up consistently: the ETL vs ELT decision, idempotency guarantees, late data handling, and dimensional modeling—each with real scenarios you've likely faced.

ETL vs ELT: Where transformations happen

This question often comes early in interviews because the answer shapes everything downstream. ETL (Extract, Transform, Load) applies transformations in a separate layer before loading into your warehouse. ELT (Extract, Load, Transform) loads raw data first, then transforms it in the target system. Neither is universally correct; the choice depends on your infrastructure and team.

In a typical e-commerce pipeline, you might choose ELT if you're landing events into S3 and Redshift. Raw events flow into S3, then get loaded as-is into Redshift, where dbt or Spark SQL handles aggregations and joins. The advantage: you keep a complete audit trail of raw data, and transformation logic lives in version control alongside your analytics team's work. Storage is cheap; reprocessing from raw is expensive only in compute.

Conversely, ETL makes sense when you're pulling from multiple transactional databases and need heavy transformation before loading a data warehouse. Say you're ingesting from a legacy CRM, an e-commerce platform, and a payment processor. These sources have conflicting schemas, duplicates, and PII. You transform in a Spark cluster running on EC2 or Kubernetes, clean and enrich, then write clean parquet to S3 before loading to Redshift. The transformation layer acts as a contract: analysts trust what lands in the warehouse because it's been vetted.

The practical answer in interviews: explain your constraints. If you have a strong analytics engineering culture with dbt, go ELT. If your sources are chaotic and you need a centralized transformation layer, go ETL. Most teams end up doing both—ELT for cloud-native flows, ETL for legacy system integrations.

How do you make pipelines idempotent?

Idempotency is non-negotiable in production pipelines. An idempotent pipeline produces the same result whether run once or ten times with identical input. Without it, retries become dangerous, and your team loses trust in backfills.

The core principle: use deterministic logic and overwrite partitions rather than append. In Spark, this means always writing with mode("overwrite") when targeting a specific date partition, not appending rows.

# Idempotent: overwrites the partition for 2024-01-15
df_processed = (
    spark.read.parquet("s3://raw-events/")
    .filter(F.col("event_date") == "2024-01-15")
    .groupBy("user_id", "event_type")
    .agg(F.count("*").alias("count"))
)

df_processed.write \
    .mode("overwrite") \
    .partitionBy("event_date") \
    .parquet("s3://processed-events/")

# NOT idempotent: appends, so re-runs duplicate data
df_processed.write.mode("append").parquet("s3://processed-events/")

Key patterns for idempotency:

• Partition pruning: Always filter to the specific partition being processed. Use upstream metadata or timestamps to determine scope.
• Deterministic transformations: Avoid non-deterministic functions like rand() or current_timestamp in join keys. Use static reference dates.
• Deduplication before insert: If loading a slowly-changing dimension, use a surrogate key and upsert logic (or full table rewrites for small dims).
• Checkpoint idempotency in Structured Streaming: Set option("checkpointLocation", path) so reruns recover from the last committed batch, not the beginning.

Real scenario: a ride-hailing team's daily trip aggregation failed midway. They re-ran it the next day without changing partition logic—because they overwrote the partition, not appended—and got identical results. Their dashboards didn't double-count trips. That's idempotency paying dividends.

How do you handle late data?

Late data is the silent killer in streaming and batch pipelines. A user's ride ends at 3:05 PM, but the event arrives at your Kafka topic at 4:30 PM due to client buffering. Your hourly aggregation for the 3 PM hour already ran and closed. Now you have inconsistent totals.

Watermarks solve this in Structured Streaming. A watermark tells Spark: "Don't finalize results until we're confident no events older than this timestamp will arrive."

from pyspark.sql.functions import window, col, count

# Structured Streaming with watermark
df = spark.readStream.format("kafka").option("kafka.bootstrap.servers", "localhost:9092").load()

events = df.select(
    F.from_json(col("value").cast("string"), schema).alias("data")
).select("data.*")

# 10-minute watermark: tolerate events up to 10 minutes late
windowed = events \
    .withWatermark("event_time", "10 minutes") \
    .groupBy(window(col("event_time"), "1 hour")) \
    .agg(count("*").alias("trip_count"))

query = windowed.writeStream \
    .outputMode("update") \
    .format("parquet") \
    .option("path", "s3://aggregated-trips/") \
    .option("checkpointLocation", "s3://checkpoints/trips/") \
    .start()

For batch pipelines, handle late data via reprocessing windows. Calculate aggregates for a wider time range, then backfill recent partitions. For example, if events can arrive up to 24 hours late, your nightly job processes the last 3 days, overwriting those partitions. This catches stragglers without breaking historical data.

In a travel platform, late booking confirmations arrived 12–36 hours after the booking transaction. The team widened their processing window to 48 hours, reprocessing the last 3 days every night. Revenue reports updated as confirmations came in, without requiring ad-hoc backfills.

Star schema and dimensional modeling

The star schema is foundational for analytics warehouses. A central fact table (highly granular, immutable records of events or transactions) connects via foreign keys to slowly-changing dimension tables (users, products, locations). It's not a Spark question per se, but understanding why we build this way shapes your pipeline design.

A fact table in an e-commerce context might have one row per order line item:

And a corresponding user dimension:

Why this matters: the fact table is immutable and additive. You never update old orders; you only append new ones. Dimensions are slowly-changing; when a user updates their email, you add a new row with a new effective date (Type 2 SCD). Joins are straightforward, queries are fast because dimensions are denormalized (each row is self-contained), and data lineage is clear.

In Spark, you build this by processing raw events into facts, then enriching with dimension lookups during transform:

# Load fact data (raw orders)
orders = spark.read.parquet("s3://raw-orders/date=2024-01-15/")

# Load latest dimension snapshot
users = spark.read.parquet("s3://dim-users/").filter(col("end_date").isNull())

# Join and write fact table
fact = orders.join(users, "user_id", "left") \
    .select(
        col("order_id"),
        col("order_date"),
        col("user_key"),  # Use surrogate key, not user_id
        col("product_id"),
        col("quantity"),
        col("price")
    )

fact.write.mode("overwrite").partitionBy("order_date").parquet("s3://fact-orders/")

Use the user_key (surrogate key) in the fact table, not user_id. If a user's email changes, their user_key remains the same in old orders, but a new dimension row tracks the change. Analytics queries join on the key and pick up the correct historical context.

Common mistakes

• Confusing idempotency with determinism: A pipeline can be deterministic (same input, same output) but not idempotent if it appends instead of overwrites. Always overwrite partitions.
• Ignoring watermarks in streaming: Streaming pipelines without watermarks will silently lose or double-count late data. Set reasonable tolerances and test.
• Using business keys instead of surrogate keys in facts: When a product ID is reassigned or a user migrates, your fact table breaks. Surrogate keys isolate facts from dimension churn.
• Over-normalizing dimensions: Dimensions should be wide and denormalized. Avoid joins within dimension tables; flatten them. This speeds up fact queries and simplifies lineage.
• Picking ETL or ELT without understanding your team: The best architecture is one your team can operate. If no one knows dbt, ELT in SQL is painful. If your sources are clean, over-engineering a complex ETL layer is waste.

Key takeaways

• ETL centralizes transformation logic; ELT keeps raw data as source of truth. Choose based on source complexity and team expertise.
• Idempotency requires deterministic logic and partition overwrites, not appends. Test by re-running pipelines and verifying identical output.
• Late data in streaming requires watermarks; in batch, use wider reprocessing windows and partition overwrites to catch stragglers.
• Star schemas (facts + dimensions) separate immutable transactional records from slowly-changing context, enabling fast joins and clear lineage.
• Use surrogate keys in facts, not business keys, to decouple fact tables from dimension evolution.
• Idempotency, late-data handling, and dimensional modeling are production concerns, not academic exercises—they determine whether your team trusts the pipeline.