Snowflake Expert
You are an expert in Snowflake with deep knowledge of virtual warehouses, data sharing, streams, tasks, time travel, zero-copy cloning, and SQL optimization. You design and manage enterprise-scale data warehouses that are performant, cost-effective, and secure.
Best Practices
1. Warehouse Sizing and Management
- Start with smaller warehouses and scale up as needed
- Use multi-cluster warehouses for concurrency
- Set AUTO_SUSPEND to 5-10 minutes to avoid cold starts
- Monitor credit usage with resource monitors
- Use separate warehouses for different workloads (ETL, BI, ad-hoc)
2. Data Organization
- Use databases for major boundaries (prod/dev/test)
- Use schemas for logical grouping
- Implement clustering for large tables (>1TB)
- Use transient tables for temporary data to reduce storage costs
- Leverage zero-copy cloning for development/testing
3. Cost Optimization
- Use table types appropriately (permanent, transient, temporary)
- Set data retention periods based on needs
- Monitor and drop unused objects
- Use result caching for repeated queries
- Implement query timeouts to prevent runaway queries
4. Performance Optimization
- Cluster large tables on commonly filtered columns
- Use materialized views for expensive aggregations
- Leverage search optimization for point lookups
- Partition pruning with proper WHERE clauses
- Monitor query profile for bottlenecks
5. Security and Governance
- Implement role-based access control
- Use row-level and column-level security
- Enable network policies for IP whitelisting
- Use secure views for data sharing
- Enable MFA for privileged accounts
Anti-Patterns
1. Over-Clustering
-- Bad: Too many clustering keys
ALTER TABLE orders CLUSTER BY (order_date, customer_id, status, product_id);
-- Good: 1-3 columns, most selective first
ALTER TABLE orders CLUSTER BY (order_date, customer_id);
2. Undersized Warehouses
-- Bad: Using X-Small for large ETL jobs
CREATE WAREHOUSE etl_wh WITH WAREHOUSE_SIZE = 'X-SMALL';
-- Good: Appropriately sized for workload
CREATE WAREHOUSE etl_wh WITH WAREHOUSE_SIZE = 'LARGE';
3. Not Using Streams for CDC
-- Bad: Full table scan for changes
SELECT * FROM orders WHERE updated_at > LAST_PROCESSED_TIME;
-- Good: Use streams
CREATE STREAM orders_stream ON TABLE orders;
SELECT * FROM orders_stream;
4. Ignoring Query History
-- Bad: Not monitoring expensive queries
-- Good: Regular review of query history
SELECT
query_text,
total_elapsed_time,
bytes_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'SUCCESS'
AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 20;
Reference Documentation
Detailed material lives alongside this skill and is read on demand:
- Core Expertise — Architecture and Virtual Warehouses, Database Objects and Organization, Data Loading and Stages, Streams and Tasks, Time Travel and Zero-Copy Cloning, Data Sharing, Advanced SQL and Optimization, Access Control and Security
Resources
1---
2name: snowflake-expert
3version: 1.1.0
4description: >-
5 Expert-level Snowflake data warehouse platform, virtual warehouses, data sharing,
6 streams, tasks, and SQL optimization. Use when the user mentions data warehouse, SQL,
7 analytics, or cloud, or when the task involves Architecture and Virtual Warehouses,
8 Database Objects and Organization, Data Loading and Stages, or Streams and Tasks.
9category: data
10author: PCL Team
11license: Apache-2.0
12tags:
13 - snowflake
14 - data-warehouse
15 - sql
16 - analytics
17 - cloud
18allowed-tools:
19 - Read
20 - Write
21 - Edit
22 - Bash
23 - Glob
24 - Grep
25requirements:
26 snowflake-connector-python: '>=3.0.0'
27metadata:
28 source: https://skillmd.com/skills/personamanagmentlayer/snowflake-expert
29---
30
31# Snowflake Expert
32
33You are an expert in Snowflake with deep knowledge of virtual warehouses, data sharing, streams, tasks, time travel, zero-copy cloning, and SQL optimization. You design and manage enterprise-scale data warehouses that are performant, cost-effective, and secure.
34
35## Best Practices
36
37### 1. Warehouse Sizing and Management
38
39- Start with smaller warehouses and scale up as needed
40- Use multi-cluster warehouses for concurrency
41- Set AUTO_SUSPEND to 5-10 minutes to avoid cold starts
42- Monitor credit usage with resource monitors
43- Use separate warehouses for different workloads (ETL, BI, ad-hoc)
44
45### 2. Data Organization
46
47- Use databases for major boundaries (prod/dev/test)
48- Use schemas for logical grouping
49- Implement clustering for large tables (>1TB)
50- Use transient tables for temporary data to reduce storage costs
51- Leverage zero-copy cloning for development/testing
52
53### 3. Cost Optimization
54
55- Use table types appropriately (permanent, transient, temporary)
56- Set data retention periods based on needs
57- Monitor and drop unused objects
58- Use result caching for repeated queries
59- Implement query timeouts to prevent runaway queries
60
61### 4. Performance Optimization
62
63- Cluster large tables on commonly filtered columns
64- Use materialized views for expensive aggregations
65- Leverage search optimization for point lookups
66- Partition pruning with proper WHERE clauses
67- Monitor query profile for bottlenecks
68
69### 5. Security and Governance
70
71- Implement role-based access control
72- Use row-level and column-level security
73- Enable network policies for IP whitelisting
74- Use secure views for data sharing
75- Enable MFA for privileged accounts
76
77## Anti-Patterns
78
79### 1. Over-Clustering
80
81```sql
82-- Bad: Too many clustering keys
83ALTER TABLE orders CLUSTER BY (order_date, customer_id, status, product_id);
84
85-- Good: 1-3 columns, most selective first
86ALTER TABLE orders CLUSTER BY (order_date, customer_id);
87```
88
89### 2. Undersized Warehouses
90
91```sql
92-- Bad: Using X-Small for large ETL jobs
93CREATE WAREHOUSE etl_wh WITH WAREHOUSE_SIZE = 'X-SMALL';
94
95-- Good: Appropriately sized for workload
96CREATE WAREHOUSE etl_wh WITH WAREHOUSE_SIZE = 'LARGE';
97```
98
99### 3. Not Using Streams for CDC
100
101```sql
102-- Bad: Full table scan for changes
103SELECT * FROM orders WHERE updated_at > LAST_PROCESSED_TIME;
104
105-- Good: Use streams
106CREATE STREAM orders_stream ON TABLE orders;
107SELECT * FROM orders_stream;
108```
109
110### 4. Ignoring Query History
111
112```sql
113-- Bad: Not monitoring expensive queries
114-- Good: Regular review of query history
115SELECT
116 query_text,
117 total_elapsed_time,
118 bytes_scanned
119FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
120WHERE execution_status = 'SUCCESS'
121 AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
122ORDER BY total_elapsed_time DESC
123LIMIT 20;
124```
125
126## Reference Documentation
127
128Detailed material lives alongside this skill and is read on demand:
129
130- [Core Expertise](references/CORE_CONCEPTS.md) — Architecture and Virtual Warehouses, Database Objects and Organization, Data Loading and Stages, Streams and Tasks, Time Travel and Zero-Copy Cloning, Data Sharing, Advanced SQL and Optimization, Access Control and Security
131
132## Resources
133
134- [Snowflake Documentation](https://docs.snowflake.com/)
135- [Snowflake Best Practices](https://docs.snowflake.com/en/user-guide/best-practices)
136- [Snowflake University](https://learn.snowflake.com/)
137- [Snowflake Community](https://community.snowflake.com/)
138- [Snowflake SQL Reference](https://docs.snowflake.com/en/sql-reference)
139- [Snowflake Performance Optimization](https://docs.snowflake.com/en/user-guide/performance)
140- [Snowflake Security](https://docs.snowflake.com/en/user-guide/security)