Stack conventions
Snowflake (SQL, Snowpark, schemachange)
Snowflake code: Snowflake SQL and versioned migrations (schemachange or Flyway), Snowpark Python (DataFrames, stored procedures, UDFs and UDTFs), tasks, streams, dynamic tables, roles and grants; Snowpark tested locally with pytest in local testing mode.
Snowflake code: Snowflake SQL and versioned migrations (schemachange or Flyway), Snowpark Python (DataFrames, stored procedures, UDFs and UDTFs), tasks, streams, dynamic tables, roles and grants; Snowpark tested locally with pytest in local testing mode.
Used for: snowflake.
Principles
- Change objects through versioned migrations (schemachange V
.sql, R for views and procedures redefined as a whole, A__ only for idempotent always-run scripts) and never edit a committed versioned script. - Use role-based access control with least privilege: functional roles granted to access roles on specific databases and schemas, managed access schemas, future grants, no work done as ACCOUNTADMIN or SYSADMIN, and nothing granted to PUBLIC.
- Authenticate pipelines and services with key-pair or OAuth (workload identity) as a dedicated service user; never a password, and never a secret in code, snowflake.yml, schemachange-config.yml or connections.toml committed to the repository.
- Bind every value in dynamic SQL (EXECUTE IMMEDIATE ... USING, :bind variables, session.sql(query, params=[...]) in Snowpark) and quote dynamic identifiers with IDENTIFIER(:name); concatenated input is SQL injection.
- Keep Snowpark logic in importable Python functions from DataFrame to DataFrame and unit-test them with pytest in local testing mode (Session.builder.config('local_testing', True)), which needs no account.
- Parameterise database, schema and warehouse names per environment (schemachange vars, Jinja templating, snowflake.yml env) so the same scripts deploy to dev, test and prod.
- Design for cost: right-size warehouses with auto-suspend, filter early so pruning works, and add clustering keys only to very large tables whose queries filter on those columns.
- Make pipelines incremental with streams and tasks or dynamic tables (TARGET_LAG), and make each task idempotent with MERGE on a key.
- Integration tests and deployments that need an account run in CI against a dedicated test database; offline they are reported as not run, never as passed.
- Lint SQL with sqlfluff (dialect snowflake) and Python with ruff, following the repository's configuration.
Conventions
Accessibility
Concurrency
- Tasks in a DAG run in order; one stream must be consumed by one task, and each MERGE is keyed so concurrent or repeated runs are safe.
- schemachange or Flyway runs from one pipeline at a time per environment (one concurrency group), with the change history table as the record.
Dependencies
- Pin snowflake-snowpark-python, snowflake-connector-python, schemachange and the Snowflake CLI in requirements or pyproject and audit them; keep the Python version matching the runtime of procedures and UDFs.
Documentation
- COMMENT on every table, view, column, procedure and task that people or tools use; tag sensitive columns (object tagging).
- Each migration starts with a comment on why it exists; the README documents environments, roles, how to run the tests locally and how deployments run.
Errors
- In Snowflake Scripting use EXCEPTION blocks that re-raise with context (RAISE) or return a clear error; declare custom exceptions with codes; never swallow errors in a WHEN OTHER handler that returns success.
- Set task error notifications (ERROR_INTEGRATION) and SUSPEND_TASK_AFTER_NUM_FAILURES so a failing task alerts instead of failing silently forever.
- Wrap multi-statement changes in explicit transactions (BEGIN ... COMMIT) where they must be atomic; remember DDL commits implicitly.
Logging
- Log from stored procedures and UDFs with Python logging or SYSTEM$LOG to an event table, and trace with the telemetry API; monitor tasks with TASK_HISTORY and alerts.
- Set QUERY_TAG for pipelines and tools so cost and query history (ACCOUNT_USAGE.QUERY_HISTORY) can be traced to the job; watch warehouse credit use with resource monitors.
- Never log row data with personal information or secret values.
Naming
- Unquoted identifiers (stored upper case) in snake_case; never quoted mixed-case names; databases and schemas named by domain and layer (RAW, STAGING, ANALYTICS or SALES_RAW), roles by purpose and level (SALES_RO, ETL_SERVICE).
- Name migrations V
. . .sql (schemachange) or V (Flyway) with two underscores; R__ - Python in snake_case; Snowpark DataFrame variables named for their content; stored procedure and UDF handlers named for what they compute.
Performance
- Let micro-partition pruning work: filter on the columns data is ordered or clustered by, avoid functions on filter columns, and select only needed columns (never SELECT * on wide tables).
- Add clustering keys only for multi-terabyte tables with selective filters, and check SYSTEM$CLUSTERING_INFORMATION before and after; consider search optimization for point lookups.
- Right-size warehouses per workload with AUTO_SUSPEND and AUTO_RESUME, use multi-cluster for concurrency rather than a larger size, and avoid row-by-row loops in procedures.
- Prefer set-based SQL and Snowpark DataFrame operations pushed down to Snowflake over collect() into Python; use vectorised UDFs where Python is needed.
Security
- Credentials only from the CI system's secret store or a key vault (SNOWFLAKE_PRIVATE_KEY_PATH, workload identity); never passwords in scripts, snowflake.yml, schemachange-config.yml or connections.toml in the repository; service users have no password (TYPE = SERVICE).
- Least privilege: no work as ACCOUNTADMIN, no GRANT ALL, no grants to PUBLIC, managed access schemas, network policies for service users, and masking and row access policies on sensitive columns.
- Bind values and use IDENTIFIER() for dynamic object names in Snowflake Scripting and Snowpark (session.sql with params); never format input into SQL text (injection).
- Validate staged files and external input (file formats with explicit types, ON_ERROR handling, VALIDATION_MODE) before loading; schema-check semi-structured data when flattening VARIANT.
- Pin snowflake-snowpark-python, schemachange and connectors to versions and audit them; package versions for procedures and UDFs come from the Snowflake Anaconda channel or a pinned requirements list.
Structure
- migrations/ (or the schemachange root folder) for SQL changes, src/
/ for Snowpark code, snowflake.yml for Snowflake CLI projects (procedures, UDFs, Streamlit), tests/ for pytest. - One object per repeatable script and one logical change per versioned script; account-level objects (roles, warehouses, integrations) in their own folder applied by an administrator role.
- Environment-specific names come from variables (schemachange --vars, Jinja {{ database }}, snowflake.yml env), never copied scripts per environment.
Testing
- Unit tests for Snowpark code with pytest in local testing mode: Session.builder.config('local_testing', True).create() in a session fixture, small DataFrames from session.create_dataframe, and asserts on collect() results; no account or network.
- Integration tests run SQL, procedures and tasks in CI against a dedicated test database (a zero-copy clone or a schema per pull request) and drop it afterwards; they need an account and a service user.
- Name tests test_
_ and keep one behaviour per test; local testing mode does not support every function, so mark tests that need the real engine and run them in the integration job. - Done means tested: new or changed behaviour ships with tests in the same change, a fixed bug gets a regression test, and the migration check passes.
Review checklist
- Is every schema change a new versioned script, with no committed one edited?
- Are roles and grants least privilege (access roles, future grants, nothing to PUBLIC, no ACCOUNTADMIN work)?
- Are all values in dynamic SQL bound and object names passed through IDENTIFIER()?
- Is authentication by key pair or OAuth, with no password or key in the repository?
- Are environment names (databases, schemas, warehouses) variables rather than hard-coded?
- Does Snowpark logic have unit tests in local testing mode?
- Are tasks idempotent (MERGE by key), with error notifications and failure suspension?
- Do queries prune well, and are clustering keys justified by table size and filters?
- Do warehouses have auto-suspend and the right size for their workload?
- Is sensitive data protected by masking or row access policies and tags?
- Do sqlfluff (snowflake dialect), ruff and the migration check pass?
- Are objects and migrations documented with comments?
Quality checks (run when your repository has the tool configured)
| Check | Kind | Tool | Blocks the pull request |
|---|---|---|---|
sqlfluff |
lint | sqlfluff | no |
sql-migrations |
test | migration check | no |
sql-grants |
security | grep | no |
sql-dynamic-sql |
security | grep | no |
ruff |
lint | ruff | no |
ruff-format |
format | ruff | no |
mypy |
typecheck | mypy | no |
pyright |
typecheck | pyright | no |
pip-audit |
security | pip-audit | no |
gitleaks |
security | gitleaks | no |