Setting up Pushdown for a Snowflake connection

This page shows you how to set up Pushdown for a Snowflake connection.

Running the Snowflake setup script

To run a Snowflake Pushdown job, you need to opt in when setting up your Snowflake connection. Select the Pushdown option in the Connection template to turn on Pushdown capabilities.

Step Details
1 Open a terminal session.
2

Update the following session variables:

Copy 
set dq_username='SERVICE_ACCOUNT_USER';
set dq_password='SERVICE_ACCOUNT_PASSWORD';
set dq_warehouse_name='COLLIBRA_DQ_WH';
set dq_warehouse_size='XSMALL';
set user_database='TARGET_DB';
set dq_role_name='COLLIBRA_DQ_ROLE';

Note dq_password must be uppercase.

Warning Do not update the variables for Collibra DQ.
3

Run as an admin user.

Copy 
USE ROLE ACCOUNTADMIN;
4

Create a user and role for Collibra DQ.

Copy 
CREATE ROLE IF NOT EXISTS identifier($dq_role_name);
CREATE USER IF NOT EXISTS identifier($dq_username) PASSWORD=$dq_password DEFAULT_ROLE=$dq_role_name;
GRANT ROLE identifier($dq_role_name) TO USER identifier($dq_username);
5

Create a virtual warehouse to run Collibra DQ. The virtual warehouse is referenced in the connection URL to direct DQ traffic to it.

Copy 
CREATE WAREHOUSE IF NOT EXISTS identifier($dq_warehouse_name) WAREHOUSE_SIZE=$dq_warehouse_size INITIALLY_SUSPENDED=TRUE
AUTO_SUSPEND = 5 AUTO_RESUME = TRUE;
6

Assign privileges to the Collibra DQ warehouse.

Copy 
GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($dq_warehouse_name) TO ROLE identifier($dq_role_name);
7

Assign metadata access to your Collibra DQ role.

Copy 
GRANT USAGE,MONITOR on DATABASE identifier($user_database) to identifier($dq_role_name);
GRANT USAGE,MONITOR ON ALL SCHEMAS IN DATABASE identifier($user_database) to identifier($dq_role_name);
8

Update the session variable user_database and grant read access to objects in the user database. Run this portion for each target database within which you want to run DQ checks.

Copy 
USE DATABASE identifier($user_database);
GRANT SELECT ON ALL TABLES IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
GRANT SELECT ON ALL STREAMS IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
                                ​
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
GRANT SELECT ON FUTURE EXTERNAL TABLES IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);
GRANT SELECT ON FUTURE STREAMS IN DATABASE identifier($user_database) TO ROLE identifier($dq_role_name);

Warning Please ensure the SQL variables are updated in the above script before proceeding.

Setting up the Snowflake Connection template

On the main menu, click , and then click Connections. Click Add on the Snowflake tile, then enter the required information.

Required Property Type Description

Yes

Name Text The unique name of your Snowflake connection.

Yes

Connection URL String The base connection string, for example, jdbc:snowflake://<account>.snowflakecomputing.com/?db=SNOWFLAKE_SAMPLE_DATA&warehouse=COLLIBRA_DQ_WH

Yes

Port Integer The port number to establish a connection to the datasource.

Yes

Driver Name Text The driver class name used for your connection, for example, com.snowflake.client.jdbc.SnowflakeDriver

No

Source Name String N/A

No

Target Agent Option

The Agent used to submit your DQ Job.

You do not need to select an option because Pushdown Jobs do not require an Agent to submit them to the data warehouse.

Yes

Auth Type Option The method to authenticate your connection. See the Snowflake documentation for more information about authentication methods for Snowflake connections.

Yes

Pushdown Checkbox

Switches Pushdown processing on or off.

Select the checkbox option to turn on Pushdown for your Snowflake connection.

Warning You must select this option for Pushdown to function.

Yes

Driver Location File path A file path which designates the name and location of the driver.

Yes

Driver Properties String

The configurable driver properties for your connection. Add the following driver properties:

CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE;QUOTED_IDENTIFIERS_IGNORE_CASE=FALSE

The following image shows an example of a Snowflake connection with Pushdown turned on and additional Driver Properties.

screenshot of a sample snowflake pushdown connection with "pushdown" and "driver properties" highlighted