There are two main methods to pull query history in Snowflake, one is using the information schema query history and the other is using the account usage. The following table describes the differences between the sources:
Query history source
Up to 7 days
Up to 1 year
Up to 45 minutes
Access to shared 'Snowflake' database (default only for account admin)
Pulling the query history from the information schema is our default. There is no delay and new queries will be shown in the lineage graph right away.
To generate a lineage graph that is based on queries the account usage, use the query_history_source parameter in the connection profile, as you can see in the example below, and type 'account_usage' as its value.
If these are not granted, Elementary will create partial lineage only based on accessible queries, or will return an error if there is no access at all.
Snowflake connection profile
The profiles.yml file to connect to Snowflake should contain a profile in the following format:
# example Snowflake profiles.yml file
## profile name, replace 'my_profile' with a name of your choice ##
## User/password auth, other options (Keypair/SSO) require other configs ##
## Schema is used as filter by default, you can use the 'ignore schema' option to see cross schema lineage
## OPTIONAL - if you want to create the lineage based on queries from more
## than the last 7 days or you have 10k or more queries in the history pulled during
## the requested dates range, add this parameter (NOTE: account_usage requires more permissions, see note below).
We support the same format and connection methods (user password, key pair authentication, SSO) as dbt. Please refer to dbt's documentation of Snowflake profile for further details.
The queries are pulled by default from the information schema, and you can use the 'query_history_source' parameter in the connection profile to change it to the account admin.
Each source requires different permissions:
Information schema permissions:
In order to create a lineage graph that includes the tables that are updated and created by all the users and services in Snowflake, the role of the user in the connection profile needs to have the right privileges.
For full lineage of up to 7 days back (depends on tier), the role of the user should have monitor privilege to the warehouse.
The following command in Snowflake grants this role:
grant monitor on warehouse <warehouse>to role <your_role>;
Note that granting monitor privilege on a warehouse does not provide access to all the data, just to the executed queries (without the results).
Account usage permissions:
For full lineage of up to a year back, the role of the user should have permission to the 'snowflake' database, here is the relevant command for Snowflake:
use role accountadmin;
## Grant the role privileges to the snowflake database
## Note that this will not grant full ACCOUNTADMIN privileges
grant imported privilegesondatabase snowflake to role <your_role>;