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

OS	Path
Unix/Mac	~/.snowflake/connections.toml
Windows	%USERPROFILE%\.snowflake\connections.toml

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

Variable	Purpose	Example
SNOWFLAKE_ACCOUNT	Override account	xy12345.us-east-1
SNOWFLAKE_USER	Override user	john_doe
SNOWFLAKE_PASSWORD	Override password	secret123
SNOWFLAKE_DATABASE	Override database	ANALYTICS_DB
SNOWFLAKE_SCHEMA	Override schema	REPORTING
SNOWFLAKE_WAREHOUSE	Override warehouse	LARGE_WH
SNOWFLAKE_ROLE	Override role	ANALYST

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.tom

---

Content truncated.