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.

MediumTechnical

61 practiced

Explain predicate pushdown and projection pushdown in distributed SQL engines. Give an example where using SELECT * causes unnecessary data movement and how to rewrite the query to ensure filters and projections are applied as early as possible.

Sample Answer

Predicate pushdown and projection pushdown are optimization techniques in distributed SQL engines (Spark, Presto/Trino, Snowflake, BigQuery) that minimize data read and network I/O by applying filters and column selection as close to the data source as possible.

- Predicate pushdown: the engine translates WHERE-clause predicates so they run in the storage layer or worker that reads the file/partition, so only rows that match are returned (e.g., filter by partition column or indexed column).- Projection pushdown (column pruning): the engine reads only the columns referenced by the query (supported by columnar formats like Parquet/ORC), avoiding reading wide rows.

Example of unnecessary data movement:Suppose orders is a wide Parquet table (many columns) partitioned by year.

Bad:SELECT *FROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.order_year = 2024 AND c.country = 'US';

Why this can be bad:- SELECT * forces reading all columns from orders (no projection pushdown).- If the engine performs the join before applying c.country filter, many rows may be shuffled across the network.- If order_year is a partition column but the engine doesn't push it down, it may scan unnecessary partitions.

Better — push filters and projections early:1) Restrict columns explicitly (projection)2) Apply filters before joins; push partition predicates (predicate)

Rewritten:SELECT o.order_id, o.order_total, c.id AS customer_id, c.nameFROM ( SELECT order_id, customer_id, order_total FROM orders WHERE order_year = 2024 -- partition/predicate pushdown) oJOIN ( SELECT id, name, country FROM customers WHERE country = 'US' -- predicate pushdown on customers) c ON o.customer_id = c.id;

Why this is better:- Only needed columns are read from both tables (column pruning).- Filters run at read time (partitions scanned reduced; fewer rows).- Join operates on much smaller datasets, reducing shuffle and latency.

Best practices:- Avoid SELECT * in production analytics queries.- Filter on partitioned/indexed columns to enable pushdown.- Use columnar storage (Parquet/ORC) so projection pushdown is effective.- Inspect query/execution plan (EXPLAIN) to confirm predicates and projections are pushed down.

MediumTechnical

78 practiced

A business dashboard queries multiple large tables and takes 2 minutes to load. As a data analyst, outline a prioritized plan to reduce dashboard latency to under 15 seconds. Consider query design, materialized views, pre-aggregations, cache policies, and BI tool behavior in your plan.

Sample Answer

Start by clarifying SLAs and measurable goals, then apply iterative, prioritized optimizations so stakeholders see wins quickly.

1) Triage & measurement (0–2 days)- Capture current query plans, cardinalities, and full dashboard load trace (DB profiler, EXPLAIN, BI query logs). Measure which queries spend most time (top 80/20).- Define success metric: TTFB/dashboard ready <15s.

2) Quick wins (days 1–7)- Add appropriate indexes (covering, composite) based on slow JOINs/WHEREs; avoid over-indexing.- Rewrite expensive queries: push predicates early, avoid SELECT *, limit JOINed row counts, replace correlated subqueries with joins or window functions.- Remove unnecessary visual-level filters that force full scans.

3) Medium-term: caching & BI tool behavior (1–2 weeks)- Enable and tune BI caching (Tableau extract refreshes, Power BI aggregated tables). Set cache TTL to business-acceptable frequency (e.g., 5–15 min) and warm cache on expected users.- Reduce dashboard-side queries: use parameterized queries, limit auto-refresh, reduce number of quick filters, pre-load context filters.

4) Strong structural fixes: materialized views & pre-aggregations (2–6 weeks)- Create materialized views or summary tables for heavy aggregations (daily/hourly rollups) updated incrementally if supported (REFRESH FAST / incremental CDC).- Example: replace SELECT SUM(sales) GROUP BY date, region from huge table with materialized summary table and index on (date, region).- Use OLAP cubes or aggregation tables tailored to dashboard granularity.

5) Architectural & scale (ongoing)- Consider read replicas for BI traffic, columnar storage (redshift, BigQuery), or OLAP engine (ClickHouse, Druid) if throughput demands.- Implement automated refresh schedules and lineage so analysts know staleness.

6) Validation, monitoring & rollout- A/B test performance, monitor cache hits, query times, user experience. Roll back changes if accuracy/latency trade-offs unacceptable.- Document changes, update dashboard owners, and automate maintenance (refresh jobs, index rebuilds).

Trade-offs: fresher data vs latency — choose based on business need. Prioritize fast observable wins (indexes, query rewrite, BI caching) before heavier engineering (materialized views, new infra).

EasyTechnical

73 practiced

Describe the difference between broadcast (map-side) joins and shuffle (repartition) joins in engines like Spark SQL and Presto. For a data analyst joining a 200-row lookup table to a 1TB fact table, which join would you expect and why? Mention memory and network considerations and any safety checks you would perform before encouraging broadcast.

EasyTechnical

116 practiced

You have a sales table partitioned by sale_date (DATE) with schema sales(order_id INT, sale_date DATE, customer_id INT, amount NUMERIC). Show two example SQL predicates that will allow partition pruning and two that will prevent pruning. Explain why the latter fail to prune and how to rewrite them for better performance in a cloud warehouse.

Sample Answer

Partition pruning works when the predicate directly matches the partition key with a constant or simple range the query planner can evaluate at plan time.

Prune-friendly examples:

sql

-- Exact date
SELECT * FROM sales WHERE sale_date = DATE('2025-01-15');

-- Range that maps to partitions
SELECT * FROM sales WHERE sale_date BETWEEN DATE('2025-01-01') AND DATE('2025-01-31');

Predicates that prevent pruning:

sql

-- Expression on partition column (prevents pruning)
SELECT * FROM sales WHERE DATE(sale_date) = CURRENT_DATE();

-- Non-deterministic / function-wrapped bound
SELECT * FROM sales WHERE sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);

Why they fail:- Wrapping the partition column in a function (DATE(sale_date)) hides the raw column value; planner can’t evaluate which partitions to scan.- Non-deterministic functions (CURRENT_DATE()) or runtime-evaluated expressions may be resolved at execution time, depending on the engine, preventing static pruning.

How to rewrite for pruning:- Push functions to the right or compute constants beforehand:

sql

-- Precompute date in client or use constant
-- e.g., compute today as '2025-12-06' in application or a CTE:
WITH params AS (SELECT DATE('2025-12-06') AS today)
SELECT s.* FROM sales s, params p WHERE s.sale_date = p.today;

-- Or compute boundary once and use it as constant
SELECT * FROM sales WHERE sale_date >= DATE_SUB(CAST('2025-12-06' AS DATE), INTERVAL 7 DAY);

Best practice in cloud warehouses: compute dynamic dates in your job scheduling layer or CTE/variable so the predicate is a plain column comparison to a constant or simple range, enabling partition pruning and much lower I/O.

MediumTechnical

79 practiced

Design partitioning keys for an e-commerce events table used for nightly aggregation and ad-hoc user lookups. Consider common query patterns (time-based aggregations vs point lookups), partition count limits, small partition problems, and how to balance scan efficiency with write throughput and compaction needs.

Sample Answer

Situation: The table receives high-volume event inserts and must support two common patterns: nightly time-based aggregations (e.g., daily metrics per product) and ad-hoc point lookups (e.g., events for a specific user_id or order_id).

Recommendation (high level):- Partition by event_date (day) as the primary partition key. This maximizes pruning for nightly aggregations and keeps partition count bounded (one per day).- Use a secondary clustering/hashing key for point lookups: cluster (user_id) or bucket/hash by user_id (and optionally event_type). Clustering speeds selective scans without exploding partitions.- If ingest volume is extremely high and you need intra-day partitioning, use hourly partitions only if each hour produces enough data (aim for partitions >>100MB). Otherwise prefer daily and rely on ingestion batches.

Example (BigQuery-style):

sql

CREATE TABLE events (
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_ts TIMESTAMP,
  event_date DATE,
  payload JSON
)
PARTITION BY event_date
CLUSTER BY user_id, event_type;

Why this balances needs:- Time-based queries: partition pruning on event_date makes nightly aggregation fast and predictable.- Point lookups: clustering by user_id reduces scan range inside relevant partitions; hash bucketing (on systems that support it) gives O(1) lookup distribution.- Partition count limits: daily partitioning keeps count linear with retention (e.g., 365/day); avoids system limits like thousands of partitions.- Small-partition/file problem: choose partition grain so partitions hold sizable data. If many users generate tiny writes, enable periodic compaction/merge jobs to combine small files and improve read throughput.- Write throughput & compaction: batch writes (micro-batches), use bulk loads where possible, and schedule compaction during low-traffic windows. For append-heavy systems, prefer immutable daily partitions and run compaction offline.

Operational tips:- Monitor partition sizes; target 100MB–1GB per partition for file-based systems.- TTL/rolling retention to bound partition count.- Create materialized nightly aggregates to avoid re-scanning raw events for dashboards.- For frequent single-user queries, maintain a denormalized user_events table or an index/table keyed by user_id if latency is critical.

Trade-offs:- Hourly partitions improve intra-day aggregation correctness but increase partition count and small-file risk.- Heavy clustering improves lookup speed but can increase write amplification and compaction frequency.

This design gives performant nightly aggregation through partition pruning while enabling efficient ad-hoc user lookups via clustering/bucketing and operational controls (batching, compaction, retention).

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.