Useful Snowflake Queries

last modified: | 1 min read

Check 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;