MediumTechnical

81 practiced

Scenario troubleshooting: A nightly pipeline that aggregates event counts per user T fails with OOM at the GROUP BY step on the compute cluster. The input volume has not changed. Describe a systematic approach to diagnose the root cause and list mitigation steps including quick fixes (increase resources), medium fixes (re-partitioning, two-stage aggregation), and long-term fixes (pre-aggregation, reorganizing data layout).

Sample Answer

Approach (systematic diagnosis)1. Reproduce & isolate: Run the job on a small sample and then on full data with debug logging. Confirm OOM occurs at GROUP BY step only.2. Check metrics/logs: - Driver/executor logs for Java OOM stacktraces, GC pause logs. - Spark UI / YARN/K8s metrics: per-task memory, shuffle read/write size, number of tasks failed. - Shuffle/spill stats: are tasks spilling to disk heavily? - Input size per partition: detect skew (some partitions much larger).3. Inspect job plan & code: - Is GROUP BY implemented as reduceByKey/aggregateByKey (map-side combine) or a full shuffle? - Check spark.sql.shuffle.partitions, memoryFraction, off-heap settings.4. Root-cause hypotheses (validate each): data skew, insufficient memory/config, bad partitioning, increase in unique keys, code regression producing explosion (cartesian, join duplication), metadata changes.

Quick fixes (fast, low-effort)- Increase resources: raise executor memory/cores, driver memory, or add more executors. (spark.executor.memory, spark.executor.cores, cluster scaling)- Increase shuffle partitions (spark.sql.shuffle.partitions) to reduce per-task memory.- Increase off-heap memory / heap fraction temporarily if configured.

Medium fixes (targeted code/config changes)- Repartition by key before GROUP BY: df.repartition(numPartitions, col("user_id")) to distribute keys evenly.- Two-stage aggregation (partial + final): perform map-side/combine (reduceByKey or aggregateByKey) to reduce shuffle volume. Example: in Spark RDD use combineByKey; in SQL use GROUP BY on partitions then final aggregation.- Address skew: salt hot keys (append hash suffix) then aggregate and un-salt; or isolate top-N heavy keys and process separately.- Tune shuffle and memory settings: lower spark.executor.memoryOverhead, increase spark.memory.fraction, adjust spark.sql.autoBroadcastJoinThreshold if joins cause blowup.

Long-term fixes (architectural)- Pre-aggregation: maintain incremental nightly/hourly pre-aggregates (materialized views or rollups) so nightly job processes summarized data.- Reorganize storage layout: partition/bucket data by user_id or date+user, sort within buckets to make aggregation streaming-friendly; compact small files to reduce task overhead.- Caching & indexing: build summary tables or OLAP structures (Hive/ClickHouse/BigQuery) optimized for group-by.- Instrumentation & alerts: add monitoring for per-key cardinality, shuffle volumes, and task-level memory to detect regressions before OOM.- Consider streaming/CDC approach: incremental event counting (upserts) instead of full re-aggregation.

Edge cases & verification- After fixes, rerun on full data and validate results match expected counts.- Add unit/integration tests to catch regressions (e.g., simulate skewed keys).- Monitor cost/latency trade-offs when increasing resources vs. optimizing pipeline.

MediumTechnical

66 practiced

Technical task (SQL + explain): Given this PostgreSQL query using a CTE:

WITH daily AS ( SELECT user_id, DATE(created_at) AS d, COUNT(*) as cnt FROM events WHERE created_at >= '2024-01-01' GROUP BY user_id, DATE(created_at))SELECT d, SUM(cnt) FROM daily GROUP BY d;

Explain how PostgreSQL treats CTEs as optimization fences (in older versions) and propose a rewrite or materialization strategy to improve performance and concurrency when needed.

Sample Answer

Brief explanation:Older PostgreSQL treated every WITH (CTE) as an optimization fence: the CTE is fully evaluated and materialized before the outer query is planned/executed. That prevents the planner from pushing predicates, changing join orders, or using parallel plans — which can hurt performance and concurrency for large tables. Newer versions (Postgres 12+) allow the planner to inline CTEs unless you force materialization.

Why this matters for your query:Your CTE aggregates per user/day. If the CTE is materialized, the database computes and writes potentially many rows (disk/memory), then the outer SUM over d reads that materialization. That can be slower and cause contention under load.

Rewrites and strategies:

1) Let planner inline (best when CTE not reused) — simpler and often faster:

sql

SELECT d, SUM(cnt)
FROM (
  SELECT DATE(created_at) AS d, COUNT(*) as cnt
  FROM events
  WHERE created_at >= '2024-01-01'
  GROUP BY DATE(created_at), user_id
) t
GROUP BY d;

Or even simpler (collapse two-level aggregation):

sql

SELECT DATE(created_at) AS d, COUNT(*) AS total_events
FROM events
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at);

If you truly need per-user dedup before summing (e.g., different logic), keep the subquery but let planner optimize it.

2) Use NOT MATERIALIZED (Postgres 12+): explicitly allow inlining while preserving CTE syntax:

sql

WITH daily AS NOT MATERIALIZED (
  SELECT user_id, DATE(created_at) AS d, COUNT(*) as cnt
  FROM events
  WHERE created_at >= '2024-01-01'
  GROUP BY user_id, DATE(created_at)
)
SELECT d, SUM(cnt) FROM daily GROUP BY d;

3) Force materialization intentionally when reuse or stable snapshot required:- If you reuse daily multiple times in the same query or need a consistent snapshot, either use WITH MATERIALIZED (...) (Postgres 12+), or create a temp/materialized table:

sql

CREATE TEMP TABLE tmp_daily ON COMMIT DROP AS
SELECT user_id, DATE(created_at) AS d, COUNT(*) as cnt
FROM events
WHERE created_at >= '2024-01-01'
GROUP BY user_id, DATE(created_at);

CREATE INDEX ON tmp_daily (d);

SELECT d, SUM(cnt) FROM tmp_daily GROUP BY d;

This can improve concurrency if you build the temp table during off-peak and add indexes for fast downstream aggregation.

Practical recommendations:- If the CTE is used only once, avoid forcing materialization — use NOT MATERIALIZED or inline the subquery.- If you need reuse or stable snapshot and the CTE is large, explicitly MATERIALIZE or use a temp/materialized table and index it.- Ensure there’s an index on events(created_at) (or a partition by date) to speed initial filtering.- Test with EXPLAIN (ANALYZE, BUFFERS) to compare plans and I/O.

Trade-offs:- Inlining can enable better plans and parallelism but may recompute work if reused.- Materializing guarantees a single computed result and stable snapshot but increases I/O, memory pressure, and may block concurrency.

This approach balances planner flexibility, performance, and correctness depending on reuse and concurrency needs.

MediumTechnical

74 practiced

Problem-solving: You need to run a GROUP BY on a column with extremely high cardinality (hundreds of millions distinct keys) for daily metrics. Propose at least five strategies to reduce cost and time: consider approximate algorithms, pre-aggregation, re-keying, sampling, and storage layout changes. Explain trade-offs for accuracy, latency, and complexity.

MediumTechnical

58 practiced

Technical task (SQL): You have monthly partitioned tables orders_2024_01(order_id, customer_id, updated_at), orders_2024_02(...), orders_2024_03(...). Design a SQL query that returns the latest row for each order_id across these monthly partitions. Assume partitions cannot be merged and you should avoid full-table sorts when possible. Show an approach using UNION ALL and window functions or partition pruning.

Sample Answer

Approach: read only relevant monthly partitions with UNION ALL, then use a window function (ROW_NUMBER) partitioned by order_id and ordered by updated_at DESC to pick the latest row per order. This avoids a full cross-partition sort if the engine can prune partitions and push down predicates; UNION ALL preserves per-partition identity and is cheap.

Example SQL:

sql

WITH src AS (
  -- explicitly reference partitions we need; use partition pruning by restricting month ranges
  SELECT order_id, customer_id, updated_at FROM orders_2024_01
  UNION ALL
  SELECT order_id, customer_id, updated_at FROM orders_2024_02
  UNION ALL
  SELECT order_id, customer_id, updated_at FROM orders_2024_03
)
SELECT order_id, customer_id, updated_at
FROM (
  SELECT
    order_id,
    customer_id,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
  FROM src
) t
WHERE rn = 1;

Key points:- Use UNION ALL (no dedupe) so DB can scan partitions independently and in parallel.- ROW_NUMBER picks the most recent row per order_id; ties broken by ORDER BY (add more columns if deterministic).- If you can push month filter (e.g., WHERE updated_at BETWEEN ...) put it inside each SELECT to enable partition pruning and reduce IO.- For very large scale, consider pre-aggregating per-partition (SELECT order_id, arg_max(updated_at) ...) then merging those smaller results with the same window logic to further reduce sort/work.

HardTechnical

79 practiced

Theoretical: Discuss trade-offs between performing deduplication at ingestion time, storing raw append-only data and deduplicating at query time, or maintaining a canonical deduped table. Consider costs (storage, compute), latency, complexity, and operational burden in large-scale data platforms.

Sample Answer

Start by clarifying goals: deduplication removes duplicate logical events/records; trade-offs depend on data volume, duplicate patterns, downstream SLA, query patterns, and reprocessing cost.

Option A — dedupe at ingestion (stream/ETL dedupe before storage)- Storage: lowest (store one copy).- Compute: higher up-front (stateful checks, bloom filters, windowed joins) often continuous and memory-heavy.- Latency: can add ingestion latency (synchronous checks), but yields fast downstream queries.- Complexity: moderate–high (exactly-once semantics, watermarking, handling late-arriving records).- Operational burden: state management, scaling state stores, correctness guarantees (idempotence, checkpointing).- Best when: duplicates are frequent, downstream consumers need low-latency clean data, and you can afford sustained compute.

Option B — store raw append-only and dedupe at query time- Storage: highest (full raw log retained).- Compute: query-time cost is higher and repeated (every query or materialization pays dedupe cost).- Latency: query latency increases (on-the-fly aggregation, distinct operations), but ingestion is fast/simple.- Complexity: simpler ingestion pipelines; query logic becomes complex (windowing, latest-by-key) and must be correct across consumers.- Operational burden: lower ingestion ops but higher cost in analytical fleet and risk of inconsistent dedupe logic across teams.- Best when: need full audit trail, want replayability, duplicates are rare, or queries are infrequent/elastic compute available.

Option C — maintain canonical deduped table (materialized/periodic)- Storage: moderate (raw kept optionally; canonical table stores deduped view).- Compute: middle — periodic batch or incremental jobs compute dedupe once; incremental techniques (CDC, upserts) reduce repeating cost.- Latency: depends on refresh cadence (near-real-time with streaming upserts vs nightly batches).- Complexity: highest in coordination — pipeline to produce and keep canonical table consistent (schema evolution, idempotent upserts, lineage).- Operational burden: jobs to maintain table, monitoring for drift, migration and backfill complexity.- Best when: many consumers need consistent clean view, duplicates exist but can tolerate refresh delay, and you want controlled compute cost.

Trade-off summary and decision framework:- If low-latency, single-source-of-truth is required and compute budget exists → ingestion dedupe.- If auditability and replay are critical and duplicates are rare → raw+query-time dedupe.- If many consumers need consistent, reasonably fresh data and you want predictable cost → canonical table with incremental maintenance.

Practical notes:- Hybrid patterns are common: keep raw append-only for lineage + maintain canonical deduped table for consumers; use streaming upserts or CDC to balance latency and cost.- Use probabilistic structures (Bloom/HyperLogLog) for approximate dedupe where tolerable.- Monitor dedupe effectiveness, cost, and divergence; provide SLAs and clear ownership to reduce operational friction.

Set Operations and Complex Aggregations Questions

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Unlock Full Question Bank