Data platform engineer role-specific interview questions

As asked

You own a daily ETL job that loads orders from an upstream API into the warehouse. Yesterday's job failed halfway through and will be retried. How do you make the pipeline idempotent?

Sample answer outline

A strong answer defines the target invariant first: re-running the job for the same logical window should produce the same final table state, not duplicate rows or partial totals. Use a stable business key such as order_id, write into a staging table, validate counts and required columns, then merge into the target inside a transaction or partition swap. Track high-water marks separately from successful commits so a failed run does not advance the cursor. Candidates often trip up by relying only on append-only loads or by deleting a partition before they know the replacement data is complete.

Expect these follow-ups

• How would your answer change if the source sends updates to old orders?
• What metadata would you store for each pipeline run?
• How do you recover if the warehouse supports no multi-statement transaction?

As asked

Delta Lake maintains a JSON transaction log that grows with every operation. How does checkpoint compaction work, when does it trigger, and what does VACUUM actually delete versus what it leaves behind?

Sample answer outline

Strong answers explain that Delta checkpoints consolidate the JSON log into a Parquet state file every 10 commits by default, allowing readers to skip older log entries. VACUUM removes data files no longer referenced by any snapshot older than the retention threshold (default 7 days), but it never touches the transaction log files themselves. Candidates should note that running VACUUM with a short retention window breaks time-travel queries and can cause concurrent readers to see missing files.

Expect these follow-ups

• What retention period would you set if you need 30-day time travel but also want to control storage cost?
• How does VACUUM interact with streaming checkpoints that reference old Delta versions?

As asked

Explain how Kafka achieves exactly-once semantics from producer through consumer. What are idempotent producers, transactions, and the read-committed isolation level, and where can each break down?

Sample answer outline

A complete answer covers idempotent producers using sequence numbers to deduplicate retried sends within a single session, transactional producers wrapping multi-partition writes in atomic transactions, and consumers setting isolation.level=read_committed to skip uncommitted records. Failure modes include epoch bumps resetting sequence state, aborted transactions leaving zombie offsets, and exactly-once breaking down at the sink if the sink itself is not transactional.

Expect these follow-ups

• How does Flink's Kafka source handle exactly-once when the job restarts mid-transaction?
• What is the overhead cost of enabling transactions, and when would you choose at-least-once instead?

As asked

A Spark job performing a large groupBy is spilling heavily to disk and taking twice as long as expected. Walk me through how you diagnose the root cause and what levers you would pull to fix it.

Sample answer outline

Strong answers start with reading the Spark UI: checking shuffle read/write sizes, spill (memory) and spill (disk) metrics per stage, and per-task memory usage. Root causes include data skew, insufficient executor memory, or too few partitions. Fixes include increasing spark.executor.memory or spark.memory.fraction, repartitioning to reduce per-task data, salting keys to address skew, or using a partial aggregation (combiner) to reduce shuffle volume.

Expect these follow-ups

• How does adaptive query execution in Spark 3 change your approach to skew tuning?
• What is the difference between shuffle fetch failures and shuffle spill, and how do you tell them apart in the UI?

As asked

A high-frequency Kafka-to-Iceberg ingestion pipeline produces thousands of small Parquet files per hour. Describe the impact on query performance and the strategies you would use to compact files without impacting concurrent readers or writers.

Sample answer outline

Small files cause excessive S3 LIST and open calls per query, slow the manifest scan in Iceberg, and increase the metadata overhead of the snapshot. Compaction rewrites small files into target-size files (typically 128 to 512 MB). Iceberg's RewriteDataFiles action can be run concurrently with active writers because it commits atomically: it adds new compacted files and removes old ones in a single snapshot, so readers always see a consistent state. The candidate should know bin-packing versus sort-based rewrite strategies and how to schedule compaction (Airflow, Spark job, or Iceberg maintenance procedures).

Expect these follow-ups

• How would you set the target file size and bin-pack threshold?
• How does compaction interact with Iceberg partition pruning if you compact across partition boundaries?

As asked

A Kafka consumer group with 10 consumers is processing a topic with 20 partitions. One consumer goes down unexpectedly. Walk me through exactly what happens during rebalancing, how long it takes, and what configuration knobs reduce the disruption.

Sample answer outline

When a consumer stops sending heartbeats within session.timeout.ms, the group coordinator marks it dead and triggers a rebalance. All consumers rejoin the group, and the group leader re-assigns the 20 partitions among the 9 remaining consumers. During the stop-the-world rebalance, no messages are consumed. Mitigation includes setting a shorter session.timeout.ms for fast failure detection, using incremental cooperative rebalancing (cooperative-sticky assignor) to move only the affected partitions, and using static group membership with group.instance.id to allow a consumer to rejoin as the same member and skip full rebalance.

Expect these follow-ups

• What is the difference between eager and cooperative rebalancing protocols?
• How does max.poll.interval.ms interact with session.timeout.ms in causing unexpected rebalances?

As asked

dbt offers append, delete-insert, merge, and insert-overwrite incremental strategies. Walk me through when you would choose each one, what the correctness risks are for each, and how late-arriving data affects your choice.

Sample answer outline

Append suits immutable event streams where duplicates are acceptable. Delete-insert replaces entire partition windows and is safe for idempotent re-runs on late data within the window. Merge is the most flexible but requires a reliable unique key and can be expensive on large datasets. Insert-overwrite replaces the target partition entirely and is efficient on Spark but requires partition-aligned incremental logic. Late-arriving data outside the lookback window is silently ignored in append and delete-insert strategies unless the window is extended, which increases scan cost.

Expect these follow-ups

• How do you handle a source that updates records in place with no reliable updated_at column?
• What is the risk of using merge without a surrogate key?

As asked

You are on call and you get an alert that a Kafka consumer group processing payment events has accumulated a 2-hour consumer lag spike that started 20 minutes ago. Walk me through exactly what you do in the first 30 minutes.

Sample answer outline

First, check whether the consumer is alive (heartbeats, pod restarts). Second, identify if the lag is on one partition (skew or hot partition) or all partitions (consumer slowdown or Kafka broker issue). Third, check consumer CPU and GC metrics to see if the processing is slow. Fourth, assess downstream impact: is the payment service blocked? Fifth, decide whether to scale consumers, replay from a different offset, or escalate. The candidate should know that adding consumers beyond the partition count does not help.

Expect these follow-ups

• The lag started exactly when a new deployment was pushed. What do you do?
• How do you safely roll back a Kafka consumer to an earlier offset without losing processed events?

As asked

Product engineering teams keep changing event payloads and breaking analytics and ML pipelines. How would you introduce data contracts?

Sample answer outline

Start with the highest-value event streams and define owners, schemas, compatibility rules, and review paths. Put validation close to the producer so bad events are caught before they enter the lake, while still storing rejected samples for debugging. Use schema registry or contract tests in CI, and make breaking changes explicit with versioning and deprecation windows. The cultural part matters: producers need to understand which downstream decisions depend on their data. Candidates trip up when they propose contracts as a data-team document rather than an enforceable engineering interface.

Expect these follow-ups

• How do you handle a producer that needs to ship a breaking change urgently?
• Where should validation run: client, service, stream, or warehouse?
• How do you stop contracts becoming stale?

As asked

Walk me through how Apache Iceberg implements snapshot isolation. How does it handle concurrent writers, and what happens when two jobs try to commit at the same time?

Sample answer outline

A strong answer covers Iceberg's optimistic concurrency model: each writer reads the current snapshot, builds a new metadata tree, and atomically swaps the catalog pointer. Concurrent commit conflicts are detected at commit time via CAS on the metadata file; one writer retries. The candidate should explain how this avoids locks at the file level and still provides serializable isolation for reads.

Expect these follow-ups

• How would you tune retry behavior for high-concurrency append workloads?
• What changes if you use a Hive metastore vs a REST catalog for the atomic swap?

As asked

A Flink job is computing 5-minute tumbling windows on event-time. Some events arrive up to 3 minutes late. Walk me through how you would configure watermarks, allowed lateness, and side outputs so you do not lose late data but also do not delay the main output indefinitely.

Sample answer outline

The candidate should explain setting a watermark strategy with a 3-minute bounded-out-of-orderness tolerance, which delays window firing by 3 minutes relative to event time. Adding allowed lateness lets Flink re-fire the window as additional late records arrive within that window. Records that arrive after allowed lateness expires go to a side output stream, which can be joined back or stored for reconciliation. The tradeoff between latency and completeness should be articulated.

Expect these follow-ups

• How do watermarks propagate across parallel subtasks in a multi-source topology?
• What happens to watermark progress if one Kafka partition goes silent?

As asked

Explain the difference between backward, forward, and full schema compatibility in Avro. Then tell me how Iceberg handles schema evolution differently, and where the two approaches can conflict when you use Avro serialization on top of Iceberg tables.

Sample answer outline

Backward compatibility means new readers can read data written by old writers; forward means old readers can read new writer output; full is both. Avro enforces this at the registry level using reader and writer schemas with field defaults. Iceberg manages schema evolution at the table layer by tracking column IDs, allowing rename and type promotion without rewriting files. Conflict arises when Avro schema IDs diverge from Iceberg column IDs after a rename, requiring careful coordination of both the registry entry and the Iceberg catalog update.

Expect these follow-ups

• How does Schema Registry handle compatibility checks for a topic consumed by multiple downstream services at different schema versions?
• What Iceberg type promotions are safe without rewriting Parquet files?

As asked

You have a daily pipeline where the number of files to process is not known at authoring time. How would you use Airflow's dynamic task mapping to fan out processing, and what are the limitations you need to plan around?

Sample answer outline

Dynamic task mapping (Airflow 2.3+) lets a task expand into N parallel instances by mapping over an XCom list returned from an upstream task. Limitations include the mapped list being resolved at runtime, so the scheduler cannot pre-plan downstream dependencies precisely, and the UI showing collapsed task groups which makes debugging harder. Large fan-outs also stress the metadata database because each mapped instance creates its own task instance row.

Expect these follow-ups

• How would you cap the fan-out to avoid overwhelming downstream databases?
• How does this compare to using a Taskflow API with a single task that iterates internally?

As asked

What problem does Iceberg hidden partitioning solve compared to Hive-style partitioning? How does partition evolution work, and what happens to queries that were compiled against the old partition spec after you evolve it?

Sample answer outline

Hive partitioning exposes partition columns to users and requires filter predicates to match partition directory names exactly. Iceberg hidden partitioning stores partition values derived from data columns (e.g., month(ts)) in the manifest without exposing them as separate columns, so query predicates on the source column are automatically applied as partition pruning. Partition evolution lets you add or change the partition spec without rewriting existing data; old partitions retain the old spec and new writes use the new spec, and the query planner handles both transparently.

Expect these follow-ups

• How does hidden partitioning interact with predicate pushdown in a Trino or Spark read?
• What are the storage layout implications of switching from daily to hourly partitioning mid-stream?

As asked

You are running a Debezium MySQL connector on Kafka Connect. The connector pod crashes and restarts after 10 minutes. What exactly is stored in the offsets topic, how does Debezium use it to resume, and what data-loss or duplication risks exist on restart?

Sample answer outline

Debezium stores the binlog file name and position (or GTID) in the Kafka Connect offsets topic, keyed by connector name plus partition. On restart it reads this offset, connects to the binlog at that position, and replays events forward. Risks include the binlog being purged if the retention is shorter than the downtime (requiring a new snapshot), and at-least-once delivery meaning the consumer must handle duplicate events near the restart boundary. The candidate should know that schema change events can arrive before the corresponding data events after a snapshot.

Expect these follow-ups

• How would you monitor binlog lag and alert before the binlog window expires?
• How does switching to GTID-based replication affect offset management?

As asked

You are building a Spark Structured Streaming job that maintains running totals per user session with a session gap timeout of 30 minutes. Walk me through how Spark manages this state, what the RocksDB state store offers over the default in-memory store, and how state is recovered after a job restart.

Sample answer outline

Spark Structured Streaming stores state per key in a state store backed by HDFS or S3 checkpoints. The default in-memory store keeps state in the executor heap, which limits total state size and causes GC pressure at scale. The RocksDB state store offloads state to an embedded LSM-tree on local disk, allowing much larger state with predictable memory use. State is checkpointed to the configured checkpoint location at each micro-batch; on restart Spark restores state from the latest valid checkpoint. The candidate should mention watermark-based state cleanup to evict expired sessions.

Expect these follow-ups

• How do you size the executor disk when using RocksDB state store for a high-cardinality key space?
• What happens to in-flight state if the checkpoint location becomes unavailable?

As asked

Your platform processes 200 tables daily. How would you design a data quality framework to run expectations automatically, route failures to the right owners, and prevent bad data from propagating downstream without creating a bottleneck?

Sample answer outline

A strong answer describes codifying expectations close to the data source (in dbt tests or Great Expectations suites), running them as part of the pipeline DAG so failures block downstream tasks, and using metadata tags to route alert notifications to dataset owners rather than a central team. The candidate should discuss the tradeoff between blocking (quarantine bad data) and non-blocking (emit metrics, continue) modes, and how to avoid alert fatigue by setting dynamic thresholds based on historical distributions.

Expect these follow-ups

• How would you handle a table where the expected null rate fluctuates seasonally?
• How do you version and review expectation changes as part of a CI pipeline?

As asked

Your lakehouse stores 2 PB on S3 and the monthly bill is growing faster than data volume. Walk me through the levers you would pull to reduce storage and API costs without degrading query performance.

Sample answer outline

Key levers include: setting S3 Intelligent-Tiering or lifecycle policies to move infrequently accessed partitions to cheaper storage classes; running Iceberg VACUUM and EXPIRE SNAPSHOTS to remove orphaned and expired files; compacting small files to reduce LIST and GET call counts; using columnar compression (Zstandard over Snappy) to shrink file sizes; and enforcing partition pruning in queries so full table scans are rare. The candidate should also mention monitoring S3 request metrics to identify hot paths generating excessive GET costs.

Expect these follow-ups

• How do S3 Glacier retrieval latencies affect your SLA for historical query workloads?
• What is the cost difference between using S3 Select versus pulling full Parquet files and filtering in Spark?

As asked

Explain how Parquet's row group and column chunk structure affects query performance for a selective point-lookup versus a full column scan. What row group size would you target for a read-heavy analytical workload and why?

Sample answer outline

Parquet stores data in row groups, and within each row group each column is stored as a column chunk with its own min/max and bloom filter statistics. A small row group means more groups to scan for a full column scan (higher metadata overhead) but finer-grained predicate pruning for point lookups. Larger row groups (128 to 512 MB) reduce metadata overhead and improve column scan throughput by enabling larger sequential I/O, but a false positive in the statistics forces reading more data per group. For analytical workloads dominated by aggregations, larger row groups and Zstandard compression are typically optimal.

Expect these follow-ups

• How does enabling dictionary encoding change the tradeoff for high-cardinality string columns?
• When would you enable bloom filters on a column and what is the memory cost?

As asked

A dbt model that powers the weekly revenue report ran without errors last night, but the finance team reports that the numbers are 15% lower than expected. The dbt tests all passed. How do you investigate?

Sample answer outline

The candidate should start by comparing the current run's row count against the previous day's to spot data volume drops. Next, check the source table freshness and row counts to see if an upstream pipeline delivered partial data. Then examine dbt's incremental logic: did the lookback window miss records, or did a filter change silently exclude a business unit? Finally, check whether a recent schema change in the source (a renamed column, a new null-handling behavior) caused silent data loss. The candidate should not assume the model logic changed without evidence.

Expect these follow-ups

• The source row count looks correct but the revenue is still 15% low. What next?
• How would you add monitoring to catch this class of issue automatically?

As asked

A Spark job that aggregates daily clickstream data runs fine for 90% of the month but OOMs on the driver on the last day of the month when traffic is highest. The executor memory is fine. What is happening and how do you fix it?

Sample answer outline

Driver OOM on high-traffic days suggests a driver-side collect() or toPandas() call that brings data into the driver, or a large broadcast variable. The candidate should check the Spark UI for stage skew on the final day and look for any collect() calls in the code. The fix is to eliminate driver-side collection (replace collect() with a write to storage), reduce broadcast variable size, or increase driver memory only as a last resort. They should also check if the Spark history server shows driver GC pauses.

Expect these follow-ups

• How would you reproduce this in a lower environment without waiting for month-end?
• What monitoring alert would catch driver memory pressure before the OOM occurs?

As asked

In Dagster, how do software-defined asset partitions work for a daily partitioned asset that depends on an hourly upstream asset? How does Dagster resolve which partition keys need to be materialized when you backfill three days?

Sample answer outline

Each daily partition maps to a set of 24 hourly upstream partitions via a partition mapping. When a backfill is triggered for three daily partitions, Dagster computes the transitive upstream partition keys and materializes them in dependency order. The candidate should explain AssetMaterialization events and how the asset graph UI shows partition status. A strong answer mentions incremental freshness policies versus full-refresh strategies.

Expect these follow-ups

• How would you model a sensor that triggers only when all 24 hourly partitions for a day are available?
• What happens if one upstream hourly partition fails mid-backfill?

As asked

Delta Lake and Iceberg both offer Z-ordering or equivalent multi-dimensional clustering. Explain how a Z-order curve maps multi-dimensional data onto a one-dimensional sort key, and when this is better or worse than sorting on a single column.

Sample answer outline

A Z-order curve interleaves the bits of each dimension's value to produce a single key that preserves multi-dimensional locality: nearby points in multiple dimensions have nearby Z-values. This makes file-level statistics (min/max per column) useful for pruning queries with filters on multiple columns simultaneously. The tradeoff is that Z-ordering is more expensive to compute and produces less optimal clustering for queries filtering on only one column compared to a simple sort on that column.

Expect these follow-ups

• How would you decide the column order for a Z-order operation when you have 5 candidate filter columns?
• What is Hilbert ordering and how does it compare to Z-order in practice?

As asked

Table-level lineage is relatively easy to capture. How would you implement column-level lineage for a platform where transformations happen in dbt, Spark jobs, and ad-hoc SQL? What are the hard cases that most lineage tools get wrong?

Sample answer outline

Column-level lineage requires parsing SQL or transformation code to trace which source columns feed each output column. dbt exposes this via its manifest JSON. Spark lineage can be captured via the OpenLineage Spark listener or through Spark's QueryExecution plan. Hard cases include wildcard SELECT *, dynamic column names built at runtime, Python UDFs that are opaque to the parser, and lateral view explodes that change row-column relationships. The candidate should mention tools like Marquez, DataHub, or Unity Catalog and their limitations.

Expect these follow-ups

• How would you handle lineage for a Flink job that reads Kafka and writes to Iceberg?
• What is the minimum metadata you need to make column lineage actionable for impact analysis?

As asked

A compliance requirement says PII columns must be masked for analysts but visible to data engineers, and certain rows must be hidden based on the user's business unit. How would you implement this in Unity Catalog, and what are the performance implications of row filters and column masks?

Sample answer outline

Unity Catalog supports column masks via SQL functions attached to columns at the catalog layer, and row filters via policy functions that inject a predicate into every query on the table. Column masks can call CURRENT_USER() or IS_MEMBER() to conditionally return masked values. Row filters are evaluated for every query and can add significant overhead on large tables if the filter function is complex. The candidate should discuss using attribute-based access control groups rather than per-user policies to keep management tractable.

Expect these follow-ups

• How do you test that row filters are correctly applied and cannot be bypassed via a DataFrame API call?
• What happens to column masks when a masked table is joined to another table?