Snowflake Overview and Interview Atlas

Answer First: Snowflake separates managed storage, elastic compute, and cloud services so independent workloads can share governed data without sharing one compute cluster.

Memory Map: request -> cloud services -> virtual warehouse -> micro-partitions -> governed result.

Version and account guardrails

• The legacy notes are a historical study snapshot. Product names, defaults, preview/GA status, region support, prices, and benchmark multipliers can change; verify the target account, cloud, region, edition, and current release documentation.
• Gen2 standard warehouses are region-dependent, use generation-specific consumption rates, and do not guarantee a fixed speedup. Benchmark the workload and consult the current service-consumption table: official guide.
• Standard Time Travel retention is one day; up to 90 days requires Enterprise Edition and eligible objects. Fail-safe and historical storage behavior also depend on table type: official guide.
• Cloud-services compute is not a flat “10% of the bill.” The daily adjustment applies when eligible cloud-services usage is below 10% of warehouse usage; serverless compute is treated separately: official cost guide.
• Snowflake has organization, account, database, and application roles. The five-role mnemonic in the legacy notes refers only to the core account hierarchy; use custom roles and least privilege: official access-control guide.
• File-based loading uses stages for files consumed by COPY INTO and Snowpipe; Snowpipe Streaming/direct row ingestion uses API channels instead of staged files: official loading overview.
• Current high-performance Snowpipe Streaming guidance describes data becoming queryable within seconds, with latency as low as five seconds—not a blanket sub-second SLA: official guide.
• Dynamic-table TARGET_LAG is a best-effort staleness target, not a refresh interval or guarantee. AUTO is the default refresh mode and resolves at creation; production designs should inspect or set the resolved mode explicitly: target-lag guide, refresh-mode guide.
• COPY INTO uses per-table file-load metadata to prevent recent duplicate loads, but that metadata expires after 64 days. FORCE can duplicate rows; use durable source identities and explicit replay controls: official loading guide.
• Do not assume serverless tasks are a fixed percentage cheaper. Compare actual serverless-task metering with a representative user-managed warehouse run: official task guide.
• Feature availability is account- and region-sensitive. For example, Snowflake Postgres became generally available on February 24, 2026, but the release note limits it to selected AWS and Azure regions: official release note.

Canonical module route

• Architecture and core concepts
• Loading, pipelines, and performance
• Security, sharing, and cost
• Scenarios, labs, and production traps
• Link-only interview question index

Answer First: The three-day plan moves from architecture and storage/compute separation to ingestion and performance, then closes with security, sharing, cost, and current-feature guardrails.

Memory Map: 3-Day Snowflake Interview Prep -> architecture establishes execution model -> pipelines establish data movement -> governance controls access and spend [SF_00_INTERVIEW_PLAN.md:5].

3-Day Snowflake Interview Prep

WHY SNOWFLAKE MATTERS FOR YOUR INTERVIEW

• Many JDs mention "BigQuery" and cloud data warehouses — Snowflake is the #1 cloud DW
• Interviewers often ask: "Have you worked with Snowflake? How does it compare to Databricks?"
• Even if your target company uses Databricks, knowing Snowflake shows breadth of data platform knowledge
• Many companies use BOTH — Databricks for ETL/ML + Snowflake for analytics/BI

3-DAY SCHEDULE

PRIORITY MATRIX

MUST KNOW (Will definitely be asked — 60%)

1. Snowflake 3-layer architecture (storage, compute, cloud services)
2. Micro-partitions & clustering keys
3. Virtual warehouse sizing & multi-cluster warehouses
4. Time Travel & cloning (zero-copy clone)
5. Snowpipe & COPY INTO — data loading patterns
6. Semi-structured data (VARIANT, FLATTEN)
7. Snowflake vs Databricks — the #1 comparison question

SHOULD KNOW (High probability — 25%)

1. Streams & Tasks (CDC + scheduling)
2. Dynamic Tables (declarative managed refresh for eligible transformations)
3. Caching (3 levels — result, local disk, remote disk)
4. RBAC & role hierarchy (ACCOUNTADMIN, SYSADMIN, etc.)
5. Data masking & row access policies
6. Secure Data Sharing
7. Cost management & resource monitors

NICE TO KNOW (Differentiators — 15%)

1. Snowpark (Python DataFrame API on Snowflake)
2. Cortex AI (LLM functions in SQL)
3. Iceberg Tables & Polaris Catalog
4. Snowpark Container Services
5. Gen2 Warehouses (benchmark workload-specific performance and cost)
6. Unistore / Hybrid Tables (OLTP on Snowflake)
7. Native dbt integration

Each main section has:

• 15-20 questions at all 3 levels (direct, mid-level, scenario-based)
• Simple explanations with real-world analogies
• Line-by-line commented code/SQL
• Interview tips for each topic

Each Quick Recall File will have:

• 🧠 Memory Maps (mnemonics, acronyms)
• ⚡ Direct questions (one-liner flash cards)
• 🔑 Mid-level questions (how/why/compare)
• ⚠️ Common traps
• Summary card for last-minute revision

LEARNING APPROACH

Same as Databricks prep:

Answer First: Snowflake is SQL- and warehouse-oriented while Databricks is strongest for open lakehouse engineering and ML; choose by workload, governance, interoperability, and operating model.

Memory Map: SNOWFLAKE vs DATABRICKS — Quick Reference -> SQL analytics favors managed warehouses -> engineering and ML favor open lakehouse compute -> shared governance decides coexistence [SF_00_INTERVIEW_PLAN.md:125].

SNOWFLAKE vs DATABRICKS — Quick Reference

┌──────────────────┬─────────────────────┬─────────────────────┐
│ Aspect           │ Snowflake           │ Databricks          │
├──────────────────┼─────────────────────┼─────────────────────┤
│ Core strength    │ Data Warehousing    │ Data Engineering/ML │
│ Language         │ SQL-first           │ Python/Scala-first  │
│ Storage format   │ Proprietary (micro) │ Delta Lake (open)   │
│ Compute          │ Virtual Warehouses  │ Spark Clusters      │
│ Semi-structured  │ VARIANT (native)    │ JSON in Delta cols  │
│ Data sharing     │ Secure Sharing      │ Delta Sharing       │
│ ML/AI            │ Cortex AI, Snowpark │ MLflow, MLlib       │
│ CDC              │ Streams             │ CDF (Change Feed)   │
│ ELT framework    │ Dynamic Tables      │ Lakeflow (DLT)      │
│ File format      │ Proprietary         │ Open (Parquet/Delta)│
│ Governance       │ Horizon Catalog     │ Unity Catalog       │
│ Pricing          │ Per-second credits  │ Per-second DBUs     │
│ Best for         │ SQL analytics/BI    │ Complex ETL/ML      │
└──────────────────┴─────────────────────┴─────────────────────┘

HOW TO USE

1. Read SF_01/02/03 main files for deep understanding (questions + explanations + code)
2. Read SF_01/02/03_Quick_Recall for memory maps and flash-card style review
3. For last-minute: Read only Quick Recall Summary Cards (10 min per day)
4. Always connect Snowflake to your Databricks knowledge: "I've used Databricks for X, and I know Snowflake handles this with Y"
5. Frame with real-world context: "In a travel data warehouse with billions of booking records..."

Snowflake Architecture and Core Concepts

Answer First: Reason about Snowflake from the request path: cloud services plan it, a virtual warehouse executes it, and micro-partition metadata limits the storage scanned.

Memory Map: services -> warehouse -> pruning -> cache -> history -> semi-structured data.

Day 1: Snowflake Architecture & Core Concepts

SECTION 1: SNOWFLAKE ARCHITECTURE (1.5 hours)

Answer First: Snowflake is a managed cloud data platform whose services layer plans queries, virtual warehouses execute them, and centralized columnar storage persists micro-partitions; storage and compute scale independently.

Memory Map: What is Snowflake? Explain its architecture. -> cloud services compile and authorize -> warehouses execute operators independently -> object storage retains shared micro-partitions [SF_01_Architecture_Core.md:15].

Q1: What is Snowflake? Explain its architecture.

Simple Explanation: Snowflake is a cloud data warehouse — a platform where you store and query massive amounts of data using SQL. Unlike traditional databases (Oracle, SQL Server), Snowflake was built from scratch for the cloud. Its biggest innovation: separate storage from compute — the disk where data lives and the computers that process it are completely independent.

Real-world analogy: Think of a library (storage) and reading desks (compute). In a traditional database, reading desks are built INTO the library — only 10 desks, and they're always there (wasting space when empty). Snowflake is like: the library is in one building, and you can bring in 1 desk or 1000 desks as needed. When no one's reading, send all desks home (auto-suspend). The books (data) are always there regardless.

Why do we need it?

• Traditional databases: scale storage = must scale compute too (expensive, wasteful)
• Snowflake: scale each independently — run 10 warehouses on the same data simultaneously

The 3-Layer Architecture:

┌──────────────────────────────────────────────────────────────────┐
│                    LAYER 3: CLOUD SERVICES                        │
│   The "brain" — always running, managed by Snowflake             │
│   ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────┐   │
│   │ Query    │  │ Metadata │  │ Auth &   │  │ Optimizer    │   │
│   │ Compiler │  │ Manager  │  │ Security │  │ & Planner    │   │
│   └──────────┘  └──────────┘  └──────────┘  └──────────────┘   │
│   • Parses SQL → builds execution plan                           │
│   • Manages metadata (table schemas, clustering info)            │
│   • Handles login, RBAC, encryption                             │
│   • Optimizes queries (pruning, caching decisions)              │
└───────────────────────────┬──────────────────────────────────────┘
                            │
┌───────────────────────────▼──────────────────────────────────────┐
│                  LAYER 2: COMPUTE (Virtual Warehouses)            │
│   The "muscles" — you turn them on when needed                   │
│                                                                  │
│   Warehouse A (XS)    Warehouse B (L)    Warehouse C (XL)        │
│   [ETL pipelines]     [BI dashboards]    [ML training]           │
│        ↕                    ↕                   ↕               │
│   Reads from storage  Reads from storage  Reads from storage     │
│                                                                  │
│   • Independent from each other — no resource contention        │
│   • Auto-suspend (stop billing when idle)                       │
│   • Auto-resume (start automatically when query arrives)        │
└───────────────────────────┬──────────────────────────────────────┘
                            │
┌───────────────────────────▼──────────────────────────────────────┐
│                    LAYER 1: STORAGE                               │
│   The "filing cabinet" — always on, centralized                  │
│                                                                  │
│   Data stored in Snowflake's proprietary compressed format       │
│   (micro-partitions — columnar, compressed Parquet-like files)  │
│   On AWS S3 / Azure ADLS Gen2 / Google Cloud Storage            │
│                                                                  │
│   • Charged separately from compute (per TB/month)              │
│   • Stores table data + metadata + Time Travel versions         │
│   • Encrypted at rest (AES-256)                                 │
└──────────────────────────────────────────────────────────────────┘

Key properties:

• Storage: Compressed columnar files in cloud object storage (S3/ADLS/GCS)
• Compute: Virtual warehouses — separate clusters, independent scaling
• Cloud Services: Query compilation, metadata, security — managed by Snowflake; cloud-services billing is not a flat percentage of cost

Interview tip: "Snowflake's key differentiator is true separation of storage and compute. Multiple virtual warehouses can query the same data simultaneously without competing for resources. That's impossible in traditional MPP databases like Redshift or Synapse."

Answer First: All data in Snowflake is automatically split into small pieces called micro-partitions. You don't define them — Snowflake creates them automatically as data is loaded. Each micro-partition is like a small drawer in a filing cabinet, containing 50-500 MB of compressed data (stored in columnar format).

Memory Map: What are Micro-Partitions? How are they different from Hive partitions? -> hidden columnar chunks -> min max zone maps -> pruning decision -> bytes skipped metric -> recluster signal [SF_01_Architecture_Core.md:79].

Q2: What are Micro-Partitions? How are they different from Hive partitions?

Simple Explanation: All data in Snowflake is automatically split into small pieces called micro-partitions. You don't define them — Snowflake creates them automatically as data is loaded. Each micro-partition is like a small drawer in a filing cabinet, containing 50-500 MB of compressed data (stored in columnar format).

Real-world analogy: Imagine a huge bookshelf of flight booking records. Instead of one massive file, Snowflake automatically splits records into drawers of roughly equal size. On the label of each drawer: "min date = Jan 1, max date = Jan 15, airlines: AI, LH, EK". When you ask for March bookings, Snowflake reads only the labels and skips drawers that can't possibly contain March data.

Why do we need them? Without micro-partitions: read the ENTIRE dataset even for one day's data. With micro-partitions: Snowflake reads labels (metadata) first, skips irrelevant micro-partitions → only reads 1% of data for a filtered query.

Technical details:

-- Query: Get all Lufthansa bookings in March 2026
SELECT * FROM bookings
WHERE booking_date BETWEEN '2026-03-01' AND '2026-03-31'
  AND airline_code = 'LH';

-- What Snowflake does (PARTITION PRUNING):
-- 1. Reads metadata of ALL micro-partitions (from Cloud Services layer — fast!)
-- 2. Skips micro-partitions where max_date < March 1 OR min_date > March 31
-- 3. Of remaining, skips where 'LH' not in airlines list
-- 4. Reads ONLY relevant micro-partitions
-- Result: Instead of reading 10B rows, reads maybe 50M rows → 200x faster!

Micro-partitions vs Hive/Databricks partitions:

⚠️ Key Interview Point: In Snowflake, you do NOT manually partition tables like in Hive. Micro-partitions are automatic. You may define clustering keys to optimize micro-partition layout — but this is separate from partitioning.

Interview tip: "Unlike Hive where you define partition columns and manage folder structure yourself, Snowflake automatically creates micro-partitions for every table. When data layout degrades over time (high overlap), I define a clustering key which triggers automatic reclustering — no manual maintenance needed."

Answer First: When data is inserted into Snowflake over time, micro-partitions can become mixed up — a partition might contain data from January, March, and July all mixed together. When you query for March data, Snowflake has to read ALL partitions. This is called high overlap or poor clustering.

Memory Map: What are Clustering Keys? When should you use them? -> access pattern columns -> overlap depth -> automatic clustering service -> maintenance credits -> profile proof [SF_01_Architecture_Core.md:142].

Q3: What are Clustering Keys? When should you use them?

Simple Explanation: When data is inserted into Snowflake over time, micro-partitions can become mixed up — a partition might contain data from January, March, and July all mixed together. When you query for March data, Snowflake has to read ALL partitions. This is called high overlap or poor clustering.

A clustering key tells Snowflake: "When organizing micro-partitions, try to group rows with similar values for this column together." Snowflake then automatically re-clusters the table in the background.

Real-world analogy: Your filing cabinet started organized by date (Jan → Feb → March). Then you added 1 million new records randomly, and now each drawer has records from all months mixed in. Clustering key = hiring an assistant to re-sort and re-organize the drawers back to chronological order.

-- Create a table WITH clustering key (good for large tables queried by date)
CREATE TABLE bookings (
    booking_id      VARCHAR,
    booking_date    DATE,
    airline_code    VARCHAR,
    passenger_id    VARCHAR,
    amount          NUMBER
)
CLUSTER BY (booking_date, airline_code);
-- This tells Snowflake: "Group micro-partitions by booking_date first, then airline_code"
-- Snowflake will keep data with same booking_date in the same or nearby micro-partitions

-- Check clustering health of a table
SELECT SYSTEM$CLUSTERING_INFORMATION('bookings', '(booking_date, airline_code)');
-- Returns: average_depth (lower = better), average_overlaps (lower = better)

-- Manually trigger reclustering (usually not needed — it's automatic)
ALTER TABLE bookings RECLUSTER;

When to use clustering keys:

Interview tip: "For our travel-platform's booking table with 10 billion rows, I'd cluster by (booking_date, airline_code) since 90% of queries filter by date range and specific airline. But first I'd check SYSTEM$CLUSTERING_INFORMATION to confirm the current overlap is actually high — clustering costs credits, so don't add it unless needed."

Answer First: A Virtual Warehouse is the compute in Snowflake — a cluster of cloud VMs (servers) that execute your queries. You size it (XS, S, M, L, XL, 2XL, 3XL, 4XL), it auto-starts when a query arrives, runs the query, and auto-suspends when idle. You pay only when it's running.

Memory Map: What is a Virtual Warehouse? Explain sizing and multi-cluster. -> compute cluster boundary -> size for spill -> scale out for queues -> suspend cache tradeoff -> metering check [SF_01_Architecture_Core.md:192].

Q4: What is a Virtual Warehouse? Explain sizing and multi-cluster.

Simple Explanation: A Virtual Warehouse is the compute in Snowflake — a cluster of cloud VMs (servers) that execute your queries. You size it (XS, S, M, L, XL, 2XL, 3XL, 4XL), it auto-starts when a query arrives, runs the query, and auto-suspends when idle. You pay only when it's running.

Real-world analogy: A virtual warehouse is like a team of analysts in an office. XS = 1 analyst, XL = 32 analysts. They all work on the same data files (the library). When no queries come in, they go home (auto-suspend). When a query arrives, they come back (auto-resume). You pay only for the hours they work.

Sizing:

Multi-Cluster Warehouse:

Current defaults matter: MIN_CLUSTER_COUNT = 1 and MAX_CLUSTER_COUNT = 1 produce a single-cluster warehouse. Setting MIN_CLUSTER_COUNT less than MAX_CLUSTER_COUNT enables auto-scale mode; a maximum above 1 requires Enterprise Edition or higher. See CREATE WAREHOUSE.

-- Create a multi-cluster warehouse for BI dashboards
-- (handles many users running queries simultaneously)
CREATE WAREHOUSE bi_dashboard_wh
    WAREHOUSE_SIZE = 'MEDIUM'
    MIN_CLUSTER_COUNT = 1     -- At minimum, 1 cluster running
    MAX_CLUSTER_COUNT = 5     -- Scale up to 5 clusters during peak load
    SCALING_POLICY = 'STANDARD';  -- Add clusters when there's queuing
-- 'ECONOMY' policy = add cluster only when existing cluster is fully busy for 6 min

-- When 50 BI users hit the dashboard at 9 AM:
-- Cluster 1 handles users 1-10
-- Cluster 2 auto-added for users 11-20  (scale-out!)
-- Cluster 3 auto-added for users 21-30
-- etc.
-- At 3 PM, usage drops → clusters 2-5 auto-removed

-- Auto-suspend (stop billing after 5 minutes idle)
ALTER WAREHOUSE bi_dashboard_wh SET AUTO_SUSPEND = 300;  -- 300 seconds = 5 minutes

-- Auto-resume (start automatically when query arrives)
ALTER WAREHOUSE bi_dashboard_wh SET AUTO_RESUME = TRUE;

Scaling UP vs Scaling OUT:

Interview tip: "For the company's BI dashboards with 200+ analysts, I'd use a multi-cluster warehouse (MIN=1, MAX=5) with ECONOMY scaling. For overnight ETL jobs, I'd use a single Large or XL warehouse since it's one complex pipeline, not many concurrent users. Different workloads → different warehouses."

Answer First: Snowflake has 3 types of cache — each one faster than the previous, and free. When Snowflake runs your query, it checks cache first at each level before doing actual work.

Memory Map: Explain the 3 levels of Caching in Snowflake. -> persisted result reuse -> warehouse local disk -> remote storage cache -> invalidation triggers -> query history evidence [SF_01_Architecture_Core.md:255].

Q5: Explain the 3 levels of Caching in Snowflake.

Simple Explanation: Snowflake has 3 types of cache — each one faster than the previous, and free. When Snowflake runs your query, it checks cache first at each level before doing actual work.

Real-world analogy:

• Level 1 (Result Cache): Your professor asks the same exam question twice. You just say the same answer — no need to re-think.
• Level 2 (Local Disk Cache): You already have the book open at the right page — read from there.
• Level 3 (Remote Disk Cache): You know which shelf the book is on — much faster than searching the whole library.

Technical details:

⚠️ Common trap: "If you suspend and resume a warehouse, the Level 2 local cache is CLEARED. For dashboards with repeated queries, keep the warehouse running with a longer auto-suspend (15-30 min) so the local cache stays warm."

Answer First: Fail-safe = an emergency recovery window AFTER time travel ends. Only Snowflake support can use it.

Memory Map: What is Time Travel? What is Fail-safe? -> retention window -> historical query -> undrop recovery -> fail-safe support path -> storage tradeoff [SF_01_Architecture_Core.md:319].

Q6: What is Time Travel? What is Fail-safe?

Simple Explanation: Time Travel = ability to query your data as it was in the past (up to 90 days). "Show me the bookings table as it was yesterday at 3 PM." Fail-safe = an emergency recovery window AFTER time travel ends. Only Snowflake support can use it.

Real-world analogy:

• Time Travel = a "rewind" button. You can rewind your table to any point in the past (within the retention window) and read it.
• Fail-safe = the data recovery team at your cloud provider. After your rewind window closes, they still have the data locked away for 7 more days (but only THEY can access it for disaster recovery).

-- TIME TRAVEL EXAMPLES:

-- Query as of exact timestamp (useful for debugging)
SELECT * FROM bookings
AT (TIMESTAMP => '2026-03-20 09:00:00'::TIMESTAMP_LTZ);

-- Query as of N minutes ago
SELECT * FROM bookings AT (OFFSET => -60 * 30);  -- 30 minutes ago

-- Query as of a specific statement ID (query ID)
SELECT * FROM bookings
BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

-- Restore an accidentally dropped table
UNDROP TABLE bookings;  -- Works within time travel window

-- Restore to a previous version (overwrite current with past version)
CREATE OR REPLACE TABLE bookings CLONE bookings
  AT (TIMESTAMP => '2026-03-24 00:00:00'::TIMESTAMP_LTZ);

-- See what changed between two points in time (audit)
SELECT a.booking_id, a.amount as old_amount, b.amount as new_amount
FROM bookings AT (TIMESTAMP => '2026-03-24 00:00:00') a
JOIN bookings AT (TIMESTAMP => '2026-03-25 00:00:00') b
ON a.booking_id = b.booking_id
WHERE a.amount != b.amount;  -- Find what changed!

Time Travel vs Fail-safe:

┌────────────────────────────────────────────────────────────────┐
│  Timeline of a table after DELETION:                           │
│                                                                │
│  [Data Deleted]─────────────────────────────────────────────→ │
│       │                                                        │
│       ├── 0-90 days: TIME TRAVEL (YOU can access)             │
│       │   Default: 1 day (free plan), up to 90 days (paid)    │
│       │   You can: UNDROP, query old data, clone old version  │
│       │                                                        │
│       └── 90-97 days: FAIL-SAFE (SNOWFLAKE SUPPORT only)      │
│           7 days fixed — you cannot access directly           │
│           Only for catastrophic disasters (entire account wipe)│
└────────────────────────────────────────────────────────────────┘

SET TIME TRAVEL on a table:
ALTER TABLE bookings SET DATA_RETENTION_TIME_IN_DAYS = 30;
-- 30 days for production tables (costs more storage)
-- 0 days for staging/temp tables (saves storage)

Interview tip: "For travel booking tables, I'd set time travel to 30 days for production tables — covers month-end reporting cycles. For temp/staging tables, I'd set it to 0 to save storage costs. And I'd document that fail-safe exists but only Snowflake support can use it — it's NOT a self-service feature."

Answer First: Zero-Copy Clone = instantly create an exact copy of a table, schema, or database — without copying ANY data. The clone points to the same micro-partitions as the original. Storage is only used when data in the clone DIVERGES from the original.

Memory Map: What is Zero-Copy Cloning? -> metadata pointer copy -> shared partitions -> copy-on-write delta -> retention dependency -> dev isolation [SF_01_Architecture_Core.md:385].

Q7: What is Zero-Copy Cloning?

Simple Explanation: Zero-Copy Clone = instantly create an exact copy of a table, schema, or database — without copying ANY data. The clone points to the same micro-partitions as the original. Storage is only used when data in the clone DIVERGES from the original.

Real-world analogy: Imagine making a "copy" of a 10 TB hard drive, but instead of actually copying 10 TB of data, you just create a new label that says "copy of drive A". When someone writes new data to the copy, THAT new data is stored separately. But unchanged data is shared — no extra storage.

-- Clone a table (instant, no data copied!)
CREATE TABLE bookings_dev CLONE bookings;
-- Creates bookings_dev that shares ALL micro-partitions with bookings
-- Takes < 1 second regardless of table size (10 GB or 10PB)!

-- Clone a schema (all tables in the schema, instantly)
CREATE SCHEMA analytics_dev CLONE analytics_prod;

-- Clone a database (all schemas + tables, instantly!)
CREATE DATABASE prod_backup CLONE prod_db;

-- Clone at a specific point in time (combine with time travel)
CREATE TABLE bookings_march_snapshot CLONE bookings
    AT (TIMESTAMP => '2026-03-01 00:00:00'::TIMESTAMP_LTZ);
-- Creates a copy as it was on March 1 — perfect for month-end snapshots

When to use:

• Dev/test environments: Clone prod database for developers — no data copying, instant
• Data experiments: Clone table before a risky transformation — easy rollback
• Monthly snapshots: Clone at month-end for point-in-time reporting
• Parallel pipelines: Two teams working on different transformations of same data

⚠️ Common trap: "Zero-copy means zero INITIAL copy. But over time, as data in the clone changes, it diverges and uses its own storage for the changed micro-partitions. If you write a lot to the clone, storage cost grows."

Answer First: VARIANT is Snowflake's special data type for storing ANY semi-structured data — JSON, Avro, Parquet, ORC, XML — in a single column. Instead of flattening JSON into separate columns before loading (which is hard), you load it as-is into a VARIANT column and query it with dot notation.

Memory Map: What is VARIANT? How does Snowflake handle semi-structured data? -> raw JSON landing -> dot path extraction -> lateral FLATTEN arrays -> cast typed columns -> null semantics check [SF_01_Architecture_Core.md:420].

Q8: What is VARIANT? How does Snowflake handle semi-structured data?

Simple Explanation: VARIANT is Snowflake's special data type for storing ANY semi-structured data — JSON, Avro, Parquet, ORC, XML — in a single column. Instead of flattening JSON into separate columns before loading (which is hard), you load it as-is into a VARIANT column and query it with dot notation.

Real-world analogy: VARIANT is like a "whatever" drawer. Instead of sorting everything before putting it in the drawer (JSON key1 → column1, key2 → column2), you just throw the whole JSON document in. Then when you need something, you reach in and say "give me the .passenger.email" and Snowflake finds it.

-- Create table with VARIANT column (for raw JSON data)
CREATE TABLE raw_bookings (
    load_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    source      VARCHAR(50),
    raw_data    VARIANT    -- Can hold ANY JSON/Avro/Parquet structure
);

-- Load JSON data into VARIANT column
COPY INTO raw_bookings (source, raw_data)
FROM (
    SELECT 'booking_api', PARSE_JSON($1)   -- $1 = first column from file
    FROM @booking_stage/bookings.json
);

-- Query VARIANT data with dot notation (: or . both work)
SELECT
    raw_data:booking_id::VARCHAR           AS booking_id,
    -- ::VARCHAR casts the JSON value to the right SQL type
    raw_data:passenger.first_name::VARCHAR AS passenger_name,
    -- Nested object: passenger.first_name
    raw_data:segments[0]:origin::VARCHAR   AS departure_airport,
    -- Array access: segments[0] = first segment
    raw_data:segments[0]:destination::VARCHAR AS arrival_airport,
    raw_data:fare.amount::NUMBER(10,2)     AS fare_amount
FROM raw_bookings
WHERE raw_data:status::VARCHAR = 'CONFIRMED';
-- Filter inside VARIANT with dot notation + cast

-- FLATTEN: Convert array inside JSON into rows
SELECT
    b.raw_data:booking_id::VARCHAR   AS booking_id,
    f.value:origin::VARCHAR          AS segment_origin,
    f.value:destination::VARCHAR     AS segment_destination,
    f.index                          AS segment_number
FROM raw_bookings b,
LATERAL FLATTEN(input => b.raw_data:segments) f;
-- LATERAL FLATTEN expands the "segments" array into separate rows
-- Each segment becomes its own row with booking_id repeated

-- Result:
-- booking_id  segment_origin  segment_destination  segment_number
-- BK001       DEL             DXB                  0
-- BK001       DXB             LHR                  1
-- BK002       BOM             SIN                  0

New in 2025-2026: Structured ARRAY, OBJECT, MAP types

-- Instead of untyped VARIANT, Snowflake now supports typed structured types
-- These give better performance and compile-time schema checking

CREATE TABLE bookings_v2 (
    booking_id  VARCHAR,
    segments    ARRAY(OBJECT(origin VARCHAR, destination VARCHAR, duration INT)),
    -- Typed array of objects — Snowflake validates schema at insert time!
    tags        MAP(VARCHAR, VARCHAR)
    -- Key-value pairs with typed keys and values
);
-- Note: VARIANT still works and is more flexible. Typed arrays/objects are faster.

Interview tip: "I'd load raw JSON into VARIANT at Bronze layer — no schema definition needed. Then at Silver layer, use FLATTEN and dot notation to extract and cast into proper typed columns. This way Bronze handles any schema change gracefully, and Silver catches quality issues."

Answer First: A Stage in Snowflake is a pointer to a location where files live before or after file-based loading. Think of it as a "loading dock" — staged files sit in the dock, then COPY INTO or Snowpipe loads them into Snowflake tables; Snowpipe Streaming and direct row ingestion use API channels instead of staged files.

Memory Map: What are Stages? Internal vs External? -> file location pointer -> internal or external storage -> credential boundary -> COPY/Snowpipe consumer -> streaming exception [SF_01_Architecture_Core.md:493].

Q9: What are Stages? Internal vs External?

Simple Explanation: A Stage in Snowflake is a pointer to a location where files live before or after file-based loading. Think of it as a "loading dock" — staged files sit in the dock, then COPY INTO or Snowpipe loads them into Snowflake tables. API-based row ingestion such as Snowpipe Streaming uses channels instead of staged files.

Real-world analogy: A stage is like the receiving dock of a warehouse. Trucks (data sources) drop files at the dock (stage). The warehouse workers (COPY INTO) pick up files from the dock and put them in the shelves (tables). The dock can be Snowflake's own parking lot (internal stage) or an external parking lot you own (external stage like ADLS/S3).

-- ══════════════════════════════════════════════════════
-- TYPE 1: USER STAGE (per-user, automatic, always exists)
-- ══════════════════════════════════════════════════════
-- Each user gets their own private stage: @~
PUT file:///local/bookings.csv @~;       -- Upload local file to your user stage
LIST @~;                                 -- See your files
COPY INTO bookings FROM @~/bookings.csv; -- Load into table

-- ══════════════════════════════════════════════════════
-- TYPE 2: TABLE STAGE (per-table, automatic, always exists)
-- ══════════════════════════════════════════════════════
-- Every table has a built-in stage: @%table_name
PUT file:///local/bookings.csv @%bookings;    -- Upload to bookings table's stage
COPY INTO bookings FROM @%bookings;           -- Load from table's stage

-- ══════════════════════════════════════════════════════
-- TYPE 3: NAMED STAGE (create your own — most flexible)
-- ══════════════════════════════════════════════════════

-- Internal Named Stage (files stored IN Snowflake's storage)
CREATE STAGE internal_booking_stage
    COMMENT = 'Internal stage for booking files';

PUT file:///local/bookings_*.csv @internal_booking_stage;  -- Upload CSV files
COPY INTO bookings FROM @internal_booking_stage PATTERN='.*bookings.*\\.csv';

-- External Named Stage (files in YOUR Azure ADLS / S3 / GCS)
CREATE STAGE azure_booking_stage
    URL = 'azure://mystorageaccount.blob.core.windows.net/bookings-container/landing/'
    CREDENTIALS = (AZURE_SAS_TOKEN = 'sv=2020-08...')
    FILE_FORMAT = (TYPE = 'PARQUET');
    -- Points to ADLS container where your pipeline drops files

COPY INTO bookings FROM @azure_booking_stage;  -- Load from ADLS

Internal vs External Stage comparison:

Answer First: Standard Basic features. Enterprise Time Travel up to 90 days, multi-cluster warehouses, materialized views. Business Critical HIPAA/PCI compliance, private link, column-level security, tri-secret secure.

Memory Map: [DIRECT] Common Basic Questions -> layer separation -> partition pruning cue -> warehouse scaling cue -> cache caveat -> recovery terms [SF_01_Architecture_Core.md:549].

Q10: [DIRECT] Common Basic Questions

📝 What is Snowflake edition difference (Standard, Enterprise, Business Critical)?

📝 What cloud providers does Snowflake support?

📝 What is the difference between a database, schema, and table in Snowflake?

📝 What is an Account Identifier?

📝 What is SnowSQL?

SECTION 2: SNOWFLAKE vs DATABRICKS (Critical Comparison)

Answer First: Snowflake is SQL- and warehouse-oriented while Databricks is strongest for open lakehouse engineering and ML; choose by workload, governance, interoperability, and operating model.

Memory Map: Snowflake vs Databricks — When to use which? -> analyst SQL favors warehouse isolation -> Python ML favors lakehouse engines -> openness and operating model break ties [SF_01_Architecture_Core.md:570].

Q11: Snowflake vs Databricks — When to use which?

Simple Explanation: This is the #1 comparison question you'll get since you're preparing for both. They solve DIFFERENT problems — they're often used TOGETHER in modern data platforms.

When to use Snowflake:

• BI dashboards with 100+ concurrent users
• SQL analysts without engineering skills
• Data sharing with external partners
• Mixed JSON + structured data analytics
• When you want zero infrastructure management

When to use Databricks:

• Complex Python ETL (pandas, PySpark)
• Machine learning pipeline + MLflow
• Streaming data from Kafka
• You need open-source portability
• Large-scale data transformation (not just querying)

Real-world at a large enterprise: "Databricks handles the heavy ingestion and transformation (Oracle CDC → Kafka → Bronze → Silver layers). The clean Silver data is then shared with Snowflake for BI analysts to query with SQL. Both tools, one pipeline."

Interview tip: "Snowflake and Databricks are complementary, not competing. I'd use Databricks for the data engineering heavy lifting and Snowflake for SQL-heavy analytics and partner data sharing. Many enterprise platforms today use both."

SECTION 3: SCENARIO-BASED QUESTIONS

Answer First: Ingest partner files through external stages and streaming events through Snowpipe Streaming, isolate ETL, BI, pricing, and data-science compute, then enforce airline row policies, PII masking, and governed shares.

Memory Map: Scenario — Design a Snowflake architecture for a travel platform's booking analytics -> batch and streaming paths land raw facts -> workload warehouses isolate contention -> policies and shares constrain partner access [SF_01_Architecture_Core.md:635].

Q12: Scenario — Design a Snowflake architecture for a travel platform's booking analytics

Context: 10 billion bookings/year, 200+ airline partners, 500 BI analysts, GDPR compliance, real-time pricing queries, historical reporting up to 5 years.

Answer:

┌─────────────────────────────────────────────────────────────────┐
│              TRAVEL PLATFORM SNOWFLAKE ANALYTICS               │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  INGESTION:                                                     │
│  Databricks Silver tables → Snowflake via Snowpipe Streaming    │
│  Partner files (CSV/JSON) → External Stage (ADLS) → COPY INTO  │
│  Real-time prices (Kafka) → Snowpipe (auto-ingest)             │
│                                                                 │
│  DATABASES:                                                     │
│  RAW_DB       → Bronze (raw data, 90-day time travel)          │
│  ANALYTICS_DB → Silver/Gold (clean data, 30-day time travel)   │
│  SHARE_DB     → Partner-specific views (data sharing)          │
│                                                                 │
│  VIRTUAL WAREHOUSES (workload isolation):                       │
│  ETL_WH     → XL, loading & transformation (overnight)         │
│  BI_WH      → M, multi-cluster (1-10), BI analysts (9-6 PM)   │
│  PRICING_WH → L, real-time fare queries (always on)           │
│  DS_WH      → XL, data science queries (on demand)            │
│                                                                 │
│  CLUSTERING KEYS:                                               │
│  bookings_fact: CLUSTER BY (booking_date, airline_code)        │
│  passengers: CLUSTER BY (passenger_id)                         │
│                                                                 │
│  SECURITY:                                                      │
│  Unity hierarchy: ACCOUNTADMIN > SYSADMIN > team roles         │
│  Column masking: email, phone, passport_no (PII/GDPR)          │
│  Row access policies: each airline sees only their bookings    │
│                                                                 │
│  SHARING:                                                       │
│  Secure Share per airline → each airline's secure portal       │
│  Data Clean Room: cross-airline analysis without raw PII       │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Answer First: Diagnose a slow query from Query History and Query Profile: separate queueing from execution time, inspect pruning and bytes scanned, locate spill or expensive operators, then benchmark one targeted change.

Memory Map: Scenario — Why is a specific query slow? How to diagnose? -> history separates queue from execution -> profile locates scan spill or join cost -> measured change confirms improvement [SF_01_Architecture_Core.md:678].

Q13: Scenario — Why is a specific query slow? How to diagnose?

-- Step 1: Find the slow query in query history
SELECT query_id, query_text, total_elapsed_time/1000 as seconds,
       bytes_scanned/1024/1024/1024 as gb_scanned,
       partitions_scanned, partitions_total,
       -- Low ratio = bad pruning (scanning too many partitions)
       ROUND(partitions_scanned/partitions_total * 100, 1) as pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE user_name = 'ANALYST_USER'
ORDER BY total_elapsed_time DESC
LIMIT 10;

-- Step 2: Check if clustering is helping
SELECT SYSTEM$CLUSTERING_INFORMATION('bookings', '(booking_date)');
-- Look at: average_depth (>6 = needs reclustering)
--          average_overlaps (>5 = partitions heavily mixed)

-- Step 3: Check execution details
SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS('query-id-here'));
-- Shows each operator: TableScan, Join, Aggregate
-- Find which step takes the most time

-- Step 4: Measure warehouse local-disk cache use for executed queries
SELECT query_id, warehouse_name, total_elapsed_time,
       percentage_scanned_from_cache
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_id = 'your-query-id';
-- percentage_scanned_from_cache measures table data read from the local
-- warehouse cache. It does NOT identify persisted-result reuse.

Snowflake exposes no dedicated persisted-result-reuse boolean in QUERY_HISTORY. A reused persisted result bypasses query execution; validate its documented eligibility conditions and inspect Query History/Query Profile rather than inventing a history flag. An executed query can separately read table data from the warehouse's local cache, measured by percentage_scanned_from_cache. See QUERY_HISTORY columns, persisted results, and warehouse data cache.

Day 1: Snowflake Architecture & Core — Quick Recall Guide

🧠 MASTER MEMORY MAP — Day 1

SECTION 1: ARCHITECTURE

🧠 Memory Map: 3 Layers

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: A fully-managed cloud data warehouse with complete separation of storage and compute. Runs on AWS, Azure, or GCP. Uses SQL for all operations.

Memory Map: What is Snowflake? -> services coordinate metadata and security -> elastic warehouses process SQL -> shared storage preserves governed data [SF_01_Quick_Recall.md:87].

What is Snowflake?

Answer First: 1. Cloud Services (brain — query parsing, auth, metadata, result cache).

Memory Map: What are the 3 layers of Snowflake? -> cloud services planner -> warehouse executors -> storage partitions -> cache interactions -> billing boundary [SF_01_Quick_Recall.md:90].

What are the 3 layers of Snowflake?

Answer First: Separation of compute and storage. Multiple virtual warehouses can query the same data at the same time without competing. Scale each independently. Pay only for compute when running.

Memory Map: What makes Snowflake different from traditional databases? -> centralized data outlives compute -> independent warehouses avoid resource contention -> suspension stops idle compute billing [SF_01_Quick_Recall.md:95].

What makes Snowflake different from traditional databases?

Answer First: Automatically-created columnar storage units, each 50-500 MB compressed. Snowflake stores min/max metadata per column per partition — used for partition pruning to skip irrelevant data.

Memory Map: What are micro-partitions? -> automatic columnar chunks -> immutable range metadata -> scan pruning -> recluster when overlap grows -> profile bytes [SF_01_Quick_Recall.md:98].

What are micro-partitions?

Answer First: NO! Hive partitions = you manually define them, separate folders per value, you manage them. Micro-partitions = Snowflake creates automatically, fine-grained, stores min/max metadata, you never touch them. Much more efficient.

Memory Map: Are Snowflake micro-partitions the same as Hive partitions? -> folder keys contrast -> service-managed metadata -> no manual directories -> pruning without partition column -> storage transparency [SF_01_Quick_Recall.md:101].

Are Snowflake micro-partitions the same as Hive partitions?

Answer First: An optional column (or expression) that tells Snowflake how to organize micro-partitions. When data is clustered, similar values are in the same partition better pruning faster queries.

Memory Map: What is a clustering key? -> ordered co-location hint -> overlap score -> recluster threshold -> service credit decision -> validation query [SF_01_Quick_Recall.md:104].

What is a clustering key?

Answer First: Snowflake skips micro-partitions that cannot contain matching rows based on their min/max metadata. Example: WHERE date = '2026-03' skip all partitions where max_date < March.

Memory Map: What is partition pruning? -> predicate range lookup -> skipped partition count -> inspect profile scan -> fix non-sargable filters -> measure bytes [SF_01_Quick_Recall.md:107].

What is partition pruning?

Answer First: A cluster of compute VMs that execute queries. You size it (XS to 4XL), it auto-starts when needed and auto-suspends when idle. Pay only when running.

Memory Map: What is a Virtual Warehouse? -> independent compute pool -> memory and CPU size -> suspend stops credits -> resume cold cache -> workload isolation [SF_01_Quick_Recall.md:110].

What is a Virtual Warehouse?

Answer First: A multi-cluster warehouse has MAX_CLUSTER_COUNT above 1. It auto-scales for concurrency only when MIN_CLUSTER_COUNT < MAX_CLUSTER_COUNT; the defaults are both 1, which remains single-cluster.

Memory Map: What is a multi-cluster warehouse? -> queue pressure signal -> add peer clusters -> keep per-query size -> concurrency not speedup -> max cluster guard [SF_01_Quick_Recall.md:113].

What is a multi-cluster warehouse?

Answer First: - Scale UP (bigger size: M L XL) = fix SLOW queries (one complex query needs more power).

Memory Map: Scale UP vs Scale OUT — what's the difference? -> larger cluster adds per-query resources -> additional clusters absorb concurrent queues -> history verifies the actual bottleneck [SF_01_Quick_Recall.md:116].

Scale UP vs Scale OUT — what's the difference?

🔑 MID-LEVEL QUESTIONS

Answer First: 1. SQL arrives Cloud Services layer parses + compiles it.

Memory Map: How does Snowflake read a query? (End-to-end flow) -> services parse authorize and optimize -> metadata prunes impossible partitions -> warehouse operators scan join and return results [SF_01_Quick_Recall.md:124].

How does Snowflake read a query? (End-to-end flow)

Answer First: The warehouse nodes (VMs) are stopped. The Local Disk Cache (SSD) is CLEARED. The next query triggers auto-resume (~5-15 seconds startup). Micro-partitions still in Storage — no data loss. Only the cache is lost.

Memory Map: What happens when warehouse is auto-suspended? -> credits stop -> local disk cache drops -> persisted results may remain -> resume latency -> idle policy [SF_01_Quick_Recall.md:133].

What happens when warehouse is auto-suspended?

Answer First: Start with Medium for most workloads. If query is slow, scale UP (L, XL). If many users are queuing, add clusters (multi-cluster). Use Query Profile to find if bottleneck is compute (scale up) or concurrency (scale out).

Memory Map: How to decide virtual warehouse size? -> spill symptom -> benchmark XS upward -> cache warmup control -> cost per query -> rollback size [SF_01_Quick_Recall.md:136].

How to decide virtual warehouse size?

Answer First: - Clustering depth = how many micro-partitions are stacked containing the same key value (lower = better, ideal = 1).

Memory Map: Explain clustering depth and overlap -> overlapping ranges -> depth score -> targeted key choice -> auto-clustering cost -> improvement proof [SF_01_Quick_Recall.md:139].

Explain clustering depth and overlap

SECTION 2: CACHING

🧠 Memory Map: 3 Cache Levels

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: 1. Result Cache (Cloud Services — 24h, free, same query returns instantly).

Memory Map: What are the 3 caching levels in Snowflake? -> result set reuse -> local disk blocks -> remote storage services -> TTL and mutation invalidators -> profile evidence [SF_01_Quick_Recall.md:179].

What are the 3 caching levels in Snowflake?

Answer First: When the exact same SQL is run again within 24 hours AND the underlying data hasn't changed. Returns instantly with ZERO compute credits.

Memory Map: When is Result Cache used? -> exact query signature -> unchanged data -> 24-hour reuse window -> no warehouse execution -> bypass proof [SF_01_Quick_Recall.md:184].

When is Result Cache used?

Answer First: 1. The underlying table data changed (INSERT/UPDATE/DELETE since last run).

Memory Map: Why might Result Cache NOT be used even for the same query? -> changed data or role -> nondeterministic function -> text mismatch -> warehouse executes -> diagnose miss [SF_01_Quick_Recall.md:187].

Why might Result Cache NOT be used even for the same query?

Answer First: Local Disk Cache (Level 2) is completely cleared. Next query after resume reads from Storage again. Result Cache (Level 1) is unaffected — it's in Cloud Services layer.

Memory Map: What happens to cache when warehouse is suspended? -> suspend event -> local SSD cleared -> persisted results separate -> cold scan risk -> warmup test [SF_01_Quick_Recall.md:193].

What happens to cache when warehouse is suspended?

SECTION 3: TIME TRAVEL & CLONING

🧠 Memory Map: Time Travel

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Ability to query, clone, or restore Snowflake data as it was at any point within the retention period (0-90 days). Works because Snowflake keeps old micro-partitions.

Memory Map: What is Time Travel? -> historical table versions -> AT BEFORE query -> retention window -> fail-safe boundary -> recovery drill [SF_01_Quick_Recall.md:230].

What is Time Travel?

Answer First: 1 day. Can be set 0-90 days with Enterprise+ edition. Set per-table: ALTER TABLE t SET DATA_RETENTION_TIME_IN_DAYS = 30.

Memory Map: What is the default time travel period? -> one-day default -> edition range -> object override -> storage impact -> restore expectation [SF_01_Quick_Recall.md:233].

What is the default time travel period?

Answer First: - Time Travel (0-90 days) YOU can access, query, restore yourself.

Memory Map: What is the difference between Time Travel and Fail-safe? -> self-service window -> Snowflake support window -> no fail-safe querying -> restore path choice -> compliance note [SF_01_Quick_Recall.md:236].

What is the difference between Time Travel and Fail-safe?

Answer First: CREATE TABLE clone CLONE original — instantly creates a copy that shares all micro-partitions with the original. No data is duplicated at creation. Storage only grows when clone data diverges.

Memory Map: What is Zero-Copy Cloning? -> instant object fork -> shared micro-partitions -> write divergence -> storage grows on change -> sandbox cleanup [SF_01_Quick_Recall.md:240].

What is Zero-Copy Cloning?

Answer First: 1. Instant dev/test environments from prod (no wait, no cost).

Memory Map: What are the use cases for Zero-Copy Clone? -> dev branch snapshot -> testing isolation -> backup rehearsal -> shared storage caveat -> cleanup owner [SF_01_Quick_Recall.md:243].

What are the use cases for Zero-Copy Clone?

SECTION 4: SEMI-STRUCTURED DATA

🧠 Memory Map: VARIANT

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Snowflake's data type for semi-structured data (JSON, Avro, Parquet, ORC). Stores any structure in one column — no schema definition needed. Max 128 MB per value (expanded in 2025).

Memory Map: What is the VARIANT data type? -> semi-structured cell -> schema-later pattern -> mixed-type risk -> typed alternatives -> bronze fit [SF_01_Quick_Recall.md:278].

What is the VARIANT data type?

Answer First: Using colon notation + cast: raw_data:passenger.email::VARCHAR — reads the email field inside passenger object and casts to VARCHAR.

Memory Map: How do you access a JSON field from a VARIANT column? -> colon path syntax -> nested key traversal -> explicit cast -> missing key null -> projection test [SF_01_Quick_Recall.md:281].

How do you access a JSON field from a VARIANT column?

Answer First: A Snowflake table function that converts an array or object inside a VARIANT column into multiple rows — one row per array element. Used with LATERAL FLATTEN(input => col:array_field).

Memory Map: What is FLATTEN? -> array explosion -> lateral join -> index and value columns -> preserve parent row -> rebuild grain [SF_01_Quick_Recall.md:284].

What is FLATTEN?

Answer First: VARIANT returns data in Snowflake's internal format. Without ::VARCHAR / ::NUMBER / ::DATE, comparisons may fail and values display oddly. Always cast: col:key::VARCHAR.

Memory Map: Why must you cast VARIANT values? -> typed comparisons -> BI display stability -> bad value errors -> TRY_CAST defense -> quality gate [SF_01_Quick_Recall.md:287].

Why must you cast VARIANT values?

🧠 FINAL REVISION — Day 1 Summary Card

┌──────────────────────────────────────────────────────────────────┐
│               DAY 1: SNOWFLAKE ARCHITECTURE                       │
├──────────────────────────────────────────────────────────────────┤
│                                                                  │
│  3 LAYERS = "BMS" (Brain, Muscles, Storage)                      │
│  Cloud Services (brain) → Compute (muscles) → Storage (disk)     │
│  Key insight: storage & compute are SEPARATE → scale each alone  │
│                                                                  │
│  MICRO-PARTITIONS = automatic, columnar, 50-500 MB                │
│  NOT like Hive partitions (those are manual, folder-based)       │
│  Metadata: min/max per column → partition PRUNING                │
│  Clustering key: optional hint to organize partitions            │
│                                                                  │
│  VIRTUAL WAREHOUSE = compute cluster (XS to 4XL)                │
│  Scale UP (M→XL) = fix slow queries                             │
│  Scale OUT (multi-cluster) = fix concurrency (many users)        │
│  Auto-suspend = stop billing when idle                          │
│  ⚠️ Suspend = Local Disk Cache CLEARED                          │
│                                                                  │
│  CACHING = "RLS" (Result, Local-disk, Storage)                   │
│  Result Cache: 24h, free, exact SQL match required              │
│  Local Cache: cleared on suspend!                               │
│                                                                  │
│  TIME TRAVEL: 0-90 days (default 1 day)                         │
│  FAIL-SAFE: 7 days after TT, Snowflake only (not self-service)  │
│  CLONE: instant copy, no data duplicated, shared micro-partitions│
│                                                                  │
│  VARIANT: store any JSON/semi-structured data                    │
│  Access: col:key::VARCHAR (always cast!)                        │
│  FLATTEN: array → rows (like Spark explode())                   │
│                                                                  │
│  SNOWFLAKE vs DATABRICKS:                                        │
│  Snowflake = SQL warehouse, BI analytics, data sharing          │
│  Databricks = Python ETL, ML, streaming, open formats           │
│  Use BOTH: Databricks for ETL → Snowflake for analytics         │
│                                                                  │
│  TOP 5 THINGS TO SAY IN INTERVIEW:                               │
│  1. "Separate compute & storage — multiple WH on same data"     │
│  2. "Micro-partitions auto-created, pruning skips irrelevant"   │
│  3. "Multi-cluster warehouse for 200+ concurrent BI analysts"   │
│  4. "Result cache returns exact same query free in 24h"         │
│  5. "Zero-copy clone: instant dev environment from prod"        │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Snowflake Loading, Pipelines, and Performance

Answer First: Choose ingestion by delivery contract, model change processing explicitly, and tune performance from Query Profile evidence before changing warehouse size or physical design.

Memory Map: stage -> ingest -> change capture -> transform -> profile -> tune -> monitor.

Day 2: Data Loading, Pipelines & Performance

SECTION 1: DATA LOADING (1 hour)

Answer First: COPY INTO is Snowflake's bulk loading command — it reads files from a stage (ADLS, S3, internal) and loads them into a table. It is MUCH faster than INSERT for large volumes because it runs in parallel across the warehouse.

Memory Map: What is COPY INTO? How is it different from INSERT? -> staged files feed parallel loaders -> table metadata tracks recent file loads -> row inserts suit only small direct writes [SF_02_Pipelines_Performance.md:11].

Q1: What is COPY INTO? How is it different from INSERT?

Simple Explanation: COPY INTO is Snowflake's bulk loading command — it reads files from a stage (ADLS, S3, internal) and loads them into a table. It is MUCH faster than INSERT for large volumes because it runs in parallel across the warehouse.

Why do we need it? INSERT processes rows one by one — fine for 100 rows, terrible for 100 million rows. COPY INTO parallelizes across the warehouse and processes files in parallel batches.

Real-world analogy: INSERT = one person typing data manually. COPY INTO = 100 workers simultaneously moving pallets from a loading dock into the warehouse.

-- Basic COPY INTO from external stage (Azure ADLS)
COPY INTO raw_bookings
FROM @azure_booking_stage/2026/03/25/        -- Read from this ADLS path
FILE_FORMAT = (
    TYPE = 'PARQUET'                          -- File type
)
ON_ERROR = 'CONTINUE';                        -- Skip bad files, continue loading
-- Options: ABORT_STATEMENT (default), CONTINUE, SKIP_FILE

-- COPY INTO with transformation (load + clean in one step)
COPY INTO silver_bookings (booking_id, booking_date, amount, airline_code)
FROM (
    SELECT
        $1:booking_id::VARCHAR,               -- Cast from VARIANT
        $1:booking_date::DATE,
        $1:fare.amount::NUMBER(10,2),
        UPPER($1:airline_code::VARCHAR)        -- Transform during load
    FROM @azure_booking_stage/bookings.json
)
FILE_FORMAT = (TYPE = 'JSON');

-- COPY INTO with error handling — see what failed
COPY INTO raw_bookings FROM @stage/
ON_ERROR = 'CONTINUE';

-- After loading, check what errors occurred:
SELECT * FROM TABLE(VALIDATE(raw_bookings, JOB_ID => '_last'));
-- Shows exactly which files + rows failed and why

-- COPY INTO uses table load metadata to avoid recent duplicate file loads; this is not an unlimited idempotency guarantee
-- Snowflake tracks which files were already loaded (load history table)
-- Re-running the same COPY INTO skips already-loaded files automatically
-- To force reload: COPY INTO ... FORCE = TRUE;

Key COPY INTO options:

Answer First: COPY INTO = you manually trigger the load (or schedule with a Task). Snowpipe = load happens AUTOMATICALLY when new files arrive. Snowpipe monitors your stage and runs COPY INTO for you the moment a new file appears.

Memory Map: What is Snowpipe? How does it differ from COPY INTO? -> file arrival event -> pipe COPY definition -> serverless load history -> latency and cost -> replay check [SF_02_Pipelines_Performance.md:69].

Q2: What is Snowpipe? How does it differ from COPY INTO?

Simple Explanation: COPY INTO = you manually trigger the load (or schedule with a Task). Snowpipe = load happens AUTOMATICALLY when new files arrive. Snowpipe monitors your stage and runs COPY INTO for you the moment a new file appears.

Real-world analogy: COPY INTO = you call a taxi (trigger manually). Snowpipe = Uber pool running continuously — jumps in automatically whenever you're ready (file arrives).

-- Create a pipe that auto-loads from a stage
CREATE PIPE booking_pipe
    AUTO_INGEST = TRUE          -- Enabled for cloud event notifications
    AS
    COPY INTO raw_bookings      -- What to run when files arrive
    FROM @azure_booking_stage
    FILE_FORMAT = (TYPE = 'PARQUET');

-- Check pipe status
SELECT SYSTEM$PIPE_STATUS('booking_pipe');
-- Shows: pendingFileCount, lastIngestedTimestamp, status

-- See load history (what files loaded, errors)
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
    TABLE_NAME => 'raw_bookings',
    START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP)
));

Snowpipe vs COPY INTO vs Snowpipe Streaming:

SECTION 2: STREAMS & TASKS (CDC in Snowflake)

Answer First: A stream stores an offset over a source object's version history and exposes inserted and deleted row changes plus metadata; its offset advances only when a committed DML transaction consumes it.

Memory Map: What Are Snowflake Streams? How Do They Enable CDC? -> table version offset -> insert/delete rows -> metadata action flag -> transactional consume -> staleness watch [SF_02_Pipelines_Performance.md:142].

Q3: What Are Snowflake Streams? How Do They Enable CDC?

Simple Explanation: A Stream is Snowflake's native CDC (Change Data Capture) mechanism. It tracks every INSERT, UPDATE, and DELETE on a table — without modifying the original table or storing a separate copy. When you query the stream, you see "what changed since I last consumed it?" Once you consume the changes inside a DML transaction, the stream automatically advances its offset — the consumed rows disappear from the stream view.

Real-world analogy: Think of a hotel check-in/check-out log. The front desk doesn't copy the entire guest database every hour — it just records new arrivals (INSERT), room changes (UPDATE), and departures (DELETE). The housekeeping team reads the log, services the rooms, and tears off the page. Tomorrow there's a fresh page with only new changes.

Why do we need it? Without streams, you'd have to either (a) reload the entire table every time (expensive, slow), or (b) build custom CDC logic using timestamps or audit columns (fragile, error-prone). Streams give you a zero-maintenance, guaranteed-consistent way to see exactly what changed — perfect for incremental ETL pipelines.

How Streams Track Changes (The Offset Model):

TABLE: raw_bookings
┌────────────────────────────────────────────┐
│  Row 1  │  Row 2  │  Row 3  │  Row 4      │
│ (old)   │ (old)   │ (new!)  │ (new!)      │
└────────────────────────────────────────────┘
                       ▲
                       │ Stream offset points here
                       │ "I've seen everything before Row 3"
                       │
STREAM VIEW: shows Row 3 and Row 4 only (the delta)

After you consume Row 3 & 4 in a DML (INSERT/MERGE):
  → Offset advances to Row 4
  → Stream view is now empty (until new changes arrive)

Streams use Snowflake's internal versioning (Time Travel metadata) to track the offset. No data is copied — the stream is essentially a pointer into the table's change history.

Stream Metadata Columns:

Every stream row includes three hidden metadata columns:

An UPDATE appears as TWO rows in the stream: a DELETE of the old value + an INSERT of the new value, both with METADATA$ISUPDATE = TRUE.

Stream Types:

-- ========================================
-- CREATING STREAMS
-- ========================================

-- Standard stream (tracks all DML changes)
CREATE STREAM booking_changes_stream
    ON TABLE raw_bookings
    SHOW_INITIAL_ROWS = FALSE;   -- Only track NEW changes from this point
    -- SHOW_INITIAL_ROWS = TRUE → backfill: include all existing rows as INSERTs

-- Append-only stream (lighter weight — only new inserts)
CREATE STREAM new_bookings_stream
    ON TABLE raw_bookings
    APPEND_ONLY = TRUE;
-- Use for event/log tables where rows are never updated or deleted

-- Insert-only stream on an external table
CREATE STREAM new_files_stream
    ON EXTERNAL TABLE ext_booking_files
    INSERT_ONLY = TRUE;
-- Fires when new files appear in the external stage

-- ========================================
-- QUERYING A STREAM (peek at changes)
-- ========================================

SELECT *,
    METADATA$ACTION,         -- 'INSERT' or 'DELETE'
    METADATA$ISUPDATE,       -- TRUE if part of an UPDATE
    METADATA$ROW_ID          -- Internal row identifier
FROM booking_changes_stream;
-- This is a READ — it does NOT consume the stream
-- Stream is only consumed when changes are used in a DML statement

-- ========================================
-- CONSUMING A STREAM (the MERGE pattern — #1 interview pattern!)
-- ========================================

-- The Stream + MERGE pattern: upsert changes into a target table
MERGE INTO silver_bookings AS target
USING (
    SELECT booking_id, booking_date, amount, airline_code,
           METADATA$ACTION AS action,
           METADATA$ISUPDATE AS is_update
    FROM booking_changes_stream
) AS src
ON target.booking_id = src.booking_id
-- Handle inserts (new rows)
WHEN NOT MATCHED AND src.action = 'INSERT'
    THEN INSERT (booking_id, booking_date, amount, airline_code)
         VALUES (src.booking_id, src.booking_date, src.amount, src.airline_code)
-- Handle updates (new value of updated rows)
WHEN MATCHED AND src.action = 'INSERT' AND src.is_update = TRUE
    THEN UPDATE SET
        target.amount = src.amount,
        target.booking_date = src.booking_date,
        target.airline_code = src.airline_code
-- Handle deletes
WHEN MATCHED AND src.action = 'DELETE' AND src.is_update = FALSE
    THEN DELETE;

-- After MERGE commits → stream advances → consumed rows disappear
-- Next query to stream shows only changes AFTER this MERGE

-- ========================================
-- STALE STREAMS — how to avoid them
-- ========================================

-- A stream becomes STALE if you don't consume it within the table's
-- DATA_RETENTION_TIME_IN_DAYS (Time Travel period, default 1 day).
-- Once stale, the stream is BROKEN — you must recreate it.

-- Check if a stream is stale:
SHOW STREAMS LIKE 'booking_changes_stream';
-- Look at "stale_after" column — if past, the stream is stale

-- Prevention: consume streams regularly OR extend retention:
ALTER TABLE raw_bookings SET DATA_RETENTION_TIME_IN_DAYS = 14;
-- Now stream has 14 days before going stale
-- Trade-off: longer retention = more storage cost (Time Travel storage)

Interview Tip: The Stream + MERGE pattern is the single most asked Snowflake pipeline question. Know it cold. Also explain that streams are "transactional consumers" — they only advance when the consuming DML commits. If the MERGE fails, the stream stays where it was (no data loss). Mention stale streams and retention as a follow-up — it shows production experience.

Answer First: A Task is Snowflake's built-in scheduler — it runs a SQL statement or stored procedure on a defined schedule (cron or interval). Tasks can be chained into a DAG (Directed Acyclic Graph) where child tasks run only after their parent succeeds. Combined with Streams, Tasks form the classic Snowflake incremental pipeline.

Memory Map: What Are Snowflake Tasks? How Do You Schedule SQL? -> schedule trigger -> warehouse or serverless run -> graph dependencies -> resume children then root -> history alert [SF_02_Pipelines_Performance.md:280].

Q4: What Are Snowflake Tasks? How Do You Schedule SQL?

Simple Explanation: A Task is Snowflake's built-in scheduler — it runs a SQL statement or stored procedure on a defined schedule (cron or interval). Tasks can be chained into a DAG (Directed Acyclic Graph) where child tasks run only after their parent succeeds. Combined with Streams, Tasks form the classic Snowflake incremental pipeline.

Real-world analogy: Think of a factory assembly line with a foreman. The foreman (Task scheduler) blows a whistle every 5 minutes. Station 1 (bronze) starts work. When Station 1 finishes, Station 2 (silver) automatically begins. Station 3 (gold) waits for Station 2. If the foreman sees no raw materials on the conveyor belt (SYSTEM$STREAM_HAS_DATA() = FALSE), he skips that cycle — no wasted work.

Why do we need it? Without Tasks, you'd need an external scheduler (Airflow, cron, Azure Data Factory) just to run SQL on a timer. Tasks keep everything inside Snowflake — no external dependencies, no credentials to manage, no network calls. For simple-to-medium pipelines, Tasks eliminate an entire orchestration layer.

-- ========================================
-- CREATING TASKS
-- ========================================

-- Simple task: run SQL every 5 minutes using a warehouse
CREATE TASK load_bronze_task
    WAREHOUSE = 'ETL_WH'               -- Uses this warehouse's credits
    SCHEDULE = '5 MINUTE'              -- Interval schedule
AS
    COPY INTO raw_bookings
    FROM @azure_booking_stage;

-- Cron-based schedule
CREATE TASK nightly_aggregate_task
    WAREHOUSE = 'ETL_WH'
    SCHEDULE = 'USING CRON 0 2 * * * UTC'   -- 2:00 AM UTC daily
    -- Cron: minute hour day month weekday timezone
AS
    CALL refresh_daily_aggregates();

-- !! CRITICAL: Tasks are SUSPENDED by default !!
-- You MUST resume them or they will never run
ALTER TASK load_bronze_task RESUME;
ALTER TASK load_bronze_task SUSPEND;     -- Pause it

-- ========================================
-- SERVERLESS TASKS vs WAREHOUSE-BASED TASKS
-- ========================================

-- Warehouse-based: uses a specific warehouse (you control size & cost)
CREATE TASK warehouse_task
    WAREHOUSE = 'ETL_WH'
    SCHEDULE = '10 MINUTE'
AS SELECT 1;

-- Serverless: Snowflake manages compute — no warehouse needed
CREATE TASK serverless_task
    USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'MEDIUM'
    -- Omit WAREHOUSE = ... to make it serverless
    SCHEDULE = '5 MINUTE'
AS
    INSERT INTO audit_log VALUES (CURRENT_TIMESTAMP, 'heartbeat');

-- Serverless tasks:
-- ✓ metered separately; compare actual workload cost than warehouse-based for equivalent work
-- ✓ No warehouse to manage — auto-scales to workload
-- ✓ Best for lightweight, frequent tasks
-- ✗ Less control over compute resources
-- ✗ Compute size is approximate (Snowflake decides)

-- ========================================
-- TASK TREES (DAG — parent → child dependencies)
-- ========================================

-- Root task: has a SCHEDULE (the only task that needs one)
CREATE TASK bronze_task
    WAREHOUSE = 'ETL_WH'
    SCHEDULE = 'USING CRON 0 2 * * * UTC'  -- 2 AM daily
AS
    CALL load_bronze_data();

-- Child task: uses AFTER clause (no SCHEDULE — triggered by parent)
CREATE TASK silver_task
    WAREHOUSE = 'ETL_WH'
    AFTER bronze_task               -- Runs ONLY after bronze_task succeeds
AS
    CALL transform_silver_data();

-- Grandchild task: runs after silver
CREATE TASK gold_task
    WAREHOUSE = 'ETL_WH'
    AFTER silver_task
AS
    CALL aggregate_gold_metrics();

-- A child can depend on MULTIPLE parents (fan-in):
CREATE TASK final_report_task
    WAREHOUSE = 'ETL_WH'
    AFTER silver_task, gold_task     -- Waits for BOTH to finish
AS
    CALL generate_report();

-- Initial activation option 1: resume every desired child first, root last
ALTER TASK gold_task RESUME;
ALTER TASK silver_task RESUME;
ALTER TASK bronze_task RESUME;      -- Root task last

-- Initial activation option 2: recursively resume root + all dependents
SELECT SYSTEM$TASK_DEPENDENTS_ENABLE('bronze_task');

-- ========================================
-- CONDITIONAL EXECUTION: SYSTEM$STREAM_HAS_DATA()
-- ========================================

-- Only run the task if the stream has new data (skip empty cycles)
CREATE TASK process_booking_changes
    WAREHOUSE = 'ETL_WH'
    SCHEDULE = '5 MINUTE'
    WHEN SYSTEM$STREAM_HAS_DATA('booking_changes_stream')
    -- If stream is empty → task is SKIPPED (no warehouse credit used!)
AS
    MERGE INTO silver_bookings AS target
    USING booking_changes_stream AS src
    ON target.booking_id = src.booking_id
    WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT'
        THEN INSERT (booking_id, booking_date, amount, airline_code)
             VALUES (src.booking_id, src.booking_date, src.amount, src.airline_code)
    WHEN MATCHED AND src.METADATA$ACTION = 'INSERT' AND src.METADATA$ISUPDATE = TRUE
        THEN UPDATE SET target.amount = src.amount
    WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' AND src.METADATA$ISUPDATE = FALSE
        THEN DELETE;

ALTER TASK process_booking_changes RESUME;

-- ========================================
-- COMPLETE STREAM + TASK PIPELINE (end-to-end example)
-- ========================================

-- Step 1: Source table exists
-- CREATE TABLE raw_bookings (booking_id VARCHAR, booking_date DATE, ...);

-- Step 2: Create stream to track changes
CREATE STREAM booking_cdc_stream ON TABLE raw_bookings;

-- Step 3: Create task that consumes stream via MERGE
CREATE TASK cdc_pipeline_task
    WAREHOUSE = 'ETL_WH'
    SCHEDULE = '5 MINUTE'
    WHEN SYSTEM$STREAM_HAS_DATA('booking_cdc_stream')
AS
    MERGE INTO silver_bookings AS t
    USING booking_cdc_stream AS s
    ON t.booking_id = s.booking_id
    WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' AND s.METADATA$ISUPDATE = FALSE
        THEN DELETE
    WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' AND s.METADATA$ISUPDATE = TRUE
        THEN UPDATE SET t.amount = s.amount, t.booking_date = s.booking_date
    WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT'
        THEN INSERT (booking_id, booking_date, amount)
             VALUES (s.booking_id, s.booking_date, s.amount);

-- Step 4: Resume the task
ALTER TASK cdc_pipeline_task RESUME;

-- Now: any INSERT/UPDATE/DELETE on raw_bookings is automatically
-- captured by the stream and merged into silver_bookings every 5 minutes

-- ========================================
-- MONITORING TASKS
-- ========================================

-- View task run history (recent runs, status, errors)
SELECT name, state, scheduled_time, completed_time,
       error_code, error_message,
       DATEDIFF('second', query_start_time, completed_time) AS duration_sec
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    TASK_NAME => 'CDC_PIPELINE_TASK',
    SCHEDULED_TIME_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP)
))
ORDER BY scheduled_time DESC;
-- state: 'SUCCEEDED', 'FAILED', 'SKIPPED' (WHEN condition was false)

-- Account-level task history (all tasks)
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
WHERE scheduled_time > DATEADD('hour', -24, CURRENT_TIMESTAMP)
ORDER BY scheduled_time DESC;

Interview Tip: Always mention three things: (1) newly created tasks are suspended; initially resume desired children before the root, or call SYSTEM$TASK_DEPENDENTS_ENABLE to recursively resume the graph; (2) only the root needs a schedule; and (3) WHEN SYSTEM$STREAM_HAS_DATA() avoids empty work. After a previously enabled graph's root is suspended, its children retain their state, so resuming that root does not require re-resuming children. See task graph activation and SYSTEM$TASK_DEPENDENTS_ENABLE.

Answer First: A dynamic table materializes a declarative query and lets Snowflake schedule supported full or incremental refreshes toward a target lag; it removes explicit offsets and task DAGs but does not guarantee freshness.

Memory Map: What Are Dynamic Tables? How Are They Different from Streams + Tasks? -> target lag planner -> declarative SELECT -> incremental or full refresh -> downstream orchestration -> refresh history [SF_02_Pipelines_Performance.md:461].

Q5: What Are Dynamic Tables? How Are They Different from Streams + Tasks?

Simple Explanation: Dynamic Tables (GA 2024, recommended approach 2025-2026) are Snowflake's declarative ELT mechanism. You define WHAT the result should look like using a SELECT query, and Snowflake handles the WHEN (scheduling) and HOW (incremental refresh). You never write MERGE logic, manage stream offsets, or build Task DAGs — Snowflake does it all automatically.

Real-world analogy:

• Streams + Tasks = writing a recipe step by step, setting timers, and telling someone exactly how to cook each ingredient
• Dynamic Tables = showing someone a photo of the finished dish and saying "keep it looking like this, and make sure it's no more than 5 minutes stale"

Why do we need them? Streams + Tasks pipelines work but require 30+ lines of boilerplate per stage (create stream, create task, write MERGE, handle edge cases, resume tasks). For a 5-stage pipeline, that's 150+ lines of procedural code to maintain. Dynamic Tables reduce each stage to a single CREATE statement. Snowflake auto-detects dependencies, auto-schedules refreshes, and auto-computes incremental diffs.

The TARGET_LAG parameter (best-effort staleness target):

-- ========================================
-- CREATING DYNAMIC TABLES
-- ========================================

-- Silver layer: clean + transform raw data
CREATE DYNAMIC TABLE silver_bookings
    TARGET_LAG = '5 minutes'         -- Best-effort maximum staleness target
    WAREHOUSE = 'ETL_WH'             -- Warehouse for refresh compute
AS
SELECT
    booking_id,
    booking_date,
    UPPER(airline_code)     AS airline_code,
    amount,
    CURRENT_TIMESTAMP       AS processed_at
FROM raw_bookings
WHERE amount > 0
  AND booking_id IS NOT NULL;

-- Snowflake AUTOMATICALLY:
-- 1. Detects when raw_bookings changes
-- 2. Computes ONLY the incremental diff (not full reprocess)
-- 3. Attempts to keep silver_bookings within the target; monitor actual lag

-- Gold layer: aggregate (auto-chains to silver!)
CREATE DYNAMIC TABLE gold_daily_revenue
    TARGET_LAG = '1 hour'
    WAREHOUSE = 'ETL_WH'
AS
SELECT
    booking_date,
    airline_code,
    SUM(amount)  AS daily_revenue,
    COUNT(*)     AS booking_count
FROM silver_bookings    -- Reads from dynamic table above!
GROUP BY booking_date, airline_code;
-- Snowflake auto-detects the silver → gold dependency
-- No AFTER clause needed — it just works

-- Manually refresh (force update now, useful for testing)
ALTER DYNAMIC TABLE silver_bookings REFRESH;

-- Suspend / resume
ALTER DYNAMIC TABLE silver_bookings SUSPEND;
ALTER DYNAMIC TABLE silver_bookings RESUME;

-- Check status of all dynamic tables
SELECT name, schema_name, target_lag, refresh_mode,
       refresh_mode_reason, data_timestamp
FROM INFORMATION_SCHEMA.DYNAMIC_TABLES;
-- refresh_mode: 'INCREMENTAL' (optimal) or 'FULL' (fallback if query is too complex)
-- data_timestamp: when the data was last refreshed to

Comparison: Dynamic Tables vs Streams + Tasks vs Materialized Views:

When to use which:

• Dynamic Tables — Default choice for new ELT pipelines. Multi-stage bronze → silver → gold transformations.
• Streams + Tasks — When you need SCD Type 2, sub-minute latency, stored procedures, or external function calls.
• Materialized Views — Single-table aggregations for BI dashboards (no joins, no UDFs, read-heavy/write-light tables).

Interview Tip: Lead with Dynamic Tables for supported declarative transformations, then contrast Streams + Tasks when explicit CDC handling or procedural orchestration is required. TARGET_LAG is a best-effort staleness target, not a schedule or SLA; actual lag can exceed it, so monitor refresh history and size refresh compute from evidence. See current target-lag semantics.

Answer First: A materialized view physically stores an eligible single-table query result and Snowflake maintains it as base micro-partitions change; repeated reads can improve while background maintenance and storage add cost.

Memory Map: Q5b: Explain Materialized Views in Snowflake -> precomputed subset -> automatic maintenance -> query rewrite opportunity -> storage credits -> profile validation [SF_02_Pipelines_Performance.md:568].

Q5b: Explain Materialized Views in Snowflake

Simple Explanation: A Materialized View (MV) is a pre-computed, physically stored result of a query. Unlike a regular view (which re-runs the query every time you SELECT from it), a Materialized View stores the results on disk and Snowflake automatically keeps them in sync with the base table. Queries that hit the MV read pre-computed results instead of scanning the full table — often 10-100x faster.

Real-world analogy: A regular view is like asking the librarian "how many books do we have by genre?" every time — she counts them all each time you ask. A Materialized View is like a summary card pinned to the wall that says "Fiction: 500, Science: 200, History: 150." The librarian updates the card whenever new books arrive. You just read the card — instant answer.

Why do we need it? BI dashboards often run the same expensive aggregation queries hundreds of times per day. Without MVs, each dashboard refresh scans the full fact table. With MVs, the aggregation is pre-computed and auto-refreshed — the dashboard reads the small, pre-aggregated result instead.

How auto-refresh works: Snowflake automatically detects when the base table changes and refreshes the MV in the background. You do not schedule refreshes — they happen transparently. The refresh is incremental (only processes changed micro-partitions), so it's efficient.

-- ========================================
-- CREATING A MATERIALIZED VIEW
-- ========================================

CREATE MATERIALIZED VIEW daily_revenue_mv AS
SELECT
    booking_date,
    airline_code,
    SUM(amount)   AS total_revenue,
    COUNT(*)      AS booking_count,
    AVG(amount)   AS avg_booking_value
FROM fact_bookings
GROUP BY booking_date, airline_code;
-- Snowflake physically stores this result
-- Auto-refreshes when fact_bookings changes

-- Query the MV (same as querying a table — but pre-computed!)
SELECT * FROM daily_revenue_mv
WHERE booking_date = '2026-03-25' AND airline_code = 'LH';
-- Returns instantly — reads pre-aggregated result, not the full fact table

-- Snowflake's optimizer can AUTOMATICALLY route queries to the MV
-- Even if you query the BASE TABLE, Snowflake may use the MV if it
-- detects the query matches the MV's definition (query rewrite)

-- Drop a materialized view
DROP MATERIALIZED VIEW daily_revenue_mv;

-- Check MV refresh status
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE materialized_view_name = 'DAILY_REVENUE_MV'
ORDER BY start_time DESC;

Limitations (important for interviews!):

Cost implications:

• Storage cost: The MV physically stores data (additional storage credits).
• Compute cost: Auto-refresh consumes serverless compute credits (background maintenance). Snowflake charges for this automatically — you don't control when it runs.
• Rule of thumb: MVs save money when reads >> writes. If the base table changes every minute and the MV is queried once per day, the refresh cost exceeds the query savings — don't use an MV.

When to use Materialized Views:

Interview Tip: The #1 interview follow-up is "MV vs Dynamic Table — when would you use each?" Answer: MVs for simple single-table aggregations on read-heavy tables (BI acceleration). Dynamic Tables for anything involving joins, multi-step transformations, or complex SQL. Also mention that MVs support automatic query rewrite — even if analysts query the base table, Snowflake can transparently use the MV if the query pattern matches. This is a feature Dynamic Tables do NOT have.

SECTION 3: SNOWPARK

Answer First: Snowpark is Snowflake's API for Python (also Java/Scala) that lets you write DataFrame-style code that runs INSIDE Snowflake's compute — like PySpark, but for Snowflake. Instead of extracting data to a Python environment and processing it there, Snowpark pushes the computation to Snowflake.

Memory Map: What is Snowpark? When is it useful? -> DataFrame code builds a lazy plan -> Snowflake compute executes near governed data -> UDF packaging and profile evidence bound suitability [SF_02_Pipelines_Performance.md:655].

Q6: What is Snowpark? When is it useful?

Simple Explanation: Snowpark is Snowflake's API for Python (also Java/Scala) that lets you write DataFrame-style code that runs INSIDE Snowflake's compute — like PySpark, but for Snowflake. Instead of extracting data to a Python environment and processing it there, Snowpark pushes the computation to Snowflake.

Real-world analogy: Normally, if you want to analyze data in Snowflake with Python, you'd pull it to your laptop (expensive network transfer), process it, then push back (another transfer). Snowpark = run Python code on Snowflake's servers directly. No data leaves Snowflake.

# Connect to Snowflake with Snowpark
from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum, upper, current_timestamp

session = Session.builder.configs({
    "account":   "travelco-prod",
    "user":      "data_engineer",
    "password":  "***",
    "warehouse": "ETL_WH",
    "database":  "ANALYTICS_DB",
    "schema":    "BOOKINGS"
}).create()

# Create a Snowpark DataFrame (lazy — nothing runs yet)
raw_df = session.table("RAW_BOOKINGS")    # Points to Snowflake table

# Transform using DataFrame API (exactly like PySpark!)
silver_df = (raw_df
    .filter(col("AMOUNT") > 0)           # Filter bad rows
    .filter(col("BOOKING_ID").isNotNull())
    .select(
        col("BOOKING_ID"),
        col("BOOKING_DATE"),
        upper(col("AIRLINE_CODE")).alias("AIRLINE_CODE"),  # Transform
        col("AMOUNT"),
        current_timestamp().alias("PROCESSED_AT")
    )
)

# Write to Snowflake table (this EXECUTES the computation — runs in Snowflake!)
silver_df.write.mode("append").save_as_table("SILVER_BOOKINGS")
# All processing happened INSIDE Snowflake — no data left Snowflake!

# Snowpark UDF: Write Python function, run in Snowflake
from snowflake.snowpark.functions import udf
import snowflake.snowpark.types as T

@udf(return_type=T.FloatType(), input_types=[T.FloatType(), T.StringType()])
def calculate_tax(amount: float, country: str) -> float:
    """Run Python code INSIDE Snowflake — no data transfer"""
    tax_rates = {"IN": 0.18, "UK": 0.20, "DE": 0.19}
    return amount * tax_rates.get(country, 0.0)

# Use the UDF in SQL or DataFrame
result = silver_df.select(
    col("AMOUNT"),
    calculate_tax(col("AMOUNT"), col("COUNTRY")).alias("TAX")
)

New 2026: Snowpark Intelligent Hybrid Execution

# Snowpark now AUTOMATICALLY decides:
# - Run small operations locally on your machine (faster for tiny data)
# - Push large operations to Snowflake (for big data)
# No code change needed — it detects which is optimal automatically

import modin.pandas as mpd   # Drop-in pandas replacement
df = mpd.read_snowflake("SELECT * FROM bookings WHERE date > '2026-01-01'")
# Snowpark decides: big table → run in Snowflake; result small → return locally

SECTION 4: PERFORMANCE TUNING

Answer First: Optimize from Query Profile evidence: reduce bytes scanned and spill first, verify pruning and physical design, then right-size or scale concurrency only for the remaining bottleneck.

Memory Map: How do you optimize query performance in Snowflake? -> profile bottleneck -> partition pruning -> join spill -> SQL rewrite -> measured rerun [SF_02_Pipelines_Performance.md:729].

Q7: How do you optimize query performance in Snowflake?

The PERFORMANCE CHECKLIST ("CQMWS"):

-- STEP 1: Find slow queries
SELECT query_text, total_elapsed_time/1000 as seconds,
       bytes_scanned/1024/1024/1024 as gb_scanned,
       partitions_scanned, partitions_total,
       ROUND(partitions_scanned*100.0/partitions_total, 1) as pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'SUCCESS'
  AND warehouse_name = 'BI_WH'
ORDER BY total_elapsed_time DESC
LIMIT 20;
-- HIGH pct_scanned (>80%) = bad pruning → add clustering key
-- LOW gb_scanned but slow = compute bottleneck → scale up warehouse

-- STEP 2: Check clustering health
SELECT SYSTEM$CLUSTERING_INFORMATION('bookings', '(booking_date, airline_code)');
-- average_depth > 6 → reclustering needed
-- average_overlaps > 5 → partitions heavily mixed

-- STEP 3: Use Materialized Views for repeated heavy aggregations
CREATE MATERIALIZED VIEW daily_revenue_mv AS
SELECT booking_date, airline_code, SUM(amount) as revenue
FROM silver_bookings
GROUP BY booking_date, airline_code;
-- Snowflake pre-computes and auto-refreshes this view
-- BI queries that aggregate by date+airline now hit MV instead of raw table
-- 100x faster for common aggregation queries!

-- STEP 4: Search Optimization Service (for needle-in-haystack queries)
ALTER TABLE bookings ADD SEARCH OPTIMIZATION;
-- Optimizes: WHERE booking_id = 'BK123456' (point lookup)
-- Optimizes: WHERE passenger_email = 'user@example.com' (equality on high-cardinality col)
-- NOT for range queries (use clustering key instead)

-- STEP 5: Query Profile analysis
-- After running a query, in Snowflake UI: Query History → Click query → Query Profile
-- Shows execution tree, where time was spent
-- Key metrics to look for:
--   "Bytes scanned from cache" high = good (cache working)
--   "Partitions scanned" vs "Partitions total" → pruning efficiency
--   "Spilling to disk" = not enough memory → scale up warehouse

Materialized Views vs Dynamic Tables:

Answer First: For 200 BI users, separate BI from ETL compute, use queued-overload time to decide whether multi-cluster scale-out is needed, then address individual scan, spill, and aggregation costs from Query Profile.

Memory Map: Scenario — Dashboard queries are slow for 200 BI analysts. How to fix? -> queue time diagnoses concurrency pressure -> profiles expose per-query waste -> isolated compute and precomputation reduce contention [SF_02_Pipelines_Performance.md:799].

Q8: Scenario — Dashboard queries are slow for 200 BI analysts. How to fix?

Day 2: Pipelines & Performance — Quick Recall Guide

🧠 MASTER MEMORY MAP — Day 2

SECTION 1: DATA LOADING

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Snowflake's bulk loading command — reads files from a stage and loads into a table in parallel. Much faster than INSERT for large volumes. Uses load metadata to avoid recent duplicate file loads; metadata retention bounds still apply.

Memory Map: What is COPY INTO? -> staged file set -> parallel warehouse readers -> table load metadata -> ON_ERROR contract -> FORCE risk [SF_02_Quick_Recall.md:53].

What is COPY INTO?

Answer First: A pointer to a file location (loading dock). Internal stage = Snowflake's storage. External stage = your ADLS/S3/GCS. Files land in stage, COPY INTO loads them into tables.

Memory Map: What is a Stage? -> internal or external file pointer -> credentials and format -> COPY consumes path -> PURGE or retention choice -> access test [SF_02_Quick_Recall.md:56].

What is a Stage?

Answer First: Auto-ingest loading — monitors a stage and automatically runs COPY INTO when new files arrive. Uses event notifications (Azure Event Grid / AWS SQS). Latency: ~30 seconds to 1 minute.

Memory Map: What is Snowpipe? -> cloud notification -> pipe backlog -> serverless copier -> copy history proof -> batch alternative [SF_02_Quick_Recall.md:59].

What is Snowpipe?

Answer First: Row-level push API (Python/Java SDK) for very low latency ingestion. Data pushed directly to Snowflake rows. Used with Kafka Connect, Flink. Current high-performance Snowpipe Streaming guidance describes queryable latency in seconds, as low as 5 seconds; benchmark the target region and workload.

Memory Map: What is Snowpipe Streaming? -> SDK channel rows -> no staged files -> seconds queryability -> buffering cost -> client error handling [SF_02_Quick_Recall.md:62].

What is Snowpipe Streaming?

Answer First: Use COPY INTO for controlled file batches, Snowpipe for event-driven file ingestion, and Snowpipe Streaming for row ingestion through the current SDK or REST architecture.

Memory Map: COPY INTO vs Snowpipe vs Snowpipe Streaming — when to use which? -> scheduled files -> event files -> streaming rows -> freshness cost matrix -> operational owner [SF_02_Quick_Recall.md:65].

COPY INTO vs Snowpipe vs Snowpipe Streaming — when to use which?

Answer First: For recent files, Snowflake load metadata normally prevents duplicate loading. The metadata expires after 64 days, and FORCE = TRUE can duplicate rows, so durable replay controls still matter.

Memory Map: Is COPY INTO safe to re-run? -> load metadata lookup -> 64-day boundary -> FORCE duplicates -> durable file id -> replay plan [SF_02_Quick_Recall.md:73].

Is COPY INTO safe to re-run?

Answer First: ON_ERROR chooses the failure boundary: ABORT_STATEMENT stops the load, CONTINUE records rejected rows while loading valid rows, and SKIP_FILE rejects an affected file.

Memory Map: What happens with bad rows in COPY INTO? -> ON_ERROR mode -> rejected row validation -> staged file quarantine -> repair reload -> audit trail [SF_02_Quick_Recall.md:76].

What happens with bad rows in COPY INTO?

SECTION 2: STREAMS

🧠 Memory Map: Streams

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: A Snowflake object that tracks row-level changes (INSERT/UPDATE/DELETE) on a table. Returns only the changes since last consumption. Used for incremental ELT pipelines.

Memory Map: What is a Stream? -> change view offset -> source version bookmark -> DML consume advances -> stale when unread -> recreate strategy [SF_02_Quick_Recall.md:123].

What is a Stream?

Answer First: METADATA$ACTION (INSERT/DELETE), METADATA$ISUPDATE (TRUE if part of UPDATE), METADATA$ROW_ID (unique row identifier).

Memory Map: What are the 3 metadata columns in a Stream? -> ACTION kind -> ISUPDATE pair marker -> ROW_ID identity -> merge routing -> audit debug [SF_02_Quick_Recall.md:126].

What are the 3 metadata columns in a Stream?

Answer First: As TWO rows: one DELETE (the old value, ISUPDATE=TRUE) and one INSERT (the new value, ISUPDATE=TRUE). To get current value: filter METADATA$ACTION='INSERT' AND METADATA$ISUPDATE=TRUE.

Memory Map: How does an UPDATE appear in a Standard Stream? -> delete old row -> insert new row -> ISUPDATE true -> merge collapse -> net change reasoning [SF_02_Quick_Recall.md:129].

How does an UPDATE appear in a Standard Stream?

Answer First: Tracks only INSERTs, not UPDATEs or DELETEs. Lighter weight (less metadata overhead). Use when your source table is append-only (like Bronze raw landing tables).

Memory Map: What is an Append-Only Stream? -> insert events only -> lower overhead -> no delete visibility -> external table fit -> limitation [SF_02_Quick_Recall.md:132].

What is an Append-Only Stream?

Answer First: After a successful DML transaction that consumes the stream (usually inside a Task). The consumed rows are no longer in the stream — new changes accumulate from that point forward.

Memory Map: When does a Stream "advance"? -> transaction commits -> consumed rows disappear -> rollback preserves offset -> repeatable read pattern -> monitoring [SF_02_Quick_Recall.md:135].

When does a Stream "advance"?

SECTION 3: TASKS

🧠 Memory Map: Task DAG

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: A scheduled Snowflake object that runs a SQL statement (or stored procedure). Like a cron job inside Snowflake. Tasks can be chained into DAGs for multi-step pipelines.

Memory Map: What is a Task? -> scheduled SQL unit -> cron or dependency -> compute context -> suspend/resume lifecycle -> run history [SF_02_Quick_Recall.md:173].

What is a Task?

Answer First: Define dependencies with AFTER parent_task_name. For initial activation, resume each desired child before the root or call SYSTEM$TASK_DEPENDENTS_ENABLE on the root to enable the graph recursively.

Memory Map: How do you chain Tasks into a pipeline? -> root coordinator -> child dependencies -> enable order -> failure isolation -> graph history [SF_02_Quick_Recall.md:176].

How do you chain Tasks into a pipeline?

Answer First: - Warehouse task: uses your virtual warehouse, same credit rate as queries.

Memory Map: What is the difference between warehouse-based and serverless tasks? -> named compute exposes sizing control -> managed compute targets completion automatically -> metering evidence decides operating cost [SF_02_Quick_Recall.md:179].

What is the difference between warehouse-based and serverless tasks?

Answer First: SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(TASK_NAME => 'task_name')) — shows status (SUCCEEDED/FAILED), start/end time, error messages.

Memory Map: How do you monitor task failures? -> TASK_HISTORY evidence -> error code -> notification integration -> retry policy -> suspend threshold [SF_02_Quick_Recall.md:184].

How do you monitor task failures?

SECTION 4: DYNAMIC TABLES

🧠 Memory Map: Dynamic Tables

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: A Snowflake table defined by a SELECT query and a TARGET_LAG. Its refresh mode defaults to AUTO, which Snowflake resolves at creation to INCREMENTAL or FULL. It can replace eligible declarative Streams + Tasks patterns; explicit CDC and procedural orchestration still have separate use cases.

Memory Map: What is a Dynamic Table? -> maintained query result -> target lag scheduling -> incremental eligibility -> warehouse choice -> dependency graph [SF_02_Quick_Recall.md:228].

What is a Dynamic Table?

Answer First: TARGET_LAG is a best-effort maximum-staleness target relative to base tables. Five minutes does not guarantee a five-minute refresh or SLA; actual lag can exceed the target, and the minimum value is one minute.

Memory Map: What is TARGET_LAG? -> staleness target -> refresh budget -> DOWNSTREAM trigger -> lag observation -> SLA wording [SF_02_Quick_Recall.md:231].

What is TARGET_LAG?

Answer First: Use Dynamic Tables for eligible declarative SQL transformations where Snowflake can manage refreshes from a freshness target. Use Streams + Tasks when you need explicit CDC consumption, procedural control, or operations unsupported by Dynamic Tables.

Memory Map: Dynamic Tables vs Streams + Tasks — when to use which? -> declarative table -> procedural stream task -> edge-case control -> maintenance burden -> rollback [SF_02_Quick_Recall.md:234].

Dynamic Tables vs Streams + Tasks — when to use which?

Answer First: INCREMENTAL refresh processes changes since the prior refresh when the query supports that mode; FULL recomputes the complete result. AUTO is the default and selects one of those modes when the dynamic table is created.

Memory Map: What is INCREMENTAL refresh vs FULL refresh? -> incremental mode merges supported source changes -> full mode recomputes complete output -> resolved mode and change volume drive cost [SF_02_Quick_Recall.md:238].

What is INCREMENTAL refresh vs FULL refresh?

Answer First: Automatically — you don't write MERGE logic. Snowflake detects which rows changed and updates the Dynamic Table accordingly. Much simpler than writing a Stream + MERGE task.

Memory Map: How does a Dynamic Table handle an UPDATE in source? -> source change detected -> refresh computes result -> old row replaced -> lag-dependent visibility -> history check [SF_02_Quick_Recall.md:242].

How does a Dynamic Table handle an UPDATE in source?

SECTION 5: PERFORMANCE

🧠 Memory Map: Performance Tuning

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Visual execution tree in Snowflake UI showing how a query ran — which operators ran, how long each took, bytes scanned, spill to disk. Used to identify bottlenecks.

Memory Map: What is Query Profile? -> operator timeline -> scan versus spill -> skew evidence -> warehouse or SQL fix -> benchmark [SF_02_Quick_Recall.md:286].

What is Query Profile?

Answer First: The virtual warehouse ran out of memory data was written to SSD. Much slower than in-memory. Fix: scale UP to a larger warehouse size.

Memory Map: What does "spilling to disk" mean? -> operator memory becomes insufficient -> intermediate data moves to local or remote storage -> profile evidence justifies memory or query reduction [SF_02_Quick_Recall.md:289].

What does "spilling to disk" mean?

Answer First: A pre-computed table based on a SELECT query. Snowflake auto-refreshes it when source data changes. Best for: heavy aggregations queried repeatedly by many users.

Memory Map: What is a Materialized View? -> repeated aggregate -> storage tradeoff -> supported SQL limits -> maintenance cost -> rewrite proof [SF_02_Quick_Recall.md:292].

What is a Materialized View?

Answer First: A feature that builds an extra index structure for point-lookup queries (WHERE id = 'X', WHERE email = 'user@co.com'). Great for high-cardinality column equality searches.

Memory Map: What is Search Optimization Service? -> point lookup path -> search access service -> selective predicate fit -> service cost -> explain proof [SF_02_Quick_Recall.md:295].

What is Search Optimization Service?

Answer First: - Clustering key range queries (WHERE date BETWEEN ... AND ...), sorted scans.

Memory Map: Clustering key vs Search Optimization — when to use which? -> range scans use clustering -> point lookups use search -> cost signal -> remove if unused -> query proof [SF_02_Quick_Recall.md:298].

Clustering key vs Search Optimization — when to use which?

🧠 FINAL REVISION — Day 2 Summary Card

┌──────────────────────────────────────────────────────────────────┐
│               DAY 2: PIPELINES & PERFORMANCE                      │
├──────────────────────────────────────────────────────────────────┤
│                                                                  │
│  LOADING = "CSP": COPY INTO → Snowpipe → Snowpipe Streaming      │
│  COPY INTO: batch, load-metadata-aware; suited to planned batch loading                         │
│  Snowpipe: auto on file arrival, ~1 min latency                 │
│  Snowpipe Streaming: push API, seconds-scale ingestion via the current SDK or REST architecture                │
│                                                                  │
│  STREAMS: track INSERT/UPDATE/DELETE on tables                  │
│  UPDATE = TWO rows: DELETE(old) + INSERT(new)                   │
│  Filter new value: METADATA$ACTION='INSERT' AND ISUPDATE=TRUE   │
│  Types: Standard (all) / Append-only (insert only)              │
│                                                                  │
│  TASKS: scheduled SQL + AFTER chain = pipeline DAG              │
│  New graph: children before root, or recursive enable function  │
│  Serverless tasks = cost depends on measured workload consumption, no warehouse                  │
│                                                                  │
│  DYNAMIC TABLES: declarative, can simplify eligible declarative pipelines      │
│  TARGET_LAG = best-effort staleness target (min 1 minute)       │
│  INCREMENTAL refresh = only processes changes (preferred)       │
│  ⚠️ Cannot do SCD Type 2 → use Streams+Tasks for that          │
│                                                                  │
│  SNOWPARK: Python/Java/Scala DataFrame API in Snowflake         │
│  Pushes computation to Snowflake — no data leaves               │
│  UDFs: Python functions run inside Snowflake                    │
│  2026: Intelligent Hybrid Execution (auto local vs cloud)       │
│                                                                  │
│  PERFORMANCE = "CQMWS":                                         │
│  Clustering → fix partition pruning (range queries)             │
│  Query Profile → find bottleneck (spill? pruning? concurrency?) │
│  Materialized Views → pre-compute aggregations                  │
│  Warehouse → scale UP (slow) or OUT (concurrent users)         │
│  Search Optimization → fix point lookups (= equality)          │
│                                                                  │
│  TOP 5 THINGS TO SAY IN INTERVIEW:                               │
│  1. "Snowpipe for near-real-time, COPY INTO for batch"          │
│  2. "Streams track CDC: UPDATE = DELETE+INSERT pair"            │
│  3. "Dynamic Tables replace most Streams+Tasks — declarative"   │
│  4. "Snowpark: Python that runs INSIDE Snowflake compute"       │
│  5. "CQMWS checklist: Clustering, Query Profile, MV, WH, Search"│
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Snowflake Security, Sharing, and Cost

Answer First: Operate Snowflake with least-privilege role hierarchies, policy-driven data controls, deliberate sharing boundaries, and cost decisions tied to metering evidence.

Memory Map: role -> privilege -> policy -> share -> meter -> budget -> verify availability.

Day 3: Security, Data Sharing, Cost & New Features 2026

SECTION 1: RBAC — ROLE-BASED ACCESS CONTROL (1 hour)

Answer First: RBAC (Role-Based Access Control) is how Snowflake controls who can access what. Instead of giving permissions directly to users, you give permissions to roles, and then assign roles to users. This makes it easy to manage 1000 users — change the role once, all users inheriting it are updated.

Memory Map: What is RBAC in Snowflake? What are the system roles? -> role graph -> least-privilege grants -> ownership hierarchy -> secondary role caveat -> access history [SF_03_Security_Sharing_New.md:11].

Q1: What is RBAC in Snowflake? What are the system roles?

Simple Explanation: RBAC (Role-Based Access Control) is how Snowflake controls who can access what. Instead of giving permissions directly to users, you give permissions to roles, and then assign roles to users. This makes it easy to manage 1000 users — change the role once, all users inheriting it are updated.

Real-world analogy: In a hotel, instead of giving each employee their own set of physical keys, you give them a "key card type" (role). Reception key cards open lobby, rooms, storage. Manager cards open everything. If you change what a Reception card can open, ALL receptionists are automatically updated.

The core account-level role hierarchy (organization roles also exist):

-- CREATING A CUSTOM ROLE HIERARCHY (best practice)
USE ROLE USERADMIN;

-- Create domain roles
CREATE ROLE bookings_read_role;       -- Can only read booking data
CREATE ROLE bookings_write_role;      -- Can read + write
CREATE ROLE analyst_role;             -- Cross-domain read access

-- Grant privileges to roles (done by SECURITYADMIN)
USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE analytics_db TO ROLE bookings_read_role;
GRANT USAGE ON SCHEMA analytics_db.bookings TO ROLE bookings_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.bookings TO ROLE bookings_read_role;
-- Future tables: automatically grant when new tables are created
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.bookings TO ROLE bookings_read_role;

-- Hierarchy: write role inherits from read role
GRANT ROLE bookings_read_role TO ROLE bookings_write_role;
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics_db.bookings TO ROLE bookings_write_role;

-- Analyst role gets read across schemas
GRANT ROLE bookings_read_role TO ROLE analyst_role;

-- Best practice: assign custom roles UP to SYSADMIN (so SYSADMIN owns them)
GRANT ROLE bookings_read_role TO ROLE SYSADMIN;
GRANT ROLE bookings_write_role TO ROLE SYSADMIN;

-- Assign roles to users
GRANT ROLE analyst_role TO USER krishna_yadav;
GRANT ROLE bookings_write_role TO USER data_pipeline_sp;  -- Service Principal

-- User switches roles
USE ROLE analyst_role;
SELECT * FROM analytics_db.bookings.fact_flights;  -- Works!

Interview tip: "At a large enterprise with 200+ engineers, I'd design 3 tiers of roles: functional roles (bookings_reader, flights_writer), environment roles (prod_access, staging_access), and team roles (data_engineering_role, analytics_role). Team roles inherit functional roles. Engineers are assigned team roles. Separation of duties: SECURITYADMIN manages roles, SYSADMIN manages objects."

Answer First: Dynamic Data Masking hides sensitive column values from users who shouldn't see them — WITHOUT changing the actual stored data. The mask is applied at query time based on the user's role.

Memory Map: What is Data Masking? How does it work? -> policy expression -> context functions -> column attachment -> role-based reveal -> caller test [SF_03_Security_Sharing_New.md:90].

Q2: What is Data Masking? How does it work?

Simple Explanation: Dynamic Data Masking hides sensitive column values from users who shouldn't see them — WITHOUT changing the actual stored data. The mask is applied at query time based on the user's role.

Real-world analogy: Imagine a table with passenger emails. You put a one-way mirror on the email column. Most people see a reflection (masked value). HR managers see through the mirror (real value). The data itself is unchanged — the mirror changes based on who's looking.

-- STEP 1: Create a masking policy
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('HR_ROLE', 'PRIVACY_OFFICER_ROLE')
            THEN val                        -- Show full email to privileged roles
        WHEN CURRENT_ROLE() IN ('ANALYST_ROLE')
            THEN REGEXP_REPLACE(val, '.+\@', '***@')  -- Show ***@domain.com
        ELSE
            '**REDACTED**'                 -- Everyone else: fully redacted
    END;
-- This policy is a SQL function — runs at query time for every row

-- STEP 2: Apply masking policy to a column
ALTER TABLE passengers
    MODIFY COLUMN email
    SET MASKING POLICY email_mask;
-- Now the email column is masked based on the querying role

-- Test: as ANALYST_ROLE
USE ROLE analyst_role;
SELECT email FROM passengers;
-- Returns: ***@gmail.com (masked)

-- Test: as HR_ROLE
USE ROLE hr_role;
SELECT email FROM passengers;
-- Returns: krishna@gmail.com (real value)

-- MULTIPLE PII COLUMNS:
ALTER TABLE passengers MODIFY COLUMN phone SET MASKING POLICY phone_mask;
ALTER TABLE passengers MODIFY COLUMN passport_no SET MASKING POLICY passport_mask;

-- VIEW: See all masking policies applied
SELECT * FROM INFORMATION_SCHEMA.POLICY_REFERENCES
WHERE POLICY_KIND = 'MASKING_POLICY';

Answer First: Row Access Policies control which ROWS a user can see — different users see different rows from the same table. Column masking = show/hide columns. Row Access = show/hide rows.

Memory Map: What are Row Access Policies? -> row predicate -> mapping table -> policy attachment -> CURRENT_ROLE evaluation -> query diff [SF_03_Security_Sharing_New.md:137].

Q3: What are Row Access Policies?

Simple Explanation: Row Access Policies control which ROWS a user can see — different users see different rows from the same table. Column masking = show/hide columns. Row Access = show/hide rows.

Real-world analogy: At a travel platform, multiple airline partners all share the same bookings table. Lufthansa should ONLY see their own bookings, not Air India's. Row Access Policy = a security gate on every row that checks "is this your booking?" before letting you through.

-- STEP 1: Create a mapping table (user role → airline they can see)
CREATE TABLE airline_access_mapping (
    role_name     VARCHAR,
    airline_code  VARCHAR
);
INSERT INTO airline_access_mapping VALUES
    ('LUFTHANSA_ROLE', 'LH'),
    ('AIR_INDIA_ROLE', 'AI'),
    ('EMIRATES_ROLE',  'EK'),
    ('ADMIN_ROLE',     NULL);    -- NULL = see ALL airlines

-- STEP 2: Create Row Access Policy
CREATE ROW ACCESS POLICY airline_row_policy
    AS (airline_code VARCHAR) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1
        FROM airline_access_mapping m
        WHERE m.role_name = CURRENT_ROLE()
          AND (m.airline_code = airline_code   -- Match role to airline
               OR m.airline_code IS NULL)       -- Or admin (sees all)
    );

-- STEP 3: Apply to table
ALTER TABLE bookings
    ADD ROW ACCESS POLICY airline_row_policy ON (airline_code);

-- Test: Lufthansa user
USE ROLE LUFTHANSA_ROLE;
SELECT * FROM bookings;  -- Returns ONLY LH bookings, even though table has all airlines

-- Test: Admin
USE ROLE ADMIN_ROLE;
SELECT * FROM bookings;  -- Returns ALL bookings

SECTION 2: DATA SHARING (1 hour)

Answer First: Secure Data Sharing lets you share your Snowflake data with another Snowflake account — WITHOUT copying data. The recipient queries your tables directly, always getting live data. They pay for their own compute; you pay for your storage.

Memory Map: What is Secure Data Sharing? How does it work? -> share object provider -> consumer database -> no data copy -> compute isolation -> revoke audit [SF_03_Security_Sharing_New.md:184].

Q4: What is Secure Data Sharing? How does it work?

Simple Explanation: Secure Data Sharing lets you share your Snowflake data with another Snowflake account — WITHOUT copying data. The recipient queries your tables directly, always getting live data. They pay for their own compute; you pay for your storage.

Real-world analogy: Instead of emailing a spreadsheet copy to a partner airline (stale data, security risk, multiple copies), you give them a secure link to YOUR Google Sheet. They always see the latest version. You control what they can access. No copy exists.

-- PROVIDER SIDE (travel platform shares booking summary with Lufthansa)

-- Step 1: Create a share object
CREATE SHARE lufthansa_share
    COMMENT = 'Lufthansa booking analytics share';

-- Step 2: Grant database and schema to share
GRANT USAGE ON DATABASE analytics_db TO SHARE lufthansa_share;
GRANT USAGE ON SCHEMA analytics_db.bookings TO SHARE lufthansa_share;

-- Step 3: Grant specific tables/views to share (NOT entire database)
GRANT SELECT ON VIEW analytics_db.bookings.lufthansa_bookings_vw TO SHARE lufthansa_share;
-- Share a VIEW (not the raw table) so you control exactly what they see
-- View can filter: WHERE airline_code = 'LH' — Lufthansa sees only their data

-- Step 4: Add recipient (Lufthansa's Snowflake account)
ALTER SHARE lufthansa_share ADD ACCOUNTS = 'lufthansa.eu-west.snowflakecomputing.com';

-- RECIPIENT SIDE (Lufthansa's Snowflake account)
-- Step 1: Create database from the share
CREATE DATABASE travelco_share FROM SHARE travelco.lufthansa_share;

-- Step 2: Query it like a normal database!
SELECT * FROM travelco_share.bookings.lufthansa_bookings_vw;
-- Returns the provider's live data directly — no copy!
-- Lufthansa pays their own compute, the provider pays storage

Data Sharing types:

SECTION 3: COST MANAGEMENT

Answer First: Control cost by attributing warehouse, serverless, cloud-services, and storage usage; then right-size, suspend idle compute, set budgets or monitors, and verify savings in metering views.

Memory Map: How do you manage and optimize Snowflake costs? -> metering attributes compute and storage -> workload policies suspend and right-size -> budgets and monitors contain exceptions [SF_03_Security_Sharing_New.md:233].

Q5: How do you manage and optimize Snowflake costs?

The COST CONTROL FRAMEWORK ("WSCRA"):

-- W: WAREHOUSE OPTIMIZATION
ALTER WAREHOUSE etl_wh SET
    AUTO_SUSPEND = 120           -- Suspend after 2 minutes idle (default is 10 min)
    AUTO_RESUME = TRUE;          -- Auto-start on query (don't forget this!)

-- Create right-sized warehouses per workload
CREATE WAREHOUSE dev_wh   SIZE = 'XSMALL' AUTO_SUSPEND = 60;  -- Dev: tiny, fast suspend
CREATE WAREHOUSE bi_wh    SIZE = 'MEDIUM' AUTO_SUSPEND = 300;  -- BI: keep warm longer (cache!)
CREATE WAREHOUSE etl_wh   SIZE = 'LARGE'  AUTO_SUSPEND = 120;  -- ETL: large but fast suspend

-- S: STORAGE OPTIMIZATION
-- Transient tables: NO fail-safe (7-day window removed), cheaper storage
-- Use for: staging tables, temp tables, data you can reload
CREATE TRANSIENT TABLE staging_bookings (...);    -- No fail-safe period
CREATE TRANSIENT SCHEMA staging;                  -- All tables in schema are transient

-- Reduce time travel for non-critical tables (reduce storage cost)
ALTER TABLE staging_bookings SET DATA_RETENTION_TIME_IN_DAYS = 0;
ALTER TABLE audit_log SET DATA_RETENTION_TIME_IN_DAYS = 7;    -- Only 7 days
ALTER TABLE fact_bookings SET DATA_RETENTION_TIME_IN_DAYS = 30; -- 30 days for prod

-- C & R: RESOURCE MONITORS (spend limits with alerts)
CREATE RESOURCE MONITOR monthly_cap
    WITH CREDIT_QUOTA = 1000               -- 1000 credits per month max
    FREQUENCY = MONTHLY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
        ON 75 PERCENT DO NOTIFY            -- Email alert at 75% usage
        ON 90 PERCENT DO NOTIFY            -- Email alert at 90%
        ON 100 PERCENT DO SUSPEND;         -- Auto-suspend all warehouses at 100%!

ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = monthly_cap;
-- Now BI warehouse auto-suspends if team exceeds 1000 credits/month

Cost breakdown reference:

SECTION 4: NEW FEATURES 2025-2026

Answer First: Snowflake had a massive product year in 2025-2026, moving hard into AI and open formats. Key themes: AI-native (Cortex AI everywhere), open format (Iceberg/Polaris), and OLTP (Hybrid Tables). Mentioning these in interviews shows you're current.

Memory Map: What are the major Snowflake features from 2025-2026? -> official availability -> workload fit -> migration risk -> cost guardrail -> pilot metric [SF_03_Security_Sharing_New.md:314].

Q6: What are the major Snowflake features from 2025-2026?

Simple Explanation: Snowflake had a massive product year in 2025-2026, moving hard into AI and open formats. Key themes: AI-native (Cortex AI everywhere), open format (Iceberg/Polaris), and OLTP (Hybrid Tables). Mentioning these in interviews shows you're current.

Answer First: Gen2 standard warehouses use newer hardware and software optimizations; availability, consumption, and performance vary, so benchmark the target workload and region.

Memory Map: Gen 2 Warehouses (2025) -> generation selects newer execution resources -> regional support constrains adoption -> workload benchmark validates price-performance [SF_03_Security_Sharing_New.md:321].

Gen 2 Warehouses (2025)

Answer First: Cortex AI exposes managed AI capabilities in Snowflake, but model, function, region, security, and cost availability must be verified for the target account.

Memory Map: Cortex AI (GA 2025 expanded 2026) -> model function surface -> governed inputs -> region availability -> cost and latency -> prompt audit [SF_03_Security_Sharing_New.md:337].

Cortex AI (GA 2025 → expanded 2026)

-- Use Cortex AI in a SQL query
SELECT
    booking_id,
    customer_feedback,
    SNOWFLAKE.CORTEX.SENTIMENT(customer_feedback)          AS sentiment_score,
    -- Returns -1 (negative) to +1 (positive)
    SNOWFLAKE.CORTEX.SUMMARIZE(customer_feedback)          AS feedback_summary,
    SNOWFLAKE.CORTEX.CLASSIFY_TEXT(customer_feedback,
        ['complaint', 'compliment', 'inquiry'])             AS feedback_category
FROM customer_feedback_table
WHERE booking_date > '2026-01-01';
-- ALL processing happens inside Snowflake — no API calls, no data leaving

Answer First: Iceberg tables and Polaris provide open table-format and catalog interoperability; validate ownership mode, engine compatibility, refresh behavior, and governance boundaries.

Memory Map: Apache Iceberg Tables + Polaris Open Catalog (GA 2025-2026) -> open table format -> catalog interoperability -> Snowflake-managed access -> cross-engine governance -> compatibility test [SF_03_Security_Sharing_New.md:374].

Apache Iceberg Tables + Polaris Open Catalog (GA 2025-2026)

-- Create an Iceberg table (data in YOUR ADLS, Snowflake manages catalog)
CREATE ICEBERG TABLE iceberg_bookings (
    booking_id    VARCHAR,
    booking_date  DATE,
    airline_code  VARCHAR,
    amount        NUMBER(10,2)
)
    CATALOG = 'SNOWFLAKE'              -- Snowflake is the catalog
    EXTERNAL_VOLUME = 'my_adls_vol'   -- Where files stored in ADLS
    BASE_LOCATION = 'bookings/';      -- Path within volume
-- Now both Snowflake AND Databricks can read the same Parquet+Iceberg files!

Answer First: Hybrid Tables add transactional row-serving behavior alongside analytics; use them only after validating regional availability, quotas, latency, and the transactional access pattern.

Memory Map: Hybrid Tables / Unistore (GA 2025) -> row-oriented workload -> primary-key access -> transactional limits -> HTAP boundary -> latency proof [SF_03_Security_Sharing_New.md:412].

Hybrid Tables / Unistore (GA 2025)

-- Create a Hybrid Table (OLTP+OLAP)
CREATE HYBRID TABLE live_bookings (
    booking_id    VARCHAR PRIMARY KEY,    -- Primary key (enforced, indexed)
    passenger_id  VARCHAR,
    booking_date  DATE,
    amount        NUMBER(10,2),
    status        VARCHAR,
    INDEX idx_passenger (passenger_id)    -- Secondary index (fast lookup)
);

-- Fast single-row lookup (OLTP-style)
SELECT * FROM live_bookings WHERE booking_id = 'BK12345678';
-- Uses row store → milliseconds response

-- Analytics (OLAP-style)
SELECT DATE_TRUNC('month', booking_date), SUM(amount)
FROM live_bookings
GROUP BY 1;
-- Uses column store → fast scan

Answer First: Snowflake Postgres provides managed Postgres instances on dedicated virtual machines and is GA only in selected AWS and Azure regions as of the cited 2026 release note.

Memory Map: Snowflake Postgres (GA February 2026) -> managed Postgres surface -> selected regions -> application fit -> replication pathway -> GA caveat [SF_03_Security_Sharing_New.md:454].

Snowflake Postgres (GA February 2026)

SECTION 5: MOCK INTERVIEW — TOP 10 QUESTIONS

Q7: 10 Most-Likely Snowflake Interview Questions

Mock Q1: "How is Snowflake different from Redshift or Synapse?"

Mock Q2: "How would you design a data sharing solution for 200 airline partners?"

Mock Q3: "How do you handle GDPR right to be forgotten?"

Mock Q4: "A query takes 10 minutes, how do you debug it?"

Mock Q5: "What's your experience with Streams and Tasks?"

Mock Q6: "How do Snowflake Virtual Warehouses scale?"

Mock Q7: "What are Snowflake's new features you're excited about?"

Mock Q8: "How would you implement column-level security for PII?"

-- Create masking policy
CREATE MASKING POLICY pii_email_mask AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('PRIVACY_OFFICER', 'DATA_STEWARD') THEN val
        WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') THEN REGEXP_REPLACE(val, '.+@', '***@')
        ELSE '**REDACTED**'
    END;

-- Apply to column
ALTER TABLE passengers MODIFY COLUMN email SET MASKING POLICY pii_email_mask;

-- Combine with Row Access Policy (airlines see only their rows + masked PII)

Mock Q9: "COPY INTO vs INSERT — when to use which?"

Mock Q10: "How do you reduce Snowflake costs for a team of 200 engineers?"

Day 3: Security, Sharing & New Features — Quick Recall Guide

🧠 MASTER MEMORY MAP — Day 3

SECTION 1: RBAC

🧠 Memory Map: Role Hierarchy

          ACCOUNTADMIN  ← Top of pyramid, reserved for 2-3 people
               │
        ┌──────┴──────┐
   SECURITYADMIN   SYSADMIN   ← Most important admin roles
        │               │
   USERADMIN        Custom Role Hierarchy:
                         SYSADMIN
                           └── team_role (e.g., data_engineering)
                                 └── functional_role (e.g., bookings_reader)
                                       └── privileges on objects

RULE: Custom roles float UP to SYSADMIN
      So SYSADMIN (and ACCOUNTADMIN above it) always has oversight

WHO DOES WHAT:
    ACCOUNTADMIN → billing, account settings, emergency only
    SECURITYADMIN → grant/revoke privileges on objects
    USERADMIN → create users, create roles (no data privileges)
    SYSADMIN → create databases, schemas, tables, warehouses
    PUBLIC → minimum, given to all users automatically

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Role-Based Access Control — grant permissions to ROLES (not individual users), then assign roles to users. One role change updates all users who have it.

Memory Map: What is RBAC in Snowflake? -> privileges to roles -> roles to users -> active role context -> ownership transfer -> audit query [SF_03_Quick_Recall.md:71].

What is RBAC in Snowflake?

Answer First: The five core account-hierarchy roles are ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, and PUBLIC; organization, database, and application roles exist outside that mnemonic.

Memory Map: What are the 5 system-defined roles? -> ACCOUNTADMIN boundary -> SYSADMIN objects -> SECURITYADMIN grants -> USERADMIN identities -> ORGADMIN separate [SF_03_Quick_Recall.md:74].

What are the 5 system-defined roles?

Answer First: - ACCOUNTADMIN: see billing, all objects, account settings (use sparingly!).

Memory Map: What does each system role do? -> object management -> user lifecycle -> security grants -> account break-glass -> custom-role overlay [SF_03_Quick_Recall.md:77].

What does each system role do?

Answer First: Using ACCOUNTADMIN as the default role for data engineers. Best practice: grant ACCOUNTADMIN to 2-3 senior admins, set their DEFAULT ROLE to SYSADMIN. ACCOUNTADMIN should only be used when explicitly needed.

Memory Map: What is the #1 RBAC mistake? -> direct user grant -> hierarchy bypass -> audit complexity -> grant to role -> revoke cleanup [SF_03_Quick_Recall.md:84].

What is the #1 RBAC mistake?

Answer First: GRANT SELECT ON FUTURE TABLES IN SCHEMA bookings TO ROLE analyst_role; — Automatically grants SELECT on any NEW tables created in that schema. Without this, every new table needs a manual grant.

Memory Map: What is FUTURE GRANTS? -> schema object template -> privilege inheritance -> ownership exceptions -> migration surprise -> SHOW GRANTS check [SF_03_Quick_Recall.md:87].

What is FUTURE GRANTS?

Answer First: USE ROLE analyst_role; — Switch to a different role in your session. Your access changes immediately.

Memory Map: How do you switch roles? -> USE ROLE context -> secondary role option -> worksheet session scope -> permission error debug -> least privilege [SF_03_Quick_Recall.md:90].

How do you switch roles?

🔑 MID-LEVEL QUESTIONS

Answer First: ACCOUNTADMIN (2-3 people: CTO, Lead Architect).

Memory Map: Design a role hierarchy for 200 engineers at a large enterprise -> functional roles -> environment roles -> service identities -> break-glass admin -> periodic review [SF_03_Quick_Recall.md:97].

Design a role hierarchy for 200 engineers at a large enterprise

SECTION 2: DATA MASKING & ROW ACCESS

🧠 Memory Map: Security Policies

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: A column-level security feature that shows different values to different roles. HR sees real email, analysts see *@domain.com. Data is unchanged in storage — masking applies at query time.

Memory Map: What is Dynamic Data Masking? -> column policy -> caller context -> masked expression -> authorized reveal -> BI validation [SF_03_Quick_Recall.md:156].

What is Dynamic Data Masking?

Answer First: Controls which rows different roles can see. Lufthansa sees only their bookings, Air India sees only theirs, even from the same table. Applied via: ALTER TABLE t ADD ROW ACCESS POLICY p ON (airline_code).

Memory Map: What is a Row Access Policy? -> predicate function -> row filter bind -> mapping lookup -> owner testing -> EXPLAIN caution [SF_03_Quick_Recall.md:159].

What is a Row Access Policy?

Answer First: - Column masking: controls WHAT VALUE you see in a specific column (hide PII).

Memory Map: Column masking vs Row Access — what's the difference? -> column redaction -> row filtering -> combined evaluation -> performance review -> test matrix [SF_03_Quick_Recall.md:162].

Column masking vs Row Access — what's the difference?

Answer First: CURRENT_ROLE() returns the active role of the user running the query. Used inside masking and row access policies to make decisions dynamically at query time.

Memory Map: How does CURRENT_ROLE() work in policies? -> active primary role -> secondary-role nuance -> mapping join -> deterministic predicate -> role simulation [SF_03_Quick_Recall.md:166].

How does CURRENT_ROLE() work in policies?

SECTION 3: DATA SHARING

🧠 Memory Map: Secure Data Sharing

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Share live Snowflake data with another Snowflake account WITHOUT copying. Recipient queries your data directly using their own warehouse. No ETL, no copies, always fresh.

Memory Map: What is Secure Data Sharing? -> provider grants share objects without copying -> consumer database references live data -> consumer warehouse pays query compute [SF_03_Quick_Recall.md:202].

What is Secure Data Sharing?

Answer First: A Snowflake-managed account created for a data recipient who doesn't have their own Snowflake. Snowflake creates and manages it; the recipient connects and queries shared data.

Memory Map: What is a Reader Account? -> provider-managed consumer -> share data only -> provider pays compute -> onboarding boundary -> usage monitor [SF_03_Quick_Recall.md:205].

What is a Reader Account?

Answer First: A secure environment for joint data analysis without either party seeing the other's raw data. Example: a travel platform + airline analyze booking patterns without the platform seeing airline's customer list or vice versa. GDPR-safe.

Memory Map: What is a Data Clean Room? -> governed collaboration -> policy-protected joins -> aggregate outputs -> no raw exchange -> consent workflow [SF_03_Quick_Recall.md:208].

What is a Data Clean Room?

Answer First: Provider pays for storage. Recipient pays for their own compute (virtual warehouse). Provider's compute is NOT used for recipient queries.

Memory Map: Who pays for what in Data Sharing? -> provider funds retained storage and services -> consumer funds warehouse execution -> reader accounts shift compute billing to provider [SF_03_Quick_Recall.md:211].

Who pays for what in Data Sharing?

SECTION 4: COST MANAGEMENT

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: A Snowflake object that sets credit limits on warehouses. Can trigger: notify at 75%, suspend at 100%. Prevents surprise bills. Apply per warehouse or account-wide.

Memory Map: What is a Resource Monitor? -> credit quota tracks assigned warehouses -> thresholds notify or suspend -> account budgets cover broader serverless spending [SF_03_Quick_Recall.md:220].

What is a Resource Monitor?

Answer First: A table with NO fail-safe period (saves 7 days × storage cost). Use for staging/temp data that can be reloaded. CREATE TRANSIENT TABLE staging_data (...).

Memory Map: What is a Transient Table? -> no fail-safe -> shorter recovery window -> storage cost reduction -> data criticality check -> retention policy [SF_03_Quick_Recall.md:223].

What is a Transient Table?

Answer First: Not setting auto-suspend on warehouses. Warehouses running idle 24/7 consume credits even with no queries. Set auto-suspend to 60-120 seconds for dev, 5-10 minutes for BI.

Memory Map: What is the biggest Snowflake cost mistake? -> idle warehouse continues credit consumption -> auto-suspend closes unused intervals -> metering confirms eliminated runtime [SF_03_Quick_Recall.md:226].

What is the biggest Snowflake cost mistake?

Answer First: 1. Transient tables for staging (no fail-safe).

Memory Map: How do you optimize storage cost? -> table type choice -> retention trim -> clone cleanup -> fail-safe awareness -> bytes audit [SF_03_Quick_Recall.md:229].

How do you optimize storage cost?

SECTION 5: NEW 2025-2026 FEATURES

🧠 Memory Map: New Features

⚡ MUST KNOW DIRECT QUESTIONS

Answer First: Next-generation virtual warehouses with newer hardware and software optimizations for analytics and engineering workloads. Verify regional availability, generation-specific consumption, account defaults, and measured workload performance.

Memory Map: What are Gen 2 Warehouses? -> generation changes execution hardware and optimizations -> service table defines consumption -> regional benchmark proves workload benefit [SF_03_Quick_Recall.md:275].

What are Gen 2 Warehouses?

Answer First: Snowflake's managed AI service. Run LLM functions directly in SQL: SNOWFLAKE.CORTEX.SENTIMENT(text), CORTEX.SUMMARIZE(text), CORTEX.COMPLETE('llama3', prompt). No data leaves Snowflake.

Memory Map: What is Cortex AI? -> SQL AI functions -> governed data input -> model availability -> token cost -> result review [SF_03_Quick_Recall.md:278].

What is Cortex AI?

Answer First: Hybrid search service (keyword + semantic vector search) on Snowflake text columns. GA March 12, 2026: now supports multiple searchable columns per service + pre-computed custom embeddings.

Memory Map: What is Cortex Search? What's new in 2026? -> text/vector index -> retrieval endpoint -> freshness window -> app integration -> relevance test [SF_03_Quick_Recall.md:281].

What is Cortex Search? What's new in 2026?

Answer First: Tables where data lives in YOUR cloud storage (ADLS/S3) in open Apache Iceberg format. Snowflake manages the catalog. Both Snowflake AND external engines (Databricks, Spark, Trino) read the same files.

Memory Map: What are Iceberg Tables? -> external metadata -> open file layout -> Snowflake optimization -> catalog choice -> engine interoperability [SF_03_Quick_Recall.md:284].

What are Iceberg Tables?

Answer First: Snowflake's managed implementation of Apache Polaris — an open-source Iceberg REST catalog. Any Iceberg-compatible engine connects to it. One catalog, many engines, no data copies.

Memory Map: What is Polaris Open Catalog? -> open catalog service -> REST interoperability -> access control model -> Iceberg clients -> governance decision [SF_03_Quick_Recall.md:287].

What is Polaris Open Catalog?

Answer First: Snowflake tables that combine a row store (for fast OLTP point lookups) with a column store (for OLAP analytics). Supports primary keys, indexes, foreign keys. Use case: operational + analytics in one table.

Memory Map: What are Hybrid Tables? -> operational row lookup -> primary key enforcement -> transactional DML -> not analytic scan default -> design fit [SF_03_Quick_Recall.md:290].

What are Hybrid Tables?

Answer First: Managed PostgreSQL databases running inside Snowflake (GA February 2026). Each instance is a dedicated Postgres server VM managed by Snowflake. Application DB + Snowflake analytics = one unified platform.

Memory Map: What is Snowflake Postgres? -> OLTP familiar API -> Snowflake-managed service -> region availability -> integration path -> workload split [SF_03_Quick_Recall.md:293].

What is Snowflake Postgres?

🧠 FINAL REVISION — Day 3 Summary Card

┌──────────────────────────────────────────────────────────────────┐
│               DAY 3: SECURITY, SHARING & NEW FEATURES             │
├──────────────────────────────────────────────────────────────────┤
│                                                                  │
│  RBAC = roles, not users. five core account-hierarchy roles: "ASUSP"               │
│  ACCOUNTADMIN = most powerful, use sparingly (⚠️ not daily use)  │
│  SECURITYADMIN = manage grants | SYSADMIN = create objects       │
│  Custom roles should inherit UP to SYSADMIN                     │
│                                                                  │
│  MASKING POLICY = different roles see different values           │
│  ROW ACCESS POLICY = different roles see different rows          │
│  Combine both = full data isolation (GDPR + multi-tenant)       │
│                                                                  │
│  DATA SHARING = PSR (Provider, Share, Recipient)                 │
│  No data copy — recipient reads live from provider storage       │
│  Provider pays storage, Recipient pays compute                  │
│  Reader Account = for recipients without Snowflake              │
│  Data Clean Room = joint analytics without raw PII exposure     │
│                                                                  │
│  COST = "WSCRA": Warehouses, Storage, Credits, Resource, Arch   │
│  ⚠️ Biggest mistake: forgetting auto-suspend!                    │
│  Transient tables = no fail-safe (cheaper storage)              │
│  Resource Monitors = hard credit limits per warehouse           │
│                                                                  │
│  NEW 2025-2026 = "GCIHP":                                        │
│  Gen2 (designed for workload-dependent performance improvements) | Cortex AI (LLM in SQL)                   │
│  Iceberg+Polaris (open format, cross-engine)                    │
│  Hybrid Tables (OLTP+OLAP) | Postgres (GA Feb 2026)            │
│  Iceberg on ADLS Gen2 (Preview March 2026 — great for Azure!)  │
│                                                                  │
│  TOP 5 THINGS TO SAY IN INTERVIEW:                               │
│  1. "RBAC: custom roles inherit up to SYSADMIN"                 │
│  2. "Masking = hide values; Row Access = hide rows (combine!)"  │
│  3. "Secure Sharing: live data, no copy, provider pays storage" │
│  4. "Resource monitors with hard limits prevent surprise bills" │
│  5. "Gen2, Dynamic Tables, Iceberg — shows I'm current in 2026" │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

🧠 SNOWFLAKE ULTRA CHEAT SHEET (Read 10 min before interview)

┌──────────────────────────────────────────────────────────────────┐
│             ALL 3 DAYS — SNOWFLAKE LAST-MINUTE RECALL             │
├──────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ARCHITECTURE = 3 layers "BMS": Brain(Cloud Svcs) + Muscles(WH) │
│  + Storage. Key: compute & storage SEPARATE → scale each alone  │
│  Micro-partitions: AUTO, 50-500 MB, min/max metadata → pruning   │
│  Virtual WH: scale UP(slow query) vs OUT(many users)            │
│  Cache "RLS": Result(24h,free) → Local(SSD,cleared on suspend)  │
│  Time Travel: 0-90 days | Fail-safe: 7 days (Snowflake only)   │
│  Clone: instant copy, no data duplicated, shared micro-parts    │
│  VARIANT: any JSON, access with col:key::VARCHAR, FLATTEN arrays │
│                                                                  │
│  PIPELINES: "CSP" = COPY INTO → Snowpipe → Snowpipe Streaming   │
│  Streams: track INSERT/UPDATE/DELETE (UPDATE=2 rows!)           │
│  Tasks: chain with AFTER; initially children then root, or recursive enable │
│  Dynamic Tables: declarative ELT (can simplify eligible declarative pipelines)  │
│  TARGET_LAG min = 1 minute, best-effort; AUTO chooses refresh mode │
│  Snowpark: Python DataFrames run INSIDE Snowflake               │
│  Performance: "CQMWS" = Cluster, Query profile, MV, WH, Search │
│                                                                  │
│  SECURITY: "RARM" = RBAC, ACCOUNTADMIN hierarchy, Row, Mask    │
│  5 roles "ASUSP": ACCOUNTADMIN, SEC, USERADMIN, SYS, PUBLIC    │
│  Masking = hide column values | Row Access = hide rows          │
│  Sharing: PSR, no copy, provider=storage, recipient=compute     │
│  Cost: auto-suspend!, transient tables, resource monitors       │
│                                                                  │
│  NEW 2026 "GCIHP": Gen2, Cortex AI, Iceberg/Polaris,           │
│  Hybrid Tables (OLTP+OLAP), Postgres (GA Feb 2026)              │
│  Iceberg on ADLS Gen2 = open format for Azure-first teams       │
│                                                                  │
│  SNOWFLAKE vs DATABRICKS:                                        │
│  Snowflake = SQL warehouse, BI, sharing, minimal code           │
│  Databricks = ETL/ELT, Python, ML, streaming, open formats     │
│  BOTH TOGETHER: Databricks (ETL) → Snowflake (analytics/BI)    │
│                                                                  │
│  FRAME WITH TRAVEL DOMAIN:                                       │
│  "At enterprise scale with 10B bookings/year, many airlines..." │
│  "For GDPR with passenger PII across 100+ countries..."        │
│  "For real-time fare pricing with seconds-scale latency..."        │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Snowflake Scenarios, Labs, and Production Traps

Answer First: Use each scenario as an evidence-led drill: state the workload contract, inspect Snowflake telemetry, change one control, and verify latency, correctness, and cost.

Memory Map: contract -> symptom -> evidence -> hypothesis -> safe change -> verification.

Answer First: The video-free pack. Read this end-to-end and you can walk into any Snowflake interview without ever opening YouTube.

Memory Map: Snowflake — Confusions, Labs, Gotchas & Mock Interview -> contract -> symptom -> evidence -> hypothesis -> safe change -> verify [SF_04_Confusions_Labs_MockInterview.md:1].

Snowflake — Confusions, Labs, Gotchas & Mock Interview

🧠 Memory Map: WAREHOUSE-STAGE-TIME

Snowflake boils down to 3 mental pillars. Remember WST:

If you can draw these 3 pillars on a whiteboard and connect them, you understand Snowflake.

SECTION 1 — TOP 8 CONFUSIONS CLEARED

Answer First: Each tier = 2× the previous. A 4X-Large running 1 hour = 128 credits ≈ $256-$512 depending on edition.

Memory Map: Confusion #1 — Scale UP vs Scale OUT (Warehouse sizing vs Multi-cluster) -> slow single query -> queueing users -> size up versus clusters out -> cache cost side effect -> metric [SF_04_Confusions_Labs_MockInterview.md:23].

Confusion #1 — Scale UP vs Scale OUT (Warehouse sizing vs Multi-cluster)

Credit cost (per hour):

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."

Answer First: Mnemonic: User, Table, Named all internal. External points to S3/Azure/GCS.

Memory Map: Confusion #2 — Internal Stage vs External Stage vs Named Stage vs Table Stage vs User Stage -> user/table/named stages -> internal storage or external URL -> credential ownership -> COPY path -> access boundary [SF_04_Confusions_Labs_MockInterview.md:47].

Confusion #2 — Internal Stage vs External Stage vs Named Stage vs Table Stage vs User Stage

Five stage types. Memorize this table:

Mnemonic: User, Table, Named → all internal. External points to S3/Azure/GCS.

Quick example:

-- External stage pointing to S3
CREATE STAGE raw_s3
  URL = 's3://my-bucket/raw/'
  STORAGE_INTEGRATION = my_s3_int
  FILE_FORMAT = (TYPE = 'CSV');

-- List files
LIST @raw_s3;

-- Load
COPY INTO orders FROM @raw_s3/orders/ PATTERN = '.*orders.*[.]csv';

Answer First: Use COPY for loading staged batches, Snowpipe for event-triggered files, external tables for query-in-place access, and Streams plus Tasks for processing table changes after ingestion.

Memory Map: Confusion #3 — COPY INTO vs Snowpipe vs External Tables vs Streams & Tasks -> batch files -> event files -> external table metadata -> stream/task transform -> latency owner [SF_04_Confusions_Labs_MockInterview.md:78].

Confusion #3 — COPY INTO vs Snowpipe vs External Tables vs Streams & Tasks

All four move data. When do I use which?

Memory rule: Data OUTSIDE Snowflake arriving in files → Snowpipe. Data INSIDE Snowflake that changes → Streams.

Interview question: "What if I need seconds-scale latency?" → Snowpipe Streaming (row-by-row API, <1 second latency). If they don't ask, don't mention — it's newer and optional.

Answer First: Killer fact: Clone is instant because Snowflake just copies metadata pointers to the same micro-partitions. Storage grows only when the clone diverges from original.

Memory Map: Confusion #4 — Time Travel vs Fail-safe vs Clone -> query old version -> support-only fail-safe -> clone snapshot -> storage growth -> incident drill [SF_04_Confusions_Labs_MockInterview.md:96].

Confusion #4 — Time Travel vs Fail-safe vs Clone

All three are "get my data back." But they differ:

┌──────────────────────────────────────────────────────────────┐
│  Day 0 ────── Day 1 ────── Day 90 ────── Day 97             │
│     │           │            │              │               │
│     │      TIME TRAVEL  FAIL-SAFE       DATA GONE           │
│     │      (user can     (Snowflake                         │
│     │       query)        support only)                     │
└──────────────────────────────────────────────────────────────┘

Code:

-- Point-in-time query
SELECT * FROM orders AT(TIMESTAMP => '2026-04-01 10:00:00'::timestamp);

-- Undrop
UNDROP TABLE orders;

-- Clone a 10 TB table in 1 second (ZERO extra storage until clone is modified)
CREATE TABLE orders_dev CLONE orders;

Killer fact: Clone is instant because Snowflake just copies metadata pointers to the same micro-partitions. Storage grows only when the clone diverges from original.

Answer First: Snowflake has NO traditional partitions. But it DOES have micro-partitions.

Memory Map: Confusion #5 — Micro-partitions vs Clustering Keys vs Partitioning (traditional) -> automatic micro chunks -> clustering hint -> no folder partitions -> pruning evidence -> recluster caveat [SF_04_Confusions_Labs_MockInterview.md:132].

Confusion #5 — Micro-partitions vs Clustering Keys vs Partitioning (traditional)

Snowflake has NO traditional partitions. But it DOES have micro-partitions.

When to set clustering key:

• Table > 1 TB
• Queries filter on a specific column 80%+ of the time
• Column has HIGH cardinality (date, customer_id)
• Natural load order DOESN'T match query order

Check clustering health:

SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date)');
-- Returns JSON: average_overlaps, average_depth, etc. Lower = better.

Interview one-liner: "Clustering key tells Snowflake HOW to group rows into micro-partitions. Don't set it unless the table is huge AND queries filter on one specific column."

Answer First: Warehouse left ON after hours set AUTO_SUSPEND = 60 (seconds).

Memory Map: Confusion #6 — Warehouse credits vs Storage cost vs Cloud services cost -> running compute consumes warehouse credits -> retained bytes accrue storage charges -> daily adjustment governs cloud-services compute [SF_04_Confusions_Labs_MockInterview.md:158].

Confusion #6 — Warehouse credits vs Storage cost vs Cloud services cost

Your Snowflake bill has 3 lines:

Biggest cost traps:

1. Warehouse left ON after hours → set AUTO_SUSPEND = 60 (seconds)
2. 90-day Time Travel on huge table → evaluate if 1 day is enough
3. Too-large warehouse for tiny queries → scale UP only when needed

Per-second billing rule: Min 60 seconds, then per-second. So resuming + 1 query = 60s minimum charge.

Answer First: Interview gold: If they ask "dedup keeping latest," answer with QUALIFY — it's 50% less code than the subquery approach.

Memory Map: Confusion #7 — Standard SQL vs Snowflake extensions (VARIANT, FLATTEN, QUALIFY) -> semi-structured access -> arrays to rows -> window filter shortcut -> portability tradeoff -> interview contrast [SF_04_Confusions_Labs_MockInterview.md:177].

Confusion #7 — Standard SQL vs Snowflake extensions (VARIANT, FLATTEN, QUALIFY)

Snowflake adds SQL features standard databases don't have:

VARIANT — store any JSON/semi-structured in a single column:

CREATE TABLE events (id INT, payload VARIANT);
INSERT INTO events VALUES (1, PARSE_JSON('{"user":"alice","action":"click"}'));

-- Query JSON with dot notation
SELECT payload:user::STRING AS user, payload:action::STRING AS action FROM events;
-- Output:
-- user  | action
-- alice | click

FLATTEN — explode arrays:

SELECT value FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[1,2,3]')));
-- Output: 1, 2, 3 (3 rows)

QUALIFY — filter window functions (like HAVING but for window funcs):

-- Get latest row per user (without a subquery!)
SELECT * FROM events
QUALIFY ROW_NUMBER() OVER (PARTITION BY user ORDER BY ts DESC) = 1;

Interview gold: If they ask "dedup keeping latest," answer with QUALIFY — it's 50% less code than the subquery approach.

Answer First: Pro tip: ALTER WAREHOUSE wh SET AUTO_SUSPEND = 60 — saves HUGE money for dev warehouses.

Memory Map: Confusion #8 — Warehouse states: SUSPENDED vs STARTED vs RESIZING -> suspended state stops cluster compute -> started state serves queued work -> resizing transitions resources while queries continue [SF_04_Confusions_Labs_MockInterview.md:210].

Confusion #8 — Warehouse states: SUSPENDED vs STARTED vs RESIZING

Warehouse lifecycle:

Pro tip: ALTER WAREHOUSE wh SET AUTO_SUSPEND = 60 — saves HUGE money for dev warehouses.

SECTION 2 — PRACTICE LABS (RUN IN SNOWSIGHT)

Answer First: What you proved: Time Travel is your undo button. No backup, no restore — just a SELECT with AT().

Memory Map: Lab 1: Time Travel recovery drill (10 mins) -> create damage -> AT BEFORE validate -> UNDROP or clone -> retention limit -> postmortem [SF_04_Confusions_Labs_MockInterview.md:234].

Lab 1: Time Travel recovery drill (10 mins)

Paste into Snowsight:

-- Setup
CREATE OR REPLACE TABLE lab_orders (id INT, amount NUMBER, status STRING);
INSERT INTO lab_orders VALUES
  (1, 100, 'PAID'), (2, 200, 'PAID'), (3, 300, 'PENDING');

SELECT * FROM lab_orders;
-- Output:
-- 1 | 100 | PAID
-- 2 | 200 | PAID
-- 3 | 300 | PENDING

-- Capture the timestamp BEFORE disaster
SET before_disaster = (SELECT CURRENT_TIMESTAMP());

-- Disaster strikes
DELETE FROM lab_orders WHERE status = 'PAID';
SELECT * FROM lab_orders;
-- Output: only PENDING row

-- Recovery #1: query historical data
SELECT * FROM lab_orders AT(TIMESTAMP => $before_disaster);
-- Output: all 3 rows are back!

-- Recovery #2: restore fully
INSERT INTO lab_orders
SELECT * FROM lab_orders AT(TIMESTAMP => $before_disaster)
WHERE id NOT IN (SELECT id FROM lab_orders);

SELECT * FROM lab_orders ORDER BY id;
-- Output: all 3 rows restored

What you proved: Time Travel is your undo button. No backup, no restore — just a SELECT with AT().

Answer First: What you proved: Clone = metadata pointer. Snowflake pays storage only for what diverges.

Memory Map: Lab 2: Zero-copy clone (5 mins) -> instant metadata fork -> write divergence test -> storage bytes inspect -> drop cleanup -> dev pattern [SF_04_Confusions_Labs_MockInterview.md:275].

Lab 2: Zero-copy clone (5 mins)

-- Create a large-ish table
CREATE OR REPLACE TABLE big_table AS
SELECT seq4() AS id, RANDOM() AS val
FROM TABLE(GENERATOR(ROWCOUNT => 1000000));

SELECT COUNT(*) FROM big_table;
-- Output: 1000000

-- Clone instantly
CREATE TABLE big_table_dev CLONE big_table;

-- Verify same data
SELECT COUNT(*) FROM big_table_dev;
-- Output: 1000000

-- Check storage (should show clone added 0 bytes)
SELECT TABLE_NAME, ACTIVE_BYTES
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE TABLE_NAME IN ('BIG_TABLE', 'BIG_TABLE_DEV');
-- Output:
-- BIG_TABLE     | 8388608
-- BIG_TABLE_DEV | 0          ← zero bytes used, just a pointer!

-- Now modify clone — storage diverges
UPDATE big_table_dev SET val = 0 WHERE id < 1000;

-- Re-check storage: clone now uses some bytes

What you proved: Clone = metadata pointer. Snowflake pays storage only for what diverges.

Answer First: What you proved: Scale UP adds resources per cluster, but speedup is not guaranteed to be linear. Medium (4 credits/hr) runs ~4× faster than X-Small (1 credit/hr) with workload-dependent runtime and generation-specific cost.

Memory Map: Lab 3: Warehouse sizing experiment (10 mins) -> fixed query set -> size ladder -> warm-cache control -> queue and spill metrics -> cost curve [SF_04_Confusions_Labs_MockInterview.md:311].

Lab 3: Warehouse sizing experiment (10 mins)

-- Create test data
CREATE OR REPLACE TABLE big_sales AS
SELECT
  seq4() AS sale_id,
  UNIFORM(1, 10000, RANDOM()) AS customer_id,
  UNIFORM(1, 100, RANDOM()) * 10 AS amount,
  DATEADD(DAY, UNIFORM(-365, 0, RANDOM()), CURRENT_DATE()) AS sale_date
FROM TABLE(GENERATOR(ROWCOUNT => 100000000));  -- 100 million rows

-- Test on X-Small warehouse
ALTER WAREHOUSE compute_wh SET WAREHOUSE_SIZE = 'XSMALL';
SELECT customer_id, SUM(amount) FROM big_sales GROUP BY customer_id ORDER BY 2 DESC LIMIT 10;
-- Note query time in Snowsight history tab

-- Scale UP and re-run
ALTER WAREHOUSE compute_wh SET WAREHOUSE_SIZE = 'MEDIUM';
SELECT customer_id, SUM(amount) FROM big_sales GROUP BY customer_id ORDER BY 2 DESC LIMIT 10;
-- Compare times

-- Clean up (important! bigger warehouse costs more)
ALTER WAREHOUSE compute_wh SET WAREHOUSE_SIZE = 'XSMALL';

What you proved: Scale UP adds resources per cluster, but speedup is not guaranteed to be linear. Medium (4 credits/hr) runs ~4× faster than X-Small (1 credit/hr) → with workload-dependent runtime and generation-specific cost.

SECTION 3 — LIVE VISUAL ANIMATIONS (whiteboard these)

Answer First: SQL arrives at Cloud Services parse, optimize, check cache.

Memory Map: Animation 1: How a query flows through Snowflake's 3-layer architecture -> cloud services compile -> result reuse check -> warehouse execution -> storage scan -> profile trace [SF_04_Confusions_Labs_MockInterview.md:343].

Animation 1: How a query flows through Snowflake's 3-layer architecture

┌─────────────────────────────────────────────────────┐
│  LAYER 3: Cloud Services (brain)                    │
│  • Parser, optimizer, metadata, security            │
│  • Cached results live here                         │
└────────────────┬────────────────────────────────────┘
                 │
                 ▼
┌─────────────────────────────────────────────────────┐
│  LAYER 2: Compute (virtual warehouses)              │
│  • X-Small, Small, Medium … 6X-Large                │
│  • Multiple warehouses share data layer             │
└────────────────┬────────────────────────────────────┘
                 │
                 ▼
┌─────────────────────────────────────────────────────┐
│  LAYER 1: Storage (S3/Azure Blob/GCS)               │
│  • Micro-partitions, columnar, compressed           │
│  • ALL warehouses read from same storage            │
└─────────────────────────────────────────────────────┘

Query lifecycle:

1. SQL arrives at Cloud Services → parse, optimize, check cache
2. If cached → return instantly (0 credits)
3. If not → Compute warehouse scans needed micro-partitions in Storage
4. Results sent back, cached in Cloud Services for 24h

Why this matters: Compute and storage scale INDEPENDENTLY. You can have 10 warehouses all reading the same table. No data copy.

Answer First: Takeaway: No index needed. Min/max metadata on each micro-partition = automatic pruning.

Memory Map: Animation 2: Micro-partition pruning in action -> min/max metadata -> predicate range check -> skipped partitions -> scanned bytes -> filter design [SF_04_Confusions_Labs_MockInterview.md:377].

Animation 2: Micro-partition pruning in action

Table orders with 1 billion rows, pruned by WHERE order_date = '2026-04-01':

Table = 1000 micro-partitions
Each stores its MIN/MAX order_date

┌──MP1──┐  ┌──MP2──┐  ┌──MP3──┐  ┌──MP4──┐  ...  ┌──MP1000─┐
│min:1/1│  │min:1/5│  │min:2/1│  │min:4/1│       │min:12/25│
│max:1/5│  │max:2/1│  │max:4/1│  │max:5/1│       │max:12/31│
└───────┘  └───────┘  └───────┘  └───────┘       └─────────┘
   SKIP        SKIP       SKIP     ✓READ            SKIP

Query: WHERE order_date = '2026-04-01'
Scan:  1 out of 1000 partitions = 0.1% of data read

Takeaway: No index needed. Min/max metadata on each micro-partition = automatic pruning.

Answer First: Gotcha: Warehouse cache disappears when you suspend. Frequently-queried dashboards keep warehouse warm OR rely on result cache.

Memory Map: Animation 3: The 3-tier cache hierarchy -> persisted result -> local SSD blocks -> remote storage services -> invalidation branches -> cost impact [SF_04_Confusions_Labs_MockInterview.md:399].

Animation 3: The 3-tier cache hierarchy

Query arrives
     │
     ▼
┌─────────────────────────────┐
│ TIER 1: Result Cache        │ ← 24hr, FREE, 0 credits
│ (Cloud Services layer)      │   If same query + same data → return cached
└──────────┬──────────────────┘
           │ miss
           ▼
┌─────────────────────────────┐
│ TIER 2: Warehouse Cache     │ ← Lives on warehouse SSD
│ (Virtual Warehouse)         │   Lost when warehouse suspends!
└──────────┬──────────────────┘
           │ miss
           ▼
┌─────────────────────────────┐
│ TIER 3: Remote Storage      │ ← S3/Azure/GCS — slowest
│ (Blob storage)              │   Always the ground truth
└─────────────────────────────┘

Gotcha: Warehouse cache disappears when you suspend. Frequently-queried dashboards → keep warehouse warm OR rely on result cache.

SECTION 4 — GOTCHAS (REAL PRODUCTION FAILURES)

Answer First: Fix: Don't embed CURRENT_TIMESTAMP, RANDOM(), CURRENT_USER() in dashboards unless you want uncached runs.

Memory Map: Gotcha 1: Result cache silently lies if clock-sensitive functions are used -> nondeterministic functions -> session context differences -> mutation invalidates -> bypass reason -> history test [SF_04_Confusions_Labs_MockInterview.md:429].

Gotcha 1: Result cache silently lies if clock-sensitive functions are used

SELECT CURRENT_TIMESTAMP(), COUNT(*) FROM orders;
-- This query NEVER hits result cache (timestamp is non-deterministic)

Fix: Don't embed CURRENT_TIMESTAMP, RANDOM(), CURRENT_USER() in dashboards unless you want uncached runs.

Answer First: AUTO_SUSPEND = 0 disables automatic suspension, so an otherwise idle warehouse can continue consuming credits until it is suspended explicitly.

Memory Map: Gotcha 2: AUTO_SUSPEND = 0 means NEVER suspend -> zero disables idle shutdown -> started warehouse keeps consuming credits -> positive timeout restores automatic suspension [SF_04_Confusions_Labs_MockInterview.md:438].

Gotcha 2: AUTO_SUSPEND = 0 means NEVER suspend

-- Wrong (runs forever, burns credits)
ALTER WAREHOUSE wh SET AUTO_SUSPEND = 0;

-- Right
ALTER WAREHOUSE wh SET AUTO_SUSPEND = 60;  -- 60 seconds idle

AUTO_SUSPEND = 0 disables automatic suspension, so an otherwise idle warehouse can continue consuming credits until it is suspended explicitly.

Answer First: A task or stored procedure might run under a DIFFERENT role than your session. Always set role/warehouse explicitly inside procedures.

Memory Map: Gotcha 3: USE ROLE / USE WAREHOUSE is session-scoped -> session context selects authorization and compute -> reconnect resets configured defaults -> explicit context prevents accidental execution [SF_04_Confusions_Labs_MockInterview.md:450].

Gotcha 3: USE ROLE / USE WAREHOUSE is session-scoped

A task or stored procedure might run under a DIFFERENT role than your session. Always set role/warehouse explicitly inside procedures.

CREATE TASK nightly_load
  WAREHOUSE = etl_wh   -- explicit
  SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
  CALL load_orders();

Answer First: Fix to force reload: COPY INTO ... FORCE = TRUE OR purge the load metadata.

Memory Map: Gotcha 4: COPY INTO silently skips files already loaded -> recent load history -> filename checksum metadata -> FORCE bypass -> duplicate prevention -> replay ledger [SF_04_Confusions_Labs_MockInterview.md:462].

Gotcha 4: COPY INTO silently skips files already loaded

COPY INTO orders FROM @stage;  -- loads file1.csv
COPY INTO orders FROM @stage;  -- SKIPS file1.csv (already loaded per LOAD_HISTORY)

Fix to force reload: COPY INTO ... FORCE = TRUE OR purge the load metadata.

Answer First: SQL NULL and JSON null are distinct inside VARIANT; test JSON null with IS_NULL_VALUE or compare against PARSE_JSON('null') instead of IS NULL alone.

Memory Map: Gotcha 5: VARIANT column NULL vs JSON null -> SQL NULL versus JSON null -> IS_NULL_VALUE check -> cast behavior -> data quality rule -> test fixture [SF_04_Confusions_Labs_MockInterview.md:471].

Gotcha 5: VARIANT column NULL vs JSON null

SELECT
  PARSE_JSON('null') IS NULL,          -- Output: FALSE (it's a JSON null, not SQL NULL)
  PARSE_JSON('null')::STRING;          -- Output: 'null' (the string "null")

Fix: Compare with PARSE_JSON('null') or use IS_NULL_VALUE(col).

Answer First: If credits are huge, clustering key may be wrong choice.

Memory Map: Gotcha 6: Clustering keys DON'T auto-maintain without auto-clustering service -> key declaration -> service enabled check -> depth monitor -> maintenance credits -> benefit proof [SF_04_Confusions_Labs_MockInterview.md:481].

Gotcha 6: Clustering keys DON'T auto-maintain without auto-clustering service

Once you ALTER TABLE t CLUSTER BY (col), Snowflake runs automatic background reclustering (costs credits). Monitor:

SELECT * FROM TABLE(INFORMATION_SCHEMA.AUTOMATIC_CLUSTERING_HISTORY(
  DATE_RANGE_START => DATEADD(DAY, -7, CURRENT_DATE())
));

If credits are huge, clustering key may be wrong choice.

SECTION 5 — TIMED MOCK INTERVIEW (45 MIN)

Answer First: External stage pointing to S3 + storage integration for secure access.

Memory Map: Q1 (8 min) — "Design an ingestion pipeline: S3 CSVs land every 5 minutes, need them queryable ASAP" -> landing notification -> Snowpipe for files -> Streaming for rows -> Dynamic Table silver -> SLA monitor [SF_04_Confusions_Labs_MockInterview.md:494].

Q1 (8 min) — "Design an ingestion pipeline: S3 CSVs land every 5 minutes, need them queryable ASAP"

Strong answer structure:

1. External stage pointing to S3 + storage integration for secure access
2. Snowpipe with auto-ingest (S3 event notification → SQS → Snowpipe)
3. Target table with appropriate clustering key (event_date)
4. Stream on target for downstream CDC
5. Task reading the stream every 5 min to populate dimension/fact tables

Key decisions to mention: Error handling (ON_ERROR = 'CONTINUE' vs 'ABORT_STATEMENT'), file format (compressed Parquet > CSV for cost), warehouse sizing for Snowpipe (it uses serverless compute by default, not your warehouse).

Answer First: Check Query Profile in Snowsight history — find longest step.

Memory Map: Q2 (6 min) — "A query that used to take 30 seconds now takes 5 minutes. How do you debug?" -> history baseline -> profile operator -> pruning/spill/skew -> one-change experiment -> rollback [SF_04_Confusions_Labs_MockInterview.md:507].

Q2 (6 min) — "A query that used to take 30 seconds now takes 5 minutes. How do you debug?"

Checklist:

1. Check Query Profile in Snowsight history — find longest step
2. Is "Bytes scanned" huge? → clustering key degraded, missing partition pruning
3. Is "Spilling to local/remote disk" shown? → warehouse too small, scale up
4. Check warehouse load — was it queuing? (other users competing)
5. Compare with a baseline: SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE query_hash = ...
6. Data volume growth? Table may need reclustering or larger warehouse

Answer First: Likely culprits: warehouse not auto-suspending, someone ran a SELECT * on 10 TB, Time Travel retention extended, new pipeline spun up.

Memory Map: Q3 (5 min) — "Your boss says the Snowflake bill doubled last month. Find the cause." -> usage history separates compute storage and services -> tags attribute owners and workloads -> runtime or retention change explains variance [SF_04_Confusions_Labs_MockInterview.md:519].

Q3 (5 min) — "Your boss says the Snowflake bill doubled last month. Find the cause."

-- Which warehouses are burning credits?
SELECT warehouse_name, SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(DAY, -30, CURRENT_DATE())
GROUP BY 1 ORDER BY 2 DESC;

-- Which users are running most?
SELECT user_name, COUNT(*) AS queries, SUM(execution_time)/1000/60 AS minutes
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(DAY, -30, CURRENT_DATE())
GROUP BY 1 ORDER BY 3 DESC;

-- Storage growth
SELECT DATE_TRUNC('DAY', usage_date) AS day, AVG(storage_bytes)/POWER(1024,4) AS tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
WHERE usage_date >= DATEADD(DAY, -60, CURRENT_DATE())
GROUP BY 1 ORDER BY 1;

Likely culprits: warehouse not auto-suspending, someone ran a SELECT * on 10 TB, Time Travel retention extended, new pipeline spun up.

Answer First: A zero-copy clone starts as metadata over shared micro-partitions, but divergent writes add storage, Time Travel accrues independently, grants need review, and load history is not cloned.

Memory Map: Q4 (4 min) — "Zero-copy clone for dev env — what's the catch?" -> shared base data -> changes add storage -> grants and retention -> stale dev risk -> cleanup owner [SF_04_Confusions_Labs_MockInterview.md:545].

Q4 (4 min) — "Zero-copy clone for dev env — what's the catch?"

Catch:

• Clone is instant + free at creation
• Modifying clone diverges → storage starts counting
• Time Travel on clone is independent (starts at clone creation)
• Clone does NOT inherit grants/permissions automatically → re-grant
• Clones don't clone LOAD_HISTORY → COPY INTO on clone may re-load files

Answer First: Use a materialized view when an eligible expensive single-table query repeats enough to outweigh automatic refresh and storage cost; verify benefit and maintenance overhead together.

Memory Map: Q5 (4 min) — "When do you use a Materialized View?" -> stable repeated query -> supported SQL check -> maintenance credit estimate -> compare search/clustering -> win metric [SF_04_Confusions_Labs_MockInterview.md:556].

Q5 (4 min) — "When do you use a Materialized View?"

• Query is SLOW and re-run often
• Base table has HIGH data volume, LOW query selectivity
• Costs: MV itself uses storage + auto-refresh consumes credits

Don't use when: base table changes constantly (refresh cost > savings), query joins many tables (MVs can't span joins in Snowflake — limited to single table aggregations/projections).

SECTION 6 — FINAL READINESS CHECKLIST

Before your Snowflake interview, be ready to answer YES to all of these:

• Can I draw the 3-layer architecture (Cloud Services / Compute / Storage) and explain independence?
• Do I know the 4 warehouse-sizing rules (UP for slow query, OUT for concurrency, AUTO_SUSPEND, per-sec billing)?
• Can I explain micro-partition pruning without saying "index"?
• Do I know Time Travel vs Fail-safe vs Clone?
• Can I write COPY INTO, Snowpipe definition, and Stream+Task from memory?
• Can I use QUALIFY and VARIANT/FLATTEN fluently?
• Do I know the 3 cache tiers and why warehouse cache dies on suspend?
• Can I debug a slow query using Query Profile (bytes scanned + spilling)?
• Can I list 3 ways to reduce a Snowflake bill?
• Can I explain when clustering key is worth its cost?

If all 10 = YES, you're interview-ready. Re-read this page the morning of the interview.

Remember WAREHOUSE-STAGE-TIME. Everything else is details.

Snowflake Interview Question Index

This module intentionally contains links rather than duplicate answers. Practice the source wording, then open the exact rendered concept owner for the direct answer, mechanism, examples, caveats, and verification evidence.

Q-SF-001: What is Snowflake? Explain its architecture.

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L15.

Q-SF-002: What are Micro-Partitions? How are they different from Hive partitions?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L79.

Q-SF-003: What are Clustering Keys? When should you use them?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L142.

Q-SF-004: What is a Virtual Warehouse? Explain sizing and multi-cluster.

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L192.

Q-SF-005: Explain the 3 levels of Caching in Snowflake.

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L255.

Q-SF-006: What is Time Travel? What is Fail-safe?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L319.

Q-SF-007: What is Zero-Copy Cloning?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L385.

Q-SF-008: What is VARIANT? How does Snowflake handle semi-structured data?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L420.

Q-SF-009: What are Stages? Internal vs External?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L493.

Q-SF-010: [DIRECT] Common Basic Questions

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L549.

Q-SF-011: Snowflake vs Databricks — When to use which?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L570.

Q-SF-012: Scenario — Design a Snowflake architecture for a travel platform's booking analytics

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L635.

Q-SF-013: Scenario — Why is a specific query slow? How to diagnose?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Architecture_Core.md#L678.

Q-SF-014: What is Snowflake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L87.

Q-SF-015: What are the 3 layers of Snowflake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L90.

Q-SF-016: What makes Snowflake different from traditional databases?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L95.

Q-SF-017: What are micro-partitions?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L98.

Q-SF-018: Are Snowflake micro-partitions the same as Hive partitions?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L101.

Q-SF-019: What is a clustering key?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L104.

Q-SF-020: What is partition pruning?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L107.

Q-SF-021: What is a Virtual Warehouse?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L110.

Q-SF-022: What is a multi-cluster warehouse?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L113.

Q-SF-023: Scale UP vs Scale OUT — what's the difference?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L116.

Q-SF-024: How does Snowflake read a query? (End-to-end flow)

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L124.

Q-SF-025: What happens when warehouse is auto-suspended?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L133.

Q-SF-026: How to decide virtual warehouse size?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L136.

Q-SF-027: Explain clustering depth and overlap

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L139.

Q-SF-028: What are the 3 caching levels in Snowflake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L179.

Q-SF-029: When is Result Cache used?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L184.

Q-SF-030: Why might Result Cache NOT be used even for the same query?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L187.

Q-SF-031: What happens to cache when warehouse is suspended?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L193.

Q-SF-032: What is Time Travel?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L230.

Q-SF-033: What is the default time travel period?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L233.

Q-SF-034: What is the difference between Time Travel and Fail-safe?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L236.

Q-SF-035: What is Zero-Copy Cloning?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L240.

Q-SF-036: What are the use cases for Zero-Copy Clone?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L243.

Q-SF-037: What is the VARIANT data type?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L278.

Q-SF-038: How do you access a JSON field from a VARIANT column?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L281.

Q-SF-039: What is FLATTEN?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L284.

Q-SF-040: Why must you cast VARIANT values?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_01_Quick_Recall.md#L287.

Q-SF-041: What is COPY INTO? How is it different from INSERT?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L11.

Q-SF-042: What is Snowpipe? How does it differ from COPY INTO?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L69.

Q-SF-043: What Are Snowflake Streams? How Do They Enable CDC?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L142.

Q-SF-044: What Are Snowflake Tasks? How Do You Schedule SQL?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L280.

Q-SF-045: What Are Dynamic Tables? How Are They Different from Streams + Tasks?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L461.

Q-SF-046: Q5b: Explain Materialized Views in Snowflake

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L568.

Q-SF-047: What is Snowpark? When is it useful?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L655.

Q-SF-048: How do you optimize query performance in Snowflake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L729.

Q-SF-049: Scenario — Dashboard queries are slow for 200 BI analysts. How to fix?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Pipelines_Performance.md#L799.

Q-SF-050: What is COPY INTO?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L53.

Q-SF-051: What is a Stage?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L56.

Q-SF-052: What is Snowpipe?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L59.

Q-SF-053: What is Snowpipe Streaming?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L62.

Q-SF-054: COPY INTO vs Snowpipe vs Snowpipe Streaming — when to use which?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L65.

Q-SF-055: Is COPY INTO safe to re-run?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L73.

Q-SF-056: What happens with bad rows in COPY INTO?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L76.

Q-SF-057: What is a Stream?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L123.

Q-SF-058: What are the 3 metadata columns in a Stream?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L126.

Q-SF-059: How does an UPDATE appear in a Standard Stream?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L129.

Q-SF-060: What is an Append-Only Stream?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L132.

Q-SF-061: When does a Stream "advance"?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L135.

Q-SF-062: What is a Task?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L173.

Q-SF-063: How do you chain Tasks into a pipeline?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L176.

Q-SF-064: What is the difference between warehouse-based and serverless tasks?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L179.

Q-SF-065: How do you monitor task failures?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L184.

Q-SF-066: What is a Dynamic Table?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L228.

Q-SF-067: What is TARGET_LAG?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L231.

Q-SF-068: Dynamic Tables vs Streams + Tasks — when to use which?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L234.

Q-SF-069: What is INCREMENTAL refresh vs FULL refresh?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L238.

Q-SF-070: How does a Dynamic Table handle an UPDATE in source?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L242.

Q-SF-071: What is Query Profile?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L286.

Q-SF-072: What does "spilling to disk" mean?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L289.

Q-SF-073: What is a Materialized View?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L292.

Q-SF-074: What is Search Optimization Service?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L295.

Q-SF-075: Clustering key vs Search Optimization — when to use which?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_02_Quick_Recall.md#L298.

Q-SF-076: What is RBAC in Snowflake? What are the system roles?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L11.

Q-SF-077: What is Data Masking? How does it work?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L90.

Q-SF-078: What are Row Access Policies?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L137.

Q-SF-079: What is Secure Data Sharing? How does it work?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L184.

Q-SF-080: How do you manage and optimize Snowflake costs?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L233.

Q-SF-081: What are the major Snowflake features from 2025-2026?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L314.

Q-SF-082: 10 Most-Likely Snowflake Interview Questions

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Security_Sharing_New.md#L472.

Q-SF-083: What is RBAC in Snowflake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L71.

Q-SF-084: What are the 5 system-defined roles?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L74.

Q-SF-085: What does each system role do?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L77.

Q-SF-086: What is the #1 RBAC mistake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L84.

Q-SF-087: What is FUTURE GRANTS?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L87.

Q-SF-088: How do you switch roles?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L90.

Q-SF-089: Design a role hierarchy for 200 engineers at a large enterprise

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L97.

Q-SF-090: What is Dynamic Data Masking?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L156.

Q-SF-091: What is a Row Access Policy?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L159.

Q-SF-092: Column masking vs Row Access — what's the difference?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L162.

Q-SF-093: How does CURRENT_ROLE() work in policies?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L166.

Q-SF-094: What is Secure Data Sharing?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L202.

Q-SF-095: What is a Reader Account?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L205.

Q-SF-096: What is a Data Clean Room?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L208.

Q-SF-097: Who pays for what in Data Sharing?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L211.

Q-SF-098: What is a Resource Monitor?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L220.

Q-SF-099: What is a Transient Table?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L223.

Q-SF-100: What is the biggest Snowflake cost mistake?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L226.

Q-SF-101: How do you optimize storage cost?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L229.

Q-SF-102: What are Gen 2 Warehouses?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L275.

Q-SF-103: What is Cortex AI?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L278.

Q-SF-104: What is Cortex Search? What's new in 2026?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L281.

Q-SF-105: What are Iceberg Tables?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L284.

Q-SF-106: What is Polaris Open Catalog?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L287.

Q-SF-107: What are Hybrid Tables?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L290.

Q-SF-108: What is Snowflake Postgres?

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_03_Quick_Recall.md#L293.

Q-SF-109: Confusion #1 — Scale UP vs Scale OUT (Warehouse sizing vs Multi-cluster)

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L23.

Q-SF-110: Confusion #2 — Internal Stage vs External Stage vs Named Stage vs Table Stage vs User Stage

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L47.

Q-SF-111: Confusion #3 — COPY INTO vs Snowpipe vs External Tables vs Streams & Tasks

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L78.

Q-SF-112: Confusion #4 — Time Travel vs Fail-safe vs Clone

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L96.

Q-SF-113: Confusion #5 — Micro-partitions vs Clustering Keys vs Partitioning (traditional)

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L132.

Q-SF-114: Confusion #6 — Warehouse credits vs Storage cost vs Cloud services cost

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L158.

Q-SF-115: Confusion #7 — Standard SQL vs Snowflake extensions (VARIANT, FLATTEN, QUALIFY)

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L177.

Q-SF-116: Confusion #8 — Warehouse states: SUSPENDED vs STARTED vs RESIZING

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L210.

Q-SF-117: Q1 (8 min) — "Design an ingestion pipeline: S3 CSVs land every 5 minutes, need them queryable ASAP"

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L494.

Q-SF-118: Q2 (6 min) — "A query that used to take 30 seconds now takes 5 minutes. How do you debug?"

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L507.

Q-SF-119: Q3 (5 min) — "Your boss says the Snowflake bill doubled last month. Find the cause."

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L519.

Q-SF-120: Q4 (4 min) — "Zero-copy clone for dev env — what's the catch?"

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L545.

Q-SF-121: Q5 (4 min) — "When do you use a Materialized View?"

Answer owner: Open the canonical concept or runnable pattern.

Alternate source wording: content/snowflake/SF_04_Confusions_Labs_MockInterview.md#L556.