Snowflake Cost Optimization — Expert Interview Questions

Q: What consumes credits and what is free?

Credits: warehouses (per-second, XS=1/hr to 6XL=512), cloud services (charged if over 10% daily WH compute), serverless (Snowpipe, auto-clustering, MV refresh, SOS, DT refresh, Streaming), Cortex AI (per-token). Storage: $/TB/month. Free: metadata queries, suspended warehouses, result cache hits, data sharing (consumer pays compute).

Q: Walk through a $50K/month cost reduction audit.

Framework: (1) WAREHOUSE_METERING_HISTORY: top-5 warehouses, oversized, auto-suspend configured? (2) QUERY_HISTORY: top-50 most expensive, full table scans, missing clustering? (3) Serverless histories: clustering on rarely-changed tables? (4) Storage: high Time Travel retention, fail-safe on transient tables? (5) Cloud services over 10% threshold? Typical savings: warehouse sizing 20%, auto-suspend 15%, query optimization 10-20%, storage cleanup 5-10%.

Q: Optimal auto-suspend settings for different workloads?

ETL: 60s (clear start/end). BI dashboards: 300s (cache warm). Ad-hoc: 300-600s. Data science: 900-1800s. Never set 0 (wastes credits while idle). Always enable auto-resume.

Q: How do multi-cluster warehouses affect costs?

Auto-scale 1-N clusters on concurrency. STANDARD adds after 20s queue, ECONOMY waits 6 min. Can save: MEDIUM dynamic (4-12 cr/hr) vs always-on XL (16 cr/hr). Can waste: aggressive scaling for brief bursts (60s min billing per cluster). Best: MIN=1, MAX=2-3.

Q: Resource monitors for cost governance?

CREDIT_QUOTA + TRIGGERS: 75% NOTIFY, 90% SUSPEND, 100% SUSPEND_IMMEDIATE. Framework: account-level hard cap, per-team quotas, per-warehouse limits. Tag warehouses by department for chargeback reporting. SUSPEND_IMMEDIATE kills running queries, use only for hard caps.

Q: How to optimize serverless feature costs?

Snowpipe: batch files to 100-250MB. Auto-clustering: only on tables with heavy scan workloads, check AUTOMATIC_CLUSTERING_HISTORY for constant reclustering. Dynamic Tables: use DOWNSTREAM lag, ensure incremental refresh mode. Serverless has no auto-suspend concept, only controls: disable, reduce frequency, resource monitors.

Q: Transient vs permanent tables for cost savings?

Permanent: 7-day Time Travel + 7-day Fail-safe = 14 days historical retention. Transient: 0-1 day TT, NO Fail-safe. 1TB permanent table stores ~14TB historical. Transient with 0-day = ~1TB. Saves ~$300/month per 1TB table at $23/TB. Use transient for staging, ETL intermediates, reproducible data.

How to estimate monthly costs?

Pricing calculator with compute, storage, serverless estimates + 20% buffer. For existing workloads: 1-month POC on pay-as-you-go, then negotiate capacity pricing (25-40% discount).

Is capacity pricing always cheaper?

25-40% per-credit discount but requires annual commitment. Strategy: commit 70-80% of expected usage on capacity, flex remaining 20-30% on-demand.