Snowflake
Connect to your Snowflake Data Warehouse.
Getting Started
Prerequisites to create a Snowflake based workflow. You will need
A source Snowflake connection.
(optional) A list of tables OR SQL queries.
(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:
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
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]
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.
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]
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;
Last updated
Was this helpful?