finding-expensive-queries

Name: finding-expensive-queries
Author: AltimateAI

17installs7forks

How to install finding-expensive-queries?

altimateai/data-engineering-skills/finding-expensive-queries

$npx -y skills add AltimateAI/data-engineering-skills --skill finding-expensive-queries

Installs into the current project.

Summary

Finds and ranks expensive Snowflake queries by cost, time, or data scanned. Use when: (1) User asks to find slow, expensive, or problematic queries (2) Task mentions "query history", "top queries", "most expensive", or "slowest queries" (3) Analyzing warehouse costs or identifyin

Files · 1

View on GitHub

SKILL.md

1	# Finding Expensive Queries
2
3	Query history → Rank by metric → Identify patterns → Recommend optimizations
4
5	## Workflow
6
7	### 1. Ask What to Optimize For
8
9	Before querying, clarify:
10	- Time period? (last day, week, month)
11	- Metric? (execution time, bytes scanned, cost, spillage)
12	- Warehouse? (specific or all)
13	- User? (specific or all)
14
15	### 2. Find Expensive Queries by Cost
16
17	Use QUERY_ATTRIBUTION_HISTORY for credit/cost analysis:
18
19	```sql
20	SELECT
21	query_id,
22	warehouse_name,
23	user_name,
24	credits_attributed_compute,
25	start_time,
26	end_time,
27	query_tag
28	FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
29	WHERE start_time >= DATEADD('days', -7, CURRENT_TIMESTAMP())
30	ORDER BY credits_attributed_compute DESC
31	LIMIT 20;
32	```
33
34	### 3. Get Performance Stats for Specific Queries
35
36	Use QUERY_HISTORY for detailed performance metrics (run separately, not joined):
37
38	```sql
39	SELECT
40	query_id,
41	query_text,
42	total_elapsed_time/1000 as seconds,
43	bytes_scanned/1e9 as gb_scanned,
44	bytes_spilled_to_local_storage/1e9 as gb_spilled_local,
45	bytes_spilled_to_remote_storage/1e9 as gb_spilled_remote,
46	partitions_scanned,
47	partitions_total
48	FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
49	WHERE query_id IN ('<query_id_1>', '<query_id_2>', ...)
50	AND start_time >= DATEADD('days', -7, CURRENT_TIMESTAMP());
51	```
52
53	### 4. Identify Patterns
54
55	Look for:
56	- High `credits_attributed_compute` queries
57	- Same `query_hash` repeated (caching opportunity)
58	- `partitions_scanned = partitions_total` (no pruning)
59	- High `gb_spilled` (memory pressure)
60
61	### 5. Return Results
62
63	Provide:
64	1. Ranked list of expensive queries with key metrics
65	2. Common patterns identified
66	3. Top 3-5 optimization recommendations
67	4. Specific queries to investigate further
68
69	## Common Filters
70
71	```sql
72	-- Time range (required)
73	WHERE start_time >= DATEADD('days', -7, CURRENT_TIMESTAMP())
74
75	-- By warehouse
76	AND warehouse_name = 'ANALYTICS_WH'
77
78	-- By user
79	AND user_name = 'ETL_USER'
80
81	-- Only queries over cost threshold
82	AND credits_attributed_compute > 0.01
83
84	-- Only queries over time threshold
85	AND total_elapsed_time > 60000 -- over 1 minute
86	```

People who liked this also viewed

6 picks

Type

finding-expensive-queries

17installs7forks

View on GitHub

How to install finding-expensive-queries?

altimateai/data-engineering-skills/finding-expensive-queries

$npx -y skills add AltimateAI/data-engineering-skills --skill finding-expensive-queries

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Finding Expensive Queries
2
3	Query history → Rank by metric → Identify patterns → Recommend optimizations
4
5	## Workflow
6
7	### 1. Ask What to Optimize For
8
9	Before querying, clarify:
10	- Time period? (last day, week, month)
11	- Metric? (execution time, bytes scanned, cost, spillage)
12	- Warehouse? (specific or all)
13	- User? (specific or all)
14
15	### 2. Find Expensive Queries by Cost
16
17	Use QUERY_ATTRIBUTION_HISTORY for credit/cost analysis:
18
19	```sql
20	SELECT
21	query_id,
22	warehouse_name,
23	user_name,
24	credits_attributed_compute,
25	start_time,
26	end_time,
27	query_tag
28	FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
29	WHERE start_time >= DATEADD('days', -7, CURRENT_TIMESTAMP())
30	ORDER BY credits_attributed_compute DESC
31	LIMIT 20;
32	```
33
34	### 3. Get Performance Stats for Specific Queries
35
36	Use QUERY_HISTORY for detailed performance metrics (run separately, not joined):
37
38	```sql
39	SELECT
40	query_id,
41	query_text,
42	total_elapsed_time/1000 as seconds,
43	bytes_scanned/1e9 as gb_scanned,
44	bytes_spilled_to_local_storage/1e9 as gb_spilled_local,
45	bytes_spilled_to_remote_storage/1e9 as gb_spilled_remote,
46	partitions_scanned,
47	partitions_total
48	FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
49	WHERE query_id IN ('<query_id_1>', '<query_id_2>', ...)
50	AND start_time >= DATEADD('days', -7, CURRENT_TIMESTAMP());
51	```
52
53	### 4. Identify Patterns
54
55	Look for:
56	- High `credits_attributed_compute` queries
57	- Same `query_hash` repeated (caching opportunity)
58	- `partitions_scanned = partitions_total` (no pruning)
59	- High `gb_spilled` (memory pressure)
60
61	### 5. Return Results
62
63	Provide:
64	1. Ranked list of expensive queries with key metrics
65	2. Common patterns identified
66	3. Top 3-5 optimization recommendations
67	4. Specific queries to investigate further
68
69	## Common Filters
70
71	```sql
72	-- Time range (required)
73	WHERE start_time >= DATEADD('days', -7, CURRENT_TIMESTAMP())
74
75	-- By warehouse
76	AND warehouse_name = 'ANALYTICS_WH'
77
78	-- By user
79	AND user_name = 'ETL_USER'
80
81	-- Only queries over cost threshold
82	AND credits_attributed_compute > 0.01
83
84	-- Only queries over time threshold
85	AND total_elapsed_time > 60000 -- over 1 minute
86	```

People who liked this also viewed

6 picks

Type