snowflake

Snowflake

Two modes. Pick based on intent:

========================================

Query mode — run SQL via the artemis connection profile, explore schema and data

Execute ad-hoc Snowflake queries using SnowSQL with key pair authentication. Accepts raw SQL or natural language descriptions.

Connection

Authentication uses key pair auth via connection profiles in ~/.snowsql/config:

• artemis — Default warehouse (ANALYTICS_XL), PC_DBT_ROLE
• dbt — DBT database/schema (PC_DBT_DB.PROD)

Run a query:

snowsql -c artemis -q "YOUR SQL HERE"

For multi-line or complex queries, use a heredoc:

snowsql -c artemis -q "$(cat <<'EOF'
SELECT table_schema, table_name, row_count
FROM information_schema.tables
WHERE table_schema = 'PROD'
ORDER BY row_count DESC
LIMIT 10;
EOF
)"

Procedure

Step 1: Parse the Request

If raw SQL provided:

• Validate the query has an appropriate LIMIT clause
• Add LIMIT if missing (default: 100 for SELECT *, 1000 for aggregations)

If natural language description:

• Identify the target table(s)
• Construct appropriate SQL query
• Always include LIMIT clause

Step 2: Validate Query Safety

MUST include LIMIT for:

• SELECT * queries
• Non-aggregated queries on fact tables
• Any query that could return unbounded rows

Exceptions (LIMIT optional):

• Aggregations with GROUP BY and constrained date range
• COUNT(*) queries
• INFORMATION_SCHEMA queries
• SHOW and DESCRIBE commands

NEVER execute:

• DROP, DELETE, TRUNCATE, CREATE statements
• Queries without schema qualification (must use DATABASE.SCHEMA.TABLE)

LIMIT guidance:

Purpose	LIMIT
Quick sanity check	10
Pattern exploration	100
Sample analysis	1,000
Full export (rare)	User must explicitly request

Step 3: Execute Query

snowsql -c artemis -q "<SQL_QUERY>"

Use -o output_format=FORMAT for different output:

• psql (default) — PostgreSQL-style tables
• csv — comma-separated
• tsv — tab-separated
• json — JSON format

Step 4: Format Results

• Use markdown tables for small result sets (<20 rows)
• Summarize large result sets with key statistics
• Highlight NULL values and data quality issues
• Offer to refine or expand the query

---

Common Query Patterns

Check table freshness:

SELECT MAX(date) as latest_date, MIN(date) as earliest_date, COUNT(*) as total_rows
FROM PC_DBT_DB.PROD.<TABLE_NAME>;

Check data by chain:

SELECT chain, COUNT(*) as row_count, MAX(date) as latest_date
FROM PC_DBT_DB.PROD.<TABLE_NAME>
GROUP BY chain
ORDER BY row_count DESC
LIMIT 50;

Sample recent data:

SELECT * FROM PC_DBT_DB.PROD.<TABLE_NAME>
WHERE date >= CURRENT_DATE - 7
ORDER BY date DESC
LIMIT 100;

Check for NULLs:

SELECT
    COUNT(*) as total,
    COUNT(<COLUMN>) as non_null,
    COUNT(*) - COUNT(<COLUMN>) as null_count,
    ROUND(100.0 * (COUNT(*) - COUNT(<COLUMN>)) / COUNT(*), 2) as null_pct
FROM PC_DBT_DB.PROD.<TABLE_NAME>;

Compare dev vs prod:

SELECT 'dev' as env, COUNT(*) as rows FROM PC_DBT_DB.DEV.DEV_<USER>.<MODEL>
UNION ALL
SELECT 'prod' as env, COUNT(*) as rows FROM PC_DBT_DB.PROD.<MODEL>;

Table metadata:

SELECT table_name, row_count, bytes / (1024*1024*1024) as size_gb, created, last_altered
FROM PC_DBT_DB.INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'PROD' AND table_name ILIKE '%<PATTERN>%'
ORDER BY row_count DESC
LIMIT 20;

Column info:

SELECT column_name, data_type, is_nullable
FROM PC_DBT_DB.INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'PROD' AND table_name = '<TABLE_NAME>'
ORDER BY ordinal_position;

List databases / schemas / tables:

snowsql -c artemis -q "SHOW DATABASES;"
snowsql -c artemis -q "SHOW SCHEMAS IN DATABASE PC_DBT_DB;"
snowsql -c artemis -q "SHOW TABLES IN SCHEMA PC_DBT_DB.PROD;"
snowsql -c artemis -q "DESCRIBE TABLE PC_DBT_DB.PROD.<TABLE_NAME>;"

---

Troubleshooting

Query timeout:

• Add date filters to reduce scan
• Use smaller warehouse for simple queries
• Check if table is clustered and filter by cluster key

Large result set:

• Add or reduce LIMIT
• Use aggregations instead of raw SELECT
• Filter by date range

Connection issues:

• Verify ~/.snowsql/config has the connection profile
• Check key pair files exist at the paths referenced in config
• Run snowsql -c artemis -q "SELECT 1;" to test connectivity

========================================

Cutover mode — migrate a project's Snowflake connection to artemis-python-tools with RSA key-pair auth

Migrate a project from custom Snowflake connection logic to the centralized artemis-python-tools credential adapter pattern with RSA key-pair auth support.

Read migration-steps.md for the full procedure with code templates and commands.

When to use

• Project has its own snowflake.connector.connect() calls
• Need to add RSA key-pair auth for service accounts
• Standardizing on SYSTEM_SNOWFLAKE_* env var naming
• Deploying to Railway/production where key files aren't on disk

Pre-flight checks

Before starting, confirm:

• You have access to the project's deployment platform (Railway, etc.)
• You know whether the project uses a central Settings class or bare env vars
• Service account credentials are available (for RSA key-pair auth)

Migration steps

Follow each step in migration-steps.md in order:

1. Discover current connection code — Find all snowflake.connector.connect() call sites, credential config, Settings classes, and test mocks
2. Check artemis-python-tools dependency — Install with --no-deps if missing; add to build command, not pyproject.toml deps
3. Create credentials adapter — Pattern A (Settings class, preferred) or Pattern B (bare env vars). The adapter MUST respect the project's existing settings management
4. Refactor connection code — Replace manual connection builders with create_snowflake_connection() calls; remove dead auth methods
5. Rename env vars — Migrate SNOWFLAKE_* to SYSTEM_SNOWFLAKE_*; add RSA key fields to Settings class if using Pattern A
6. Update tests — Mock the new adapter, drop auth-specific test coverage
7. Deploy — Set new env vars FIRST, deploy code, verify, then remove old vars

Rollback guidance

If issues arise after deployment:

• The old SNOWFLAKE_* env vars are still present until Step 7.4 — revert the code deploy and the old config still works
• If old env vars were already removed, re-add them and revert the code change
• The credentials adapter is a single file — removing it and restoring the old connection methods is a clean revert

Notes

• artemis-python-tools pins fastapi==0.115.12 — always install with --no-deps to avoid conflicts
• Raw PEM content support (SYSTEM_SNOWFLAKE_PRIVATE_KEY) enables key-pair auth in environments where key files aren't on disk (Railway, containers)
• If a central Settings class exists, never bypass it with SnowflakeCredentials.from_env() — that creates a parallel config path

========================================

Migration

Consolidated from legacy claudefather skills. Pick the mode based on intent.