10 Data Engineering SQL Optimization Interview Questions and Answers

Short answer: You read a plan bottom-up and inside-out: the leaves are table access (Seq Scan / Index Scan), joins and aggregations sit above. Hunt for the most expensive node, full scans of large tables under a filter, and the chosen join method.

In depth:

1. Reading direction — from the innermost nodes (data access) up to the root (final result). In EXPLAIN ANALYZE compare the estimate (rows=) with reality (actual rows): a large gap exposes stale optimizer statistics.
2. Access method — Seq Scan (full scan) vs Index / Index Only Scan. A full scan of a big table under a selective filter is the first candidate for tuning.
3. Join method — Nested Loop, Hash Join, Merge Join (see table).
4. The expensive node — the one with the largest cost/time; that's where you focus.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-01-01';

-- Hash Join  (cost=...  rows=...)        <- join method
--   ->  Seq Scan on orders o            <- full scan: filter not on an index
--         Filter: (created_at >= ...)
--   ->  Hash
--         ->  Seq Scan on customers c

⚠️ Common mistake: looking only at the final cost and ignoring the rows vs actual rows gap — that gap is exactly what reveals stale statistics and a bad plan choice.

Short answer: The key is cutting how much data you read: partition pruning via a WHERE on the partition key, reading only the needed columns (no SELECT *), filtering early (predicate pushdown), and dropping needless DISTINCT/ORDER BY.

In depth:

1. Fewer rows — a filter on the partition column triggers partition pruning; predicates are pushed down to the scanner (predicate pushdown) so nothing extra is read from disk.
2. Fewer columns — a columnar store reads only the listed fields; SELECT * kills that advantage and inflates I/O.
3. Remove wasted work — DISTINCT and ORDER BY force a sort and a shuffle; often DISTINCT just masks a join fan-out, and an ORDER BY in a subquery is useless.
4. Early joins and pre-aggregation — filter and aggregate before the join when it shrinks the join's input.

-- Slow: full scan + all columns + needless DISTINCT + function on the key
SELECT DISTINCT *
FROM events e JOIN users u ON u.id = e.user_id
WHERE date_trunc('day', e.ts) = DATE '2026-06-01';

-- Fast: partition pruning, only needed columns, predicate on the raw column
SELECT e.user_id, e.event_type, u.country
FROM events e
JOIN users u ON u.id = e.user_id
WHERE e.event_date = DATE '2026-06-01';   -- partition key = event_date

⚠️ Common mistake: wrapping the partition column in a function (date_trunc, CAST) — it breaks partition pruning and the engine scans the whole table.

Short answer: Join so that intermediate results stay minimal: broadcast/hash a small table, sort-merge large ones, filter before the join, and watch for fan-out (row multiplication) and accidental cross joins.

In depth:

1. Join order — do the most selective joins and filters first so later joins operate on smaller inputs.
2. Broadcast (map-side) join — if one side is small, ship a copy to every node and join locally, with no shuffle of the big table.
3. Shuffle sort-merge / hash — when both sides are large: data is shuffled by key. Costlier because of the network reshuffle.
4. Fan-out — a join on a non-unique key multiplies rows (1→N) and inflates downstream aggregates; always check key cardinality.

-- Broadcast a small lookup dimension (Spark SQL)
SELECT /*+ BROADCAST(d) */ f.order_id, f.amount, d.name
FROM fact_sales f
JOIN dim_product d ON d.id = f.product_id;

-- Fan-out: if dim_product isn't unique on id, rows multiply,
-- and SUM(amount) overstates revenue. Dedup the dimension beforehand.

⚠️ Common mistake: forgetting the join condition (or making it always true) — you get a Cartesian product; and aggregating on top of a fan-out join, silently overstating sums.

Short answer: Partitioning lays a table out into directories/files by a column (usually a date). A WHERE on that column lets the engine read only the matching partitions (partition pruning), while predicate pushdown pushes the filter down to the scanner/file format, skipping unneeded blocks before they're even read.

In depth:

1. Partition pruning — the plan selects only partitions that satisfy the predicate; the rest of the directories/files are never opened.
2. Predicate pushdown — the filter is applied at the file level: using Parquet row-group min/max stats, non-matching row groups are skipped (data skipping).
3. The requirement — the predicate must sit on the raw partition column, with no wrapping functions.
4. Granularity — too-fine partitions (hourly over years) spawn millions of tiny files; too-coarse ones give weak pruning.

-- events is partitioned by event_date
SELECT count(*)
FROM events
WHERE event_date BETWEEN DATE '2026-06-01' AND DATE '2026-06-07';
-- reads 7 partitions out of thousands

-- Anti-pattern: a function on the key -> pruning doesn't fire
-- WHERE year(event_date) = 2026;   -- scans everything

⚠️ Common mistake: filtering on a derived expression of the partition key (CAST, date_trunc, year()) — pruning turns off and the engine reads the whole table.

Short answer: Two idiomatic approaches. MERGE (upsert) matches source to target on a key and updates or inserts rows; INSERT OVERWRITE PARTITION fully rewrites the affected partitions with recomputed data. Both are idempotent — a rerun yields the same result.

In depth:

1. MERGE / upsert — suits point changes and slowly changing dimensions (SCD); needs a reliable key and, for dedup, picking the latest version of a row.
2. INSERT OVERWRITE partition — simpler and more robust for large daily recomputes: drop and rewrite the whole partition, with no half-written state.
3. Idempotency — the basis for safe pipeline retries: a rerun doesn't spawn duplicates.
4. Choice — MERGE when the change fraction is small; OVERWRITE for a full recompute of a date window.

-- Upsert on the key
MERGE INTO dim_customer t
USING staging_customer s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET name = s.name, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, name, updated_at)
  VALUES (s.customer_id, s.name, s.updated_at);

-- Idempotent overwrite of one day's partition
INSERT OVERWRITE TABLE fact_daily PARTITION (dt = DATE '2026-06-30')
SELECT user_id, sum(amount) AS revenue
FROM raw_events
WHERE event_date = DATE '2026-06-30'
GROUP BY user_id;

⚠️ Common mistake: doing increments via plain INSERT INTO with no key/overwrite — a retry duplicates rows; or MERGE from a non-deduplicated source (several rows per key) updates unpredictably — collapse the source to one row per key first (ROW_NUMBER).

Short answer: Exact COUNT(DISTINCT) must hold every unique value and do a global dedup with a shuffle — heavy on memory and it doesn't scale to billions of rows. Approximate counting via HyperLogLog (APPROX_COUNT_DISTINCT) gives ~1–2% error with fixed, tiny memory.

In depth:

1. Cost of exact distinct — data is shuffled by value to count uniques globally; at high cardinality this is expensive and often hits memory limits.
2. HyperLogLog — a probabilistic structure: constant memory (kilobytes) for any volume, error around 2%. Ideal for "unique users" dashboards.
3. Pre-aggregation — compute metrics into a daily intermediate table and aggregate on top; HLL sketches are also mergeable across periods with no recompute from scratch.
4. When accuracy is mandatory — billing, finance, audit: exact COUNT(DISTINCT) only.

-- Expensive: exact global dedup
SELECT count(DISTINCT user_id)
FROM events WHERE event_date = DATE '2026-06-30';

-- Cheap and scalable: HyperLogLog, ~2% error
SELECT approx_count_distinct(user_id) AS uniq_users
FROM events
WHERE event_date = DATE '2026-06-30';

⚠️ Common mistake: running exact COUNT(DISTINCT) over billions of rows for a dashboard where 2% error doesn't matter — the query OOMs or drags on for minutes.

Short answer: OLTP serves point queries (find/update a few rows), where a B-tree index gives a fast seek. Analytical columnar warehouses scan millions of rows over a few columns, so point indexes are useless — data is physically ordered by partitioning and clustering, and zone maps (per-block min/max) skip unneeded blocks during the scan.

In depth:

1. OLTP — selective key lookups; a B-tree speeds up the seek and enforces uniqueness / foreign keys.
2. Warehouse — queries read whole columns over large ranges; scanning less beats "hopping" through an index for every row.
3. Warehouse mechanisms — partitioning (pruning), clustering/sorting of data, and zone maps / per-block min-max stats to skip blocks (data skipping).

⚠️ Common mistake: trying to "add an index" in a columnar warehouse to fix a slow analytical query — the right lever is the partition/clustering key, not a secondary B-tree index.

Short answer: ROW_NUMBER() OVER (PARTITION BY key ORDER BY version) numbers rows within a group; a rn = 1 filter keeps one — the freshest. The window does this in a single pass with a sort, whereas the self-join + aggregate equivalent needs an extra join and often a fan-out.

In depth:

1. Dedup — PARTITION BY on the business key, ORDER BY time descending; keep rn = 1 (latest version).
2. Running aggregates — running sum/avg via a window frame, no self-join.
3. Cost of a window — needs a shuffle by PARTITION BY and a sort within the partition; the heavy parts are the sort and any key skew.
4. Window vs join — replacing a correlated subquery or self-join with a window removes re-reading the table and the risk of row multiplication.

-- Keep the latest record per user
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY updated_at DESC) AS rn
  FROM staging_users
)
SELECT * FROM ranked WHERE rn = 1;

⚠️ Common mistake: putting the rn = 1 filter in the WHERE of the same SELECT that computes ROW_NUMBER — window functions can't be filtered in WHERE; use a subquery/CTE, or QUALIFY where it's supported.

Short answer: Skew is when a few keys hold a disproportionate number of rows, so when shuffling by key one reducer node gets a giant partition while the rest sit idle. You fix it with salting: append a random suffix to the hot key, splitting it into N sub-keys, then re-aggregate.

In depth:

1. Symptom — a join or group-by where 99% of tasks finish and one "hangs" — a classic sign of skew.
2. Salting — split the hot key into key || '_' || rand(0..N), spreading the load across N tasks; a final stage sums the partial results.
3. Separate handling of hot keys — known heavy keys (NULL, "guest", a top customer) are joined or aggregated separately, the rest goes the normal path.
4. Broadcast — if the skewed join side can be broadcast whole, the shuffle and the skew vanish.

-- Salting a hot key during aggregation
SELECT user_id, sum(cnt) AS total
FROM (
  SELECT user_id,
         concat(user_id, '_', cast(floor(rand() * 16) AS int)) AS salted,
         count(*) AS cnt
  FROM events
  GROUP BY user_id, salted        -- load spread across 16 groups
) t
GROUP BY user_id;                  -- re-aggregate the partial sums

⚠️ Common mistake: chasing a "slow query" overall when a single task hangs on one key (often NULL in the join key) — look at the key distribution first, don't just throw more cluster memory at it.

Short answer: The cost-based optimizer (CBO) enumerates plan alternatives and picks the cheapest by estimate, relying on table statistics: row counts, column cardinality, histograms, size. If stats are stale, the row estimates are wrong — the optimizer picks the wrong join method or order and the plan degrades. That's why you run ANALYZE after big loads.

In depth:

1. What the CBO estimates — predicate selectivity and the cardinality of intermediate results from the collected statistics.
2. Why the plan is bad — underestimated rows → chose a Nested Loop or broadcast instead of a hash join; overestimated → a needless shuffle and sort.
3. The tell — in EXPLAIN ANALYZE the estimate rows= diverges sharply from actual rows.
4. The fix — regular ANALYZE / stats collection, especially after bulk inserts or partition overwrites; hints are a last resort, not a substitute for statistics.

-- Refresh statistics after a load
ANALYZE events;                     -- PostgreSQL
-- ANALYZE TABLE events COMPUTE STATISTICS FOR ALL COLUMNS;  -- Spark SQL

-- Diagnosis: estimate vs reality
EXPLAIN ANALYZE SELECT ... ;
-- rows=1000  (actual rows=2500000)   <- statistics are stale

⚠️ Common mistake: patching the symptom with hints instead of refreshing statistics; or forgetting to recompute stats after INSERT OVERWRITE of large partitions — the plan stays built on the "old" table size.