Domain 1: Snowflake AI Data Cloud Features and Architecture
- Snowflake uses a multi-cluster, shared-data architecture that combines a shared-disk-like centralized storage layer with a shared-nothing-like set of independent compute clusters.
- The three architecture layers are Database Storage (compressed, columnar micro-partitions managed automatically), Query Processing (virtual warehouses, which are independent MPP compute clusters), and Cloud Services (authentication, metadata, query optimization, and infrastructure management).
- Storage and compute scale independently, so a virtual warehouse can be resized or suspended without moving or duplicating the underlying data.
- A virtual warehouse is a cluster of compute resources, sized in T-shirt sizes from X-Small through 6X-Large, used to run queries, DML, and data loads; it holds no permanent data of its own.
- Multiple virtual warehouses can query the same stored data concurrently without contention, since compute is isolated per warehouse while the data itself stays centralized and shared.
- A multi-cluster warehouse (Enterprise Edition and above) adds extra clusters of the same size to absorb concurrent query load, instead of resizing the warehouse.
- Snowflake runs natively on AWS, Azure, or GCP, and the object hierarchy is Organization > Account > Database > Schema > tables, views, stages, and other schema-level objects.
- Snowsight is the primary web-based UI, SnowSQL is the command-line client, and Snowflake also provides JDBC/ODBC/Python drivers and Snowpark, a DataFrame API for Python, Java, and Scala.
- Snowflake editions (Standard, Enterprise, Business Critical, and Virtual Private Snowflake) unlock progressively more features, such as extended Time Travel, materialized views, and multi-cluster warehouses.
- Snowflake Marketplace lets an account discover and query data and app listings published by third-party providers without copying that data into the account.
- Separating storage and compute lets each be scaled to match actual workload demand, avoiding the over-provisioning that results from tying the two together.
Domain 2: Account Access and Security
- Snowflake combines role-based access control (RBAC) with discretionary access control (DAC): privileges are granted to roles, roles are granted to users, and object owners control access to what they own.
- Granting a role to another role builds a hierarchy in which the parent role inherits every privilege of the child role.
- ORGADMIN operates at the organization level, above individual accounts, managing account creation and organization-wide settings rather than objects inside any one account.
- ACCOUNTADMIN is the top-level system role, built from SYSADMIN and SECURITYADMIN, and should be granted to as few users as possible because of its account-wide reach.
- SECURITYADMIN manages grants and security administration, such as network and authentication policies, across the account, and inherits USERADMIN's user and role privileges.
- USERADMIN is the system role dedicated to creating and managing users and roles.
- SYSADMIN is the recommended parent role for custom, object-owning roles, such as warehouse, database, and schema administrators.
- PUBLIC is a fixed system role automatically available to every user in the account and cannot be dropped or renamed.
- Best practice is to build custom functional roles scoped to a specific job function and chain them up to SYSADMIN, rather than assigning broad administrative roles for routine work.
- Authentication options include username and password with MFA (Duo), federated SSO via SAML 2.0, key-pair authentication, OAuth, and SCIM for automated user provisioning.
- Data protection controls include dynamic data masking policies, row access policies, secure views and UDFs, object tagging, and network policies that allow or block IP ranges, with all stored data encrypted end-to-end using AES-256 and automatically rotated keys.
Domain 3: Performance Concepts
- Each warehouse T-shirt size step, from X-Small up through 6X-Large, roughly doubles both available compute capacity and credits billed per hour.
- Scale up to a larger warehouse size to speed up a single heavy or spilling query, since a bigger size adds more memory and local disk to each cluster.
- Scale out with a multi-cluster warehouse to absorb more concurrent queries and reduce queuing, rather than resizing the warehouse itself.
- Spilling happens when a query's intermediate results exceed the warehouse's available memory and local disk, slowing the query; increasing warehouse size is the direct remedy.
- Warehouses bill per second of runtime with a 60-second minimum charge each time they resume from suspension, and a suspended warehouse consumes zero credits.
- AUTO_SUSPEND sets how long a warehouse can sit idle before it automatically suspends, and AUTO_RESUME automatically restarts it when a new query arrives.
- Multi-cluster warehouses require Enterprise Edition or higher and are bounded by MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT.
- The Standard scaling policy starts additional clusters quickly to minimize query queuing, while the Economy policy conserves credits and tolerates some queuing before adding a cluster.
- Micro-partition pruning lets a query scan only the micro-partitions relevant to its filters, using metadata that Snowflake maintains automatically for every table.
- Clustering keys keep related rows co-located within the same micro-partitions on very large tables to improve pruning, and reclustering runs automatically as a serverless operation.
- The result cache, held in Cloud Services, returns a repeated and unchanged query's results instantly at no compute cost for 24 hours, while the warehouse's local disk cache is lost whenever that warehouse suspends.
Domain 4: Data Loading and Unloading
- Every user automatically has a user stage, referenced as @~, for files that only that user needs to load; it cannot be altered or dropped.
- Every table automatically has a table stage, referenced as @%table_name, accessible only to that table, and it is dropped automatically when the table is dropped.
- A named internal stage is an independent, shareable database object that can carry its own file format and grants, reusable across many load and unload statements.
- A named external stage references files that already sit in a cloud location using the s3://, azure://, or gcs:// URL prefix, so files never need to be copied into Snowflake first.
- A storage integration centralizes cloud provider authentication, such as an IAM role, as a reusable Snowflake object instead of embedding credentials in every stage definition.
- COPY INTO <table> loads staged files into an existing table, while COPY INTO <location> unloads query results out to a stage.
- The ON_ERROR copy option controls error handling: ABORT_STATEMENT (the default) fails the whole load on the first error, CONTINUE skips only the bad rows, and SKIP_FILE drops an entire file once it hits an error threshold.
- Load metadata is retained for about 64 days so Snowflake can detect and skip files already loaded, unless FORCE is specified to reload them anyway.
- MATCH_BY_COLUMN_NAME, often set to CASE_INSENSITIVE, maps staged fields to table columns by name; Parquet, ORC, and Avro embed column names that make this possible, unlike plain CSV.
- Snowpipe provides continuous, serverless data loading triggered automatically by cloud storage event notifications or its REST API, in contrast to bulk loading with COPY INTO on a user-managed warehouse.
- PUT uploads local files into an internal stage and GET downloads them back out, while PURGE removes staged files automatically after a successful COPY INTO load.
Domain 5: Data Transformations
- A common table expression, defined with WITH, provides a named result set scoped to a single statement, improving readability without creating a persisted object.
- WITH RECURSIVE lets a CTE reference itself to walk a hierarchy, such as an org chart or bill of materials, until no new rows are produced.
- An uncorrelated (scalar) subquery executes once and returns a single value, while a correlated subquery references an outer-query column and is logically re-evaluated once per outer row.
- INNER JOIN keeps only matching rows from both tables, LEFT OUTER JOIN preserves every row from the left table with NULLs where no match exists, FULL OUTER JOIN preserves unmatched rows from both sides, and CROSS JOIN produces the full Cartesian product.
- A self join aliases a single table twice and joins it to itself, a common pattern for relating rows such as employees to their managers.
- Set operators UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT) combine or compare the results of two compatible SELECT statements.
- CAST(expr AS type) and the shorthand :: operator both convert a value's data type, while TRY_CAST returns NULL instead of raising an error for a value that cannot be converted.
- Aggregate functions such as SUM, COUNT, MIN, MAX, and COUNT DISTINCT collapse rows per GROUP BY key, and HAVING filters those grouped results based on an aggregated value.
- Window functions use OVER() to compute a value across a set of related rows while still returning one output row per input row, and PARTITION BY restarts the calculation independently within each partition.
- Semi-structured data (JSON, Avro, XML) is stored in the VARIANT type, with OBJECT and ARRAY for nested structures, queried using colon path notation, dot notation, or bracket indexing; FLATTEN, often combined with LATERAL, explodes a VARIANT array or object into multiple rows.
- User-defined functions (SQL, JavaScript, Python, or Java), UDTFs, and stored procedures extend SQL with custom scalar, tabular, or procedural logic, while streams track row-level changes for change data capture and tasks schedule SQL or DAGs of SQL to run automatically, commonly paired together for incremental pipelines.
Domain 6: Data Protection and Data Sharing
- Time Travel lets you query, clone, or restore data as it existed at a past point in time, within a configurable retention window, using AT or BEFORE clauses.
- Standard Edition defaults to 1 day of Time Travel retention on permanent tables, while Enterprise Edition and above allow extending DATA_RETENTION_TIME_IN_DAYS up to 90 days.
- AT and BEFORE accept a TIMESTAMP, an OFFSET expressed as negative seconds before now, or a STATEMENT query ID to pinpoint the historical moment; AT is inclusive of that moment and BEFORE is exclusive.
- UNDROP restores a recently dropped table, schema, or database within its Time Travel retention period, provided no live object of the same name is blocking the restore.
- Fail-safe is a fixed, non-configurable 7-day period that follows Time Travel, during which only Snowflake, not the customer, can recover data for permanent tables as a last resort.
- Transient and temporary tables have no Fail-safe period at all, trading recoverability for lower storage cost, and temporary tables exist only for the session that created them.
- Zero-copy cloning creates a new table, schema, or database that initially shares the same underlying micro-partitions as its source, with storage diverging only as either copy changes.
- Secure Data Sharing lets a provider grant a consumer account live, read-only access to selected objects without copying or moving any data.
- Reader accounts let a provider share data with a consumer who has no Snowflake account of their own, while Direct Shares, Listings, and the Snowflake Marketplace offer different ways to publish and discover shared data.
- Replication copies databases across regions or cloud platforms, and failover and failback, available on Business Critical Edition and above, let an account switch its primary region during an outage.
- Longer Time Travel retention keeps more historical micro-partitions and increases storage cost, so retention should be set per object based on actual recovery needs.
SnowPro Core (COF-C02) exam tips
- Know scale up versus scale out cold: a bigger warehouse size fixes a single heavy or spilling query, while a multi-cluster warehouse (more clusters, same size) fixes concurrency and queuing - this pairing is tested often.
- Distinguish Time Travel (configurable 0-90 days, customer self-service via AT/BEFORE/UNDROP) from Fail-safe (fixed 7 days, Snowflake-only recovery, permanent tables only) - one of the most commonly confused pairs on the exam.
- Memorize the default role hierarchy: ACCOUNTADMIN sits above SYSADMIN and SECURITYADMIN, which sit above USERADMIN, with PUBLIC granted to everyone; custom functional roles should chain up to SYSADMIN, not directly to ACCOUNTADMIN.
- Know the three stage types (user @~, table @%, named @stage) and the three caches (result cache in Cloud Services, metadata cache, warehouse local disk cache), since scenario questions often ask which one applies.
- Understand that micro-partition pruning and reclustering are automatic: Snowflake prunes using metadata it maintains itself, so you rarely manually reorganize physical storage layout.
Study guide FAQ
What format and passing requirements does the SnowPro Core exam have?
SnowPro Core (COF-C02) is a multiple-choice and multiple-select exam with about 100 questions, delivered online proctored or at a test center over 115 minutes. It is scored on a scaled basis and the passing score is 750 out of 1000. There is no hands-on lab component.
Are there any prerequisites for SnowPro Core?
There are no mandatory prerequisites or required courses. Snowflake recommends hands-on experience with the platform, or equivalent training, before attempting it, since the exam favors applied scenario questions over pure memorization.
How does SnowPro Core differ from the SnowPro Advanced certifications?
SnowPro Core is the single foundational exam covering architecture, security, performance, loading, transformations, and data protection at a broad, associate level. SnowPro Advanced credentials, such as Architect, Data Engineer, and Administrator, are role-specific, require holding SnowPro Core first, and go far deeper into one specialty.
What should I focus on most while studying?
Architecture and Data Transformations together make up nearly half the exam, so be fluent in the storage, compute, and Cloud Services layers, virtual warehouse behavior, semi-structured data (VARIANT, FLATTEN), and core SQL such as joins, CTEs, and window functions. Also be precise about the RBAC role hierarchy and the Time Travel versus Fail-safe distinction, which are common sources of missed questions.
What should I expect from the multiple-select questions?
A subset of questions are select-all-that-apply style, asking you to choose two or three correct options instead of one, with no partial credit for a partly correct answer. These appear most often in the Architecture, Account Access and Security, and Data Transformations domains.