borghei/Claude-Skills / engineering/snowflake-development
snowflake-development
VerifiedReview Snowflake SQL for pruning and join problems, size warehouses and cut credit spend. Use when a Snowflake query is slow or expensive, or when sizing or reviewing warehouses.
Snowflake Development
Category: Engineering Domain: Data Warehouse
Overview
The Snowflake Development skill provides tools for analyzing and optimizing Snowflake SQL queries, recommending warehouse sizing, and enforcing Snowflake-specific best practices. Helps data engineers reduce costs and improve query performance.
When to Use
- A Snowflake query is slow or a warehouse bill jumped and you need the likely causes.
- Sizing a new warehouse for an ETL, BI, ad-hoc, data-science or ingest workload.
- Reviewing SQL or a data model before it goes into a scheduled pipeline.
For other databases use sql-database-assistant; for pipeline orchestration use senior-data-engineer.
Clarify First
Before analyzing or sizing, settle the inputs below. When one is missing or vague and would change the result, ask about it; when it wouldn't, use a sensible default and say which one you used.
- Action: analyze / optimize / warehouse-sizing (
--action; selects the workflow). - SQL file or query: the specific query(ies) to optimize (
--file; the subject of the analysis). - Workload type & data volume: ETL / BI / ad-hoc and the GB scale (
--workload/--data-volume; drives the warehouse recommendation).
Ask about the two or three inputs that matter most for this request, not the whole list. If the user wants a result straight away, produce it and state your assumptions at the top.
Quick Start
# Analyze a Snowflake SQL file for optimization opportunities (bundled sample: 4 issues)
python scripts/snowflake_query_helper.py --file assets/sample_queries.sql --action analyze
# Get warehouse sizing recommendations, costed at your hours and contract credit price
python scripts/snowflake_query_helper.py --action warehouse-sizing --workload "etl" --data-volume "500GB" --hours 4 --credit-price 3
# Optimize a specific query
python scripts/snowflake_query_helper.py --file slow_query.sql --action optimize
Tools Overview
| Tool | Purpose | Key Flags |
|---|---|---|
snowflake_query_helper.py | Analyze, optimize Snowflake SQL and recommend warehouse sizes | --file/--sql, --action analyze/optimize/warehouse-sizing, --workload, --data-volume, --hours, --credit-price, --format human/json |
Exit codes: 0 ok (analysis found no critical issue); 1 a critical issue was found;
2 bad input (missing file, no SQL given).
Workflows
Query Performance Optimization
- Collect the expensive queries with real numbers (needs access to the
SNOWFLAKE.ACCOUNT_USAGEshare; it lags by up to about 45 minutes):SELECT query_id, warehouse_name, total_elapsed_time / 1000 AS seconds, bytes_scanned, partitions_scanned, partitions_total, query_text FROM snowflake.account_usage.query_history WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP()) AND execution_status = 'SUCCESS' ORDER BY total_elapsed_time DESC LIMIT 20; - Run the analyzer on those query texts for pattern-level fixes (SELECT *, functions on filter columns, always-true joins).
- For each query, open the Query Profile: the real evidence is partitions scanned vs total, spilling to local/remote storage, and the most expensive operator. Prefer it over the script whenever they disagree.
- Apply the change and verify against the same metrics before/after; done when elapsed time or bytes scanned fell on the same input.
Warehouse Right-Sizing
- Identify workload type (ETL, BI, ad-hoc, etc.)
- Run warehouse-sizing with data volume
- Review recommendations
- Implement multi-cluster settings if applicable
Scope & Limitations
In Scope:
- Rule-based review of Snowflake SQL from a file or
--sqltext withsnowflake_query_helper.py --action analyzeoroptimize, with a complexity score and a suggested warehouse size per query. - Warehouse sizing for five workload types (etl, bi, ad-hoc, data-science, ingest) from a data volume, with credits per hour, an estimated daily cost and a
CREATE WAREHOUSEstatement. - Query, warehouse, cost and data modeling practices in
references/snowflake-best-practices.md.
Out of Scope:
- Connecting to Snowflake, reading query history or query profiles, or running any statement.
- SQL work for other databases (see
engineering/sql-database-assistant/). - Building and orchestrating data pipelines (see
engineering/senior-data-engineer/). - Access control, governance and data sharing setup.
Important Caveats:
- Analysis matches patterns in SQL text. It does not see execution plans, table sizes or clustering, so it misses issues that only show at run time.
- Sizing comes from a fixed table of data volume and concurrency limits. It is a starting point to adjust with observed query performance.
- Cost figures default to 8 hours a day at $2 per credit; pass
--hoursand--credit-pricefor your contract. Credit prices depend on edition, region and contract, and Snowflake features and pricing change over time.
Reference Documentation
- Snowflake Best Practices - Query patterns, warehouse management, cost optimization
Common Patterns
Cost Reduction
- Right-size warehouses (don't use XL for small queries)
- Set auto-suspend to 60 seconds for ad-hoc warehouses
- Use materialized views for frequently accessed aggregations
- Let Snowflake's automatic micro-partitioning do its job; add a clustering key only to very large tables (multi-terabyte) whose frequent filters scan most partitions (
partitions_scannedclose topartitions_total), and checkSYSTEM$CLUSTERING_INFORMATIONfirst, since automatic reclustering costs credits - Avoid SELECT * in production queries
Anti-Patterns
Sizing Up to Fix a Slow Query
Mistake: Moving a slow query to a larger warehouse without reading its profile. Why it happens: It is one setting and often helps a little. Instead: Check the Query Profile first. Poor pruning or a bad join is not fixed by more compute; spilling to remote storage is the case where a larger size usually pays for itself.
Clustering Every Large Table
Mistake: Adding clustering keys as routine "partitioning". Why it happens: Other warehouses need explicit partitioning. Instead: Snowflake micro-partitions automatically. Cluster only when a frequent filter on a very large table scans most partitions, and watch the reclustering credits.
Warehouses That Never Sleep
Mistake: Leaving AUTO_SUSPEND at hours or disabled on ad-hoc and BI warehouses.
Why it happens: Users dislike the resume delay.
Instead: Suspend after 60-300 seconds; resume takes seconds while idle compute bills every second it runs.
Files
| File | Purpose |
|---|---|
scripts/snowflake_query_helper.py | SQL pattern analysis and warehouse sizing |
assets/sample_queries.sql | Two queries with deliberate problems |
references/snowflake-best-practices.md | Query, warehouse, cost and modelling practices |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |