←

# data engineering

// [83] real interview questions. Answers and sources live in the practice app.

practice this topic
  1. Explain topics, partitions, and brokers in Kafka. How does partitioning enable horizontal scale?(junior)
  2. How do you guarantee ordering (FIFO) of events in Kafka? What are the limits of that guarantee?(mid)
  3. What is a Kafka consumer group, and how does the number of partitions constrain consumer parallelism?(mid)
  4. How do you know whether consumers have fully caught up with a Kafka topic? What is consumer lag and how do you monitor it?(mid)
  5. What triggers a Kafka consumer group rebalance, why is it disruptive, and how do you reduce the pain?(senior)
  6. Explain at-most-once, at-least-once, and exactly-once delivery. What does 'exactly-once' actually mean in Kafka, and where does the guarantee stop?(senior)
  7. Your Kafka consumer writes events into a database and you see duplicate rows after a deploy. Walk through why this happens and how you'd design the consumer to be safe.(senior)
  8. Batch or streaming: how do you decide which one a new pipeline needs? Give the concrete factors you'd weigh.(mid)
  9. Compare Lambda and Kappa architectures. Why did many teams move away from Lambda?(senior)
  10. In stream processing, what is the difference between tumbling and sliding windows? When would you use each?(mid)
  11. What is micro-batching, and how does Spark Structured Streaming differ from a true event-at-a-time engine like Flink?(mid)
  12. RDDs vs DataFrames in Spark: what's the difference, and why are DataFrames usually faster?(mid)
  13. What is lazy evaluation in Spark? Explain transformations vs actions and why laziness matters.(junior)
  14. What is a shuffle in Spark, which operations trigger it, and why is it the most expensive thing in a job?(mid)
  15. A Spark job has 200 tasks; 199 finish in a minute and one runs for an hour. What's happening and how do you fix it?(senior)
  16. Explain the roles of the Spark driver and executors. What are the classic causes of driver vs executor out-of-memory errors?(mid)
  17. How does Spark achieve fault tolerance without replicating all intermediate data?(mid)
  18. Parquet vs Avro: how do the formats differ and when do you choose each in a pipeline?(mid)
  19. How do you handle schema evolution in a pipeline — say a producer adds or renames a field? Explain backward vs forward compatibility.(senior)
  20. What is the small files problem in HDFS and object-store data lakes? How does it arise and how do you fix it?(senior)
  21. Data warehouse vs data lake vs lakehouse — define each and explain what actual problem the lakehouse tries to solve.(mid)
  22. Why do analytical databases use columnar storage while OLTP databases use row storage?(junior)
  23. How do you choose a partitioning scheme for a large table in a data lake? What goes wrong with over-partitioning?(mid)
  24. Explain the difference between OLTP and OLAP systems. Why shouldn't you run analytics on your production OLTP database?(junior)
  25. What is a star schema? Explain fact and dimension tables and why warehouses denormalize on purpose.(mid)
  26. Explain slowly changing dimensions. How do you implement SCD Type 2, and what do the extra columns look like?(senior)
  27. Why do data warehouses use surrogate keys instead of natural/business keys?(mid)
  28. When would you denormalize a schema, and what does normalization actually buy you in the first place?(mid)
  29. What architectural ideas distinguish modern cloud warehouses like Snowflake from classic MPP warehouses like Redshift?(mid)
  30. ETL vs ELT: what changed, and where does a tool like dbt fit in the modern stack?(mid)
  31. Full load vs incremental load in ETL: trade-offs, and how do incremental models work in practice (e.g., in dbt)?(mid)
  32. Compare the ways to capture changes from a source database: timestamp columns, triggers, and log-based CDC. Why has log-based CDC won?(senior)
  33. What makes a data pipeline idempotent, and why is idempotency the property that makes retries and backfills safe? Give concrete techniques.(senior)
  34. You need to backfill two years of daily data after fixing a transformation bug. How do you design and run the backfill without breaking production?(senior)
  35. What are the classic Airflow DAG pitfalls you check for in code review?(senior)
  36. What data quality checks do you build into a pipeline, and where do they run? What happens when a check fails?(mid)
  37. What are the most common failure modes of ETL pipelines in production, and how do you engineer against each?(mid)
  38. How do you implement monitoring and alerting for data pipelines? What signals matter beyond 'the job succeeded'?(junior)
  39. What problem do watermarks solve in stream processing, and what trade-off are you making when you set one?(senior)
  40. You join a stream of ad impressions with a stream of clicks in a streaming job. After a week in production the state store has grown to hundreds of GB and checkpoints take forever. What went wrong?(senior)
  41. Your mobile analytics dashboard shows a traffic dip last Tuesday, but the app team swears usage was normal. The pipeline buckets events by the time they arrive. What's the likely cause and the fix?(mid)
  42. Your nightly dbt run fails because a not_null test on a source started failing, and downstream dashboards are now stale. How do you handle it tonight — and structurally?(mid)
  43. You inherit a dbt project with 400 models and almost no tests. Where do you add tests first, and how do you keep test runtime from exploding?(mid)
  44. Your team is standardizing the lakehouse on one table format — Iceberg vs Delta Lake vs Hudi. What actually drives the decision?(senior)
  45. After a year of hourly-partitioned data, your query patterns changed and the Hive-style layout now hurts. What do Iceberg-style hidden partitioning and partition evolution fix?(mid)
  46. A bad deploy wrote garbage into a critical Delta/Iceberg table two hours ago. How do you recover, and what limits how far back you can go?(mid)
  47. Two pipelines MERGE into the same Delta table and one intermittently fails with a concurrent-modification error. Why does this happen, and what are your options?(senior)
  48. The orchestrator marked a warehouse-load task failed after a timeout and retried it — but the first attempt's SQL was still running in the warehouse. The table now has duplicates. Unpack everything that went wrong.(senior)
  49. What is a data contract, and how do you actually enforce one between a product team's service and the analytics pipelines downstream?(senior)
  50. Your Snowflake bill tripled month-over-month with no obvious new workload. How do you investigate, and what are the usual culprits?(senior)
  51. On BigQuery's on-demand pricing, which query patterns quietly cost the most, and what guardrails do you set?(mid)
  52. A teammate ran a two-year backfill of a fact table on Friday; on Monday, finance reports double revenue across the whole period. You own the postmortem — what likely went wrong and which guardrails were missing?(senior)
  53. Events arrive at-least-once from Kafka into your lake, and the marts must be duplicate-free. Where and how do you dedupe at billions of events per day?(senior)
  54. An order fact arrives in the warehouse before the customer row it references exists — the CDC feed for customers lags. How do you load it without losing or blocking data?(mid)
  55. Kafka topic cleanup: what's the difference between time-based retention and log compaction, and when do you use a compacted topic?(mid)
  56. A dbt incremental model drifts about 0.5% from a --full-refresh rebuild every month. What causes incremental models to silently diverge, and how do you defend?(senior)
  57. A bug shipped in your streaming job and a week of its output is wrong. In a kappa-style architecture, how do you reprocess — and what makes replay harder than it sounds?(senior)
  58. A GDPR erasure request arrives: delete one user's data from a lake holding years of Parquet files. How do you actually do it?(senior)
  59. You change the aggregation logic of a Spark Structured Streaming job and redeploy against the existing checkpoint. The job crashes — or silently computes nonsense. What's going on?(senior)
  60. Your CDC feed lands insert/update/delete events for an orders table. Downstream, do you keep the append-only changelog, maintain a merged 'current state' table, or both?(mid)
  61. A downstream team polls your table every minute with a sensor task that just checks 'did today's partition appear'. It fires thousands of times a day, hammers the scheduler, and still sometimes reads a half-written partition. Redesign this.(senior)
  62. When you register an Avro/Protobuf schema in a schema registry, you pick a subject name strategy and a compatibility mode. Why do these two choices matter more than people expect?(mid)
  63. A consumer group falls hours behind after a traffic spike. Adding more consumer instances doesn't help. What's the ceiling you hit, and how do you actually recover throughput?(senior)
  64. Beyond partitioning, how do you tune Parquet files so a query engine reads as little as possible? Cover row group size, column ordering, and the statistics that enable skipping.(mid)
  65. A dashboard query over a 5TB table times out. You can't change the query. What layout and warehouse-side changes give the engine a chance, and how do you diagnose which one it needs?(senior)
  66. What is reverse ETL, and how does it change the failure surface compared to a normal analytics pipeline?(mid)
  67. You need to expose customer analytics to internal teams, but the tables contain emails, names, and payment info. How do you serve useful data without every analyst seeing raw PII?(senior)
  68. A downstream analyst says 'the numbers changed and nobody told us.' How do you build data observability so a change is detected and attributed automatically, not reported by a human?(mid)
  69. What is 'data mesh' actually proposing, and what's the honest failure mode a mid-size company hits when it adopts the buzzword?(senior)
  70. Your pipeline consumes from a third-party API that occasionally returns malformed records. Right now one bad record fails the whole batch and blocks everything. How do you make it resilient?(mid)
  71. Trino/Presto lets you query data across S3, Postgres, and Kafka in one SQL statement without loading it anywhere. What is this pattern, and where does it help versus hurt?(mid)
  72. You're moving a batch pipeline to Spark on spot/preemptible instances to cut cost 70%. Two weeks in, jobs randomly fail or run slower than before. What did the cost optimization break?(senior)
  73. How do you decide the grain of a fact table, and why is 'declaring the grain' the step everything else depends on?(mid)
  74. A metric on the executive dashboard is computed three different ways in three tools and gives three different numbers. Nobody trusts the dashboard. As the data engineer, how do you fix this — and prevent it?(senior)
  75. Explain the CAP theorem the way it actually applies to choosing a datastore, and why 'CA' is basically a fiction in distributed systems.(mid)
  76. Debezium is doing the initial snapshot of a 2-billion-row table before it starts streaming changes. The snapshot locks/loads the source hard and takes a day — during which changes are happening. How is correctness preserved, and what are the operational options?(senior)
  77. Your team keeps copy-pasting the same 30-line SQL block across dbt models and it drifts. How do dbt macros, ref(), and the DAG address this, and what's the discipline for not overusing macros?(mid)
  78. Flink and Spark Structured Streaming both do fault-tolerant streaming, but their checkpoint/state models differ in ways that matter operationally. Contrast them for someone choosing between them.(senior)
  79. You must write results from a streaming job to an external Postgres table exactly once, but Kafka transactions can't span Postgres. How do you actually get end-to-end exactly-once to an external sink?(senior)
  80. What is an idempotency key in a data ingestion API, who generates it, and what breaks if it's generated at the wrong moment?(mid)
  81. An analyst reports that a dashboard number is 3% off from finance's system of record every month, but it's never the same rows. There's no error, no failed job. How do you hunt a silent correctness drift like this?(senior)
  82. Why do warehouse teams distinguish staging, intermediate, and mart layers in their models, and what actually goes wrong when everything is one big query?(mid)
  83. Your batch job that used to finish in 2 hours now takes 9 as data grew, threatening the morning SLA. Before throwing more cluster at it, what do you investigate and change?(senior)