Analytical Query Performance and Optimization Questions

Compare ETL and ELT specifically on where the transformation compute happens and who pays for it, since that is what actually drives downstream query performance and cost, not just a naming difference.

Where transformation happens

In ETL, data is transformed in a separate processing layer (a dedicated ETL tool or compute cluster) before it lands in the warehouse, so the warehouse only ever stores already-clean, already-shaped data. In ELT, raw data is loaded into the warehouse first, and transformation happens inside the warehouse using its own compute, typically via SQL (often orchestrated by a tool like dbt).

Effect on query performance and cost

ELT means transformation work competes for the same warehouse compute that serves ad-hoc and dashboard queries, so a heavy nightly ELT transformation run can directly degrade interactive query latency during that window unless workload isolation (routing the transformation job to its own separate compute pool) is in place. ETL keeps that transformation load off the warehouse entirely, at the cost of running and maintaining a separate transformation system. On the cost side, ELT's transformation cost is warehouse compute (usually billed per query or per second of warehouse time), which is easy to see and attribute per transformation, while ETL's transformation cost lives in a separate system's compute budget, which can be cheaper or more expensive depending on that system, but is a genuinely separate cost line to manage.

Why cloud warehouses pushed teams toward ELT

Modern cloud warehouses decouple storage from compute and can scale compute elastically, which removes the traditional reason to do transformation somewhere else first: warehouse compute used to be a fixed, expensive, hard-to-scale resource you wanted to protect from heavy transformation jobs, but an elastically scalable warehouse can absorb that load (in its own dedicated pool) without permanently starving other workloads. Combined with SQL-based transformation tooling becoming mature and version-controllable, ELT collapsed the operational reason to maintain a separate ETL system for many teams.

Trade-offs and pitfalls

ELT is not free of the workload-isolation problem, it just moves the isolation decision inside the warehouse (a dedicated transformation compute pool) rather than outside it (a separate ETL cluster); teams that adopt ELT without also isolating transformation compute from serving compute reproduce the exact resource-contention problem ETL was originally designed to avoid, just inside one platform instead of across two.

Cache invalidation for a mostly-hourly, sometimes-manually-refreshed dashboard needs three distinct mechanisms working together: a default time-to-live (TTL) that expires stale results automatically, an explicit manual-refresh path that bypasses the TTL on request, and a way to prevent many simultaneous manual requests from all triggering the same expensive recomputation at once.

Balancing TTL and manual refresh

Set the TTL to the hourly cadence (results expire and recompute automatically on schedule), and expose a manual "Refresh" action that force-invalidates the specific cached entry a user is looking at, triggering an immediate recompute for that one entry rather than waiting for the next scheduled hour. The manual path should write the same cache key the scheduled refresh would use, so the next scheduled refresh simply finds the cache already warm and does not duplicate work.

Avoiding a thundering herd at hour boundaries

If every cached entry's TTL expires at exactly the top of the hour, every dashboard viewer's next request in that same minute triggers its own recomputation simultaneously, which is the thundering-herd failure. Two effective techniques: stagger TTL expiry with a small random jitter per cache key (so not every entry expires in the same instant), and use a "stale-while-revalidate" pattern, where an expired entry is still served immediately while a single background refresh recomputes it, so concurrent requests during the recompute window get the (slightly stale) old value instead of all blocking on, or all independently triggering, the same expensive query.

Preventing duplicate concurrent recomputation

When a cache entry expires and multiple requests arrive before the recompute finishes, only the first request should actually trigger the underlying query; subsequent requests during that window should either wait on the same in-flight computation or serve the stale value, never independently re-run the same expensive query. This is typically implemented with a short-lived lock or "computation in progress" marker keyed to the same cache key.

Trade-offs and pitfalls

Stale-while-revalidate trades a small, bounded amount of extra staleness (users see last hour's number for the few seconds the refresh takes) for a large reduction in redundant load, which is almost always the right trade for a dashboard where a few seconds of extra staleness is invisible to users. The main pitfall is forgetting the manual-refresh path needs the same duplicate-suppression logic as the scheduled one; without it, ten users clicking Refresh on the same dashboard within the same second reintroduces exactly the thundering-herd problem the scheduled path was designed to avoid.

Reading a slow-query profile is fundamentally the same discipline across engines, comparing what the query actually did against what it should have needed to do, but the artifact you read to get there differs by product: Snowflake's Query Profile shows an operator-by-operator breakdown with bytes scanned and pruning stats per step, BigQuery's job statistics expose bytes processed, slot-time consumed, and per-stage timing, and Redshift exposes system tables (STL_QUERY, SVL_QUERY_REPORT, STL_SCAN, SVV_TABLE_INFO) you query directly with SQL rather than a graphical profile.

The common diagnostic shape

1. Start with bytes/rows scanned versus what the filter should have needed. If a query filtering to one day scanned the whole table's history, that is a pruning failure before anything else is worth investigating.
2. Look at where time, CPU, or slot-time actually went, not just total duration. Snowflake's Query Profile breaks each operator's time into a percentage breakdown including a processing (CPU-bound) share; BigQuery's slot-time-consumed statistic is fundamentally a CPU-time proxy, since a slot is a unit of CPU and memory; Redshift's SVL_QUERY_REPORT.elapsed_time per step combined with the is_diskbased flag distinguishes a step that is CPU-bound in memory from one that spilled to disk. A query dominated by CPU-heavy computation (an expensive per-row expression, a large in-memory sort) needs a different fix than one dominated by I/O (an unpruned scan) or network (a shuffle stage).
3. Check for spill or disk-based intermediate results (Snowflake's Query Profile flags disk spillage explicitly; Redshift's SVL_QUERY_REPORT and disk-usage views show it; BigQuery surfaces this less directly but excessive shuffle bytes at a stage is the analog signal), since spill often means the query's working set does not fit the allocated compute.
4. Cross-check against table statistics/metadata freshness: an operator's estimated versus actual row counts diverging sharply points to stale statistics driving a bad plan choice.

Reading a Spark SQL plan instead

On Spark SQL specifically, there is no separate "job details" view the way BigQuery or Snowflake offer; instead you read EXPLAIN (or the Spark UI's SQL tab) to see the logical plan (what the query asks for) alongside the physical plan (how the engine intends to execute it, including Exchange nodes for shuffles and the chosen join strategy), and cross-reference that physical plan against the Spark UI's stage-level metrics (shuffle read/write, task duration) exactly as you would use Redshift's SVL_QUERY_REPORT or Snowflake's Query Profile, the artifact differs, the underlying discipline of comparing planned-versus-actual does not.

Worked example: Redshift

-- find the query (STL_QUERY has no "elapsed" or "substring" columns: compute elapsed explicitly and truncate the query text with the SUBSTRING function)
SELECT query, DATEDIFF(seconds, starttime, endtime) AS elapsed_seconds, SUBSTRING(querytxt, 1, 100) AS query_preview
FROM stl_query
WHERE userid <> 1 ORDER BY starttime DESC LIMIT 20;

-- see where its time went, per step
SELECT * FROM svl_query_report WHERE query = <query_id> ORDER BY segment, step;

-- check for a runaway scan
SELECT * FROM stl_scan WHERE query = <query_id>;

-- check table stats/skew that might explain a bad plan
SELECT * FROM svv_table_info WHERE "table" = 'fact_table';

This sequence (identify the query, see per-step time, inspect the scan step specifically, then check table-level stats) mirrors what a Snowflake Query Profile view or a BigQuery job-details page shows visually rather than via SQL.

Trade-offs and pitfalls

The single biggest mistake is reading total elapsed time and stopping there; two queries with identical wall-clock time can have completely different bottlenecks (one I/O-bound on an unpruned scan, one shuffle-bound on a skewed join), and the fix for each is unrelated to the other. Always break the total down by stage or step, and by CPU versus I/O versus network, before deciding what to change, and treat the metrics as the primary evidence over intuition about what "should" be slow.

A 10x regression (2 seconds to 20 seconds) with no obvious single cause needs a systematic sweep across every layer between the user's click and the number on screen, since the same symptom (a slow-feeling dashboard) can originate almost anywhere in that chain, and guessing which layer to start with wastes time when the guess is wrong.

Systematic debugging plan

1. Frontend rendering: rule this out first, it is the cheapest check. Open the browser's network tab and compare how long the actual data request takes versus how long the page takes to become interactive after the data arrives; if the request itself is fast but rendering is slow, the regression is a frontend problem (a new chart type, more rows rendered, a JavaScript issue), not a query problem, and the rest of this plan does not apply.
2. The BI tool's generated query: if the request itself is slow, capture the actual SQL the BI tool generated for this specific view. A subtle change (a new filter added to the dashboard, a join the tool auto-generates differently after a schema change) can silently make the generated query far more expensive without anyone directly editing SQL.
3. The query engine: run that captured query's EXPLAIN (or the engine's equivalent execution-plan view) and compare it, if possible, against a plan captured before the regression. Look specifically for a changed join strategy, a missing partition prune that used to fire, or a full scan where a smaller one used to happen.
4. The caching layer: check whether this specific query (or an equivalent one) used to hit a result cache or a materialized view and no longer does, since a cache/materialized-view miss alone can account for exactly this kind of sudden, large regression with no code change at all.
5. Upstream data changes: check whether the underlying table's size, cardinality, or statistics freshness changed recently (a data volume spike, a schema change, stale statistics after a large load) in a way that would explain the query planner making a different, worse choice than it used to.

Telemetry, logs, and quick experiments

Pull the query engine's own execution history/logs for this exact query text (or its generated variants) over the days spanning the regression, most warehouses retain enough query history to directly compare a slow run against a recent fast run's plan and stats. If historical plans are not available, a quick experiment (re-running the captured query directly against the warehouse, bypassing the BI tool entirely) isolates whether the BI layer or the query itself is responsible, in under a minute.

Reducing user impact while investigating

If the caching layer is suspect, a quick, low-risk mitigation is manually warming or refreshing the cache/materialized view for this specific dashboard while the deeper investigation continues, which can restore acceptable performance immediately even before the root cause is confirmed. If a specific recent change (a new filter, a schema change) is a strong suspect, temporarily reverting just that change while investigating is often faster and safer than a partial fix applied under time pressure.

Trade-offs and pitfalls

The instinct under pressure is to jump straight to the query engine and start tuning the SQL, since that is where the most familiar levers live, but skipping steps 1 and 2 risks spending real effort optimizing a query that was never the actual problem. Work the layers in order from cheapest-to-check to most-expensive-to-check, and confirm each layer is genuinely implicated with real evidence (a captured plan, a network-tab timing) before moving deeper, rather than jumping to whichever layer feels most likely.

Guardrails against a runaway query work best as a graduated sequence, warn first, then throttle, then hard-stop, rather than a single blunt limit, since the goal is protecting the budget without making every analyst feel like they are working against the platform.

Dry-run cost estimation

BigQuery's dry-run mode (a dryRun flag on the query job configuration, exposed as the --dry_run flag in the bq CLI and as a checkbox in the console's query editor) compiles and validates a query and reports the exact bytes it would process, without actually running it or incurring any cost. Surfacing that estimate to the user BEFORE they run the query, in the query editor itself, turns an invisible cost into a visible one at the moment the analyst can still change their mind, which is far more effective than a limit they only discover after the fact. Presto/Trino-based lakehouses have an analogous EXPLAIN (TYPE IO) or connector-level statistics that can approximate the same estimate, though with less precision than BigQuery's exact dry-run since it depends on the underlying connector's metadata quality.

Maximum-bytes-scanned quotas

Set a per-query maximum-bytes-scanned limit (BigQuery supports this directly via the maximum_bytes_billed job parameter) that fails the query before it runs if the dry-run estimate exceeds the threshold, and set a separate per-user or per-project daily quota so no single day's cumulative usage can blow the monthly budget even if every individual query is under the per-query limit.

Query linting

A lightweight, automated check run before a query executes (or as part of a scheduled query's CI) that flags common expensive patterns, an unfiltered SELECT * on a large partitioned table, a missing partition filter, a cross join with no obvious bound, catches a large share of accidental cost before it ever reaches the warehouse, and can run as a warning rather than a hard block for ad-hoc work.

Automatic abort policies

For queries already running, an automatic timeout or a bytes-processed circuit breaker that kills a query exceeding an unusually high threshold protects against a query whose actual cost diverges badly from its dry-run estimate (a rare but real risk if statistics are stale or the query's shape changes its actual scan pattern from what dry-run predicted).

Making it feel helpful, not punitive

Pair every block with a clear, specific message (what threshold was hit, what the estimated cost was, and a concrete next step, add a partition filter, request a temporary quota increase) rather than a bare error code, and make the escalation path fast (a self-service temporary quota bump for a named, time-boxed reason) so a genuine one-off need does not turn into a multi-day ticket. A guardrail that blocks legitimate work with no visible reason or fast escape hatch trains people to route around it rather than respect it.

Trade-offs and pitfalls

A maximum-bytes-billed limit set too conservatively blocks legitimate, large, business-critical queries as often as it blocks true mistakes, and a query linter that is too aggressive (flagging every unpartitioned query, even ones that are genuinely small) trains analysts to ignore its warnings entirely, defeating the point. Calibrate thresholds against real historical query-cost distributions for your workload rather than a generic industry number, and revisit them periodically as usage patterns change.