Back to Utils
Snowflake · View
search_optimization_cost
Daily search optimization 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 ... ADD SEARCH OPTIMIZATION in ACCESS_HISTORY.
Source: SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY + ACCESS_HISTORY + QUERY_HISTORY · Latency: ~3h
| Column | Description |
|---|---|
| day | Date |
| database_name | Database of the optimised table |
| schema_name | Schema of the optimised table |
| table_name | Table name |
| status | ACTIVE or DISABLED |
| enabled_by_role | Role that ran the enabling ALTER TABLE |
| enabled_by_user | User that ran the enabling ALTER TABLE |
| credits_used | Credits consumed that day |
| maintenance_runs | Number of maintenance jobs that day |
Source code
create_admin_views.sql
create or replace view search_optimization_cost
comment = 'Daily search optimization 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 '%search optimization%'
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
, h.status
, e.role_name as enabled_by_role
, e.user_name as enabled_by_user
, sum(h.credits_used) as credits_used
, count(*) as maintenance_runs
from snowflake.account_usage.search_optimization_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, 7;