optimizing-query-by-id

Name: optimizing-query-by-id
Author: AltimateAI

18installs7forks

How to install optimizing-query-by-id?

altimateai/data-engineering-skills/optimizing-query-by-id

$npx -y skills add AltimateAI/data-engineering-skills --skill optimizing-query-by-id

Installs into the current project.

Summary

Optimizes Snowflake query performance using query ID from history. Use when optimizing Snowflake queries for: (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 I

Files · 1

View on GitHub

SKILL.md

1	# Optimize Query from Query ID
2
3	Fetch query → Get profile → Apply best practices → Verify improvement → Return optimized query
4
5	## Workflow
6
7	### 1. Fetch Query Details from Query ID
8
9	```sql
10	SELECT
11	query_id,
12	query_text,
13	total_elapsed_time/1000 as seconds,
14	bytes_scanned/1e9 as gb_scanned,
15	bytes_spilled_to_local_storage/1e9 as gb_spilled_local,
16	bytes_spilled_to_remote_storage/1e9 as gb_spilled_remote,
17	partitions_scanned,
18	partitions_total,
19	rows_produced
20	FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
21	WHERE query_id = '<query_id>';
22	```
23
24	Note the key metrics:
25	- `seconds`: Total execution time
26	- `gb_scanned`: Data read (lower is better)
27	- `gb_spilled`: Spillage indicates memory pressure
28	- `partitions_scanned/total`: Partition pruning effectiveness
29
30	### 2. Get Query Profile Details
31
32	```sql
33	-- Get operator-level statistics
34	SELECT *
35	FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>'));
36	```
37
38	Look for:
39	- Operators with high `output_rows` vs `input_rows` (explosions)
40	- TableScan operators with high bytes
41	- Sort/Aggregate operators with spillage
42
43	### 3. Identify Optimization Opportunities
44
45	Based on profile, look for:
46
47	\| Metric \| Issue \| Fix \|
48	\|--------\|-------\|-----\|
49	\| partitions_scanned = partitions_total \| No pruning \| Add filter on cluster key \|
50	\| gb_spilled > 0 \| Memory pressure \| Simplify query, increase warehouse \|
51	\| High bytes_scanned \| Full scan \| Add selective filters, reduce columns \|
52	\| Join explosion \| Cartesian or bad key \| Fix join condition, filter before join \|
53
54	### 4. Apply Optimizations
55
56	Rewrite the query:
57	- Select only needed columns
58	- Filter early (before joins)
59	- Use CTEs to avoid repeated scans
60	- Ensure filters align with clustering keys
61	- Add LIMIT if full result not needed
62
63	### 5. Get Explain Plan for Optimized Query
64
65	```sql
66	EXPLAIN USING JSON
67	<optimized_query>;
68	```
69
70	### 6. Compare Plans
71
72	Compare original vs optimized:
73	- Fewer partitions scanned?
74	- Fewer intermediate rows?
75	- Better join order?
76
77	### 7. Return Results
78
79	Provide:
80	1. Original query metrics (time, data scanned, spillage)
81	2. Identified issues
82	3. The optimized query
83	4. Summary of changes made
84	5. Expected improvement
85
86	## Example Output
87
88	Original Query Metrics:
89	- Execution time: 45 seconds
90	- Data scanned: 12.3 GB
91	- Partitions: 500/500 (no pruning)
92	- Spillage: 2.1 GB
93
94	Issues Found:
95	1. No partition pruning - filtering on non-cluster column
96	2. SELECT * scanning unnecessary columns
97	3. Large table joined without pre-filtering
98
99	Optimized Query:
100	```sql
101	WITH filtered_events AS (
102	SELECT event_id, user_id, event_type, created_at
103	FROM events
104	WHERE created_at >= '2024-01-01'
105	AND created_at < '2024-02-01'
106	AND event_type = 'purchase'
107	)
108	SELECT fe.event_id, fe.created_at, u.name
109	FROM filtered_events fe
110	JOIN users u ON fe.user_id = u.id;
111	```
112
113	Changes:
114	- Added date range filter matching cluster key
115	- Replaced SELECT * with specific columns
116	- Pre-filtered in CTE before join
117
118	Expected Improvement:
119	- Partitions: 500 → ~15 (97% reduction)
120	- Data scanned: 12.3 GB → ~0.4 GB
121	- Estimated time: 45s → ~3s

People who liked this also viewed

6 picks

Type

optimizing-query-by-id

18installs7forks

View on GitHub

How to install optimizing-query-by-id?

altimateai/data-engineering-skills/optimizing-query-by-id

$npx -y skills add AltimateAI/data-engineering-skills --skill optimizing-query-by-id

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Optimize Query from Query ID
2
3	Fetch query → Get profile → Apply best practices → Verify improvement → Return optimized query
4
5	## Workflow
6
7	### 1. Fetch Query Details from Query ID
8
9	```sql
10	SELECT
11	query_id,
12	query_text,
13	total_elapsed_time/1000 as seconds,
14	bytes_scanned/1e9 as gb_scanned,
15	bytes_spilled_to_local_storage/1e9 as gb_spilled_local,
16	bytes_spilled_to_remote_storage/1e9 as gb_spilled_remote,
17	partitions_scanned,
18	partitions_total,
19	rows_produced
20	FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
21	WHERE query_id = '<query_id>';
22	```
23
24	Note the key metrics:
25	- `seconds`: Total execution time
26	- `gb_scanned`: Data read (lower is better)
27	- `gb_spilled`: Spillage indicates memory pressure
28	- `partitions_scanned/total`: Partition pruning effectiveness
29
30	### 2. Get Query Profile Details
31
32	```sql
33	-- Get operator-level statistics
34	SELECT *
35	FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>'));
36	```
37
38	Look for:
39	- Operators with high `output_rows` vs `input_rows` (explosions)
40	- TableScan operators with high bytes
41	- Sort/Aggregate operators with spillage
42
43	### 3. Identify Optimization Opportunities
44
45	Based on profile, look for:
46
47	\| Metric \| Issue \| Fix \|
48	\|--------\|-------\|-----\|
49	\| partitions_scanned = partitions_total \| No pruning \| Add filter on cluster key \|
50	\| gb_spilled > 0 \| Memory pressure \| Simplify query, increase warehouse \|
51	\| High bytes_scanned \| Full scan \| Add selective filters, reduce columns \|
52	\| Join explosion \| Cartesian or bad key \| Fix join condition, filter before join \|
53
54	### 4. Apply Optimizations
55
56	Rewrite the query:
57	- Select only needed columns
58	- Filter early (before joins)
59	- Use CTEs to avoid repeated scans
60	- Ensure filters align with clustering keys
61	- Add LIMIT if full result not needed
62
63	### 5. Get Explain Plan for Optimized Query
64
65	```sql
66	EXPLAIN USING JSON
67	<optimized_query>;
68	```
69
70	### 6. Compare Plans
71
72	Compare original vs optimized:
73	- Fewer partitions scanned?
74	- Fewer intermediate rows?
75	- Better join order?
76
77	### 7. Return Results
78
79	Provide:
80	1. Original query metrics (time, data scanned, spillage)
81	2. Identified issues
82	3. The optimized query
83	4. Summary of changes made
84	5. Expected improvement
85
86	## Example Output
87
88	Original Query Metrics:
89	- Execution time: 45 seconds
90	- Data scanned: 12.3 GB
91	- Partitions: 500/500 (no pruning)
92	- Spillage: 2.1 GB
93
94	Issues Found:
95	1. No partition pruning - filtering on non-cluster column
96	2. SELECT * scanning unnecessary columns
97	3. Large table joined without pre-filtering
98
99	Optimized Query:
100	```sql
101	WITH filtered_events AS (
102	SELECT event_id, user_id, event_type, created_at
103	FROM events
104	WHERE created_at >= '2024-01-01'
105	AND created_at < '2024-02-01'
106	AND event_type = 'purchase'
107	)
108	SELECT fe.event_id, fe.created_at, u.name
109	FROM filtered_events fe
110	JOIN users u ON fe.user_id = u.id;
111	```
112
113	Changes:
114	- Added date range filter matching cluster key
115	- Replaced SELECT * with specific columns
116	- Pre-filtered in CTE before join
117
118	Expected Improvement:
119	- Partitions: 500 → ~15 (97% reduction)
120	- Data scanned: 12.3 GB → ~0.4 GB
121	- Estimated time: 45s → ~3s

People who liked this also viewed

6 picks

Type