Snowflake Data Engineering

This skill covers SQL conventions, pipeline architecture (Dynamic Tables, Streams, Tasks, Snowpipe), performance tuning, and cost/access management on Snowflake.

Workflow for Building a Snowflake Pipeline

1. Land raw data — Use Snowpipe (AUTO_INGEST = TRUE) for continuous file loads from an external stage, or Snowpipe Streaming for low-latency row-level ingestion via SDK.
2. Choose a transformation approach — Prefer Dynamic Tables for declarative, most pipelines; fall back to Streams + Tasks only when you need procedural logic or stored-procedure calls.
3. Model semi-structured data — Land raw JSON/Avro/Parquet as VARIANT, then flatten into typed relational columns as early as practical.
4. Chain pipeline stages — Build Dynamic Tables on top of each other (or Streams feeding Tasks) so each stage narrows scope from raw to cleaned to aggregated.
5. Tune for performance — Add clustering keys or Search Optimization only where query patterns justify them; tag queries for cost attribution.
6. Set access controls — Apply least-privilege RBAC with functional roles (loader, transformer, analyst) and masking/row-access policies for sensitive data.
7. Monitor cost and freshness — Track WAREHOUSE_METERING_HISTORY and QUERY_HISTORY, set Resource Monitors, and validate TARGET_LAG matches actual freshness requirements.

SQL and Semi-Structured Data

• Use VARIANT, OBJECT, and ARRAY types for JSON, Avro, Parquet, and ORC data.
• Access nested fields with colon notation and cast explicitly: src:customer.name::STRING, src:price::NUMBER(10,2), src:created_at::TIMESTAMP_NTZ.
• Flatten arrays with LATERAL FLATTEN: