What is Snowflake?

Snowflake is the cloud data warehouse that changed the economics and ergonomics of analytical data. Before Snowflake, data warehousing meant Teradata or Oracle racks that cost millions and needed a team of DBAs to tune indexes and manage partitions. Snowflake's bet — separate storage from compute, run on cloud object storage, charge per second of compute used — turned a capital-intensive on-prem product into a utility. A decade later, it's one of the most widely-deployed data platforms on earth.

What makes Snowflake different from "a database in the cloud"

Snowflake's architecture is genuinely different from lift-and-shift warehouses like Redshift's original design. Three independent layers: (1) storage — compressed columnar Parquet-like format sitting in S3/ADLS/GCS, managed by Snowflake, you never touch files; (2) compute — virtual warehouses (XS through 6XL) that spin up in seconds, scale independently, and auto-suspend when idle; (3) services — metadata, security, query optimization, transaction management. The killer consequence: ten teams can run ten workloads on ten warehouses against the same data with zero resource contention and separate billing. Zero-copy cloning means dev environments are free. Time Travel means "undo" for accidental DELETE commands.

Field story

A retail client's Redshift cluster was bursting at 90%+ utilization during nightly ETL, starving BI dashboards and causing 30-minute query queues for analysts. They spent six months trying to optimize — distribution keys, sort keys, VACUUM operations — with marginal results. We migrated to Snowflake with separate warehouses: one LARGE for nightly ETL (8 hours/day), one MEDIUM for BI (auto-suspend after 60s idle), one SMALL for ad-hoc analyst queries. Nightly ETL sped up from 4h to 90min because it stopped fighting for resources; dashboard p95 dropped from 14s to 800ms; total monthly compute cost dropped 40% because of auto-suspend on the analyst warehouse. The migration itself took three months. Snowflake's edge isn't raw query speed — it's workload isolation and operational simplicity, which matter enormously when multiple teams share a warehouse.

Snowflake is a cloud-native data warehouse platform built for the modern data stack. Unlike traditional data warehouses, Snowflake was designed from the ground up for the cloud, offering unique architecture that separates storage and compute resources.

Key Architecture Features

Snowflake uses a multi-cluster shared data architecture that consists of three layers:

1. Database Storage Layer: Data is stored in a compressed, columnar format in cloud object storage (AWS S3, Azure Blob, or Google Cloud Storage). This layer is fully managed and automatically optimized.

1. Query Processing Layer (Virtual Warehouses): Compute clusters that execute queries independent of storage. You can spin up multiple warehouses of different sizes without affecting each other.

1. Cloud Services Layer: Handles authentication, infrastructure management, metadata, query optimization, and access control.

Why Data Engineers Choose Snowflake

• Zero Management: No indexes to tune, no partitions to manage
• Instant Elasticity: Scale compute up/down in seconds
• Concurrency: Multiple workloads without resource contention
• Time Travel: Query historical data up to 90 days back
• Data Sharing: Share live data across organizations securely
• Semi-structured Data: Native support for JSON, Avro, Parquet

Snowflake vs Traditional Data Warehouses

Traditional on-premise solutions like Teradata or Oracle require significant hardware investment and maintenance. Snowflake eliminates this with its SaaS model, offering true pay-per-second pricing and automatic performance optimization.

Common Use Cases

• Data Lakes: Combine structured and semi-structured data
• Data Engineering: Build scalable ETL/ELT pipelines
• Data Science: Run ML workloads with Snowpark
• Business Intelligence: Power dashboards with fast queries

Snowflake pitfalls that cost teams real money and time:

• Warehouses left running overnight. Default auto-suspend is 10 minutes; if someone creates a warehouse with auto-suspend disabled or set to hours, it burns credits 24/7. Always set auto-suspend to 60 seconds and auto-resume=TRUE for interactive warehouses. Audit warehouses monthly.
• Oversized warehouses for the workload. Doubling warehouse size doubles credit burn; it does NOT halve query time unless the workload is CPU-bound and well-parallelized. Many teams run LARGE warehouses for workloads that would complete in the same wall time on SMALL at 1/4 the cost. Right-size based on actual query profiling, not gut feel.
• Unbounded clustering / reclustering. Auto-clustering can re-sort tables constantly if clustering keys are poorly chosen or data ingestion pattern fights the clustering. Can cost thousands/month silently. Monitor AUTOMATIC_CLUSTERING_HISTORY and set up credit alerts.
• Time Travel extended to 90 days on huge tables. Storage for Time Travel includes all changed micro-partitions over the retention window. A 10TB table with frequent updates + 90-day retention can balloon to 50TB of storage. Match retention to actual recovery needs (1-7 days is usually enough for most tables).
• Snowpipe failing silently. Auto-ingest errors (malformed files, permission issues, schema drift) don't fail Snowpipe loudly. Check COPY_HISTORY and set up PIPE_ERRORS monitoring. Teams have lost days of data before noticing.
• Queries scanning full tables. Snowflake prunes micro-partitions via metadata, but only if WHERE clauses match clustering/natural ordering. A query with WHERE UPPER(email)='X' or WHERE cast(ts as date)='2024-01-01' disables pruning; rewrite with direct column predicates.
• Role hierarchy chaos. Without a clean RBAC design (functional roles granted to access roles granted to users), privilege management becomes impossible after 6 months. Adopt a pattern like Snowflake's RBAC best practices or use a tool (Permifrost, dbt permissions) before complexity explodes.
• Result cache misunderstood. Query result cache returns instant results if underlying micro-partitions haven't changed; however, any schema change, insert, or role change can invalidate it. Teams benchmark Snowflake by repeatedly running the same query and mistake cache hits for actual performance.
• Resource monitors set too late. Without credit quotas and warehouse resource monitors, a runaway query or poorly-designed dbt model can burn $10K in an hour. Set hard limits on every warehouse in production from day one.
• Over-reliance on VARIANT for semi-structured data. VARIANT is great for ingesting JSON with unknown schema but costs 2-5x in storage + query time vs flattened columns. For frequently-queried JSON fields, flatten into typed columns at the staging layer.