SnowPro Core (COF-C03) Practice Exam

Free sample questions

1. Sample · question 1 · Micro-partition immutability

   Snowflake's micro-partitions are foundational to its query performance and storage model. Which statement about micro-partitions is correct?

   • A.Their size is user-configurable between 16 MB and 256 MB at table creation time.
   • B.They are immutable once created — DML operations produce new micro-partitions and mark old ones for the Fail-Safe lifecycle.correct
   • C.A single micro-partition can span multiple tables to enable efficient cross-table scans.
   • D.They are held in the compute layer rather than the storage layer for performance.

   Why: Micro-partitions are immutable after creation; updates and deletes produce new micro-partitions and the originals enter Time Travel + Fail-Safe before cleanup. Size is automatically managed by Snowflake (each holds roughly 50–500 MB of uncompressed data, stored compressed), they belong to a single table, and they live in the cloud storage layer.

   Open this question on its own page →
2. Sample · question 2 · SECURITYADMIN for user and role management

   You're setting up role-based access in a new Snowflake account. Which built-in role is the recommended starting point for creating and managing users and other roles?

   • A.ACCOUNTADMIN
   • B.SECURITYADMINcorrect
   • C.SYSADMIN
   • D.PUBLIC

   Why: SECURITYADMIN is purpose-built for user and role management. ACCOUNTADMIN can do it but has broader powers and should be used sparingly. SYSADMIN owns the object hierarchy (warehouses, databases, schemas). PUBLIC is granted to every role and is for default grants only.

   Open this question on its own page →
3. Sample · question 3 · Multi-cluster warehouses for concurrency

   A team complains that their analytics dashboard is slow during business hours when many analysts hit it simultaneously, but it is fast off-hours. The warehouse is already an X-Large and a single query runs quickly when there is no other load. Which configuration change directly addresses concurrency contention?

   • A.Increase the warehouse size to 2X-Large.
   • B.Enable multi-cluster warehouse with auto-scale and a max cluster count greater than 1.correct
   • C.Add a clustering key on the dashboard's main fact table.
   • D.Increase the auto-suspend timer so the warehouse stays warm.

   Why: The symptom is concurrency contention — many simultaneous queries during business hours — which multi-cluster warehouses solve by spinning up additional clusters of the same size as demand grows. Scaling up improves individual query speed but doesn't increase concurrent capacity. Clustering keys improve pruning, not concurrency. Auto-suspend timing affects warmup latency, not capacity.

   Open this question on its own page →
4. Sample · question 4 · Snowflake edition for extended Time Travel

   An organization needs Time Travel retention greater than 1 day on production tables. What is the MINIMUM Snowflake edition required to enable this?

   • A.Standard
   • B.Enterprisecorrect
   • C.Business Critical
   • D.Virtual Private Snowflake (VPS)

   Why: Standard edition supports up to 1 day of Time Travel. Enterprise adds up to 90 days on permanent tables — that's the minimum edition where extended Time Travel is available. Business Critical adds additional encryption and HIPAA/PCI features; VPS provides a dedicated tenant. For extended Time Travel alone, Enterprise is enough.

   Open this question on its own page →
5. Sample · question 5 · Recovering a truncated table with Time Travel

   Your team accidentally ran TRUNCATE TABLE on a production table at 14:32. The table is in a standard (not transient) database and the account's DATA_RETENTION_TIME_IN_DAYS is 1. At 14:50 the same day, what is the fastest way to restore the data?

   • A.Restore from an off-platform backup using SnowSQL.
   • B.Issue `UNDROP TABLE` on the truncated table.
   • C.Use `CREATE TABLE … AS SELECT … FROM <table> AT(OFFSET => -1080)` (1,080 seconds back) and swap the new table in.correct
   • D.Open a Fail-Safe restore ticket with Snowflake Support.

   Why: Time Travel lets you query historical data within the retention window (1 day here). `AT(OFFSET => -seconds)` reads the table as of a point in time; CTAS into a replacement and swap. `UNDROP` is for dropped objects, not truncated ones. Fail-Safe is a Snowflake-administered 7-day recovery period accessed via support — slow, and only after Time Travel expires.

   Open this question on its own page →
6. Sample · question 6 · Types of internal stages

   Which of the following are valid types of internal stages in Snowflake? (Choose three.)

   • A.Named stagecorrect
   • B.User stagecorrect
   • C.Table stagecorrect
   • D.Schema stage
   • E.Database stage

   Why: Internal stages come in three flavors: named (explicit `CREATE STAGE`), user (one per user, referenced as `@~`), and table (one per table, referenced as `@%table_name`). Schema and database stages do not exist as object types.

   Open this question on its own page →
7. Sample · question 7 · File sizing for COPY INTO

   You're loading several gigabytes of CSV files into Snowflake via `COPY INTO` and want the highest load throughput. Which file-size strategy aligns with Snowflake's documented best practice?

   • A.One large file of any size — Snowflake parallelizes inside a single file.
   • B.Files of approximately 100–250 MB compressed, loaded concurrently.correct
   • C.Many tiny files under 100 KB each, for maximum parallelism.
   • D.Exactly one file per target micro-partition size of 16 MB.

   Why: Snowflake's documented sweet spot is files of 100–250 MB compressed because that balances parallelism (the warehouse splits work across files) against per-file overhead. A single huge file limits parallelism; tiny files multiply overhead; sizing to micro-partition size is unrelated to load-side file sizing.

   Open this question on its own page →
8. Sample · question 8 · Query result cache

   A user runs the exact same query twice in a row within one minute. The underlying tables have not changed and the warehouse is the same. The second run returns instantly and consumes no warehouse credits. Which Snowflake cache served the result?

   • A.The metadata cache in the cloud services layer.
   • B.The result cache in the cloud services layer.correct
   • C.The local disk cache on the running warehouse cluster.
   • D.The remote storage cache in cloud blob storage.

   Why: The result cache (cloud services layer) serves identical queries against unchanged tables for up to 24 hours without using warehouse credits. Local disk cache would still require a running warehouse and incur credit. The metadata cache stores object metadata, not result rows.

   Open this question on its own page →
9. Sample · question 9 · Reader accounts for non-Snowflake consumers

   You want to share a subset of your production data with an external partner who does not have a Snowflake account of their own. The data must not be physically copied; the partner should query it live. Which Snowflake feature enables this?

   • A.Use `COPY INTO` to export the subset to S3 and grant the partner pre-signed-URL access.
   • B.Create a Reader Account from your account and grant the partner credentials to it.correct
   • C.Snowflake does not support sharing with non-Snowflake customers; the partner must sign up first.
   • D.Create a database replica in the partner's region for them to query.

   Why: Reader Accounts let you share live data with non-Snowflake customers. Your account creates and pays for the reader account; the partner gets a login that can query the shared objects without copying data. Export to S3 violates the no-copy constraint. Replication is for cross-region availability, not external sharing.

   Open this question on its own page →
10. Sample · question 10 · LATERAL FLATTEN on VARIANT arrays

    You have a column `event` of type VARIANT containing JSON, including an inner array `event:items`. You need to produce one row per element of that array. Which SQL construct achieves this?

    • A.`SELECT event:items FROM events;`
    • B.`SELECT f.value FROM events, LATERAL FLATTEN(input => event:items) f;`correct
    • C.`SELECT JSON_EXTRACT(event, 'items[*]') FROM events;`
    • D.`SELECT event::ARRAY[i] FROM events GROUP BY i;`

    Why: `FLATTEN` is Snowflake's table function that turns array or object members into rows; it's typically used with a `LATERAL` join. Direct extraction (option A) returns the whole array as a value, not exploded rows. The other SQL forms shown do not exist in Snowflake.

    Open this question on its own page →
11. Sample · question 11 · Streams and tasks for change data capture

    You want to capture row-level changes (inserts, updates, deletes) on a source table and process them in batches every five minutes using native Snowflake features. Which combination achieves this?

    • A.A STREAM on the source table consumed by a TASK scheduled every 5 minutes.correct
    • B.Snowpipe configured with a 5-minute throttle.
    • C.A materialized view that refreshes on schedule.
    • D.S3 event notifications triggering an external Lambda that writes back via JDBC.

    Why: Streams capture row-level CDC on a base table; Tasks run on a schedule and can consume from a Stream to process change batches. Snowpipe ingests new files into Snowflake — it is not a CDC mechanism. Materialized views maintain query results, not change feeds.

    Open this question on its own page →
12. Sample · question 12 · Clustering keys for partition pruning

    A 5 TB table is loaded continuously and is currently unclustered. Queries that filter by `event_date` have become slow despite the WHERE clause being correct. Which is the most appropriate first step?

    • A.Define a clustering key on `event_date` and allow Snowflake's background reclustering service to maintain it.correct
    • B.Manually re-partition by running `CREATE TABLE … AS SELECT … ORDER BY event_date` once.
    • C.Drop and reload the table to force micro-partition reorganization.
    • D.Create a B-tree index on `event_date`.

    Why: Clustering keys signal which column(s) should drive micro-partition pruning, and Snowflake reclusters in the background as data lands. There are no traditional B-tree indexes. A one-time CTAS orders the existing data but does nothing to keep new data clustered as it streams in.

    Open this question on its own page →
13. Sample · question 13 · Network policies for IP allowlisting

    A security audit requires that Snowflake be reachable only from your corporate VPN's IP range. Which Snowflake feature enforces this restriction?

    • A.SECURITY INTEGRATION
    • B.NETWORK POLICYcorrect
    • C.ROW ACCESS POLICY
    • D.MASKING POLICY

    Why: NETWORK POLICY objects define allowed and blocked IP ranges and can be attached at the account or user level. SECURITY INTEGRATION wires up federated SSO, OAuth, or SCIM. ROW ACCESS POLICY and MASKING POLICY govern data visibility, not network access.

    Open this question on its own page →
14. Sample · question 14 · Zero-copy cloning for QA environments

    Your QA team needs a writable copy of a 500 GB production database for testing. They must not affect production data. Which approach is fastest and uses the least storage at creation time?

    • A.Use `COPY INTO` to export production to S3, then `COPY INTO` a new QA database.
    • B.Use `CREATE DATABASE qa CLONE prod;` (zero-copy clone).correct
    • C.Replicate the database to a separate QA account.
    • D.Open a Fail-Safe restore ticket asking Snowflake to recreate the database under a new name.

    Why: Zero-copy cloning creates a metadata pointer to existing micro-partitions. The clone completes in seconds and uses no additional storage until the QA team writes (which creates new partitions only for the diverged data). Export/import duplicates everything; replication is for cross-region disaster recovery; Fail-Safe is not a self-service feature.

    Open this question on its own page →
15. Sample · question 15 · Snowpipe auto-ingest for low latency

    A streaming source writes ~50 small files (about 5 MB each) per minute into an S3 bucket. You want to load them into Snowflake with the lowest end-to-end latency. Which approach is best?

    • A.A scheduled TASK that runs `COPY INTO` every 5 minutes.
    • B.Snowpipe with auto-ingest triggered by S3 event notifications.correct
    • C.A Stream over an external table built on the S3 prefix.
    • D.Manual `COPY INTO` commands issued by an analyst.

    Why: Snowpipe with auto-ingest is purpose-built for continuous file-based loading: S3 event notifications trigger ingestion within roughly a minute of file arrival. Scheduled `COPY INTO` has worse latency bounded by the schedule interval. Streams capture CDC on base tables, not external file arrivals.

    Open this question on its own page →