Snowflake

Step 1: Create a user and role for Datafold
It is best practice to create a separate role for the Datafold integration. In this guide, we will refer to the role as DATAFOLDROLE :
1
CREATE ROLE DATAFOLDROLE;
2
CREATE USER DATAFOLD DEFAULT_ROLE = "DATAFOLDROLE" MUST_CHANGE_PASSWORD = FALSE;
3
GRANT ROLE DATAFOLDROLE TO USER DATAFOLD;
Copied!
To provide column-level lineage, Datafold needs to read & parse all SQL statements executed in your Snowflake account:
1
GRANT MONITOR EXECUTION ON ACCOUNT TO ROLE DATAFOLDROLE;
2
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE DATAFOLDROLE;
Copied!
Step 2a: Use password based authentication
Datafold supports username/password authentication, but also key-pair authentication.
1
ALTER USER DATAFOLD SET PASSWORD = 'SomethingSecret';
Copied!
You can set the username/password in the Datafold web UI.
Step 2b: Use key-pair authentication
If you want to use key-pair authentication, please follow the steps of Snowflake. The public key will be set to the Snowflake user:
1
ALTER USER datafold SET rsa_public_key='abc..'
Copied!
The private key needs to be uploaded to Datafold, and the optional passphrase of the private-key can be set to the user.
Step 3: Create schema for Datafold
Datafold requires a schema that is being used as a scratch surface for performance, and this allows us to keep the data processing inside of the DWH, and only fetch the results back to Datafold.
1
CREATE SCHEMA MYDATABASE.DATAFOLD_TMP;
2
GRANT ALL ON SCHEMA MYDATABASE.DATAFOLD_TMP TO DATAFOLDROLE;
Copied!
This is the only schema that Datafold needs write access to.
Step 4: Give the Datafold role access to your Data warehouse
Datafold will only scan the tables that it has access to. The snippet below will give Datafold read access to a database. Make sure to grant access to all the databases that you want to use in Datafold.
1
/* Repeat for every DATABASE to be usable in Datafold. This allows Datafold to
2
correctly discover, profile & diff each table */
3
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE DATAFOLDROLE;
4
GRANT USAGE ON DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
5
​
6
GRANT USAGE ON ALL SCHEMAS IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
7
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
8
​
9
GRANT SELECT ON ALL TABLES IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
10
GRANT SELECT ON FUTURE TABLES IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
11
​
12
GRANT SELECT ON ALL VIEWS IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
13
GRANT SELECT ON FUTURE VIEWS IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
14
​
15
GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
16
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE MYDATABASE TO ROLE DATAFOLDROLE;
Copied!
​

Redshift

BigQuery

Last modified 1mo ago

Copy link