Back to Utils
Snowflake · View
clustering_cost
Daily automatic clustering credit consumption per table, with the role and user that enabled it. enabled_by_role and enabled_by_user are derived from the most recent ALTER TABLE ... CLUSTER BY in ACCESS_HISTORY.
Source: SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY + ACCESS_HISTORY + QUERY_HISTORY · Latency: ~3h
| Column | Description |
|---|---|
| day | Date |
| database_name | Database of the clustered table |
| schema_name | Schema of the clustered table |
| table_name | Table name |
| enabled_by_role | Role that ran the CLUSTER BY |
| enabled_by_user | User that ran the CLUSTER BY |
| credits_used | Credits consumed that day |
| bytes_reclustered | Bytes rewritten during clustering |
| rows_reclustered | Rows rewritten during clustering |
| clustering_runs | Number of clustering jobs that day |
Source code
create_admin_views.sql
create or replace view clustering_cost
comment = 'Daily automatic clustering credit consumption per table, including the role and user that enabled it (source: SNOWFLAKE.ACCOUNT_USAGE)'
as
with enabler as (
select split_part(replace(om.value:objectName::string, '"', ''), '.', 1) as database_name
, split_part(replace(om.value:objectName::string, '"', ''), '.', 2) as schema_name
, split_part(replace(om.value:objectName::string, '"', ''), '.', 3) as table_name
, qh.role_name
, ah.user_name
from snowflake.account_usage.access_history ah
join snowflake.account_usage.query_history qh
on qh.query_id = ah.query_id
, lateral flatten(ah.objects_modified) om
where om.value:objectDomain::string ilike 'Table'
and qh.query_type = 'ALTER'
and qh.query_text ilike '%cluster by%'
qualify row_number() over (partition by database_name
, schema_name
, table_name
order by qh.start_time desc) = 1)
select date_trunc('day', h.start_time)::date as day
, h.database_name
, h.schema_name
, h.table_name
, e.role_name as enabled_by_role
, e.user_name as enabled_by_user
, sum(h.credits_used) as credits_used
, sum(h.num_bytes_reclustered) as bytes_reclustered
, sum(h.num_rows_reclustered) as rows_reclustered
, count(*) as clustering_runs
from snowflake.account_usage.automatic_clustering_history h
left join enabler e
on upper(e.database_name) = upper(h.database_name)
and upper(e.schema_name) = upper(h.schema_name)
and upper(e.table_name) = upper(h.table_name)
group by 1, 2, 3, 4, 5, 6;