COF-C02 — Snowflake SnowPro Core Certification Quick Reference

How to Use This Quick Reference

This independent Quick Reference supports candidates preparing for the Snowflake SnowPro Core Certification (COF-C02). Use it to review high-yield Snowflake concepts, decision points, SQL patterns, and common exam traps.

Focus on recognizing what the scenario is really asking:

Snowflake Architecture Mental Model

Snowflake separates storage, compute, and cloud services. Many COF-C02 questions test whether you know which layer solves the problem.

Object Hierarchy

Feature Selection Matrix

Roles, Users, and Privileges

Snowflake uses role-based access control. Users receive roles; roles receive privileges; roles can be granted to other roles to form a hierarchy.

System and Common Role Types

Privilege Patterns

High-Yield RBAC Rules

Common Grant Pattern

USE ROLE SECURITYADMIN;

CREATE ROLE analyst_ro;

GRANT USAGE ON WAREHOUSE wh_bi TO ROLE analyst_ro;

GRANT USAGE ON DATABASE analytics TO ROLE analyst_ro;
GRANT USAGE ON SCHEMA analytics.mart TO ROLE analyst_ro;

GRANT SELECT ON ALL TABLES IN SCHEMA analytics.mart TO ROLE analyst_ro;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.mart TO ROLE analyst_ro;

GRANT ROLE analyst_ro TO USER j_smith;

Managed Access Schema Pattern

USE ROLE SYSADMIN;

CREATE SCHEMA analytics.secure WITH MANAGED ACCESS;

Use managed access when the scenario requires centralized privilege control within a schema.

Warehouses and Compute

A virtual warehouse is compute. It can be started, suspended, resized, and configured for concurrency.

Warehouse Decision Table

Scale Up vs Scale Out

Warehouse Configuration Snippet

CREATE OR REPLACE WAREHOUSE wh_bi
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

Resource Monitor Concept

CREATE OR REPLACE RESOURCE MONITOR rm_bi
  WITH CREDIT_QUOTA = 1000
  FREQUENCY = MONTHLY
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

For the exam, focus on what resource monitors do: track credit usage and trigger actions. Do not rely on memorizing arbitrary quotas.

Caching, Pruning, and Performance

Performance Troubleshooting Checklist

1. Check whether the query was queued.
2. Check whether the warehouse was undersized or overloaded.
3. Inspect bytes/partitions scanned.
4. Look for poor filter predicates or functions blocking pruning.
5. Check join order, join keys, and data skew.
6. Look for local or remote disk spill.
7. Decide whether to resize, scale out, rewrite SQL, cluster, or precompute.

Storage, Tables, and Data Protection

Table and Object Choices

Time Travel, Fail-safe, and Cloning

Common Data Protection Traps

Time Travel Query Pattern

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

Clone Pattern

CREATE TABLE orders_restore
CLONE orders AT (OFFSET => -3600);

Data Loading, Unloading, and Stages

COPY INTO Direction

Stage Types

Batch Load Workflow

File Format and Load Example

CREATE OR REPLACE FILE FORMAT ff_csv
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL');

CREATE OR REPLACE STAGE stg_orders
  FILE_FORMAT = ff_csv;

COPY INTO raw.orders
FROM @stg_orders
ON_ERROR = CONTINUE;

External Stage Pattern

CREATE OR REPLACE STAGE stg_ext_orders
  URL = 's3://example-bucket/orders/'
  STORAGE_INTEGRATION = my_storage_integration
  FILE_FORMAT = ff_csv;

For exam purposes, know that external stages usually use a storage integration rather than embedding long-lived credentials.

Validation and Load Troubleshooting

COPY INTO raw.orders
FROM @stg_orders
VALIDATION_MODE = RETURN_ERRORS;

Unload Example

COPY INTO @exports/orders/
FROM (
  SELECT order_id, order_date, amount
  FROM mart.orders
)
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP)
HEADER = TRUE;

Snowpipe vs COPY INTO

SQL and Semi-Structured Data

Core SQL Concepts

Semi-Structured Types

JSON Query Pattern

SELECT
  event_data:customer.id::STRING AS customer_id,
  event_data:order.amount::NUMBER AS amount
FROM raw.events;

Flatten Pattern

SELECT
  e.event_id,
  item.value:sku::STRING AS sku,
  item.value:quantity::NUMBER AS quantity
FROM raw.events e,
LATERAL FLATTEN(input => e.event_data:items) item;

Timestamp Distinctions

Streams, Tasks, and ELT Automation

Streams and Tasks

Stream and Task Pattern

CREATE OR REPLACE STREAM orders_stream
ON TABLE raw.orders;

CREATE OR REPLACE TASK merge_orders_task
  WAREHOUSE = wh_etl
  SCHEDULE = 'USING CRON 0 * * * * UTC'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO mart.orders t
USING (
  SELECT *
  FROM orders_stream
  WHERE METADATA$ACTION = 'INSERT'
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, amount)
  VALUES (s.order_id, s.amount);

After creating a task, remember that tasks may need to be resumed before they run.

ALTER TASK merge_orders_task RESUME;

Dynamic Table Pattern

CREATE OR REPLACE DYNAMIC TABLE mart.daily_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = wh_etl
AS
SELECT
  order_date,
  SUM(amount) AS total_amount
FROM raw.orders
GROUP BY order_date;

Automation Decision Table

Views, Sharing, and Collaboration

View and Sharing Choices

Secure Data Sharing Concepts

Provider Pattern

CREATE OR REPLACE SECURE VIEW share_db.public.v_sales AS
SELECT region, order_date, amount
FROM mart.sales
WHERE region IS NOT NULL;

CREATE SHARE sh_sales;

GRANT USAGE ON DATABASE share_db TO SHARE sh_sales;
GRANT USAGE ON SCHEMA share_db.public TO SHARE sh_sales;
GRANT SELECT ON VIEW share_db.public.v_sales TO SHARE sh_sales;

Exact account-identification syntax for adding consumers can vary by account naming context, but the exam concept is stable: the provider grants selected objects to a share, and the consumer creates a database from that share.

Sharing Traps

Governance, Security, and Compliance Controls

Security Control Matrix

Masking Policy Pattern

CREATE OR REPLACE MASKING POLICY mask_email
AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_READ') THEN val
    ELSE '***MASKED***'
  END;

ALTER TABLE customer
  MODIFY COLUMN email
  SET MASKING POLICY mask_email;

Row Access Policy Pattern

CREATE OR REPLACE ROW ACCESS POLICY region_filter
AS (region STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'SALES_ADMIN'
  OR region = CURRENT_ROLE();

ALTER TABLE sales
  ADD ROW ACCESS POLICY region_filter ON (region);

Governance Traps

Monitoring and Metadata Reference

Common Troubleshooting Scenarios

High-Yield Distinctions

Exam-Day Decision Checklist

Before answering a scenario question, classify the requirement:

1. Layer: Is the issue storage, compute, cloud services, security, or data movement?
2. Goal: Is the priority cost, performance, recovery, governance, concurrency, or automation?
3. Access path: Does the role have warehouse, database, schema, and object privileges?
4. Compute ownership: Is compute user-managed, Snowflake-managed, provider-paid, or consumer-paid?
5. Data state: Is data loaded into Snowflake, staged, external, shared, cloned, or historical?
6. Pipeline pattern: Batch load, continuous ingestion, scheduled SQL, CDC, or declarative transformation?
7. Protection need: Restore data, mask columns, filter rows, audit access, or restrict login?
8. Performance signal: Queueing, scanned partitions, spills, poor pruning, or repeated computation?

Next Step

Use this Quick Reference to target weak areas, then move into COF-C02 scenario practice. For every missed question, map the explanation back to one of these decision points: warehouse sizing, RBAC grants, loading pattern, Time Travel/clone recovery, semi-structured SQL, sharing, or governance.