Snowflake for Data Engineers: Architecture, Virtual Warehouses, Micro-Partitions, Pricing, Editions, Snowflake vs Databricks vs Fabric vs Redshift, and Everything You Need to Know Before Your First Query

Table of Contents

• What Is Snowflake?
• The Three-Layer Architecture
• The Storage Layer — Micro-Partitions and Columnar Storage
• The Compute Layer — Virtual Warehouses
• The Cloud Services Layer — The Brain
• How the Three Layers Work Together
• Snowflake Editions
• The Pricing Model — Credits, Storage, and What Actually Costs Money
• Snowflake vs Databricks
• Snowflake vs Microsoft Fabric
• Snowflake vs Amazon Redshift
• Snowflake vs Google BigQuery
• When to Use Snowflake
• When NOT to Use Snowflake
• Key Snowflake Terminology
• Common Mistakes
• Interview Questions
• Wrapping Up

If you have been working with Azure (ADF, Synapse, Databricks, Fabric) or AWS (Glue, Redshift, EMR), you have seen how cloud data platforms work — but every one of those tools is tied to a single cloud provider. Snowflake takes a completely different approach. It runs identically on AWS, Azure, and GCP with the same SQL, the same features, and the same architecture — but it is not part of any cloud provider. Snowflake is an independent cloud data platform built from scratch for the cloud.

Analogy — A hotel chain vs building your own house. Azure Synapse and AWS Redshift are like building a custom house on a specific plot of land — powerful but permanently tied to that location. Snowflake is like a luxury hotel chain (think Marriott) — the same quality experience whether you check into the New York, London, or Tokyo location. You pick the cloud, Snowflake provides the same platform everywhere. And just like a hotel, you do not manage the plumbing, electricity, or maintenance — you just use the rooms (warehouses) and pay for the nights (credits) you stay.

What Is Snowflake?

Snowflake is a cloud-native data platform that provides data warehousing, data lake, data engineering, data sharing, and AI/ML capabilities — all in a single service with a SQL interface.

Key facts that define Snowflake:

• Cloud-native SaaS — fully managed, no infrastructure to provision, no servers to maintain, no knobs to tune
• Multi-cloud — runs on AWS, Azure, and GCP with identical features on all three
• Separation of storage and compute — you can store 500 TB of data and only pay for compute when queries actually run
• Per-second billing — warehouses charge by the second (60-second minimum), not by the hour
• Near-zero maintenance — no indexing, no vacuuming, no partitioning decisions, no statistics updates
• Founded in 2012 by three data warehousing veterans from Oracle. IPO in September 2020 — the largest software IPO in history at the time. Over 13,900 customers as of 2026

The Snowflake pitch in one sentence:
  "A data warehouse that separates storage from compute, runs on any cloud,
   scales automatically, and charges you only for what you use."

What that means for a data engineer:
  - No cluster sizing decisions (just pick a T-shirt size warehouse)
  - No index management (Snowflake handles optimization automatically)
  - No competition for resources (each team gets their own warehouse)
  - No vendor lock-in (same SQL works on AWS, Azure, and GCP)
  - No infrastructure management (it is fully managed SaaS)

The Three-Layer Architecture

Snowflake’s architecture is what makes it fundamentally different from traditional data warehouses. It separates the platform into three independent layers, and each layer scales independently of the others.

Analogy — A public library system. The storage layer is the central book warehouse — all the books (data) are stored here, organized and compressed. The compute layer is the reading rooms — separate rooms (virtual warehouses) where different groups of people read the books simultaneously without interfering with each other. The cloud services layer is the librarian and catalog system — it manages who can enter, finds the right books for each request, and keeps everything organized. The warehouse does not own any books. The reading rooms can open and close independently. And the librarian works behind the scenes so readers never think about logistics.

Architecture overview:

  Users / Applications
        |
  Cloud Services Layer (authentication, metadata, query optimization, security)
        |
  Compute Layer (virtual warehouses -- independent compute clusters)
        |
  Storage Layer (micro-partitions in cloud object storage -- S3, Azure Blob, GCS)

Key principle: Each layer scales independently.
  - Need more storage? Just load more data (storage scales automatically)
  - Need more compute? Spin up a bigger warehouse or add more warehouses
  - Cloud services? Managed entirely by Snowflake (you never touch it)

The Storage Layer — Micro-Partitions and Columnar Storage

When you load data into Snowflake, it is automatically organized into micro-partitions — small, compressed, columnar storage units of 50-500 MB (uncompressed). You never create partitions manually. Snowflake handles all of it.

Analogy — A filing cabinet that organizes itself. Imagine a filing cabinet where every time you add documents, the cabinet automatically sorts them into small drawers (micro-partitions), compresses them to save space, and creates an index card (metadata) for each drawer listing what is inside. When someone asks for “all invoices from March,” the cabinet instantly knows which drawers to open and which to skip. That is micro-partition pruning — the most important performance feature in Snowflake.

How micro-partitions work:

  Raw data (1 million rows)
    |
  Snowflake automatically splits into micro-partitions
    |
  Micro-partition 1: rows 1-50,000     [dates: Jan 1 - Jan 15]
  Micro-partition 2: rows 50,001-100,000 [dates: Jan 16 - Jan 31]
  Micro-partition 3: rows 100,001-150,000 [dates: Feb 1 - Feb 14]
  ...

  Each micro-partition stores:
    - Data in compressed columnar format (each column stored separately)
    - Metadata: min/max values per column, row count, byte size
    - Stored as immutable files in cloud object storage (S3, Blob, GCS)

  Query: SELECT * FROM orders WHERE order_date = '2026-03-15'
    - Snowflake checks metadata: which partitions have dates near March 15?
    - Skips all partitions where max(order_date) < '2026-03-15'
    - Only reads the 2-3 partitions that contain March 15 data
    - This is "pruning" -- the key to Snowflake query performance

Key characteristics of micro-partitions:

• Immutable — once written, never modified. Updates create new micro-partitions
• Columnar — each column is stored separately, so queries that read 3 of 50 columns only scan 6% of the data
• Compressed — Snowflake automatically compresses data, typically achieving 3-5x compression ratios
• Automatic — you never create, manage, or tune partitions. Snowflake handles everything
• Metadata-rich — every partition stores min/max values per column, enabling partition pruning

Traditional database vs Snowflake:

  Traditional (Oracle, SQL Server):
    - You create indexes manually
    - You define partition schemes
    - You run ANALYZE to update statistics
    - You VACUUM to reclaim space
    - You monitor fragmentation

  Snowflake:
    - No indexes (micro-partition pruning replaces them)
    - No manual partitioning (automatic micro-partitions)
    - No statistics maintenance (metadata is always current)
    - No vacuuming (immutable files, automatic cleanup)
    - No fragmentation (columnar storage, always optimal)

The Compute Layer — Virtual Warehouses

A virtual warehouse is Snowflake’s unit of compute. It is a cluster of cloud compute nodes (EC2 instances on AWS, VMs on Azure, GCE instances on GCP) that Snowflake provisions and manages for you. You choose a size, Snowflake handles everything else.

Analogy — A food truck fleet. Each virtual warehouse is a food truck. A small truck (X-Small) serves one customer at a time. A large truck (Large) has 8 cooks and serves 8 customers simultaneously. You can have multiple trucks (warehouses) operating at the same time — the analytics truck, the ETL truck, the data science truck — and they never compete for ingredients (data) because they all read from the same central kitchen (storage layer). When a truck is not serving, you shut it off and stop paying. When the lunch rush hits, you start more trucks.

Key warehouse concepts:

Auto-suspend and auto-resume:
  - Auto-suspend: warehouse shuts down after X minutes of inactivity (default: 5 min)
  - Auto-resume: warehouse starts automatically when a query arrives
  - Best practice: set auto-suspend to 1-2 minutes for development, 5 minutes for production
  - Per-second billing means you only pay while it is running

Multi-cluster warehouses (Enterprise edition):
  - A single warehouse can scale OUT by adding clusters during high concurrency
  - Example: 10 analysts hit the dashboard at 9 AM
  - Snowflake adds 2-3 clusters automatically, removes them when load drops
  - This is horizontal scaling -- more clusters, not bigger clusters

Isolation:
  - Each warehouse is completely independent
  - The ETL warehouse running a heavy load does NOT slow down the BI warehouse
  - Different teams can have different warehouse sizes for different workloads
  - This is the killer feature for organizations with mixed workloads

Typical warehouse setup for a data team:

  WH_ETL_LOADING    (Medium, auto-suspend: 5 min)   -- Nightly data loads
  WH_TRANSFORM      (Large, auto-suspend: 5 min)    -- dbt transformations
  WH_ANALYTICS      (Small, auto-suspend: 2 min)    -- Analyst ad-hoc queries
  WH_BI_REPORTING   (X-Small, multi-cluster: 1-3)   -- Dashboard queries
  WH_DATA_SCIENCE   (X-Large, auto-suspend: 10 min) -- ML model training
  WH_DEV            (X-Small, auto-suspend: 1 min)  -- Development and testing

  All six warehouses read from the same data -- zero data duplication.
  Each warehouse has its own compute budget -- no resource contention.
  Warehouses that are suspended cost $0.

The Cloud Services Layer — The Brain

The cloud services layer runs on Snowflake’s own infrastructure (not on your cloud account) and handles everything that is not storage or query execution.

What it does:

• Authentication and access control — who can log in, what they can see, RBAC
• Query parsing and optimization — compiles SQL, creates execution plans, decides which micro-partitions to scan
• Metadata management — tracks every table, column, micro-partition, and their statistics
• Transaction management — ACID transactions across all operations
• Infrastructure management — provisions and deprovisions warehouse nodes

Why this layer matters:

  When you run: SELECT * FROM orders WHERE region = 'Ontario'

  The cloud services layer:
    1. Authenticates your session
    2. Checks your permissions on the orders table
    3. Parses the SQL
    4. Looks up metadata: which micro-partitions contain 'Ontario'?
    5. Creates an execution plan that only reads those partitions
    6. Sends the plan to your virtual warehouse for execution
    7. The warehouse scans ONLY the relevant partitions
    8. Results return to you

  Steps 1-6 happen in cloud services (free up to 10% of compute).
  Step 7 happens in the compute layer (charged in credits).
  Step 8 returns results to your client.

  The optimization in step 5 is why Snowflake queries are fast without indexes.

Billing note: Cloud services are free up to 10% of your daily compute usage. If your warehouses use 100 credits in a day, cloud services are free up to 10 credits. Exceeding that threshold is rare and usually indicates excessive metadata operations.

How the Three Layers Work Together

Example: A data engineer runs a daily ETL pipeline

  6:00 AM -- Schedule trigger fires
    Cloud Services: authenticates the service account, resumes WH_ETL_LOADING

  6:00:01 AM -- COPY INTO command runs
    Cloud Services: parses SQL, identifies source files in S3 stage
    Compute: WH_ETL_LOADING reads files, loads into micro-partitions
    Storage: new micro-partitions written to cloud object storage

  6:15 AM -- Transformation queries run
    Cloud Services: optimizes each query, identifies partitions to scan
    Compute: WH_ETL_LOADING executes transformations
    Storage: new micro-partitions created for transformed tables

  6:30 AM -- ETL complete, no more queries
    Cloud Services: after 5 minutes of inactivity, suspends WH_ETL_LOADING
    Compute: warehouse nodes released back to Snowflake (you stop paying)
    Storage: data persists permanently (pennies per TB per month)

  9:00 AM -- Analyst opens a dashboard
    Cloud Services: auto-resumes WH_ANALYTICS
    Compute: WH_ANALYTICS runs dashboard queries (completely separate from ETL)
    Storage: reads the SAME data the ETL pipeline wrote -- zero copies

  Total cost: 30 minutes of Medium warehouse + storage
  Traditional approach: 24/7 server running = 48x more expensive

Snowflake Editions

Snowflake offers four editions, each adding features on top of the previous one:

Which edition to choose: Start with Enterprise for production workloads. It is the sweet spot — multi-cluster warehouses and 90-day Time Travel are essential for real-world data engineering. Use Standard only for development or proof-of-concept.

The Pricing Model — Credits, Storage, and What Actually Costs Money

Snowflake pricing has three components, and understanding them is critical because the most expensive part is not what most people expect.

Analogy — An electricity bill. Storage is like the monthly connection fee — small, flat, and predictable (like $23/TB/month). Compute (credits) is like the electricity meter — it runs only when your appliances (warehouses) are on, and bigger appliances consume more. Cloud services are like the grid maintenance fee — usually free because it is included in your electricity cost (up to 10% of compute). Most people worry about the connection fee (storage) when they should be watching the meter (compute).

Component 1: Compute (the big one -- typically 70-85% of the bill)
  - Charged in Snowflake credits
  - Billed per second with a 60-second minimum per start/resume
  - X-Small warehouse: 1 credit/hour ($2-4/hour depending on edition)
  - Each size up doubles the credits: Small=2, Medium=4, Large=8, XL=16
  - A Medium warehouse running for 15 minutes = 4 credits/hour * 0.25 hours = 1 credit

Component 2: Storage (small and predictable -- typically 10-20% of the bill)
  - ~$23/TB/month on AWS (on-demand), ~$40/TB/month on Azure
  - Snowflake compresses data 3-5x, so 1 TB raw = ~200-300 GB billed
  - Includes Time Travel data and Fail-safe data (7 days automatic backup)
  - Data at rest is always encrypted

Component 3: Cloud services (usually free)
  - Free up to 10% of daily compute usage
  - Rarely exceeds the threshold for normal workloads

Component 4: Data transfer (often overlooked)
  - Loading data INTO Snowflake: FREE
  - Moving data OUT of Snowflake to a different region/cloud: charged per GB
  - Cross-region replication: charged per GB transferred

Real-world cost example:

  Company: mid-size analytics team, 5 TB of data, Enterprise edition on AWS

  Storage:
    5 TB * ~$23/TB/month * 0.3 (compression) = ~$35/month

  Compute:
    WH_ETL (Medium): 30 min/day * 4 credits/hr * 30 days = 60 credits
    WH_ANALYTICS (Small): 4 hrs/day * 2 credits/hr * 22 workdays = 176 credits
    WH_BI (X-Small): 8 hrs/day * 1 credit/hr * 30 days = 240 credits
    Total: 476 credits * $3/credit = $1,428/month

  Total monthly bill: ~$35 (storage) + $1,428 (compute) = ~$1,463/month

  Key insight: storage is 2% of the bill. Compute is 98%.
  Optimization focus should be on warehouse sizing and auto-suspend,
  not on reducing data volume.

Snowflake vs Databricks

This is the most common comparison in data engineering interviews and architecture decisions.

When to choose Snowflake over Databricks:
  - Your team is SQL-first (analysts and analytics engineers)
  - You need zero-copy data sharing with partners or customers
  - You want zero infrastructure management
  - Your workload is primarily analytics, BI, and reporting
  - You use dbt for transformations

When to choose Databricks over Snowflake:
  - Your team is Python/Spark-first (data engineers and data scientists)
  - You have advanced ML workloads (model training, feature stores, model serving)
  - You need fine-grained infrastructure control
  - Your workload involves petabyte-scale complex transformations
  - You want deep open-source ecosystem integration

Many companies use both:
  Snowflake: data warehouse, analytics, BI, data sharing
  Databricks: data engineering, ML, feature engineering, model serving
  Connected via: Iceberg tables, Delta Sharing, or external stages

Snowflake vs Microsoft Fabric

Key difference:
  Snowflake is a best-of-breed data warehouse you plug into any ecosystem.
  Fabric is an all-in-one platform for companies already in the Microsoft world.

  Snowflake + dbt + Fivetran + Tableau = best-of-breed stack
  Fabric = everything-in-one-box (but Azure-only)

Snowflake vs Amazon Redshift

Snowflake vs Google BigQuery

Key pricing difference:
  Snowflake: you pay for TIME (warehouse running)
    - Predictable if you control warehouse schedules
    - Risky if warehouses are left running

  BigQuery: you pay for DATA SCANNED (per query)
    - Cheap for small/infrequent queries
    - Expensive for full-table scans on large tables
    - Completely free when idle (no queries = no cost)

When to Use Snowflake

• Your team is SQL-first — analysts, analytics engineers, and BI developers who primarily write SQL
• You need multi-cloud — your company uses AWS and Azure, or might switch clouds later
• You want zero administration — no indexes, no vacuuming, no partition management, no cluster tuning
• Data sharing is important — you share data with partners, customers, or across business units
• You use a modern data stack — dbt for transformations, Fivetran/Airbyte for ingestion, Tableau/Looker for BI
• Variable workloads — heavy during business hours, quiet at night (per-second billing saves money)
• Concurrency matters — dozens of analysts querying simultaneously without performance degradation

When NOT to Use Snowflake

• Real-time streaming (sub-second latency) — Kafka, Flink, or Spark Structured Streaming are better
• Advanced ML/AI — Databricks or SageMaker provide better ML tooling (model training, feature stores, model serving)
• OLTP workloads — Snowflake is built for analytics (OLAP), not transactional workloads. Use PostgreSQL, MySQL, or DynamoDB for OLTP
• Small, steady workloads — if you run a single dashboard with 5 users, Snowflake credits may cost more than a small dedicated database
• Budget-constrained development — Snowflake has a free trial but no permanent free tier. For learning, BigQuery offers 1 TB/month free

Key Snowflake Terminology

Common Mistakes

1. Leaving warehouses running when not in use. A Medium warehouse left running 24/7 on Enterprise edition costs about $8,640/month. Set auto-suspend to 1-2 minutes for development and 5 minutes for production. This single setting can cut compute costs by 40-60%.

2. Oversizing warehouses “just in case.” A Large warehouse is not always faster than a Small one — it depends on query complexity and data volume. Start with X-Small or Small, test query performance, and only size up if queries are genuinely slow. Each size up doubles your cost.

3. Using one warehouse for everything. When ETL and analytics share the same warehouse, a heavy ETL load makes dashboards slow. Create separate warehouses for different workloads (ETL, analytics, BI, development) so they never compete for resources.

4. Thinking Snowflake replaces Databricks. Snowflake excels at SQL analytics, data sharing, and zero-admin warehousing. Databricks excels at large-scale data engineering, ML, and Python-first workflows. Many companies use both, connected via Iceberg or external stages.

5. Not understanding credit-based pricing. Credits are the compute meter, not a prepaid token. Running a Large warehouse for 1 hour costs 8 credits, regardless of how many queries run. The key cost driver is warehouse time, not query count. Focus optimization on reducing warehouse uptime, not reducing query count.

6. Ignoring micro-partition pruning. If your queries always scan the entire table (no WHERE clause on partition-aligned columns), you are paying for full table scans. Filter on columns that align with how data is naturally clustered (usually date or timestamp columns). The QUERY_PROFILE shows how many partitions were pruned vs scanned.

7. Copying data when you could share it. Snowflake’s zero-copy data sharing lets you share live data across accounts, regions, and clouds without copying. If teams are exporting CSVs or running CTAS to duplicate data between departments, data sharing eliminates that entirely.

8. Choosing Standard edition for production. Standard edition lacks multi-cluster warehouses, 90-day Time Travel, materialized views, and dynamic data masking. Enterprise edition costs ~50% more per credit but provides features that most production workloads need.

Interview Questions

Q: What is Snowflake and how does its architecture differ from traditional data warehouses? A: Snowflake is a cloud-native data platform with a three-layer architecture that separates storage, compute, and cloud services. Traditional warehouses (Oracle, Teradata) couple storage and compute on the same hardware, forcing you to scale both together. Snowflake decouples them, so you can store 500 TB and only pay for compute when queries run. Virtual warehouses are independent compute clusters that can scale up, down, or out without affecting each other. This eliminates resource contention, enables per-second billing, and removes the need for index management, vacuuming, or manual partitioning.

Q: What are micro-partitions and why are they important? A: Micro-partitions are Snowflake’s fundamental storage unit — immutable, compressed, columnar files of 50-500 MB stored in cloud object storage. Snowflake automatically organizes all table data into micro-partitions and tracks metadata (min/max values per column) for each one. This metadata enables partition pruning — when a query has a WHERE clause, Snowflake checks the metadata and skips partitions that cannot contain matching rows. This replaces traditional indexes entirely. Because micro-partitions are immutable, updates and deletes create new partitions rather than modifying existing ones, which simplifies storage management and enables Time Travel.

Q: How does Snowflake billing work? A: Snowflake bills three things separately. Compute is charged in credits — 1 credit equals 1 hour of an X-Small warehouse, and each size up doubles the credits. Billing is per-second with a 60-second minimum per start. Storage is charged per compressed TB per month (about $23/TB on AWS). Cloud services are free up to 10% of daily compute. Compute is typically 70-85% of the total bill. The key optimization lever is warehouse auto-suspend — suspending warehouses when idle can cut costs by 40-60%.

Q: What is the difference between scaling up and scaling out in Snowflake? A: Scaling up means increasing the warehouse size (Small to Medium to Large) — this makes individual queries faster by adding more compute nodes. Scaling out means adding more clusters to a multi-cluster warehouse (Enterprise edition) — this handles more concurrent queries without slowing down. Scale up when queries are slow. Scale out when many users are waiting in queue. Scaling up doubles cost per hour. Scaling out adds clusters only when needed and removes them when demand drops.

Q: How does Snowflake compare to Databricks? A: Snowflake is SQL-first with zero infrastructure management — ideal for analytics, BI, data sharing, and SQL-centric teams. Databricks is Spark-first with full infrastructure control — ideal for large-scale data engineering, ML model training, and Python-first teams. Snowflake’s strengths are zero-copy data sharing, multi-cluster concurrency, and zero-admin operation. Databricks’ strengths are MLflow integration, Delta Lake’s open format, and fine-grained cluster tuning. Many enterprises use both — Snowflake for the data warehouse and BI layer, Databricks for advanced data engineering and ML.

Q: What is Time Travel in Snowflake and how does it work? A: Time Travel lets you query data as it existed at any point in the past, up to 90 days on Enterprise edition (1 day on Standard). You use the AT or BEFORE clause: SELECT * FROM orders AT(TIMESTAMP => '2026-07-15 10:00:00'). This works because Snowflake’s micro-partitions are immutable — when data changes, old partitions are retained for the Time Travel retention period. Time Travel is useful for recovering accidentally deleted data, auditing changes, and creating point-in-time snapshots. It also powers zero-copy clones, which create instant copies of tables without duplicating storage.

Q: What is zero-copy cloning and when would you use it? A: Zero-copy cloning creates an instant, metadata-only copy of a table, schema, or database without duplicating any data. The clone shares the same underlying micro-partitions as the original. Only when data in the clone or original is modified do new, separate micro-partitions get created. Use it for creating development or testing copies of production data (instant, free), point-in-time backups before risky operations, experimenting with schema changes without affecting production, and giving analysts their own copy of a dataset to explore freely.

Wrapping Up

Snowflake’s three-layer architecture — storage, compute, and cloud services — is what makes it fundamentally different from traditional data warehouses. The separation of storage and compute means you can store petabytes while paying for compute only when queries run. Virtual warehouses give different teams independent compute without resource contention. Micro-partitions eliminate the need for manual indexing, partitioning, and vacuuming. And per-second billing means you pay for what you actually use.

In the next post, we will get hands-on: creating a Snowflake account, setting up databases and schemas, configuring virtual warehouses, and building the role-based access control structure that every production Snowflake environment needs.

Related posts: – Microsoft Fabric Overview – Databricks Introduction – Cloud Computing Explained – Data Warehouse vs Database