{"filename":"snowflake-data-engineering-cursorrules-prompt-file.mdc","content":"---\ndescription: \"Cursor rules for Snowflake SQL, data pipelines (Dynamic Tables, Streams, Tasks, Snowpipe), semi-structured data, Snowflake Postgres, and cost optimization.\"\nglobs: **/*\nalwaysApply: false\n---\n// Snowflake Data Engineering\n// Comprehensive guidance for SQL, data pipelines, and platform best practices on Snowflake\n\nYou are an expert Snowflake data engineer with deep knowledge of the entire platform: SQL, data pipelines (Dynamic Tables, Streams, Tasks, Snowpipe), semi-structured data, Snowflake Postgres, and cost optimization.\n\n// Architecture\n// Snowflake separates storage (columnar micro-partitions), compute (elastic virtual warehouses), and services (metadata, security, optimization).\n\n// ═══════════════════════════════════════════\n// SQL AND SEMI-STRUCTURED DATA\n// ═══════════════════════════════════════════\n\n// Use VARIANT, OBJECT, and ARRAY types for JSON, Avro, Parquet, ORC.\n// Access nested fields with colon notation: src:customer.name::STRING\n// Cast explicitly: src:price::NUMBER(10,2), src:created_at::TIMESTAMP_NTZ\n// Flatten arrays:\n//   SELECT f.value:name::STRING AS name\n//   FROM my_table, LATERAL FLATTEN(input => src:items) f;\n// Flatten semi-structured into relational columns when data contains dates, numbers as strings, or arrays.\n// Avoid mixed types in the same VARIANT field — prevents subcolumnarization.\n// VARIANT null vs SQL NULL: JSON null stored as string \"null\". Use STRIP_NULL_VALUES => TRUE on load.\n\n// SQL Coding Standards\n// - snake_case for all identifiers. Avoid quoted identifiers.\n// - CTEs over nested subqueries. CREATE OR REPLACE for idempotent DDL.\n// - COPY INTO for bulk loading, not INSERT. MERGE for upserts:\n//   MERGE INTO target t USING source s ON t.id = s.id\n//   WHEN MATCHED THEN UPDATE SET t.name = s.name\n//   WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);\n\n// Stored Procedures — prefix variables with colon : inside SQL statements:\n//   CREATE PROCEDURE my_proc(p_id INT) RETURNS STRING LANGUAGE SQL AS\n//   BEGIN\n//     LET result STRING;\n//     SELECT name INTO :result FROM users WHERE id = :p_id;\n//     RETURN result;\n//   END;\n\n// ═══════════════════════════════════════════\n// PERFORMANCE OPTIMIZATION\n// ═══════════════════════════════════════════\n\n// Cluster keys: for very large tables (multi-TB), on WHERE/JOIN/GROUP BY columns.\n//   ALTER TABLE large_events CLUSTER BY (event_date, region);\n// Search Optimization Service: point lookups on high-cardinality columns, substring/regex.\n//   ALTER TABLE logs ADD SEARCH OPTIMIZATION ON EQUALITY(sender_ip), SUBSTRING(error_message);\n// Materialized Views: pre-compute expensive aggregations (single table only).\n// Use RESULT_SCAN(LAST_QUERY_ID()) to reuse results. Query tags for attribution:\n//   ALTER SESSION SET QUERY_TAG = 'etl_daily_load';\n\n// ═══════════════════════════════════════════\n// DATA PIPELINES\n// ═══════════════════════════════════════════\n\n// Choose Your Approach:\n// Dynamic Tables   — Declarative. Define the query, Snowflake handles refresh. Best for most pipelines.\n// Streams + Tasks  — Imperative CDC + scheduling. Best for procedural logic, stored procedure calls.\n// Snowpipe         — Continuous file loading from S3/GCS/Azure.\n// Snowpipe Streaming — Low-latency row-level ingestion via SDK (Java, Python).\n\n// Dynamic Tables\nCREATE OR REPLACE DYNAMIC TABLE cleaned_events\n  TARGET_LAG = '5 minutes'\n  WAREHOUSE = transform_wh\n  AS\n  SELECT event_id, event_type, user_id, event_data:page::STRING AS page, event_timestamp\n  FROM raw_events\n  WHERE event_type IS NOT NULL;\n\n// Chain for multi-step pipelines:\nCREATE OR REPLACE DYNAMIC TABLE user_sessions\n  TARGET_LAG = '10 minutes'\n  WAREHOUSE = transform_wh\n  AS\n  SELECT user_id, MIN(event_timestamp) AS session_start, MAX(event_timestamp) AS session_end, COUNT(*) AS event_count\n  FROM cleaned_events GROUP BY user_id;\n\n// TARGET_LAG: freshness target. REFRESH_MODE: AUTO, FULL, or INCREMENTAL.\n// Manage: ALTER DYNAMIC TABLE ... SET TARGET_LAG / REFRESH / SUSPEND / RESUME.\n\n// Streams (CDC)\nCREATE OR REPLACE STREAM raw_events_stream ON TABLE raw_events;\n// Columns added: METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID\n// APPEND_ONLY = TRUE for insert-only sources (lower overhead).\n\n// Tasks (Scheduled/Triggered)\nCREATE OR REPLACE TASK process_events\n  WAREHOUSE = transform_wh\n  SCHEDULE = 'USING CRON 0 */1 * * * America/Los_Angeles'\n  WHEN SYSTEM$STREAM_HAS_DATA('raw_events_stream')\n  AS\n  INSERT INTO cleaned_events\n  SELECT event_id, event_type, user_id, event_timestamp\n  FROM raw_events_stream WHERE event_type IS NOT NULL;\n\n// Task DAGs: CREATE TASK child_task ... AFTER parent_task ...\n// Tasks start SUSPENDED — ALTER TASK ... RESUME to enable.\n\n// Snowpipe\nCREATE OR REPLACE PIPE my_pipe AUTO_INGEST = TRUE AS\n  COPY INTO raw_events FROM @my_external_stage FILE_FORMAT = (TYPE = 'JSON');\n\n// Common Pattern: Snowpipe → Dynamic Table chain (simplest end-to-end pipeline).\n\n// ═══════════════════════════════════════════\n// TIME TRAVEL AND DATA PROTECTION\n// ═══════════════════════════════════════════\n\n// Time Travel (default 1 day, up to 90 on Enterprise+):\n//   SELECT * FROM my_table AT(TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP);\n//   SELECT * FROM my_table BEFORE(STATEMENT => '<query_id>');\n// UNDROP TABLE/SCHEMA/DATABASE to recover dropped objects.\n// Zero-copy cloning: CREATE TABLE clone CLONE source; CREATE SCHEMA dev CLONE prod;\n\n// ═══════════════════════════════════════════\n// SNOWFLAKE POSTGRES\n// ═══════════════════════════════════════════\n\n// Managed PostgreSQL (v16/17/18) with full wire compatibility.\n// CREATE POSTGRES INSTANCE my_instance COMPUTE_FAMILY='STANDARD_S' STORAGE_SIZE_GB=50;\n// Bridge OLTP to analytics via pg_lake extension (Iceberg tables readable from both Postgres and Snowflake).\n// FORK for point-in-time recovery. HIGH_AVAILABILITY = TRUE for production.\n\n// ═══════════════════════════════════════════\n// WAREHOUSE AND COST MANAGEMENT\n// ═══════════════════════════════════════════\n\n// Size by query complexity, not data volume. Start X-Small, scale up.\n// AUTO_SUSPEND = 60, AUTO_RESUME = TRUE. Separate warehouses per workload.\n// Multi-cluster for concurrency scaling. Transient tables for staging (no Fail-safe cost).\n// Monitor: SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY, WAREHOUSE_METERING_HISTORY.\n// Resource Monitors for credit limits. Avoid SELECT * on wide tables.\n\n// Access Control\n// Least-privilege RBAC. Database roles for object grants.\n// Masking policies for PII. Row access policies for multi-tenant isolation.\n// Functional roles: loader (write raw), transformer (read raw, write analytics), analyst (read analytics).\n\n// Data Sharing\n// CREATE SHARE for zero-copy cross-account sharing. Snowflake Marketplace for exchange.\n\n// Iceberg Tables\n// CREATE ICEBERG TABLE ... CATALOG='SNOWFLAKE' EXTERNAL_VOLUME='vol' BASE_LOCATION='path/';\n// Interoperable with Spark, Flink, Trino.\n\n// Anti-Patterns\n- Do NOT use streams+tasks for simple transformations that dynamic tables can handle.\n- Do NOT set TARGET_LAG shorter than needed — directly impacts cost.\n- Do NOT forget to RESUME tasks after creation.\n- Do NOT use SELECT * on wide tables. Do NOT skip clustering analysis on multi-TB tables.\n- Do NOT hardcode database/schema names in reusable code.\n"}