Data engineering interview questions on ETL, idempotency, partitions

Round plan

Concepts (minutes 0–10)

Q1. ETL versus ELT?

ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms inside the warehouse with SQL. ELT is common now because warehouses are good at large transformations and raw data is kept for reprocessing. ETL still makes sense when data must be cleaned or masked before it lands.

Listening for: a reason for each, not a definition only.

Q2. What does idempotent mean for a pipeline, and why does it matter?

Running the same job for the same input period more than once leaves the target in the same state. Jobs fail and are retried; schedulers occasionally run twice. A non-idempotent append after a retry doubles the day's revenue.

Q3. Full refresh, append or upsert — when is each used?

Full refresh replaces the table: simple and idempotent, fine for small tables. Append adds rows: suits immutable events but needs deduplication or partition overwrite to be rerunnable. Upsert inserts new keys and updates existing ones: suits records that change, such as order status.

Q4. What is partitioning, and how do you choose a key?

Splitting a table's storage by a column — commonly event date — so queries and rewrites touch only relevant partitions. Choose a column that queries filter on and that gives partitions of reasonable size. A high-cardinality key such as customer ID produces huge numbers of tiny partitions.

Hands-on 1 (minutes 10–25)

Q5. "This daily job appends yesterday's orders. It ran twice on 2 March. Show the problem and make the load idempotent."

CREATE TABLE staging_orders (order_id TEXT, order_date TEXT, amount INTEGER, status TEXT);
CREATE TABLE fact_orders (order_id TEXT, order_date TEXT, amount INTEGER, status TEXT);

INSERT INTO staging_orders VALUES
 ('A1','2026-03-02',500,'placed'),('A2','2026-03-02',1200,'placed'),('A3','2026-03-02',300,'placed');

-- Naive append, run twice by a retry
INSERT INTO fact_orders SELECT * FROM staging_orders;
INSERT INTO fact_orders SELECT * FROM staging_orders;
SELECT 'after double append' AS state, COUNT(*) AS rows_loaded, SUM(amount) AS revenue FROM fact_orders;

-- Idempotent version: overwrite the partition for the run date inside a transaction
DELETE FROM fact_orders;   -- reset the table for the demonstration
SAVEPOINT load_day;
DELETE FROM fact_orders WHERE order_date = '2026-03-02';
INSERT INTO fact_orders SELECT * FROM staging_orders WHERE order_date = '2026-03-02';
RELEASE load_day;
SAVEPOINT load_day;
DELETE FROM fact_orders WHERE order_date = '2026-03-02';
INSERT INTO fact_orders SELECT * FROM staging_orders WHERE order_date = '2026-03-02';
RELEASE load_day;
SELECT 'after two partition overwrites' AS state, COUNT(*) AS rows_loaded, SUM(amount) AS revenue FROM fact_orders;

The double append shows 6 rows and revenue 4,000; the partition overwrite, run twice, shows 3 rows and 2,000. Say why the transaction matters: without it, a failure between DELETE and INSERT leaves the day empty. (A savepoint is used so the block runs inside any client; BEGIN … COMMIT works equally well in the SQLite shell.)

Hands-on 2 (minutes 25–35)

Q6. "Orders change status after they are placed, and some updates arrive days late. Load changes incrementally with a watermark and upsert."

CREATE TABLE src_orders (order_id TEXT PRIMARY KEY, status TEXT, amount INTEGER, updated_at TEXT);
CREATE TABLE dim_orders (order_id TEXT PRIMARY KEY, status TEXT, amount INTEGER, updated_at TEXT);
CREATE TABLE load_state (job TEXT PRIMARY KEY, watermark TEXT);
INSERT INTO load_state VALUES ('orders', '2026-03-01 00:00:00');

INSERT INTO src_orders VALUES
 ('A1','placed',500,'2026-03-01 10:00:00'),
 ('A2','placed',1200,'2026-03-01 11:00:00');

-- Run 1: rows updated after the watermark, minus a 1-day lookback for late updates
INSERT INTO dim_orders (order_id, status, amount, updated_at)
SELECT order_id, status, amount, updated_at FROM src_orders
WHERE updated_at > datetime((SELECT watermark FROM load_state WHERE job='orders'), '-1 day')
ON CONFLICT(order_id) DO UPDATE SET
  status = excluded.status, amount = excluded.amount, updated_at = excluded.updated_at
WHERE excluded.updated_at > dim_orders.updated_at;
UPDATE load_state SET watermark = (SELECT MAX(updated_at) FROM dim_orders) WHERE job='orders';

-- New source changes: A1 delivered, A3 new
UPDATE src_orders SET status='delivered', updated_at='2026-03-02 09:00:00' WHERE order_id='A1';
INSERT INTO src_orders VALUES ('A3','placed',300,'2026-03-02 08:00:00');

-- Run 2, then an accidental rerun of run 2
INSERT INTO dim_orders (order_id, status, amount, updated_at)
SELECT order_id, status, amount, updated_at FROM src_orders
WHERE updated_at > datetime((SELECT watermark FROM load_state WHERE job='orders'), '-1 day')
ON CONFLICT(order_id) DO UPDATE SET
  status = excluded.status, amount = excluded.amount, updated_at = excluded.updated_at
WHERE excluded.updated_at > dim_orders.updated_at;
INSERT INTO dim_orders (order_id, status, amount, updated_at)
SELECT order_id, status, amount, updated_at FROM src_orders
WHERE updated_at > datetime((SELECT watermark FROM load_state WHERE job='orders'), '-1 day')
ON CONFLICT(order_id) DO UPDATE SET
  status = excluded.status, amount = excluded.amount, updated_at = excluded.updated_at
WHERE excluded.updated_at > dim_orders.updated_at;
UPDATE load_state SET watermark = (SELECT MAX(updated_at) FROM dim_orders) WHERE job='orders';

SELECT * FROM dim_orders ORDER BY order_id;
SELECT watermark FROM load_state;

The result is three rows — A1 delivered, A2 placed, A3 placed — with no duplicates after the rerun, and the watermark at 2026-03-02 09:00:00. Three points to say aloud: the lookback window re-reads recent rows so a late update just behind the watermark is not missed; the WHERE excluded.updated_at > dim_orders.updated_at guard stops an older update overwriting a newer one; and the watermark is advanced from the loaded data, only after the load succeeds. (ON CONFLICT … DO UPDATE requires SQLite 3.24 or later; warehouses use MERGE.)

Design discussion (minutes 35–40)

Q7. How do you backfill three months after fixing a transformation bug?

Run the same idempotent job per partition for each date in the range, in bounded batches, into the same table (partition overwrite) or into a new table swapped in when complete. Compare row counts and key totals before and after for a sample of dates, and tell downstream users the window during which numbers change.

Q8. Which data-quality checks run on every load?

Row count within an expected range versus recent days; no nulls in key columns; unique primary keys; totals reconciling with the source system for the same period; freshness of the latest timestamp. Fail the job, or quarantine the partition, rather than publishing bad data silently.

Q9. Where do duplicates usually come from, and where do you remove them?

Retries, at-least-once message delivery and overlapping extraction windows. Deduplicate at the first layer that has a reliable key — often staging, keeping the latest record per key — and keep the check downstream as a guard.

What distinguishes strong answers

Every answer returns to rerunnability: partitions overwritten in transactions, upserts with ordering guards, watermarks advanced last. For the time-zone and date logic that often sits inside these jobs, see SQL date and time interview questions. Pipelines built this way underpin the client integrations in FDE for Freshers; the cloud basics bank covers where such jobs run.