Use this syllabus as your source of truth for COF-C02. Work through each domain in order and drill targeted sets after every section.
What’s covered
Domain 1: Snowflake AI Data Cloud Features and Architecture (24%)
Practice this topic →
Task 1.1 - Outline key features of the Snowflake AI Data Cloud
- Describe the overall purpose and value proposition of the Snowflake AI Data Cloud for analytics and applications.
- Explain the concept of interoperable storage in Snowflake and how it enables multiple workloads on the same data.
- Describe how Snowflake separates storage and compute and why this matters for scaling and cost control.
- Identify the benefits of elastic compute using virtual warehouses for varying workloads in Snowflake.
- Explain Snowflake’s multi-cluster shared data architecture at a conceptual level.
- Identify the three main Snowflake layers: storage, compute, and cloud services.
- Describe the primary responsibilities of the storage layer in Snowflake.
- Describe the primary responsibilities of the compute layer in Snowflake.
- Describe the primary responsibilities of the cloud services layer in Snowflake.
- Outline the purpose of Snowflake editions and how they relate to available features and SLAs.
- Identify types of advanced capabilities that may only be available in higher Snowflake editions, such as advanced security or data protection features.
- Recognize why understanding your Snowflake edition is important when designing solutions and estimating costs.
- Describe core capabilities of Snowsight as the primary web interface for interacting with Snowflake.
- Identify common tasks that are well suited to the SnowSQL command-line client instead of the web interface.
- Distinguish Snowflake-provided connectors from drivers and explain at a high level when each is used.
- Explain the role of Snowflake drivers such as JDBC and ODBC for application connectivity.
- Describe Snowpark and when you might use it for data processing instead of pure SQL.
- Explain how SnowCD supports CI/CD automation and deployment of Snowflake objects.
- Describe Streamlit in Snowflake and when to use it to build interactive data applications.
- Outline how Cortex AI/ML services can be used within the Snowflake AI Data Cloud at a conceptual level.
- Describe when to use the Snowflake SQL API for programmatic query execution and metadata access.
- Differentiate interactive user interfaces like Snowsight from programmatic interfaces such as drivers, APIs, and SnowCD.
- Recognize basic connectivity considerations for client tools, such as account URL, role selection, and network access.
- Identify which Snowflake interface or tool is most appropriate given a simple usage scenario.
Task 1.3 - Outline Snowflake's catalog and objects
- Describe the high-level hierarchy of organization, accounts, databases, schemas, and objects in Snowflake.
- Differentiate permanent, transient, and temporary tables in terms of Time Travel and Fail-safe behavior.
- Identify when to choose transient tables to reduce storage costs while retaining some Time Travel.
- Identify when to choose temporary tables for session-scoped or intermediate results in Snowflake.
- Describe standard views and how they present logical subsets of data without storing another copy.
- Explain how materialized views differ from standard views in terms of storage and performance.
- Describe secure views at a conceptual level and how they help protect sensitive data.
- Differentiate internal and external stages and what each is typically used for in Snowflake.
- Describe named, table, and user stages and when to use each type for data loading and unloading.
- Explain how Snowflake data types, including VARIANT, support structured and semi-structured data.
- Differentiate scalar user-defined functions (UDFs), table functions (UDTFs), and external functions at a high level.
- Describe stored procedures and how they differ from UDFs in terms of capabilities and use cases.
- Explain the purpose of streams for change data capture in Snowflake.
- Describe tasks as a way to schedule and automate SQL-based workloads in Snowflake.
- Explain how shares provide controlled read-only access to data for other Snowflake accounts.
- Describe sequences and when they are used to generate unique numeric values in Snowflake.
Task 1.4 - Outline Snowflake storage concepts
- Describe Snowflake micro-partitions and how data is automatically organized on disk.
- Explain how clustering within micro-partitions supports pruning and efficient queries in Snowflake.
- Identify when defining a clustering key can improve performance for large tables in Snowflake.
- Recognize that Snowflake automatically maintains micro-partitions and handles data compression.
- Describe how query filters and sort orders influence micro-partition pruning effectiveness.
- Outline basic tools in Snowsight and ACCOUNT_USAGE for monitoring storage usage over time.
- Explain how Time Travel and Fail-safe settings impact overall storage consumption in Snowflake.
- Recognize high-level trade-offs between storage cost and performance when designing large Snowflake tables.
Domain 2: Account Access and Security (18%)
Practice this topic →
Task 2.1 - Outline security principles
- Describe Snowflake’s shared responsibility model for security at a high level.
- Explain the purpose of network policies for restricting client IP addresses that can access a Snowflake account.
- Identify when to use network policies in addition to role-based access control to reduce attack surface.
- Describe why multi-factor authentication (MFA) is recommended for console access to Snowflake.
- Explain how account administrators can enforce MFA requirements for users in Snowflake.
- Describe federated authentication and how it integrates Snowflake with an external identity provider.
- Differentiate single sign-on (SSO) from basic username/password authentication in Snowflake.
- Explain key pair authentication for programmatic Snowflake clients.
- Compare key pair authentication with password-based authentication in terms of security and manageability.
- Recognize why TLS-encrypted connections are required when connecting clients to Snowflake.
- Identify common security best practices that apply across Snowflake tools and interfaces.
Task 2.2 - Define the entities and roles that are used in Snowflake
- Describe a Snowflake organization and how it groups multiple accounts under a single administrative boundary.
- Differentiate organization-level responsibilities from account-level configuration tasks in Snowflake.
- Describe the purpose of a Snowflake account as a logical container for data, compute, users, and roles.
- Explain the relationship between users, roles, and objects within a Snowflake account.
- Describe the ACCOUNTADMIN system role and when its use should be restricted.
- Describe the SECURITYADMIN system role and its typical responsibilities.
- Describe the SYSADMIN system role and its responsibilities for creating and managing warehouses and database objects.
- Describe the USERADMIN system role and its responsibilities for creating and managing users and roles.
- Explain how custom roles can be created and arranged into a role hierarchy in Snowflake.
- Describe how roles are granted to users and to other roles to build effective access models.
- Explain the difference between object ownership and granted privileges in Snowflake.
- Recognize why the PUBLIC role should be granted only minimal privileges in a secure Snowflake deployment.
Task 2.3 - Outline data governance capabilities in Snowflake
- Describe how secure views can be used to hide sensitive columns or rows from certain roles.
- Explain secure UDFs and secure UDTFs at a conceptual level and their role in protecting logic and data.
- Differentiate INFORMATION_SCHEMA and ACCOUNT_USAGE views for data governance reporting in Snowflake.
- Describe how ACCESS_HISTORY helps track read and write operations on data objects.
- Identify the types of details available in ACCESS_HISTORY, such as which user accessed which objects and when.
- Explain Snowflake’s role-based access control (RBAC) model at a high level.
- Describe common privilege types such as USAGE, SELECT, INSERT, and OWNERSHIP in Snowflake.
- Explain how privileges differ by object type, such as databases, schemas, tables, warehouses, and shares.
- Describe the principle of least privilege and how it applies to Snowflake role design.
- Explain how GRANT and REVOKE statements are used to manage privileges in Snowflake.
- Describe role hierarchy and how privilege inheritance works between parent and child roles.
- Explain how row access policies and masking policies support row- and column-level security in Snowflake.
- Describe how object tags can be used for classification, governance, and cost allocation in Snowflake.
Domain 3: Performance and Cost Optimization Concepts (16%)
Practice this topic →
Task 3.1 - Explain the use of the Query Profile
- Identify the main sections of the Snowflake Query Profile and what each section represents.
- Recognize indicators of data spilling to local or remote storage in the Query Profile.
- Explain why data spilling can negatively impact query performance in Snowflake.
- Identify where micro-partition pruning effectiveness is shown in the Query Profile.
- Describe how to use Query History to locate long-running or expensive queries for further analysis.
- Explain how to filter Query History by user, warehouse, time range, or query characteristics.
- Differentiate the purposes of Query Profile versus Query History when analyzing Snowflake performance.
Task 3.2 - Explain virtual warehouse configurations and caching
- Describe the purpose of the result cache and when a query can reuse a cached result in Snowflake.
- Explain common reasons why the result cache is not used for a particular query.
- Describe the metadata cache and how it speeds up query planning and execution in Snowflake.
- Explain the warehouse cache (data cache) and how it benefits repeated scans of the same data.
- Recognize how suspending, resuming, or resizing a warehouse affects the different cache types.
- Identify best practices for leveraging caches in Snowflake without relying on them for correctness.
- Differentiate between standard warehouses and multi-cluster warehouses in Snowflake.
- Explain the differences between scaling up (changing warehouse size) and scaling out (adding clusters).
- Describe how to monitor warehouse load and concurrency using Snowsight views and dashboards.
- Recognize when multi-cluster warehouses are appropriate for spiky or highly concurrent workloads.
- Explain the purpose of the Query Acceleration Service and when it may be beneficial.
- Identify how warehouse settings such as auto-suspend, auto-resume, and min/max clusters affect performance and cost.
- Describe materialized views and how they can improve query performance in Snowflake.
- Explain when Snowflake can automatically rewrite queries to use materialized views.
- Describe clustering and how choosing an appropriate clustering key can improve pruning for large tables.
- Explain when to consider the search optimization service for highly selective lookup queries.
- Identify SELECT query patterns that generally improve performance, such as avoiding SELECT * and filtering early.
- Describe how persisted query results can reduce both compute cost and latency for repetitive workloads in Snowflake.
Task 3.5 - Describe cost optimization concepts and best practices in Snowflake
- Identify the main components of Snowflake cost: storage, virtual warehouse compute, and cloud services.
- Describe how to use the Cost Insights feature in Snowsight to understand Snowflake spending patterns.
- Explain how table types, retention periods, and Time Travel settings influence storage costs in Snowflake.
- Recognize cost considerations when using serverless features such as Snowpipe, search optimization, tasks, and Query Acceleration Service.
- Describe how data replication and failover across regions or clouds affect both storage and compute costs.
- Explain how resource monitors and the Snowflake Budgets service can be used to monitor and control credit usage.
- Describe how object tags and ACCOUNT_USAGE views support cost attribution and chargeback in Snowflake.
Domain 4: Data Loading and Unloading (12%)
Practice this topic →
Task 4.1 - Define concepts and best practices when loading data
- Differentiate internal and external stages used as sources for loading data into Snowflake.
- Describe common file formats supported for loading into Snowflake, such as CSV and JSON, at a high level.
- Explain recommended file size ranges for efficient bulk loading and why many small files can hurt performance.
- Recognize how organizing source files into logical folder structures can simplify Snowflake COPY INTO commands.
- Compare ad hoc or bulk loading with COPY INTO to continuous loading with Snowpipe in Snowflake.
- Identify scenarios where Snowpipe is preferred over batch loading for near real-time ingestion.
Task 4.2 - Outline commands used to load data and when to use them
- Describe the purpose of CREATE STAGE when preparing to load data into Snowflake.
- Explain why defining reusable FILE FORMAT objects with CREATE FILE FORMAT is recommended for consistent loading.
- Describe when to define a PIPE object to support Snowpipe-based continuous loading.
- Explain how CREATE EXTERNAL TABLE can be used to query data stored in external locations without fully loading it.
- Compare using COPY INTO versus INSERT or INSERT OVERWRITE for loading data into Snowflake tables.
- Describe when PUT and VALIDATE commands are used to load and verify files from local environments.
Task 4.3 - Define concepts and best practices when unloading data
- Explain how COPY INTO can be used to unload data from Snowflake tables to stages or external storage.
- Describe how to choose file formats and compression methods when unloading large result sets from Snowflake.
- Recognize how Snowflake treats NULL values and empty strings during unload operations.
- Explain how to configure COPY INTO to unload data into a single file versus multiple files.
- Describe best practices for unloading wide or large relational tables, including column selection and partitioning.
- Identify security considerations when unloading sensitive data from Snowflake to external storage locations.
Task 4.4 - Outline commands used to unload data and when to use them
- Describe how the LIST command is used to see files available in a Snowflake stage.
- Explain how the GET command retrieves files from internal stages to client or external storage locations.
- Differentiate COPY INTO <table> from COPY INTO <location> in Snowflake load and unload scenarios.
- Describe how stage configuration, such as URL, credentials, and encryption settings, affects unload behavior.
- Explain why using shared FILE FORMAT objects can keep load and unload behavior consistent across teams.
- Describe how to monitor and troubleshoot load and unload operations using ACCOUNT_USAGE and INFORMATION_SCHEMA views such as COPY_HISTORY.
Domain 5: Data Transformations (18%)
Practice this topic →
Task 5.1 - Explain how to work with structured data
- Describe how standard SQL DDL and DML statements are used for structured data transformations in Snowflake.
- Explain when to use CREATE TABLE AS SELECT (CTAS) to materialize query results in Snowflake.
- Describe when to use MERGE to implement upserts in transformation pipelines.
- Identify when estimation functions, such as approximate aggregates, are appropriate for large data sets.
- Explain the trade-offs between approximate and exact aggregate functions in Snowflake.
- Describe the SAMPLE and TABLESAMPLE clauses for exploring and testing with subsets of data.
- Differentiate fraction-based sampling from fixed-size sampling when using SAMPLE in Snowflake.
- Describe the role of system functions in supporting transformations and metadata queries in Snowflake.
- Explain the difference between scalar functions and table functions in Snowflake transformation logic.
- Describe how external functions can extend transformations with external services at a high level.
- Explain how stored procedures can orchestrate multiple SQL steps in a Snowflake transformation workflow.
- Describe how streams and tasks work together to support incremental ELT patterns in Snowflake.
Task 5.2 - Explain how to work with semi-structured data
- Describe how VARIANT columns store semi-structured data such as JSON in Snowflake.
- Identify common semi-structured file formats that Snowflake can ingest, such as JSON, AVRO, and PARQUET.
- Explain how to access nested attributes in VARIANT columns using path expressions in Snowflake SQL.
- Describe the purpose of the FLATTEN function when working with arrays or objects in semi-structured data.
- Explain how LATERAL FLATTEN is used to join flattened data back to its parent rows in Snowflake.
- Describe how ARRAY-construction functions can be used to create arrays within Snowflake queries.
- Describe how OBJECT-construction functions can be used to build key-value structures in Snowflake.
- Explain how type predicate functions help safely navigate semi-structured data in Snowflake.
- Describe approaches to designing relational tables or views on top of semi-structured data in Snowflake.
- Recognize performance considerations when querying very large VARIANT columns, such as selective access and pruning.
- Explain when to consider external tables versus loading semi-structured data into VARIANT columns.
- Describe strategies for handling schema evolution when semi-structured source data adds new fields over time.
Task 5.3 - Explain how to work with unstructured data
- Define unstructured data in the context of Snowflake, such as documents, images, or binary files.
- Describe how internal and external stages are used to store unstructured files for access from Snowflake.
- Explain what directory tables are and how they expose metadata about files in a stage.
- Describe how SQL file functions can be used to read file metadata and contents inside Snowflake.
- Differentiate presigned URLs, internal stage URLs, and cloud storage URLs used to access data files.
- Identify security considerations when sharing or generating URLs that grant access to unstructured files.
- Explain how user-defined functions (UDFs) can process unstructured data contents, such as text or images, at a conceptual level.
- Describe how stored procedures can orchestrate complex unstructured data processing workflows in Snowflake.
- Explain how to combine directory table metadata with structured tables to support analytics use cases.
- Recognize performance considerations when frequently reading large unstructured files from Snowflake stages.
- Describe how unstructured data workloads can integrate with AI/ML services in the Snowflake AI Data Cloud at a high level.
- Explain limitations and appropriate expectations when querying unstructured data compared to structured or semi-structured data in Snowflake.
Domain 6: Data Protection and Data Sharing (12%)
Practice this topic →
Task 6.1 - Outline Continuous Data Protection with Snowflake
- Describe Time Travel and how it allows querying or restoring historical versions of data in Snowflake.
- Explain how Time Travel retention can be configured at the database, schema, or table level in Snowflake.
- Differentiate Time Travel from Fail-safe in terms of purpose and who can use each feature.
- Describe Fail-safe as a last-resort recovery mechanism managed by Snowflake.
- Explain how Snowflake encrypts data at rest and in transit by default at a high level.
- Recognize the role of key management and rotation in Snowflake without going into provider-specific details.
- Describe zero-copy cloning and how it enables fast creation of test or backup environments in Snowflake.
- Explain how cloning interacts with Time Travel and storage usage in Snowflake.
- Describe database and account replication and how it supports business continuity across regions or clouds.
- Explain the high-level steps involved in setting up a failover group for critical objects in Snowflake.
- Recognize how replication and failover settings influence recovery point objective (RPO) and recovery time objective (RTO).
- Identify cost and governance considerations when using replication, cloning, and data protection features in Snowflake.
Task 6.2 - Outline Snowflake data sharing capabilities
- Describe provider, consumer, and reader accounts and their roles in Snowflake data sharing.
- Explain how secure data sharing allows consumers to query shared data without copying it in Snowflake.
- Describe Snowflake Marketplace and the types of listings that can appear there.
- Differentiate Snowflake Marketplace from private Data Exchanges.
- Explain the high-level DDL workflow to create, grant, and manage shares in Snowflake.
- Identify the privileges typically required for a role to create and manage shares in Snowflake.
- Describe how a consumer account uses a shared database in queries and analytics.
- Explain governance controls that should be in place when sharing sensitive or regulated data via Snowflake.
- Describe how data sharing works across regions or cloud platforms at a conceptual level in Snowflake.
- Recognize what types of objects, such as tables and secure views, can be shared versus those that cannot, such as warehouses.
- Explain how to monitor data sharing usage and access using Snowflake views and logs.
- Describe best practices for designing shareable data products that are stable, well-documented, and secure in Snowflake.
Tip: After finishing a domain, take a 20–25 question drill focused on that domain, then revisit any weak objectives before moving on.