Develop and Optimize Snowflake Data Solutions

A Snowflake development reference covering SQL style, data pipelines, Cortex AI, Snowpark Python, dbt, performance, and security. SQL best practices call for snake_case identifiers, CTEs over nested subqueries, CREATE OR REPLACE for idempotent DDL, and explicit column lists instead of SELECT * since Snowflake's columnar storage only scans referenced columns; stored procedures require a colon : prefix on variables inside SQL statements or Snowflake raises an "invalid identifier" error. It covers semi-structured data access (VARIANT/OBJECT/ARRAY, casting nested fields with ::TYPE, STRIP_NULL_VALUE on load, LATERAL FLATTEN for arrays) and MERGE-based upserts. For pipelines it gives a decision table between Dynamic Tables (declarative, the default choice), Streams plus Tasks (imperative CDC), and Snowpipe (continuous file loading), plus Dynamic Table rules - progressive TARGET_LAG, incremental DTs cannot depend on full-refresh DTs, no SELECT *, change tracking must stay enabled, no Views between two Dynamic Tables - and a reminder that Tasks start SUSPENDED and must be explicitly resumed. It documents the current Cortex AI function set (AI_COMPLETE, AI_CLASSIFY, AI_FILTER, AI_EXTRACT, AI_SENTIMENT, AI_PARSE_DOCUMENT, AI_REDACT) against eight deprecated functions to avoid, a TO_FILE gotcha where the stage path and filename are separate arguments, and Cortex Agent creation rules including the $spec$ delimiter, models as an object not an array, tool_resources as a separate top-level object, and never editing production agents directly. For Snowpark Python it covers session configuration without hardcoded credentials, lazy DataFrame execution, avoiding collect() on large DataFrames, and vectorized UDFs for 10-100x throughput over scalar UDFs; for dbt it gives dynamic_table and incremental materialization configs plus the is_incremental() guard requirement. Performance guidance covers cluster keys for multi-TB tables only, search optimization, warehouse auto-suspend and auto-resume sizing, and token-cost estimation before Cortex AI calls; security guidance covers least-privilege RBAC, auditing ACCOUNTADMIN grants, network policies, and masking or row-access policies. A five-row error table maps common Snowflake errors to their cause and fix.

Use it when writing Snowflake SQL, building data pipelines, using Cortex AI functions or agents, working with Snowpark Python, or needing Snowflake-specific guidance for dbt, performance tuning, or security hardening. It is a reference of Snowflake-specific rules and gotchas rather than a general SQL or data-engineering tutorial.

Input is a Snowflake development task - SQL to write or debug, a pipeline to design, a Cortex AI function call, or a Snowpark or dbt configuration. Output is Snowflake-idiomatic code following the documented rules, for example an upsert pattern:

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());

Covers Snowflake SQL, Dynamic Tables, Streams and Tasks, Snowpipe, the Cortex AI function family and Cortex Agents, Snowpark Python (snowflake.snowpark.Session), and dbt materializations targeting Snowflake warehouses.

Data engineers and analysts writing production Snowflake SQL, pipelines, or Cortex AI integrations who need Snowflake's specific syntax rules and common failure modes rather than generic SQL guidance.