50 Data Engineer Interview Questions and Answers for 2026-27

Data engineering interviews have changed. Two years ago, a recruiter in Hinjewadi or Kharadi would ask you to write a join and explain ETL. In 2026-27, the same recruiter expects you to reason about lakehouse design, Spark performance, cloud cost, orchestration and, increasingly, how Gen AI fits into a pipeline. That is why learners who complete a Data Engineering Course in Pune at IntelliBI practise interviews from the first month, not the last week.

This guide collects 50 questions that our placement team and alumni have actually faced at companies such as TCS, Infosys, Cognizant, Accenture, Deloitte and Pune-based product firms. The answers are deliberately short: the length you would speak in a real round. Use them as a checklist, then back each one with a project you can walk through on a whiteboard.

Section 1: SQL and Data Modelling Questions

SQL is still the first filter in every Data Engineering Training in Pune interview loop. Expect these.

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation. Use WHERE for row-level conditions and HAVING for conditions on aggregates like SUM or COUNT.

2. Explain window functions with an example.

Window functions compute values across a set of related rows without collapsing them. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) returns each customer's latest order while keeping all rows.

3. How do you find duplicate records in a table?

Group by the business key and filter HAVING COUNT(*) > 1, or use ROW_NUMBER() partitioned by the key and keep rows where the number is greater than 1 for deletion.

4. What is a CTE and when do you prefer it over a subquery?

A Common Table Expression is a named temporary result set. Prefer it for readability, reuse within a query, and recursive logic such as hierarchies.

5. Explain star schema versus snowflake schema.

A star schema has a central fact table joined to denormalised dimensions; it is fast and simple for BI. A snowflake normalises dimensions into sub-tables, saving storage but adding joins.

6. What is a Slowly Changing Dimension Type 2?

SCD Type 2 preserves history by inserting a new row when an attribute changes, with effective dates and a current flag. Interviewers love a phone-number or address change example.

7. How do indexes improve query performance, and when do they hurt?

Indexes speed up lookups and joins by avoiding full scans. They slow down inserts and updates and waste space when over-used or placed on low-cardinality columns.

8. What is the difference between ETL and ELT?

ETL transforms before loading, typical of on-prem warehouses. ELT loads raw data first and transforms inside the cloud warehouse or lakehouse, which is the pattern Databricks and Snowflake favour.

9. How would you design a fact table for daily sales?

Grain is one row per product, store and day. Include surrogate keys to dimensions, additive measures like quantity and revenue, and a date key. State the grain first; that is what interviewers check.

10. What are surrogate keys and why use them?

System-generated integer keys that replace natural business keys. They are stable when source keys change, support SCD Type 2 and make joins faster.

Section 2: Python and PySpark Questions

Every Data Engineer Course in Pune worth its fee goes deep on Spark, because this is where senior rounds separate candidates.

11. Why is Python the default language for data engineering?

Readable syntax, mature libraries (pandas, PySpark, Airflow SDKs), first-class support on every cloud, and the largest hiring pool. Scala still appears in legacy Spark shops.

12. Explain lazy evaluation in Spark.

Transformations such as filter and select build a logical plan; nothing runs until an action like count or write. This lets Catalyst optimise the whole plan before execution.

13. What is the difference between a transformation and an action?

Transformations return a new DataFrame and are lazy. Actions trigger computation and return results or write data. Knowing which operations are actions prevents accidental recomputation.

14. What are narrow and wide transformations?

Narrow transformations (map, filter) work within a partition. Wide transformations (groupBy, join) require a shuffle across partitions, which is the most expensive step in Spark.

15. How do you handle data skew in PySpark?

Enable Adaptive Query Execution, salt the skewed key, broadcast the smaller table, or repartition on a better-distributed column. Mention checking the Spark UI stage timings first.

16. When should you use cache or persist?

When a DataFrame is reused across multiple actions. Choose a storage level that fits memory, and unpersist when done to avoid executor pressure.

17. What is the difference between repartition and coalesce?

Repartition does a full shuffle and can increase or decrease partitions evenly. Coalesce merges partitions without a shuffle, so it is cheaper for reducing partition count before writing.

18. Why is Parquet preferred over CSV?

Columnar storage, compression, schema evolution and predicate pushdown. Spark reads only the columns and row groups it needs, cutting I/O dramatically.

19. How do you write a UDF in PySpark and why avoid it?

Register a Python function with udf() or pandas_udf. Standard UDFs serialise rows to Python and bypass Catalyst, so prefer built-in functions; use pandas UDFs when custom logic is unavoidable.

20. What is Structured Streaming?

Spark's incremental processing engine that treats a stream as an unbounded table. It supports micro-batch and continuous modes, watermarks for late data and exactly-once sinks like Delta.

Section 3: Azure and Databricks Questions

Pune enterprises lean Azure, so an Azure Data Engineer Course in Pune pays off in interviews. Databricks Training in Pune is the second most requested skill on local job descriptions.

21. What is Azure Data Factory and what are its core components?

ADF is Azure's managed data integration service. Core parts are pipelines, activities, datasets, linked services, integration runtimes and triggers.

22. Explain the types of integration runtime in ADF.

Azure IR for cloud-to-cloud movement, Self-Hosted IR for on-premises or private network sources, and SSIS IR to lift and shift existing SSIS packages.

23. How do you implement incremental loading in ADF?

Use a watermark column stored in a control table, read the last value with a Lookup activity, copy only newer rows, then update the watermark. Change Data Capture is the alternative for SQL sources.

24. What is the medallion architecture?

A lakehouse pattern with Bronze (raw), Silver (cleaned and conformed) and Gold (business aggregates) layers. It gives lineage, replayability and clear ownership per layer.

25. What is Delta Lake and why does it matter?

An open table format adding ACID transactions, schema enforcement, time travel and upserts on top of Parquet. It is the foundation of reliable lakehouse pipelines on Databricks.

26. Explain Delta Lake time travel.

Every write creates a new version in the transaction log. You can query a table AS OF a version or timestamp to audit, debug or roll back.

27. What is Unity Catalog?

Databricks' centralised governance layer for data and AI assets: a three-level namespace, fine-grained access control, lineage and audit across workspaces.

28. What is Auto Loader?

A Databricks feature that incrementally ingests new files from cloud storage using file notifications or directory listing, with schema inference and evolution built in.

29. Compare Databricks Jobs and Delta Live Tables pipelines.

Jobs orchestrate arbitrary tasks with dependencies. DLT pipelines are declarative, manage dependencies automatically and include data quality expectations. Use DLT for streaming-style ETL, Jobs for mixed workloads.

30. Which Azure certification is relevant for data engineers in 2026?

DP-700 Microsoft Certified: Fabric Data Engineer Associate replaced DP-203, and DP-750 Azure Databricks Data Engineer Associate launched in 2026. Mentioning both shows you track the market.

Section 4: AWS, Airflow and Pipeline Design Questions

Cloud engineer courses in Pune often stop at one provider. Multi-cloud questions are now common, so cover AWS basics too.

31. Describe a typical AWS data engineering stack.

S3 as the data lake, Glue for catalog and ETL, EMR for large Spark jobs, Lambda for event-driven tasks, Athena for serverless SQL and Redshift as the warehouse.

32. What is the AWS Glue Data Catalog?

A central metadata repository that stores table definitions and schemas discovered by crawlers, used by Glue jobs, Athena, EMR and Redshift Spectrum.

33. When would you choose Athena over Redshift?

Athena for ad-hoc, pay-per-query analysis directly on S3 with no infrastructure. Redshift for high-concurrency, repeated BI workloads needing a tuned warehouse.

34. What is Apache Airflow and what is a DAG?

Airflow is an orchestration tool. A DAG is a Directed Acyclic Graph of tasks with dependencies and a schedule, defined in Python.

35. How do tasks share data in Airflow?

Through XComs for small values, or by writing to external storage and passing references. Large payloads should never go through XComs.

36. What is backfilling and how do you make it safe?

Re-running a DAG for past intervals. Make tasks idempotent so re-runs overwrite the same partition instead of duplicating data.

37. What does idempotency mean in a data pipeline?

Running the pipeline twice with the same input produces the same result. Achieve it with partition overwrites, merge/upsert statements and deterministic keys.

38. How would you implement Change Data Capture?

Use database logs (Debezium, SQL Server CDC) or Delta Change Data Feed to capture inserts, updates and deletes, then merge into the target with a MERGE statement.

39. How do you monitor a production pipeline?

Track freshness, volume, schema drift and failure rate; alert on SLA misses; log run metadata to a control table; use tools such as Airflow alerts, Azure Monitor or CloudWatch.

40. How do you control cloud cost in data pipelines?

Right-size clusters, use job clusters and auto-termination, partition and prune data, prefer serverless for spiky loads, and review storage tiers and query patterns monthly.

Section 5: Gen AI, Scenario and HR Questions

An Advanced Data Engineer Course in Pune now includes Gen AI, because interviewers at AI-first teams ask about it directly.

41. How is Gen AI changing the data engineer's role?

Engineers now build pipelines that feed LLMs: chunking documents, generating embeddings, maintaining vector indexes and monitoring data quality for AI applications.

42. What is RAG and where does data engineering fit?

Retrieval-Augmented Generation retrieves relevant documents from a vector store and passes them to an LLM. Data engineers own ingestion, chunking, embedding refresh and access control.

43. What is a vector database?

A store optimised for similarity search over embeddings, such as Databricks Vector Search or Pinecone. It supports nearest-neighbour queries at scale.

44. What is MCP and why should data engineers know it?

Model Context Protocol lets AI agents call tools and data sources in a standard way. Data engineers expose governed datasets and pipelines as tools that agents can use safely.

45. Scenario: a daily pipeline that normally takes 20 minutes now takes 3 hours. What do you check?

Data volume spike, partition skew, cluster size changes, a new wide join, loss of caching, upstream schema changes and concurrent jobs competing for resources.

46. Scenario: downstream reports show duplicate revenue. How do you debug?

Check join cardinality, re-run logic without idempotency, late-arriving data merged twice, and dimension SCD handling. Reproduce with a small date range before fixing.

47. Scenario: a source system changes a column type without notice. What is your fix and your prevention?

Fix by casting in the Silver layer and reprocessing affected partitions. Prevent with schema enforcement, contract tests and alerts on schema drift.

48. Describe your most challenging data engineering project.

Connect your background to the role. A tester can talk about validation discipline; a SQL developer about modelling; a BI developer about understanding what business users need.e the STAR method: situation, task, action, result. Quantify: rows per day, latency reduced, cost saved. IntelliBI learners usually present their medallion lakehouse or healthcare claims platform capstone here.

49. Why do you want to move into data engineering?

Connect your background to the role. A tester can talk about validation discipline; a SQL developer about modelling; a BI developer about understanding what business users need.

50. What are your salary expectations?

Quote a researched range for Pune and your experience level, anchored to the modern stack you bring. Candidates with Databricks and cloud skills typically negotiate from a stronger position.

How to Prepare: From Questions to Offers

Knowing answers is the floor, not the ceiling. The Best Data Engineering Course in Pune is the one that makes you build what you talk about. At IntelliBI's Best Data Engineering Institute in Pune in Thergaon, PCMC, learners complete 10+ production-grade projects across retail, healthcare, banking and insurance, then face mock technical interviews with senior professionals, HackerRank-style coding rounds and system design sessions. That combination is why our Data Engineering Classes in Pune have supported 2,200+ career transformations with a 4.9-star Google rating.

Three habits that move candidates from "prepared" to "placed":

1. Explain one project end to end in under five minutes, with architecture, decisions and a failure you fixed.
2. Keep a GitHub portfolio with readable notebooks, DAGs and a README for every project.
3. Practise aloud. Interviewers judge clarity as much as correctness.

Final Word

The 2026-27 interview is a conversation about judgement: why Delta over plain Parquet, why Airflow over cron, why a Gold table has that grain, how an agent should access governed data. Learn the fundamentals, build on real cloud platforms and rehearse these 50 answers until they sound like you.

If you are ready to go beyond the question bank, explore the Advanced Data Engineer Course in Pune from IntelliBI Innovations Technologies, with weekday evening and weekend batches, lifetime LMS access and placement support until you are placed. Book a free demo class and bring your toughest interview question with you.