SQL Interview Questions for Data Engineers (2026)

Q1: Implement SCD Type 2 for a customer dimension

The question: "A customer's plan_tier can change over time. Design a table that preserves that history, and write the logic to apply an incoming change."

UPDATE dim_customer
SET effective_end_date = CURRENT_DATE - INTERVAL '1 day', is_current = FALSE
WHERE customer_id = 42 AND is_current = TRUE;

INSERT INTO dim_customer (customer_sk, customer_id, plan_tier, effective_start_date, is_current)
VALUES (NEXT VALUE FOR customer_sk_seq, 42, 'Pro', CURRENT_DATE, TRUE);

What the interviewer is checking: that the candidate knows the old row must be closed, not overwritten or deleted, and that the new row gets its own surrogate key — the mechanism that lets historical fact rows keep pointing at the plan tier that was actually true when the transaction happened. The full pattern, including the schema and a MERGE-based version, is in Slowly Changing Dimensions Type 2 in SQL.

Q2: How do you handle late-arriving data?

The question: "An order event arrives two days after its order date, after that day's revenue report has already run. How do you keep historical numbers correct?"

Answer: Two viable strategies, and a strong answer names the tradeoff between them rather than just one:

• Reprocess the affected period. Re-run the aggregation for the specific day the late row belongs to, overwriting that day's stored total. Correct, but requires the pipeline to support targeted backfills.
• Track a "as-of" load date separately from the event date. Keep both the true business date and the date the row was actually loaded, so reports can distinguish "what we knew as of report time" from "what actually happened," without needing to silently rewrite history.

-- Flag rows that arrived more than 1 day after their business date
SELECT order_id, order_date, loaded_at,
  loaded_at::DATE - order_date AS days_late
FROM raw_orders
WHERE loaded_at::DATE - order_date > 1;

Q3: Deduplicate rows with no unique ID

The question: "A source table has exact duplicate rows and no primary key. Remove them, keeping one copy of each."

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer_id, email, signup_date
    ORDER BY (SELECT NULL)
  ) AS rn
  FROM raw_customers
)
DELETE FROM ranked WHERE rn > 1;

Follow-up interviewers ask: "What if the columns you partition by aren't actually the full definition of a duplicate?" — the correct answer is that the PARTITION BY list must include every column that defines a duplicate, or rows that differ only in an omitted column get incorrectly merged. Full pattern and PostgreSQL/Oracle shortcuts in How to Remove Duplicates in SQL Without a Unique ID.

Q4: Validate data quality after a load

The question: "How would you confirm a nightly load actually succeeded and produced trustworthy data, beyond just checking it didn't error?"

-- 1. Row count sanity check against the source
SELECT (SELECT COUNT(*) FROM source.orders) AS source_count,
       (SELECT COUNT(*) FROM warehouse.fct_orders) AS loaded_count;

-- 2. Unexpected NULLs in required columns
SELECT COUNT(*) FROM fct_orders WHERE customer_sk IS NULL;

-- 3. Referential integrity: facts pointing to a dimension row that doesn't exist
SELECT COUNT(*) FROM fct_orders f
LEFT JOIN dim_customer d ON d.customer_sk = f.customer_sk
WHERE d.customer_sk IS NULL;

-- 4. Duplicate primary-key-equivalent rows in a fact table
SELECT order_id, COUNT(*) FROM fct_orders
GROUP BY order_id HAVING COUNT(*) > 1;

What separates a strong answer: naming multiple independent checks (volume, nullability, referential integrity, uniqueness) rather than just one, and noting that these checks should run automatically as part of the pipeline and fail loudly, not get discovered manually days later.

Q5: Fact table vs. dimension table, and surrogate keys

The question: "Explain the difference between a fact table and a dimension table, and why fact tables typically join on a surrogate key rather than a natural business key."

Answer: A fact table holds transactional, measurable events (an order, a page view, a payment) at a specific grain, with foreign keys pointing to dimensions. A dimension table holds descriptive context about the entities involved (a customer, a product, a date) that facts are analyzed by. Facts join on a surrogate key — an artificial, database-generated ID — rather than the natural business key (like customer_id) specifically because, under SCD Type 2, a single business key can correspond to multiple dimension rows over time; only the surrogate key uniquely identifies the one specific version that was true when the fact occurred.

Q6: Make a load idempotent

The question: "A pipeline step fails halfway through and gets automatically retried. How do you ensure re-running it doesn't create duplicate or incorrect data?"

MERGE INTO fct_orders AS tgt
USING staging_orders AS src
  ON tgt.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET
  amount = src.amount, status = src.status
WHEN NOT MATCHED THEN INSERT (order_id, customer_sk, amount, status)
VALUES (src.order_id, src.customer_sk, src.amount, src.status);

Why this answers the question: a plain INSERT ... SELECT re-run after a partial failure would insert every row a second time. MERGE (or an equivalent upsert) checks whether each row already exists and updates instead of duplicating — running it once or running it five times against the same source produces the identical end state, which is the definition of idempotent. Full pattern in SQL UPSERT Explained.