Snowflake

Create a user and role for Datachecks

It is best practice to create a separate role for the Datachecks integration (e.g., DATACHECKSOLE):

A Full script can be found at the bottom of this page..

   /* Create a Datachecks role */
   CREATE ROLE DATACHECKSROLE;

   /* Create a Datachecks user and assign the default role */
   CREATE USER DATACHECKS 
   DEFAULT_ROLE = "DATACHECKSROLE" 
   MUST_CHANGE_PASSWORD = FALSE;

   /* Grant the role to the Datachecks user */
   GRANT ROLE DATACHECKSROLE TO USER DATACHECKS;

To provide column-level lineage, Datachecks needs to read & parse all SQL statements executed in your Snowflake account:

GRANT MONITOR EXECUTION ON ACCOUNT TO ROLE DATACHECKSROLE;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE DATACHECKSROLE;

Set up password-based authentication

Datachecks supports username/password authentication.

ALTER USER DATACHECKS SET PASSWORD = 'Yourstrongpassword';

You can set the username/password in the Datachecks web UI.

Create schema for Datachecks

Datachecks utilizes a temporary dataset to materialize scratch work and keep data processing in the your warehouse.

CREATE SCHEMA <database_name>.DATACHECKS_TMP;
GRANT ALL ON SCHEMA <database_name>.DATACHECKS_TMPTO DATACHECKSROLE;

Give the Datachecks role access

Datachecks will only scan the tables that it has access to. The snippet below will give Datachecks read access to a database. If you have more than one database that you want to use in Datachecks, rerun the script below for each one.

/* Repeat for every DATABASE to be usable in Datachecks. This allows Datachecks to
correctly discover, profile & compare each table */
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE DATACHECKSROLE;
GRANT USAGE ON DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE DATACHECKSROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT SELECT ON ALL VIEWS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT ALL PRIVILEGES ON ALL DYNAMIC TABLES IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

Full Script

--Step 1: Create a user and role for Datachecks
CREATE ROLE DATACHECKSROLE;
CREATE USER DATACHECKSROLE DEFAULT_ROLE = "DATACHECKSROLE" MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE DATACHECKSROLE TO USER DATACHECKS;

GRANT MONITOR EXECUTION ON ACCOUNT TO ROLE DATACHECKSROLE;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE DATACHECKSROLE;

--Step 2a: Use password-based authentication
ALTER USER DATACHECKS SET PASSWORD = 'Yourstrongpassword';

--ALTER USER DATACHECKS SET rsa_public_key='abc..'

--Step 3: Create schema for Datachecks
CREATE SCHEMA <database_name>.DATACHECKS_TMP;
GRANT ALL ON SCHEMA <database_name>.DATACHECKS_TMPTO DATACHECKSROLE;

--Step 4: Give the Datachecksrole access to your data connection
/*
Repeat for every DATABASE to be usable in Datachecks. This allows Datachecks to
correctly discover, profile & compare each table
*/
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE DATACHECKSROLE;
GRANT USAGE ON DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE DATACHECKSROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

GRANT SELECT ON ALL VIEWS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE <database_name> TO ROLE DATACHECKSROLE;

Add Snowflake as a data source in Datachecks

Go to Settings > Datasources, click New Datasource, and select Snowflake to configure the connection to your database.

Field Name	Description
Warehouse	The Snowflake virtual warehouse used for query execution.
Account Identifier	The unique identifier for your Snowflake account.
Username	The dedicated user for Datachecks.
Password	The password for the Datachecks user.
Database	The Snowflake database you want to connect to.
Role	The role assigned to the Datachecks user.
Schema	Specify the schema you want to access (optional).
Connection Name	A custom name for the data connection within Datachecks.

Click Test & Save Datasource. Your data connection will be established.