Introducing PopSQL + dbt. The first SQL editor with built-in dbt support.Read more →

Snowflake Data Catalog

As part of PopSQL's data catalog, we periodically poll your Snowflake query history to give you a complete picture of which tables/columns are popular.

Grant the MONITOR privilege

Your Snowflake user will need the MONITOR privilege in order to look at queries run by all users (source). Please run this in the Snowflake UI:

begin;
  -- create variables
  set role_name = 'MONITOR';
  set user_name = 'YOUR_USER_NAME';
  set warehouse_name = 'YOUR_WAREHOUSE_NAME';

  -- change role to securityadmin since we're doing role/user stuff
  use role securityadmin;

  -- create role
  create role identifier($role_name);

  -- grant the `monitor` privilege to the role
  grant monitor on warehouse identifier($warehouse_name) to role identifier($role_name);

  -- verify the grant to the role worked
  show grants to role identifier($role_name);

  -- grant the role to the user
  grant role identifier($role_name) to user identifier($user_name);

  -- verify the grant to the user worked
  show grants to user identifier($user_name);
commit;

Historical data

By default, Snowflake will return the last 7 days of query history. To get historical data up to 1 year ago, your Snowflake user will need access to the QUERY_HISTORY view, which lives in the SNOWFLAKE database (source). A Snowflake account admin can grant access to the SNOWFLAKE database by running this in the Snowflake UI:

begin;
  -- create variables
  set role_name = 'MONITOR';

  -- Only accountadmins can grant this access
  -- https://docs.snowflake.com/en/sql-reference/account-usage.html#enabling-account-usage-for-other-roles
  use role accountadmin;

  -- grant the privilege
  grant imported privileges on database snowflake to role identifier($role_name);

  -- test that the role can access the snowflake database
  use role identifier($role_name);
  select * from snowflake.account_usage.databases;
commit;

Frequency

We'll poll your Snowflake query history every 12 hours to update our data catalog. If you'd like to tune this for your account, please contact us.

Ready for a modern SQL editor?