From snowflake-skills
Optimizes Snowflake queries using query ID: fetches history and profile, analyzes metrics like bytes scanned, spillage, partition pruning, identifies bottlenecks, and returns rewritten SQL with improvements.
npx claudepluginhub altimateai/data-engineering-skills --plugin dbt-skillsThis skill uses the workspace's default tool permissions.
**Fetch query → Get profile → Apply best practices → Verify improvement → Return optimized query**
Guides Next.js Cache Components and Partial Prerendering (PPR) with cacheComponents enabled. Implements 'use cache', cacheLife(), cacheTag(), revalidateTag(), static/dynamic optimization, and cache debugging.
Guides building MCP servers enabling LLMs to interact with external services via tools. Covers best practices, TypeScript/Node (MCP SDK), Python (FastMCP).
Generates original PNG/PDF visual art via design philosophy manifestos for posters, graphics, and static designs on user request.
Fetch query → Get profile → Apply best practices → Verify improvement → Return optimized query
SELECT
query_id,
query_text,
total_elapsed_time/1000 as seconds,
bytes_scanned/1e9 as gb_scanned,
bytes_spilled_to_local_storage/1e9 as gb_spilled_local,
bytes_spilled_to_remote_storage/1e9 as gb_spilled_remote,
partitions_scanned,
partitions_total,
rows_produced
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_id = '<query_id>';
Note the key metrics:
seconds: Total execution timegb_scanned: Data read (lower is better)gb_spilled: Spillage indicates memory pressurepartitions_scanned/total: Partition pruning effectiveness-- Get operator-level statistics
SELECT *
FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>'));
Look for:
output_rows vs input_rows (explosions)Based on profile, look for:
| Metric | Issue | Fix |
|---|---|---|
| partitions_scanned = partitions_total | No pruning | Add filter on cluster key |
| gb_spilled > 0 | Memory pressure | Simplify query, increase warehouse |
| High bytes_scanned | Full scan | Add selective filters, reduce columns |
| Join explosion | Cartesian or bad key | Fix join condition, filter before join |
Rewrite the query:
EXPLAIN USING JSON
<optimized_query>;
Compare original vs optimized:
Provide:
Original Query Metrics:
Issues Found:
Optimized Query:
WITH filtered_events AS (
SELECT event_id, user_id, event_type, created_at
FROM events
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01'
AND event_type = 'purchase'
)
SELECT fe.event_id, fe.created_at, u.name
FROM filtered_events fe
JOIN users u ON fe.user_id = u.id;
Changes:
Expected Improvement: