Skip to content

Commit a943d74

Browse files
altimateanasclaude
andcommitted
Adding snowflake-setup skill
Co-Authored-By: Claude Opus 4.7 (1M context) <[email protected]>
1 parent 8e76c90 commit a943d74

22 files changed

Lines changed: 5664 additions & 0 deletions

‎.opencode/.gitignore‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,3 +5,5 @@ bun.lock
55
.gitignore
66
package-lock.json
77
references/
8+
!skills/snowflake-setup/references/
9+
!skills/snowflake-setup/references/**

‎.opencode/skills/snowflake-setup/SKILL.md‎

Lines changed: 428 additions & 0 deletions
Large diffs are not rendered by default.

‎.opencode/skills/snowflake-setup/TESTING.md‎

Lines changed: 387 additions & 0 deletions
Large diffs are not rendered by default.
Lines changed: 354 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,354 @@
1+
# Advanced Features
2+
3+
Optional sections the skill emits when specific opt-in questions are answered. Each has its own trigger question and can be enabled independently.
4+
5+
## Network Security and SSO
6+
7+
### Trigger questions
8+
- "Restrict Snowflake access to specific IP ranges?" (VPN, office, cloud VPC)
9+
- "Federate authentication via Okta / Azure AD / Google Workspace?"
10+
11+
### Account-level network policy
12+
13+
```sql
14+
USE ROLE SECURITYADMIN;
15+
16+
CREATE NETWORK POLICY office_and_vpn
17+
ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.5/32', '10.100.0.0/16')
18+
BLOCKED_IP_LIST = ()
19+
COMMENT = 'Corporate office + VPN ranges';
20+
21+
-- Apply to entire account (default policy)
22+
ALTER ACCOUNT SET NETWORK_POLICY = office_and_vpn;
23+
24+
-- Or apply per-user (overrides account default)
25+
ALTER USER alice SET NETWORK_POLICY = office_and_vpn;
26+
27+
-- Service accounts often need a different policy (CI runner IPs)
28+
CREATE NETWORK POLICY ci_runners
29+
ALLOWED_IP_LIST = ('52.0.0.0/8', '54.0.0.0/8'); -- GitHub-hosted runners; tighten in production
30+
ALTER USER dbt_service SET NETWORK_POLICY = ci_runners;
31+
```
32+
33+
### Key-pair auth for service accounts
34+
35+
Password auth for service accounts is a critical audit finding. Generate a keypair:
36+
37+
```bash
38+
# Generate encrypted private key (recommended)
39+
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out fivetran_loader.p8 -v2 aes-256-cbc
40+
# Extract the public key
41+
openssl rsa -in fivetran_loader.p8 -pubout -out fivetran_loader.pub
42+
# Strip PEM headers for Snowflake ALTER USER
43+
cat fivetran_loader.pub | grep -v -- ----- | tr -d '\n'
44+
```
45+
46+
```sql
47+
ALTER USER fivetran_loader SET RSA_PUBLIC_KEY = '<PUBLIC_KEY_BODY_HERE>';
48+
-- Rotate by setting RSA_PUBLIC_KEY_2, then swapping later
49+
```
50+
51+
### SCIM provisioning (Okta / Azure AD)
52+
53+
```sql
54+
USE ROLE ACCOUNTADMIN;
55+
56+
-- Enable SCIM
57+
CREATE SECURITY INTEGRATION okta_scim
58+
TYPE = SCIM
59+
SCIM_CLIENT = 'OKTA'
60+
RUN_AS_ROLE = 'OKTA_PROVISIONER';
61+
62+
-- Custom role SCIM provisioner runs as (least privilege)
63+
CREATE ROLE OKTA_PROVISIONER;
64+
GRANT CREATE USER, CREATE ROLE ON ACCOUNT TO ROLE OKTA_PROVISIONER;
65+
GRANT ROLE OKTA_PROVISIONER TO ROLE ACCOUNTADMIN;
66+
67+
-- Retrieve SCIM auth token — paste this into Okta's SCIM app config
68+
SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('OKTA_SCIM');
69+
```
70+
71+
### SAML SSO
72+
73+
```sql
74+
CREATE SECURITY INTEGRATION okta_saml
75+
TYPE = SAML2
76+
ENABLED = TRUE
77+
SAML2_ISSUER = 'http://www.okta.com/<okta-issuer-id>'
78+
SAML2_SSO_URL = 'https://<org>.okta.com/app/<app-id>/sso/saml'
79+
SAML2_PROVIDER = 'OKTA'
80+
SAML2_X509_CERT = '<cert-body>'
81+
SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'Okta'
82+
SAML2_ENABLE_SP_INITIATED = TRUE;
83+
84+
-- Force SSO for all human users (block password login)
85+
ALTER ACCOUNT SET SSO_LOGIN_PAGE = TRUE;
86+
```
87+
88+
### MFA enforcement
89+
90+
```sql
91+
-- Enforce Duo MFA for password-auth users
92+
ALTER USER alice SET MINS_TO_BYPASS_MFA = 0;
93+
-- SET DISABLE_MFA = FALSE for all users
94+
```
95+
96+
## Disaster Recovery (Replication + Failover)
97+
98+
### Trigger question
99+
- "Set up cross-region replication for disaster recovery?"
100+
101+
### Replication group (data-only, no automatic failover)
102+
103+
```sql
104+
USE ROLE ACCOUNTADMIN;
105+
106+
-- On source account (primary region)
107+
CREATE REPLICATION GROUP prod_replication
108+
OBJECT_TYPES = DATABASES, SHARES, ROLES, WAREHOUSES, RESOURCE MONITORS, INTEGRATIONS, NETWORK POLICIES
109+
ALLOWED_DATABASES = RAW, TRANSFORM, ANALYTICS
110+
ALLOWED_SHARES = ()
111+
ALLOWED_ACCOUNTS = <org_name>.<secondary_account_locator>
112+
REPLICATION_SCHEDULE = '60 MINUTE';
113+
114+
-- On target account (secondary region)
115+
CREATE REPLICATION GROUP prod_replication_secondary
116+
AS REPLICA OF <org_name>.<primary_account>.prod_replication;
117+
118+
-- Trigger initial replication
119+
ALTER REPLICATION GROUP prod_replication_secondary REFRESH;
120+
```
121+
122+
### Failover group (automatic failover with client redirect)
123+
124+
```sql
125+
-- Failover groups replicate AND enable failover — clients use a Connection URL
126+
CREATE FAILOVER GROUP prod_failover
127+
OBJECT_TYPES = DATABASES, SHARES, ROLES, WAREHOUSES, RESOURCE MONITORS, INTEGRATIONS, NETWORK POLICIES
128+
ALLOWED_DATABASES = RAW, TRANSFORM, ANALYTICS
129+
ALLOWED_ACCOUNTS = <org_name>.<secondary_account>
130+
REPLICATION_SCHEDULE = '15 MINUTE';
131+
132+
-- Client connection URL that survives failover
133+
CREATE CONNECTION prod_connection
134+
AS PRIMARY OF <org_name>.<primary_account>.prod_failover;
135+
136+
-- Manually failover to secondary (or automate via monitoring)
137+
ALTER FAILOVER GROUP prod_failover PRIMARY; -- on secondary account
138+
ALTER CONNECTION prod_connection PRIMARY; -- redirect clients
139+
```
140+
141+
### DR monitoring
142+
143+
```sql
144+
-- Replication lag
145+
SELECT phase, start_time, end_time, DATEDIFF('second', start_time, end_time) AS duration_s,
146+
total_bytes / POWER(1024, 3) AS gb_replicated
147+
FROM TABLE(INFORMATION_SCHEMA.REPLICATION_GROUP_REFRESH_HISTORY('prod_replication'))
148+
ORDER BY start_time DESC LIMIT 20;
149+
150+
-- Estimated RPO (recovery point objective) = max age of last successful refresh
151+
SELECT DATEDIFF('minute', MAX(end_time), CURRENT_TIMESTAMP()) AS minutes_since_last_sync
152+
FROM TABLE(INFORMATION_SCHEMA.REPLICATION_GROUP_REFRESH_HISTORY('prod_replication'))
153+
WHERE phase = 'COMPLETED';
154+
```
155+
156+
## Data Sharing
157+
158+
### Trigger question
159+
- "Share data with external consumers (Snowflake or reader accounts)?"
160+
161+
### Outbound share to another Snowflake account
162+
163+
```sql
164+
USE ROLE ACCOUNTADMIN;
165+
166+
CREATE SHARE finance_metrics_share
167+
COMMENT = 'Monthly finance dashboards for parent-company data team';
168+
169+
-- Grant objects to the share
170+
GRANT USAGE ON DATABASE ANALYTICS TO SHARE finance_metrics_share;
171+
GRANT USAGE ON SCHEMA ANALYTICS.FINANCE TO SHARE finance_metrics_share;
172+
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.FINANCE TO SHARE finance_metrics_share;
173+
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.FINANCE TO SHARE finance_metrics_share;
174+
175+
-- Add consumer accounts (must be in same region + cloud, or cross-region-enabled)
176+
ALTER SHARE finance_metrics_share ADD ACCOUNTS = <consumer_org>.<consumer_account>;
177+
178+
-- Consumer side: consume the share as a read-only database
179+
CREATE DATABASE finance_metrics_from_provider FROM SHARE <provider_org>.<provider_account>.finance_metrics_share;
180+
```
181+
182+
### Reader account (for consumers without Snowflake)
183+
184+
```sql
185+
-- Provider creates the reader account (billed to provider)
186+
CREATE MANAGED ACCOUNT client_x_reader
187+
ADMIN_NAME = client_x_admin
188+
ADMIN_PASSWORD = '<initial-password>'
189+
TYPE = READER
190+
COMMENT = 'Read-only account for Client X';
191+
192+
-- Add the reader account to the share
193+
ALTER SHARE finance_metrics_share ADD ACCOUNTS = <provider_account>.client_x_reader;
194+
```
195+
196+
### Secure views for shared data
197+
198+
Never share raw tables directly — always use secure views to control column exposure.
199+
200+
```sql
201+
CREATE SECURE VIEW ANALYTICS.FINANCE.monthly_revenue_share AS
202+
SELECT
203+
DATE_TRUNC('month', order_date) AS month,
204+
region,
205+
SUM(revenue) AS total_revenue
206+
-- customer_id and email intentionally excluded from share
207+
FROM ANALYTICS.FINANCE.fact_orders
208+
GROUP BY 1, 2;
209+
210+
GRANT SELECT ON VIEW ANALYTICS.FINANCE.monthly_revenue_share TO SHARE finance_metrics_share;
211+
```
212+
213+
## Snowflake Cortex / ML
214+
215+
### Trigger question
216+
- "Enable Snowflake Cortex for LLM functions or Cortex Search?"
217+
218+
### Cortex functions warehouse
219+
220+
```sql
221+
USE ROLE ACCOUNTADMIN;
222+
223+
-- Cortex functions run on a regular warehouse but benefit from Snowpark-optimized instances
224+
CREATE WAREHOUSE CORTEX_WH
225+
WAREHOUSE_SIZE = 'MEDIUM'
226+
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
227+
AUTO_SUSPEND = 60
228+
AUTO_RESUME = TRUE
229+
COMMENT = 'Cortex LLM and ML functions';
230+
231+
-- Grant usage to roles that will call Cortex functions
232+
GRANT USAGE ON WAREHOUSE CORTEX_WH TO ROLE ANALYST_ROLE;
233+
234+
-- The account-level parameter enables Cortex features
235+
-- (already true by default in most regions; check first)
236+
SHOW PARAMETERS LIKE 'CORTEX_ENABLED_CROSS_REGION' IN ACCOUNT;
237+
```
238+
239+
### Cortex Search service (for RAG applications)
240+
241+
```sql
242+
CREATE CORTEX SEARCH SERVICE support_docs_search
243+
ON content
244+
ATTRIBUTES doc_id, title, url, updated_at
245+
WAREHOUSE = CORTEX_WH
246+
TARGET_LAG = '1 hour'
247+
AS (
248+
SELECT doc_id, title, url, content, updated_at
249+
FROM ANALYTICS.SUPPORT.help_articles
250+
);
251+
252+
-- Query the service from an application
253+
SELECT PARSE_JSON(
254+
SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
255+
'ANALYTICS.SUPPORT.support_docs_search',
256+
'{ "query": "how to reset password", "limit": 5 }'
257+
)
258+
):results;
259+
```
260+
261+
### Cortex LLM function grants
262+
263+
```sql
264+
-- Cortex functions live in SNOWFLAKE.CORTEX schema
265+
GRANT USAGE ON DATABASE SNOWFLAKE TO ROLE ANALYST_ROLE;
266+
GRANT USAGE ON SCHEMA SNOWFLAKE.CORTEX TO ROLE ANALYST_ROLE;
267+
-- Functions are already granted USAGE to PUBLIC by default
268+
```
269+
270+
### Cortex usage governance
271+
272+
```sql
273+
-- Track Cortex spend
274+
SELECT
275+
DATE_TRUNC('day', start_time) AS day,
276+
function_name,
277+
SUM(token_credits) AS credits
278+
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
279+
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
280+
GROUP BY 1, 2
281+
ORDER BY day DESC, credits DESC;
282+
```
283+
284+
## Cost Forecasting
285+
286+
### Trigger question
287+
- Automatic when a monthly credit budget is not provided, or user opts in for a tailored budget
288+
289+
### Forecast inputs
290+
291+
The skill collects:
292+
- Expected data volume ingested per day (GB)
293+
- Expected number of dbt models
294+
- Expected concurrent BI users
295+
- Expected ad-hoc analyst sessions per week
296+
297+
### Forecast formulas
298+
299+
```
300+
LOADING_WH credits/month
301+
≈ (daily_gb × 30) / <load_throughput_gb_per_credit>
302+
where load_throughput ≈ 100 GB/credit on XS/S warehouses
303+
304+
TRANSFORM_WH credits/month
305+
≈ (dbt_models × avg_runtime_minutes × runs_per_day × 30) / 60
306+
where avg_runtime_minutes defaults to 2 (SMALL wh, ~10 rows/sec)
307+
308+
ANALYTICS_WH credits/month
309+
≈ (concurrent_users × active_hours_per_day × 30) × warehouse_size_credits_per_hour
310+
where MEDIUM = 4 credits/hour
311+
```
312+
313+
### Budget output
314+
315+
```
316+
Estimated monthly credits (500 GB/day, 200 dbt models, 15 BI users):
317+
LOADING_WH: 150 credits → $450 at $3/credit
318+
TRANSFORM_WH: 200 credits → $600
319+
ANALYTICS_WH: 300 credits → $900
320+
DEV_WH: 50 credits → $150
321+
Buffer (20%): 140 credits → $420
322+
─────────────────────────────
323+
Total budget: 840 credits → $2,520/month
324+
325+
Resource monitor recommendations:
326+
Account-level: 840 credits (100% suspend, 75%/90% notify)
327+
TRANSFORM_WH: 240 credits (20% headroom over forecast)
328+
ANALYTICS_WH: 360 credits
329+
LOADING_WH: 180 credits
330+
```
331+
332+
### Forecast validation
333+
334+
After 30 days, re-run the audit and compare actual to forecast:
335+
336+
```sql
337+
-- Actual vs. forecast (feed forecast values as literals)
338+
SELECT
339+
warehouse_name,
340+
SUM(credits_used) AS actual_credits,
341+
CASE warehouse_name
342+
WHEN 'LOADING_WH' THEN 150
343+
WHEN 'TRANSFORM_WH' THEN 200
344+
WHEN 'ANALYTICS_WH' THEN 300
345+
WHEN 'DEV_WH' THEN 50
346+
END AS forecast_credits,
347+
ROUND(SUM(credits_used) / NULLIF(forecast_credits, 0) * 100, 1) AS pct_of_forecast
348+
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
349+
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
350+
GROUP BY 1
351+
ORDER BY pct_of_forecast DESC;
352+
```
353+
354+
If any warehouse is > 120% of forecast, the skill recommends either a size-up (if queue time is high) or workload investigation via the `query-optimize` and `cost-report` skills.

0 commit comments

Comments
 (0)