SQL Interview Questions for Data Engineers (27 Q&As)

Twenty-seven questions that data engineering interviews ask about SQL: deduplication, incremental loads, upserts, slowly changing dimensions, sessionization, data quality checks and partitioning. Every query was run on PostgreSQL 16 and the output is real.

By Matthew Rathbone · November 1, 2026

Data engineering interviews test the SQL you write when moving and shaping data: removing duplicates, loading only what changed, keeping history in dimension tables, and proving that a pipeline did what it should. Expect fewer "what does HAVING do" questions than in an analyst interview and more "how would you make this load safe to re-run".

This page has 27 questions grouped in that order, each with a short answer you can say out loud and, where it helps, a query with its real output. The fundamentals (joins, aggregates, keys) are in our 30 SQL interview questions, and window functions, recursive CTEs and query plans go deeper in the advanced SQL interview questions. Aiming at an analyst role instead? See SQL interview questions for data analysts.

Jump to: set up the data · concepts · loading data · slowly changing dimensions · transformations · data quality · performance and design · interview tips · FAQ

Set up the practice database

Queries use PostgreSQL syntax and were run on PostgreSQL 16. MERGE needs version 15 or later. Each question notes where Snowflake, BigQuery, SQL Server or MySQL differ. Paste the script below into any PostgreSQL client, such as Beekeeper Studio, to follow along. It creates a tiny warehouse: raw and staging tables, a fact table, a Type 2 customer dimension and a few event tables. Questions that change data are shown on their own, so rerun the script to reset.

CREATE TABLE raw_customers (customer_id INT, email TEXT, plan TEXT, updated_at TIMESTAMP);
INSERT INTO raw_customers VALUES
 (1,'[email protected]','free','2025-01-10 09:00'),
 (1,'[email protected]','pro','2025-02-14 12:30'),
 (2,'[email protected]','free','2025-01-20 08:15'),
 (3,'[email protected]','pro','2025-01-22 17:45'),
 (3,'[email protected]','pro','2025-01-22 17:45'),
 (4,'[email protected]','free','2025-03-01 10:00'),
 (4,'[email protected]','team','2025-03-18 11:20');
CREATE TABLE dim_customer (customer_key SERIAL PRIMARY KEY, customer_id INT, plan TEXT, valid_from DATE, valid_to DATE, is_current BOOLEAN);
INSERT INTO dim_customer (customer_id, plan, valid_from, valid_to, is_current) VALUES
 (1,'free','2025-01-10','2025-02-14',false),
 (1,'pro','2025-02-14','9999-12-31',true),
 (2,'free','2025-01-20','9999-12-31',true),
 (3,'pro','2025-01-22','9999-12-31',true);
CREATE TABLE fact_orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, amount NUMERIC(8,2), loaded_at TIMESTAMP);
INSERT INTO fact_orders VALUES
 (101,1,'2025-01-12',40.00,'2025-01-13 02:00'),
 (102,1,'2025-02-20',90.00,'2025-02-21 02:00'),
 (103,2,'2025-01-25',25.00,'2025-01-26 02:00'),
 (104,3,'2025-02-02',120.00,'2025-02-03 02:00'),
 (105,3,'2025-02-03',60.00,'2025-02-04 02:00'),
 (106,2,'2025-02-05',35.00,'2025-02-06 02:00'),
 (107,1,'2025-02-07',55.00,'2025-02-08 02:00'),
 (108,9,'2025-02-08',70.00,'2025-02-09 02:00');
CREATE TABLE stg_orders (order_id INT, customer_id INT, order_date DATE, amount NUMERIC(8,2), updated_at TIMESTAMP);
INSERT INTO stg_orders VALUES
 (106,2,'2025-02-05',45.00,'2025-02-22 09:00'),
 (109,4,'2025-02-22',80.00,'2025-02-22 15:00'),
 (110,2,'2025-02-23',30.00,'2025-02-23 16:30');
CREATE TABLE page_events (user_id INT, event_time TIMESTAMP, page TEXT);
INSERT INTO page_events VALUES
 (1,'2025-03-01 09:00','home'),(1,'2025-03-01 09:05','pricing'),(1,'2025-03-01 09:20','signup'),
 (1,'2025-03-01 14:00','home'),(1,'2025-03-01 14:10','docs'),
 (2,'2025-03-01 10:00','home'),(2,'2025-03-01 10:40','pricing');
CREATE TABLE daily_signups (day DATE, signups INT);
INSERT INTO daily_signups VALUES
 ('2025-03-01',12),('2025-03-02',9),('2025-03-03',15),('2025-03-05',20),('2025-03-06',11),('2025-03-07',18);
CREATE TABLE raw_json_events (id INT, payload JSONB);
INSERT INTO raw_json_events VALUES
 (1,'{"user":{"id":1,"country":"US"},"event":"purchase","items":[{"sku":"A1","qty":2},{"sku":"B7","qty":1}]}'),
 (2,'{"user":{"id":2,"country":"BR"},"event":"view","items":[]}'),
 (3,'{"user":{"id":3},"event":"purchase","items":[{"sku":"A1","qty":1}]}');
CREATE TABLE products (sku TEXT PRIMARY KEY, category TEXT);
INSERT INTO products VALUES ('A1','books'),('B7','games');
CREATE TABLE refunds (order_id INT, amount NUMERIC(8,2));
INSERT INTO refunds VALUES (101,10.00),(101,5.00),(104,120.00);

Data engineering concepts

1. What is the difference between ETL and ELT?

ETL (extract, transform, load) cleans and reshapes data before loading it into the warehouse, usually on a separate processing server. ELT loads raw data first and transforms it inside the warehouse with SQL. ELT is now the common pattern because cloud warehouses (Snowflake, BigQuery, Redshift, PostgreSQL) are cheap and fast enough to do the transformation, the raw data stays available for reprocessing, and the transformations are plain SQL that analysts can read. Tools like dbt exist to manage that SQL. ETL still makes sense when data must be masked or filtered before it may be stored.

2. What is the difference between OLTP and OLAP databases?

OLTP systems run applications: many small reads and writes, strict consistency, normalized tables. OLAP systems answer analytical questions: fewer but much larger queries that scan and aggregate millions of rows, often on denormalized or columnar storage. A data engineer moves data from the first kind of system into the second. See what is normalization for why OLTP schemas are split into many tables.

3. What are fact and dimension tables, and what is a star schema?

A fact table records events or measurements (one row per order, click or payment) and holds numeric measures plus foreign keys. A dimension table describes the entities those events refer to (customers, products, dates). A star schema puts one fact table in the middle with dimensions around it, so most reports are one fact table joined to a few dimensions and aggregated. Dimensions are usually denormalized so analysts need fewer joins.

SELECT d.plan,
       COUNT(*)      AS orders,
       SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND d.is_current
GROUP BY d.plan
ORDER BY revenue DESC;

Result (2 rows):

plan	orders	revenue
pro	5	365.00
free	2	60.00

This sample warehouse has a fact_orders table and a dim_customer table, both defined in the setup script. Order 108 is missing from the result because its customer is not in the dimension. We come back to that in question 18.

Loading data: deduplication, incremental loads and upserts

4. How do you remove duplicates and keep only the latest row per key?

The standard answer is ROW_NUMBER() partitioned by the business key and ordered by the timestamp, newest first, then keep row number 1. It works in every modern warehouse and is the pattern you will write most often in a pipeline. Ties (like the two identical rows for customer 3) are broken arbitrarily unless you add a tiebreaker column to the ORDER BY.

SELECT customer_id, email, plan, updated_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM raw_customers
) t
WHERE rn = 1
ORDER BY customer_id;

Result (4 rows):

customer_id	email	plan	updated_at
1	[email protected]	pro	2025-02-14 12:30:00
2	[email protected]	free	2025-01-20 08:15:00
3	[email protected]	pro	2025-01-22 17:45:00
4	[email protected]	team	2025-03-18 11:20:00

PostgreSQL also has SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, updated_at DESC, and BigQuery and Snowflake have QUALIFY, which lets you filter on the window function without a subquery. For the building block, see how to use DENSE_RANK and ranking rows with RANK.

5. How do you delete duplicate rows from a table that has no primary key?

Number the rows inside each duplicate group and delete everything except the first. PostgreSQL gives every row a physical address, ctid, which you can use as a stand-in key. Other databases need a unique column or a ROW_NUMBER() subquery over the primary key.

DELETE FROM raw_customers
WHERE ctid IN (
  SELECT ctid
  FROM (
    SELECT ctid,
           ROW_NUMBER() OVER (
             PARTITION BY customer_id, email, plan, updated_at
           ) AS rn
    FROM raw_customers
  ) t
  WHERE rn > 1
);
SELECT COUNT(*) AS rows_left FROM raw_customers;

Result (1 row):

rows_left
6

Seven rows became six: only the exact duplicate for customer 3 was removed, while the two versions of customer 1 survived because their plan and updated_at differ. Always run the SELECT version first to see what you are about to delete, and do it inside a transaction.

6. How do you design an incremental load?

A full reload copies the whole source every run, which gets slow and expensive. An incremental load copies only what changed since last time. The usual approach is a watermark: store the highest updated_at (or id) you have already loaded, and select rows beyond it on the next run. The weaknesses to mention in an interview: it misses hard deletes, it needs a reliable updated_at column, and late-arriving rows can slip behind the watermark, so many pipelines re-read a small overlap window (say, the last 2 days) and rely on idempotent upserts to absorb the repeats.

SELECT order_id, customer_id, amount, updated_at
FROM stg_orders
WHERE updated_at > (SELECT MAX(loaded_at) FROM fact_orders)
ORDER BY order_id;

Result (3 rows):

order_id	customer_id	amount	updated_at
106	2	45.00	2025-02-22 09:00:00
109	4	80.00	2025-02-22 15:00:00
110	2	30.00	2025-02-23 16:30:00

Here the watermark is the newest loaded_at in the target, 2025-02-21 02:00. Three staging rows are newer: order 106 (a correction to an existing order) and two new orders.

7. What does it mean for a pipeline to be idempotent, and how do you get it with SQL?

An idempotent job can be run twice, or retried after a failure, and leave the data in the same state as running it once. Plain INSERT is not idempotent: a retry duplicates rows. The fix is an upsert, which inserts new keys and updates existing ones. In PostgreSQL that is INSERT ... ON CONFLICT (MySQL has ON DUPLICATE KEY UPDATE). Other idempotent patterns are DELETE then INSERT for a date partition inside one transaction, and overwriting a partition.

INSERT INTO fact_orders (order_id, customer_id, order_date, amount, loaded_at)
SELECT order_id, customer_id, order_date, amount, TIMESTAMP '2025-02-25 02:00'
FROM stg_orders
ON CONFLICT (order_id) DO UPDATE
  SET amount    = EXCLUDED.amount,
      loaded_at = EXCLUDED.loaded_at;

SELECT order_id, amount, loaded_at
FROM fact_orders
WHERE order_id >= 106
ORDER BY order_id;

Result (5 rows):

order_id	amount	loaded_at
106	45.00	2025-02-25 02:00:00
107	55.00	2025-02-08 02:00:00
108	70.00	2025-02-09 02:00:00
109	80.00	2025-02-25 02:00:00
110	30.00	2025-02-25 02:00:00

Order 106 was updated from 35.00 to 45.00, orders 109 and 110 were inserted, and running the statement a second time changes nothing more. ON CONFLICT needs a unique constraint or primary key on order_id.

8. What is MERGE, and when would you use it instead of an upsert?

MERGE is the SQL-standard way to combine insert, update and delete in one statement by joining a source to a target. It is supported by SQL Server, Oracle, Snowflake, BigQuery, Databricks and PostgreSQL 15 and later (MySQL does not have it). Prefer it when you need conditional logic, such as updating only if something actually changed, or deleting rows flagged as removed in the source.

MERGE INTO fact_orders t
USING stg_orders s
  ON t.order_id = s.order_id
WHEN MATCHED AND t.amount <> s.amount THEN
  UPDATE SET amount = s.amount, loaded_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, order_date, amount, loaded_at)
  VALUES (s.order_id, s.customer_id, s.order_date, s.amount, s.updated_at);

SELECT order_id, amount, loaded_at
FROM fact_orders
WHERE order_id >= 106
ORDER BY order_id;

Result (5 rows):

order_id	amount	loaded_at
106	45.00	2025-02-22 09:00:00
107	55.00	2025-02-08 02:00:00
108	70.00	2025-02-09 02:00:00
109	80.00	2025-02-22 15:00:00
110	30.00	2025-02-23 16:30:00

The AND t.amount <> s.amount condition skips rows that have not changed, which avoids pointless writes. Note that MERGE can fail or behave unexpectedly if several source rows match the same target row, so deduplicate the source first (question 4).

Slowly changing dimensions and joins

9. What is a slowly changing dimension (SCD)? Explain Type 1, 2 and 3.

Dimension attributes change over time: a customer moves country or upgrades their plan. An SCD strategy decides what happens to history.

Type 1: overwrite the old value. Simple, but history is lost.
Type 2: add a new row for each change, with valid_from, valid_to and an is_current flag. Full history, and the most common answer in interviews.
Type 3: keep the previous value in an extra column (previous_plan). Only one step of history.

With Type 2, a customer has several rows, so a fact table should join on the row that was valid when the event happened, not simply the current one.

10. How do you join a fact table to a Type 2 dimension "as of" the event date?

Join on the business key and put the date range in the join condition. Use a half-open interval (valid_from <= date AND date < valid_to) so a date on the boundary matches exactly one row. Use a LEFT JOIN so facts without a matching dimension row are kept and visible rather than silently dropped.

SELECT f.order_id, f.customer_id, f.order_date, d.plan AS plan_at_order
FROM fact_orders f
LEFT JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.valid_from
 AND f.order_date <  d.valid_to
ORDER BY f.order_id;

Result (8 rows):

order_id	customer_id	order_date	plan_at_order
101	1	2025-01-12	free
102	1	2025-02-20	pro
103	2	2025-01-25	free
104	3	2025-02-02	pro
105	3	2025-02-03	pro
106	2	2025-02-05	free
107	1	2025-02-07	free
108	9	2025-02-08	NULL

Order 101 happened while customer 1 was on the free plan, and order 102 after they upgraded to pro. Order 108 has no match at all, which is the kind of problem a data engineer has to notice (see question 18).

11. How do you apply a change to a Type 2 dimension?

Two steps in one transaction: close the current row by setting its valid_to and is_current = false, then insert a new current row. If the transaction is not atomic, a failure between the two steps leaves the customer with no current row. In production you only do this when a tracked attribute really changed, and you usually write it as one MERGE or as a dbt snapshot.

UPDATE dim_customer
SET valid_to = '2025-03-01', is_current = false
WHERE customer_id = 2 AND is_current;

INSERT INTO dim_customer (customer_id, plan, valid_from, valid_to, is_current)
VALUES (2, 'pro', '2025-03-01', '9999-12-31', true);

SELECT customer_id, plan, valid_from, valid_to, is_current
FROM dim_customer
WHERE customer_id = 2
ORDER BY valid_from;

Result (2 rows):

customer_id	plan	valid_from	valid_to	is_current
2	free	2025-01-20	2025-03-01	f
2	pro	2025-03-01	9999-12-31	t

12. Your join made the totals too big. What happened?

This is a fan-out (join multiplication). Joining a table to a second table that has several rows per key repeats the first table's rows, so a SUM counts them more than once. Order 101 has two refunds, so joining orders to refunds duplicates its 40.00 amount. The fix is to aggregate the many-side table to one row per key before joining.

-- Wrong: order 101 appears twice
SELECT SUM(f.amount) AS revenue_wrong
FROM fact_orders f
LEFT JOIN refunds r ON r.order_id = f.order_id;

Result (1 row):

revenue_wrong
535.00

That returns 535.00 but the real total is 495.00. Aggregate first:

SELECT SUM(f.amount) AS revenue,
       SUM(COALESCE(r.refunded, 0)) AS refunded
FROM fact_orders f
LEFT JOIN (
  SELECT order_id, SUM(amount) AS refunded
  FROM refunds
  GROUP BY order_id
) r ON r.order_id = f.order_id;

Result:

revenue	refunded
495.00	135.00

A quick guard is to compare COUNT(*) before and after the join: if it went up when you did not expect it to, you have a fan-out.

Transformation and analytics queries

13. How do you turn raw click events into sessions?

Define a session as a run of events by one user where no gap exceeds 30 minutes. Use LAG to get the previous event time, flag a new session when the gap is over 30 minutes (or there is no previous event), then take a running SUM of the flags to number the sessions.

WITH flagged AS (
  SELECT user_id, event_time, page,
         CASE
           WHEN event_time - LAG(event_time) OVER (
                  PARTITION BY user_id ORDER BY event_time
                ) > INTERVAL '30 minutes'
             OR LAG(event_time) OVER (
                  PARTITION BY user_id ORDER BY event_time
                ) IS NULL
           THEN 1 ELSE 0
         END AS is_new
  FROM page_events
),
numbered AS (
  SELECT *, SUM(is_new) OVER (
              PARTITION BY user_id ORDER BY event_time
            ) AS session_no
  FROM flagged
)
SELECT user_id, session_no,
       MIN(event_time) AS started,
       COUNT(*)        AS events
FROM numbered
GROUP BY user_id, session_no
ORDER BY user_id, session_no;

Result (4 rows):

user_id	session_no	started	events
1	1	2025-03-01 09:00:00	3
1	2	2025-03-01 14:00:00	2
2	1	2025-03-01 10:00:00	1
2	2	2025-03-01 10:40:00	1

User 1 has two sessions (morning and afternoon) and user 2 has two as well, because their pricing page view came 40 minutes after the first event. This is the same technique as "gaps and islands". See how to use LAG and LEAD if the window functions are new to you.

14. How do you fill in missing dates so a report has no gaps?

Group-by queries only return days that have rows. To show a zero for the empty days, build a date spine with generate_series (PostgreSQL), a recursive CTE, or a calendar table, then LEFT JOIN your data onto it and use COALESCE for the zeros. Most warehouses keep a permanent dim_date table for exactly this reason.

SELECT d::date AS day,
       COALESCE(s.signups, 0) AS signups
FROM generate_series(DATE '2025-03-01', DATE '2025-03-07', INTERVAL '1 day') AS d
LEFT JOIN daily_signups s ON s.day = d::date
ORDER BY day;

Result (7 rows):

day	signups
2025-03-01	12
2025-03-02	9
2025-03-03	15
2025-03-04	0
2025-03-05	20
2025-03-06	11
2025-03-07	18

March 4 had no row in daily_signups, and now appears with 0. Without the spine, a chart would silently skip that day. The lesson on COALESCE covers the zero-filling part.

15. How do you calculate a rolling 3-day average?

Use an aggregate window function with an explicit frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current row and the two before it. Build on the date spine from the previous question: a ROWS frame counts rows, so if days are missing the average silently covers a wider time range. Use RANGE with an interval, or a spine, when the calendar matters.

WITH spine AS (
  SELECT d::date AS day
  FROM generate_series(DATE '2025-03-01', DATE '2025-03-07', INTERVAL '1 day') d
),
daily AS (
  SELECT sp.day, COALESCE(s.signups, 0) AS signups
  FROM spine sp
  LEFT JOIN daily_signups s ON s.day = sp.day
)
SELECT day, signups,
       ROUND(AVG(signups) OVER (
         ORDER BY day
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_3_day
FROM daily
ORDER BY day;

Result (7 rows):

day	signups	avg_3_day
2025-03-01	12	12.0
2025-03-02	9	10.5
2025-03-03	15	12.0
2025-03-04	0	8.0
2025-03-05	20	11.7
2025-03-06	11	10.3
2025-03-07	18	16.3

The first two rows average fewer than three days because there is not enough history yet. Say so in an interview, and mention whether the business wants those partial values shown or hidden.

16. How do you pivot rows into columns?

Conditional aggregation works in every database: wrap a CASE expression (or PostgreSQL's FILTER clause) inside SUM or COUNT, one per output column. Native PIVOT keywords exist in SQL Server, Oracle and Snowflake, but they need the column values hard-coded, so conditional aggregation is just as flexible. More in how to pivot in SQL.

SELECT customer_id,
       SUM(amount) FILTER (WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01') AS jan,
       SUM(amount) FILTER (WHERE order_date >= '2025-02-01' AND order_date < '2025-03-01') AS feb
FROM fact_orders
GROUP BY customer_id
ORDER BY customer_id;

Result (4 rows):

customer_id	jan	feb
1	40.00	145.00
2	25.00	35.00
3	NULL	180.00
9	NULL	70.00

A customer with no January orders shows NULL, not zero. Wrap the sum in COALESCE(..., 0) if the report needs zeros. The portable version is SUM(CASE WHEN ... THEN amount END).

17. How do you query semi-structured JSON events?

Data engineers constantly land JSON from APIs and event trackers. PostgreSQL stores it as jsonb and gives you -> (returns JSON), ->> (returns text) and jsonb_array_elements to turn an array into rows. Snowflake uses FLATTEN, BigQuery uses UNNEST, and SQL Server uses OPENJSON. Cast the extracted text to the right type, and decide what to do with missing keys, which come back as NULL.

SELECT e.id,
       e.payload -> 'user' ->> 'id'            AS user_id,
       COALESCE(e.payload -> 'user' ->> 'country', 'unknown') AS country,
       i.item ->> 'sku'                        AS sku,
       (i.item ->> 'qty')::int                 AS qty,
       p.category
FROM raw_json_events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS i(item)
LEFT JOIN products p ON p.sku = i.item ->> 'sku'
ORDER BY e.id, sku;

Result (3 rows):

id	user_id	country	sku	qty	category
1	1	US	A1	2	books
1	1	US	B7	1	games
3	3	unknown	A1	1	books

Event 2 has an empty items array, so it produces no rows with a cross join. Use LEFT JOIN LATERAL ... ON true if you need to keep it. Event 3 is missing the country key, so COALESCE supplies "unknown". See how to query a JSON column in PostgreSQL for more.

Data quality and validation

18. How do you find orphaned rows (facts with no matching dimension)?

Use an anti-join: LEFT JOIN and keep rows where the right side is NULL, or NOT EXISTS. Orphans appear when a fact arrives before its dimension row, or when an upstream record was deleted. Real pipelines either quarantine them, create an "unknown" placeholder dimension row, or fail the load, depending on how strict the business needs to be.

SELECT f.order_id, f.customer_id, f.amount
FROM fact_orders f
WHERE NOT EXISTS (
  SELECT 1 FROM dim_customer d
  WHERE d.customer_id = f.customer_id
);

Result (1 row):

order_id	customer_id	amount
108	9	70.00

Order 108 belongs to customer 9, who does not exist in the dimension. A foreign key would have prevented this, but warehouses often skip enforced constraints for load speed, so you check with a query instead (see the benefit of foreign keys).

19. What data quality checks would you run on a new table?

A good answer lists a few categories: completeness (null counts, row counts versus the source), uniqueness (duplicate primary keys), validity (values in range, allowed categories, valid dates), referential integrity (orphans) and freshness (newest timestamp is recent). Most checks fit in one profiling query.

SELECT COUNT(*)                                   AS total_rows,
       COUNT(DISTINCT customer_id)                AS distinct_keys,
       COUNT(*) - COUNT(DISTINCT customer_id)     AS duplicate_rows,
       COUNT(*) FILTER (WHERE email IS NULL)      AS null_emails,
       MAX(updated_at)                            AS newest_row
FROM raw_customers;

Result (1 row):

total_rows	distinct_keys	duplicate_rows	null_emails	newest_row
7	4	3	0	2025-03-18 11:20:00

Seven rows but only four distinct customers means duplicates exist. Tools such as dbt tests and Great Expectations run checks like these automatically on every load. Null handling details are in how to use NULL values in SQL.

20. Why can NOT IN return no rows when you expect some?

If the subquery returns even one NULL, x NOT IN (...) evaluates to unknown for every row, and WHERE discards unknowns. It is a classic data quality trap, because a single bad row upstream makes a query silently return nothing. NOT EXISTS and LEFT JOIN ... IS NULL do not have this problem.

-- NOT IN: the subquery contains a NULL
SELECT customer_id FROM (SELECT DISTINCT customer_id FROM raw_customers) c
WHERE customer_id NOT IN (
  SELECT customer_id FROM dim_customer
  UNION ALL SELECT NULL
);

Result (0 rows):

customer_id

Zero rows, even though customer 4 is in the raw data but not in the dimension. The safe version:

SELECT customer_id
FROM (SELECT DISTINCT customer_id FROM raw_customers) c
WHERE NOT EXISTS (
  SELECT 1 FROM dim_customer d WHERE d.customer_id = c.customer_id
);

Result:

customer_id
4

21. How do you reconcile a source table against a target after a load?

Compare counts and sums first, then find the specific rows that differ with a FULL OUTER JOIN on the key: rows with a key on only one side are missing or extra, and rows with both keys but different values were changed. Reconciliation queries like this are how you prove a migration or pipeline is correct.

SELECT COALESCE(s.order_id, t.order_id) AS order_id,
       s.amount AS source_amount,
       t.amount AS target_amount,
       CASE
         WHEN t.order_id IS NULL THEN 'missing in target'
         WHEN s.order_id IS NULL THEN 'extra in target'
         ELSE 'value mismatch'
       END AS issue
FROM stg_orders s
FULL OUTER JOIN fact_orders t ON t.order_id = s.order_id
WHERE t.order_id IS NULL
   OR s.order_id IS NULL
   OR s.amount IS DISTINCT FROM t.amount
ORDER BY order_id;

Result (10 rows):

order_id	source_amount	target_amount	issue
101	NULL	40.00	extra in target
102	NULL	90.00	extra in target
103	NULL	25.00	extra in target
104	NULL	120.00	extra in target
105	NULL	60.00	extra in target
106	45.00	35.00	value mismatch
107	NULL	55.00	extra in target
108	NULL	70.00	extra in target
109	80.00	NULL	missing in target
110	30.00	NULL	missing in target

This listing is limited to the changes that matter: staging rows 109 and 110 have not been loaded, order 106 has a different amount, and all of the other target rows (101-105, 107, 108) show up as "extra" because they are not in this staging batch. In a real check you would restrict both sides to the same date range first. Note IS DISTINCT FROM, which compares NULLs safely, unlike <>.

Performance, partitioning and design

22. What is table partitioning and why does it matter for data engineering?

Partitioning splits one large table into smaller physical pieces, usually by date. Queries that filter on the partition key only read the relevant pieces (partition pruning), and old data can be dropped or archived by detaching a partition instead of running a slow DELETE. It also makes loads idempotent: reload one day by replacing one partition. Snowflake and BigQuery partition or cluster automatically or by declaration; in PostgreSQL you declare it.

CREATE TABLE events (id INT, created DATE, kind TEXT) PARTITION BY RANGE (created);
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE created = '2025-02-14';

Plan:

Seq Scan on events_2025_02 events
  Filter: (created = '2025-02-14'::date)

The plan scans only events_2025_02; January is never touched. If the query had no filter on created, or wrapped it in a function, every partition would be scanned.

23. When should you add an index, and do data warehouses use them?

In a row-store database such as PostgreSQL or MySQL, index the columns you filter, join and sort on, because it lets the database find rows without scanning the table. Every index slows writes and takes space, so bulk loads often drop indexes, load, then rebuild them. Cloud warehouses like Snowflake, BigQuery and Redshift mostly do not use traditional indexes: they store data in columns and rely on partitioning, clustering or sort keys and pruning instead. Our guides to creating an index in SQL and in PostgreSQL cover the syntax.

24. How do you troubleshoot a slow query?

Read the plan before changing anything. In PostgreSQL run EXPLAIN (ANALYZE, BUFFERS), compare estimated and actual row counts, and look for sequential scans on big tables, joins that multiply rows, sorts that spill to disk and filters applied late. Typical fixes: select fewer columns and rows early, filter on the partition key, replace a correlated subquery with a join or window function, add or fix an index, refresh statistics with ANALYZE, and pre-aggregate heavy joins into an intermediate table. Our guide on improving SQL query performance goes through each of these.

25. CTE, temp table, view or materialized view: how do you choose?

A CTE names a step inside one query for readability (using several CTEs). A view saves a query under a name but recomputes it each time. A materialized view stores the result and must be refreshed, so it trades freshness for speed. A temp table stores an intermediate result for the session, which helps when you reuse an expensive step several times or want to index it. In a transformation pipeline you typically use CTEs inside models, views for light layers and materialized tables for expensive outputs.

26. How do you make a full reload safe, so consumers never see a half-empty table?

Do the whole replacement in one transaction, or build into a new table and swap names. PostgreSQL supports transactional DDL, so even TRUNCATE can be rolled back; many warehouses offer atomic swaps (ALTER TABLE ... SWAP WITH in Snowflake, CREATE OR REPLACE TABLE).

BEGIN;
TRUNCATE fact_orders_new;
INSERT INTO fact_orders_new SELECT * FROM staging_clean;
COMMIT;   -- readers see the old data until this moment, then the new data

If the insert fails, the transaction rolls back and the old data is still there. The same idea applies to rebuilding a single partition. This block is a pattern to adapt, so it does not show output.

27. How do you handle late-arriving data and backfills?

Late data is when a record for an old date shows up after you already loaded that date. Three habits cover most cases: track both event time and load time, so you can tell when something arrived; reprocess a rolling window (for example the last 3 days) on every run so late rows are picked up; and make every load idempotent so rerunning is harmless. A backfill is the same logic run over a historical date range, which is why pipelines take a date parameter instead of using CURRENT_DATE inside the SQL.

How to answer data engineering SQL questions

Ask about the grain. "What does one row in this table represent?" Most pipeline bugs are duplicated or missing rows caused by a wrong assumption about grain.
Make it re-runnable. Whenever you describe a load, say how a retry behaves. Idempotency is the single most valued property in these interviews.
Mention failure and edge cases. Late data, hard deletes, NULL keys, duplicate keys and schema changes. Naming them unprompted separates experienced candidates.
Validate your own output. Say how you would check it: row counts before and after a join, totals reconciled to the source.
Name your dialect and the alternative. "In Snowflake I would use QUALIFY, in PostgreSQL DISTINCT ON" shows you know the tools differ.
Explain cost. For large tables, talk about partition pruning, selecting only needed columns and avoiding repeated full scans.

Where to go next

Brush up on fundamentals with the SQL cheat sheet and 38 SQL query examples.
Practice with 28 SQL practice problems with solutions.
Revisit the basics interactively: GROUP BY, joins and LEFT JOIN.
Interviewing for a specific database? See PostgreSQL, MySQL and SQL Server interview questions.

FAQ

What SQL topics are asked in data engineer interviews?

Joins and aggregation at scale, window functions (especially ROW_NUMBER, LAG and running totals), deduplication, incremental loads and upserts, slowly changing dimensions, date handling and gap filling, JSON parsing, data quality checks, and performance topics such as partitioning and reading query plans.

How is a data engineer SQL interview different from a data analyst one?

Analyst interviews focus on answering business questions with queries. Data engineer interviews focus on building reliable pipelines: how data is loaded, deduplicated, versioned and validated, plus how queries behave on large tables. You still need strong joins and window functions, but you are also asked about idempotency, schema design and failure handling.

Do data engineers need to know advanced SQL?

Yes. Window functions, CTEs, MERGE or upserts and execution plans come up daily. You do not need obscure features, but you should write correct, readable SQL for messy data.

Which SQL dialect should I practice for data engineering interviews?

Ask the recruiter which warehouse the team uses (Snowflake, BigQuery, Redshift, Databricks or PostgreSQL). The concepts transfer, and the syntax differences are small: QUALIFY, UNNEST versus FLATTEN, and MERGE details. Practicing in PostgreSQL is a safe default because it supports almost everything.

Where can I practice SQL for free?

Use SQL Easy's free interactive lessons, which run in your browser with instant feedback, then work through the practice problems.