Semantic View to monitor the Snowflake Credit spend

SQL Show and Tell
1

Snowflake recently announced Semantic Views. Semantic Views help in defining schema and relationships for your data.

Here is a Semantic View over the Snowflake account_usage schema to get cost of queries that get executed multiple times as an aggregated metric

create or replace semantic view query_credits 
  TABLES( 
    query_history as snowflake.account_usage.query_history PRIMARY KEY (query_id)
    , query_attribution_history as snowflake.account_usage.query_attribution_history PRIMARY KEY (query_id)
  )
  RELATIONSHIPS (
    query_attribution_history (query_id) references query_history

  )
  DIMENSIONS (
    query_history.query_parameterized_hash as query_parameterized_hash
    , query_history.query_date as start_time::date
  )
  METRICS (
    query_history.number_of_times_executed as count(*)
    , query_history.executed_query as any_value(query_text)
    , query_attribution_history.total_credits_spent as sum(credits_attributed_compute)
    , query_attribution_history.average_credits_spent as avg(credits_attributed_compute)
    , query_history.average_bytes_spilled_to_remote_storage as avg(bytes_spilled_to_remote_storage
  )
;

Once defined, this Semantic View can be queried as following.

Let’s say you are interested in queries that got executed on a certain date and the number of times they got executed and the credit spend on those:

create or replace semantic view query_credits 
  TABLES( 
    query_history as snowflake.account_usage.query_history PRIMARY KEY (query_id)
    , query_attribution_history as snowflake.account_usage.query_attribution_history PRIMARY KEY (query_id)
  )
  RELATIONSHIPS (
    query_attribution_history (query_id) references query_history

  )
  DIMENSIONS (
    query_history.query_parameterized_hash as query_parameterized_hash
    , query_history.query_date as start_time::date
    , query_history.warehouse_size as warehouse_size
  )
  METRICS (
    query_history.number_of_times_executed as count(*)
    , query_history.executed_query as any_value(query_text)
    , query_attribution_history.total_credits_spent as sum(credits_attributed_compute)
    , query_attribution_history.average_credits_spent as avg(credits_attributed_compute)
    , query_history.average_bytes_spilled_to_remote_storage as avg(bytes_spilled_to_remote_storage)
  )
;

Now let’s say you are interested in getting the total credit cost for each query since the start of the month:

SELECT * FROM SEMANTIC_VIEW(
  query_credits
    DIMENSIONS 
      query_parameterized_hash
    METRICS 
         executed_query
         , number_of_times_executed
         , average_credits_spent
         , total_credits_spent
         , average_bytes_spilled_to_remote_storage
)
where 1=1
  and average_credits_spent>0
  and query_date => '2025-06-01'
  --and executed_query ilike '%userbase%'
order by 2 desc
;

Note that you have to do is remove the query_date from the dimension.

Now let’s say you want to know what Warehouse Size each of these queries is using. You can simply add the warehouse_size to the DIMENSIONS clause.

SELECT * FROM SEMANTIC_VIEW(
  query_credits
    DIMENSIONS 
      query_parameterized_hash
      , query_date
      , warehouse_size
    METRICS 
         executed_query
         , number_of_times_executed
         , average_credits_spent
         , total_credits_spent
         , average_bytes_spilled_to_remote_storage
)
where 1=1
  and average_credits_spent>0
  and query_date = '2025-06-20'
  --and executed_query ilike '%userbase%'
order by 2 desc
;

Have you tried the Semantic Views in Snowflake?

  • Yes
  • No
  • What are Semantic Views?
0 voters