snowflake

name	snowflake
description	Snowflake playbook. Workflows for RBAC rollout, warehouse sizing and cost control, secure data sharing, Streams/Tasks/Snowpipe pipelines, permissions audit, and Time Travel / Fail-safe recovery. Use when designing, operating, or auditing Snowflake.

Snowflake - Playbook

Companion rule: 482-snowflake.mdc. This skill turns those patterns into end-to-end workflows.

---

When to invoke

Use when the user is:

• Designing a new Snowflake account, database, or warehouse
• Rolling out or auditing RBAC (roles, grants, object ownership)
• Tuning cost (warehouse sizing, auto-suspend, query optimization, storage)
• Building pipelines with Snowpipe, Streams, Tasks, or Dynamic Tables
• Setting up secure data sharing (Reader Accounts, Snowflake Marketplace, Private Listings)
• Recovering from a bad change (Time Travel, Fail-safe)
• Governing sensitive data (masking, row access policies, tagging)

---

Golden Rules

1. Roles hold privileges; users hold roles. Never grant directly to users.
2. Use USE ROLE at the top of every script. Implicit roles cause access-denied mysteries.
3. Warehouses suspend aggressively (60s auto-suspend for interactive; higher for streaming).
4. Right-size warehouses; scale with multi-cluster, not with XL.
5. Time Travel is real; Fail-safe is last-resort. Set retention deliberately per schema.
6. Every production role chain has an owner and an audit trail.

---

Workflow 1 - RBAC Rollout

Design a production-grade role hierarchy and migrate away from ACCOUNTADMIN-as-default.

Steps

1. Map your access model to Snowflake's RBAC:
   • System roles: ORGADMIN, ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, PUBLIC
   • Custom roles: business- and data-oriented
2. Use the standard hierarchy:
   • Functional roles (granted to users): e.g., ANALYST_SALES, ENGINEER_DATA, ADMIN_FINOPS
   • Access roles (hold privileges on objects): e.g., DB_SALES_READ, DB_SALES_WRITE, WH_LOAD_USE
   • Functional roles inherit from access roles, not from each other
3. Ownership:
   • Custom databases / schemas / warehouses are OWNED by a custom role (e.g., DB_SALES_OWNER), never by ACCOUNTADMIN
   • SYSADMIN sits above functional roles; custom-role-owned objects grant managed access through their owner role
4. USE MANAGED ACCESS schemas for any schema where multiple teams grant access - centralize grant control.
5. Separation of duties:
   • USERADMIN manages users + role grants; SECURITYADMIN manages privilege grants on objects; they do NOT overlap
   • Application service users are locked to a specific role via DEFAULT_ROLE and cannot switch
6. Audit:
   • Snapshot GRANTS to a governance schema nightly (SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES)
   • Alert on new grants to ACCOUNTADMIN / SECURITYADMIN
7. Terraform / dbt:
   • Manage roles and grants via snowflake-terraform-provider (Snowflake Labs) or Permifrost (dbt Labs)
   • PR-reviewed changes; drift detection
8. Migrate existing access incrementally; lock down ACCOUNTADMIN-for-daily-use.

Deliverable: Role hierarchy diagram, Terraform modules, audit job, migration runbook.

See references/rbac-patterns.md.

---

Workflow 2 - Warehouse Sizing and Cost Control

Keep Snowflake cheap without starving workloads.

Steps

1. Classify workloads:
   • Interactive (BI, ad-hoc) - small/medium WH, multi-cluster auto-scale, short auto-suspend
   • Batch ETL - medium/large WH, single cluster, on-demand
   • Streaming (Snowpipe, Tasks) - small WH, tasks share warehouses
   • ML / heavy - large/xlarge WH, purpose-specific
2. Start small, scale on evidence. Double warehouse size only if >50% of query time is spillage or queueing.
3. Multi-cluster warehouses for concurrency, not for speed. A bigger warehouse makes one query faster; multi-cluster lets more concurrent queries run at the same speed.
4. Auto-suspend:
   • Interactive: 60s
   • Batch / streaming: 60-600s depending on duty cycle
   • Never AUTO_SUSPEND = 0 in prod
5. Resource monitors per warehouse (or per warehouse group):
   • Daily, weekly, monthly credit quotas
   • Actions: notify at 75%, suspend at 100%
   • Owner and escalation path in the notification message
6. Query tuning:
   • EXPLAIN and QUERY_HISTORY the slow queries
   • Prune with clustering keys only when necessary (micro-partition pruning is automatic - test before adding)
   • Materialize results (tables / materialized views) for repeated heavy scans
   • Use Query Acceleration Service for BI workloads with unpredictable heavy queries
7. Storage:
   • Time Travel retention appropriate to schema (0 for staging scratch, 1d for most, 7-90d for critical)
   • Transient / temporary tables for scratch
   • Track storage cost by database with ACCOUNT_USAGE.STORAGE_DAILY_HISTORY
8. Reports:
   • Weekly top-N queries by credit cost
   • Weekly top-N tables by storage growth
   • Month-over-month trend

Deliverable: Warehouse catalog, resource monitors, dashboard, slow-query hit list.

---

Workflow 3 - Pipeline via Snowpipe / Streams / Tasks / Dynamic Tables

When to use which

Use	Why
Snowpipe	Continuous ingestion from S3/GCS/ADLS into raw tables
Streams	CDC (change-tracking) on tables; power incremental pipelines
Tasks	Scheduled or conditional SQL; often reads from streams
Dynamic Tables	Declarative, managed materialization (prefer over manual Stream+Task where it fits)
Snowflake Connector for Kafka	Direct Kafka ingestion when latency < Snowpipe is needed

Design pattern

Cloud storage  --Snowpipe-->  raw.tableRaw (VARIANT)
                                     |
                                     | Stream cdc_raw
                                     v
staging.tableStaged  <--Task-- INSERT/MERGE from raw via stream consumption
                                     |
                                     | Stream cdc_staging
                                     v
warehouse.fact_table  <--Task-- MERGE into dimensional model

Or with Dynamic Tables:

raw.tableRaw  --> dt_staging (target lag 5 min)  --> dt_fact (target lag 15 min)

Guardrails

• Idempotent MERGE (use stable business keys)
• DLQ table for malformed rows
• Monitoring: unprocessed stream rows, task failure count, ingestion latency
• Alert thresholds and runbook for each pipeline

---

Workflow 4 - Secure Data Sharing

Share data without moving it.

Options

• Listings (Snowflake Marketplace): public or private catalog-based sharing
• Private Listings: curated access with request workflow
• Direct Shares: account-to-account inside the same cloud region; cross-region uses Replication
• Reader Accounts: consumer gets a Snowflake account you pay for (to share with non-Snowflake customers)

Design checklist

• Secure views / secure UDFs for shared objects (prevent data peek via EXPLAIN)
• Explicit column selection; no SELECT * on shared views
• Row access policies for tenant-level filtering where applicable
• Masking policies for any PII in shared objects
• Document retention / SLA for the share; versioning strategy for schema changes
• Consumer-side usage reported back via SHARE_USAGE (governance)

---

Workflow 5 - Permissions Audit

Quarterly or after any org change.

Steps

1. Dump grants from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS, GRANTS_TO_ROLES, GRANTS_ON_OBJECTS.
2. Flag:
   • Direct grants to users (should be zero)
   • ACCOUNTADMIN / SECURITYADMIN granted to non-service identities beyond a small list
   • Privileges granted to PUBLIC
   • Objects owned by ACCOUNTADMIN (drift; should be owned by custom role)
3. Check users:
   • Disabled / stale users still holding roles
   • Service users without scoped roles
4. Check network policies:
   • Service users restricted to known CIDRs / PrivateLink
   • Admin roles require MFA
5. Masking / row-access policies:
   • Cover all PII columns?
   • Applied via tags where feasible
6. File findings with owner, severity, fix.

---

Workflow 6 - Recovery via Time Travel and Fail-safe

Time Travel = queryable historical data (0-90 days, per-object DATA_RETENTION_TIME_IN_DAYS).
Fail-safe = Snowflake-only recovery, 7 days, for permanent tables only, not self-serve.

Recovery playbook

1. Quantify the blast radius: which objects, what time, which rows?

2. Query the past with AT(OFFSET => ...), AT(TIMESTAMP => ...), BEFORE(STATEMENT => ...):

   SELECT * FROM my_table AT(OFFSET => -3600); -- 1 hour ago
   SELECT * FROM my_table BEFORE(STATEMENT => '<query_id>');
   

3. Clone to isolate:

   CREATE TABLE my_table_recovered CLONE my_table AT(OFFSET => -3600);
   

4. Restore selectively (SQL MERGE from clone).

5. Undrop recently-dropped objects:

   UNDROP TABLE my_table;
   

6. If Time Travel is expired, engage Snowflake support for Fail-safe recovery. Not self-serve; not a plan.

Prevention

• Set DATA_RETENTION_TIME_IN_DAYS per schema (>= 7 for prod, >= 30 for critical)
• TRANSIENT tables where durability matters less than cost
• Don't DROP DATABASE in prod from a human session; use roles that cannot

---

Anti-patterns

1. Users with ACCOUNTADMIN for daily work
2. Grants direct to users
3. AUTO_SUSPEND disabled or very long on interactive warehouses
4. Clustering keys added "for performance" without an EXPLAIN showing benefit
5. SELECT * across wide VARIANT tables in production queries
6. Secrets in SQL scripts or task definitions (use External Functions + vault)
7. Tasks using ACCOUNTADMIN as OWNER
8. Shares exposing base tables instead of secure views
9. No resource monitors on shared warehouses
10. No storage / credit dashboard

---

References

• references/rbac-patterns.md - Role hierarchy, ownership, managed access, service accounts

Related

• Rule: 482-snowflake.mdc
• Rule: 480-data-engineering.mdc (cross-platform data contracts, DQ, governance)
• Rule: 475-sql.mdc (safe SQL patterns)
• Rule: 316-zero-trust.mdc (data tier controls)
• Skill: data-engineering (pipeline concepts)
• Skill: database-postgresql (for comparison)