Skip to main content
📋

Quick Reference

Cheat Sheets

Condensed, exam-ready reference material across all six COF-C02 domains. Bookmark this page for last-minute revision before your exam.

🏗️ Architecture 🔒 Security ⚡ Performance 📥 Data Loading 🔄 Transformations 🛡️ Data Protection
1

Architecture & Features (25%)

📋
Quick Reference

Snowflake Editions — Feature Matrix

🟦
Standard
Time Travel
Up to 1 day maximum
Warehouses
Single-cluster only
Security
Basic RBAC only
Compliance
SOC 2 Type II
🟨
Enterprise (adds on top of Standard)
Time Travel
Up to 90 days (configurable per object)
Warehouses
Multi-cluster warehouses— For high concurrency
Security
Column-level Security + Row Access Policies— Dynamic Data Masking
Other
Search Optimisation, Database Replication— Periodic rekeying
🟧
Business Critical (adds on top of Enterprise)
Compliance
HIPAA, PCI DSS, SOC 1 & 2, ISO 27001
Encryption
Tri-Secret Secure — customer-managed keys
Network
Private Link (AWS / Azure / GCP)— No public internet
DR
Business Continuity Failover & Fallback
🟥
VPS — Virtual Private Snowflake
Isolation
Dedicated single-tenant hardware
Use case
Government, defence, highest-security sectors
📋
Quick Reference

Virtual Warehouse Sizes & Credits

⚡
Credit Consumption (per hour)
X-Small
1 credit/hour— Development & testing
Small
2 credits/hour
Medium
4 credits/hour
Large
8 credits/hour
X-Large
16 credits/hour
2X-Large
32 credits/hour
3X-Large
64 credits/hour
4X-Large
128 credits/hour— Maximum size
💰
Billing Rules
Billing
Per second, 60-second minimum per resume
Suspended
Zero credits consumed when suspended
Auto-suspend
Default: 600 seconds (10 minutes) idle
Cloud Services
Free up to 10% of daily compute credits
📋
Quick Reference

Snowflake Object Hierarchy

🗂️
Container Hierarchy (top to bottom)
Org
Organisation — optional grouping of multiple accounts
Account
Tied to one cloud provider + one region— Cannot span clouds
Database
Top-level container for schemas
Schema
Container for all objects (tables, views, stages, etc.)
📦
Schema-Level Objects
Tables
Permanent, Transient, Temporary, External
Views
Standard, Secure, Materialized
Stages
User (@~), Table (@%), Named (internal/external)
Pipes
Snowpipe continuous loading definitions
Streams
Change Data Capture (CDC) tracking
Tasks
Scheduled SQL execution
Functions
UDFs, UDTFs (JavaScript, Python, SQL, Java)
Procedures
Stored Procedures (JavaScript, Python, Snowpark)
File Formats
CSV, JSON, Parquet, Avro, ORC, XML
Sequences
Auto-incrementing counter
📋
Quick Reference

Three-Layer Architecture

🏗️
Layers
Storage
Cloud object storage (S3/Azure Blob/GCS) — columnar, compressed micro-partitions— Managed by Snowflake
Compute
Virtual Warehouses — independent MPP clusters— Scale independently of storage
Services
Cloud Services Layer — auth, metadata, query optimisation, access control— Free up to 10% daily compute
🗃️
Micro-Partition Facts
Size
50–500 MB compressed (typically 16–160 MB uncompressed)
Format
Columnar — each column stored separately within partition
Immutable
Never updated in place — new micro-partitions created on change— Enables Time Travel
Metadata
Min/max, NDV, null count per column — enables pruning
2

Account Access & Security (20%)

📋
Quick Reference

Snowflake System Roles

👤
Built-in System Roles
ACCOUNTADMIN
Highest privilege — manages entire account, billing, users— Restrict to ≤2 users
SYSADMIN
Creates/manages databases, warehouses, schemas, tables— Most common admin role
SECURITYADMIN
Manages users, roles, network policies, grants/revokes
USERADMIN
Creates and manages users and roles only— Subset of SECURITYADMIN
PUBLIC
Default role granted to every user automatically— Lowest privilege
🔺
Role Hierarchy Rule
Inherit
Roles inherit all privileges of roles GRANTED to them
GRANT
GRANT ROLE child_role TO ROLE parent_role— Parent inherits child's privileges
Best practice
Custom roles should roll up to SYSADMIN— So SYSADMIN can manage all objects
📋
Quick Reference

Key Privileges Reference

🏛️
Account-Level Privileges
CREATE DATABASE
Create new databases— Grant to SYSADMIN
CREATE WAREHOUSE
Create virtual warehouses
CREATE ROLE
Create new roles— Granted to USERADMIN
CREATE USER
Create new users
MANAGE GRANTS
Grant any privilege to any role— SECURITYADMIN has this
📋
Object-Level Privileges
OWNERSHIP
Full control of an object (transfer, drop)— One role per object
USAGE
Required to USE a database, schema, or warehouse— Needed to access child objects
SELECT
Query table or view data
INSERT/UPDATE/DELETE
Modify table data
CREATE
Create objects within a schema or database
REFERENCES
Create foreign key constraints
📋
Quick Reference

Authentication Methods

🔑
Methods
Password
Username + password — default method
Key-pair
RSA private/public key — programmatic access (CLI, connectors)
MFA
Duo Security — time-based one-time password— Recommended for ACCOUNTADMIN
SAML 2.0
Federated SSO via identity providers (Okta, ADFS, Azure AD)
OAuth 2.0
Delegated access — Snowflake OAuth or External OAuth
3

Performance Concepts (15%)

📋
Quick Reference

Snowflake's Three Cache Layers

1️⃣
Result Cache
What
Stores the exact result set of a completed query
Duration
24 hours — reset if underlying data changes
Cost
FREE — no warehouse credits consumed
Condition
Same SQL text + same user privileges + data unchanged
2️⃣
Local Disk Cache (Data Cache)
What
Caches micro-partition data on warehouse SSD storage
Duration
Until warehouse is suspended (lost on suspend)
Cost
Credits consumed (warehouse must be running)
Best for
Repeated queries against same data — warm cache
3️⃣
Metadata Cache (Cloud Services)
What
Stores metadata — row counts, min/max, table structure
Duration
Persistent in Cloud Services layer
Cost
FREE (Cloud Services — up to 10% of compute)
Best for
SHOW commands, COUNT(*) without WHERE, MIN/MAX
📋
Quick Reference

Query Performance — Key Concepts

✂️
Pruning & Optimisation
Pruning
Snowflake skips micro-partitions based on column min/max metadata
Effective
Predicates on low-cardinality columns benefit most from pruning
Clustering
Clustering keys improve pruning on frequently filtered columns
EXPLAIN
EXPLAIN <sql> — shows logical execution plan
💿
Spilling to Disk
Local spill
Data spills to warehouse SSD when memory is exceeded— Slower than memory
Remote spill
Data spills to cloud storage (S3/Blob) when SSD is full— Much slower
Fix
Increase warehouse size or optimise query (reduce data volume)
📈
Multi-Cluster Scaling
Scale up
Larger warehouse size — handles bigger individual queries
Scale out
More clusters — handles more concurrent users/queries
Auto-scale
Clusters added/removed based on queue depth— Enterprise+
Maximized
All clusters run simultaneously regardless of load
📋
Quick Reference

Search Optimisation Service (SOS)

🔍
Key Facts
Purpose
Accelerates point lookup queries on large tables
Helps with
Equality predicates, IN lists, substring/ILIKE searches, geo
Enable
ALTER TABLE t ADD SEARCH OPTIMIZATION ON EQUALITY(col)
Cost
Additional serverless credits for maintenance— Enterprise+ required
vs Clustering
SOS: point lookups. Clustering: range scans, large table scans
4

Data Loading & Unloading (15%)

📋
Quick Reference

Stage Types Reference

🏠
Internal Stages
@~
User stage — private to each user, cannot be shared— PUT/GET only
@%<table>
Table stage — one per table, accessible by table owners
@<name>
Named internal stage — shared, schema-level object, most flexible
☁️
External Stages
S3
Amazon S3 — URL: s3://bucket/path
Azure
Azure Blob Storage — URL: azure://account.blob.core.windows.net/container
GCS
Google Cloud Storage — URL: gcs://bucket/path
⌨️
Stage Commands
PUT
Upload file from local machine to internal stage— SnowSQL only
GET
Download file from internal stage to local machine— SnowSQL only
LIST
LIST @mystage — shows files in a stage
REMOVE
REMOVE @mystage/file.csv — delete from stage
📋
Quick Reference

COPY INTO Command Reference

📥
Key Options (Loading)
ON_ERROR
CONTINUE, SKIP_FILE, SKIP_FILE_n, ABORT_STATEMENT
PURGE
Delete staged files after successful load
FORCE
Re-load files already loaded (bypasses load metadata)
MATCH_BY_COLUMN_NAME
Map JSON/Parquet keys to column names automatically
FILES
Specify a list of files to load from the stage
PATTERN
Regex pattern to filter files: PATTERN='.*sales.*\.csv'
📜
Load History
64 days
Snowflake tracks loaded files for 64 days per table
COPY_HISTORY
INFORMATION_SCHEMA.LOAD_HISTORY — per database
ACCOUNT_USAGE
SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY — all tables— 365-day history
📋
Quick Reference

Snowpipe Quick Reference

🚰
Key Facts
What
Serverless continuous data loading — triggered by file arrival
Auto-ingest
Cloud event notifications (SQS/Event Grid/Pub-Sub) trigger pipe— Fully automatic
REST API
insertFiles() or insertReport() for manual triggering
Billing
Per-file serverless credit model — no warehouse needed
Status
SYSTEM$PIPE_STATUS('pipe_name')
Latency
Near-real-time (minutes, not seconds)
5

Data Transformations (15%)

📋
Quick Reference

Semi-Structured Data (VARIANT)

{ }
Key Operators & Functions
:
Path access — obj:key or obj:key:nested— e.g. src:address:city
::
Type cast — obj:key::STRING
GET_PATH
GET_PATH(obj, 'key.nested') — same as : notation
PARSE_JSON
PARSE_JSON('{}') — convert JSON string to VARIANT
TO_VARIANT
Cast any type to VARIANT
TYPEOF
Returns data type of VARIANT value
FLATTEN
Flattens array/object to rows — use with LATERAL
ARRAY_AGG
Aggregate rows into JSON array
OBJECT_CONSTRUCT
Build JSON object from key-value pairs
📋
Quick Reference

Streams & Tasks Reference

🌊
Stream Types
Standard
Captures INSERT, UPDATE, DELETE — all DML changes
Append-only
Captures INSERT only — more efficient for append workloads
Insert-only
For external tables — INSERT tracking only
📊
Stream Metadata Columns
METADATA$ACTION
INSERT or DELETE— UPDATE = DELETE + INSERT pair
METADATA$ISUPDATE
TRUE if part of an UPDATE operation
METADATA$ROW_ID
Unique immutable row identifier
⏰
Tasks
CRON
SCHEDULE = 'USING CRON 0 9 * * MON-FRI UTC'
Minutes
SCHEDULE = '5 MINUTE'
Tree
AFTER parent_task — child tasks form a DAG
Enable
ALTER TASK mytask RESUME — tasks start in suspended state
Serverless
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE — no warehouse
6

Data Protection & Sharing (10%)

📋
Quick Reference

Time Travel & Fail-safe Reference

⏰
Time Travel
Standard
Max 1 day Time Travel
Enterprise+
Max 90 days Time Travel
Transient/Temp
Max 1 day — regardless of edition
AT OFFSET
AT(OFFSET => -3600) — seconds from now
AT TIMESTAMP
AT(TIMESTAMP => '2024-01-01'::TIMESTAMP_TZ)
AT STATEMENT
AT(STATEMENT => '<query_id>')
UNDROP
UNDROP TABLE/SCHEMA/DATABASE name
🛡️
Fail-safe
Duration
7 days — fixed, non-configurable
Access
Snowflake Support ONLY — no self-service
Applies to
Permanent tables only
Not for
Transient, temporary, or external tables
©️
Zero-Copy Cloning
Cost
Zero initial storage — shares micro-partition metadata
Cloneable
Databases, schemas, tables, stages, streams, tasks
With TT
CLONE ... AT(OFFSET => ...) or BEFORE(STATEMENT => ...)
Privileges
Grantees of original do NOT auto-inherit clone access
📋
Quick Reference

Secure Data Sharing Reference

🤝
Share Concepts
Share
Metadata object created by provider — no data copied
Provider
Account that creates and grants the share
Consumer
Account that creates a read-only DB FROM the share
Live
Consumer always sees provider's current data
⌨️
Key Commands
CREATE SHARE
CREATE SHARE my_share
GRANT
GRANT USAGE ON DATABASE db TO SHARE my_share
ADD ACCOUNT
ALTER SHARE my_share ADD ACCOUNTS = xy12345
Consumer DB
CREATE DATABASE from_share FROM SHARE provider.share
Marketplace
Snowflake Marketplace — public/private data listings

🎯 Top Exam Tips

  • ✓ Snowflake is SaaS — not IaaS or PaaS
  • ✓ Each account = one cloud + one region
  • ✓ 60-second minimum per warehouse resume
  • ✓ Cloud Services free up to 10% of daily compute
  • ✓ Enterprise needed for 90-day TT + multi-cluster
  • ✓ Business Critical needed for HIPAA/PCI
  • ✓ Result cache = FREE (no warehouse needed)
  • ✓ Local disk cache lost when warehouse suspends
  • ✓ UNDROP only works within Time Travel window
  • ✓ Fail-safe = 7 days, Snowflake access only
  • ✓ Transient/Temporary tables = max 1 day TT, no Fail-safe
  • ✓ Streams are consumed (offset advances) only after successful DML