|
| 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