6# Snowflake Development
7
8Snowflake SQL, data pipelines, Cortex AI, and Snowpark Python development. Covers the colon-prefix rule, semi-structured data, MERGE upserts, Dynamic Tables, Streams+Tasks, Cortex AI functions, agent specs, performance tuning, and security hardening.
9
10> Originally contributed by [James Cha-Earley](https://github.com/jamescha-earley) — enhanced and integrated by the claude-skills team.
11
12## Quick Start
13
14```bash
15# Generate a MERGE upsert template
16python scripts/snowflake_query_helper.py merge --target customers --source staging_customers --key customer_id --columns name,email,updated_at
17
18# Generate a Dynamic Table template
19python scripts/snowflake_query_helper.py dynamic-table --name cleaned_events --warehouse transform_wh --lag "5 minutes"
20
21# Generate RBAC grant statements
22python scripts/snowflake_query_helper.py grant --role analyst_role --database analytics --schemas public,staging --privileges SELECT,USAGE
23```
24
25---
26
27## SQL Best Practices
28
29### Naming and Style
30
31- Use snake_case for all identifiers. Avoid double-quoted identifiers -- they force case-sensitive names that require constant quoting.
32- Use CTEs (WITH clauses) over nested subqueries.
33- Use CREATE OR REPLACE for idempotent DDL.
34- Use explicit column lists -- never SELECT * in production. Snowflake's columnar storage scans only referenced columns, so explicit lists reduce I/O.
35
36### Stored Procedures -- Colon Prefix Rule
37
38In SQL stored procedures (BEGIN...END blocks), variables and parameters **must** use the colon : prefix inside SQL statements. Without it, Snowflake treats them as column identifiers and raises "invalid identifier" errors.
39
40```sql
41-- WRONG: missing colon prefix
42SELECT name INTO result FROM users WHERE id = p_id;
43
44-- CORRECT: colon prefix on both variable and parameter
45SELECT name INTO :result FROM users WHERE id = :p_id;
46```
47
48This applies to DECLARE variables, LET variables, and procedure parameters when used inside SELECT, INSERT, UPDATE, DELETE, or MERGE.
49
50### Semi-Structured Data
51
52- VARIANT, OBJECT, ARRAY for JSON/Avro/Parquet/ORC.
53- Access nested fields: src:customer.name::STRING. Always cast with ::TYPE.
54- VARIANT null vs SQL NULL: JSON null is stored as the string "null". Use STRIP_NULL_VALUE = TRUE on load.
55- Flatten arrays: SELECT f.value:name::STRING FROM my_table, LATERAL FLATTEN(input => src:items) f;
56
57### MERGE for Upserts
58
59```sql
60MERGE INTO target t USING source s ON t.id = s.id
61WHEN MATCHED THEN UPDATE SET t.name = s.name, t.updated_at = CURRENT_TIMESTAMP()
62WHEN NOT MATCHED THEN INSERT (id, name, updated_at) VALUES (s.id, s.name, CURRENT_TIMESTAMP());
63```
64
65> See references/snowflake_sql_and_pipelines.md for deeper SQL patterns and anti-patterns.
66
67---
68
69## Data Pipelines
70
71### Choosing Your Approach
72
73| Approach | When to Use |
74|----------|-------------|
75| Dynamic Tables | Declarative transformations. **Default choice.** Define the query, Snowflake handles refresh. |
76| Streams + Tasks | Imperative CDC. Use for procedural logic, stored procedure calls, complex branching. |
77| Snowpipe | Continuous file loading from cloud storage (S3, GCS, Azure). |
78
79### Dynamic Tables
80
81```sql
82CREATE OR REPLACE DYNAMIC TABLE cleaned_events
83 TARGET_LAG = '5 minutes'
84 WAREHOUSE = transform_wh
85 AS
86 SELECT event_id, event_type, user_id, event_timestamp
87 FROM raw_events
88 WHERE event_type IS NOT NULL;
89```
90
91Key rules:
92- Set TARGET_LAG progressively: tighter at the top of the DAG, looser downstream.
93- Incremental DTs cannot depend on Full-refresh DTs.
94- SELECT * breaks on upstream schema changes -- use explicit column lists.
95- Views cannot sit between two Dynamic Tables in the DAG.
96
97### Streams and Tasks
98
99```sql
100CREATE OR REPLACE STREAM raw_stream ON TABLE raw_events;
101
102CREATE OR REPLACE TASK process_events
103 WAREHOUSE = transform_wh
104 SCHEDULE = 'USING CRON 0 */1 * * * America/Los_Angeles'
105 WHEN SYSTEM$STREAM_HAS_DATA('raw_stream')
106 AS INSERT INTO cleaned_events SELECT ... FROM raw_stream;
107
108-- Tasks start SUSPENDED. You MUST resume them.
109ALTER TASK process_events RESUME;
110```
111
112> See references/snowflake_sql_and_pipelines.md for DT debugging queries and Snowpipe patterns.
113
114---
115
116## Cortex AI
117
118### Function Reference
119
120| Function | Purpose |
121|----------|---------|
122| AI_COMPLETE | LLM completion (text, images, documents) |
123| AI_CLASSIFY | Classify text into categories (up to 500 labels) |
124| AI_FILTER | Boolean filter on text or images |
125| AI_EXTRACT | Structured extraction from text/images/documents |
126| AI_SENTIMENT | Sentiment score (-1 to 1) |
127| AI_PARSE_DOCUMENT | OCR or layout extraction from documents |
128| AI_REDACT | PII removal from text |
129
130**Deprecated names (do NOT use):** COMPLETE, CLASSIFY_TEXT, EXTRACT_ANSWER, PARSE_DOCUMENT, SUMMARIZE, TRANSLATE, SENTIMENT, EMBED_TEXT_768.
131
132### TO_FILE -- Common Pitfall
133
134Stage path and filename are **separate** arguments:
135
136```sql
137-- WRONG: single combined argument
138TO_FILE('@stage/file.pdf')
139
140-- CORRECT: two arguments
141TO_FILE('@db.schema.mystage', 'invoice.pdf')
142```
143
144### Cortex Agents
145
146Agent specs use a JSON structure with top-level keys: models, instructions, tools, tool_resources.
147
148- Use $spec$ delimiter (not $$).
149- models must be an object, not an array.
150- tool_resources is a separate top-level key, not nested inside tools.
151- Tool descriptions are the single biggest factor in agent quality.
152
153> See references/cortex_ai_and_agents.md for full agent spec examples and Cortex Search patterns.
154
155---
156
157## Snowpark Python
158
159```python
160from snowflake.snowpark import Session
161import os
162
163session = Session.builder.configs({
164 "account": os.environ["SNOWFLAKE_ACCOUNT"],
165 "user": os.environ["SNOWFLAKE_USER"],
166 "password": os.environ["SNOWFLAKE_PASSWORD"],
167 "role": "my_role", "warehouse": "my_wh",
168 "database": "my_db", "schema": "my_schema"
169}).create()
170```
171
172- Never hardcode credentials. Use environment variables or key pair auth.
173- DataFrames are lazy -- executed on collect() / show().
174- Do NOT call collect() on large DataFrames. Process server-side with DataFrame operations.
175- Use **vectorized UDFs** (10-100x faster) for batch and ML workloads.
176
177## dbt on Snowflake
178
179```sql
180-- Dynamic table materialization (streaming/near-real-time marts):
181{{ config(materialized='dynamic_table', snowflake_warehouse='transforming', target_lag='1 hour') }}
182
183-- Incremental materialization (large fact tables):
184{{ config(materialized='incremental', unique_key='event_id') }}
185
186-- Snowflake-specific configs (combine with any materialization):
187{{ config(transient=true, copy_grants=true, query_tag='team_daily') }}
188```
189
190- Do NOT use {{ this }} without {% if is_incremental() %} guard.
191- Use dynamic_table materialization for streaming or near-real-time marts.
192
193## Performance
194
195- **Cluster keys**: Only for multi-TB tables. Apply on WHERE / JOIN / GROUP BY columns.
196- **Search Optimization**: ALTER TABLE t ADD SEARCH OPTIMIZATION ON EQUALITY(col);
197- **Warehouse sizing**: Start X-Small, scale up. Set AUTO_SUSPEND = 60, AUTO_RESUME = TRUE.
198- **Separate warehouses** per workload (load, transform, query).
199
200## Security
201
202- Follow least-privilege RBAC. Use database roles for object-level grants.
203- Audit ACCOUNTADMIN regularly: SHOW GRANTS OF ROLE ACCOUNTADMIN;
204- Use network policies for IP allowlisting.
205- Use masking policies for PII columns and row access policies for multi-tenant isolation.
206
207---
208
209## Proactive Triggers
210
211Surface these issues without being asked when you notice them in context:
212
213- **Missing colon prefix** in SQL stored procedures -- flag immediately, this causes "invalid identifier" at runtime.
214- **SELECT * in Dynamic Tables** -- flag as a schema-change time bomb.
215- **Deprecated Cortex function names** (CLASSIFY_TEXT, SUMMARIZE, etc.) -- suggest the current AI_* equivalents.
216- **Task not resumed** after creation -- remind that tasks start SUSPENDED.
217- **Hardcoded credentials** in Snowpark code -- flag as a security risk.
218
219---
220
221## Common Errors
222
223| Error | Cause | Fix |
224|-------|-------|-----|
225| "Object does not exist" | Wrong database/schema context or missing grants | Fully qualify names (db.schema.table), check grants |
226| "Invalid identifier" in procedure | Missing colon prefix on variable | Use :variable_name inside SQL statements |
227| "Numeric value not recognized" | VARIANT field not cast | Cast explicitly: src:field::NUMBER(10,2) |
228| Task not running | Forgot to resume after creation | ALTER TASK task_name RESUME; |
229| DT refresh failing | Schema change upstream or tracking disabled | Use explicit columns, verify change tracking |
230| TO_FILE error | Combined path as single argument | Split into two args: TO_FILE('@stage', 'file.pdf') |
231
232---
233
234## Practical Workflows
235
236### Workflow 1: Build a Reporting Pipeline (30 min)
237
2381. **Stage raw data**: Create external stage pointing to S3/GCS/Azure, set up Snowpipe for auto-ingest
2392. **Clean with Dynamic Table**: Create DT with TARGET_LAG = '5 minutes' that filters nulls, casts types, deduplicates
2403. **Aggregate with downstream DT**: Second DT that joins cleaned data with dimension tables, computes metrics
2414. **Expose via Secure View**: Create SECURE VIEW for the BI tool / API layer
2425. **Grant access**: Use snowflake_query_helper.py grant to generate RBAC statements
243
244### Workflow 2: Add AI Classification to Existing Data
245
2461. **Identify the column**: Find the text column to classify (e.g., support tickets, reviews)
2472. **Test with AI_CLASSIFY**: SELECT AI_CLASSIFY(text_col, ['bug', 'feature', 'question']) FROM table LIMIT 10;
2483. **Create enrichment DT**: Dynamic Table that runs AI_CLASSIFY on new rows automatically
2494. **Monitor costs**: Cortex AI is billed per token — sample before running on full tables
250
251### Workflow 3: Debug a Failing Pipeline
252
2531. **Check task history**: SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY()) WHERE STATE = 'FAILED' ORDER BY SCHEDULED_TIME DESC;
2542. **Check DT refresh**: SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('my_dt')) ORDER BY REFRESH_END_TIME DESC;
2553. **Check stream staleness**: SHOW STREAMS; -- check stale_after column
2564. **Consult troubleshooting reference**: See references/troubleshooting.md for error-specific fixes
257
258---
259
260## Anti-Patterns
261
262| Anti-Pattern | Why It Fails | Better Approach |
263|---|---|---|
264| SELECT * in Dynamic Tables | Schema changes upstream break the DT silently | Use explicit column lists |
265| Missing colon prefix in procedures | "Invalid identifier" runtime error | Always use :variable_name in SQL blocks |
266| Single warehouse for all workloads | Contention between load, transform, and query | Separate warehouses per workload type |
267| Hardcoded credentials in Snowpark | Security risk, breaks in CI/CD | Use os.environ[] or key pair auth |
268| collect() on large DataFrames | Pulls entire result set to client memory | Process server-side with DataFrame operations |
269| Nested subqueries instead of CTEs | Unreadable, hard to debug, Snowflake optimizes CTEs better | Use WITH clauses |
270| Using deprecated Cortex functions | CLASSIFY_TEXT, SUMMARIZE etc. will be removed | Use AI_CLASSIFY, AI_COMPLETE etc. |
271| Tasks without WHEN SYSTEM$STREAM_HAS_DATA | Task runs on schedule even with no new data, wasting credits | Add the WHEN clause for stream-driven tasks |
272| Double-quoted identifiers | Forces case-sensitive names across all queries | Use snake_case unquoted identifiers |
273
274---
275
276## Cross-References
277
278| Skill | Relationship |
279|-------|-------------|
280| engineering/sql-database-assistant | General SQL patterns — use for non-Snowflake databases |
281| engineering/database-designer | Schema design — use for data modeling before Snowflake implementation |
282| engineering-team/senior-data-engineer | Broader data engineering — pipelines, Spark, Airflow, data quality |
283| engineering-team/senior-data-scientist | Analytics and ML — use alongside Snowpark for feature engineering |
284| engineering-team/senior-devops | CI/CD for Snowflake deployments (Terraform, GitHub Actions) |
285
286---
287
288## Reference Documentation
289
290| Document | Contents |
291|----------|----------|
292| references/snowflake_sql_and_pipelines.md | SQL patterns, MERGE templates, Dynamic Table debugging, Snowpipe, anti-patterns |
293| references/cortex_ai_and_agents.md | Cortex AI functions, agent spec structure, Cortex Search, Snowpark |
294| references/troubleshooting.md | Error reference, debugging queries, common fixes |
295