Optimizing Query By ID Agent Skill
| (1) User provides a Snowflake query_id (UUID format) to analyze or optimize (2) Task mentions "slow query", "optimize", "query history", or "query profile" with a query ID (3) Analyzing query performance metrics - bytes scanned, spillage, partition pruning (4) User references a previously run query that needs optimization Fetches query profile, identifies bottlenecks, returns optimized SQL with expected improvements.
Install
one command, takes just this skill from the repositorynpx skills add https://github.com/AltimateAI/data-engineering-skills --skill optimizing-query-by-idThe instruction itself
10 sections, as written by the authorOptimize Query from Query ID
Fetch query → Get profile → Apply best practices → Verify improvement → Return optimized query
Workflow
1. Fetch Query Details from Query ID
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
2. Get Query Profile Details
-- Get operator-level statistics
SELECT *
FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>'));
Look for:
- Operators with high
output_rowsvsinput_rows(explosions) - TableScan operators with high bytes
- Sort/Aggregate operators with spillage
3. Identify Optimization Opportunities
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 |
4. Apply Optimizations
Rewrite the query:
- Select only needed columns
- Filter early (before joins)
- Use CTEs to avoid repeated scans
- Ensure filters align with clustering keys
- Add LIMIT if full result not needed
5. Get Explain Plan for Optimized Query
EXPLAIN USING JSON
<optimized_query>;
6. Compare Plans
Compare original vs optimized:
- Fewer partitions scanned?
- Fewer intermediate rows?
- Better join order?
7. Return Results
Provide:
- Original query metrics (time, data scanned, spillage)
- Identified issues
- The optimized query
- Summary of changes made
- Expected improvement
Example Output
Original Query Metrics:
- Execution time: 45 seconds
- Data scanned: 12.3 GB
- Partitions: 500/500 (no pruning)
- Spillage: 2.1 GB
Issues Found:
- No partition pruning - filtering on non-cluster column
- SELECT * scanning unnecessary columns
- Large table joined without pre-filtering
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:
- Added date range filter matching cluster key
- Replaced SELECT * with specific columns
- Pre-filtered in CTE before join
Expected Improvement:
- Partitions: 500 → ~15 (97% reduction)
- Data scanned: 12.3 GB → ~0.4 GB
- Estimated time: 45s → ~3s
Other skills for the same job
different authors, same section of the catalogue
Query openFDA API for drugs, devices, adverse events, recalls, regulatory submissions (510k, PMA), substance identification (UNII), for FDA regulatory data analysis and safety research.
Access AlphaFold 200M+ AI-predicted protein structures. Retrieve structures by UniProt ID, download PDB/mmCIF files, analyze confidence metrics (pLDDT, PAE), for drug discovery and structural biology.
Access AlphaFold's 200M+ AI-predicted protein structures. Retrieve structures by UniProt ID, download PDB/mmCIF files, analyze confidence metrics (pLDDT, PAE), for drug discovery and structural biology.
> Generate or improve a company-specific data analysis skill by extracting tribal knowledge from analysts. "Help me create a skill for our database", "Generate a data skill for [company]" → Discovers schemas, asks key questions, generates initial skill with reference files "Update the data skill with [metrics/tables/terminology]", "Improve the [domain] reference" → Loads existing skill, asks targeted questions, appends/updates reference files Use when data analysts want Claude to understand their company's specific data warehouse, terminology, metrics definitions, and common query patterns.
Master advanced AgentDB features including QUIC synchronization, multi-database management, custom distance metrics, hybrid search, and distributed systems integration. Use when building distributed AI systems, multi-agent coordination, or advanced vector search applications.
NCBI GEO access via GEOparse and E-utilities. Search by keyword/organism/platform, download GSE series matrices, parse GPL annotations, extract GSM metadata, load expression matrices into pandas. For single-cell use cellxgene-census; for multi-DB access use gget-genomic-databases.
Master advanced AgentDB features including QUIC synchronization, multi-database management, custom distance metrics, and hybrid search for distributed AI systems.
Master advanced AgentDB features including QUIC synchronization, multi-database management, custom distance metrics, hybrid search, and distributed systems integration. Use when building distributed AI systems, multi-agent coordination, or advanced vector search applications.
How to use it
Take altimateai/optimizing-query-by-id from the repository into ~/.claude/skills for personal
use, or into .claude/skills inside a project.
The agent identifies a skill by the name field in its header. Two skills with the
same name cannot sit side by side — one of them will be ignored.