Data Engineer Interview Questions and Answers

Questions and model answers

9 questions you are likely to hear.

1. 1

   How would you remove duplicate records from a large table, keeping the latest version?

   Why they ask: Deduplication is a daily data engineering task and tests window function skills.

   Sample answer: I would use ROW_NUMBER() OVER (PARTITION BY the business key ORDER BY updated_at DESC, and a tie-breaker like ingestion ID), then keep rows where the row number equals 1. In a warehouse I would write that result to a new table or use MERGE into the target, rather than deleting in place on a huge table. In Spark, I would do the same with a window function, or with dropDuplicates if any version is acceptable. On a customer table of around 200 million rows in Databricks, this approach ran in minutes once the data was partitioned by ingestion date.

2. 2

   What is data skew in Spark and how do you handle it?

   Why they ask: Skew is one of the most common Spark performance problems in real pipelines.

   Sample answer: Data skew happens when a few keys have far more records than others, so during a shuffle some tasks process huge partitions while most finish quickly. In the Spark UI it shows as one or two long-running tasks in a stage. Fixes include broadcasting the smaller table to avoid the shuffle, enabling Adaptive Query Execution with skew join handling, or salting the hot key by adding a random suffix and aggregating in two steps. In a sales pipeline, one store ID held around 30 percent of records; salting that key brought the job from about 50 minutes to 12.

3. 3

   Explain slowly changing dimensions and when you use Type 2.

   Why they ask: This tests dimensional modelling knowledge used in almost every warehouse.

   Sample answer: Slowly changing dimensions handle attributes that change over time. Type 1 overwrites the old value, so history is lost, which is fine for corrections like a typo. Type 2 adds a new row for each change with effective start and end dates and a current flag, preserving history. I use Type 2 when reports need to reflect the attribute at the time of the event, such as a customer's region when a sale happened. I implemented SCD Type 2 for customer and product dimensions with MERGE in Snowflake, and with dbt snapshots in another project.

4. 4

   How do you make a pipeline idempotent?

   Why they ask: Idempotency is essential for safe retries and backfills.

   Sample answer: An idempotent pipeline produces the same result whether it runs once or several times for the same input. I process data in clearly defined partitions, usually by date, and write with overwrite of that partition or a MERGE on a unique key instead of plain appends. I avoid relying on the current time inside transformations and pass the logical run date from the orchestrator. Airflow retries and backfills then become safe. In one project, append-only loads created duplicates every time a job retried; switching to partition overwrite fixed reconciliation issues with finance.

5. 5

   Design a pipeline that ingests clickstream events and makes them available for analytics.

   Why they ask: This tests end-to-end design across streaming and batch.

   Sample answer: Events would be sent from apps to Kafka, partitioned by user ID for ordering. A Spark Structured Streaming or Flink job would validate the schema against a registry, enrich events and write to a Delta or Iceberg bronze table in object storage, with checkpointing for fault tolerance. Batch jobs would then build cleaned silver tables and aggregated gold tables for dashboards, orchestrated by Airflow or Databricks Workflows. I would add data quality checks on volume and nulls, alerts on consumer lag, and a dead-letter topic for bad events. Late events would be handled with watermarks.

6. 6

   How do you optimise costs and performance in Snowflake?

   Why they ask: Warehouse cost control is a key expectation in 2026.

   Sample answer: I right-size virtual warehouses per workload and set auto-suspend to a minute or so, with separate warehouses for ELT, BI and data science so they do not compete. I check query profiles for full scans and spilling, and define clustering keys only on very large tables with selective filters. I prefer incremental models over full rebuilds and use result caching for repeated dashboard queries. Resource monitors cap spend. In one account, splitting a single large warehouse into workload-specific ones and fixing a few full-refresh dbt models reduced monthly credit usage noticeably.

7. 7

   How do you handle schema changes from source systems?

   Why they ask: Schema drift breaks pipelines, and interviewers want a robust approach.

   Sample answer: I agree a data contract with source teams where possible, defining fields, types and how changes are announced. In ingestion I land raw data with schema evolution enabled, for example in Delta Lake, so new columns do not break loads, but I alert on any schema change. Breaking changes like a type change or a removed column fail fast in a validation step rather than silently loading bad data. Downstream models select explicit columns. When a CRM team renamed a field without notice, our alert caught it before the morning dashboards refreshed.

8. 8

   What data quality checks do you build into pipelines?

   Why they ask: Trust in data depends on quality checks, not just working jobs.

   Sample answer: I check freshness, row counts against expected ranges, null rates on key columns, uniqueness of primary keys, referential integrity between facts and dimensions and accepted value lists for status fields. In dbt I use built-in and custom tests, and Great Expectations in Spark pipelines. Critical failures stop the pipeline, while warnings notify the team in Slack. I also reconcile totals with the source for financial data. In one bank project, a freshness check caught a stalled upstream extract before regulatory reports were generated.

9. 9

   Tell me about a pipeline you made significantly faster or more reliable.

   Why they ask: This shows ownership and measurable engineering impact.

   Sample answer: At a retail client, the nightly sales pipeline on Hadoop took about seven hours and failed roughly once a week, delaying store reports. I migrated it to Databricks with Delta Lake, replaced full reloads with incremental MERGE based on change data capture, fixed small files by compacting with OPTIMIZE, and broadcast small dimension tables. I added retries, data quality checks and alerting. Run time dropped to about 90 minutes and failures became rare, and store managers had reports before opening time.

Keep going.

• ETL Developer Interview Questions and Answers
• SQL Developer Interview Questions and Answers
• Data Scientist Interview Questions and Answers
• Data engineering job support
• Databricks Job Support Online for Data Engineers
• System Design Interview Basics