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, data pipelines, Cortex AI, or Snowpark Python.
• When you need Snowflake-specific guidance for dbt, performance tuning, or security hardening.

SQL Best Practices

Naming and Style

• Use snake_case for all identifiers. Avoid double-quoted identifiers — they create case-sensitive names requiring 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).

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

• VARIANT, OBJECT, ARRAY for JSON/Avro/Parquet/ORC.
• Access nested fields: src:customer.name::STRING. Always cast: src:price::NUMBER(10,2).
• VARIANT null vs SQL NULL: JSON null is stored as "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());

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:

• Set TARGET_LAG progressively: tighter at top, 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;

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$
{

…