Snowflake — Confusions, Labs, Gotchas & Mock Interview
💡 Interview Tip
The video-free pack. Read this end-to-end and you can walk into any Snowflake interview without ever opening YouTube.
🧠 Memory Map: WAREHOUSE-STAGE-TIME
Snowflake boils down to 3 mental pillars. Remember WST:
| Letter | Pillar | What it controls |
|---|---|---|
| W | Warehouse (compute) | Speed + cost. Scale UP for big queries, OUT for concurrency. |
| S | Stage (data loading) | How data gets IN. Internal vs External vs Named. |
| T | Time Travel + Storage | How data is kept SAFE. Micro-partitions, zero-copy clones, fail-safe. |
If you can draw these 3 pillars on a whiteboard and connect them, you understand Snowflake.
SECTION 1 — TOP 8 CONFUSIONS CLEARED
Confusion #1 — Scale UP vs Scale OUT (Warehouse sizing vs Multi-cluster)
| Concept | What it does | When to use |
|---|---|---|
| Scale UP (resize: X-Small → Small → Medium → Large → X-Large…) | Gives ONE warehouse more CPU/RAM. Each size DOUBLES credits/hour. | Single slow query, heavy JOIN, big GROUP BY |
| Scale OUT (multi-cluster: min=1, max=10) | Adds MORE warehouses of same size, running in parallel. | 100 users hitting dashboards at 9am (concurrency) |
Credit cost (per hour):
X-Small = 1 credit/hr
Small = 2
Medium = 4
Large = 8
X-Large = 16
2X-Large = 32
3X-Large = 64
4X-Large = 128
Each tier = 2× the previous. A 4X-Large running 1 hour = 128 credits ≈ $256-$512 depending on edition.
Interview one-liner: "Scale UP makes one query faster. Scale OUT lets more users run queries at the same time. Queuing? Scale OUT. Slow query? Scale UP."
Confusion #2 — Internal Stage vs External Stage vs Named Stage vs Table Stage vs User Stage
Five stage types. Memorize this table:
| Stage | Storage location | Created by | Lifetime | Use case |
|---|---|---|---|---|
User stage (@~) | Snowflake-managed | Auto (1 per user) | Forever | Personal file loads |