Data Engineer Interview Questions

How to approach this

Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) in a CTE, then filter WHERE rn = 1. Mention why ROW_NUMBER is better than DISTINCT or GROUP BY for this problem: you control which duplicate to keep, and you can access all columns without re-joining. Bring up the fact that this is a daily pipeline operation, not just an interview trick.

How to approach this

Aggregate to monthly grain with DATE_TRUNC and SUM, then use LAG to get previous month revenue in a CTE. Compute (current - previous) / NULLIF(previous, 0) * 100. Explain why NULLIF prevents division by zero for the first month. Interviewers will ask about the first row where LAG returns NULL.

How to approach this

This is a gap-and-island problem. Assign ROW_NUMBER() within each user ordered by date, then subtract row_number from the date to create a grouping key. Consecutive dates produce the same grouping key. GROUP BY user and grouping key, then HAVING COUNT(*) >= 3. Walk the interviewer through why the subtraction trick works with a small example.

How to approach this

JOIN employees e1 ON employees e2 WHERE e1.manager_id = e2.manager_id AND e1.id < e2.id. The inequality condition (< not !=) prevents duplicate pairs (Alice-Bob and Bob-Alice). Mention that without this condition, you get each pair twice plus self-pairs.

How to approach this

Base case: SELECT employees WHERE manager_id = target. Recursive case: JOIN employees to the CTE on manager_id. Include a depth counter. Mention the importance of a termination condition to prevent infinite loops in circular org data (add WHERE depth < 20 or track visited IDs).

How to approach this

Use AVG with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Wrap in a CTE and add ROW_NUMBER to filter out the first 6 rows (partial windows). Explain ROWS vs RANGE: RANGE groups ties, ROWS counts physical rows. Most candidates forget about partial windows; bringing it up proactively impresses interviewers.

How to approach this

DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC). Filter WHERE rank <= 3. Explain why DENSE_RANK, not RANK or ROW_NUMBER: DENSE_RANK keeps all tied products and does not skip ranks, so you get a true top-3 even with ties. RANK would skip numbers, ROW_NUMBER would arbitrarily exclude tied products.

How to approach this

Use recursion to walk the JSON tree, building column names by concatenating keys with a separator (e.g., 'address.city'). Handle arrays by either exploding them into rows or serializing them as strings. Show that you think about edge cases: empty arrays, null values, inconsistent nesting depths. Mention that in production you would use a library like pandas json_normalize, but writing the recursive version shows you understand the underlying logic.

How to approach this

Sort by the composite key plus timestamp descending, then iterate and keep the first occurrence of each key. Or use a dictionary keyed on the composite key, updating only when the timestamp is newer. Discuss time complexity: sorting is O(n log n), the dictionary approach is O(n). Mention that this is the Python equivalent of the SQL ROW_NUMBER deduplication pattern.

How to approach this

Use a generator function with yield to process rows lazily without loading the entire file into memory. Open the file, read a chunk of N lines, transform each row, and yield it. Explain why generators matter for data pipelines: a 50GB file does not fit in memory, but you can process it row by row or chunk by chunk. Mention pandas read_csv with chunksize as the production equivalent.

How to approach this

Sort events by user and timestamp. Iterate through events, comparing each timestamp to the previous one for the same user. If the gap exceeds 30 minutes, increment the session counter. Assign the current session ID to the event. This maps directly to a real pipeline use case: web analytics sessionization. Walk through a concrete example with 5 to 6 events.

How to approach this

Write a decorator or wrapper function that catches exceptions, waits 2^attempt seconds (with jitter), and retries up to a max number of attempts. Jitter prevents thundering herd when multiple workers retry simultaneously. Mention that production systems use libraries like tenacity, but the interviewer wants to see you implement the core logic from scratch.

How to approach this

Identify the grain of the fact table first: one row per order line item. Fact table: order_id, product_id, customer_id, store_id, date_id, quantity, unit_price, discount, total_amount. Dimension tables: dim_product (name, category, brand), dim_customer (name, segment, region), dim_date (date, month, quarter, year, is_weekend), dim_store (name, city, state). Explain why star schemas outperform normalized schemas for analytical queries: fewer joins, simpler SQL, and columnar storage compression benefits.

How to approach this

Describe SCD Types 1, 2, and 3. Type 1 overwrites the old value (simple, loses history). Type 2 adds a new row with effective_date, end_date, and is_current flag (preserves full history, most common in practice). Type 3 adds columns for previous values (limited history, rarely used). Recommend Type 2 for the customer address use case because analysts need to know which address was active when an order was placed. Walk through the pipeline logic: compare incoming records to current dimension, insert new rows for changes, update end_date on old rows.

How to approach this

Data vault separates structural data (hubs), relationships (links), and descriptive data (satellites). Use it when sources change frequently, you need full auditability, or multiple teams load data independently. The tradeoff: data vault is harder to query directly (more joins), so you typically build star schema business vaults on top. Interviewers want to see that you know both approaches and can articulate when each is appropriate.

How to approach this

Fact table: trips at grain of one row per trip (trip_id, rider_id, driver_id, pickup_location_id, dropoff_location_id, start_time, end_time, distance_miles, base_fare, surge_multiplier, total_fare, tip). Dimensions: dim_rider, dim_driver (with rating history as SCD Type 2), dim_location (lat, long, zone, city), dim_date. Discuss why surge_multiplier belongs in the fact table (it varies per trip) while driver rating belongs in the dimension (it changes slowly). Address the location dimension challenge: do you model pickup and dropoff as two foreign keys to the same dimension, or as separate dimensions?

How to approach this

Bronze (raw ingestion, append-only), Silver (cleaned, deduplicated, typed), Gold (aggregated, business-ready). Used in lakehouse architectures (Databricks, Delta Lake). Each layer has clear ownership and quality contracts. Bronze is cheap and fast to load. Silver handles deduplication, schema enforcement, and NULL handling. Gold serves specific business domains. Mention that this is not a replacement for star schemas; Gold tables often are star schemas.

How to approach this

Start with the source: a web app emitting events to a message queue (Kafka) for durability and decoupling. A stream processor (Flink or Spark Streaming) consumes events, applies basic validation (schema conformity, timestamp parsing), and writes to a staging area in the warehouse or a data lake. A batch job runs every 15 minutes to transform staged data into analytics-ready tables. Address exactly-once semantics: use idempotent writes with a dedup key (event_id + timestamp). Discuss monitoring: track event lag, throughput, and schema drift.

How to approach this

Idempotency means running the pipeline twice with the same input produces the same result. Three strategies: (1) DELETE-then-INSERT for the partition being processed, (2) MERGE/UPSERT using a natural key, (3) write to a temp table then atomically swap. Explain why idempotency matters: retries after failures, backfills, and re-processing should not create duplicates or corrupt data. Give a concrete example: a daily pipeline that processes yesterday's data should be safe to re-run without doubling the row count.

How to approach this

Check the orchestrator (Airflow, Dagster) for which task failed and the error message. If a task timed out, check the data volume for that partition (was there a data spike?). If it errored, check the logs for the specific exception. Common causes: source schema changed (new column, changed type), data volume spike, infrastructure issue (out of memory, network timeout), upstream dependency delivered late. After fixing, validate the output data, re-run the failed task, and add monitoring for the root cause to prevent recurrence.

How to approach this

Batch: simpler, cheaper, easier to debug, but latency is bounded by the batch interval (minimum 1 to 5 minutes). Streaming: sub-second latency, but more complex to build, test, and operate. Exactly-once semantics are harder in streaming. For a real-time dashboard, ask what 'real-time' means: if 5-minute latency is acceptable, micro-batch (Spark Structured Streaming with 1-minute triggers) is simpler than full streaming. If sub-second latency is required, use Kafka + Flink with event-time processing and watermarks.

How to approach this

Three levels: (1) source schema changes (new columns, renamed fields), (2) pipeline transformation logic, (3) warehouse schema. Use a schema registry (Confluent Schema Registry for Avro/Protobuf) to detect breaking changes before they hit the pipeline. For additive changes (new nullable column), handle automatically by adding the column downstream. For breaking changes (removed column, type change), fail loudly and alert the on-call engineer. Store raw data in a bronze layer so you can always reprocess after fixing the schema mapping.