Distributed SQL and Query Scaling Questions

Principles and practices for running and optimizing SQL queries in distributed query engines and cloud data warehouses. Candidates should understand how distributed execution affects query performance including partitioning strategies, shuffle operations, data skew, partition pruning, and cost based optimization in engines such as Spark SQL, Presto, and BigQuery. This topic includes designing queries to minimize data movement, choosing appropriate partition keys, leveraging cluster resources efficiently, and interpreting execution plans and job stages to diagnose bottlenecks in large scale queries.

MediumSystem Design

66 practiced

Analysts repeatedly run a heavy aggregation of pageviews by country and date. Design a materialized view or pre-aggregated table strategy to serve these queries. Explain options for refresh (full daily batch vs incremental), integration with query optimizer (automatic rewrite vs manual), and how to handle late-arriving events.

Sample Answer

Requirements & constraints:- Query pattern: heavy aggregations of pageviews by country × date (daily granularity).- Freshness: usually daily, but occasional intra-day/near-real-time needs.- Volume: high event throughput; late-arriving events possible.- Tech: BigQuery as data warehouse, Spark for ETL (can adapt to other clouds).

High-level approach:- Build a pre-aggregated daily table: pageviews_by_country_date(country, event_date, pv_count, last_updated, watermark).- Maintain raw events in partitioned (by ingestion_date) table or streaming topic.

Refresh strategies (trade-offs):1) Full daily batch - Run nightly Spark job that reads the day's events from raw table and writes/overwrites the partition for event_date in the pre-agg table. - Pros: simple, idempotent, easy rollback, consistent. - Cons: longer latency for intra-day needs; reprocessing cost for large partitions.

2) Incremental (preferred for scale) - Maintain micro-batches or streaming aggregation (Spark Structured Streaming or Dataflow) that upserts deltas per country/date into a staging delta table. - Periodically compact deltas into the main pre-agg (merge/mergeInto in BigQuery or time-partitioned MERGE). - Pros: lower compute, supports near-real-time, cheaper for sparse changes. - Cons: more complex (idempotency, exactly-once semantics).

Integration with query optimizer:- Automatic rewrite: If using BigQuery materialized views, create a materialized view on raw events that aggregates by country,event_date. BigQuery can automatically rewrite queries that match. Good for simplicity but limited control and can be expensive if MV cannot match complex queries.- Manual rewrite: Maintain explicit pre-agg table and document it in a data catalog. Teach analysts to query pre-agg or provide view layer that routes queries to pre-agg by rewriting SQL (semantic layer like dbt exposures, LookML, or query router). Manual gives full control and supports more complex rollups (e.g., multiple date granularities, segments).- Hybrid: Provide BigQuery materialized view for simple cases and an optimized pre-agg table + view for larger/custom rollups.

Handling late-arriving events:- Watermarking: Define a late threshold (e.g., 48 hours). Initial daily run computes aggregates for event_date; mark partition as provisional.- Reconciliation job: After watermark passes, run a correction batch that re-aggregates affected event_date partitions merging late events into pre-agg (MERGE with upsert).- Maintain change logs: Keep a small changelog table of upserts and correction runs for auditing and replay.- Provide “as-of” awareness: Add flags or versions (provisional vs final) to rows so analysts can choose to include final data only.- Backfill automation: Support ad-hoc backfills for older dates when necessary; use idempotent MERGE to avoid duplicates.

Operational details & best practices:- Partition pre-agg by event_date and cluster by country to optimize reads.- Store row-level watermark/last_updated to facilitate incremental merges.- Use idempotent writes (MERGE or upsert) and de-duplication keys to achieve correctness.- Monitor drift: track differences between pre-agg and on-the-fly aggregations for sample dates to detect bugs.- Cost optimization: keep multiple rollups (daily, weekly) if queries benefit; use materialized views for tiny rollups but prefer partitioned pre-agg for large-scale.- Documentation & discoverability: register in data catalog, expose semantic views so analysts get the pre-aggregated table transparently.

This design balances cost, freshness, and correctness: incremental upserts with a reconciliation after a defined watermark, partitioned/clustering for performance, and a hybrid optimizer integration strategy (materialized view when possible, explicit pre-agg + semantic layer for control).

HardSystem Design

61 practiced

You manage BigQuery workloads with nightly batching and daytime ad-hoc analysis. Describe slot allocation strategies (separate reservations, flex slots, autoscaling) to guarantee SLAs for nightly ETL while keeping on-demand analysts productive and costs reasonable. Explain how you would size reservations and handle concurrency spikes.

Sample Answer

Requirements & constraints (clarify):- Hard SLA: nightly ETL must finish between 00:00–06:00 (6h window).- Daytime analysts require interactive latency (seconds→low minutes).- Minimize cost while avoiding ETL misses and painful analyst waits.- Expectable load: nightly ETL needs X total slot‑seconds; daytime has many small ad‑hoc queries with unpredictable spikes.

High-level strategy1. Separate reservations by workload class: - One reservation dedicated to nightly ETL (guaranteed capacity, high priority during night). - One or more reservations for interactive/analyst workloads (smaller committed capacity). - Keep a small shared “buffer” or use flex slots for peaks that can be attached to either reservation dynamically.

2. Capacity mix: baseline commitments + elastics (flex slots) - Purchase capacity commitments to cover steady-state needs (cheaper per-slot for commitments). - Use flex slots (short-term hourly/Flex) or autoscaling to absorb transient spikes rather than overprovisioning the committed pool.

Sizing reservations (practical calc + example)- Measure: from historical runs, compute total slot‑seconds consumed by ETL per night (S_total). Required committed slots = ceil(S_total / window_seconds) * safety_factor. Example: ETL uses 72,000 slot‑minutes = 4,320,000 slot‑seconds. Window = 6 hours = 21,600s. Baseline slots = 4,320,000 / 21,600 ≈ 200 slots. Add 20–30% buffer → commit ~240–260 slots for ETL.- For analysts: size committed slots to cover median daytime concurrency. If median concurrent analysts consume 80 slots and 95th percentile consumes 200 slots, commit ~80 slots and rely on autoscaling/flex for spikes.- Cost trade-off: committed slots cheaper per slot-hour; flex slots cost more but only used when needed.

Autoscaling & flex slot use- Enable autoscaling (if using BigQuery Reservations autoscaling) for the interactive reservation so it can add flex slots automatically during spikes up to a cap.- Programmatically buy flex slots (or use autoscale feature) to temporarily expand the ETL reservation if an unusually large ETL run occurs; release after job completes.- Set hard caps to prevent unexpected cost runaway.

Concurrency spikes & priority handling- Use separate reservations + assignment tags to ensure ETL never competes with analysts for committed slots.- For spikes: - Analysts: allow queries to burst to flex/autoscale capacity; if caps reached, queue low‑priority/long queries (use query priority = BATCH for large non‑interactive jobs) or enforce per-user concurrency limits. - ETL: run with HIGH priority and retry/backoff logic; if ETL needs more than committed + allowed flex cap, fall back to graceful degradation (stagger pipeline tasks, skip optional workloads, produce partial results) rather than missing SLA.- Use BigQuery slot reservations' query queues and capacity management features to shape concurrency (max slots per query, prioritized job queues).

Operational controls & monitoring- Instrument: - slot utilization (per reservation), latency percentiles, job queue lengths, slot‑seconds per job. - Alerts on slot utilization >85% sustained, ETL lag > threshold, unexpected cost spikes.- Autoscaling policy knobs: max flex slots, scale‑up/scale‑down frequency, cooldown windows.- CI/CD triggers: if ETL profiles change (bigger tables/transform), re-run sizing calculator to adjust committed capacity.

Best practices & trade-offs- Separate reservations reduces noisy‑neighbor risk; increases manageability.- Commit to baseline enough to meet SLAs most nights; use flex slots for rare peaks to optimize cost.- Keep ETL parallelism and reservation-aware tuning (batch small transforms into fewer queries or increase query parallelism to use slots efficiently).- Regularly re-evaluate sizing monthly or after significant data growth.

Resulting behavior- Guaranteed ETL SLA via dedicated committed slots sized by slot‑seconds calculation and small flex buffer.- Analysts get responsive interactive experience with committed baseline + autoscaled flex for spikes.- Costs controlled by balancing committed vs flex; monitoring and caps prevent runaway spending.

EasyTechnical

69 practiced

Explain what a broadcast (map-side) join is in distributed SQL engines. When is a broadcast join preferred, what are the trade-offs (memory, duplication), and how do engines like Spark decide to broadcast a table (hint: thresholds such as spark.sql.autoBroadcastJoinThreshold)?

EasyTechnical

78 practiced

Explain in the context of distributed SQL engines (Spark SQL, Presto, BigQuery) what a 'shuffle' operation is, how it differs from physical data partitioning, and why shuffles are expensive. Give concrete examples of SQL operations that cause shuffles (e.g., group by, join, repartition) and examples that avoid them.

MediumTechnical

59 practiced

A Spark job applies a Python scalar UDF to compute a derived column on 100 million rows and is very slow. Explain the performance characteristics of Python UDFs in Spark and provide alternatives (built-in SQL functions, vectorized pandas UDFs with Arrow, or Scala/Java UDFs). Describe how you would benchmark and measure the improvement.

Unlock Full Question Bank

Get access to hundreds of Distributed SQL and Query Scaling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.