COF-C02 — Snowflake SnowPro Core Certification Cheat Sheet

Focus on recognizing what the scenario is really asking:

The goal is not to replace Snowflake documentation or hands-on practice. The goal is to help you quickly reconnect the most testable concepts: Snowflake architecture, virtual warehouses, storage, data loading, querying, security, governance, sharing, performance, and cost control.

This page supports IT Mastery exam-prep resources and are not affiliated with Snowflake.

Object Hierarchy

Snowflake architecture: the mental model

Snowflake is built around separation of storage, compute, and cloud services. Most COF-C02 reasoning questions become easier if you first identify which layer performs the work.

High-yield architecture rules

• Storage and compute are independent. You can resize or suspend a warehouse without changing stored tables.
• Virtual warehouses consume credits while running. Auto-suspend and auto-resume are key cost controls.
• Warehouse size is not the same as concurrency.
  • Larger warehouse: more compute for each cluster.
  • Multi-cluster warehouse: more clusters to serve concurrent workloads.
• Snowflake automatically manages micro-partitions. Candidates often over-assume manual maintenance tasks that are not required in Snowflake.
• Metadata is central. Cloning, pruning, Time Travel, and query optimization all rely heavily on metadata.

Account objects and object hierarchy

Understand the hierarchy because privilege questions depend on it.

Names, context, and sessions

Candidates often miss session context. Review:

• Current role.
• Current warehouse.
• Current database.
• Current schema.
• Fully qualified object names.
• Temporary objects shadowing permanent objects.
• Role hierarchy and inherited privileges.

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.

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.

Virtual warehouses and compute

Virtual warehouses are the main compute resource candidates must understand. Questions often ask whether a workload needs faster individual query execution, better concurrency handling, or lower cost.

Scale up versus scale out

Auto-suspend and auto-resume traps

• Auto-suspend too high: warehouses keep running after work finishes.
• Auto-suspend too low: frequent suspend/resume may hurt interactive workloads.
• Auto-resume disabled: users or jobs may fail when the warehouse is suspended.
• Assuming all cost stops when a warehouse suspends: serverless features, storage, and other services may still incur cost.

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.

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);

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

Data loading and unloading

COF-C02 candidates should know the normal ingestion path: stage files, define file formats, load with COPY INTO, then validate and handle errors.

Loading method review

Common loading traps

• A stage is not the same thing as a table.
• A file format tells Snowflake how to parse data; it does not store the data itself.
• COPY INTO requires correct stage references, file format settings, and target table structure.
• Snowpipe is for ingestion automation, not a full transformation orchestration tool by itself.
• Loading semi-structured data often uses VARIANT, but you still need to understand querying and flattening.
• Duplicate file handling and load history matter when troubleshooting repeated loads.
• External tables query external data; they are not equivalent to fully loaded native Snowflake tables.

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

SQL, semi-structured data, and transformations

Snowflake supports standard SQL plus native handling for semi-structured data. Expect conceptual questions about VARIANT, path access, flattening, and transformation patterns.

Semi-structured data decision points

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

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

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

Security and access control

Snowflake access control is a frequent exam area because many questions combine roles, privileges, object hierarchy, and warehouses.

Core access model

Privilege checklist for querying a table

To query data, a user generally needs the correct active role context and required privileges, such as:

• A role assigned to the user.
• USAGE on the warehouse.
• USAGE on the database.
• USAGE on the schema.
• SELECT on the table or view.
• Correct role activation during the session.

Common trap: SELECT on a table alone is not enough if the role lacks usage on the parent database/schema or warehouse.

System-defined role concepts

Do not overuse ACCOUNTADMIN in exam scenarios. Least privilege and role separation are common correct-answer themes.

Access control traps

• Forgetting the active role matters.
• Granting object privilege but not parent USAGE.
• Granting table access but not warehouse access.
• Confusing role grants to users with privilege grants to roles.
• Assuming privileges automatically flow down the object hierarchy.
• Forgetting future grants can simplify access for newly created objects.
• Missing managed access schema behavior, where grant control is centralized.

Table type comparison

Time Travel, Fail-safe, and cloning

Zero-copy clone decision rules

Use zero-copy cloning when you need:

• Fast dev/test environments.
• A safe copy before schema or data changes.
• Point-in-time analysis.
• Efficient experimentation without a full physical copy.

Be careful when:

• Long-lived clones diverge heavily from the source.
• Clones are used as a substitute for lifecycle governance.
• You assume privileges, ownership, and object dependencies always behave exactly like the source context.

Data sharing concepts

Sharing traps

• Sharing is not the same as exporting files.
• The provider does not copy physical table data into the consumer account for standard secure sharing.
• Consumers still need appropriate roles and warehouses to query shared data.
• Secure views are commonly used to expose only intended rows/columns.
• Not every governance requirement is solved by sharing; masking, row access, and role design may also be needed.

Caching concepts

Clustering and pruning

Strong pruning usually depends on filters that align with how data is organized in micro-partitions. Clustering can help when:

• Tables are large.
• Queries repeatedly filter on specific columns.
• Natural load order does not support pruning.
• Query profile shows excessive scanning.

Clustering may be a poor choice when:

• Tables are small.
• Query patterns are inconsistent.
• Maintenance cost outweighs benefit.
• Filters do not align with the proposed clustering key.

Security mistakes

• Thinking a user receives privileges directly rather than through roles.
• Forgetting USAGE on parent database and schema.
• Forgetting USAGE on a warehouse.
• Using ACCOUNTADMIN as the default answer.
• Confusing masking policies with row access policies.
• Assuming object hierarchy automatically grants access to child objects.

Data loading mistakes

• Confusing stage, file format, and target table.
• Using Snowpipe for every ingestion scenario, even simple batch loads.
• Forgetting external tables query data in external storage.
• Ignoring load validation and error handling.
• Treating semi-structured data as automatically relational without FLATTEN or path access.

Performance and cost mistakes

• Scaling up when the actual issue is concurrency.
• Adding multi-cluster capacity when the issue is one inefficient query.
• Creating clustering keys on small or rarely queried tables.
• Forgetting that auto-clustering and other managed services can consume credits.
• Leaving warehouses running after scheduled jobs complete.
• Assuming result cache behavior will always save a poorly designed workload.