Data Engineering Interview Questions 2026: SQL, Pipelines, Spark, and System Design

Complete preparation guide for data engineering interviews covering advanced SQL, pipeline architecture, Apache Spark optimization, data modeling, and real-world scenario questions asked at top companies.

Data Engineering Interview Structure in 2026

Data engineering interviews typically consist of 4-5 rounds:

1. SQL Round (45-60 min) — Complex queries, window functions, optimization
2. Coding Round (45-60 min) — Python/Scala data processing, ETL logic
3. Data Pipeline System Design (45-60 min) — Architecture of data-intensive systems
4. Domain Knowledge (45 min) — Tools, concepts, trade-offs in data infrastructure
5. Behavioral (30-45 min) — Team collaboration, project experience

The weight distribution varies by company:

• Amazon/Google: Heavy on system design (40%) + SQL (30%)
• Meta: Heavy on SQL (40%) + coding (30%)
• Startups: Heavy on practical coding (40%) + pipeline design (30%)
• Databricks/Snowflake: Heavy on distributed systems knowledge (40%)

SQL Mastery: The Non-Negotiable Foundation

Window Functions (Asked in 95% of Data Engineering Interviews)

You must be able to write these from memory:

Ranking functions:

• ROW_NUMBER() — Unique sequential numbers (1, 2, 3, 4)
• RANK() — Same rank for ties, gaps after (1, 2, 2, 4)
• DENSE_RANK() — Same rank for ties, no gaps (1, 2, 2, 3)
• NTILE(n) — Divides into n equal groups

Analytic functions:

• LAG(col, n) — Access previous row's value
• LEAD(col, n) — Access next row's value
• FIRST_VALUE(col) / LAST_VALUE(col) — First/last in window
• SUM/AVG/COUNT OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)

Common patterns with window functions:

1. Running totals: SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)
2. Moving averages: AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
3. Year-over-year comparison: LAG(revenue, 12) OVER (ORDER BY month)
4. Top-N per group: ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) WHERE rn <= 3
5. Sessionization: Assign session IDs based on time gaps between events

Advanced SQL Patterns

Gap and Island Problems:

Finding consecutive sequences (dates without gaps, sequences of events). Uses LAG/LEAD or ROW_NUMBER difference technique.

Self-Joins:

Hierarchical data (employee-manager), comparisons within the same table, matching events.

Recursive CTEs:

Tree traversal in SQL, expanding hierarchies, generating date sequences.

Correlated Subqueries:

Row-by-row processing where the subquery depends on the outer query. Important for understanding query optimizer behavior.

Query Optimization (Critical for Senior Roles)

Index strategies:

• B-tree indexes for equality and range queries
• Composite indexes: order matters (most selective column first for equality, range column last)
• Covering indexes: include all selected columns to avoid table lookups
• Partial indexes: index only rows matching a condition

Query plan reading:

• Sequential Scan (bad for large tables — missing index)
• Index Scan vs Index Only Scan (latter avoids table access)
• Hash Join vs Nested Loop vs Merge Join (know when each is optimal)
• Sort operations (ORDER BY, DISTINCT, GROUP BY without matching index)

Optimization techniques:

• Avoid SELECT * (retrieve only needed columns)
• Predicate pushdown (filter early in CTEs/subqueries)
• Materialized views for expensive aggregations
• Partitioning for time-series data (query pruning)
• EXPLAIN ANALYZE — measure actual vs estimated costs

Data Pipeline Design (The Core Interview)

The Standard Pipeline Architecture

Source → Ingestion → Processing → Storage → Serving → Monitoring

Each stage has design choices:

Ingestion:

• Batch: Scheduled pulls (hourly/daily), file-based (S3 → processor)
• Streaming: Event-driven (Kafka, Kinesis), CDC (Debezium)
• Hybrid: Lambda architecture (batch + stream paths)

Processing:

• Batch: Spark, dbt, Airflow-orchestrated SQL
• Stream: Flink, Spark Structured Streaming, Kafka Streams
• Micro-batch: Spark with short intervals (best of both worlds)

Storage:

• Data Lake: S3/GCS with Parquet/Delta/Iceberg (schema-on-read)
• Data Warehouse: Snowflake, BigQuery, Redshift (schema-on-write)
• Feature Store: For ML features (Feast, Tecton)
• OLTP: PostgreSQL, DynamoDB (for serving layer)

8 Pipeline Design Questions You Must Practice

1. Design a real-time analytics pipeline for an e-commerce platform

• Key aspects: click stream ingestion, session stitching, real-time dashboards, historical roll-ups

1. Design a data quality monitoring system

• Key aspects: schema validation, freshness checks, statistical anomaly detection, alerting, data contracts

1. Design a change data capture (CDC) pipeline

• Key aspects: Debezium, log-based vs trigger-based, exactly-once semantics, schema evolution handling

1. Design a recommendation system data pipeline

• Key aspects: user event processing, feature engineering, batch model training vs real-time inference, A/B test data

1. Design a data lake architecture for a growing startup

• Key aspects: medallion architecture (bronze/silver/gold), governance, cost optimization, access patterns

1. Design an event-driven architecture for microservices

• Key aspects: event schema registry, dead letter queues, event sourcing, CQRS pattern

1. Design a pipeline that handles late-arriving data

• Key aspects: watermarking, allowed lateness, reprocessing strategies, SLA definitions

1. Design a cross-region data replication system

• Key aspects: consistency models, conflict resolution, bandwidth optimization, compliance (GDPR)

Design Principles That Impress Interviewers

1. Idempotency — Every pipeline stage should produce the same output if run multiple times
2. Schema evolution — Handle added/removed/renamed columns without breaking downstream
3. Backfill capability — Any pipeline should be re-runnable for historical dates
4. Data lineage — Track which source data produced which output
5. Exactly-once semantics — Understand where and how to guarantee this
6. Separation of storage and compute — Modern architectures decouple these

Apache Spark Interview Topics

Core Concepts

• RDD vs DataFrame vs Dataset — Evolution of Spark APIs, when to use each
• Transformations vs Actions — Lazy evaluation, DAG construction, triggering execution
• Partitioning — How data is distributed, repartition vs coalesce
• Shuffle — The most expensive operation. Causes: joins, groupBy, repartition
• Catalyst Optimizer — How Spark optimizes query plans (predicate pushdown, projection pruning)
• Tungsten — Memory management, off-heap storage, code generation

Common Spark Interview Questions

"How would you handle data skew in a Spark join?"

• Salting technique (add random prefix to skewed key, replicate small table)
• Broadcast join if one side is small (< 10MB default, configurable)
• Adaptive Query Execution in Spark 3+ (automatic skew handling)
• Custom partitioner for known skew patterns

"Explain the difference between narrow and wide transformations"

• Narrow: Each input partition contributes to at most one output partition (map, filter, union)
• Wide: Input partitions contribute to multiple output partitions (groupBy, join, repartition)
• Wide transformations trigger shuffles (expensive network I/O)

"How do you optimize a Spark job that's running slowly?"

1. Check Spark UI for stage durations and shuffle sizes
2. Look for data skew (some tasks taking 10x longer)
3. Check for spill to disk (insufficient memory per executor)
4. Reduce shuffles (use broadcast joins, pre-partition data)
5. Optimize serialization (use Parquet, enable Kryo serializer)
6. Tune parallelism (spark.sql.shuffle.partitions, spark.default.parallelism)

Data Modeling Interview Questions

Dimensional Modeling

Star Schema:

• Central fact table (measures: revenue, quantity, clicks)
• Dimension tables (who, what, where, when: customer, product, location, date)
• Best for: OLAP queries, BI dashboards, simple joins

Snowflake Schema:

• Normalized dimensions (dimension tables have sub-dimensions)
• Best for: Reducing redundancy, storage efficiency
• Trade-off: More joins required for queries

When to denormalize:

• When query performance matters more than storage
• When write patterns are infrequent (batch loads)
• When analysts need simple queries (star schema enables this)

Slowly Changing Dimensions (SCD)

Type 1: Overwrite old value (lose history)

Type 2: Add new row with versioning (effective_from, effective_to, is_current)

Type 3: Add new column for previous value (limited history)

Interview question: "Customer changes their address. How do you track both current and historical orders?"

Answer: SCD Type 2 on the customer dimension. Orders reference the customer dimension key (surrogate key), so historical orders point to the old address record, new orders point to the new address record.

Modern Data Modeling

Activity Schema:

• Event-centric (user_id, event_type, timestamp, properties JSON)
• Flexible schema for varied events
• Popular in product analytics (Amplitude, Mixpanel)

One Big Table (OBT):

• Pre-joined, wide table with all needed columns
• Optimized for specific query patterns
• Trade-off: high redundancy, complex maintenance

Data Vault:

• Hubs (business keys), Links (relationships), Satellites (attributes)
• Designed for enterprise data warehousing
• Supports full history and auditability

Tools You Must Know (And When to Use Each)

Orchestration

• Airflow: Standard for batch workflows. DAG-based, Python-native, huge community.
• Dagster: Modern alternative. Asset-based, better testing, native observability.
• Prefect: Simpler than Airflow, good for smaller teams, cloud-native.

Processing

• Spark: De facto for large-scale batch and streaming. Use for TB+ datasets.
• dbt: SQL-based transformation. Use for warehouse-native ELT (Snowflake, BigQuery).
• Flink: True stream processing. Use for low-latency real-time requirements (< 1 second).
• Polars: Rust-based DataFrame library. Use for single-machine processing (faster than Pandas for medium data).

Storage

• Delta Lake / Apache Iceberg: Table formats on data lakes. ACID transactions, time travel, schema evolution.
• Snowflake / BigQuery / Redshift: Managed data warehouses. Use for analytics workloads.
• Apache Kafka: Event streaming platform. Use as the backbone for real-time pipelines.

Data Quality

• Great Expectations: Open-source data validation
• Monte Carlo / Anomalo: Automated data observability
• dbt tests: Built-in testing for SQL transformations

Behavioral Questions for Data Engineers

Data engineering behavioral questions focus on:

• "Tell me about a data pipeline that failed in production. How did you handle it?"
• "How do you handle conflicting priorities between data consumers?"
• "Describe a time you had to make a trade-off between data freshness and accuracy"
• "How do you ensure data quality in pipelines you own?"

Use STAR format: Situation → Task → Action → Result. Include specific metrics where possible.