Useful Snowflake Queries
last modified: | 1 min readCheck if two tables are equal
Snowflake has two hash functions: HASH and HASH_AGG. These aren’t cryptographic hashes but a way go group/compare values.
To compare two tables:
select hash_agg(*) = (select hash_agg(*) from x) from y;
Check how many times colums from a table have been used
select
obj:objectName::string as object_name,
col:columnName::string as column_name,
count(*) as uses,
min(query_start_time) as since,
max(query_start_time) as until
from snowflake.account_usage.access_history,
table(flatten(direct_objects_accessed)).value obj,
table(flatten(obj.vale:columns)).value col
where starts_with(object_name, "<prefix of the object you want to inspect>")
group by 1, 2
order by uses desc
Refresh external table
alter external table <external-table> refresh;