snowflake-development
Use when writing Snowflake SQL, building data pipelines with Dynamic Tables or Streams/Tasks, using Cortex AI functions, creating Cortex Agents, writing Snowpark Python, configuring dbt for Snowflake, or troubleshooting Snowflake errors.
- License 许可
- MIT
- 最近更新
- 2026-08-30
- Stars
- ★ 1
- Forks
- 0
01 前言 Front Matter
- name
- snowflake-development
- description
- Use when writing Snowflake SQL, building data pipelines with Dynamic Tables or Streams/Tasks, using Cortex AI functions, creating Cortex Agents, writing Snowpark Python, configuring dbt for Snowflake, or troubleshooting Snowflake errors.
02 资源文件 4
- 参考文档references/cortex_ai_and_agents.md7.5 KB
- 参考文档references/snowflake_sql_and_pipelines.md7.4 KB
- 参考文档references/troubleshooting.md5.2 KB
- 脚本scripts/snowflake_query_helper.py8.2 KB
03 SKILL.md 正文
Snowflake Development
Snowflake 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.
Originally contributed by James Cha-Earley — enhanced and integrated by the claude-skills team.
Quick Start
# Generate a MERGE upsert template
python scripts/snowflake_query_helper.py merge --target customers --source staging_customers --key customer_id --columns name,email,updated_at
# Generate a Dynamic Table template
python scripts/snowflake_query_helper.py dynamic-table --name cleaned_events --warehouse transform_wh --lag "5 minutes"
# Generate RBAC grant statements
python scripts/snowflake_query_helper.py grant --role analyst_role --database analytics --schemas public,staging --privileges SELECT,USAGE
SQL Best Practices
Naming and Style
- Use
snake_casefor all identifiers. Avoid double-quoted identifiers -- they force case-sensitive names that require constant quoting. - Use CTEs (
WITHclauses) over nested subqueries. - Use
CREATE OR REPLACEfor idempotent DDL. - Use explicit column lists -- never
SELECT *in production. Snowflake's columnar storage scans only referenced columns, so explicit lists reduce I/O.
Stored Procedures -- Colon Prefix Rule
In 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.
-- WRONG: missing colon prefix
SELECT name INTO result FROM users WHERE id = p_id;
-- CORRECT: colon prefix on both variable and parameter
SELECT name INTO :result FROM users WHERE id = :p_id;
This applies to DECLARE variables, LET variables, and procedure parameters when used inside SELECT, INSERT, UPDATE, DELETE, or MERGE.
Semi-Structured Data
- VARIANT, OBJECT, ARRAY for JSON/Avro/Parquet/ORC.
- Access nested fields:
src:customer.name::STRING. Always cast with::TYPE. - VARIANT null vs SQL NULL: JSON
nullis stored as the string"null". UseSTRIP_NULL_VALUE = TRUEon load. - Flatten arrays:
SELECT f.value:name::STRING FROM my_table, LATERAL FLATTEN(input => src:items) f;
MERGE for Upserts
MERGE INTO target t USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (id, name, updated_at) VALUES (s.id, s.name, CURRENT_TIMESTAMP());
See
references/snowflake_sql_and_pipelines.mdfor deeper SQL patterns and anti-patterns.
Data Pipelines
Choosing Your Approach
| Approach | When to Use |
|---|---|
| Dynamic Tables | Declarative transformations. Default choice. Define the query, Snowflake handles refresh. |
| Streams + Tasks | Imperative CDC. Use for procedural logic, stored procedure calls, complex branching. |
| Snowpipe | Continuous file loading from cloud storage (S3, GCS, Azure). |
Dynamic Tables
CREATE OR REPLACE DYNAMIC TABLE cleaned_events
TARGET_LAG = '5 minutes'
WAREHOUSE = transform_wh
AS
SELECT event_id, event_type, user_id, event_timestamp
FROM raw_events
WHERE event_type IS NOT NULL;
Key rules:
- Set
TARGET_LAGprogressively: tighter at the top of the DAG, looser downstream. - Incremental DTs cannot depend on Full-refresh DTs.
SELECT *breaks on upstream schema changes -- use explicit column lists.- Views cannot sit between two Dynamic Tables in the DAG.
Streams and Tasks
CREATE OR REPLACE STREAM raw_stream ON TABLE raw_events;
CREATE OR REPLACE TASK process_events
WAREHOUSE = transform_wh
SCHEDULE = 'USING CRON 0 */1 * * * America/Los_Angeles'
WHEN SYSTEM$STREAM_HAS_DATA('raw_stream')
AS INSERT INTO cleaned_events SELECT ... FROM raw_stream;
-- Tasks start SUSPENDED. You MUST resume them.
ALTER TASK process_events RESUME;
See
references/snowflake_sql_and_pipelines.mdfor DT debugging queries and Snowpipe patterns.
Cortex AI
Function Reference
| Function | Purpose |
|---|---|
AI_COMPLETE |
LLM completion (text, images, documents) |
AI_CLASSIFY |
Classify text into categories (up to 500 labels) |
AI_FILTER |
Boolean filter on text or images |
AI_EXTRACT |
Structured extraction from text/images/documents |
AI_SENTIMENT |
Sentiment score (-1 to 1) |
AI_PARSE_DOCUMENT |
OCR or layout extraction from documents |
AI_REDACT |
PII removal from text |
Deprecated names (do NOT use): COMPLETE, CLASSIFY_TEXT, EXTRACT_ANSWER, PARSE_DOCUMENT, SUMMARIZE, TRANSLATE, SENTIMENT, EMBED_TEXT_768.
TO_FILE -- Common Pitfall
Stage path and filename are separate arguments:
-- WRONG: single combined argument
TO_FILE('@stage/file.pdf')
-- CORRECT: two arguments
TO_FILE('@db.schema.mystage', 'invoice.pdf')
Cortex Agents
Agent specs use a JSON structure with top-level keys: models, instructions, tools, tool_resources.
- Use
$spec$delimiter (not$$). modelsmust be an object, not an array.tool_resourcesis a separate top-level key, not nested insidetools.- Tool descriptions are the single biggest factor in agent quality.
See
references/cortex_ai_and_agents.mdfor full agent spec examples and Cortex Search patterns.
Snowpark Python
from snowflake.snowpark import Session
import os
session = Session.builder.configs({
"account": os.environ["SNOWFLAKE_ACCOUNT"],
"user": os.environ["SNOWFLAKE_USER"],
"password": os.environ["SNOWFLAKE_PASSWORD"],
"role": "my_role", "warehouse": "my_wh",
"database": "my_db", "schema": "my_schema"
}).create()
- Never hardcode credentials. Use environment variables or key pair auth.
- DataFrames are lazy -- executed on
collect()/show(). - Do NOT call
collect()on large DataFrames. Process server-side with DataFrame operations. - Use vectorized UDFs (10-100x faster) for batch and ML workloads.
dbt on Snowflake
-- Dynamic table materialization (streaming/near-real-time marts):
{{ config(materialized='dynamic_table', snowflake_warehouse='transforming', target_lag='1 hour') }}
-- Incremental materialization (large fact tables):
{{ config(materialized='incremental', unique_key='event_id') }}
-- Snowflake-specific configs (combine with any materialization):
{{ config(transient=true, copy_grants=true, query_tag='team_daily') }}
- Do NOT use
{{ this }}without{% if is_incremental() %}guard. - Use
dynamic_tablematerialization for streaming or near-real-time marts.
Performance
- Cluster keys: Only for multi-TB tables. Apply on WHERE / JOIN / GROUP BY columns.
- Search Optimization:
ALTER TABLE t ADD SEARCH OPTIMIZATION ON EQUALITY(col); - Warehouse sizing: Start X-Small, scale up. Set
AUTO_SUSPEND = 60,AUTO_RESUME = TRUE. - Separate warehouses per workload (load, transform, query).
Security
- Follow least-privilege RBAC. Use database roles for object-level grants.
- Audit ACCOUNTADMIN regularly:
SHOW GRANTS OF ROLE ACCOUNTADMIN; - Use network policies for IP allowlisting.
- Use masking policies for PII columns and row access policies for multi-tenant isolation.
Proactive Triggers
Surface these issues without being asked when you notice them in context:
- Missing colon prefix in SQL stored procedures -- flag immediately, this causes "invalid identifier" at runtime.
SELECT *in Dynamic Tables -- flag as a schema-change time bomb.- Deprecated Cortex function names (
CLASSIFY_TEXT,SUMMARIZE, etc.) -- suggest the currentAI_*equivalents. - Task not resumed after creation -- remind that tasks start SUSPENDED.
- Hardcoded credentials in Snowpark code -- flag as a security risk.
Common Errors
| Error | Cause | Fix |
|---|---|---|
| "Object does not exist" | Wrong database/schema context or missing grants | Fully qualify names (db.schema.table), check grants |
| "Invalid identifier" in procedure | Missing colon prefix on variable | Use :variable_name inside SQL statements |
| "Numeric value not recognized" | VARIANT field not cast | Cast explicitly: src:field::NUMBER(10,2) |
| Task not running | Forgot to resume after creation | ALTER TASK task_name RESUME; |
| DT refresh failing | Schema change upstream or tracking disabled | Use explicit columns, verify change tracking |
| TO_FILE error | Combined path as single argument | Split into two args: TO_FILE('@stage', 'file.pdf') |
Practical Workflows
Workflow 1: Build a Reporting Pipeline (30 min)
- Stage raw data: Create external stage pointing to S3/GCS/Azure, set up Snowpipe for auto-ingest
- Clean with Dynamic Table: Create DT with
TARGET_LAG = '5 minutes'that filters nulls, casts types, deduplicates - Aggregate with downstream DT: Second DT that joins cleaned data with dimension tables, computes metrics
- Expose via Secure View: Create
SECURE VIEWfor the BI tool / API layer - Grant access: Use
snowflake_query_helper.py grantto generate RBAC statements
Workflow 2: Add AI Classification to Existing Data
- Identify the column: Find the text column to classify (e.g., support tickets, reviews)
- Test with AI_CLASSIFY:
SELECT AI_CLASSIFY(text_col, ['bug', 'feature', 'question']) FROM table LIMIT 10; - Create enrichment DT: Dynamic Table that runs
AI_CLASSIFYon new rows automatically - Monitor costs: Cortex AI is billed per token — sample before running on full tables
Workflow 3: Debug a Failing Pipeline
- Check task history:
SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY()) WHERE STATE = 'FAILED' ORDER BY SCHEDULED_TIME DESC; - Check DT refresh:
SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('my_dt')) ORDER BY REFRESH_END_TIME DESC; - Check stream staleness:
SHOW STREAMS; -- check stale_after column - Consult troubleshooting reference: See
references/troubleshooting.mdfor error-specific fixes
Anti-Patterns
| Anti-Pattern | Why It Fails | Better Approach |
|---|---|---|
SELECT * in Dynamic Tables |
Schema changes upstream break the DT silently | Use explicit column lists |
| Missing colon prefix in procedures | "Invalid identifier" runtime error | Always use :variable_name in SQL blocks |
| Single warehouse for all workloads | Contention between load, transform, and query | Separate warehouses per workload type |
| Hardcoded credentials in Snowpark | Security risk, breaks in CI/CD | Use os.environ[] or key pair auth |
collect() on large DataFrames |
Pulls entire result set to client memory | Process server-side with DataFrame operations |
| Nested subqueries instead of CTEs | Unreadable, hard to debug, Snowflake optimizes CTEs better | Use WITH clauses |
| Using deprecated Cortex functions | CLASSIFY_TEXT, SUMMARIZE etc. will be removed |
Use AI_CLASSIFY, AI_COMPLETE etc. |
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 |
| Double-quoted identifiers | Forces case-sensitive names across all queries | Use snake_case unquoted identifiers |
Cross-References
| Skill | Relationship |
|---|---|
engineering/sql-database-assistant |
General SQL patterns — use for non-Snowflake databases |
engineering/database-designer |
Schema design — use for data modeling before Snowflake implementation |
engineering-team/senior-data-engineer |
Broader data engineering — pipelines, Spark, Airflow, data quality |
engineering-team/senior-data-scientist |
Analytics and ML — use alongside Snowpark for feature engineering |
engineering-team/senior-devops |
CI/CD for Snowflake deployments (Terraform, GitHub Actions) |
Reference Documentation
| Document | Contents |
|---|---|
references/snowflake_sql_and_pipelines.md |
SQL patterns, MERGE templates, Dynamic Table debugging, Snowpipe, anti-patterns |
references/cortex_ai_and_agents.md |
Cortex AI functions, agent spec structure, Cortex Search, Snowpark |
references/troubleshooting.md |
Error reference, debugging queries, common fixes |
04 同仓库更多 Skills
syllabus
Generates a curated supplementary reading list from any course syllabus using Consensus academic search. Grill-me intake (syllabus input format + course audience + year range) plus a grouping forcing-options checkpoint before any search runs — so the reading list matches the course's level and recency need. Parses the syllabus to extract topics and learning outcomes, searches Consensus for recent peer-reviewed papers per topic, and produces a professionally formatted .docx with clickable Consensus links, plain-language summaries calibrated to audience level, and Bloom-higher-order discussion questions tied to course learning goals. Use when the user uploads a syllabus, course outline, or curriculum document and wants supplementary readings (e.g., 'create a reading list from this syllabus', 'find recent papers for my course') — even casual mentions with a syllabus attached should trigger this skill.
research
Default entry point for any research request — a hybrid router that classifies the question deterministically and either delegates to a specialist research skill (pulse for trends/sentiment, grants for NIH funding, litreview for academic literature, syllabus for course reading, patent for prior-art + IP landscape, dossier for entity research, deepread for evidence-first reading of supplied documents) or runs its own plan-decompose-multi-source-search-synthesize-cite fallback workflow when no specialist matches. Always surfaces the routing decision so users can override. Use when the user makes any research request that doesn't obviously match a more-specific specialist skill (e.g., "research [topic]", "look into [topic]", "what do we know about [topic]", "investigate [topic]", "find me information on [topic]", "do some research on [topic]", "I need to understand [topic]"). Output is a markdown briefing (default) or .docx document (on request) with full citations and an audit log.
pulse
Multi-source recency research skill that takes the pulse of any topic across Reddit, Hacker News, the open web, and optionally X/Twitter within a configurable recent window (default 30 days). Forcing intake clarifies topic specificity, angle (trend/sentiment/problems/opportunities/comparison), time window, and platform scope before searching. Returns a synthesized briefing with citations, engagement metrics, and cross-platform pattern analysis. Use when the user requests multi-source recency intelligence on a topic (e.g., 'pulse on [topic]', 'what's happening with [topic]', 'what are people saying about [topic]', 'current conversation about [topic]', 'take the pulse of [topic]', 'trending: [topic]', 'find me info on [topic]'), and for competitor research, trend discovery, tool comparisons, and audience sentiment analysis.
patent
Patent prior-art and landscape intelligence skill — not generic patent help. Commits to one of five sub-use-cases via forcing intake (novelty search / freedom-to-operate / competitive landscape / acquisition diligence / litigation prior-art) before any search runs. Searches Google Patents, Espacenet, USPTO, and optionally Lens.org for citation-graph signals. Output is an editable Word document (.docx) with verdict, ranked closest art (claim-text extracted), CPC-class-aware landscape, family-resolved hits, geographic coverage, FTO flags where applicable, strategy recommendations, and full audit log. Use when the user asks for patent searching or analysis (e.g., 'prior art search for [invention]', 'freedom to operate analysis for [product]'). Produces search signal, not legal advice — always recommends consulting a patent attorney before filing or licensing decisions. Trademark, copyright, and trade-secret questions are out of scope.
notebooklm
Browser automation skill for controlling Google's NotebookLM. Use when the user wants anything done in NotebookLM (e.g., 'open NotebookLM', 'check my [name] notebook', 'ask my notebook about X', 'add [source] to NotebookLM', 'generate a Video Overview from my notebook', 'use NotebookLM Studio'). Handles reading and querying notebooks, adding sources (URLs, text, files, YouTube links, synthesized content), generating Studio outputs (Audio/Video Overviews, Mind Maps, Reports incl. Briefing Doc/Study Guide/FAQ, Flashcards, Quiz, slide decks, infographics — discover the exact set from the live Studio panel; the UI evolves fast), and creating new notebooks. Requires browser automation environment — fails gracefully when unavailable.
litreview
Academic literature orientation skill that searches papers via free keyless APIs (PubMed E-utilities + OpenAlex) by default — with the Consensus MCP as an optional enhancement lane when connected — builds a strategic search plan using PICO (default) or SPIDER / Decomposition / hybrid as fallbacks, and synthesizes findings into a formatted Word (.docx) research guide. Grill-me intake (research question specificity + framework hint + tentative depth) before the recon search; a second forcing checkpoint after Phase 2 confirms framework + sub-areas + depth before searches consume budget. Configurable depth (5/10/20 queries) controls coverage vs. speed. Output is a 'launching pad' — an orientation guide that lets a researcher dive in confidently, not a finished review. Use when the user starts literature-oriented research (e.g., 'litreview on [topic]', 'literature review on [topic]', 'I'm starting a literature review on X', 'I'm writing a paper on X', 'help me research X', 'I'm doing research on X', 'can you help me research X'). Do NOT use for single one-off paper searches wanting a quick list — that's a plain PubMed/OpenAlex (or Consensus) query.