Snowflake Connections

Snowflake Connections

Configure and manage Snowflake connections for CLI tools, Streamlit apps, dbt, and Snowpark applications.

Configuration Files:

• connections.toml - Used by Snowflake CLI, Streamlit, and Snowpark
• profiles.yml - Used by dbt (different format, covered in dbt-core skill)

When to Use This Skill

Activate this skill when users ask about:

• Setting up Snowflake connections for CLI, Streamlit, or Snowpark
• Configuring connections.toml file
• Authentication methods (SSO, key pair, username/password, OAuth)
• Managing multiple environments (dev, staging, prod)
• Overriding connection settings with environment variables
• Troubleshooting authentication or connection issues
• Rotating credentials or keys
• Setting up CI/CD authentication

Note: For dbt-specific connection setup using profiles.yml, see the dbt-core skill. The concepts and authentication methods in this skill still apply, but dbt uses a different configuration file format.

Configuration File

This skill covers connections.toml used by Snowflake CLI, Streamlit, and Snowpark.

For dbt: Use ~/.dbt/profiles.yml instead. See the dbt-core skill for dbt configuration. The authentication methods described here apply to both files.

Location

Basic Structure

[default]
account = "your_account"
user = "your_username"
warehouse = "COMPUTE_WH"
database = "MY_DB"
schema = "PUBLIC"
role = "MY_ROLE"

# Add authentication method (see below)

Key Fields:

• account - Snowflake account identifier (e.g., xy12345.us-east-1)
• user - Snowflake username
• warehouse - Default warehouse for queries
• database - Default database context
• schema - Default schema context
• role - Default role to use

Authentication Methods

Option 1: SSO/External Browser (Recommended for Development)

Best for: Organizations with SSO, interactive development

[default]
account = "your_account"
user = "your_username"
authenticator = "externalbrowser"

How it works: Opens browser for SSO authentication

Pros:

• ✅ Most secure for development
• ✅ Leverages existing SSO infrastructure
• ✅ No password storage required
• ✅ MFA support built-in

Cons:

• ❌ Requires browser access
• ❌ Not suitable for headless/CI environments

Usage:

# Browser opens automatically for authentication
streamlit run app.py
snow sql -c default -q "SELECT CURRENT_USER()"

Option 2: Key Pair Authentication (Recommended for Production)

Best for: Production deployments, CI/CD pipelines, automation

[default]
account = "your_account"
user = "your_username"
authenticator = "snowflake_jwt"
private_key_path = "~/.ssh/snowflake_key.p8"
private_key_passphrase = "your_passphrase"  # Optional if key is encrypted

Setup Steps:

1. Generate Key Pair:

# Generate encrypted private key (recommended)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8

# Or unencrypted (less secure, but no passphrase needed)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt

# Generate public key
openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub

2. Extract Public Key (remove header/footer/newlines):

# Remove header, footer, and newlines
cat snowflake_key.pub | grep -v "BEGIN PUBLIC" | grep -v "END PUBLIC" | tr -d '\n'

3. Add Public Key to Snowflake:

-- Set public key for user
ALTER USER your_username SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A...';

-- Verify
DESC USER your_username;
-- Check RSA_PUBLIC_KEY_FP field is populated

4. Test Connection:

snow sql -c default -q "SELECT CURRENT_USER()"

Pros:

• ✅ Very secure for production
• ✅ No password storage
• ✅ Ideal for CI/CD and automation
• ✅ Works in headless environments
• ✅ No interactive prompts

Cons:

• ❌ More complex initial setup
• ❌ Requires key management and rotation

Security Best Practices:

• Store private keys outside project directory
• Use encrypted keys with passphrases
• Rotate keys every 90 days
• Use different keys for different environments
• Never commit keys to version control

Option 3: Username/Password (Development Only)

Best for: Quick testing, local development

[default]
account = "your_account"
user = "your_username"
password = "your_password"

Pros:

• ✅ Simple setup
• ✅ Works everywhere

Cons:

• ❌ Less secure (password in plain text)
• ❌ Not recommended for production
• ❌ MFA requires separate handling

⚠️ WARNING: Never use for production or commit connections.toml with passwords to git!

Option 4: OAuth Token

Best for: OAuth-based integrations, programmatic access

[default]
account = "your_account"
authenticator = "oauth"
token = "your_oauth_token"

Pros:

• ✅ Supports OAuth workflows
• ✅ Token-based security

Cons:

• ❌ Requires token refresh logic
• ❌ Token expiration management

Usage Pattern:

# Token needs to be refreshed before expiration
from snowflake.snowpark import Session
import os

session = Session.builder.configs({
    "account": "your_account",
    "authenticator": "oauth",
    "token": os.getenv("OAUTH_TOKEN")
}).create()

Multiple Connections (Multi-Environment)

Define multiple connection profiles for different environments:

[default]
account = "dev_account"
user = "dev_user"
authenticator = "externalbrowser"
warehouse = "DEV_WH"
database = "DEV_DB"
schema = "PUBLIC"

[staging]
account = "staging_account"
user = "staging_user"
authenticator = "externalbrowser"
warehouse = "STAGING_WH"
database = "STAGING_DB"
schema = "PUBLIC"

[prod]
account = "prod_account"
user = "prod_user"
authenticator = "snowflake_jwt"
private_key_path = "~/.ssh/prod_key.p8"
warehouse = "PROD_WH"
database = "PROD_DB"
schema = "PUBLIC"

Using Connection Profiles

Snowflake CLI:

# Use specific connection
snow sql -c default -q "SELECT CURRENT_DATABASE()"
snow sql -c staging -q "SELECT CURRENT_DATABASE()"
snow sql -c prod -q "SELECT CURRENT_DATABASE()"

# Deploy with specific connection
snow streamlit deploy -c prod

Streamlit Apps:

import streamlit as st
from snowflake.snowpark import Session

# Allow user to select environment
env = st.selectbox("Environment", ["default", "staging", "prod"])
session = Session.builder.config("connection_name", env).create()

dbt:

# profiles.yml
snowflake_demo:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      # Uses connections.toml if not specified
    prod:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_PROD_ACCOUNT') }}"

Environment Variable Overrides

Override connection settings without modifying connections.toml:

Supported Variables

Usage Examples

Command-Line Overrides:

# Override database/schema
export SNOWFLAKE_DATABASE=ANALYTICS_DB
export SNOWFLAKE_SCHEMA=REPORTING
streamlit run app.py

# Override warehouse for heavy query
export SNOWFLAKE_WAREHOUSE=XLARGE_WH
snow sql -c default -f heavy_query.sql

# Multiple overrides
export SNOWFLAKE_DATABASE=PROD_DB
export SNOWFLAKE_SCHEMA=PUBLIC
export SNOWFLAKE_WAREHOUSE=COMPUTE_WH
dbt run

Startup Script Pattern:

#!/bin/bash
# run_dev.sh

# Set environment-specific variables
export SNOWFLAKE_DATABASE=DEV_DB
export SNOWFLAKE_SCHEMA=DEV_SCHEMA
export SNOWFLAKE_WAREHOUSE=DEV_WH

# Start application
streamlit run app.py

Multi-Environment Scripts:

#!/bin/bash
# run.sh
ENV="${1:-dev}"

case $ENV in
  dev)
    export SNOWFLAKE_DATABASE=DEV_DB
    export SNOWFLAKE_WAREHOUSE=DEV_WH
    ;;
  staging)
    export SNOWFLAKE_DATABASE=STAGING_DB
    export SNOWFLAKE_WAREHOUSE=STAGING_WH
    ;;
  prod)
    export SNOWFLAKE_DATABASE=PROD_DB
    export SNOWFLAKE_WAREHOUSE=PROD_WH
    ;;
esac

streamlit run app.py

Usage: ./run.sh prod

Connection Patterns for Different Tools

Streamlit Apps

Required pattern for local/Snowflake compatibility:

import streamlit as st
from snowflake.snowpark.context import get_active_session
from snowflake.snowpark import Session

@st.cache_resource
def get_snowpark_session():
    """Get or create Snowpark session (cached)"""
    try:
        # When running in Snowflake (deployed)
        return get_active_session()
    except:
        # When running locally - uses connections.toml
        return Session.builder.config('connection_name', 'default').create()

session = get_snowpark_session()

With environment selection:

@st.cache_resource
def get_snowpark_session(connection_name='default'):
    try:
        return get_active_session()
    except:
        return Session.builder.config('connection_name', connection_name).create()

# Allow user to select environment
env = st.selectbox("Environment", ["default", "staging", "prod"])
session = get_snowpark_session(env)

Snowflake CLI

# Use default connection
snow sql -c default -q "SELECT CURRENT_USER()"

# Use specific connection profile
snow sql -c prod -q "SELECT CURRENT_DATABASE()"

# Test connection
snow connection test -c default

dbt

Important: dbt uses ~/.dbt/profiles.yml instead of connections.toml.

# ~/.dbt/profiles.yml (NOT connections.toml)
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      # Authentication method - choose one:
      authenticator: externalbrowser # SSO
      # OR
      private_key_path: ~/.ssh/snowflake_key.p8 # Key pair
      # OR
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}" # Username/password

      warehouse: COMPUTE_WH
      database: MY_DB
      schema: PUBLIC

Note: While dbt uses a different configuration file, the authentication methods and environment variable patterns are the same. See the dbt-core skill for complete dbt configuration.

Snowpark Scripts

from snowflake.snowpark import Session

# Use connections.toml
session = Session.builder.config('connection_name', 'default').create()

# Or with explicit config
session = Session.builder.configs({
    "account": "your_account",
    "user": "your_user",
    "authenticator": "externalbrowser"
}).create()

Best Practices

✅ DO

Development:

• Use SSO/externalbrowser for local development
• Use separate connection profiles for each environment
• Use startup scripts for consistent configuration
• Test connections before running applications: snow connection test -c <profile>

Production:

• Use key pair authentication for production and CI/CD
• Store private keys outside project directory (e.g., ~/.ssh/)
• Use encrypted keys with passphrases
• Rotate keys every 90 days
• Use different keys for different environments

Security:

• Add connections.toml to .gitignore
• Never commit credentials or keys to version control
• Use least-privilege roles
• Enable MFA where possible
• Audit connection usage regularly

Configuration:

• Use environment variables for overrides
• Document connection requirements in README
• Provide connection templates (without credentials)
• Use connection profiles for multi-environment setups

❌ DON'T

• Commit connections.toml to git (add to .gitignore)
• Hardcode credentials in code
• Share private keys between team members
• Use production credentials for local development
• Store passwords in plain text for production
• Use same authentication method for all environments
• Skip testing connections before deployment

Testing Connections

Test Connection Profile

# Basic test
snow connection test -c default

# Test with query
snow sql -c default -q "SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_DATABASE()"

# Verbose test
snow connection test -c default --verbose

Verify Environment Variables

# Check which variables are set
env | grep SNOWFLAKE_

# Test override
export SNOWFLAKE_DATABASE=TEST_DB
snow sql -c default -q "SELECT CURRENT_DATABASE()"

Debug Connection Issues

Add debug info to your application:

Streamlit:

st.write("Database:", session.get_current_database())
st.write("Schema:", session.get_current_schema())
st.write("Warehouse:", session.get_current_warehouse())
st.write("Role:", session.get_current_role())

Python Script:

print(f"Account: {session.get_current_account()}")
print(f"User: {session.get_current_user()}")
print(f"Database: {session.get_current_database()}")
print(f"Schema: {session.get_current_schema()}")
print(f"Warehouse: {session.get_current_warehouse()}")
print(f"Role: {session.get_current_role()}")

Troubleshooting

Connection Failed

Error: Could not connect to Snowflake or Connection timeout

Solutions:

1. Verify connections.toml exists at correct location
2. Check account identifier format (e.g., xy12345.us-east-1)
3. Verify user has appropriate permissions
4. Check network connectivity/firewall
5. Test with: snow connection test -c <profile>

SSO/External Browser Issues

Error: External browser authentication failed

Solutions:

1. Ensure browser is installed and accessible
2. Check firewall/proxy settings
3. Try clearing browser cookies for Snowflake
4. Verify SSO configuration in Snowflake
5. Check if user exists: DESC USER your_username

Key Pair Authentication Failed

Error: JWT token is invalid or Private key authentication failed

Solutions:

1. Verify public key is set: DESC USER your_username (check RSA_PUBLIC_KEY_FP)
2. Ensure private key path is correct in connections.toml
3. Verify private key format is PKCS#8 (not PKCS#1)
4. Check passphrase is correct (if key is encrypted)
5. Regenerate and re-upload public key if needed

Wrong Database/Schema/Warehouse

Problem: Application uses unexpected database/schema/warehouse

Solutions:

1. Check connection profile settings in connections.toml
2. Verify environment variables: env | grep SNOWFLAKE_
3. Check for application-level overrides
4. Use USE DATABASE/SCHEMA/WAREHOUSE statements if needed
5. Debug with current context queries (see Testing Connections above)

Environment Variables Not Applied

Problem: Overrides don't take effect

Solutions:

1. Verify variables are exported: env | grep SNOWFLAKE_
2. Restart application after setting variables
3. Check if application caches session (clear cache if needed)
4. Ensure variable names are correct (case-sensitive)
5. Try setting variables inline: SNOWFLAKE_DATABASE=MY_DB streamlit run app.py

Connection Profile Not Found

Error: Connection 'profile_name' not found

Solutions:

1. Check ~/.snowflake/connections.toml exists
2. Verify profile name in file (case-sensitive)
3. Check TOML syntax is valid
4. List available connections: snow connection list

Permission Denied

Error: Insufficient privileges or Access denied

Solutions:

1. Verify role has necessary grants
2. Check if role is specified in connection profile
3. Try with different role: snow sql -c default --role ACCOUNTADMIN
4. Review grants: SHOW GRANTS TO USER your_username

Security Considerations

Credential Storage

Never store credentials in:

• ❌ Application code
• ❌ Version control (git)
• ❌ Shared drives
• ❌ Documentation
• ❌ Environment files committed to git

Safe storage locations:

• ✅ ~/.snowflake/connections.toml (with appropriate file permissions)
• ✅ Secure secret management systems (AWS Secrets Manager, HashiCorp Vault, etc.)
• ✅ CI/CD secret stores (GitHub Secrets, GitLab CI Variables, etc.)
• ✅ Environment variables (for temporary overrides)

File Permissions

Restrict access to connection files:

# Set restrictive permissions (Unix/Mac)
chmod 600 ~/.snowflake/connections.toml

# Verify permissions
ls -la ~/.snowflake/connections.toml
# Should show: -rw------- (owner read/write only)

Key Management

For key pair authentication:

1. Generate separate keys for each environment
2. Use encrypted keys with strong passphrases
3. Store keys in secure location (~/.ssh/ with 600 permissions)
4. Rotate keys every 90 days
5. Revoke old keys after rotation
6. Document key rotation procedures

Key rotation process:

# 1. Generate new key pair
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key_new.p8

# 2. Extract public key
openssl rsa -in snowflake_key_new.p8 -pubout -out snowflake_key_new.pub

# 3. Add new public key to Snowflake (keeps old key)
ALTER USER your_username SET RSA_PUBLIC_KEY_2='NEW_PUBLIC_KEY';

# 4. Test new key
mv ~/.ssh/snowflake_key.p8 ~/.ssh/snowflake_key_old.p8
mv ~/.ssh/snowflake_key_new.p8 ~/.ssh/snowflake_key.p8
snow connection test -c default

# 5. Remove old key after verification
ALTER USER your_username UNSET RSA_PUBLIC_KEY;

CI/CD Integration

GitHub Actions

# .github/workflows/deploy.yml
name: Deploy to Snowflake

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2

      - name: Setup Snowflake connection
        run: |
          mkdir -p ~/.snowflake
          cat > ~/.snowflake/connections.toml <<EOF
          [default]
          account = "${{ secrets.SNOWFLAKE_ACCOUNT }}"
          user = "${{ secrets.SNOWFLAKE_USER }}"
          authenticator = "snowflake_jwt"
          private_key_path = "~/.ssh/snowflake_key.p8"
          warehouse = "PROD_WH"
          database = "PROD_DB"
          EOF

          echo "${{ secrets.SNOWFLAKE_PRIVATE_KEY }}" > ~/.ssh/snowflake_key.p8
          chmod 600 ~/.ssh/snowflake_key.p8

      - name: Deploy
        run: |
          snow streamlit deploy -c default

GitLab CI

# .gitlab-ci.yml
deploy:
  stage: deploy
  script:
    - mkdir -p ~/.snowflake
    - |
      cat > ~/.snowflake/connections.toml <<EOF
      [default]
      account = "${SNOWFLAKE_ACCOUNT}"
      user = "${SNOWFLAKE_USER}"
      authenticator = "snowflake_jwt"
      private_key_path = "~/.ssh/snowflake_key.p8"
      EOF
    - echo "${SNOWFLAKE_PRIVATE_KEY}" > ~/.ssh/snowflake_key.p8
    - chmod 600 ~/.ssh/snowflake_key.p8
    - snow streamlit deploy -c default
  only:
    - main

Quick Reference

Basic Setup

# ~/.snowflake/connections.toml

# SSO (Development)
[default]
account = "your_account"
user = "your_username"
authenticator = "externalbrowser"
warehouse = "COMPUTE_WH"
database = "MY_DB"
schema = "PUBLIC"

# Key Pair (Production)
[prod]
account = "prod_account"
user = "prod_user"
authenticator = "snowflake_jwt"
private_key_path = "~/.ssh/snowflake_key.p8"
warehouse = "PROD_WH"
database = "PROD_DB"
schema = "PUBLIC"

Common Commands

# Test connection
snow connection test -c default

# List connections
snow connection list

# Use specific connection
snow sql -c prod -q "SELECT CURRENT_USER()"

# Override settings
export SNOWFLAKE_DATABASE=MY_DB
streamlit run app.py

# Generate key pair
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt
openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub

Connection Pattern (Streamlit)

from snowflake.snowpark.context import get_active_session
from snowflake.snowpark import Session

@st.cache_resource
def get_snowpark_session():
    try:
        return get_active_session()  # Snowflake
    except:
        return Session.builder.config('connection_name', 'default').create()  # Local

Related Skills

• snowflake-cli skill - Snowflake CLI operations and commands
• streamlit-development skill - Streamlit application development
• dbt-core skill - dbt project configuration using profiles.yml (dbt's configuration format)

Goal: Transform AI agents into experts at configuring and managing Snowflake connections securely across all tools and environments, with proper authentication methods, multi-environment support, and security best practices.