Snowflake Architecture and Best Practices AI Prompt

Design and optimize a Snowflake deployment for this organization.

Workload: {{workload}} (ELT, mixed, analytics, ML feature store)
Team: {{team}} (data engineers, analysts, data scientists)
Data volume: {{volume}}
Cost target: {{cost_target}}

1. Snowflake architecture concepts:
   - Separation of storage and compute: storage billed per TB, compute per second
   - Virtual warehouses: compute clusters that scale independently
   - Databases → Schemas → Tables: same as traditional databases
   - Zero-copy cloning: instant clone of any table/schema/database for testing or branching

2. Virtual warehouse strategy:
   - One warehouse per workload type (not per team):
     LOADING_WH (X-Large): bulk data loading, ELT
     ANALYTICS_WH (Small-Medium): analyst queries
     TRANSFORM_WH (Medium): dbt runs
     ML_WH (Large): data science ad-hoc
   - Auto-suspend: 60 seconds (analysts), 300 seconds (loading)
   - Auto-resume: always enabled
   - Multi-cluster warehouses: for concurrent analyst workloads

3. Cost optimization:
   - Monitor: QUERY_HISTORY view for expensive queries
   - Use result cache: same query within 24h returns the cached result (free)
   - Use CLUSTERING KEYS on large tables filtered by a column:
     ALTER TABLE orders CLUSTER BY (TO_DATE(order_date));
   - Use SEARCH OPTIMIZATION for high-cardinality point lookups
   - Tag warehouses with resource monitors to cap monthly spend

4. Snowflake roles hierarchy:
   ACCOUNTADMIN → SYSADMIN → custom roles
   - SYSADMIN: creates databases, warehouses
   - Custom roles: ANALYST_ROLE (SELECT), ENGINEER_ROLE (DML + DDL on own schema), LOADER_ROLE (COPY INTO)
   - Follow least-privilege; never use ACCOUNTADMIN for day-to-day operations

5. Data sharing:
   - Share data with other Snowflake accounts without copying (live read access)
   - CREATE SHARE; GRANT SELECT ON TABLE TO SHARE; ADD ACCOUNT TO SHARE
   - Use for: sharing data products with customers, cross-department data sharing

Return: warehouse configuration, role hierarchy DDL, clustering key recommendations, cost monitoring queries, and data sharing setup.