Snowflake

Connect to your Snowflake Data Warehouse.

Getting Started

Prerequisites to create a Snowflake based workflow. You will need

1. A source Snowflake connection.

2. (optional) A list of tables OR SQL queries.

3. (optional) A destination Snowflake connection.

Do not use your input data warehouse connection as an output connector. This action can result in the unintended overwriting of existing data.

Configuring a Snowflake Connection

There are two ways to authenticate a Gretel Snowflake connection, each methoed requires different fields for a connection creation:

1. Standard Authentication

2. External OAuth

Standard Authentication

A snowflake connection authenticated via username/password is created using the following parameters:

name	Display name of your choosing used to identify your connection within Gretel.	my-snowflake-connection
host	Fully qualified domain name (FQDN) used to establish connection to database server.	account_identifier.snowflakecomputing.com
username	Unique identifier associated with specific account authorized to access database.	john
password	Security credential to authenticate username.	...
database	Name of database to connect to.	MY_DATABASE
warehouse	Name of warehouse.	MY_WAREHOUSE
(optional) schema	Optional Name of schema.	MY_SCHEMA
(optional) params	Optional JDBC URL parameters that can be used for advanced configuration.	role=MY_ROLE

Creating Connections

CLI

First, create a file on your local computer containing the connection credentials. This file should also include type, name , config, and credentials. The config and credentials fields should contain fields that are specific to the connection being created.

Below is an example Snowflake connection:

{
    "type": "snowflake",
    "name": "my-snowflake-connection",
    "config": {
        "host": "account_identifier.snowflakecomputing.com",
        "username": "john",
        "database": "MY_DATABASE",
        "warehouse": "MY_WAREHOUSE",
        "schema": "MY_SCHEMA",
        "params": "role=MY_ROLE"
    },
    "credentials": {
        "password": "..."
    }
}

Now that you've created the credentials file, use the CLI to create the connection

gretel connections create --project [project id] --from-file [credential_file.json]

Console

• Navigate to the Connections page using the menu item in the left sidebar.
• Click the New Connection button.

• Step 1, choose the Type for the Connection - Snowflake.

• Step 2, choose the Project for your Connection.

• Step 3, fill in the credentials and select Add Connection.

Step 1, choose the Type of connection

Step 3, fill in credentials

SDK

from gretel_client import create_or_get_unique_project
from gretel_client.config import get_session_config
from gretel_client.rest_v1.api.connections_api import ConnectionsApi
from gretel_client.rest_v1.models import (
    CreateConnectionRequest,
    UpdateConnectionRequest,
)

session = get_session_config()
connection_api = session.get_v1_api(ConnectionsApi)

project = create_or_get_unique_project(name="snowflake-workflow")

connection = connection_api.create_connection(
    CreateConnectionRequest(
        name="my-snowflake-connection",
        project_id=project.project_guid,
        type="snowflake",
        config={
            "host": "account_identifier.snowflakecomputing.com",
            "username": "john",
            "database": "MY_DATABASE",
            "warehouse": "MY_WAREHOUSE",
            #"schema": "MY_SCHEMA",
            #"params": "role=MY_ROLE",
        },
        # note: best practice is to read in credentials from a file
        # or secret instead of directly embedding sensitive values
        # in python code.
        credentials={
            "password": "...",
        },
    )
)

External OAuth

A snowflake connection authenticated via External OAuth is created using the following parameters:

name	Display name of your choosing used to identify your connection within Gretel.	my-snowflake-connection
host	Fully qualified domain name (FQDN) used to establish connection to database server.	account_identifier.snowflakecomputing.com
username	Unique identifier associated with specific account authorized to access database.	john
password	Security credential to authenticate username.	...
database	Name of database to connect to.	MY_DATABASE
warehouse	Name of warehouse.	MY_WAREHOUSE
oauth_client_id	Unique identifier associated with the authentication application.
oauth_grant_type	method through which oauth token will be acquired	ex. "password"
oauth_scope	scope given to request token
oauth_url	endpoint to fetch access token from
(optional) schema	Optional Name of schema.	MY_SCHEMA
(optional) params	Optional JDBC URL parameters that can be used for advanced configuration.	role=MY_ROLE

Creating Connections

External OAuth is currently only supported via CLI/SDK.

CLI

First, create a file on your local computer containing the connection credentials. This file should also include type, name , config, and credentials. The config and credentials fields should contain fields that are specific to the connection being created.

Below is an example Snowflake External OAuth connection:

{
    "type": "snowflake",
    "name": "my-snowflake-connection-oauth",
    "config": {
        "host": "account_identifier.snowflakecomputing.com",
        "username": "john",
        "database": "MY_DATABASE",
        "warehouse": "MY_WAREHOUSE",
        "schema": "MY_SCHEMA",
        "oauth_client_id": "MY_OAUTH_CLIENT_ID",
        "oauth_grant_type": "GRANT_TYPE",
        "oauth_scope": "OAUTH_SCOPE",
        "oauth_url": "OAUTH_URL",
        "params": "role=MY_ROLE",
    },
    "credentials": {
        "password": "...",
        "oauth_client_secret": "...",
    },
    "auth_strategy": "oauth"
}

Now that you've created the credentials file, use the CLI to create the connection

gretel connections create --project [project id] --from-file [credential_file.json]

SDK

from gretel_client import create_or_get_unique_project
from gretel_client.config import get_session_config
from gretel_client.rest_v1.api.connections_api import ConnectionsApi
from gretel_client.rest_v1.models import (
    CreateConnectionRequest,
    UpdateConnectionRequest,
)

session = get_session_config()
connection_api = session.get_v1_api(ConnectionsApi)

project = create_or_get_unique_project(name="snowflake-workflow")

connection = connection_api.create_connection(
    CreateConnectionRequest(
        name="my-snowflake-connection",
        project_id=project.project_guid,
        type="snowflake",
        config={
            "host": "account_identifier.snowflakecomputing.com",
            "username": "john",
            "database": "MY_DATABASE",
            "warehouse": "MY_WAREHOUSE",
            "oauth_client_id": "MY_OAUTH_CLIENT_ID",
            "oauth_grant_type": "GRANT_TYPE",
            "oauth_scope": "OAUTH_SCOPE",
            "oauth_url": "OAUTH_URL",
        },
        # note: best practice is to read in credentials from a file
        # or secret instead of directly embedding sensitive values
        # in python code.
        credentials={
            "password": "...",
            "oauth_client_secret": "...",
        },
        auth_strategy="oauth",
    )
)

Permissions

Source Connection Permissions

The Snowflake source action requires enough access to read from tables and access schema metadata. The following SQL script will create a Snowflake user suitable for a Gretel Snowflake source.

-- Create the Gretel source role
CREATE ROLE IF NOT EXISTS GRETEL_SOURCE_ROLE;

-- Snowflake best practice. This ensures other roles can modify or drop objects created by the custom role
GRANT ROLE GRETEL_SOURCE_ROLE TO ROLE SYSADMIN;

-- Create the Gretel source user
CREATE USER IF NOT EXISTS GRETEL_SOURCE_USER
    PASSWORD = 'my secure password' -- be sure to change this to a more secure password
    DEFAULT_ROLE = GRETEL_SOURCE_ROLE
    DEFAULT_WAREHOUSE= "<your warehouse>";

-- Grant schema access to the Gretel source role
GRANT USAGE ON DATABASE "<your database>" TO ROLE GRETEL_SOURCE_ROLE;
GRANT USAGE ON SCHEMA "<your database>.<your schema>" TO ROLE GRETEL_SOURCE_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA "<your database>.<your schema>" TO ROLE GRETEL_SOURCE_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA "<your database>.<your schema>" TO ROLE GRETEL_SOURCE_ROLE;

Destination Connection Permissions

The snowflake destination action requires enough permissions to write to the destination schema.

If your destination database and schema do not already exist, create those first.

CREATE DATABASE "<your destination db>";
CREATE SCHEMA "<your destination schema>";

Next configure a user for the Snowflake destination. This user must have OWNERSHIP permissions in order to write data to the destination schema.

The following SQL script will create a Snowflake user suitable for a Gretel Snowflake destination.

-- Create the Gretel destination role
CREATE ROLE IF NOT EXISTS GRETEL_DESTINATION_ROLE;

-- Snowflake best practice. This ensures other roles can modify or drop objects created by the custom role
GRANT ROLE GRETEL_DESTINATION_ROLE TO ROLE SYSADMIN;

-- Create the Gretel destination user
CREATE USER IF NOT EXISTS GRETEL_DESTINATION_USER
    PASSWORD = 'my secure password' -- be sure to change this to a more secure password
    DEFAULT_ROLE = GRETEL_DESTINATION_ROLE
    DEFAULT_WAREHOUSE = "<your warehouse>";

-- Grant ownership to the Gretel destination user
GRANT OWNERSHIP ON SCHEMA "<your database>.<your schema>" TO ROLE GRETEL_DESTINATION_ROLE;