Snowflake is a cloud-based data warehouse (“data warehouse-as-a-service”) widely used for analytics and storage. Pecan’s Snowflake connector allows you to connect to Snowflake from the Pecan platform to import data or export predictions. This guide will walk you through establishing a connection. Pecan supports two Snowflake authentication methods: the basic Username/Password login and the preferred key-pair authentication for Snowflake. We recommend using key-pair authentication for a more secure Snowflake connection, but instructions for both options are provided so you can choose the right one for your setup.
Note: The steps below involve Snowflake administrative tasks (creating users, roles, etc.). If you need assistance with any step, consult your Snowflake administrator or IT team.
Prerequisites
Whitelist Pecan’s IP Addresses: To allow Pecan to connect to your Snowflake account, ensure your Snowflake firewall or network policy permits incoming requests from the following IP addresses (add these to Snowflake’s network policy if one is in effect):
54.246.108.184
54.216.8.168
54.73.234.235Snowflake Admin Access: You will need the ability to create users and roles in Snowflake. Log in as a user with the ACCOUNTADMIN or SECURITYADMIN role (or another role with user/role management privileges).
Dedicated Snowflake Role & User: It’s best practice to create a dedicated Snowflake role and user for Pecan with only the necessary permissions. In the steps below, we’ll create a new role and user for Pecan in Snowflake and grant the appropriate privileges.
Method 1: Username/Password Authentication (Basic)
This method uses a standard Snowflake login with a username and password. It’s simpler to set up but less secure than key-pair authentication (since it relies on a password). Ensure you choose a strong, unique password if you use this method.
Steps to connect with Username/Password:
1. Create a Role with Required Permissions in Snowflake
Using your Snowflake admin role, create a new role for Pecan and grant it the permissions Pecan will need (typically read access, and write access if you plan to export predictions). For example, you can run the following SQL commands in Snowflake:
-- these commands create a new user and role on Snowflake
-- and grant them the right access permissions to use with Pecan
-- based on these parameters (change if needed):
-- ====
-- Username: pecan_user
-- Password: '--choose-your-password--'
-- Role: pecan_role
-- Schema: public
-- Warehouse: 'COMPUTE_WH'
-- ====
-- create role
CREATE role 'pecan_role';
GRANT USAGE on warehouse 'COMPUTE_WH' to role 'pecan_role';
-- give permissions to role
GRANT select,insert on all tables in schema 'public' to role 'pecan_role';
GRANT usage on schema 'public' to role 'pecan_role';
-- create user and attach it to created role
CREATE USER 'pecan_user'
password='--choose-your-password--'
DEFAULT_ROLE = 'pecan_role'
must_change_password = false
DEFAULT_WAREHOUSE = 'COMPUTE_WH';
GRANT ROLE 'pecan_role' TO USER 'pecan_user';
Make sure to adjust the following objects in the script according to your own configuration and preferences:
- Warehouse name (“
COMPUTE_WH” in the example)- Database name and schema (“
demo_db.public” in the example)- Dedicated username and password (instead of “
pecan_user”)
2. Configure the Snowflake Connection in Pecan
Now that the Snowflake user and role are set up, log in to the Pecan platform and add a Snowflake connection:
In Pecan, go to the Connections tab and click Add Connection.
Select Snowflake as the connection type.
Fill in the following fields in the Configure Snowflake connection form:
Connection name: A friendly name for this connection (e.g., “Snowflake Marketing DB”). This name is just for your reference in Pecan (it cannot be changed later).
Connection type: Choose Read if you are importing data from Snowflake into Pecan. Choose Write if you plan to export Pecan’s predictions to Snowflake.
Snowflake URL: The URL of your Snowflake account. This is the host portion of the URL you use to log into Snowflake. It typically looks like your_account_name.region.snowflakecomputing.com. (Example: accountname.us-east-1.snowflakecomputing.com). You can copy this from your Snowflake browser address bar.
Username: The Snowflake username you created for Pecan (e.g., pecan_user).
Password: The password you set for that Snowflake user. (If you used the SQL example above, this is the password you provided in the CREATE USER statement.)
Database name: The Snowflake database that Pecan should use for data. (If you want Pecan to access multiple databases on the same Snowflake account, you will need to create a separate connection for each database.)
Database schema (optional): The schema within the database that Pecan should use. If left blank, Pecan will default to the Snowflake “public” schema. (Note: Pecan will always display the “public” schema in addition to any schema you specify.)
Warehouse (optional): The virtual warehouse to use for queries. This should typically match the warehouse you granted access to in Step 1 (e.g., COMPUTE_WH or your equivalent).
After filling in all the details, click Test connection to verify that Pecan can connect to Snowflake using the provided credentials. If the test is successful, click Create connection to save the Snowflake connection in Pecan. (If the test fails, double-check the credentials, permissions, and whitelisting from the earlier steps.)
Method 2: Key-Pair Authentication (Recommended for Security)
Key-pair authentication is a more secure method that uses an RSA public-private key pair instead of a password to authenticate the connection to Snowflake. Snowflake supports key-pair authentication as an enhanced security alternative to using username and password. With this method, the Snowflake user’s login is tied to an RSA key pair: the public key is stored in Snowflake, and the private key is kept securely on Pecan’s side (never shared with Snowflake). This eliminates the need to store a password in Pecan and greatly reduces the risk of compromised credentials.
Steps to connect with Key-Pair Authentication:
1. Generate an RSA Key Pair
You will need to generate a public-private key pair (at least 2048-bit RSA) for Snowflake to use. Snowflake requires an RSA key pair in PEM format, which you can create using OpenSSL. Run the following commands in a terminal:
# Generate a 2048-bit RSA private key (encrypted with a passphrase for security)
openssl genrsa -aes256 -out pecan_snowflake_private_key.pem 2048
# You will be prompted to set a passphrase - be sure to remember it and keep it secure.
# Extract the public key from the private key
openssl rsa -in pecan_snowflake_private_key.pem -pubout -out pecan_snowflake_public_key.pem
These commands will produce two files: pecan_snowflake_private_key.pem (your private key, encrypted with a passphrase) and pecan_snowflake_public_key.pem (your public key). Keep the private key file and passphrase secure – do not share the private key. Only the public key will be uploaded to Snowflake. (If you prefer not to use a passphrase on the private key, you can omit the -aes256 flag when generating it; however, using an encrypted private key is recommended for better security.)
2. Create a Role and User in Snowflake (using the public key)
As a Snowflake admin (using SECURITYADMIN or similar), create the dedicated role and user for Pecan, and assign the public key to the user for authentication. For example, run the following SQL commands in Snowflake:
-- Create a role for Pecan (if not created already)
CREATE ROLE pecan_role;
-- Grant warehouse usage to the role (replace COMPUTE_WH with your warehouse)
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE pecan_role;
-- Create a Pecan user and assign the RSA public key for key-pair auth
CREATE USER pecan_user
RSA_PUBLIC_KEY='MIIBIjANBg...<rest_of_your_public_key_here>...'
DEFAULT_ROLE = pecan_role
DEFAULT_WAREHOUSE = COMPUTE_WH;
-- (No password is set for this user; authentication will rely on the key pair)
-- Assign the role to the user
GRANT ROLE pecan_role TO USER pecan_user;
-- Grant read/write privileges on the target schema to the role (adjust as needed)
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA demo_db.public TO ROLE pecan_role;
GRANT USAGE ON SCHEMA demo_db.public TO ROLE pecan_role;
In this example, we created the same role pecan_role and a user pecan_user, but instead of a password we set the user’s RSA_PUBLIC_KEY to the public key value. Replace the 'MIIBIjANBg...<rest_of_your_public_key_here>...' placeholder with your actual public key string. This should be the full Base-64 encoded key (the contents of your .pem public key file, without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines).
As before, adjust the other parts of the script for your environment: the warehouse name (COMPUTE_WH), database and schema (demo_db.public), and the privileges the role should have.
Optional: You can omit the INSERT privilege if Pecan will only read data.
Once executed, the Snowflake user pecan_user is configured to use key-pair authentication only (no password needed for login).
Optional: After creating the user, you can verify that the public key is properly associated by running: DESC USER pecan_user; in Snowflake. The output will show an RSA_PUBLIC_KEY (and a fingerprint RSA_PUBLIC_KEY_FP) for the user, confirming the key was added.
3. Configure the Snowflake Connection in Pecan
With the user, role, and keys in place, configure the connection in Pecan similar to the steps for username/password, but using the key for authentication.
In Pecan, go to Connections → Add Connection, and select Snowflake.
Fill in the connection details (Connection name, type, Snowflake URL, Database, etc.) as described in Method 1 above. Use the Snowflake username you created (e.g., pecan_user).
Authentication method: If the Pecan interface allows you to choose an authentication method, select Key Pair Authentication for Snowflake. Pecan will prompt you to provide your private key (and a passphrase if the private key is encrypted).
Private Key: Paste the contents of your pecan_snowflake_private_key.pem file (the full PEM text) or upload the file if the UI provides an option.
Private Key Passphrase: Enter the passphrase you set when generating the private key (if you used -aes256). If you generated the key without a passphrase, you can leave this blank.
Test and save the connection: Click Test connection to ensure Pecan can authenticate to Snowflake using the key pair. If the test is successful, click Create connection to save the connection.
Tip: A successful connection test for key-pair authentication indicates that your public key was correctly configured for the Snowflake user and that the private key (and passphrase, if used) provided to Pecan match that public key. If the test fails, re-check that the public key in Snowflake exactly matches the private key you’re using, and that you’ve entered the correct passphrase. Also verify that the Pecan IP addresses are whitelisted and the user has the necessary role and privileges.
Choosing the Right Authentication Method
Both authentication options will allow Pecan to connect to Snowflake, but consider the following when choosing the best method for your setup:
Security: Key-pair authentication is more secure and is the recommended approach. There is no password transmitted or stored that could be compromised; instead, authentication uses a cryptographic key. If your organization prioritizes security or has compliance requirements, using the key-pair method is preferable. Snowflake itself endorses key-pair authentication for enhanced security.
Simplicity: Username/password is straightforward to set up if you’re not familiar with generating keys. It might be sufficient for quick tests or non-production scenarios. However, be sure to use a strong password and rotate it periodically if you use this method.
Maintenance: With key pairs, you should implement key management practices (safeguard the private key, rotate keys periodically, etc.). This adds a bit of overhead, but greatly improves security. Passwords also require management (rotations, complexity), but keys (especially with passphrases) generally offer a higher level of protection.
Compatibility: Pecan’s Snowflake connector supports both methods. Most Snowflake clients and connectors (including the Snowflake Python connector that Pecan uses under the hood) fully support key-pair authentication. If you encounter any issues with key pairs, ensure that the key was generated in the proper format (OpenSSL PEM) and that the Snowflake user has the key configured.
In summary, if possible choose key-pair authentication for a secure Snowflake connection, and use the username/password method only if a key-pair setup is not feasible for your environment. By following the steps above, you can confidently connect to Snowflake from Pecan using either method, with your data and credentials kept as secure as possible.