optimizing-query-text

Name: optimizing-query-text
Author: AltimateAI

21installs7forks

How to install optimizing-query-text?

altimateai/data-engineering-skills/optimizing-query-text

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

Installs into the current project.

Summary

Optimizes Snowflake SQL query performance from provided query text. Use when optimizing Snowflake SQL for: (1) User provides or pastes a SQL query and asks to optimize, tune, or improve it (2) Task mentions "slow query", "make faster", "improve performance", "optimize SQL", or "q

Files · 1

View on GitHub

SKILL.md

1	# Optimize Query from SQL Text
2
3	## OUTPUT FORMAT
4
5	Return ONLY the optimized SQL query. No markdown formatting, no explanations, no bullet points - just pure SQL that can be executed directly in Snowflake.
6
7	## CRITICAL: Semantic Preservation Rules
8
9	The optimized query MUST return IDENTICAL results to the original.
10
11	Before returning ANY optimization, verify:
12	- Same columns: Exact same columns in exact same order with exact same aliases
13	- Same rows: Filter conditions must be semantically equivalent
14	- Same ordering: Preserve `ORDER BY` exactly as written
15	- Same limits: If original has `LIMIT N`, keep `LIMIT N`. If no LIMIT, do NOT add one.
16
17	If you cannot guarantee identical results, return the original query unchanged.
18
19	---
20
21	## Pattern 1: Function on Filter Column
22
23	Problem: Functions on columns in WHERE clause prevent partition pruning and index usage.
24
25	### CAN Fix
26
27	\| Original \| Optimized \| Why Safe \|
28	\|----------\|-----------\|----------\|
29	\| `WHERE DATE(ts) = '2024-01-01'` \| `WHERE ts >= '2024-01-01' AND ts < '2024-01-02'` \| Equivalent range \|
30	\| `WHERE YEAR(dt) = 2024` \| `WHERE dt >= '2024-01-01' AND dt < '2025-01-01'` \| Equivalent range \|
31	\| `WHERE MONTH(dt) = 3 AND YEAR(dt) = 2024` \| `WHERE dt >= '2024-03-01' AND dt < '2024-04-01'` \| Equivalent range \|
32	\| `WHERE DATE(ts) >= '2024-01-01' AND DATE(ts) < '2024-02-01'` \| `WHERE ts >= '2024-01-01' AND ts < '2024-02-01'` \| Same boundaries \|
33	\| `WHERE YEAR(dt) BETWEEN 1995 AND 1996` \| `WHERE dt >= '1995-01-01' AND dt < '1997-01-01'` \| Equivalent range \|
34
35	### CANNOT Fix
36
37	\| Pattern \| Why Not \|
38	\|---------\|---------\|
39	\| `WHERE YEAR(dt) IN (SELECT year FROM ...)` \| Dynamic values, cannot precompute range \|
40	\| `WHERE DATE(ts) = DATE(other_col)` \| Comparing two columns, both need function \|
41	\| `WHERE EXTRACT(DOW FROM dt) = 1` \| Day-of-week has no contiguous range \|
42	\| `WHERE DATE_TRUNC('month', dt) = '2024-01-01'` in GROUP BY \| Needed for grouping logic \|
43	\| `SELECT YEAR(dt) AS yr ... GROUP BY YEAR(dt)` \| Function in SELECT/GROUP BY is fine, only filter matters \|
44
45	---
46
47	## Pattern 2: Function on JOIN Column
48
49	Problem: Functions on JOIN columns prevent hash joins, forcing slower nested loop joins.
50
51	### CAN Fix
52
53	\| Original \| Optimized \| Why Safe \|
54	\|----------\|-----------\|----------\|
55	\| `ON CAST(a.id AS VARCHAR) = CAST(b.id AS VARCHAR)` \| `ON a.id = b.id` \| If both are same type (e.g., INTEGER) \|
56	\| `ON UPPER(a.code) = UPPER(b.code)` \| `ON a.code = b.code` \| If data is already consistently cased \|
57	\| `ON TRIM(a.name) = TRIM(b.name)` \| `ON a.name = b.name` \| If data has no leading/trailing spaces \|
58
59	### CANNOT Fix
60
61	\| Pattern \| Why Not \|
62	\|---------\|---------\|
63	\| `ON CAST(a.id AS VARCHAR) = b.string_id` \| Types genuinely differ, CAST required \|
64	\| `ON DATE(a.timestamp) = b.date_col` \| Different granularity, DATE() required \|
65	\| `ON UPPER(a.code) = b.code` \| If b.code might have different case \|
66	\| `ON a.id = b.id + 1` \| Arithmetic transformation, cannot remove \|
67
68	---
69
70	## Pattern 3: NOT IN Subquery
71
72	Problem: `NOT IN` has poor performance and unexpected NULL behavior.
73
74	### CAN Fix
75
76	\| Original \| Optimized \| Why Safe \|
77	\|----------\|-----------\|----------\|
78	\| `WHERE id NOT IN (SELECT id FROM t WHERE ...)` \| `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id AND ...)` \| Equivalent when subquery column is NOT NULL \|
79	\| `WHERE id NOT IN (SELECT id FROM t)` where id has NOT NULL constraint \| `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id)` \| NOT NULL guarantees equivalence \|
80
81	### CANNOT Fix
82
83	\| Pattern \| Why Not \|
84	\|---------\|---------\|
85	\| `WHERE id NOT IN (SELECT nullable_col FROM t)` \| If subquery returns NULL, NOT IN returns no rows; NOT EXISTS doesn't \|
86	\| `WHERE (a, b) NOT IN (SELECT x, y FROM t)` \| Multi-column NOT IN has complex NULL semantics \|
87
88	Key Rule: Only convert NOT IN to NOT EXISTS if you can verify the subquery column cannot be NULL.
89
90	---
91
92	## Pattern 4: Repeated Subquery
93
94	Problem: Same subquery executed multiple times causes redundant scans.
95
96	### CAN Fix
97
98	\| Original \| Optimized \|
99	\|----------\|-----------\|
100	\| Subquery appears 2+ times identically \| Extract to CTE, reference CTE multiple times \|
101	\| Same aggregation used in multiple places \| Compute once in CTE \|
102
103	### CANNOT Fix
104
105	\| Pattern \| Why Not \|
106	\|---------\|---------\|
107	\| Correlated subquery (references outer table) \| Each execution is different, cannot cache \|
108	\| Subqueries with different filters \| Not actually the same subquery \|
109	\| Subquery in SELECT that depends on current row \| Correlation pr

People who liked this also viewed

6 picks

Type

optimizing-query-text

21installs7forks

View on GitHub

How to install optimizing-query-text?

altimateai/data-engineering-skills/optimizing-query-text

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

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Optimize Query from SQL Text
2
3	## OUTPUT FORMAT
4
5	Return ONLY the optimized SQL query. No markdown formatting, no explanations, no bullet points - just pure SQL that can be executed directly in Snowflake.
6
7	## CRITICAL: Semantic Preservation Rules
8
9	The optimized query MUST return IDENTICAL results to the original.
10
11	Before returning ANY optimization, verify:
12	- Same columns: Exact same columns in exact same order with exact same aliases
13	- Same rows: Filter conditions must be semantically equivalent
14	- Same ordering: Preserve `ORDER BY` exactly as written
15	- Same limits: If original has `LIMIT N`, keep `LIMIT N`. If no LIMIT, do NOT add one.
16
17	If you cannot guarantee identical results, return the original query unchanged.
18
19	---
20
21	## Pattern 1: Function on Filter Column
22
23	Problem: Functions on columns in WHERE clause prevent partition pruning and index usage.
24
25	### CAN Fix
26
27	\| Original \| Optimized \| Why Safe \|
28	\|----------\|-----------\|----------\|
29	\| `WHERE DATE(ts) = '2024-01-01'` \| `WHERE ts >= '2024-01-01' AND ts < '2024-01-02'` \| Equivalent range \|
30	\| `WHERE YEAR(dt) = 2024` \| `WHERE dt >= '2024-01-01' AND dt < '2025-01-01'` \| Equivalent range \|
31	\| `WHERE MONTH(dt) = 3 AND YEAR(dt) = 2024` \| `WHERE dt >= '2024-03-01' AND dt < '2024-04-01'` \| Equivalent range \|
32	\| `WHERE DATE(ts) >= '2024-01-01' AND DATE(ts) < '2024-02-01'` \| `WHERE ts >= '2024-01-01' AND ts < '2024-02-01'` \| Same boundaries \|
33	\| `WHERE YEAR(dt) BETWEEN 1995 AND 1996` \| `WHERE dt >= '1995-01-01' AND dt < '1997-01-01'` \| Equivalent range \|
34
35	### CANNOT Fix
36
37	\| Pattern \| Why Not \|
38	\|---------\|---------\|
39	\| `WHERE YEAR(dt) IN (SELECT year FROM ...)` \| Dynamic values, cannot precompute range \|
40	\| `WHERE DATE(ts) = DATE(other_col)` \| Comparing two columns, both need function \|
41	\| `WHERE EXTRACT(DOW FROM dt) = 1` \| Day-of-week has no contiguous range \|
42	\| `WHERE DATE_TRUNC('month', dt) = '2024-01-01'` in GROUP BY \| Needed for grouping logic \|
43	\| `SELECT YEAR(dt) AS yr ... GROUP BY YEAR(dt)` \| Function in SELECT/GROUP BY is fine, only filter matters \|
44
45	---
46
47	## Pattern 2: Function on JOIN Column
48
49	Problem: Functions on JOIN columns prevent hash joins, forcing slower nested loop joins.
50
51	### CAN Fix
52
53	\| Original \| Optimized \| Why Safe \|
54	\|----------\|-----------\|----------\|
55	\| `ON CAST(a.id AS VARCHAR) = CAST(b.id AS VARCHAR)` \| `ON a.id = b.id` \| If both are same type (e.g., INTEGER) \|
56	\| `ON UPPER(a.code) = UPPER(b.code)` \| `ON a.code = b.code` \| If data is already consistently cased \|
57	\| `ON TRIM(a.name) = TRIM(b.name)` \| `ON a.name = b.name` \| If data has no leading/trailing spaces \|
58
59	### CANNOT Fix
60
61	\| Pattern \| Why Not \|
62	\|---------\|---------\|
63	\| `ON CAST(a.id AS VARCHAR) = b.string_id` \| Types genuinely differ, CAST required \|
64	\| `ON DATE(a.timestamp) = b.date_col` \| Different granularity, DATE() required \|
65	\| `ON UPPER(a.code) = b.code` \| If b.code might have different case \|
66	\| `ON a.id = b.id + 1` \| Arithmetic transformation, cannot remove \|
67
68	---
69
70	## Pattern 3: NOT IN Subquery
71
72	Problem: `NOT IN` has poor performance and unexpected NULL behavior.
73
74	### CAN Fix
75
76	\| Original \| Optimized \| Why Safe \|
77	\|----------\|-----------\|----------\|
78	\| `WHERE id NOT IN (SELECT id FROM t WHERE ...)` \| `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id AND ...)` \| Equivalent when subquery column is NOT NULL \|
79	\| `WHERE id NOT IN (SELECT id FROM t)` where id has NOT NULL constraint \| `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id)` \| NOT NULL guarantees equivalence \|
80
81	### CANNOT Fix
82
83	\| Pattern \| Why Not \|
84	\|---------\|---------\|
85	\| `WHERE id NOT IN (SELECT nullable_col FROM t)` \| If subquery returns NULL, NOT IN returns no rows; NOT EXISTS doesn't \|
86	\| `WHERE (a, b) NOT IN (SELECT x, y FROM t)` \| Multi-column NOT IN has complex NULL semantics \|
87
88	Key Rule: Only convert NOT IN to NOT EXISTS if you can verify the subquery column cannot be NULL.
89
90	---
91
92	## Pattern 4: Repeated Subquery
93
94	Problem: Same subquery executed multiple times causes redundant scans.
95
96	### CAN Fix
97
98	\| Original \| Optimized \|
99	\|----------\|-----------\|
100	\| Subquery appears 2+ times identically \| Extract to CTE, reference CTE multiple times \|
101	\| Same aggregation used in multiple places \| Compute once in CTE \|
102
103	### CANNOT Fix
104
105	\| Pattern \| Why Not \|
106	\|---------\|---------\|
107	\| Correlated subquery (references outer table) \| Each execution is different, cannot cache \|
108	\| Subqueries with different filters \| Not actually the same subquery \|
109	\| Subquery in SELECT that depends on current row \| Correlation pr

People who liked this also viewed

6 picks

Type