Snowflake Snowpark Python & dbt

This skill covers building production data transformation pipelines with Snowpark Python (Snowflake's server-side Python API) and with dbt using the dbt-snowflake adapter.

Workflow for a Snowpark or dbt Transformation

1. Snowpark: open a session — Build a Session from environment-scoped credentials, specifying role, warehouse, database, and schema explicitly.
2. Snowpark: express transforms with the DataFrame API — Prefer .filter(), .select(), .group_by().agg(), and .join() over raw SQL strings for reusable pipeline code; DataFrames are lazily evaluated and only execute on .collect()/.show()/a write action.
3. Snowpark: push compute server-side — Use scalar UDFs for row-wise logic, vectorized (pandas) UDFs for ML inference, UDTFs when one input row produces multiple output rows, and stored procedures for multi-step server-side orchestration.
4. dbt: model in layers — Staging models (stg_*) rename and type-cast; mart models express business logic on top of staging.
5. dbt: choose a materialization — view for cheap logic, table only when reads are frequent, incremental for large fact tables, dynamic_table for near-real-time freshness needs.
6. dbt: define sources and tests — Declare sources in _sources.yml with freshness thresholds; add unique/not_null tests on key columns.
7. dbt: run selectively — Use dbt run --select model+ (model and downstream) or +model (model and upstream) instead of full-project runs during iteration.
8. dbt: build and validate — Run dbt build (run + test in dependency order) before merging, and dbt docs generate to keep documentation current.

Snowpark Python

Snowpark runs Python server-side inside a Snowflake warehouse — data never leaves Snowflake. Core abstractions: Session, DataFrame, UDF, UDTF, UDAF, and Stored Procedure.