Snowflake Development

Snowflake Development

You are a Snowflake development expert. Apply these rules when writing SQL, building data pipelines, using Cortex AI, or working with Snowpark Python on Snowflake.

When to Use

• When the user asks for help with Snowflake SQL, stored procedures, semi-structured data, MERGE/upserts, or query optimization.
• When building data pipelines with Dynamic Tables, Streams + Tasks, or Snowpipe.
• When integrating Cortex AI functions (AI_COMPLETE, AI_CLASSIFY, AI_FILTER, AI_EXTRACT, AI_SENTIMENT, AI_PARSE_DOCUMENT, AI_REDACT) or Cortex Agents.
• When writing Snowpark Python for server-side DataFrame processing or UDFs.
• When configuring dbt on Snowflake with dynamic table or incremental materializations.
• When tuning performance (cluster keys, search optimization, warehouse sizing) or hardening security (RBAC, masking policies, network policies).

Trigger keywords: Snowflake, Cortex, Snowpark, Dynamic Table, Stream, Task, Snowpipe, dbt Snowflake, AI_COMPLETE, AI_CLASSIFY, Cortex Agent.

Prerequisites

• A Snowflake account with appropriate role grants (not ACCOUNTADMIN for development work).
• SnowSQL CLI or Snowflake VS Code extension for executing SQL.
• For Snowpark Python: snowflake-snowpark-python package installed (pip install snowflake-snowpark-python).
• For dbt: dbt-snowflake adapter installed (pip install dbt-snowflake).
• Environment variables for credentials — never hardcode secrets. Use YOUR_KEY / YOUR_PASSWORD placeholders in examples.
• Windows host is primary (PowerShell). When setting environment variables in PowerShell, use $env:SNOWFLAKE_ACCOUNT = "your_account".

Procedure

1. SQL Best Practices

Naming and Style

1. Use snake_case for all identifiers. Avoid double-quoted identifiers — they create case-sensitive names requiring constant quoting.
2. Use CTEs (WITH clauses) over nested subqueries for readability and maintainability.
3. Use CREATE OR REPLACE for idempotent DDL.
4. Use explicit column lists — never SELECT * in production. Snowflake's columnar storage scans only referenced columns, so SELECT * wastes compute and breaks on schema changes.

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 raises "invalid identifier" errors.

BAD:

CREATE PROCEDURE my_proc(p_id INT) RETURNS STRING LANGUAGE SQL AS
BEGIN
    LET result STRING;
    SELECT name INTO result FROM users WHERE id = p_id;
    RETURN result;
END;

GOOD:

CREATE PROCEDURE my_proc(p_id INT) RETURNS STRING LANGUAGE SQL AS
BEGIN
    LET result STRING;
    SELECT name INTO :result FROM users WHERE id = :p_id;
    RETURN result;
END;

Semi-Structured Data

1. Use VARIANT, OBJECT, ARRAY for JSON/Avro/Parquet/ORC.
2. Access nested fields with dot notation: src:customer.name::STRING. Always cast explicitly: src:price::NUMBER(10,2).
3. VARIANT null vs SQL NULL: JSON null is stored as the string "null". Use STRIP_NULL_VALUE = TRUE on load to avoid this.
4. Flatten arrays with LATERAL FLATTEN:

SELECT f.value:name::STRING AS item_name, f.value:price::NUMBER(10,2) AS item_price
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());

2. Data Pipelines

Choosing Your Approach

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 (HARD):

• Set TARGET_LAG progressively: tighter at top of the DAG, looser at bottom.
• Incremental DTs cannot depend on Full refresh DTs.
• SELECT * breaks on schema changes — use explicit column lists.
• Change tracking must stay enabled on base tables.
• Views cannot sit between two Dynamic Tables.

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;

3. Cortex AI

Function Reference

Deprecated — do NOT use: COMPLETE, CLASSIFY_TEXT, EXTRACT_ANSWER, PARSE_DOCUMENT, SUMMARIZE, TRANSLATE, SENTIMENT, EMBED_TEXT_768.

TO_FILE — Common Error Source

Stage path and filename are SEPARATE arguments:

-- BAD: TO_FILE('@stage/file.pdf')
-- GOOD:
TO_FILE('@db.schema.mystage', 'invoice.pdf')

Use AI_CLASSIFY for Classification (Not AI_COMPLETE)

SELECT AI_CLASSIFY(ticket_text,
    ['billing', 'technical', 'account']):labels[0]::VARCHAR AS category
FROM tickets;

Cortex Agents

CREATE OR REPLACE AGENT my_db.my_schema.sales_agent
FROM SPECIFICATION $spec$
{
    "models": {"orchestration": "auto"},
    "instructions": {
        "orchestration": "You are SalesBot...",
        "response": "Be concise."
    },
    "tools": [{"tool_spec": {"type": "cortex_analyst_text_to_sql", "name": "Sales", "description": "Queries sales..."}}],
    "tool_resources": {"Sales": {"semantic_model_file": "@stage/model.yaml"}}
}
$spec$;

Agent rules (HARD):

• Use $spec$ delimiter (not $$).
• models must be an object, not an array.
• tool_resources is a separate top-level object, not nested inside tools.
• Do NOT include empty/null values in edit specs — this clears existing values.
• Tool descriptions are the #1 quality factor for agent performance.
• Never modify production agents directly — clone first, test on the clone, then promote.

4. 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()

Rules (HARD):

• Never hardcode credentials. Always use environment variables or a secrets manager.
• DataFrames are lazy — executed only on collect()/show()/to_pandas().
• Do NOT use collect() on large DataFrames — process server-side with save_as_table() or write to a stage.
• Use vectorized UDFs (10-100x faster) for batch/ML workloads instead of scalar UDFs.

5. 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') }}

Rules (HARD):

• Do NOT use {{ this }} without {% if is_incremental() %} guard — it will full-refresh and clobber data.
• Use dynamic_table materialization for streaming/near-real-time marts.

6. Performance Tuning

1. Cluster keys: Only for multi-TB tables, on columns used in WHERE/JOIN/GROUP BY. Do not cluster small tables.
2. Search Optimization: ALTER TABLE t ADD SEARCH OPTIMIZATION ON EQUALITY(col);
3. Warehouse sizing: Start X-Small, scale up only when needed. Set AUTO_SUSPEND = 60, AUTO_RESUME = TRUE.
4. Separate warehouses per workload (ETL, BI, ad-hoc) to avoid contention.
5. Estimate AI costs first before running large batch Cortex jobs:

SELECT SUM(AI_COUNT_TOKENS('claude-4-sonnet', text)) FROM table;

7. Security Hardening

1. Follow least-privilege RBAC. Use database roles for object-level grants.
2. Audit ACCOUNTADMIN regularly:

SHOW GRANTS OF ROLE ACCOUNTADMIN;

3. Use network policies for IP allowlisting.
4. Use masking policies for PII columns and row access policies for multi-tenant isolation.

Pitfalls

Verification

1. Dynamic Table is refreshing:

SELECT name, state, data_timestamp, refresh_mode
FROM INFORMATION_SCHEMA.DYNAMIC_TABLES
WHERE name = 'CLEANED_EVENTS';

Expected: state = 'OK', data_timestamp recent relative to TARGET_LAG.

2. Task is running (not suspended):

SELECT name, state FROM INFORMATION_SCHEMA.TASKS WHERE name = 'PROCESS_EVENTS';

Expected: state = 'STARTED'.

3. Stream has data:

SELECT SYSTEM$STREAM_HAS_DATA('raw_stream');

Expected: TRUE when changes are pending.

4. Stored procedure executes without "invalid identifier":

CALL my_proc(42);

Expected: returns result without error.

5. Cortex AI function returns expected type:

SELECT AI_SENTIMENT('I love this product!') AS sentiment;

Expected: numeric score between -1 and 1.

6. Cortex Agent is created:

SHOW AGENTS;

Expected: agent name appears in results.

7. Snowpark session connects:

print(session.sql("SELECT CURRENT_VERSION()").collect()[0][0])

Expected: Snowflake version string (e.g., 8.x.x).

8. dbt models compile and run:

dbt run --select my_dynamic_table_model

Expected: OK status for each model.

9. Search optimization is active:

SHOW SEARCH OPTIMIZATION ON TABLE my_table;

Expected: row with the optimization type and column.

10. No ACCOUNTADMIN over-grant:

SHOW GRANTS OF ROLE ACCOUNTADMIN;

Expected: review list — flag any unexpected user or role grants.

Limitations

• Use this skill only when the task clearly matches the scope described above.
• Do not treat the output as a substitute for environment-specific validation, testing, or expert review.
• Stop and ask for clarification if required inputs, permissions, safety boundaries, or success criteria are missing.