Interactive Learning Guide

Snowflake Architecture
& Design Fundamentals

A practical, hands-on guide to Snowflake's core concepts β€” from compute/storage separation to AI workloads. Expand each section for deep dives and check your understanding with mini-quizzes.

0 of 10 sections
0%
01

The Core Idea: Separate Compute from Storage

Everything in Snowflake flows from one architectural decision: compute and storage are completely independent layers. This single choice drives the pricing model, the scaling behaviour, and almost every design pattern you'll use.

Cloud Storage (pay per TB/month)
S3 (AWS)
Azure Blob
GCS
Columnar FDN format
All your data lives here β€” always
↑     reads / writes     ↓
Compute β€” Virtual Warehouses (pay per second when running)
ETL Warehouse
BI / Reporting WH
Data Science WH
Ad-hoc WH
↑     results     ↓
Services Layer (always on β€” metadata, auth, query planning)
Query Optimizer
Metadata Store
Authentication
Access Control
Transaction Manager

Why this matters for your workloads: The BI team can run heavy dashboards without slowing down the ETL pipeline. Data science can train models while reports are refreshing. Each team gets its own independent compute cluster β€” zero contention, separate billing.

πŸ” Deep Dive: The Three Layers Explained

+

Cloud Storage Layer: Snowflake stores all data in its own optimised columnar format (FDN β€” Foundation) on cloud object storage. You never access S3/Blob directly β€” Snowflake manages files, compression, and encryption. Because storage is separate, you can scale it to petabytes without touching your compute.

Virtual Warehouse Layer: These are clusters of VMs that load data micro-partitions into local SSD cache when executing queries. When you suspend a warehouse, the VMs are released β€” billing stops. Cache is warm within seconds of resume. This is stateless compute: the data itself never moves, only read operations happen against storage.

Cloud Services Layer: The "brain" of Snowflake. It runs continuously (Snowflake pays for this, not you), handles authentication, compiles SQL, manages transactions, and maintains metadata. When a query runs, this layer figures out which micro-partitions need to be scanned, then the warehouse does the actual reading.

Micro-partitions: Snowflake automatically divides every table into micro-partitions of 50–500 MB of uncompressed data. Each micro-partition stores column data contiguously (columnar), which means SELECT revenue FROM sales only reads the revenue column β€” not entire rows. This is why Snowflake is so fast on analytical queries even over hundreds of billions of rows.

πŸ“Š Deep Dive: How Credits Work

+

1 Credit = 1 warehouse node running for 1 hour. Snowflake charges at per-second granularity (with a 60-second minimum). Suspend a warehouse and billing stops within 60 seconds.

Credit rates vary by cloud region and tier (Standard / Enterprise / Business Critical). Indicative: EU regions run approximately $2–$4 per credit. Storage is separate: ~$23/TB/month on AWS.

-- Resume and suspend manually, or set auto-suspend: ALTER WAREHOUSE bi_wh SET AUTO_SUSPEND = 60; -- suspend after 60s idle ALTER WAREHOUSE bi_wh SET AUTO_RESUME = TRUE; -- wake automatically on query

Cost surprise β€” Concurrency Scaling: When your warehouse hits its query queue, Snowflake can spin up burst clusters automatically. The first 1 credit per day per warehouse is free; after that you pay at the standard rate. On high-user workloads this can increase your actual spend by 1.3–2.2Γ—.

βœ… Check Your Understanding

Q1: You suspend a Virtual Warehouse at 2:30 PM. A user submits a query at 2:35 PM. What happens?

Q2: Your data science team runs 6-hour ML training jobs. Your BI team runs 2-minute dashboard queries. What's the best compute setup?


02

Virtual Warehouses β€” Sizing & Patterns

A Virtual Warehouse is a named cluster of compute nodes. Size it for your slowest acceptable query time, not for peak volume β€” scale out with concurrency scaling or multi-cluster warehouses for burst load.

XS
1 credit/hr
Dev, light ad-hoc queries
S
2 cr/hr
Small BI teams, <10 users
M
4 cr/hr
Standard reporting, dbt models
L
8 cr/hr
Heavy transforms, large joins
XL
16 cr/hr
ML feature prep, bulk loads
2XL+
32–128
Large ML training, massive ETL

πŸ”€ Multi-Cluster Warehouse

For high-concurrency workloads (many simultaneous users). Set MIN=1, MAX=3. Additional clusters spin up when the queue exceeds a threshold and shut down when idle. Best for: Power BI / Tableau with 50+ concurrent users.

⚑ Concurrency Scaling

Temporary burst clusters that attach to your warehouse during peak periods. First 1 credit/day is free. Unlike multi-cluster, you don't manage it β€” Snowflake decides when to add burst capacity. Best for: ad-hoc analytical spikes.

πŸ’€ Auto Suspend / Resume

Set AUTO_SUSPEND (60–300s recommended for BI, longer for ETL). This is the biggest lever for cost control. A warehouse sitting idle burning credits is the #1 avoidable cost in Snowflake deployments.

πŸ“Š Resource Monitors

Set credit quotas per warehouse or account. Trigger alerts at 75%, suspend at 100%. Prevents runaway queries from blowing the monthly budget. Critical for multi-team environments.

πŸ” Deep Dive: Warehouse Sizing for a Real Estate Analytics Platform

+

For a platform with ~50 internal analysts, ~120 partner portal users, and 80K daily events, a typical warehouse setup would be:

WarehouseSizeAuto-suspendPurpose
ETL_WHM300sSnowpipe loads, dbt incremental runs, Streams + Tasks
BI_WHS (multi-cluster max=2)60sPower BI direct query, Tableau, dashboards
ADHOC_WHXS60sAnalyst exploration, Snowsight, Jupyter
DS_WHL600sPython ML workloads via Snowpark, feature engineering

At these sizes, with typical utilisation (ETL ~4hr/day, BI ~8hr/day, DS ~2hr/day, ADHOC ~3hr/day), monthly credit usage is approximately 800–1,200 credits/month. At €3/credit that's ~€2,400–€3,600/month in compute. Storage for 150GB is negligible (~€3.50/month).


03

Storage Layer β€” Databases, Schemas & Stages

Snowflake organises data into a three-level hierarchy. Everything is a named object, and access control applies at every level.

Object Hierarchy
Account
DB: RAW
DB: SILVER
DB: ANALYTICS
Database: SILVER
Schema: EVENTS
Schema: LISTINGS
Schema: CRM
Schema: LISTINGS
Table: fact_listing
View: v_active
Stage: listing_files
Stream: changes

πŸ” Deep Dive: Stages β€” The Loading Zone

+

A Stage is a named pointer to a file location. Before loading data into a table, files must land in a stage. There are three types:

TypeLocationWhen to use
InternalSnowflake-managed storageSmall files, quick uploads via PUT command
External NamedYour S3/ADLS/GCS bucketExisting data lake, partner data drops, event hub archives
External TableYour cloud bucketQuery files directly without COPY β€” no data movement
-- Create external stage pointing at Azure ADLS Gen2 CREATE OR REPLACE STAGE raw.events.azure_stage URL = 'azure://your-account.blob.core.windows.net/raw-events/' CREDENTIALS = (AZURE_SAS_TOKEN = '...') FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE); -- Load from stage into table COPY INTO raw.events.portal_events FROM @raw.events.azure_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'CONTINUE';

πŸ” Deep Dive: Columnar Storage & Micro-Partitions

+

Every table is automatically split into micro-partitions of 50–500MB (uncompressed). Snowflake never exposes this to you β€” it's fully automatic. Three properties make this powerful:

1. Columnar storage: Within each micro-partition, values for the same column are stored together. SELECT revenue FROM sales reads only the revenue column bytes β€” not entire rows. Analytical queries are typically 10–100Γ— faster than row-oriented databases.

2. Automatic clustering: Snowflake tracks min/max values per column per micro-partition. A query with WHERE listing_date = '2026-06-01' can skip micro-partitions that don't contain that date β€” this is called partition pruning.

3. Compression: Columnar data compresses extremely well (similar values near each other). Snowflake auto-selects the best compression algorithm per column type. Real-world compression ratios of 3–10Γ— are common.

Cluster Keys (optional): For very large tables (>1 TB), you can define a cluster key on high-cardinality columns to physically co-locate related data and improve pruning. Not needed for typical workloads under 500GB.


04

Ingestion Patterns

How data gets from your operational systems into Snowflake determines latency, cost, and complexity. Pick the pattern that matches your freshness requirement β€” don't over-engineer for real-time when daily is sufficient.

PatternLatencyToolingCost ModelBest For
Bulk COPY Minutes–Hours COPY INTO, dbt seeds, custom scripts Warehouse compute while loading Historical loads, daily batch from Azure SQL Server, ERP exports
Snowpipe ~1 minute Snowpipe + SQS/Event Notification Per-file fee (~$0.06/1000 files) + small compute Event streams from Azure Event Hubs, clickstream, GA4 exports
Kafka Connector Seconds Kafka + Snowflake Kafka Connector Warehouse compute High-throughput real-time events (portal activity, property search)
Partner Connectors Minutes–Daily Fivetran, Airbyte, Stitch Connector fee + warehouse compute SaaS sources: Jira, Confluence, GA4, Google Ads, Meta Ads, Dynamics 365
CDC Near real-time Debezium β†’ Kafka β†’ Snowpipe Kafka infra + Snowpipe fee OLTP replication: Azure SQL β†’ Snowflake without full refresh

πŸ” Deep Dive: Snowpipe β€” Continuous Serverless Ingestion

+

Snowpipe is a serverless ingestion service. Files land in cloud storage β†’ a notification (SQS event or Azure Event Grid event) triggers Snowpipe β†’ data is loaded into a target table within ~1 minute. No warehouse needed.

-- Step 1: Create a pipe with the COPY statement embedded CREATE OR REPLACE PIPE raw.events.portal_events_pipe AUTO_INGEST = TRUE AS COPY INTO raw.events.portal_events FROM @raw.events.azure_stage FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE); -- Step 2: Configure Azure Event Grid notification on the blob container -- (done in Azure Portal or Terraform β€” Snowpipe polls the SQS/Event Grid queue) -- Monitor pipe status SELECT SYSTEM$PIPE_STATUS('raw.events.portal_events_pipe');

Error handling: Failed rows are recorded in COPY_HISTORY. You can query exactly which files failed and why. Snowpipe retries automatically for transient errors but skips on parse failures (use ON_ERROR='CONTINUE' and monitor errors separately).

Scaling: Snowpipe scales automatically with file volume. 80,000 events/day in small JSON files is a trivial Snowpipe workload. Even at 10Γ— peak (800,000 events/day) it requires no configuration changes.

πŸ” Deep Dive: Azure Event Hubs β†’ Snowflake

+

Your platform generates portal events via Azure Event Hubs. There are two recommended patterns:

Pattern A β€” Event Hubs β†’ ADLS Gen2 β†’ Snowpipe (preferred for <1min latency)

Azure Event Hubs
β†’
Event Hubs Capture
β†’
ADLS Gen2 (Avro files)
β†’
Snowpipe
β†’
raw.events table

Pattern B β€” Event Hubs β†’ Kafka Connector β†’ Snowflake (for seconds-level latency)

Azure Event Hubs
β†’
Kafka (MSK or Azure)
β†’
Snowflake Kafka Connector
β†’
raw.events table

For 80,000 events/day (peak 250,000), Pattern A is more cost-effective and simpler. Pattern B is warranted only if you need real-time alerting or event processing with sub-minute SLA.


05

Streams & Tasks β€” Built-in CDC and Scheduling

Snowflake has built-in CDC and scheduling primitives. For simple pipelines, you may not need Airflow at all.

🌊 Streams (CDC)

A Stream tracks every INSERT, UPDATE, and DELETE on a source table. Consuming the stream marks changes as processed β€” like an offset pointer. Supports append-only (events) and standard (full CDC) modes.

⏰ Tasks (Scheduler)

A Task runs a SQL statement or stored procedure on a cron schedule or when triggered by a parent task. Build DAG-like pipelines without external orchestration for simple flows.

πŸ” Deep Dive: Stream + Task Pattern (Incremental Load)

+

The canonical pattern: raw table receives new rows β†’ a Stream detects them β†’ a Task runs every 5 minutes and processes only the new rows into Silver.

-- 1. Create a stream on the raw table CREATE STREAM raw.events.portal_events_stream ON TABLE raw.events.portal_events APPEND_ONLY = TRUE; -- event tables are append-only -- 2. Create a task that processes the stream every 5 minutes CREATE TASK silver.events.load_portal_events WAREHOUSE = etl_wh SCHEDULE = '5 MINUTE' WHEN SYSTEM$STREAM_HAS_DATA('raw.events.portal_events_stream') AS INSERT INTO silver.events.portal_events_clean SELECT TRY_CAST(event:user_id::STRING AS VARCHAR) AS user_id, TRY_CAST(event:event_type::STRING AS VARCHAR) AS event_type, TRY_TO_TIMESTAMP(event:timestamp::STRING) AS event_ts, event:properties AS properties FROM raw.events.portal_events_stream; ALTER TASK silver.events.load_portal_events RESUME;

Key detail: The task only runs if STREAM_HAS_DATA returns true β€” zero credits consumed when there's nothing to process. This is far more efficient than a warehouse that polls every 5 minutes regardless.

βœ… Check Your Understanding

Q3: A Stream's offset advances (moves forward) when:


06

Medallion Architecture β€” Bronze, Silver, Gold

The industry-standard data organisation pattern for Snowflake deployments. Each layer has a single responsibility, enabling independent quality control and access governance.

πŸ₯‰ Bronze / Raw β€” Append-only, never modified, full fidelity
raw.portal_events
raw.azure_sql_listings
raw.dynamics_crm
raw.ga4_sessions
raw.jira_issues
↓ Streams + Tasks, or dbt sources β†’ staging
πŸ₯ˆ Silver / Conformed β€” Cleaned, typed, deduplicated, joined
silver.fact_portal_events
silver.dim_property
silver.dim_user
silver.dim_date
↓ dbt models, Materialized Views
πŸ₯‡ Gold / Analytics β€” Business-ready, aggregated, BI-served
gold.listing_performance_weekly
gold.user_segment_daily
gold.funnel_conversion
gold.partner_activity

πŸ” Deep Dive: dbt + Snowflake β€” The Standard Stack

+

dbt (data build tool) is the de-facto transformation layer for Snowflake. It compiles SQL templates into CREATE TABLE AS SELECT or CREATE VIEW statements and runs them in dependency order.

Key dbt concepts:

ConceptWhat it does
dbt source()References raw tables in the Bronze layer. Adds data freshness checks.
dbt ref()References another dbt model. Builds a DAG automatically β€” dbt ensures correct execution order.
materialization: tableFull refresh β€” drops and recreates the table. Use for Gold aggregates.
materialization: incrementalOnly processes new rows. Uses a unique_key for upserts. Use for Silver fact tables.
materialization: viewNo data stored β€” query computed at runtime. Use for lightweight transformations.
dbt testBuilt-in data quality tests: not_null, unique, accepted_values, referential integrity.
-- models/silver/fact_portal_events.sql {{ config( materialized='incremental', unique_key='event_id', on_schema_change='sync_all_columns' ) }} SELECT {{ dbt_utils.generate_surrogate_key(['user_id', 'event_ts', 'event_type']) }} AS event_id, user_id, event_type, event_ts, properties:listing_id::VARCHAR AS listing_id FROM {{ source('raw', 'portal_events') }} {% if is_incremental() %} WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }}) {% endif %}

07

RBAC β€” Roles, Privileges & Access Control

Snowflake uses a strict role-based hierarchy. Every object access goes through a role, never a direct user grant. This is fundamental to multi-tenant governance.

Role Hierarchy (roles inherit privileges of child roles)
ACCOUNTADMIN ← system admin only, never used for daily work
└── SYSADMIN ← creates databases, warehouses, schemas
β”œβ”€β”€ LOADER_ROLE ← COPY INTO raw layer only
β”œβ”€β”€ TRANSFORMER_ROLE ← dbt/pipeline, read raw+silver, write silver+gold
└── ANALYST_ROLE ← read-only on gold layer
β”œβ”€β”€ DS_ROLE ← read silver+gold, own ML schemas
└── PARTNER_ROLE ← read-only on specific gold views

πŸ” Deep Dive: Row-Level and Column-Level Security

+

Row Access Policies filter rows based on the current user's role or attributes. One table definition, different users see different rows β€” no view proliferation.

-- Only show rows where region matches the user's assigned region CREATE ROW ACCESS POLICY property_region_policy AS (region VARCHAR) RETURNS BOOLEAN -> CASE WHEN CURRENT_ROLE() = 'ANALYST_ROLE' THEN TRUE -- analysts see all ELSE region = GET(CURRENT_AVAILABLE_ROLES(), 0) -- partners see own region END; ALTER TABLE gold.listings ADD ROW ACCESS POLICY property_region_policy ON (region);

Dynamic Data Masking shows real values to authorized roles and masked values to others β€” same table, same column, different result per role.

-- Mask email for non-analyst roles CREATE MASKING POLICY email_mask AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') THEN val ELSE REGEXP_REPLACE(val, '.+@', '****@') END; ALTER TABLE silver.crm.users MODIFY COLUMN email SET MASKING POLICY email_mask;

This is critical for GDPR compliance β€” partners and external BI users can use the same tables without exposing PII.


08

Time Travel, Fail-Safe & Zero-Copy Cloning

Three features that together eliminate most backup/restore concerns and enable near-zero-cost dev/test environments.

⏱️ Time Travel

Query any table as it existed at any point in the past, up to 90 days (Enterprise tier). Restore accidentally deleted/modified data with a single SQL statement. No backup infrastructure needed.

πŸ›‘οΈ Fail-Safe

7-day read-only recovery window beyond Time Travel. Managed by Snowflake β€” contact support. Not self-service, but provides a safety net for catastrophic incidents. Included in all tiers at no extra cost.

πŸ“‹ Zero-Copy Cloning

Instant copy of any table, schema, or database. Uses no extra storage until the clone diverges from the original. Critical for dev/test β€” clone PROD to DEV in seconds with no data duplication cost.

πŸ”€ Undrop

Accidentally dropped a table? UNDROP TABLE my_table restores it from Time Travel. Works on tables, schemas, and databases within the retention window.

πŸ” Deep Dive: Time Travel SQL Examples

+
-- Query table as it was 1 hour ago SELECT * FROM gold.listings AT(OFFSET => -3600); -- Query at a specific timestamp SELECT * FROM gold.listings AT(TIMESTAMP => '2026-06-10 09:00:00'::TIMESTAMP_TZ); -- Query before a specific statement (use query ID) SELECT * FROM gold.listings BEFORE(STATEMENT => '01ab2c3d-...'); -- Restore: create a new table from Time Travel snapshot CREATE OR REPLACE TABLE gold.listings_recovered AS SELECT * FROM gold.listings AT(TIMESTAMP => '2026-06-10 08:00:00'::TIMESTAMP_TZ); -- Zero-copy clone of PROD database for DEV testing CREATE DATABASE dev_analytics_20260611 CLONE analytics; -- Instant. No storage cost until DEV data diverges from PROD.

09

Cortex AI β€” Built-in ML & Conversational Data

Snowflake Cortex is the AI/ML layer built directly into Snowflake. No separate ML infrastructure, no data movement β€” models run inside Snowflake's compute boundary.

πŸ—£οΈ Cortex Analyst

Natural language to SQL. Connect Power BI / your own app β†’ users ask questions in plain language β†’ Cortex generates and executes SQL. Supports semantic models to define business vocabulary. GDPR compliant: queries stay in EU.

πŸ” Cortex Search

Vector search built into Snowflake. Embed documents, property descriptions, contract text β†’ retrieve by semantic similarity. Powers RAG pipelines without a separate vector DB. Supports hybrid keyword + semantic search.

πŸ€– Cortex LLM Functions

SQL functions that call hosted LLMs: SNOWFLAKE.CORTEX.COMPLETE(), SUMMARIZE(), TRANSLATE(), SENTIMENT(), EXTRACT_ANSWER(). Run on documents, contract text, customer notes in-flight.

πŸ“Š ML Functions

No-code ML: FORECAST() for time-series prediction, ANOMALY_DETECTION(), CLASSIFICATION(). Train and run models with pure SQL β€” no Python environment needed for common ML tasks.

πŸ” Deep Dive: RAG Pipeline with Cortex Search

+

A RAG (Retrieval Augmented Generation) pipeline for property documents β€” contracts, legal deeds, floor plans β€” using only Snowflake Cortex:

-- Step 1: Create a Cortex Search Service on your documents table CREATE CORTEX SEARCH SERVICE property_docs_search ON document_text ATTRIBUTES (property_id, document_type, document_date) WAREHOUSE = etl_wh TARGET_LAG = '1 hour' AS (SELECT property_id, document_type, document_date, chunk_text AS document_text FROM silver.documents.property_doc_chunks); -- Step 2: Query via Snowpark Python (or REST API from your app) -- search_service.search("title deed Athens property March 2026", columns=["document_text"]) -- Step 3: Pass retrieved chunks to Cortex COMPLETE for generation SELECT SNOWFLAKE.CORTEX.COMPLETE( 'mistral-large2', CONCAT('Answer based on: ', retrieved_chunks, '\n\nQuestion: ', :user_question) ) AS answer;

This entire pipeline runs within Snowflake's EU infrastructure. No data leaves the EU boundary. No separate vector database. No API calls to external LLM providers (Snowflake hosts the models).

πŸ” Deep Dive: Snowpark β€” Python Inside Snowflake

+

Snowpark lets you write transformations in Python (or Java/Scala) that execute inside Snowflake's compute β€” your code runs next to the data, not on your laptop pulling data over a network.

Use cases: custom ML feature engineering, complex transformations not expressible in SQL, custom Python UDFs, calling external APIs from within Snowflake.

# Snowpark Python β€” runs inside Snowflake's compute from snowflake.snowpark import Session session = Session.builder.configs(connection_params).create() # DataFrames work like pandas but execute in Snowflake listings = session.table("silver.listings.dim_property") enriched = listings.filter(col("status") == "active") \ .with_column("price_per_sqm", col("price") / col("area_sqm")) \ .group_by("property_type") \ .agg(avg("price_per_sqm").alias("avg_price_sqm")) enriched.write.save_as_table("gold.listings.price_benchmarks")

10

Open Standards, DevOps & Multi-Cloud

Snowflake's stance on open standards has evolved significantly. These are the key capabilities relevant to avoiding lock-in and supporting multi-cloud deployment.

RequirementSnowflake AnswerStatus
Open table format Apache Iceberg β€” native read/write. Tables stored in your S3/ADLS bucket in Iceberg format. Queryable by Spark, Flink, Athena without Snowflake. Native
Open file format Parquet, Avro, ORC β€” both ingest and export. COPY INTO @stage TYPE='PARQUET' Native
Metadata catalog Apache Polaris β€” open-source Iceberg catalog donated by Snowflake. REST Catalog API compatible. Native
Multi-cloud (Azure + AWS) Snowflake runs natively on Azure and AWS. Cross-cloud replication available. EU regions: Azure West Europe (Amsterdam), AWS eu-west-1 (Ireland), AWS eu-central-1 (Frankfurt). Native
IaC / Terraform Official Snowflake Terraform provider. Manage warehouses, databases, roles, pipes as code. Native
CI/CD / Git Snowflake CLI + Git integration. dbt Cloud has native Snowflake CI/CD. schemachange for SQL migrations. Via dbt/CLI
Data Sharing Snowflake Secure Data Sharing β€” share live data with partners without copying. Zero ETL, no Snowpipe needed. Partners can be on different clouds. Native

πŸ” Deep Dive: DevOps Stack for Snowflake

+

A production-grade Snowflake DevOps stack typically combines these tools:

LayerToolPurpose
IaCTerraform + Snowflake providerProvision warehouses, databases, roles, stages. Version-controlled infrastructure.
Transformationsdbt Core / dbt CloudSQL transformations, tests, docs, lineage graph. Git-based version control of all models.
CI/CDGitHub Actions / Azure DevOpsdbt test on PR, deploy to staging on merge, promote to prod on release tag.
Schema migrationsschemachangeOrdered SQL scripts applied idempotently β€” like Flyway for Snowflake.
OrchestrationAirflow / Prefect / dbt CloudDAG scheduling for complex multi-system pipelines. Simple flows use Tasks instead.
Data qualitydbt tests + Great ExpectationsSchema tests, row counts, freshness checks. Alert on failures before data reaches BI.
ObservabilitySnowflake Query History + MontecarloQuery performance, pipeline reliability, data lineage.

11

Sizing for Real Workloads

Translating raw numbers into a warehouse design. Key inputs: daily event volume, concurrent users, data freshness requirement, query complexity.

Workload SignalInputSnowflake Implication
Portal events/day80,000 avg / 250,000 peakSnowpipe handles this trivially. Even at 10Γ— growth no infrastructure change needed.
New listings/day~70Batch load from Azure SQL via Fivetran/Airbyte or direct JDBC. M warehouse, daily schedule.
Current data volume150 GB usableStorage cost ~€3.50/month. Query performance at this size is very fast β€” XS warehouse can handle most BI queries.
Mid-term growth1 TBStill small for Snowflake. No architecture changes at 1 TB. Clustering keys may help for the largest fact tables at 5–10 TB.
Concurrent users50 internal + 120 partner portalMulti-cluster BI warehouse (min=1, max=2) handles this. Total ~170 concurrent users is well within 1 burst cluster.
GA4 sessions/day6,800 avg / 10,000 peakGA4 BigQuery export β†’ GCS β†’ Snowpipe, or direct Fivetran GA4 connector. Very low volume.

Bottom line: At 150 GB–1 TB and <200 concurrent users, this is a small-to-medium Snowflake deployment. All workloads fit comfortably on Standard tier warehouses. Business Critical tier is required only for HIPAA-equivalent compliance or cross-region replication for disaster recovery β€” GDPR is satisfied on Standard + EU region selection.


12

Key Challenges & Solutions in Large Data Platform Programs

Theory is clean. Real programs are not. These are the failure modes that repeatedly derail data platform initiatives β€” and the specific patterns that prevent them. Grouped by the phase in which they typically surface.

πŸ’° Cost & Compute

⚠️ Challenge 1 β€” Runaway Credits: "The Bill Shock"

+

What goes wrong: A developer runs a full table scan on a 10 TB table using an XL warehouse for debugging. A BI dashboard has a misconfigured always-on refresh that hits Snowflake every 30 seconds. A scheduled dbt job fails silently and retries in a tight loop. The warehouse never auto-suspends because a long-running query keeps it alive. Month-end invoice is 4Γ— the estimate.

Why it's common: The pay-per-second model feels invisible until it isn't. Engineers who come from fixed-infrastructure backgrounds don't build the mental habit of checking credit burn.

PreventionHow to implement it
Resource monitors on every warehouseSet CREDIT_QUOTA per warehouse per month. Alert at 75%, suspend at 100%. Include the account-level monitor as a safety net. One SQL statement at account creation β€” never skip this.
Statement timeoutALTER WAREHOUSE etl_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 3600. Kills queries running over 1 hour β€” catches runaway scans before they drain the budget.
Query tagging + attributionTag every session with ALTER SESSION SET QUERY_TAG = 'dbt:silver:portal_events'. Analyse ACCOUNT_USAGE.QUERY_HISTORY weekly to identify top credit consumers by team/job/model.
Auto-suspend disciplineETL: 300s. BI: 60s. Ad-hoc: 60s. DS: 600s. Never set AUTO_SUSPEND=0 (disables it). The biggest single cost-reduction lever across all Snowflake deployments.
Dedicated small warehouse for developmentXS warehouse for all exploration and debugging. Prevents engineers from inadvertently running experiments on the L warehouse that runs production dbt.
-- Weekly credit burn by warehouse and query tag (add to a Snowflake Alert) SELECT warehouse_name, query_tag, SUM(credits_used_cloud_services + credits_used_compute) AS total_credits, COUNT(*) AS query_count, AVG(execution_time) / 1000 AS avg_seconds FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP()) GROUP BY 1, 2 ORDER BY total_credits DESC LIMIT 20;

⚠️ Challenge 2 β€” Concurrency Scaling Surprises

+

What goes wrong: The BI warehouse is correctly sized for average load. But Monday morning at 9 AM, every regional sales manager opens Power BI simultaneously. The warehouse queue fills, concurrency scaling kicks in, and 40 burst clusters spin up for 2 hours. The daily credit budget is consumed in 90 minutes.

The model mismatch: Concurrency scaling charges per burst cluster per hour. At 2 burst clusters running for 2 hours on an M warehouse (4 credits/hr each): 2 Γ— 4 Γ— 2 = 16 bonus credits on top of the base warehouse. Harmless once. Catastrophic if it happens every Monday and Wednesday.

SolutionDetail
Switch to multi-cluster warehouseSet MAX_CLUSTER_COUNT = 2 (not concurrency scaling). You control the cap. Credit spend per extra cluster is predictable, not unlimited.
Stagger BI refresh timesPower BI reports that auto-refresh can be staggered (08:55, 09:05, 09:15 instead of all at 09:00). Reduces peak queue depth dramatically.
Materialise heavy Gold models as tables, not viewsA Gold "view" forces recomputation on every BI hit. A materialised table returns in milliseconds. The query against the BI warehouse is then simple and fast β€” no concurrency spike.
Result cache exploitationIdentical queries return from cache in <100ms, zero credits. Design dashboards so the most common filters hit the same query text. Avoids re-execution entirely during morning rush.
πŸ”¬ Data Quality & Trust

⚠️ Challenge 3 β€” The "I Don't Trust This Number" Problem

+

What goes wrong: The sales director runs the same revenue query in Power BI and in Snowsight and gets different numbers. The marketing team's pipeline counts users differently from the CRM team's pipeline. Nobody knows which is right. The data platform is technically functioning β€” data is flowing β€” but trust collapses. Users go back to Excel. The platform's business value evaporates.

Root cause: Multiple transformation paths producing the same metric with subtly different business logic. No single authoritative definition. No data contract between teams.

SolutionImplementation
Single source of truth for every metricEvery business metric lives in exactly one dbt model in the Gold layer. Power BI and all other tools read from that one model. No parallel calculations in BI tool layers. "Revenue is defined in gold.fact_revenue, period."
dbt metrics layer / semantic layerdbt MetricFlow defines metric: total_revenue as a versioned, documented definition. Any BI tool that queries via the semantic layer gets the identical calculation β€” eliminates divergence at the definition level.
Data contractsEach dbt source declares expected schema, freshness SLA, and row count ranges. A schema change upstream that breaks a contract fails the CI pipeline before it reaches production. Source teams are accountable for their contract.
Visible data freshness in dashboardsEvery Gold table has a _dbt_updated_at timestamp. Power BI shows "Last updated: 14 minutes ago" in the footer. Users know when data is stale β€” they stop assuming the number is wrong when it's actually just not refreshed yet.
-- dbt source freshness check β€” fails CI if portal_events is > 2 hours stale sources: - name: raw tables: - name: portal_events loaded_at_field: _loaded_at freshness: warn_after: {count: 1, period: hour} error_after: {count: 2, period: hour} -- dbt test β€” fails CI if any listing_id in fact table has no match in dimension models: - name: fact_listing_activity columns: - name: listing_id tests: - not_null - relationships: to: ref('dim_property') field: listing_id

⚠️ Challenge 4 β€” Silent Pipeline Failures

+

What goes wrong: The Fivetran connector for Dynamics 365 silently fails due to an OAuth token expiry. The Silver CRM models run on stale data for 3 days. Nobody notices because the dashboards still load β€” the numbers just stop moving. A sales manager notices "the CRM data looks frozen" in a weekly review meeting, 3 days after the break.

The pipeline observability gap: Most teams instrument "did the job run?" but not "did the job produce correct output?" A job that runs and writes zero rows is technically successful by the scheduler.

SolutionHow
Row count assertions on every loaddbt test: assert_row_count_gte: 1 on every incrementally-loaded table. A zero-row load after a 30-minute pipeline run fails the test and fires an alert.
Freshness monitoring via Snowflake AlertsSQL Alert: IF MAX(_loaded_at) < DATEADD('hour', -2, CURRENT_TIMESTAMP()) THEN alert. Fires to Slack if any key table hasn't received new data within its expected window. Runs every 30 minutes, costs essentially zero credits.
Source system health checksFivetran provides connector sync logs queryable via its API. Import Fivetran sync status into a Snowflake table and include it in the dbt test suite β€” connector failure becomes a first-class data quality failure.
Dead letter queue for Snowpipe failuresQuery SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY daily for STATUS = 'Load failed'. Route failures to a dead_letter schema for review. Don't let bad files silently disappear.
πŸ”§ Schema Evolution & Pipeline Fragility

⚠️ Challenge 5 β€” Source System Schema Changes Break Pipelines

+

What goes wrong: The application team renames a column in Azure SQL Server from property_status to listing_status during a sprint. Fivetran syncs the schema change. The Silver dbt model that references property_status fails. The Gold listings table stops updating. BI goes stale. Nobody told the data team about the rename because it seemed trivial from an application perspective.

The underlying pattern: Source systems treat schema changes as internal implementation details. Data platforms treat them as breaking API changes. The two worlds need a contract mechanism.

SolutionDetail
Isolate raw layer from Silver layerBronze tables are the blast shield. Silver models reference only Bronze β€” never directly reference source system tables. When Fivetran picks up a column rename, Bronze captures it. You have time to update Silver before anything downstream breaks.
dbt on_schema_change: failConfigure dbt incremental models with on_schema_change: fail during CI, sync_all_columns in production. CI pipeline detects schema drift and requires explicit engineer review before deployment.
Source system change notification SLAEstablish a process: any schema change to a source system that feeds the data platform requires a 2-day notice to the data engineering team. Document this in the data contract. Simple, not technical β€” but critical.
Column aliasing in Bronze staging modelsFirst staging model (Bronze β†’ Silver staging) aliases all columns explicitly: property_status AS listing_status. When the source renames, only one file changes β€” all downstream models are unaffected.
-- Bronze staging model: absorbs source schema changes in one place SELECT id AS listing_id, property_status AS listing_status, -- renamed in source? change here only created_at AS created_ts, price_eur AS price, area_m2 AS area_sqm, _fivetran_synced AS _loaded_at FROM {{ source('raw_rep', 'listings') }}

⚠️ Challenge 6 β€” Full Refresh vs Incremental: Choosing Wrong at Scale

+

What goes wrong: Phase 1 uses materialization: table (full refresh) for every model because it's simple and always correct. At 10 GB of Silver data this is fine β€” each run takes 5 minutes. By the time the platform reaches 500 GB, the full refresh nightly run takes 4 hours, costs 80 credits, and blocks the morning BI load. Switching to incremental at scale requires a full backfill strategy and careful unique key design.

The rule of thumb: Anything over ~1 GB should be incremental from day one. Retrofitting is expensive.

Model typeUse whenPitfall to avoid
View<1 GB source, computation is fast, no materialisation neededViews on large tables scan the full table on every query β€” destroys BI performance at scale
Table (full refresh)Dimension tables (<10M rows), Gold aggregates that need recomputationFull refresh on a 500 GB fact table takes hours β€” switch to incremental before you hit 50 GB
IncrementalAny append-dominant fact table >1 GBMissing a unique_key leads to duplicate rows over time; wrong watermark column skips late-arriving events
Dynamic TableWhen you want Snowflake to manage the refresh schedule automatically based on downstream query demandTarget lag configuration can lead to unexpected credit usage if set too aggressively
-- Incremental model with late-arriving data handling {{ config( materialized='incremental', unique_key='event_id', on_schema_change='sync_all_columns' ) }} SELECT ... FROM {{ source('raw', 'portal_events') }} {% if is_incremental() %} -- Look back 3 hours to catch late-arriving events WHERE event_ts >= DATEADD('hour', -3, (SELECT MAX(event_ts) FROM {{ this }})) {% endif %}
πŸ” Governance & Security

⚠️ Challenge 7 β€” RBAC Sprawl: "Just Grant Them SYSADMIN"

+

What goes wrong: In the rush to unblock a developer, someone grants SYSADMIN. "Temporarily." Then a new team joins and someone grants them ACCOUNTADMIN "just to get things moving." After 12 months, 40% of users have elevated privileges they don't need, role inheritance is a spider web nobody understands, and a GDPR audit finds three partner service accounts with read access to the full PII-containing Silver layer.

Why it happens: Proper RBAC design takes an upfront investment that feels like it slows delivery. The cost is paid months later in an audit or a breach.

PatternRule
Principle of least privilege, enforced by TerraformEvery role and privilege is defined in Terraform. No manual GRANT statements outside of Terraform. Role changes require a PR review β€” prevents one-off privilege escalations.
Functional roles, never person rolesNever CREATE ROLE mahesh_admin. Always CREATE ROLE transformer_role and assign users to it. When a person leaves, revoke the user β†’ role assignment. All their privileges go with it, automatically.
Time-limited elevated accessFor maintenance tasks requiring ACCOUNTADMIN: create a session-scoped elevation via a stored procedure that logs the access and auto-revokes after 4 hours. Never a standing ACCOUNTADMIN grant for individuals.
Quarterly access reviewAutomated report from ACCOUNT_USAGE.GRANTS_TO_USERS cross-referenced with HR active employees list. Flag all service accounts inactive for 30+ days. Review and revoke.
-- Automated access review query β€” run monthly, pipe to Slack SELECT grantee_name AS username, role AS granted_role, granted_by, created_on AS granted_date, DATEDIFF('day', created_on, CURRENT_DATE()) AS days_held FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS WHERE role IN ('SYSADMIN', 'ACCOUNTADMIN') AND deleted_on IS NULL ORDER BY days_held DESC;

⚠️ Challenge 8 β€” PII Leakage via Ad-Hoc Queries and Exports

+

What goes wrong: An analyst writes SELECT * FROM silver.users LIMIT 1000 to debug a join issue, exports the result to CSV, and shares it in a Slack thread. It contains email addresses and phone numbers. The Silver layer was never meant to be exposed to analysts directly β€” only the masked Gold views were. The GDPR DPA says that data. Should not. Have left. Snowflake.

The layered defence:

LayerControlWhat it prevents
1. Role accessANALYST_ROLE has SELECT only on analytics_db (Gold), never on silver_dbPrevents Silver direct access entirely
2. Dynamic masking on SilverEven if someone somehow gets Silver access, PII columns return ****@domain.comDefence-in-depth for misconfigured grants
3. Network policySnowflake network policy restricts access to corporate IP ranges onlyPrevents credential theft + access from personal devices
4. Access history monitoringACCOUNT_USAGE.ACCESS_HISTORY logs every column accessed by every query. Alert when a service account accesses a PII-tagged column it hasn't touched before.Detects anomalous access patterns before they become incidents
5. Object tagging for PIITag every PII column: ALTER TABLE users MODIFY COLUMN email SET TAG pii_tag = 'EMAIL'. Query all PII columns across the account in one SELECT.Makes PII inventory auditable β€” no manual spreadsheet tracking
πŸ§‘β€πŸ€β€πŸ§‘ Organisation & Adoption

⚠️ Challenge 9 β€” Data Ownership Vacuum: "Who Owns This Table?"

+

What goes wrong: Six months after go-live, there are 340 tables in the Gold schema. Nobody knows which ones are still used, which team built which model, or who to contact when a table has wrong data. Three teams each have a fact_sessions table with different column names and different definitions of "session." The data platform has become a data swamp with Snowflake paint.

The organisational pattern that prevents this:

PracticeImplementation
Data domain ownershipEvery schema and every Gold model has an owning team declared in dbt metadata: meta: {owner: 'growth-team', team_slack: '#data-growth'}. Surfaced automatically in dbt docs. One search shows who to contact.
Table deprecation processTables with zero queries in 90 days (queryable from ACCOUNT_USAGE.QUERY_HISTORY) are flagged for deprecation. Owner team notified. 30-day notice before drop. Prevents accumulation of zombie models.
Data product mindsetInstead of "Gold table", think "Data Product": versioned, documented, SLA-committed, owned. Teams publish data products with freshness guarantees and column-level documentation. Consumers subscribe and are notified of breaking changes.
Data council (lightweight)Monthly 45-minute cross-team sync: review new models proposed, deprecations, schema changes planned. Not bureaucracy β€” a forcing function for communication that prevents the ownership vacuum.

⚠️ Challenge 10 β€” "Users Won't Leave Excel"

+

What goes wrong: The platform is technically complete, fast, accurate. But the senior analysts still pull CSVs into Excel for their weekly reports. "It's just easier." "I know how to do the vlookup." "I don't trust the Power BI filter." The platform investment delivers no ROI because the behaviour change never happened.

Why this happens: Technical delivery and organisational change management are treated as separate projects. They're not. Adoption is a feature, not a phase.

What actually worksWhy
Embed with the first power usersFind 2–3 analysts who have credibility with their peers. Build their most painful report in Power BI with them, not for them. When peers see it works, adoption spreads laterally.
Make the platform faster than Excel for the common caseIf the most-used Excel report takes 2 hours to refresh manually and the Snowflake-backed dashboard takes 5 minutes, the economic argument makes itself. Start with the highest-pain, most-repeated manual work.
Cortex Analyst for non-technical usersAsking a question in plain English and getting an answer in 10 seconds removes the SQL barrier entirely. For non-analysts, this is more important than any BI tool. Deploy it early, even with a limited semantic model.
Show data lineage to build trustUsers who can trace a number from its source β€” "this revenue figure comes from Azure SQL Server, transformed through this logic, last updated 12 minutes ago" β€” trust it. dbt docs provide this trace in one click. Publish the lineage graph internally.
⚑ Performance at Scale

⚠️ Challenge 11 β€” Queries That Were Fast at 10 GB Are Slow at 500 GB

+

What goes wrong: A Gold model that runs in 30 seconds at launch takes 18 minutes 18 months later. The data grew 50Γ— but the query logic and warehouse size never changed. The BI dashboard that "used to be instant" now times out. Users complain. Engineers assume the platform is broken.

The performance debugging toolkit:

-- Step 1: Find the slow query ID in Query History SELECT query_id, query_text, execution_time / 1000 AS seconds, bytes_scanned / 1e9 AS gb_scanned, partitions_scanned, partitions_total, ROUND(partitions_scanned * 100.0 / NULLIF(partitions_total, 0), 1) AS pct_scanned FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE execution_time > 60000 -- > 60 seconds AND start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP()) ORDER BY execution_time DESC; -- Step 2: Check partition pruning in Query Profile (Snowsight UI) -- If pct_scanned is near 100%, the query can't prune β€” needs a cluster key -- If pct_scanned is low but still slow β€” warehouse is too small
SymptomDiagnosisFix
100% partitions scanned, slowNo useful cluster key for the WHERE clauseAdd CLUSTER BY (event_date) on the fact table β€” enables pruning for date-filtered queries
Low partitions scanned, still slowWarehouse too small for the data readScale warehouse up one size. Check if the Gold model can be pre-aggregated further.
Spill to remote storageWarehouse ran out of local SSD cache β€” spilling to cloud storage (10–100Γ— slower I/O)Scale warehouse up. Reduce result set size (aggregations, projections). Avoid SELECT *.
Many small queries all slowWarehouse cold start on every queryCheck AUTO_SUSPEND. If it's 60s for a heavily-used BI warehouse, increase to 300s to keep cache warm between queries.
JOIN explosion (row count spikes)Missing or wrong join cardinality β€” Cartesian productUse Query Profile to find the exploding node. Add unique key tests to dbt to catch this in CI.

⚠️ Challenge 12 β€” The dbt Model Explosion

+

What goes wrong: After 18 months, the dbt project has 400+ models. The full dbt run takes 3 hours. A developer making a change to one model has no idea which of the 400 downstream models might break. CI takes 45 minutes. Every PR is a gamble. The project has become unmaintainable.

Prevention patterns:

PatternWhy it helps
dbt state:modified+ in CIOnly re-runs models changed in the PR and their downstream dependents. A change to one Bronze staging model only re-runs that model + its Silver + Gold descendants β€” not all 400 models.
Thin staging models, fat transformationsBronze staging models are 1:1 with source tables, purely renaming/casting. Complexity lives in Silver. This makes Bronze models trivially fast and disposable β€” regenerate any time without cascading impact.
dbt project splitting by domainAt 200+ models, split into sub-projects by data domain (REP, Marketing, SDLC, Finance). Each sub-project is independently runnable. Cross-domain references go through a shared semantic layer.
Model performance SLAs in dbt metaTag each model: meta: {max_execution_seconds: 120}. A dbt macro checks execution time in CI and fails if a model exceeds its SLA. Engineers are forced to optimise before merging a slow model.
πŸ“‹ Program Delivery

⚠️ Challenge 13 β€” Source System Access Delays the Entire Program

+

What goes wrong: The implementation starts. Week 3: Fivetran connector for Azure SQL Server is ready. The data engineering team is waiting for read-only credentials and network firewall rules from the application team. The application team's DBA is on leave. Week 5: credentials provided, but the wrong database is whitelisted. Week 7: correct access, but the user doesn't have read on 3 of the 12 tables needed. This is the #1 cause of data platform programme slippage β€” not technical complexity.

MitigationWhen to do it
Access prerequisite checklist agreed before kick-offDuring scoping, before contract signed. A formal list: "we need read access to X tables in Azure SQL, a service principal for Azure Event Hubs, OAuth credentials for Dynamics 365, GA4 BigQuery export enabled." Each item has a named owner and a due date.
Parallel workstream: access + pipeline buildBuild Snowflake infrastructure, RBAC, CI/CD, and dbt scaffolding while access is being provisioned. Don't block. When access arrives, the connector + staging model is a 1-day task, not a 3-day task.
Mock data for early-phase testingGenerate synthetic data matching the expected schema for every source. Test the full Bronze β†’ Silver β†’ Gold pipeline with mock data. When real credentials arrive, swap the source β€” the logic is already proven.
Prioritise by access readiness, not business valueIf GA4 and Jira credentials are available day 1 and Azure SQL takes 3 weeks, build GA4 and Jira pipelines first. Deliver value while waiting for the harder access. Don't let blocked items halt all work.

⚠️ Challenge 14 β€” Scope Creep: "While You're At It…"

+

What goes wrong: Phase 1 is defined as REP data + Power BI. During delivery, the CTO asks "while you're connecting to Dynamics, can you also pull finance data?" The CFO asks "can we add the HR system? It's only 3 tables." The data science team asks "can you add a new warehouse for ML experiments?" Each request seems small. Collectively they extend Phase 1 from 8 weeks to 22 weeks, blow the budget, and delay every phase that follows.

PatternImplementation
Strict phase scope agreement, signed offPhase 1 scope is a written document, signed by the sponsor. "In scope: X. Out of scope: Y." Any addition to Phase 1 scope requires a formal change request with cost and schedule impact explicitly stated. Not bureaucracy β€” protection for both sides.
Backlog for good ideasEvery "while you're at it" request goes into a prioritised backlog, not into the current sprint. This validates the request ("yes, we'll do it") without committing to a date. Phase 2 planning draws from this backlog.
Time-box exploration workGenuine spikes (e.g., "evaluate the Dynamics 365 connector") get a timebox: 1 day, deliver findings. If the findings say it's easy, schedule it. If it's hard, it goes to a future phase. Never start unbounded exploration mid-delivery.
Quick Reference β€” Challenge β†’ Priority Action
#ChallengeHighest-priority actionWhen to act
1Runaway creditsResource monitor on every warehouse, day 1Before go-live
2Concurrency scaling surpriseSwitch BI warehouse to multi-cluster with explicit MAXBefore go-live
3Number trust collapseSingle canonical Gold model per metric, dbt semantic layerPhase 1
4Silent pipeline failuresRow count assertions + freshness Snowflake AlertsPhase 1
5Source schema breaks pipelinesAliasing in Bronze staging layer; source team change notice SLAPhase 1
6Full refresh performance cliffIncremental models from day 1 for all tables >1 GBPhase 1
7RBAC sprawlAll grants in Terraform; no manual GRANTs; quarterly reviewBefore go-live
8PII leakageObject tagging; analyst access only to masked Gold layerBefore go-live
9Data ownership vacuumOwner metadata in every dbt model; table deprecation processPhase 2
10Low adoption / Excel dependencyEmbed with power users; Cortex Analyst for non-technical staffPhase 1 launch
11Query performance degradationWeekly slow query review; partition pruning check; cluster keys at 50 GB+Ongoing
12dbt model explosionstate:modified+ CI from the start; domain-split at 200 modelsPhase 1
13Source access delaysAccess prerequisite checklist before kick-off; mock data in parallelBefore kick-off
14Scope creepSigned scope doc; all additions go to backlog, not current sprintBefore kick-off

πŸ“–

Quick Glossary

TermDefinition
Virtual Warehouse (VW)Named cluster of compute VMs. Independent from storage. Billed per second when running.
CreditSnowflake billing unit. 1 credit = 1 VW node running 1 hour. XS=1cr/hr, M=4cr/hr, XL=16cr/hr.
Micro-partition50–500MB columnar storage unit. Snowflake manages these automatically. Enables partition pruning.
StageNamed pointer to a file location (Snowflake-managed or external cloud bucket). Landing zone for data files before COPY INTO.
SnowpipeServerless continuous ingestion. Files in cloud storage β†’ event notification β†’ table load within ~1 minute.
StreamCDC object that tracks INSERT/UPDATE/DELETE on a source table. Offset advances when stream is consumed in a DML transaction.
TaskScheduled SQL statement or stored procedure. Cron-style scheduling built into Snowflake. Can form DAG pipelines.
Time TravelQuery historical data up to 90 days in the past. Restore accidentally deleted data.
Fail-safe7-day recovery beyond Time Travel. Managed by Snowflake support, not self-service.
Zero-copy cloneInstant copy of table/schema/database. No storage cost until data diverges from original.
UndropRestore a dropped table/schema/database from Time Travel.
RBACRole-Based Access Control. All access goes through roles, not direct user grants.
Dynamic MaskingColumn shows real data to authorized roles, masked data to others β€” same table, same query.
Row Access PolicyFilter table rows based on current user's role or session attributes. Row-level security.
Concurrency ScalingAuto-added burst clusters when warehouse queue fills. First 1 credit/day free.
Multi-cluster WarehouseWarehouse with MIN/MAX cluster settings. Scales out for high-concurrency BI workloads.
Resource MonitorCredit quota on a warehouse or account. Alert/suspend at threshold to control costs.
dbtData build tool. SQL templating + testing + lineage for Snowflake transformations. Industry standard for the Silver/Gold layers.
Medallion ArchitectureBronze (raw) β†’ Silver (conformed) β†’ Gold (analytics) data organisation pattern.
SnowparkPython/Java/Scala API to run code inside Snowflake's compute. No data export needed for ML/transformations.
CortexSnowflake's AI layer. Includes Cortex Analyst (NL→SQL), Cortex Search (vector search), LLM functions, ML functions.
Iceberg TableTable stored in Apache Iceberg format on your cloud bucket. Queryable by other engines. Snowflake's open-standards exit ramp.
FDN (Foundation format)Snowflake's internal columnar storage format. Not user-accessible but optimised for their query engine.
Secure Data SharingShare live Snowflake tables with partners or consumers β€” zero copy, zero ETL, near real-time.