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_case for all identifiers. Avoid double-quoted identifiers -- they force case-sensitive names that require constant quoting.
- Use CTEs (
WITH clauses) over nested subqueries.
- Use
CREATE OR REPLACE for 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
null is stored as the string "null". Use STRIP_NULL_VALUE = TRUE on 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.md for 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_LAG progressively: 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.md for 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 $$).
models must be an object, not an array.
tool_resources is a separate top-level key, not nested inside tools.
- Tool descriptions are the single biggest factor in agent quality.
See references/cortex_ai_and_agents.md for 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_table materialization 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 current AI_* 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 VIEW for the BI tool / API layer
- Grant access: Use
snowflake_query_helper.py grant to 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_CLASSIFY on 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.md for 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 |
1---2name: snowflake-development-23description: "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."4disable-model-invocation: true # skill-optimizer5metadata:6 source: https://skillmd.com/skills/slipcast-dev/snowflake-development-27---89# Snowflake Development1011Snowflake 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.1213> Originally contributed by [James Cha-Earley](https://github.com/jamescha-earley) — enhanced and integrated by the claude-skills team.1415## Quick Start1617```bash18# Generate a MERGE upsert template19python scripts/snowflake_query_helper.py merge --target customers --source staging_customers --key customer_id --columns name,email,updated_at2021# Generate a Dynamic Table template22python scripts/snowflake_query_helper.py dynamic-table --name cleaned_events --warehouse transform_wh --lag "5 minutes"2324# Generate RBAC grant statements25python scripts/snowflake_query_helper.py grant --role analyst_role --database analytics --schemas public,staging --privileges SELECT,USAGE26```2728---2930## SQL Best Practices3132### Naming and Style3334- Use `snake_case` for all identifiers. Avoid double-quoted identifiers -- they force case-sensitive names that require constant quoting.35- Use CTEs (`WITH` clauses) over nested subqueries.36- Use `CREATE OR REPLACE` for idempotent DDL.37- Use explicit column lists -- never `SELECT *` in production. Snowflake's columnar storage scans only referenced columns, so explicit lists reduce I/O.3839### Stored Procedures -- Colon Prefix Rule4041In 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.4243```sql44-- WRONG: missing colon prefix45SELECT name INTO result FROM users WHERE id = p_id;4647-- CORRECT: colon prefix on both variable and parameter48SELECT name INTO :result FROM users WHERE id = :p_id;49```5051This applies to DECLARE variables, LET variables, and procedure parameters when used inside SELECT, INSERT, UPDATE, DELETE, or MERGE.5253### Semi-Structured Data5455- VARIANT, OBJECT, ARRAY for JSON/Avro/Parquet/ORC.56- Access nested fields: `src:customer.name::STRING`. Always cast with `::TYPE`.57- VARIANT null vs SQL NULL: JSON `null` is stored as the string `"null"`. Use `STRIP_NULL_VALUE = TRUE` on load.58- Flatten arrays: `SELECT f.value:name::STRING FROM my_table, LATERAL FLATTEN(input => src:items) f;`5960### MERGE for Upserts6162```sql63MERGE INTO target t USING source s ON t.id = s.id64WHEN MATCHED THEN UPDATE SET t.name = s.name, t.updated_at = CURRENT_TIMESTAMP()65WHEN NOT MATCHED THEN INSERT (id, name, updated_at) VALUES (s.id, s.name, CURRENT_TIMESTAMP());66```6768> See `references/snowflake_sql_and_pipelines.md` for deeper SQL patterns and anti-patterns.6970---7172## Data Pipelines7374### Choosing Your Approach7576| Approach | When to Use |77|----------|-------------|78| Dynamic Tables | Declarative transformations. **Default choice.** Define the query, Snowflake handles refresh. |79| Streams + Tasks | Imperative CDC. Use for procedural logic, stored procedure calls, complex branching. |80| Snowpipe | Continuous file loading from cloud storage (S3, GCS, Azure). |8182### Dynamic Tables8384```sql85CREATE OR REPLACE DYNAMIC TABLE cleaned_events86 TARGET_LAG = '5 minutes'87 WAREHOUSE = transform_wh88 AS89 SELECT event_id, event_type, user_id, event_timestamp90 FROM raw_events91 WHERE event_type IS NOT NULL;92```9394Key rules:95- Set `TARGET_LAG` progressively: tighter at the top of the DAG, looser downstream.96- Incremental DTs cannot depend on Full-refresh DTs.97- `SELECT *` breaks on upstream schema changes -- use explicit column lists.98- Views cannot sit between two Dynamic Tables in the DAG.99100### Streams and Tasks101102```sql103CREATE OR REPLACE STREAM raw_stream ON TABLE raw_events;104105CREATE OR REPLACE TASK process_events106 WAREHOUSE = transform_wh107 SCHEDULE = 'USING CRON 0 */1 * * * America/Los_Angeles'108 WHEN SYSTEM$STREAM_HAS_DATA('raw_stream')109 AS INSERT INTO cleaned_events SELECT ... FROM raw_stream;110111-- Tasks start SUSPENDED. You MUST resume them.112ALTER TASK process_events RESUME;113```114115> See `references/snowflake_sql_and_pipelines.md` for DT debugging queries and Snowpipe patterns.116117---118119## Cortex AI120121### Function Reference122123| Function | Purpose |124|----------|---------|125| `AI_COMPLETE` | LLM completion (text, images, documents) |126| `AI_CLASSIFY` | Classify text into categories (up to 500 labels) |127| `AI_FILTER` | Boolean filter on text or images |128| `AI_EXTRACT` | Structured extraction from text/images/documents |129| `AI_SENTIMENT` | Sentiment score (-1 to 1) |130| `AI_PARSE_DOCUMENT` | OCR or layout extraction from documents |131| `AI_REDACT` | PII removal from text |132133**Deprecated names (do NOT use):** `COMPLETE`, `CLASSIFY_TEXT`, `EXTRACT_ANSWER`, `PARSE_DOCUMENT`, `SUMMARIZE`, `TRANSLATE`, `SENTIMENT`, `EMBED_TEXT_768`.134135### TO_FILE -- Common Pitfall136137Stage path and filename are **separate** arguments:138139```sql140-- WRONG: single combined argument141TO_FILE('@stage/file.pdf')142143-- CORRECT: two arguments144TO_FILE('@db.schema.mystage', 'invoice.pdf')145```146147### Cortex Agents148149Agent specs use a JSON structure with top-level keys: `models`, `instructions`, `tools`, `tool_resources`.150151- Use `$spec$` delimiter (not `$$`).152- `models` must be an object, not an array.153- `tool_resources` is a separate top-level key, not nested inside `tools`.154- Tool descriptions are the single biggest factor in agent quality.155156> See `references/cortex_ai_and_agents.md` for full agent spec examples and Cortex Search patterns.157158---159160## Snowpark Python161162```python163from snowflake.snowpark import Session164import os165166session = Session.builder.configs({167 "account": os.environ["SNOWFLAKE_ACCOUNT"],168 "user": os.environ["SNOWFLAKE_USER"],169 "password": os.environ["SNOWFLAKE_PASSWORD"],170 "role": "my_role", "warehouse": "my_wh",171 "database": "my_db", "schema": "my_schema"172}).create()173```174175- Never hardcode credentials. Use environment variables or key pair auth.176- DataFrames are lazy -- executed on `collect()` / `show()`.177- Do NOT call `collect()` on large DataFrames. Process server-side with DataFrame operations.178- Use **vectorized UDFs** (10-100x faster) for batch and ML workloads.179180## dbt on Snowflake181182```sql183-- Dynamic table materialization (streaming/near-real-time marts):184{{ config(materialized='dynamic_table', snowflake_warehouse='transforming', target_lag='1 hour') }}185186-- Incremental materialization (large fact tables):187{{ config(materialized='incremental', unique_key='event_id') }}188189-- Snowflake-specific configs (combine with any materialization):190{{ config(transient=true, copy_grants=true, query_tag='team_daily') }}191```192193- Do NOT use `{{ this }}` without `{% if is_incremental() %}` guard.194- Use `dynamic_table` materialization for streaming or near-real-time marts.195196## Performance197198- **Cluster keys**: Only for multi-TB tables. Apply on WHERE / JOIN / GROUP BY columns.199- **Search Optimization**: `ALTER TABLE t ADD SEARCH OPTIMIZATION ON EQUALITY(col);`200- **Warehouse sizing**: Start X-Small, scale up. Set `AUTO_SUSPEND = 60`, `AUTO_RESUME = TRUE`.201- **Separate warehouses** per workload (load, transform, query).202203## Security204205- Follow least-privilege RBAC. Use database roles for object-level grants.206- Audit ACCOUNTADMIN regularly: `SHOW GRANTS OF ROLE ACCOUNTADMIN;`207- Use network policies for IP allowlisting.208- Use masking policies for PII columns and row access policies for multi-tenant isolation.209210---211212## Proactive Triggers213214Surface these issues without being asked when you notice them in context:215216- **Missing colon prefix** in SQL stored procedures -- flag immediately, this causes "invalid identifier" at runtime.217- **`SELECT *` in Dynamic Tables** -- flag as a schema-change time bomb.218- **Deprecated Cortex function names** (`CLASSIFY_TEXT`, `SUMMARIZE`, etc.) -- suggest the current `AI_*` equivalents.219- **Task not resumed** after creation -- remind that tasks start SUSPENDED.220- **Hardcoded credentials** in Snowpark code -- flag as a security risk.221222---223224## Common Errors225226| Error | Cause | Fix |227|-------|-------|-----|228| "Object does not exist" | Wrong database/schema context or missing grants | Fully qualify names (`db.schema.table`), check grants |229| "Invalid identifier" in procedure | Missing colon prefix on variable | Use `:variable_name` inside SQL statements |230| "Numeric value not recognized" | VARIANT field not cast | Cast explicitly: `src:field::NUMBER(10,2)` |231| Task not running | Forgot to resume after creation | `ALTER TASK task_name RESUME;` |232| DT refresh failing | Schema change upstream or tracking disabled | Use explicit columns, verify change tracking |233| TO_FILE error | Combined path as single argument | Split into two args: `TO_FILE('@stage', 'file.pdf')` |234235---236237## Practical Workflows238239### Workflow 1: Build a Reporting Pipeline (30 min)2402411. **Stage raw data**: Create external stage pointing to S3/GCS/Azure, set up Snowpipe for auto-ingest2422. **Clean with Dynamic Table**: Create DT with `TARGET_LAG = '5 minutes'` that filters nulls, casts types, deduplicates2433. **Aggregate with downstream DT**: Second DT that joins cleaned data with dimension tables, computes metrics2444. **Expose via Secure View**: Create `SECURE VIEW` for the BI tool / API layer2455. **Grant access**: Use `snowflake_query_helper.py grant` to generate RBAC statements246247### Workflow 2: Add AI Classification to Existing Data2482491. **Identify the column**: Find the text column to classify (e.g., support tickets, reviews)2502. **Test with AI_CLASSIFY**: `SELECT AI_CLASSIFY(text_col, ['bug', 'feature', 'question']) FROM table LIMIT 10;`2513. **Create enrichment DT**: Dynamic Table that runs `AI_CLASSIFY` on new rows automatically2524. **Monitor costs**: Cortex AI is billed per token — sample before running on full tables253254### Workflow 3: Debug a Failing Pipeline2552561. **Check task history**: `SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY()) WHERE STATE = 'FAILED' ORDER BY SCHEDULED_TIME DESC;`2572. **Check DT refresh**: `SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('my_dt')) ORDER BY REFRESH_END_TIME DESC;`2583. **Check stream staleness**: `SHOW STREAMS; -- check stale_after column`2594. **Consult troubleshooting reference**: See `references/troubleshooting.md` for error-specific fixes260261---262263## Anti-Patterns264265| Anti-Pattern | Why It Fails | Better Approach |266|---|---|---|267| `SELECT *` in Dynamic Tables | Schema changes upstream break the DT silently | Use explicit column lists |268| Missing colon prefix in procedures | "Invalid identifier" runtime error | Always use `:variable_name` in SQL blocks |269| Single warehouse for all workloads | Contention between load, transform, and query | Separate warehouses per workload type |270| Hardcoded credentials in Snowpark | Security risk, breaks in CI/CD | Use `os.environ[]` or key pair auth |271| `collect()` on large DataFrames | Pulls entire result set to client memory | Process server-side with DataFrame operations |272| Nested subqueries instead of CTEs | Unreadable, hard to debug, Snowflake optimizes CTEs better | Use `WITH` clauses |273| Using deprecated Cortex functions | `CLASSIFY_TEXT`, `SUMMARIZE` etc. will be removed | Use `AI_CLASSIFY`, `AI_COMPLETE` etc. |274| 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 |275| Double-quoted identifiers | Forces case-sensitive names across all queries | Use `snake_case` unquoted identifiers |276277---278279## Cross-References280281| Skill | Relationship |282|-------|-------------|283| `engineering/sql-database-assistant` | General SQL patterns — use for non-Snowflake databases |284| `engineering/database-designer` | Schema design — use for data modeling before Snowflake implementation |285| `engineering-team/senior-data-engineer` | Broader data engineering — pipelines, Spark, Airflow, data quality |286| `engineering-team/senior-data-scientist` | Analytics and ML — use alongside Snowpark for feature engineering |287| `engineering-team/senior-devops` | CI/CD for Snowflake deployments (Terraform, GitHub Actions) |288289---290291## Reference Documentation292293| Document | Contents |294|----------|----------|295| `references/snowflake_sql_and_pipelines.md` | SQL patterns, MERGE templates, Dynamic Table debugging, Snowpipe, anti-patterns |296| `references/cortex_ai_and_agents.md` | Cortex AI functions, agent spec structure, Cortex Search, Snowpark |297| `references/troubleshooting.md` | Error reference, debugging queries, common fixes |