Snowflake

Required connection details

The following details are required per target instance configuration:

-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIE6T...
-----END ENCRYPTED PRIVATE KEY-----

Configuration

To connect Agile Data Engine to Snowflake, you must set up specific Snowflake objects and assign appropriate privileges. These include creating users, roles, databases, warehouses, and optionally storage integrations or external stages. The instructions below assume default Snowflake role configurations; if your setup uses custom roles or naming conventions, adjust accordingly.

1. Create databases per environment

Use the SYSADMIN role to create a dedicated database for each environment:

USE ROLE SYSADMIN;
CREATE DATABASE CLOUDDW_DEV;
CREATE DATABASE CLOUDDW_PROD;

-- Repeat for other environments.

2. Create warehouses per environment

Warehouses provide compute resources for each environment. Although optional, using separate warehouses per environment enables better control and cost tracking.

USE ROLE SYSADMIN;
CREATE WAREHOUSE WH_ADE_DEV
WITH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 1
  SCALING_POLICY = 'STANDARD'
  COMMENT = 'Agile Data Engine DEV';

-- Repeat for other environments.

Customize warehouse settings as needed.

3. Create roles per environment

Use the USERADMIN role to define a dedicated role for each environment:

USE ROLE USERADMIN;
CREATE ROLE ROLE_ADE_DEV COMMENT = 'Agile Data Engine DEV';

-- Repeat for other environments.

4. Grant roles to higher-level role

Establish a continuous role hierarchy by granting the environment roles to a higher role:

USE ROLE USERADMIN;
GRANT ROLE ROLE_ADE_DEV TO ROLE SYSADMIN;

-- Repeat for other environments.

5. Create service users per environment

Create service users using key-pair authentication. You can generate either an encrypted or an unencrypted private key. Generate a separate RSA key pair for each environment.

Preferably, generate an encrypted private key (with password):

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key_dev.p8

Generate the corresponding public key:

openssl rsa -in rsa_key_dev.p8 -pubout -out rsa_key_dev.pub

Copy the contents of rsa_key_dev.pub for use in the next step.

Create a user with the public key in Snowflake:

USE ROLE USERADMIN;
CREATE USER S_ADE_DEV
  COMMENT = 'Service account for Agile Data Engine DEV.'
  DEFAULT_ROLE = ROLE_ADE_DEV
  DEFAULT_WAREHOUSE = WH_ADE_DEV
  RSA_PUBLIC_KEY = '<paste-public-key-here>'
  TYPE = SERVICE;

-- Repeat for other environments, using separate keys per environment.

Refer to Snowflake documentation for additional details:

• https://docs.snowflake.com/en/user-guide/key-pair-auth

• https://docs.snowflake.com/en/sql-reference/sql/create-user

Store the private keys and key passwords securely.

6. Grant roles to users

USE ROLE USERADMIN;
GRANT ROLE ROLE_ADE_DEV TO USER S_ADE_DEV;

-- Repeat for other environments.

7. Grant privileges to roles

Use the SYSADMIN role to grant necessary privileges:

USE ROLE SYSADMIN;
-- Warehouses
GRANT MODIFY, OPERATE, USAGE ON WAREHOUSE WH_ADE_DEV TO ROLE ROLE_ADE_DEV;
-- Databases
GRANT USAGE, CREATE SCHEMA ON DATABASE CLOUDDW_DEV TO ROLE ROLE_ADE_DEV;

-- Repeat for other environments.

You may also use other databases, warehouses and other objects than the defaults configured here. Grant privileges to ADE roles accordingly.

8. (Optional) Grant storage integration and external stage privileges

If you will be using ADE to orchestrate file loads from cloud storage to Snowflake staging tables, configure storage integration(s) and/or external stages in Snowflake.

Grant the USAGE privilege on storage integrations to the ADE roles:

USE ROLE ACCOUNTADMIN;
GRANT USAGE ON INTEGRATION <integration_name> TO ROLE ROLE_ADE_DEV;

-- Repeat for other environments and storage integrations.

Grant the USAGE privilege on external stages to ADE roles:

USE ROLE SYSADMIN; -- Execution role depends on your setup
GRANT USAGE ON STAGE <stage_name> TO ROLE ROLE_ADE_DEV;

-- Repeat for other environments and stages.

See https://docs.snowflake.com/en/user-guide/data-load-overview for more details.

9. Configure user-level network policies

It is strongly recommended to configure network policies that restrict access to known and trusted IP addresses.

In Snowflake, this can be enforced at the user level by defining and assigning a network policy:

USE ROLE SECURITYADMIN;
CREATE NETWORK POLICY ADE_ALLOWED
  ALLOWED_IP_LIST = (
    '123.456.789.0'
  )
  COMMENT = 'Allow: Agile Data Engine outbound public IP';

ALTER USER S_ADE_DEV SET NETWORK_POLICY = ADE_ALLOWED;
-- Repeat for TEST and PROD users

This configuration ensures that ADE service users can only authenticate from approved IP addresses, enhancing security and preventing unauthorized access.

Agile Data Engine uses a tenant-specific outbound IP address, which you can request via the ADE Support Portal.

If your organization requires private connectivity, see Private Connectivity and VPN.

Summary of Required Privileges