Data Engineer Interview Questions &amp; Answers

Show answer

A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.

An interviewer asks how you would find why a query is slow. What steps do you describe?

Show answer

First reproduce the issue and measure time. Then check the execution plan for scans, expensive sorts, row estimate errors and join types. Look at indexes on filter and join columns, whether predicates are SARGable, and statistics freshness. Check for blocking or locks from other sessions. Change one thing at a time, measure again, and document the improvement.

What are referential actions such as ON DELETE CASCADE?

Show answer

Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.

Tell me about a time you used advanced SQL to solve a business problem.

Show answer

Situation: the product team wanted to know where users dropped off in the onboarding flow. Task: build a funnel from raw event logs. Action: I used window functions to order events per user, LEAD to find the next step and time taken, and grouped the results into a funnel with conversion rates. Result: we found a 40 percent drop at KYC upload, and the redesign improved completion by 15 percent.

Someone ran an UPDATE without WHERE on production. What would you do?

SQL BasicsMedium

Show answer

Stop further changes and inform the lead and DBA immediately. If the session is still in an open transaction, roll back. Otherwise, restore affected rows from the latest backup or point-in-time recovery to a separate database and update the production table from it, or use audit or history tables if available. Afterwards, add safeguards such as safe-update mode, reviews and restricted write access.

Tell me about a database design decision you made and its outcome.

Show answer

Situation: our app stored user preferences in a wide table with 60 nullable columns. Task: make it flexible for new settings. Action: I proposed a key-value preferences table with a JSON column for rarely used settings, kept frequently queried fields as columns, and wrote a migration. Result: new settings no longer needed schema changes and query speed for common fields stayed the same.

Tell me about a time you significantly improved query performance.

Show answer

Situation: a daily settlement report took 45 minutes and often missed its deadline. Task: bring it under ten minutes. Action: I studied the execution plan, replaced a non-SARGable date filter, added a composite covering index, pre-aggregated a large join in a temp table and updated statistics. Result: runtime dropped to six minutes, and the finance team received reports on time every day.

Your running total jumps by large amounts on some dates instead of row by row. Why?

Show answer

With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.

What is a trigger and when should it be used?

Transactions and Advanced SQLMedium

Show answer

A trigger is code that runs automatically before or after INSERT, UPDATE or DELETE on a table. It is used for auditing changes, enforcing complex rules and maintaining derived data. Triggers are hidden from application developers, can slow writes and make debugging hard, especially when they cascade. Use them sparingly, document them well, and prefer constraints or application logic when possible.

A query that ran in 1 second now takes 40 seconds after a data load. What do you check?

Show answer

Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.

Describe a time you reviewed someone else's SQL and suggested improvements.

Show answer

Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.

A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?

Show answer

Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.

Why is NOT IN dangerous when the subquery contains NULL?

Show answer

If the subquery returns any NULL, value NOT IN (list) becomes unknown for every row, because comparing with NULL is unknown, so the query returns no rows. For example, NOT IN (SELECT manager_id FROM employees) fails when a manager_id is NULL. Use NOT EXISTS or filter out NULLs in the subquery to get correct results.

You need each customer's latest address from a history table. How do you query it?

Show answer

Use ROW_NUMBER partitioned by customer and ordered by updated date descending, then keep rn = 1: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM address_history) SELECT * FROM r WHERE rn = 1. Add a tiebreaker such as id DESC in case two updates share the same timestamp.

What are window functions in SQL?

Window FunctionsEasy

Show answer

Window functions perform calculations across a set of rows related to the current row without collapsing them into one row like GROUP BY. They use the OVER clause with optional PARTITION BY, ORDER BY and a frame. Examples are ROW_NUMBER, RANK, running SUM, LAG and LEAD. You can show each order alongside the customer's total, which GROUP BY alone cannot do.

What is a window frame and what is the default frame?

Show answer

A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.

What are surrogate keys and natural keys?

Show answer

A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.

What is a SARGable query and why does it matter?

Show answer

A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.

A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?

Show answer

The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.

Can you use window functions with aggregate functions in the same query?

Show answer

Yes. Window functions are evaluated after GROUP BY, so they can operate on aggregated values. For example, SELECT region, SUM(sales) AS s, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk FROM orders GROUP BY region ranks regions by total sales. The aggregate is computed per group first, and then the window function runs over the grouped rows.

What is a star schema and a snowflake schema?

Show answer

A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.

Rewrite a nested query using CTEs for readability.

Show answer

WITH order_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), ranked AS ( SELECT customer_id, total, NTILE(10) OVER (ORDER BY total DESC) AS decile FROM order_totals ) SELECT * FROM ranked WHERE decile = 1; Each CTE is a named step, making top 10 percent customer logic easy to read.

What is denormalisation and when is it used?

Show answer

Denormalisation intentionally adds redundancy, such as storing customer name in an orders reporting table, to reduce joins and speed up reads. It is common in data warehouses, reporting tables and high-read applications. The cost is extra storage and the need to keep copies consistent through ETL, triggers or application logic. Use it when read performance matters more than write simplicity.

You need to delete duplicate rows but keep one copy of each. How do you do it?

Show answer

Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.

What is the difference between an index scan and an index seek?

Show answer

An index seek navigates the B-tree directly to the matching keys, reading only the needed rows, which is efficient for selective filters. An index scan reads the whole index or a large range of it. A table scan reads every row. Scans are not always bad; for queries returning a large share of rows, a scan can be cheaper than many seeks.

Are CTEs materialised or inlined?

Show answer

It depends on the database. SQL Server and MySQL usually inline CTEs like subqueries. PostgreSQL before version 12 always materialised CTEs, which could hurt performance; since version 12 non-recursive CTEs referenced once are inlined, and you can force behaviour with MATERIALIZED or NOT MATERIALIZED. Referencing a heavy CTE many times may cause repeated work, so check the plan.

What is a covering index?

Show answer

A covering index contains all columns a query needs, either as key columns or included columns, so the database answers the query from the index alone without visiting the table. For SELECT order_date, amount FROM orders WHERE customer_id = 5, an index on (customer_id) INCLUDE (order_date, amount) avoids lookups. It speeds reads but increases index size and write cost.

What are LAG and LEAD functions?

Show answer

LAG returns a value from a previous row in the partition and LEAD from a following row, based on the ORDER BY. LAG(revenue, 1, 0) OVER (ORDER BY month) gives last month's revenue with 0 as default for the first row. They are used for month-over-month change, time between events, and detecting status changes without self joins.

Calculate a 7-day moving average of daily active users.

Show answer

SELECT activity_date, dau, ROUND(AVG(dau) OVER ( ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ), 1) AS dau_7d_avg FROM daily_users ORDER BY activity_date; This assumes one row per day; fill missing dates with a calendar table first, or ROWS will span more than seven days.

A query with many OR conditions across different columns is slow. How can you optimise it?

Indexes and PerformanceHard

Show answer

OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.

How would you design a database for an online food delivery app at a high level?

Show answer

Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.

An interviewer asks for the Nth highest salary in a reusable way. How do you answer?

Show answer

Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.

What are FIRST_VALUE, LAST_VALUE and NTH_VALUE?

Show answer

FIRST_VALUE returns the first value in the window frame, LAST_VALUE the last, and NTH_VALUE the nth. A common trap is LAST_VALUE with the default frame, which ends at the current row, so it returns the current row's value. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value of the partition.

What is the difference between IN and EXISTS?

Show answer

IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.

How do you read an EXPLAIN ANALYZE output in PostgreSQL?

Show answer

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 101; Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.

What are database statistics and why do they matter?

Show answer

Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.

What is the difference between ROWS and RANGE frames?