Free · No signup required
38 data engineer interview questions — with answers and what they are really testing
The questions we see asked most often in Indian data engineering interviews, grouped by round. Each one has a model answer and a note on what the interviewer is actually assessing — which is usually not the thing the question appears to be about.
Compiled by Venu Katragadda from mock interviews with 1,200+ students and 14+ years of hiring and being hired in this field.
Jump to a round
How a data engineering interview is usually structured
Most Indian data engineering loops run four rounds: a live SQL round, a Python/PySpark coding round, a pipeline design round, and a deep-dive on your own projects. Screening is often a 30-minute call covering tools and notice period.
The two rounds that reject the most candidates are design and project deep-dive — not coding. Both reward the same habit: stating your assumptions and trade-offs out loud instead of jumping to an answer. Practise talking while you think.
SQL (5 questions)
What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
All three are window functions that number rows within a partition. ROW_NUMBER always gives a unique sequential number, even for ties. RANK gives tied rows the same number and then skips (1,2,2,4). DENSE_RANK gives tied rows the same number and does not skip (1,2,2,3). Use ROW_NUMBER for deduplication, RANK when gaps are meaningful, DENSE_RANK for top-N by distinct value.
What they are testing: Whether you use window functions daily or only read about them.
How would you find the second-highest salary per department?
Use a window function rather than a correlated subquery: rank salaries with DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) in a CTE, then filter for rank = 2. Say explicitly how you handle ties and departments with only one employee — that is the part interviewers watch for.
What they are testing: Window-function fluency plus whether you think about edge cases unprompted.
What is the difference between a LEFT JOIN and a LEFT ANTI JOIN, and when do you use each?
A LEFT JOIN keeps every left row and attaches matching right rows, producing NULLs where there is no match — and multiplying rows where there are several. A LEFT ANTI JOIN keeps only the left rows with no match, and never multiplies. Anti joins are the right tool for 'find records not yet loaded' rather than a LEFT JOIN … WHERE right.id IS NULL, which is slower on large tables.
What they are testing: Whether you know the set-semantics behind joins, not just the syntax.
Your query returned more rows than the source table. What happened?
A join fan-out: the join key is not unique on the right side, so each left row matched several right rows. Diagnose by counting distinct keys versus rows on each side before joining. Fix by deduplicating the right side to the correct grain, aggregating before the join, or switching to a semi/anti join if you only need existence.
What they are testing: Debugging instinct. Almost every real data bug is a grain problem.
How do you find and remove duplicate rows while keeping the most recent one?
ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY updated_at DESC) in a CTE, then keep rn = 1. Mention that you would first confirm what 'duplicate' means with the business — exact-copy rows and multiple versions of the same entity need different treatment.
What they are testing: Whether you clarify requirements before writing code.
Spark & PySpark (7 questions)
Explain what happens when you call an action on a Spark DataFrame.
Transformations build a logical plan lazily. When an action fires, Catalyst optimises the logical plan, produces a physical plan, and the DAG scheduler splits it into stages at shuffle boundaries. Each stage becomes tasks — one per partition — distributed to executors. Results return to the driver or a sink. Adaptive Query Execution can re-plan between stages using runtime statistics.
What they are testing: Whether you understand the engine or just call the API.
What is data skew, how do you detect it, and how do you fix it?
Skew is an uneven distribution of a key so a few tasks handle far more data than the rest — you see it in the Spark UI as a stage where max task duration dwarfs the median. Fixes in order of preference: enable AQE skew join handling; broadcast the small side if it fits; salt the skewed key and aggregate in two passes; or split the hot keys out and process them separately. Filtering out a null-heavy join key is often the real fix.
What they are testing: The single most common senior Spark question. Vague answers fail here.
Broadcast join versus sort-merge join — how do you choose?
Broadcast when one side fits comfortably in executor memory (default threshold 10 MB, commonly raised to 100–200 MB); it avoids the shuffle entirely by copying the small side to every executor. Sort-merge is the fallback for two large sides: both get shuffled and sorted by key. Broadcasting something too large causes driver OOM or executor pressure, which is why the threshold exists.
What they are testing: Cost intuition — do you know what a shuffle costs?
Why is repartition more expensive than coalesce?
repartition triggers a full shuffle and can increase or decrease partition count with even distribution. coalesce merges existing partitions without a shuffle, so it is cheap, but only reduces the count and can leave you with uneven partitions — and it can also reduce upstream parallelism, which sometimes makes the whole job slower.
What they are testing: Whether you know the trap in coalesce, not just the definition.
How do you decide spark.sql.shuffle.partitions?
Target roughly 128–200 MB of shuffled data per partition. Estimate shuffle write size from the Spark UI, divide, and round to a multiple of your total executor cores so the last wave is not half-idle. The default of 200 is only correct by accident. With AQE enabled, coalescing handles much of this automatically, but the starting number still matters.
What they are testing: Practical tuning experience versus copied blog advice.
When is caching a DataFrame the wrong choice?
When the DataFrame is used once, when it is too large and forces spill to disk or evicts more useful data, or when recomputation is cheaper than the memory pressure. Caching also pins memory that executors need for shuffles. Always unpersist when done, and check the Storage tab to confirm the cache is actually fully in memory.
What they are testing: Whether you cache reflexively or deliberately.
Why are Python UDFs slow, and what would you use instead?
A Python UDF serialises each row from the JVM to a Python process and back, and is opaque to Catalyst so no predicate pushdown or codegen applies. Prefer built-in Spark SQL functions; if you genuinely need Python, use a pandas UDF, which uses Arrow to move data in batches and is typically several times faster.
What they are testing: Performance awareness in everyday code.
Databricks & Delta Lake (6 questions)
How does Delta Lake provide ACID transactions on object storage?
Through the transaction log (_delta_log): an ordered set of JSON commit files, periodically checkpointed to Parquet. Readers reconstruct table state from the log rather than listing files, and writers use optimistic concurrency — they read a snapshot version, write data files, then atomically commit the next log entry, retrying if another writer got there first.
What they are testing: Depth. Most candidates say 'Delta gives ACID' and stop.
Explain the medallion architecture and why the layers exist.
Bronze is raw ingested data kept as-received so you can always replay. Silver is cleaned, deduplicated, conformed and quality-checked. Gold is business-level aggregates modelled for consumption. The layers exist so that a bad transformation is recoverable without re-ingesting from source, and so that reprocessing is cheap and auditable.
What they are testing: Whether you understand the reasoning or just the diagram.
How do you implement SCD Type 2 in Delta Lake?
MERGE INTO the dimension on the business key where the record is current. When an incoming row differs, close the existing row by setting end_date and is_current = false, then insert the new version with is_current = true. Because MERGE cannot both update and insert the same source row, the standard pattern unions the source with a marker row to drive both actions.
What they are testing: Whether you have actually written one — the union trick is the tell.
OPTIMIZE, Z-ORDER, liquid clustering — what does each do?
OPTIMIZE compacts small files into larger ones. Z-ORDER co-locates related values across up to a few columns so data skipping can prune more files. Liquid clustering replaces both partitioning and Z-ORDER with a clustering scheme that can be changed without rewriting the table, and handles skew and evolving query patterns better. For new tables, liquid clustering is now usually the right default.
What they are testing: Whether your knowledge is current — this area changed recently.
What does Auto Loader give you over a plain directory read?
Incremental discovery with state: it tracks which files it has already processed via checkpoint and RocksDB state, so reruns do not reprocess. It scales to millions of files using file notification mode instead of directory listing, and it handles schema inference, schema evolution and a rescued-data column for records that do not match.
What they are testing: Ingestion experience at scale.
How would you cut a Databricks bill by 40%?
Move scheduled work from all-purpose to job compute; enable autotermination and right-size clusters using actual utilisation; use spot instances for workers with on-demand drivers; enable Photon where the workload is eligible; fix small files and skew so jobs finish faster; and attribute spend with system tables and cluster policies so teams see their own DBUs. Measure before and after.
What they are testing: Cost fluency. This is the question that decides senior-level offers.
Cloud & architecture (6 questions)
Design a pipeline to ingest 50 GB/day from 40 source tables into a lakehouse.
Talk through: a metadata-driven ingestion framework with a control table rather than 40 hand-built pipelines; CDC where the source supports it, watermarked incremental pulls where it does not; landing raw to object storage; a bronze/silver/gold Delta or Iceberg layout; orchestration with Airflow or native workflows; data-quality gates that fail loudly; and monitoring, alerting, backfill strategy and cost per run. Say your assumptions out loud.
What they are testing: Structure. They want to hear you scope before you solve.
How do you handle late-arriving data in a streaming pipeline?
Use event time, not processing time. Set a watermark that reflects how late data realistically arrives, sized against the cost of holding state. Data later than the watermark is dropped from windowed aggregates — so route it to a side output or a quarantine table rather than losing it, and reconcile with a periodic batch correction job.
What they are testing: Whether you have run streaming in production or only read about it.
Explain exactly-once semantics. Is it really achievable?
End-to-end exactly-once means each record affects the result once despite failures. It is achievable within a closed system — Kafka transactions, Spark checkpointing, an idempotent or transactional sink like Delta. Across arbitrary systems, the honest answer is at-least-once delivery plus idempotent writes keyed on a business key, which gives you the same observable outcome with far less complexity.
What they are testing: Intellectual honesty. Candidates who claim exactly-once everywhere get probed.
Partitioning versus bucketing — when do you use which?
Partition on a low-cardinality column you filter on constantly, usually a date; it prunes whole directories. Bucket on a high-cardinality join or aggregation key to pre-shuffle data into a fixed number of files and avoid shuffles at query time. Over-partitioning on high-cardinality columns is the classic mistake — it produces millions of tiny files and destroys performance.
What they are testing: Physical data layout judgement.
How do you decide between a data lake, a warehouse and a lakehouse?
Warehouse for structured data, strong governance and BI-heavy SQL workloads with predictable schemas. Lake for cheap storage of raw and semi-structured data at any scale, at the cost of governance and performance. Lakehouse when you want warehouse guarantees — ACID, schema enforcement, time travel — on lake economics and open formats, and when the same data must serve both BI and ML. Then justify against the team's actual skills, not the diagram.
What they are testing: Architecture reasoning, including organisational constraints.
A nightly job that used to take 40 minutes now takes 4 hours. Walk me through your investigation.
Start with what changed — data volume, code, cluster config, upstream schema. Compare Spark UI runs: which stage grew? Look for skew (max vs median task time), spill, small-file explosion, a broadcast that stopped being broadcast because the table grew past the threshold, or a shuffle partition count that no longer fits the data. Form one hypothesis at a time and test it.
What they are testing: Method. They want a systematic debugger, not a guesser.
Airflow & orchestration (4 questions)
What makes an Airflow task idempotent, and why does it matter?
Running it twice with the same parameters produces the same result. It matters because retries, backfills and manual reruns are normal operations, not exceptions. Achieve it by partitioning writes by the logical execution date and overwriting that partition, or by using MERGE keyed on a business key — never by blind appends.
What they are testing: Whether you have operated pipelines, not just written them.
How do you handle a backfill of 18 months of daily data?
Make sure tasks are idempotent and partitioned by execution date first. Then limit concurrency with pools and max_active_runs so the backfill does not starve production. Consider batching months rather than 540 individual runs, and verify a single day end-to-end before launching the whole range. Have a plan to detect and rerun partial failures.
What they are testing: Operational maturity.
When would you not use Airflow?
For sub-minute latency — Airflow is a scheduler, not a stream processor. For simple single-platform workflows where native orchestration (Databricks Workflows, ADF, Step Functions) is cheaper to run and operate. For event-driven fan-out at very high volume, where a serverless state machine fits better. Saying 'always Airflow' is a red flag.
What they are testing: Whether you pick tools or defend habits.
Explain Kafka consumer groups and partition assignment.
A topic is split into partitions; a consumer group is a set of consumers sharing the work. Each partition is assigned to exactly one consumer in the group, so parallelism is capped by partition count — extra consumers sit idle. Adding or removing a consumer triggers a rebalance. Offsets are committed per group, which is how two independent groups can read the same topic at their own pace.
What they are testing: Kafka fundamentals — commonly asked, commonly fumbled.
Modelling & data quality (4 questions)
What is the grain of a fact table and why does it matter so much?
The grain is what exactly one row represents — 'one line item on one order' rather than 'orders'. It matters because every measure, every dimension key and every aggregation depends on it. Almost all double-counting bugs come from a fact table whose grain was never explicitly stated, or from joining two facts at different grains.
What they are testing: Dimensional modelling depth beyond 'star schema'.
How do you handle a schema change from an upstream source?
Detect it rather than discover it in a broken dashboard: schema checks in ingestion, a rescued-data column, and alerting on unexpected fields. Additive changes can flow through with schema evolution. Breaking changes should quarantine the batch and page a human. Longer term, a data contract with the producing team is the actual fix.
What they are testing: Whether you have been burned by this before.
What data quality checks do you put in a production pipeline?
Freshness (did data arrive), volume (row count within an expected band), schema (types and required fields), uniqueness on the business key, referential integrity to dimensions, null rates on critical columns, and a small set of business-rule assertions. Split them into fail-the-build checks and warn-only checks, so alerting stays meaningful.
What they are testing: Production discipline.
How do you test a data pipeline?
Unit test transformations with small fixture DataFrames and assert on the output frame. Integration test against a sample dataset in a dev environment. Add data-quality assertions that run on every production execution. Add regression fixtures for every bug you fix. 'We check the dashboard looks right' is not testing.
What they are testing: Software engineering standards in a data role.
Generative AI (3 questions)
Why would a data engineer be asked about RAG?
Because retrieval-augmented generation is a data pipeline: ingest documents, chunk, embed, index, retrieve, rerank. Chunking strategy, freshness, incremental indexing, deletion and permissions-aware retrieval are all data engineering problems. Teams are increasingly staffing these with data engineers rather than ML engineers.
What they are testing: Whether you are current with where the roles are moving.
How do you evaluate a RAG system?
Build a golden set of questions with known correct answers and known source passages. Measure retrieval separately from generation: context precision and recall for retrieval; faithfulness (is the answer grounded in retrieved context) and answer relevance for generation. RAGAS automates much of this. LLM-as-judge is useful but needs a rubric and a calibration check. Wire the eval into CI so quality regressions fail the build.
What they are testing: Whether you ship on measurement or on vibes.
When is fine-tuning the wrong answer?
Almost always at first. If the problem is missing knowledge, retrieval fixes it more cheaply and stays current. If the problem is format or tone, a better prompt with examples usually fixes it. Fine-tuning earns its cost when you need a consistent output style at scale, a smaller cheaper model to match a larger one, or a domain vocabulary the base model genuinely lacks.
What they are testing: Cost judgement, which is what separates engineers from demo-builders.
Behavioural (3 questions)
Tell me about a pipeline that failed in production. What happened?
Pick a real one. State the impact in business terms, the root cause (not 'the API was flaky' — why did the failure propagate?), the immediate fix, and the systemic change that stopped it recurring. Interviewers are listening for ownership and for whether you changed the system or just the symptom.
What they are testing: Ownership and root-cause thinking.
How do you handle a stakeholder who wants a dashboard by Friday when the data is not ready?
Say what is achievable by Friday and what is not, offer a reduced scope that is honest about its limits, and be explicit about what would break if you shipped the full thing untested. Escalate with options rather than a refusal. Interviewers are testing whether you will quietly ship something wrong under pressure.
What they are testing: Judgement under pressure and communication.
Why are you moving from your current role?
Answer forwards, not backwards: the work you want to do next and why this role offers it. Avoid criticising your current employer even when it is deserved — interviewers extrapolate how you will talk about them later.
What they are testing: Professionalism and self-awareness.
Want a mock interview on these?
Every masterclass includes a mock interview with Venu Katragadda and written feedback on your SQL, Spark and design rounds — plus a resume rewritten around outcomes.