# Snowflake Query Profile: Fix Slow Joins, Spilling and Excessive Scans

Use Snowflake Query Profile to explain the work a query performed, then change the operation responsible for unnecessary work. First separate execution from compilation, queuing and application delay. Next inspect row growth, scan selectivity and memory pressure. Retest the same result under comparable conditions; a faster query that changes revenue totals or excludes customers is a failed optimization.

Canonical URL: https://www.martechdevs.com/blog/snowflake-query-profile

By [Jafrin Akter Alo](https://www.martechdevs.com/authors/jafrin-akter-alo)

Data Engineer and Co-founder at [Martechdevs](https://www.martechdevs.com) and [Growth and Analytics Partners](https://www.growthandanalytics.com)

[LinkedIn](https://linkedin.com/in/jafrinalo)

Last updated: 2026-10-25

Next step: [Snowflake Implementation: Plan the Phases, Price the Work and Connect Your Sources](https://www.martechdevs.com/blog/snowflake-implementation)

## What should you capture before opening Query Profile?

Capture the exact query ID and the business result it was supposed to produce. A dashboard can issue several statements, so identify the slow statement rather than assuming the last query in the session is the one the user noticed.

Record the SQL version, parameters, reporting window, role, warehouse configuration, row count and expected totals. Include concurrent workloads and whether the warehouse had recently resumed. These details distinguish a query regression from a change in input size, permissions or operating conditions.

In Snowsight, open the relevant entry in Query History and inspect its details and profile. Operator nodes show the execution work; arrows show rowsets moving between operations. Query details and profiles can be unavailable because of permissions, age, failure or other collection limits. [Snowflake Query History and Profile](https://docs.snowflake.com/en/user-guide/ui-snowsight-activity)

Our [warehouse performance and implementation work](https://www.martechdevs.com/data-warehouse-cdp) starts with a representative business query and a reconciled baseline. The broader [Snowflake implementation troubleshooting guide](https://www.martechdevs.com/blog/snowflake-implementation-mistakes) covers access, billing and recovery; this guide focuses on explaining and repairing the query itself.

## Is the delay actually inside query execution?

Compare elapsed, compilation, execution and waiting times before tuning SQL. A statement that executes in one second after waiting twenty seconds needs a different intervention from one that spends twenty seconds expanding a join.

The following read-only diagnostic selects one known query from Account Usage. Replace the placeholder with the saved query ID and use a role with the appropriate access to the shared `SNOWFLAKE` database. It does not query customer tables.

```sql
SELECT
    query_id,
    warehouse_name,
    total_elapsed_time / 1000.0 AS elapsed_seconds,
    compilation_time / 1000.0 AS compilation_seconds,
    execution_time / 1000.0 AS execution_seconds,
    queued_provisioning_time / 1000.0 AS provisioning_seconds,
    queued_overload_time / 1000.0 AS overload_seconds,
    transaction_blocked_time / 1000.0 AS blocked_seconds,
    bytes_spilled_to_local_storage,
    bytes_spilled_to_remote_storage
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_id = '<QUERY_ID>';
```

The history time fields are milliseconds, and Account Usage can lag by up to 45 minutes. An absent recent row does not prove the query never ran. Use the client's query ID and immediate Snowsight evidence while the history catches up. Other timing categories, including repair or retry work, can matter; do not assume the displayed columns explain every elapsed millisecond. [QUERY_HISTORY fields and latency](https://docs.snowflake.com/en/sql-reference/account-usage/query_history)

| Dominant delay | First investigation | Evidence a repair should improve |
| --- | --- | --- |
| Compilation | Generated SQL complexity and repeated query construction | Compilation time for the same requested result |
| Provisioning | Resume or resize timing | Time waiting for resources before work begins |
| Overload queue | Concurrent workloads and scheduling | Queue time under representative concurrent demand |
| Transaction blocking | Conflicting write activity | Blocking time with the required transaction behavior preserved |
| Execution | Operators, row growth, reads and memory | Correct-result execution time and resource use |
| Application remains slow after SQL finishes | Result volume, transfer and rendering | End-to-end user latency, measured outside the warehouse too |

For example, reducing a two-second execution to one second removes only one second from a request that spends eighteen seconds waiting elsewhere. Keep the performance target attached to the user or downstream job, not just the easiest metric to improve.

## How do you read the expensive operators without misdiagnosing them?

Start with the expensive operation, then trace its inputs backward. An expensive aggregation can be the consequence of an earlier join multiplying rows. Changing the final aggregation before fixing that multiplication can hide both the performance defect and an incorrect metric.

Inspect the operator's input/output counts, expressions, joins and upstream scans. Move between execution steps where the query has more than one. Snowflake's profile includes operator statistics, attributes and an expensive-node view; those views describe a particular execution, not a guaranteed plan for every future run. [Query Profile navigation](https://docs.snowflake.com/en/user-guide/ui-snowsight-activity)

Use this investigation map to choose the next question:

| Operator or pattern | Question to answer | Typical repair to evaluate |
| --- | --- | --- |
| Join produces an unexpectedly large rowset | Did both sides have multiple rows per join key? | Correct the key or aggregate to the intended grain before joining |
| Table scan reads nearly everything for a narrow request | Is the useful filter reaching a suitable stored column? | Correct the predicate and inspect partition pruning |
| Large sort or duplicate elimination | Does the output genuinely require that ordering or deduplication? | Remove only unnecessary work; preserve required semantics |
| Expensive repeated transformation | Are several downstream queries rebuilding the same logic? | Evaluate a maintained intermediate model with a freshness contract |
| External function dominates | Is time spent in remote calls, retries or excessive payloads? | Reduce unnecessary calls and inspect the external service path |

A large rowset is not automatically wrong. An account-to-events analysis may intentionally expand each account into its events. The useful test is whether the output grain matches the question and whether subsequent sums remain valid.

Snowflake Query Insights can flag issues such as exploding joins, ineffective filters, unnecessary aggregation, remote spillage and queuing. Use these as investigation leads, then verify the proposed change against the required result. An insight is not permission to discard duplicates that are legitimate business records. [Query Insights](https://docs.snowflake.com/en/user-guide/query-insights)

## Can you retrieve operator evidence with SQL?

Yes. `GET_QUERY_OPERATOR_STATS` returns operator statistics for a completed query from the previous 14 days. The role needs `MONITOR` or `OPERATE` on the warehouse. Preserve the original query ID explicitly; using a session's latest query after several diagnostics can inspect the wrong statement. [Operator-statistics function](https://docs.snowflake.com/en/sql-reference/functions/get_query_operator_stats)

This read-only example returns raw row and pruning evidence. It leaves unavailable values as null rather than turning missing evidence into zero:

```sql
SELECT
    step_id,
    operator_id,
    operator_type,
    operator_statistics:input_rows::NUMBER AS input_rows,
    operator_statistics:output_rows::NUMBER AS output_rows,
    operator_statistics:pruning:partitions_scanned::NUMBER
        AS partitions_scanned,
    operator_statistics:pruning:partitions_total::NUMBER
        AS partitions_total
FROM TABLE(GET_QUERY_OPERATOR_STATS('<QUERY_ID>'))
ORDER BY step_id, operator_id;
```

The function exposes different statistics for different operators. Input rows can be absent for an operator with no input edge, and output rows can be absent for a result operator. Read null as unavailable or inapplicable evidence in that context. Do not rank all nulls as “zero work.” [Operator-statistics output](https://docs.snowflake.com/en/sql-reference/functions/get_query_operator_stats)

Keep query-level and operator-level denominators separate. The full query can touch several tables, while a particular scan's partition count concerns its own input. A single percentage across unrelated scans can conceal the table responsible for most of the avoidable work.

## How can a join make both the query and the revenue number wrong?

Joining two detail tables on a shared account key can multiply both sides. Pre-aggregate each detail table to the output grain before joining when the report needs account-level measures.

Consider an illustrative USD-only dataset. Account A has two orders worth USD 100 and USD 50, plus two contacts. Account B has one USD 80 order and one contact. Account C has no orders or contacts. Account IDs and order IDs are unique in their own source tables, order amounts are known, and every order belongs to a known account.

| Account | Correct order revenue | Contacts | Revenue after a naive orders-to-contacts join |
| --- | ---: | ---: | ---: |
| A | USD 150 | 2 | USD 300 |
| B | USD 80 | 1 | USD 80 |
| C | USD 0 | 0 | Often absent with inner joins |
| Total | USD 230 | 3 | USD 380 |

The join has made account A's two orders appear once for each contact. A final `DISTINCT` is not a reliable repair: two genuine orders can share an amount, and the distinct row shape may still include the contact ID.

For a report with one row per account, aggregate orders and contacts independently. The example assumes three controlled input tables: `accounts(account_id)`, `orders(order_id, account_id, amount_usd)` and `contacts(contact_id, account_id)`. Confirm those columns, unique keys and the reporting population before adapting it to your warehouse.

```sql
WITH orders_by_account AS (
    SELECT account_id, SUM(amount_usd) AS revenue_usd
    FROM orders
    GROUP BY account_id
), contacts_by_account AS (
    SELECT account_id, COUNT(*) AS contact_count
    FROM contacts
    GROUP BY account_id
)
SELECT
    a.account_id,
    COALESCE(o.revenue_usd, 0) AS revenue_usd,
    COALESCE(c.contact_count, 0) AS contact_count
FROM accounts AS a
LEFT JOIN orders_by_account AS o
    ON a.account_id = o.account_id
LEFT JOIN contacts_by_account AS c
    ON a.account_id = c.account_id
ORDER BY a.account_id;
```

The expected output is A: USD 150 and two contacts; B: USD 80 and one; C: USD 0 and zero. The zero is justified by the example's complete account population and validated input. A missing order amount is not automatically zero revenue, and an orphan order should fail reconciliation rather than disappear from the account spine unnoticed.

Apply equivalent date, currency and status rules to both the reference and candidate query. If the business definition requires net revenue, refunds and credits belong in that definition before optimization. Our [data-model design guide](https://www.martechdevs.com/blog/data-warehouse-design) explains grain and relationships; [dbt data tests](https://www.martechdevs.com/blog/dbt-tests) can keep the chosen cardinality from drifting after deployment.

This example demonstrates a logical repair. Whether it is faster for your warehouse must be measured on representative data; SQL readability alone does not prove an execution plan.

## How do you tell whether a table scan is excessive?

Relate the scan to the requested population. Reading most partitions can be reasonable for a complete historical aggregate and wasteful for a narrow customer lookup. Inspect the table-scan predicate, selected columns, partitions and bytes alongside the result's intended scope.

Snowflake uses micro-partition metadata to prune data that cannot satisfy applicable predicates. Pruning depends on the data's organization and the supported predicate, not merely the presence of a `WHERE` clause. [Micro-partitions and pruning](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions)

For an illustrative scan with 8,000 partitions, reading 7,600 means 95% were scanned. A corrected predicate that reads 400 scans 5%. That is evidence of improved pruning for that table, not a promise of a 19-fold speedup: other operators and overhead remain.

Before buying a storage optimization, inspect these basics:

- The filter targets the intended stored value and uses compatible data types.
- The date window matches the metric; narrowing it has not removed required history.
- Unneeded columns are not flowing into expensive later operations.
- A filter has not been placed after a transformation that prevents useful early reduction.
- The measured query represents the production workload rather than one unusually selective value.

Automatic clustering, search optimization and materialized views solve different access patterns. Search optimization targets selective lookups and requires Enterprise Edition or higher; materialized results and clustering add their own maintenance considerations. Evaluate the relevant feature only after measuring the workload. [Snowflake storage strategies](https://docs.snowflake.com/en/user-guide/performance-query-storage)

Do not add a cluster key to every marketing table. A small table or infrequent query may gain little while adding maintenance work. Record the frequency, input growth and measurable service requirement before deciding the extra structure is worth operating.

## Does remote spilling always mean the warehouse is too small?

Significant spilling is a reason to investigate memory pressure, but the cause may be unnecessary intermediate data. Check the join and aggregation grain before purchasing more memory to process the wrong rowset.

Snowflake spills to local storage when available memory is insufficient, and can then spill to remote storage when more capacity is needed. Larger warehouses or smaller processing batches can help. However, Query Acceleration Service can produce a small nonzero remote-storage value even without the memory problem suggested by a large spill. Interpret the amount and operator evidence together. [Memory spillage and the QAS caveat](https://docs.snowflake.com/en/user-guide/performance-query-warehouse-memory)

For example, a query producing forty million unnecessary intermediate rows has a different repair from a necessary forty-million-row sort. In the first case, correct the data shape. In the second, test whether reduced row width, an appropriate batch boundary or a larger warehouse meets the requirement more efficiently.

Avoid replacing one large job with many smaller jobs without checking the aggregate result. Distinct counts, windows and cross-batch relationships may not combine by simple addition. A batch design needs an explicit method for preserving those semantics.

Warehouse scale-up and concurrency scaling are different decisions. Snowflake's performance guidance distinguishes resource demand within a query from several queries competing at once. A query limited by skew or insufficient parallel work may gain little from a larger warehouse. [Snowflake performance framework](https://www.snowflake.com/en/developers/guides/performance/)

## How do repeated scans and CTEs affect the repair?

Inspect the executed plan instead of assuming a common table expression is cached or materialized in a particular way. The text of a query is not a complete description of how many times a large input is processed.

If several statements repeatedly rebuild the same customer eligibility calculation, consider a maintained intermediate model. Specify its grain, refresh trigger, source completeness and failure behavior. Reusing a prepared model is valuable only when it remains fresh enough for the consumers.

Keep the trade-off visible: the repeated query cost may fall while model refresh, storage and operational work increase. Compare total work across the reporting cycle, including users who never open the dashboard and models refreshed even when no consumer needs them.

A particularly useful investigation is a dashboard that executes the same expensive base logic for every tile. Compare the common input and filters, then evaluate a shared aggregate or semantic model without changing each tile's metric. The [marketing semantic-layer guide](https://www.martechdevs.com/blog/semantic-layer-ai) helps define those shared measures before optimizing their execution.

## How should you benchmark a proposed fix fairly?

Run the same business workload with the same inputs and record the cache conditions. Compare correctness first, execution and user latency second, and measured cost over an appropriate operating window third.

Persisted result reuse can bypass repeated computation when its conditions are met. `USE_CACHED_RESULT` controls result reuse at the session level, but it does not make the warehouse's local table-data cache cold. Those are separate caches. Record how each candidate was tested; do not compare a newly executed baseline with a reused result and call the difference a SQL improvement. [Persisted results](https://docs.snowflake.com/en/user-guide/querying-persisted-results), [warehouse cache](https://docs.snowflake.com/en/user-guide/performance-query-warehouse-cache)

For an execution benchmark, use a controlled session with result reuse disabled if that is the approved test design, preserve the prior session setting and restore it afterward. Also run representative warm-cache behavior if that is how the dashboard normally operates. Do not suspend a shared production warehouse merely to manufacture a cold-cache test.

| Benchmark evidence | Keep comparable | Reject the comparison when |
| --- | --- | --- |
| Business result | Keys, grain, nulls, counts and relevant monetary totals | The candidate drops required rows or changes the definition |
| Input | Reporting window and source snapshot | One query sees fewer records or newer corrections |
| Runtime | Warehouse type/settings and concurrent demand | Resizing or scheduling changed without being part of the stated test |
| Cache | Persisted-result and local-data-cache conditions | A reused result is compared with full execution |
| Operational cost | Whole workload window and separately billed features | Only the fastest query duration is counted |

Suppose a hypothetical baseline runs for 120 seconds on a resource costing two credits/hour, while a candidate runs for 75 seconds at four credits/hour. Ignoring idle tails and other charges, the baseline consumes 0.0667 credits and the candidate 0.0833. The candidate is 37.5% faster but uses 25% more compute per run. That can be an acceptable trade-off for a deadline, but it is not a cost saving.

Use unrounded values for the comparison. Add actual idle time, retries and applicable services to the production cost assessment. The [Snowflake pricing guide](https://www.martechdevs.com/blog/snowflake-pricing) separates those cost categories.

## What should pass before an optimization is released?

Release a query change when it preserves the agreed answer and improves the measured operating requirement. Keep the previous query and its baseline so an operator can explain or reverse a regression.

![Separate non-execution delay from expensive execution operators, test one bounded repair, reject changed business results, and return to the full baseline when latency or operating cost misses the target. Release only a correct and measured improvement.](https://www.martechdevs.com/assets/blog/snowflake-query-profile.svg)

Diagram connections:

- Capture query ID input baseline and required result to Execution is the dominant delay?. 
- Execution is the dominant delay? to Investigate queue compilation or application delay: No. 
- Execution is the dominant delay? to Inspect row growth scans and memory at costly operators: Yes. 
- Investigate queue compilation or application delay to Test one bounded repair under comparable conditions. 
- Inspect row growth scans and memory at costly operators to Test one bounded repair under comparable conditions. 
- Test one bounded repair under comparable conditions to Required rows and metrics remain correct?. 
- Required rows and metrics remain correct? to Reject the change and repair its semantics: No. 
- Reject the change and repair its semantics to Test one bounded repair under comparable conditions. 
- Required rows and metrics remain correct? to Latency and full operating cost meet the target?: Yes. 
- Latency and full operating cost meet the target? to Capture query ID input baseline and required result: No. 
- Latency and full operating cost meet the target? to Release with baseline and regression monitoring: Yes

## Checklist

- [ ] The slow query ID and the actual business requirement are recorded.
- [ ] Compilation, queuing, blocking and application time are separated from execution.
- [ ] The expensive operator's upstream row shape is understood.
- [ ] Keys, nulls, complete populations and monetary totals still reconcile.
- [ ] Scan and spill evidence supports the chosen change.
- [ ] Cache, warehouse and concurrent-load conditions are recorded.
- [ ] The result holds for representative parameters and input growth.
- [ ] Cost includes the complete operating window and relevant extra services.
- [ ] An owner can detect a regression and restore the accepted version.

Bring the saved query ID, plan and reconciled output to the [$99 martech audit](https://www.martechdevs.com/pricing), or [request a scoped warehouse performance repair](https://www.martechdevs.com/data-warehouse-cdp#offer). Those artifacts make the work concrete enough to test and hand over.

## Frequently asked questions

### Why does a query have history but no usable Query Profile?

History and detailed execution evidence have different availability limits. The query may have failed before producing a profile, be outside the detailed retention window, or be hidden by your role's permissions. Some query types also have limited profiling evidence. Preserve the query ID and inspect the actual reason rather than treating the absence of a profile as proof of negligible work. [Profile availability](https://docs.snowflake.com/en/user-guide/ui-snowsight-activity)

### Is an output-to-input row increase always an exploding-join bug?

No. A deliberate one-to-many relationship can increase the row count while answering the intended question. Check the expected output grain and the keys on both sides. The warning becomes actionable when that expansion is unexplained or causes invalid measures, such as summing an order once per contact. Compare the resulting records with a known reference before rewriting the relationship.

### Can we compare query speed using only the fastest run?

A fastest run can hide queuing, cold starts, cache effects and unstable workloads. Keep the test conditions and compare several representative executions, including the conditions users actually encounter. Examine both typical and slower cases, with correctness preserved. A change that occasionally wins but causes severe regressions under normal concurrency may be worse for the business than a slightly slower, predictable query.

### Will adding DISTINCT repair duplicated revenue after a join?

Not reliably. DISTINCT removes duplicate result rows according to the selected columns, not according to your business definition of an order or account. Two legitimate transactions can share an amount, and including a contact field can keep multiplied rows distinct. Correct the join grain or aggregate the contributing measures independently, then reconcile against the original transaction population before evaluating performance.

### Should we materialize every expensive calculation?

No. Materialization adds refresh, storage and ownership work, and it can make results stale if the refresh contract is wrong. Consider it when a well-defined calculation is reused enough to justify maintaining it. Measure the whole cycle, including refreshes with no readers, and prove that late changes reach consumers within the required time. A one-off calculation may be cheaper and clearer to execute directly.

## Sources

1. [Snowflake Query History and Query Profile](https://docs.snowflake.com/en/user-guide/ui-snowsight-activity)

2. [QUERY_HISTORY fields and latency](https://docs.snowflake.com/en/sql-reference/account-usage/query_history)

3. [Snowflake Query Insights](https://docs.snowflake.com/en/user-guide/query-insights)

4. [GET_QUERY_OPERATOR_STATS](https://docs.snowflake.com/en/sql-reference/functions/get_query_operator_stats)

5. [Micro-partitions and pruning](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions)

6. [Storage optimization strategies](https://docs.snowflake.com/en/user-guide/performance-query-storage)

7. [Memory spillage and QAS](https://docs.snowflake.com/en/user-guide/performance-query-warehouse-memory)

8. [Snowflake performance framework](https://www.snowflake.com/en/developers/guides/performance/)

9. [Persisted query-result reuse](https://docs.snowflake.com/en/user-guide/querying-persisted-results)

10. [Warehouse data cache](https://docs.snowflake.com/en/user-guide/performance-query-warehouse-cache)