---
description: Snowflake SQL and Snowpark conventions
globs: ["**/*.sql", "snowpark/**/*.py", "**/snowflake.yml"]
alwaysApply: false
---

# Snowflake

- Use fully qualified names `DATABASE.SCHEMA.OBJECT` in deployable SQL; never depend on the session's current database.
- DDL is idempotent: `CREATE OR ALTER` / `CREATE ... IF NOT EXISTS` where supported; avoid `CREATE OR REPLACE TABLE` on tables with data (it drops history and grants). Use `CREATE OR REPLACE` freely for views and functions.
- Least privilege: work under a functional role, not `ACCOUNTADMIN`/`SYSADMIN`. Grants live in versioned scripts.
- Cost: select only needed columns (no `SELECT *` on wide tables), filter on clustering/date columns, add `LIMIT` to exploratory queries, and use an XS warehouse with auto-suspend for dev.
- Use `QUALIFY` for window filtering, `MERGE` for upserts, and `COPY INTO` / Snowpipe for loads instead of row-by-row inserts.
- Semi-structured data: `VARIANT` with `:path::type` casts and `LATERAL FLATTEN`; cast early and name columns.
- Time: store `TIMESTAMP_TZ` or UTC `TIMESTAMP_NTZ` consistently; document which.
- Snowpark Python: build lazy DataFrames (`session.table(...).filter(col(...))`), push work into Snowflake, and call `.collect()`/`.to_pandas()` only on small results. Register UDFs/stored procs with explicit `packages` and types.
- Secrets and connection params come from `connections.toml`/env vars or key-pair auth, never from code.
- Deploy with the repo's tool (Snowflake CLI `snow`, dbt, or schemachange); don't run ad hoc DDL in production.

## Done when
SQL runs cleanly in a dev schema, `snow`/dbt validation passes, and new objects have grants and comments.