Query Profiling and Cost Attribution

Performance work in Snowflake follows one loop: find the query, read its profile, identify the dominant operator, fix that. Guessing at SQL rewrites without the profile wastes more time than the query does.

Finding the query

select
    query_id,
    left(query_text, 60) as sql_text,
    user_name,
    warehouse_size,
    total_elapsed_time / 1000 as seconds,
    bytes_scanned / power(1024, 3) as gb_scanned,
    bytes_spilled_to_remote_storage / power(1024, 3) as gb_spilled_remote
from snowflake.account_usage.query_history
where start_time >= dateadd('day', -1, current_timestamp())
  and execution_status = 'SUCCESS'
order by total_elapsed_time desc
limit 5;

+--------------------------------------+--------------------------------+-----------+----------------+---------+------------+-------------------+
| QUERY_ID                             | SQL_TEXT                       | USER_NAME | WAREHOUSE_SIZE | SECONDS | GB_SCANNED | GB_SPILLED_REMOTE |
|--------------------------------------+--------------------------------+-----------+----------------+---------+------------+-------------------|
| 01b2c3d4-0000-a1b2-0000-c3d400001a2b | select c.country_code, o.ord... | ETL_SVC   | Large          | 884.204 |     412.60 |             18.44 |
| 01b2c3d4-0000-a1b2-0000-c3d400001a3c | select * from orders_large w... | ANALYST_1 | Small          | 402.118 |     412.60 |              0.00 |
| 01b2c3d4-0000-a1b2-0000-c3d400001a4d | with monthly as (select dat...  | BI_SVC    | Medium         | 288.401 |      88.02 |              2.10 |
+--------------------------------------+--------------------------------+-----------+----------------+---------+------------+-------------------+
3 Row(s) produced. Time Elapsed: 1.204s

Three different problems in three rows. The first spills 18 GB to remote storage. The second scans the whole table on a Small warehouse. The third spills a little and is probably fine.

The profile

select
    operator_id,
    operator_type,
    operator_statistics:input_rows::number   as input_rows,
    operator_statistics:output_rows::number  as output_rows,
    execution_time_breakdown:overall_percentage::number as pct_time
from table(get_query_operator_stats('01b2c3d4-0000-a1b2-0000-c3d400001a2b'))
order by pct_time desc
limit 6;

+-------------+---------------+-------------+--------------+----------+
| OPERATOR_ID | OPERATOR_TYPE | INPUT_ROWS  | OUTPUT_ROWS  | PCT_TIME |
|-------------+---------------+-------------+--------------+----------|
|           4 | Join          |  4212000000 | 184920000000 |       71 |
|           2 | TableScan     |  4212000000 |   4212000000 |       18 |
|           5 | Aggregate     | 184920000000|           36 |        8 |
|           3 | TableScan     |      412000 |       412000 |        2 |
|           1 | Result        |          36 |           36 |        1 |
+-------------+---------------+-------------+--------------+----------+
5 Row(s) produced. Time Elapsed: 0.688s

The Join takes 71% of the time and — read the row counts — emits 44× more rows than it consumed. 4.2 billion in, 185 billion out. That is an exploding join, and no warehouse size fixes it.

The cause is almost always a join key that is not as unique as assumed:

select customer_id, count(*) as n
from customers
group by 1 having count(*) > 1
order by n desc limit 3;

+-------------+----+
| CUSTOMER_ID | N  |
|-------------+----|
|      481920 | 44 |
|      118204 | 41 |
|       99120 | 38 |
+-------------+----+
3 Row(s) produced. Time Elapsed: 0.402s

The dimension table has duplicates — an SCD table queried without a current-row filter, most likely. Fixing the join fixes the query:

join (select * from customers where dbt_valid_to is null) c
    on c.customer_id = o.customer_id

+-------------+---------------+-------------+-------------+----------+
| OPERATOR_ID | OPERATOR_TYPE | INPUT_ROWS  | OUTPUT_ROWS | PCT_TIME |
|-------------+---------------+-------------+-------------+----------|
|           2 | TableScan     |  4212000000 |  4212000000 |       61 |
|           4 | Join          |  4212000000 |  4212000000 |       22 |
|           5 | Aggregate     |  4212000000 |          36 |       14 |
+-------------+---------------+-------------+-------------+----------+

884.204s → 41.882s

Rows in equals rows out at the join, and the scan is now the dominant cost — which is a pruning problem, and the previous lesson’s territory.

The operators worth recognising

Spilling

select
    query_id,
    bytes_spilled_to_local_storage  / power(1024,3) as local_gb,
    bytes_spilled_to_remote_storage / power(1024,3) as remote_gb,
    total_elapsed_time / 1000 as seconds
from snowflake.account_usage.query_history
where start_time >= dateadd('day', -1, current_timestamp())
  and bytes_spilled_to_remote_storage > 0
order by bytes_spilled_to_remote_storage desc limit 3;

+--------------------------------------+----------+-----------+---------+
| QUERY_ID                             | LOCAL_GB | REMOTE_GB | SECONDS |
|--------------------------------------+----------+-----------+---------|
| 01b2c3d4-0000-a1b2-0000-c3d400001a2b |    88.14 |     18.44 | 884.204 |
| 01b2c3d4-0000-a1b2-0000-c3d400001a4d |    12.02 |      2.10 | 288.401 |
+--------------------------------------+----------+-----------+---------+

Local spilling is tolerable; remote spilling is catastrophic, often accounting for most of the runtime. Three fixes in order of preference: reduce the data (better filters, fewer columns), remove the explosion (the join fix above), then size up.

The three caches

Result cache      services layer, 24h, exact query match, zero compute
Local disk cache  warehouse SSD, lost on suspend/resize
Remote storage    the data itself

Benchmark honestly by disabling the first and warming or clearing the second:

alter session set use_cached_result = false;
alter warehouse bookshop_wh suspend;    -- clears local disk cache
alter warehouse bookshop_wh resume;

-- cold:  Time Elapsed: 44.204s
-- warm:  Time Elapsed:  8.118s
-- result cache: Time Elapsed: 0.043s

Three legitimate numbers for the same query. Comparing an optimisation’s “before” on a cold cache with its “after” on a warm one is the most common way people convince themselves a rewrite worked.

Attributing cost

Warehouse-level billing stops being useful when one warehouse serves several teams. Tag the work:

alter session set query_tag = '{"team":"finance","job":"monthly_close","env":"prod"}';

select country_code, sum(amount) from orders_large join customers using (customer_id) group by 1;

select
    parse_json(q.query_tag):team::string as team,
    count(*)                             as queries,
    round(sum(a.credits_attributed_compute), 2) as credits
from snowflake.account_usage.query_attribution_history a
join snowflake.account_usage.query_history q using (query_id)
where q.start_time >= dateadd('day', -7, current_timestamp())
  and q.query_tag is not null
group by 1
order by credits desc;

+-----------+---------+---------+
| TEAM      | QUERIES | CREDITS |
|-----------+---------+---------|
| finance   |    1204 |  188.44 |
| marketing |    8812 |   94.02 |
| data-eng  |   41209 |   62.18 |
+-----------+---------+---------+
3 Row(s) produced. Time Elapsed: 1.688s

Finance runs 1,204 queries and spends three times what data engineering spends on 41,209. That is the conversation worth having, and it is impossible to have without tags. Set the tag in your BI tool’s connection, in your dbt profile, and at the top of every scheduled job.

Cost patterns worth auditing

select
    warehouse_name,
    count(*) as query_count,
    round(sum(total_elapsed_time) / 1000 / 60, 1) as query_minutes,
    round(sum(credits_used_cloud_services), 2) as cloud_svc_credits
from snowflake.account_usage.query_history
where start_time >= dateadd('day', -7, current_timestamp())
group by 1
order by query_minutes desc;

+----------------+-------------+---------------+-------------------+
| WAREHOUSE_NAME | QUERY_COUNT | QUERY_MINUTES | CLOUD_SVC_CREDITS |
|----------------+-------------+---------------+-------------------|
| ETL_WH         |        4120 |        1884.4 |             12.04 |
| REPORTING_WH   |      182044 |         402.1 |            188.44 |
| BOOKSHOP_WH    |         881 |          88.2 |              2.18 |
+----------------+-------------+---------------+-------------------+

REPORTING_WH burns 188 cloud-services credits on 182,000 queries — a dashboard polling far too often. Cloud services are free only up to 10% of daily compute; past that they bill, and metadata-heavy workloads like SHOW and INFORMATION_SCHEMA in a loop are the usual cause.

Two more habitual wins:

-- UNION deduplicates and sorts; UNION ALL does not
select ... union all select ...

-- ORDER BY in a subquery or a table-building CTAS is discarded work
create table t as select ... order by x;    -- pointless: storage has no order

Materialising a repeated aggregation

When the same expensive aggregate is queried all day, compute it once:

create or replace materialized view mv_daily_revenue as
select ordered_at, country_code, sum(amount) as revenue, count(*) as orders
from orders_large
group by 1, 2;

+-----------------------------------------------------+
| status                                              |
|-----------------------------------------------------|
| Materialized view MV_DAILY_REVENUE successfully created. |
+-----------------------------------------------------+
1 Row(s) produced. Time Elapsed: 88.204s

Snowflake maintains it in the background and — the useful part — rewrites qualifying queries against the base table to use it, even if they never mention it:

-- select ordered_at, sum(amount) from orders_large group by 1
-- before: 44.204s, 412 GB scanned
-- after:   0.882s,   0.04 GB scanned

Maintenance costs credits on every write to the base table, so they suit slowly changing tables with heavy read traffic. They also have real limits — no joins, no window functions, one table only. When those bite, dynamic tables are the answer, and they are the next lesson.

Practice

+-------------+---------------+------------+-------------+----------+
| OPERATOR_ID | OPERATOR_TYPE | INPUT_ROWS | OUTPUT_ROWS | PCT_TIME |
|-------------+---------------+------------+-------------+----------|
|           4 | Join          | 4212000000 | 184920000000|       71 |
+-------------+---------------+------------+-------------+----------+

| Join | 4212000000 | 184920000000 | 71 |

44.204s → 8.118s → 0.043s

alter session set query_tag = '{"team":"analytics","job":"adhoc"}';

+-----------+---------+---------+
| TEAM      | QUERIES | CREDITS |
|-----------+---------+---------|
| analytics |      42 |    8.02 |
+-----------+---------+---------+

Next: dynamic tables — declarative pipelines that refresh themselves.