snowflake-cli（Snowflake CLI 运维）

Snowflake CLI (snow)

Execute SQL, manage Snowflake objects, and deploy applications using the Snowflake CLI command-line tool.

When to Use This Skill

Activate this skill when users ask about:

• Running SQL queries and scripts from command line
• Deploying Streamlit applications to Snowflake
• Managing Snowflake stages (upload/download/execute files)
• Using variables and templating in SQL scripts
• Executing Snowpark procedures and Python scripts
• Managing database objects (warehouses, tables, etc.)
• Automating Snowflake operations in CI/CD pipelines
• Multi-environment deployments with variables
• Troubleshooting CLI connection or execution issues

Quick Start

Three Main Use Cases:

1. SQL Execution - Run queries and scripts with variable substitution
2. Deployments - Deploy Streamlit apps and Snowpark objects
3. Stage Operations - Manage files and execute scripts from stages

Connection Behavior

Important: The Snowflake CLI uses the default connection profile from ~/.snowflake/connections.toml unless you specify a different connection with the -c or --connection flag.

# Uses 'default' connection (implicit)
snow sql -q "SELECT CURRENT_USER()"

# Uses 'default' connection (explicit)
snow sql -q "SELECT CURRENT_USER()" -c default

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

For connection configuration, see the snowflake-connections skill.

SQL Execution

# Inline query
snow sql -q "SELECT * FROM my_table" -c default

# Execute file
snow sql -f script.sql -c default

# With variables (Jinja {{ }} or <% %> syntax)
snow sql -q "SELECT * FROM {{db}}.{{schema}}.table" \
  -D db=PROD_DB -D schema=SALES -c default

Variables & Templating

Critical Concept: Snowflake CLI supports three different variable syntaxes depending on context.

Three Syntax Types

1. Bash Variables - Shell expansion (for environment control):

DB="PROD_DB"
SCHEMA="SALES"
snow sql -q "SELECT * FROM ${DB}.${SCHEMA}.orders" -c default

Use for: Connection names, file paths, environment selection, shell control flow

2. Standard Syntax <% %> - Default for snow sql commands:

# Single-line query with -q flag
snow sql -q "SELECT * FROM <% db %>.<% schema %>.orders" \
  -D db=PROD_DB -D schema=SALES -c default

# Multi-line query with -i flag (reads from stdin)
# The -i flag tells snow sql to read SQL from standard input
# <<EOF creates a here-document that feeds multi-line SQL to stdin
snow sql -i -D db=PROD_DB -D schema=SALES -c default <<EOF
SELECT
  order_id,
  customer_id,
  order_total
FROM <% db %>.<% schema %>.orders
WHERE order_date >= CURRENT_DATE - 7;
EOF

Understanding heredoc (<<EOF):

• <<EOF - Start of here-document (ends with matching EOF)
• SQL between the markers is fed to snow sql -i as standard input
• Variables are substituted using <% %> syntax
• Useful for readable multi-line SQL without escaping quotes
• The closing EOF must be on its own line with no indentation

Combining bash variables with heredoc for multi-statement scripts:

# Set bash variables for environment and database objects
ENV="prod"
CONNECTION="${ENV}_connection"
DB="PROD_DB"
SCHEMA="SALES"
TABLE="orders"

# Heredoc enables multiple SQL statements and complex scripts
# without worrying about quote escaping or line continuations
# Bash expands ${variables} before sending to Snowflake
snow sql -i -c ${CONNECTION} <<EOF
-- Create or replace view
CREATE OR REPLACE VIEW ${DB}.${SCHEMA}.recent_${TABLE} AS
SELECT
  order_id,
  customer_id,
  order_total,
  order_date,
  '${ENV}' as environment
FROM ${DB}.${SCHEMA}.${TABLE}
WHERE order_date >= CURRENT_DATE - 7;

-- Grant permissions
GRANT SELECT ON VIEW ${DB}.${SCHEMA}.recent_${TABLE} TO ROLE ANALYST;

-- Verify row count
SELECT
  COUNT(*) as row_count,
  MIN(order_date) as earliest_date,
  MAX(order_date) as latest_date
FROM ${DB}.${SCHEMA}.recent_${TABLE};
EOF

Why use heredoc:

• ✅ Multiple SQL statements in one execution
• ✅ No quote escaping needed for complex SQL
• ✅ Readable multi-line scripts with comments
• ✅ Bash expands ${VAR} before sending to Snowflake
• ✅ Natural formatting for longer migration or deployment scripts

When to use bash vs Snowflake CLI variables:

• Bash ${VAR} - Simple, expanded before execution (use for most cases)
• Snowflake CLI <% var %> - Use with -D flags when you need Snowflake CLI to handle substitution (safer for user input)

Use for: Inline SQL and heredoc with snow sql -q or snow sql -i

3. Jinja Syntax {{}} - Automatic for staged SQL files:

# SQL files on stage use Jinja automatically (no flag needed)
snow stage execute @my_stage/script.sql -c default \
  -D db=PROD_DB \
  -D schema=SALES

Use for: SQL files executed from stages with snow stage execute

Template Syntax Control

Control which syntaxes are enabled with --enable-templating:

# STANDARD (default): <% var %> only
snow sql -q "SELECT <% var %>" -D var=value

# JINJA: {{ var }} only
snow sql --enable-templating JINJA -q "SELECT {{ var }}" -D var=value

# LEGACY: &var or &{var} (SnowSQL compatibility)
snow sql --enable-templating LEGACY -q "SELECT &var" -D var=value

# ALL: Enable all syntaxes
snow sql --enable-templating ALL -q "SELECT <% var %> {{ var }}" -D var=value

# NONE: Disable templating (useful for queries containing template-like text)
snow sql --enable-templating NONE -q "SELECT '<% not_a_var %>'"

Default: STANDARD and LEGACY are enabled by default

Important Notes

• Stage execution automatically uses Jinja - SQL files uploaded to stages should use {{var}} syntax
• String values need quotes - Use -D name="'John'" for string literals
• Enable Jinja explicitly - Add --enable-templating JINJA to use {{}} with snow sql commands
• Combining variable types - Use bash for environment, <% %> for SQL: ENV="prod" CONNECTION="${ENV}_connection" snow sql -c ${CONNECTION} -i -D db=PROD_DB <<EOF SELECT * FROM <% db %>.orders; EOF

Comparison Table

Deployments

Streamlit Apps

snow streamlit deploy --replace -c default
snow streamlit list -c default
snow streamlit get-url my_app -c default

Snowpark (UDFs/Procedures)

snow snowpark build -c default
snow snowpark deploy --replace -c default

Project Creation

See PROJECT_CREATION.md for:

• How to create app projects
• Streamlit project structures
• Snowpark object projects

Stage Operations

Quick Commands:

# Upload/download files
snow stage copy ./script.sql @my_stage/ -c default
snow stage copy @my_stage/file.csv ./downloads/ -c default

# List files
snow stage list-files @my_stage -c default

# Execute SQL (uses Jinja {{ }} syntax automatically)
snow stage execute @my_stage/script.sql -c default -D db=PROD_DB

# Execute Python (access variables via os.environ)
snow stage execute @my_stage/script.py -c default -D var=value

For comprehensive stage management, see STAGE_OPERATIONS.md for:

• Complete file operations (upload, download, list, remove)
• Variable syntax for SQL vs Python scripts
• Multi-file execution patterns
• Integration with schemachange
• Troubleshooting guide

Object Management

# List objects
snow object list warehouse -c default
snow object list table -c default

# Describe object
snow object describe table my_table -c default

# Create object
snow object create warehouse my_wh --size SMALL -c default

Connection Configuration

All Snowflake CLI commands use the -c flag to specify connection profiles:

snow sql -c default -q "SELECT * FROM table"
snow sql -c prod -q "SELECT * FROM table"

For complete connection setup, see the snowflake-connections skill for:

• Creating ~/.snowflake/connections.toml
• All authentication methods (SSO, key pair, OAuth, username/password)
• Multiple environment configurations (dev, staging, prod)
• Environment variable overrides
• Security best practices and troubleshooting

Common Patterns

Multi-Environment Deployment

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

case $ENV in
  dev)
    DB="DEV_DB"
    SCHEMA="DEV_SCHEMA"
    ;;
  prod)
    DB="PROD_DB"
    SCHEMA="PROD_SCHEMA"
    ;;
esac

snow sql -c default -i -D db=$DB -D schema=$SCHEMA <<EOF
CREATE OR REPLACE TABLE <% db %>.<% schema %>.my_table AS
SELECT * FROM <% db %>.<% schema %>.source_table;
EOF

For stage-specific patterns, see STAGE_OPERATIONS.md for:

• Migration scripts from stage
• Data pipeline execution
• Multi-environment deployments with stages
• CI/CD integration examples

Troubleshooting

Variable Not Substituted

Problem: Variable appears literally in SQL (e.g., SELECT * FROM <% db %>.orders)

Solutions:

1. Check syntax matches command type:

- snow sql -q → Use <% var %> - snow stage execute → Use {{var}} - Bash expansion → Use ${var}

1. Verify -D flag is before SQL
2. Ensure proper quoting for string values: -D name="'John'"

Syntax Conflicts

Problem: Query contains template-like text

Example: snow sql -q "SELECT '<% not_a_variable %>'"

Solution: Disable templating

snow sql --enable-templating NONE -q "SELECT '<% not_a_variable %>'"

Stage Execute Variables

Problem: Variables not working with snow stage execute

Solution: Use Jinja {{}} syntax (default for stage execute)

# ✅ CORRECT
snow stage execute @stage/script.sql -D var=value

# In script.sql: SELECT * FROM {{ var }}.table

Permission Errors

Problem: SQL access control error: Insufficient privileges

Solution: Grant appropriate permissions:

GRANT USAGE ON STAGE my_stage TO ROLE my_role;
GRANT READ, WRITE ON STAGE my_stage TO ROLE my_role;

Connection Failed

Problem: Can't connect to Snowflake

Quick Test:

snow connection test -c default

For comprehensive connection troubleshooting, see the snowflake-connections skill

Quick Reference

# Bash variables (shell expansion)
DB="PROD"
snow sql -c default -q "USE ${DB}_DATABASE"

# Standard syntax (default)
snow sql -c default -q "USE <% db %>" -D db=PROD

# Jinja syntax (explicit)
snow sql --enable-templating JINJA -c default -q "USE {{ db }}" -D db=PROD

# Stage execute (Jinja automatic)
snow stage execute @stage/script.sql -D db=PROD

# Disable templating
snow sql --enable-templating NONE -q "SELECT '<% literal %>'"

# String values need quotes
snow sql -D name="'John'" -D date="'2024-01-01'"

# Test connection
snow connection test -c default

# Multi-environment pattern
ENV="${1:-dev}"
case $ENV in
  dev) DB="DEV_DB" ;;
  prod) DB="PROD_DB" ;;
esac
snow sql -c default -i -D db=$DB <<EOF
SELECT * FROM <% db %>.orders;
EOF

Best Practices

✅ DO:

• Use bash variables for environment selection
• Use <% %> for inline SQL queries
• Use {{}} for staged SQL files (automatic)
• Organize staged scripts in subdirectories
• Quote string variable values: -D name="'value'"
• Test locally before deploying to production
• Use multiple connections for different environments

❌ DON'T:

• Mix variable syntaxes incorrectly
• Hardcode environment-specific values
• Use {{}} with snow sql without --enable-templating JINJA
• Forget to grant stage permissions
• Skip error handling in automation scripts

References

• STAGE_OPERATIONS.md - Comprehensive stage management and script execution
• snowflake-connections skill - Connection setup and authentication
• Snowflake CLI Documentation - Official documentation

Goal: Transform AI agents into expert Snowflake CLI operators who efficiently execute SQL, manage stages, deploy applications, and automate operations with proper variable handling and connection configuration.