Data Warehousing
Intermediate 14 min read Data Warehousing

The Snowflake Platform

The engine that made 'just add compute' a feature

Snowflake separates storage and compute, virtual warehouses run queries, micro-partitions enable pruning, TIME TRAVEL recovers mistakes, and cloning gives you instant dev copies.

Snowflakevirtual warehousemicro-partitiontime travelcloneclustering
Prerequisites: Warehouse Architecture
Before we start

What you will learn

  • Explain the role of virtual warehouses in concurrency
  • Describe micro-partition pruning and automatic clustering
  • Recover from mistakes with TIME TRAVEL and cloned dev data
Introduction

What is this?

Snowflake is a SaaS warehouse where tables live in a shared storage layer and queries run on virtual warehouses you size independently. Data is stored in immutable micro-partitions; metadata and automatic clustering prune partitions at query time; TIME TRAVEL and CLONE make mistakes cheap.

Motivation

Why do you need it?

  • Its architecture (shared storage + elastic compute) is the template others copy.
  • Virtual warehouse sizing controls cost and concurrency — a day-one operational lever.
  • Time travel and clones are the sort of features that save production when someone drops a table.
Think about it

Mental model

Imagine

A warehouse with cargo containers you can reopen and clone for free

Think of

Snowflake storage + compute

Cargo sits in a shared yard (storage); you rent extra forklift teams (virtual warehouses) to process an order. Containers come with a log of what changed so a mis-shipment can be undone (time travel).

Mechanics

How it works

  • Storage is immutable micro-partitions (thin slices of a table's rows) with metadata (min/max per column).
  • A virtual warehouse is a cluster of compute nodes; queries route to it and it can spin up/down.
  • The optimizer reads partition pruning based on metadata so a WHERE date='2026-03-01' touches a fraction of a table.
  • TIME TRAVEL keeps versions for a retention window; UNDROP and restore queries read old snapshots.
  • A CLONE is a metadata-level copy of a table/schema/database — instant and storage-cheap until you write.

Query flow against micro-partitions.

Query
Metadata
Pruning
Scan
Result
Reference

Key commands

ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'X-SMALL';

Resize compute (2 credits per XS hour) before heavy jobs.

SELECT * FROM orders AT(OFFSET => -60*60);

Time-travel an hour back in one clause.

SHOW WEAREHOUSES;

Check running virtual warehouses and auto-suspend states.

CREATE TABLE x CLONE y;

Metadata-level clone without copying data.

Hands-on

Examples

Mistake recovery with TIME TRAVEL and CLONE

Mistake recovery with TIME TRAVEL and CLONE
-- Accidental destructive statement? Replay snapshots:
SELECT * FROM orders AT(TIMESTAMP => '2026-03-14 03:59:55') LIMIT 5;
 
-- Rebuild a dropped table cheaply:
CREATE TABLE orders_restored CLONE orders (from earlier);
-- or UNDROP TABLE orders;   -- classic rescue
 
-- Instant dev copy of a full schema:
CREATE SCHEMA dev_data CLONE prod_data;

Snapshots within the retention window make 'rewrite history' reproducible, not nerve-racking.

Real world

In production

  • A nightly ELT sizes a LARGE warehouse for transforms, then suspends it to stop credits.
  • An incident runbook uses AT(TIMESTAMP) to reconstruct a bad row set deleted by a bug.
  • A dev environment clones prod tables for realistic local ad-hoc queries without paying for copy storage.
Guidelines

Best practices

  • Match warehouse size to workload and let auto-suspend stop idle credits.
  • Set TIME_TRAVEL_RETENTION_IN_DAYS at the table level to match RPO.
  • Use CLONE for dev/test instead of re-ingesting or duplicating row data.
  • Rely on micro-partition metadata by choosing rarely-changing predicates as filters (time, natural keys).
Watch out

Common mistakes

  • Leaving warehouses unsuspended and racking credits for idle clusters.
  • Blasting a whole table because a stale predicate defeats pruning.
  • Forgetting that CLONE creates metadata, not a copy — writing to it diverges storage in your account.
When things break

Debugging tips

  • A query touching 'the whole table' despite a date filter = pruning defeated (check the predicate or the cluster key).
  • Stuck queries usually point at one XS warehouse serializing several big jobs; scale horizontally.
  • Slow CLONE writes often mean the clone diverged and now pays full write costs.
Interviews

Interview checkpoint

Practice

Hands-on lab

Explore Warehouse-Like Behavior Locally with DuckDB
30 min · intermediate
In progress
Objectives
  • Experience storage/compute separation and metadata-driven pruning in miniature
  • Demonstrate time-travel style versioning using DuckDB features
  • Run EXPLAIN to see partition/predicate behavior

Explore Warehouse-Like Behavior Locally with DuckDB

30 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

What does a CLONE of a Snowflake table create?

2

TIME TRAVEL lets you query a table as it existed at an earlier timestamp within the retention window.

Go deeper

Official documentation

Snowflake key concepts
docs
Snowflake micro-partitions
docs
TIME TRAVEL & cloning
docs