Data Engineer Interview Help — AI for SQL, Modeling, Pipelines & Spark

1. Advanced SQL

Beyond joins: window functions, complex aggregations, deduplication, slowly-changing dimensions, and query optimization. The AI writes the correct query and names the pattern. See SQL interview help.

2. Data modeling

Star vs snowflake schema, normalization vs denormalization, fact and dimension tables, partitioning, and choosing keys. "Model this analytics use case." The AI lays out the dimensional model and the trade-offs.

3. Pipelines & data system design

ETL/ELT design, batch vs streaming (Spark, Kafka), idempotency and backfills, data quality, and "design a pipeline for X." The AI provides the standard skeleton: source → ingest → transform → store → serve, plus failure handling.

1. "What's the difference between ETL and ELT?"

ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms inside the warehouse using its compute. ELT suits cloud warehouses and large volumes where storage is cheap and you want raw data retained; name that trade-off explicitly.

2. "Batch vs streaming — how do you decide?"

Batch processes bounded chunks on a schedule (high throughput, higher latency); streaming processes events continuously with tools like Kafka and Spark Streaming (low latency, harder semantics). Choose by the freshness requirement, and mention exactly-once vs at-least-once delivery.

3. "Star vs snowflake schema — when would you use each?"

A star schema has a central fact table joined to denormalized dimension tables — simple and fast for analytics. A snowflake normalizes those dimensions to save space at the cost of more joins. Define the fact's grain first, since it drives the whole model.

4. "How do partitioning and bucketing improve query performance?"

Partitioning splits data by a column (e.g. date) so the engine prunes irrelevant files and scans less; bucketing hashes a column into a fixed number of buckets to make joins and aggregations cheaper. Pick partition keys with reasonable cardinality to avoid tiny-file and skew problems.

5. "Write a query using window functions to deduplicate or rank rows."

Reach for ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) and keep rows where the number equals 1 to dedupe, or use RANK / LAG / LEAD for running comparisons. Naming the pattern (window function vs GROUP BY) is half the signal.

6. "Why is my Spark job slow?"

Usually a wide transformation causing an expensive shuffle, or data skew where one partition is huge. Mitigate with better partitioning, broadcast joins for small tables, filtering early, and caching reused datasets. Show you reason about the physical plan, not just the SQL.

7. "How do you make a pipeline idempotent and ensure data quality?"

Design for replay: use deterministic transforms, upserts/merge keyed on a natural or surrogate key, and idempotent writes so a re-run produces the same result rather than duplicates. Add validation checks (row counts, null/range constraints, schema enforcement) and orchestrate with an Airflow DAG that handles retries and backfills.