Snowflake
Snowflake integration details

Snowflake query history

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
Information schema
Account usage
Time limit
Up to 7 days
Up to 1 year
Queries limit
10,000
None
Delay
None
Up to 45 minutes
Permmisions
Monitor warehouse
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.
Note that each query source requires different permissions.
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:
1
# example Snowflake profiles.yml file
2
3
## profile name, replace 'my_profile' with a name of your choice ##
4
my_profile:
5
6
outputs:
7
default:
8
type: snowflake
9
account: [account id]
10
11
## User/password auth, other options (Keypair/SSO) require other configs ##
12
13
user: [username]
14
password: [password]
15
16
role: [user role]
17
database: [database name]
18
warehouse: [warehouse name]
19
## Schema is used as filter by default, you can use the 'ignore schema' option to see cross schema lineage
20
schema: [schema name]
21
22
## OPTIONAL - if you want to create the lineage based on queries from more
23
## than the last 7 days or you have 10k or more queries in the history pulled during
24
## the requested dates range, add this parameter (NOTE: account_usage requires more permissions, see note below).
25
query_history_source: account_usage
Copied!
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.

Snowflake permissions

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:
1
grant monitor on warehouse <warehouse> to role <your_role>;
Copied!
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:
1
use role accountadmin;
2
## Grant the role privileges to the snowflake database
3
## Note that this will not grant full ACCOUNTADMIN privileges
4
grant imported privileges on database snowflake to role <your_role>;
5
6
## Validate that it worked:
7
select * from snowflake.account_usage.databases LIMIT 5;
Copied!
Last modified 7d ago