Data Engineering Interview Questions for Freshers: SQL, Python and Scenario Rounds, with Answers

Data engineering interviews for freshers in India test five things: SQL you can write live, working Python, how you model data, how you build pipelines that survive failures, and whether you can explain your own project clearly. The 45 questions are grouped by round, each with a short model answer you can adapt, plus SQL written out as you would type it in a coding round. Work through one round a day and you will have covered the whole loop in a week.

How a fresher data engineering interview runs

Most hiring loops for fresher and junior data engineer roles follow the same shape, whether at a bank, a product company or a global capability centre:

Round	What it tests	Typical format
Online assessment	SQL, Python, aptitude	Timed questions on a coding platform
Technical round 1	SQL depth, Python, data modelling	Live coding with an interviewer
Technical round 2	Pipelines, cloud and Spark basics, scenarios	Discussion around your project and a small design problem
Managerial or HR	Communication, ownership, motivation	Conversation about your projects, choices and fit

Prepare for every round, not only SQL. Round 2 is where many freshers struggle, because it asks you to explain what happens when your pipeline runs twice or fails halfway.

Round 1: SQL interview questions for data engineers

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation. To find cities with more than 100 orders in 2026: SELECT city, COUNT(*) FROM orders WHERE order_date >= '2026-01-01' GROUP BY city HAVING COUNT(*) > 100;

2. Explain the types of joins. What does a LEFT JOIN return when there is no match?

INNER JOIN returns matching rows from both tables. LEFT JOIN returns every row from the left table and NULLs for the right table's columns when nothing matches. RIGHT JOIN is the mirror image, FULL OUTER JOIN keeps unmatched rows from both sides, and CROSS JOIN returns every combination. A common follow-up: find customers who never ordered, using a LEFT JOIN and WHERE o.customer_id IS NULL.

3. Find the second-highest salary.

The version that also handles ties uses DENSE_RANK:

SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2;

A shorter version for interviews that allow it: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

4. How do you find and remove duplicate rows?

Number the rows inside each duplicate group and keep only the first. To see duplicates: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1; To keep one row per email, the latest one:

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn FROM customers) SELECT * FROM ranked WHERE rn = 1;

5. How is a window function different from GROUP BY?

GROUP BY collapses rows into one row per group. A window function calculates across related rows but keeps every row; the PostgreSQL documentation describes it as rows that "retain their separate identities". So AVG(salary) OVER (PARTITION BY dept) gives every employee their department's average on their own row.

6. ROW_NUMBER, RANK and DENSE_RANK: what is the difference?

For scores 90, 90, 80: ROW_NUMBER gives 1, 2, 3 (always unique); RANK gives 1, 1, 3 (ties share, then a gap); DENSE_RANK gives 1, 1, 2 (ties share, no gap). Use ROW_NUMBER for deduplication and DENSE_RANK for "Nth highest" questions.

7. Write a running total and a 7-day moving average of daily revenue.

SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS running_total, AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM daily_revenue;

Mention that ROWS counts physical rows, so missing days need filling first if the average must cover seven calendar days.

8. Find the top 3 products by revenue in each category.

WITH r AS (SELECT category, product, SUM(amount) AS rev, DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS rk FROM order_items GROUP BY category, product) SELECT * FROM r WHERE rk <= 3;

9. Show month-on-month change in revenue.

Use LAG to bring last month's value onto this month's row: SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM monthly_revenue; Divide by the LAG value for a percentage, and guard against division by zero with NULLIF.

10. What is a CTE, and when would you use a recursive one?

A CTE (WITH clause) names a subquery so a long query reads top to bottom and can reuse a step. A recursive CTE references itself, which is how you walk hierarchies such as an employee-manager tree or a category tree of unknown depth.

11. What is an index, and when can it hurt?

An index is a sorted lookup structure that lets the database find rows without scanning the whole table. It speeds up reads on filtered and joined columns, but every insert and update must also update the index, so heavy-write tables and low-selectivity columns (such as a yes/no flag) gain little. Use EXPLAIN to check whether a query actually uses it.

Round 2: Python questions

12. List, tuple, set and dict: when do you use each?

A list is ordered and changeable; a tuple is ordered and fixed, useful as a dictionary key; a set holds unique items with fast membership checks; a dict maps keys to values with fast lookup. In pipelines, sets are the quick answer to "have I seen this ID already?"

13. How do you process a 10 GB CSV on a laptop with 8 GB of RAM?

Do not load it at once. Read it in chunks with pandas (read_csv with chunksize), process and write each chunk, or stream it line by line with a generator. Better still, convert it to Parquet once and query it with an engine built for larger-than-memory data, or use Spark if it keeps growing.

14. Remove duplicates from a list while keeping the order.

Use a dict, which keeps insertion order: list(dict.fromkeys(items)). Or loop with a seen set and append only unseen items. Interviewers usually want you to explain why list(set(items)) loses the order.

15. How would you pull data from a paginated API reliably?

Loop until the API returns no next page or cursor, call requests.get with a timeout, check the status code, and retry on 429 and 5xx errors with exponential backoff. Log each page, write raw responses to storage before transforming them, and keep secrets in environment variables, never in code.

16. What is the difference between merge and concat in pandas?

merge joins two DataFrames on key columns, like a SQL join (how='left', 'inner' and so on). concat stacks DataFrames on top of each other or side by side without matching keys. After a merge, check row counts: an unexpected increase means duplicate keys on one side.

17. How do you test a data-cleaning function?

Write pytest tests with small input tables and the exact output you expect: nulls handled, types converted, duplicates removed, bad dates rejected. Add one test for empty input. Tests let you change the code later without silently breaking the data.

Round 3: Data modelling questions

18. OLTP versus OLAP?

OLTP systems run the business: many small, fast inserts and updates, normalised tables (a bank's transaction system). OLAP systems analyse it: large scans and aggregations over history, usually denormalised star schemas in a warehouse.

19. What are fact and dimension tables? Star versus snowflake schema?

A fact table holds measurable events (an order line, a toll payment) with foreign keys and numbers. Dimension tables describe them (customer, product, date). In a star schema, dimensions connect directly to the fact; in a snowflake schema, dimensions are normalised into sub-tables. Star is simpler and faster to query, which is why most warehouses prefer it.

20. Explain slowly changing dimensions types 1, 2 and 3.

Type 1 overwrites the old value, so history is lost. Type 2 adds a new row for each change; Kimball recommends at least three extra columns: an effective date, an expiry date and a current-row flag. Type 3 adds a column for the previous value, keeping limited history. A customer moving from Pune to Bengaluru is the classic Type 2 example.

21. What is the grain of a fact table, and why decide it first?

The grain is what one row represents: one order, one order line, one payment per minute. Deciding it first stops you mixing levels of detail, which causes double counting when you aggregate.

22. Why do warehouses denormalise when databases normalise?

Normalisation (1NF to 3NF) removes redundancy so writes stay consistent. Warehouses are read-heavy, so they accept some redundancy to avoid expensive joins and make queries simpler and faster.

Round 4: Pipeline and ETL questions

23. ETL versus ELT?

ETL transforms data before loading it into the target. ELT loads raw data first and transforms it inside the warehouse or lakehouse, usually with SQL or dbt. ELT dominates with cloud warehouses because storage is cheap and compute scales, and keeping the raw copy lets you rebuild when logic changes.

24. What does idempotent mean for a pipeline?

Running it once or five times for the same input gives the same result. The Airflow documentation says to treat tasks like database transactions and to replace INSERT with UPSERT, because a retried INSERT creates duplicate rows. Idempotency is what makes retries and backfills safe.

25. Full load versus incremental load? How do you do incremental?

A full load reloads everything; an incremental load moves only new or changed rows. Track a watermark (the highest updated_at or ID processed), pull rows above it, and MERGE them into the target. For deletes and exact change history, use change data capture from the database log.

26. What is a backfill, and how do you run one safely?

A backfill reprocesses past periods, for example after fixing a bug. It is safe when each run reads a fixed time window (Airflow's data interval) rather than "the latest data", and writes by overwriting that window's partition or by MERGE, so reruns replace rather than duplicate.

27. In Airflow, what are a DAG, a task, an operator and a sensor?

A DAG is the workflow: tasks and their dependencies, with a schedule. A task is one unit of work; an operator is the template it is built from (PythonOperator, BashOperator). A sensor is a task that waits for a condition, such as a file arriving. Mention retries, alerts on failure, and keeping heavy code out of the DAG file's top level.

28. What data quality checks would you add to a pipeline?

Row counts against the source, null checks on key columns, uniqueness of primary keys, accepted values for status columns, foreign keys that match their parent table, and freshness. dbt ships these as four generic tests (unique, not_null, accepted_values and relationships), and you can fail the run when a check breaks.

Round 5: Cloud, Spark and streaming basics

29. Data lake, data warehouse and lakehouse: what is the difference?

A lake stores raw files of any format cheaply in object storage. A warehouse stores structured, modelled tables for fast SQL. A lakehouse adds table formats such as Delta Lake or Iceberg on top of the lake, giving warehouse features (transactions, schema enforcement) on lake storage. Many teams organise it in medallion layers: bronze for raw, silver for cleaned and validated, gold for business-ready data.

30. Why Parquet instead of CSV?

Parquet is columnar and compressed: a query reading three columns skips the rest, files are much smaller, and the schema and data types travel with the file. CSV has no types, must be read in full, and breaks on stray commas.

31. What are transformations and actions in Spark? What is lazy evaluation?

Transformations (select, filter, join) define a new dataset; actions (count, collect, write) return a result or write output. The Spark documentation states that all transformations are lazy: Spark records them and only computes when an action needs a result, which lets it optimise the whole plan.

32. What is a shuffle, and why is it expensive?

A shuffle redistributes data across partitions, for example for a groupBy or a join on a non-partitioned key. Spark's documentation explains it involves disk I/O, data serialisation and network I/O. Wide transformations cause shuffles; narrow ones (filter, map) do not.

33. What is data skew, and how do you fix it?

Skew is when one key holds far more rows than others, so one task runs for an hour while the rest finish in minutes. Fixes: broadcast the small table in a join, salt the hot key, filter early, and rely on Adaptive Query Execution, which Spark enables by default since 3.2.0 and which splits skewed partitions at runtime.

34. What is a broadcast join?

Spark copies a small table to every executor so the large table never needs shuffling. By default Spark broadcasts tables under 10 MB (spark.sql.autoBroadcastJoinThreshold), and you can force it with a broadcast hint.

35. repartition versus coalesce?

repartition can increase or decrease partitions and performs a full shuffle to balance them. coalesce only reduces partitions by merging neighbours without a full shuffle, which makes it the cheaper choice before writing fewer output files.

36. What delivery semantics does Kafka offer?

At most once (messages may be lost, never redelivered), at least once (never lost, may repeat) and exactly once (each message processed once). Kafka's default is at least once, so consumers should be idempotent, for example by upserting on an event ID.

Round 6: Scenario and system design questions

37. Your daily pipeline failed overnight and the morning dashboard is empty. What do you do?

Tell the dashboard's users first, with an expected fix time. Read the failed task's logs, find the root cause (source late, schema changed, credentials expired, out of memory), fix it, and rerun only the failed window, which is safe if the pipeline is idempotent. Then write a short root-cause note and add the check or alert that would have caught it earlier.

38. Design a pipeline that shows UPI-style payment failure rates per bank, updated every minute.

Payment events go to Kafka, partitioned by bank. Spark Structured Streaming reads them, computes one-minute windows on event time with a watermark for late events, and writes failure rates to a fast store the dashboard reads. Raw events also land in the lake for daily batch reconciliation. Mention Lambda (separate batch and stream paths) versus Kappa (one streaming path, replayed for history) and why you chose one.

39. A number on the dashboard does not match the source system. How do you investigate?

Pick one day and one ID and trace it layer by layer: source, raw, cleaned, model, dashboard. Compare row counts at each step. The usual causes are join fan-out from duplicate keys, time-zone mismatches (UTC against IST), late-arriving data, and filters applied in one layer but not another.

40. How would you handle personal data such as phone numbers and PAN in a pipeline?

Classify the columns, mask or hash them as early as possible, restrict raw access by role, and log who reads what. In India, the Digital Personal Data Protection Rules were notified on 13 November 2025 and obligations on security safeguards, breach reporting and retention apply from 13 May 2027, so expect this question more often.

41. The team's cloud data bill doubled this month. Where do you look?

Check the cost breakdown by service and job, then the largest queries and clusters. Common causes: full-table scans on unpartitioned data, clusters left running, a job rerunning in a loop, and duplicated storage. Fixes include partitioning, auto-stop on clusters, right-sizing, and budget alerts.

Round 7: Behavioural questions

42. Tell me about yourself.

Use 60 to 90 seconds: your background in one line, why data engineering, the one project you are proudest of with a number (rows processed, runtime cut, cost per month), and what you want to do in this role. Skip your school marks unless asked.

43. Walk me through a project you built.

Problem, data sources, architecture (draw it), the hardest decision and why, how you tested it, what happens when it fails, and what you would change. Interviewers probe the failure and re-run parts, so rehearse them.

44. Tell me about a mistake you made and how you fixed it.

Choose a technical mistake from a project, such as a pipeline that double-loaded data. Say what happened, how you found it, how you fixed the data, and what you changed so it cannot recur. Ownership matters more than the size of the mistake.

45. Why data engineering and not data science?

Give a specific reason: you enjoy building systems that run reliably, and every model and dashboard depends on the data underneath. Point to a project where you made data usable for someone else.

A 4-week plan to prepare

Week	Focus	Daily target
1	SQL: joins, aggregation, window functions	5 timed questions, then explain each aloud
2	Python and data modelling	2 coding problems and one schema sketched on paper
3	Pipelines, Airflow, Spark, cloud basics	Rebuild one project feature; answer 5 concept questions
4	Scenarios, project walkthrough, mock rounds	One mock round a day with a friend or recorded on video

How SkilD prepares you for these rounds

In the PG Program in Data Engineering, a full hiring week covers data-platform system design (including Lambda versus Kappa), timed SQL, Python and DSA-for-data rounds, and your portfolio page. These questions come with projects you can talk through, among the 17 in the program: Kirana Ledger, the Highway Toll Data Pipeline, the Mandi Price Pipeline, the Real-Time Payment Monitor and a Data Quality Test Suite. Classes are live on Saturdays and Sundays, 10 AM to 1 PM IST, with industry experts including faculty from Mastercard and Paytm, and placement support runs until you have an offer. SkilD has trained 4,200+ learners, and the highest recorded salary hike among them is 82%.

Frequently asked questions