Snowflake SnowPro Core (COF-C03)

Question 1

A data governance officer must produce a report for a compliance audit showing exactly which columns were read by user queries over the past 90 days, including which base table columns were accessed through views. The officer wants an authoritative source that captures column-level read and write lineage. Which Snowflake feature should be used?

Accepted Answer

The ACCESS_HISTORY view in the ACCOUNT_USAGE schema

Answer

The QUERY_HISTORY view in the INFORMATION_SCHEMA

Answer

The OBJECT_DEPENDENCIES view in the ACCOUNT_USAGE schema

Answer

A masking policy applied to each sensitive column

Question 2

A data governance analyst needs to audit which users queried a specific table over the past 18 months. They plan to use the ACCOUNT_USAGE schema in the SNOWFLAKE database. When they query the QUERY_HISTORY view, they discover that records older than one year are missing. What is the reason for this behavior?

Accepted Answer

The ACCOUNT_USAGE.QUERY_HISTORY view retains data for up to 365 days (1 year); older data is not available through this view.

Answer

The INFORMATION_SCHEMA must be queried instead, because it retains historical query data indefinitely.

Answer

Time Travel retention on the account is set to the default of 1 day, which truncates ACCOUNT_USAGE views.

Answer

The analyst lacks the MONITOR privilege, so only the most recent 365 days of history are returned.

Question 3

A governance analyst must build a report that lists every query executed across the account over the past 180 days, including queries run by users whose accounts have since been dropped. The analyst wants the most complete historical coverage and is willing to accept some data latency. Which data source should the analyst query?

Accepted Answer

The QUERY_HISTORY view in the SNOWFLAKE.ACCOUNT_USAGE schema

Answer

The QUERY_HISTORY table function in the INFORMATION_SCHEMA of the current database

Answer

The QUERY_HISTORY view in each database's INFORMATION_SCHEMA schema

Answer

The ACCESS_HISTORY view in the SNOWFLAKE.ACCOUNT_USAGE schema

Question 4

A data governance analyst needs to build a monthly report showing the total average bytes of storage consumed by each individual table across the entire account, including bytes attributable to Time Travel and Fail-safe. They want a single query that requires ACCOUNTADMIN-level visibility across all databases. Which approach should they use?

Accepted Answer

Query the TABLE_STORAGE_METRICS view in the SNOWFLAKE.ACCOUNT_USAGE schema, which reports active, Time Travel, and Fail-safe bytes per table for the whole account

Answer

Query the TABLES view in each database's INFORMATION_SCHEMA and sum the BYTES column across all schemas

Answer

Query the STORAGE_USAGE view in SNOWFLAKE.ACCOUNT_USAGE, which returns per-table storage broken down by Time Travel and Fail-safe

Answer

Query the DATABASE_STORAGE_USAGE_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE to obtain per-table storage details

Question 5

A Snowflake administrator is organizing objects in a new account. They need to create a network policy that restricts login access, a database to hold analytics tables, and a resource monitor to cap warehouse credit usage. When deciding where each object lives in the Snowflake object hierarchy, which statement correctly classifies these objects?

Accepted Answer

Network policies and resource monitors are account-level objects, while tables reside within a schema inside a database.

Answer

Network policies, resource monitors, and databases are all schema-level objects that must be created inside a database.

Answer

Resource monitors are database objects, while network policies and tables are account-level objects.

Answer

All three—network policy, database, and resource monitor—are organization-level objects managed above the account.

Question 6

A new data engineer joins your team and needs to create and manage warehouses, databases, and roles across the account, but company policy strictly forbids granting anyone the ability to view billing information or manage account-level parameters unless they are a designated administrator. Which approach best follows Snowflake's principle of least privilege while meeting these requirements?

Accepted Answer

Grant SYSADMIN for object creation and management, and separately grant SECURITYADMIN if role management is required, without granting ACCOUNTADMIN.

Answer

Grant the ACCOUNTADMIN role to the engineer so they have full control to create the needed objects.

Answer

Grant ORGADMIN so the engineer can manage the account and its objects without seeing billing.

Answer

Grant the PUBLIC role with additional privileges added directly to the user rather than to a role.

Question 7

A finance operations team wants to track and receive email notifications when the combined monthly credit consumption of a specific set of warehouses, serverless features, and materialized view maintenance for a project exceeds a defined spending limit. They need this to span multiple object types and send alerts without automatically suspending anything. Which Snowflake feature best meets this requirement?

Accepted Answer

A Snowflake Budget with a custom group of resources and a spending limit that triggers notifications

Answer

A resource monitor assigned at the account level with a notify-only action

Answer

A row access policy applied to the ACCOUNT_USAGE.METERING_HISTORY view

Answer

Multiple resource monitors, one per warehouse, each with a suspend action

Question 8

A healthcare analytics company must comply with HIPAA regulations. They require that all data be encrypted using a customer-managed key combined with a Snowflake-managed key, and they need support for private connectivity to isolate traffic from the public internet. Which is the minimum Snowflake edition that satisfies these requirements?

Accepted Answer

Business Critical

Answer

Standard

Answer

Enterprise

Answer

Virtual Private Snowflake (VPS)

Question 9

A data engineer runs CREATE DATABASE analytics_dev CLONE analytics_prod; to give the development team a copy of production for testing. Shortly after, the team drops several tables in analytics_dev and adds new columns to others. A manager worries this experimentation could harm the production database. Which statement correctly describes the relationship between the cloned database and its source?

Accepted Answer

The clone is an independent object; changes to schemas and tables in analytics_dev do not affect analytics_prod, and cloned data initially shares underlying micro-partitions with the source at no extra storage cost.

Answer

The clone is a read-only reference to analytics_prod, so any attempt to drop tables in analytics_dev will fail with a permission error.

Answer

The clone physically duplicates all micro-partitions immediately, so it consumes the same storage as analytics_prod, but changes remain isolated from the source.

Answer

The clone stays synchronized with analytics_prod, so dropping tables in analytics_dev will also drop them in production unless the link is broken.

Question 10

A Snowflake administrator is reviewing which components handle various platform responsibilities. They need to identify which architectural layer is responsible for user authentication, access control (RBAC), query parsing and optimization, and transaction management. Which layer of the Snowflake architecture handles these responsibilities?

Accepted Answer

The Cloud Services layer, which coordinates activities such as authentication, access control, query optimization, and metadata management

Answer

The Database Storage layer, because it manages all metadata alongside the compressed table data

Answer

The Compute (virtual warehouse) layer, since each warehouse authenticates users and optimizes its own queries

Answer

A dedicated fourth layer that must be provisioned separately for security-focused features

Question 11

A data engineer runs an identical aggregation query twice within a few minutes. The underlying tables have not changed, and the second execution returns results almost instantly without consuming any virtual warehouse compute credits. Which layer of the Snowflake architecture is responsible for enabling this behavior?

Accepted Answer

The Cloud Services layer, which manages the query result cache and metadata

Answer

The Compute layer, which caches results locally on the virtual warehouse SSD

Answer

The Database Storage layer, which stores query outputs in micro-partitions

Answer

The virtual warehouse's multi-cluster controller, which reuses idle clusters

Question 12

A data engineer manages a 12 TB events table that is frequently queried with filters on an event_date column. Query performance has degraded over time because new data arrives continuously and rows for the same date end up scattered across many micro-partitions. The engineer wants Snowflake to automatically reorganize the table's data so that partitions are better co-located by event_date, improving pruning for date-range queries. Which action should the engineer take?

Accepted Answer

Define a clustering key on event_date so Automatic Clustering maintains co-location as new data loads

Answer

Increase the virtual warehouse size so more micro-partitions can be scanned in parallel

Answer

Enable multi-cluster scaling on the warehouse to distribute the date-range queries across clusters

Answer

Recreate the table with a smaller micro-partition size to reduce the number of partitions scanned

Question 13

A data engineer loads several years of transaction data into a large Snowflake table using daily batch loads ordered by transaction_date. Analysts almost always filter queries by transaction_date ranges, and query pruning is already very effective. The engineer is deciding whether to define an explicit clustering key on transaction_date. What is the most appropriate recommendation?

Accepted Answer

Do not add an explicit clustering key, because the natural load order already aligns micro-partitions with transaction_date, providing effective pruning without incurring reclustering credit costs.

Answer

Add a clustering key on transaction_date immediately, because explicit clustering is always required for any table larger than a few gigabytes.

Answer

Add a clustering key on a high-cardinality column such as transaction_id to maximize the number of micro-partitions scanned.

Answer

Convert the table to a materialized view instead, since clustering keys cannot be applied to tables that receive daily batch loads.

Question 14

A data engineering team runs heavy nightly ETL jobs that must never slow down the interactive dashboards used by business analysts during the day. Both workloads query the same production tables. A junior engineer suggests simply increasing the size of the single shared warehouse, but the lead engineer wants to guarantee that the two workloads never compete for compute resources while still reading the exact same underlying data. Which characteristic of the Snowflake architecture enables the recommended approach?

Accepted Answer

The Compute layer allows multiple independent virtual warehouses to operate on the same data in the Storage layer simultaneously without resource contention.

Answer

The Cloud Services layer automatically routes competing queries to separate physical servers to prevent contention within one warehouse.

Answer

Each database in the Storage layer maintains its own dedicated compute cluster that isolates workloads by schema.

Answer

Increasing a single warehouse to a larger size partitions its compute so that ETL and dashboard queries run on separate isolated clusters.

Question 15

A data provider shares a database containing several large tables with a consumer account using a direct share. The consumer's data engineering team runs frequent analytical queries against the shared tables. During a cost review, the provider's finance team notices no compute charges related to the consumer's query activity on their account, and the consumer asks who pays for the compute used to query the shared data. Which statement correctly describes how compute is billed for querying shared data?

Accepted Answer

The consumer pays for all compute using their own virtual warehouses when querying the shared data.

Answer

The provider pays for the compute because they own the underlying data being queried.

Answer

Compute charges are split equally between the provider and consumer accounts.

Answer

Querying shared data is serverless, so neither the provider nor consumer is charged for compute.

Question 16

A data engineer runs a bulk COPY INTO command to load 500 CSV files into a table. The load completed, but the engineer suspects some rows were rejected due to data type mismatches. They want to retrieve the specific rows that failed during that most recent load so they can investigate the raw values. Which approach should they use?

Accepted Answer

Query the VALIDATE table function referencing the target table and the COPY job's query ID to return the rejected rows and error details

Answer

Re-run the COPY INTO command with VALIDATION_MODE = 'RETURN_ERRORS' to reload and capture the failed rows

Answer

Query the COPY_HISTORY table function, which returns each individual rejected row with its column values

Answer

Set ON_ERROR = SKIP_FILE and re-run the COPY INTO to output the error rows to a separate error stage

Question 17

A data engineer runs COPY INTO on a table from an internal named stage that already contains 50 files. The load completes successfully. Later that day, the same 50 files plus 10 new files are present in the stage, and the engineer reruns the identical COPY INTO command without any options that alter default behavior. What happens?

Accepted Answer

Only the 10 new files are loaded because COPY INTO uses load metadata to skip files already loaded within the last 64 days.

Answer

All 60 files are reloaded because COPY INTO always processes every file present in the stage.

Answer

The command fails with a duplicate-file error until the previously loaded files are purged from the stage.

Answer

None of the files are loaded because the target table already contains data from the first COPY operation.

Question 18

A data engineer runs COPY INTO to unload a very large query result to an external S3 stage. They omit the SINGLE option (leaving the default) and do not set MAX_FILE_SIZE. After the unload, they observe many output files rather than one. A downstream partner requires that no individual output file exceed 100 MB. Which action correctly enforces this requirement?

Accepted Answer

Set MAX_FILE_SIZE to 104857600 in the COPY INTO statement so Snowflake splits output into files no larger than that size

Answer

Set SINGLE = TRUE so Snowflake produces one file that is automatically capped at 100 MB

Answer

Add PARTITION BY with a column whose cardinality guarantees each partition is under 100 MB

Answer

Reduce the warehouse size so fewer threads write smaller files under 100 MB

Question 19

A data engineer used the PUT command to upload three CSV files to their user stage. They now want to load only one specific file, sales_west.csv, into the WEST_SALES table using a predefined file format named CSV_FMT. Which COPY INTO statement correctly loads just that single file from the user stage?

Accepted Answer

COPY INTO WEST_SALES FROM @~/sales_west.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FMT');

Answer

COPY INTO WEST_SALES FROM @%WEST_SALES/sales_west.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FMT');

Answer

COPY INTO WEST_SALES FROM @sales_west.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FMT');

Answer

COPY INTO WEST_SALES FROM '@~/sales_west.csv' PATTERN = 'CSV_FMT';

Question 20

A data engineer is loading Parquet files into a Snowflake table using COPY INTO. The Parquet files contain named columns, but the physical column order in the files does not match the column order defined in the target table. The engineer wants Snowflake to map source fields to table columns based on their names rather than their position. Which COPY INTO option should be used?

Accepted Answer

MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE

Answer

ENFORCE_LENGTH = TRUE

Answer

VALIDATION_MODE = RETURN_ALL_ERRORS

Answer

ON_ERROR = CONTINUE

Question 21

A data engineer runs a COPY INTO command to bulk load 50 CSV files from a named internal stage into a target table. Several rows across multiple files contain formatting errors that will cause parsing failures. The business requirement is that all valid rows must be loaded, and only the individual problematic rows should be skipped without aborting the load. Which ON_ERROR option should be specified in the COPY INTO command?

Accepted Answer

ON_ERROR = CONTINUE

Answer

ON_ERROR = ABORT_STATEMENT

Answer

ON_ERROR = SKIP_FILE

Answer

ON_ERROR = SKIP_FILE_10%

Question 22

A data engineer is loading a CSV file from a named internal stage into a target table. The source file has columns in the order (id, raw_timestamp, amount), but the target table columns are (amount, event_id, event_time), and the team wants to cast raw_timestamp to a TIMESTAMP and multiply amount by 100 during the load. Which approach lets them accomplish this in a single COPY INTO statement?

Accepted Answer

Use a COPY INTO statement with a SELECT query referencing the staged file (e.g., $1, $2, $3) to reorder, cast, and transform the columns before loading.

Answer

COPY INTO does not support transformations; load the raw data first, then run an UPDATE to reorder and cast the columns.

Answer

Define the transformations and column reordering entirely within the FILE FORMAT object, then run a standard COPY INTO.

Answer

Use the MATCH_BY_COLUMN_NAME option, which automatically casts and multiplies numeric columns to match the target schema.

Question 23

A data engineer needs to unload a large fact table from Snowflake to an external S3 stage. Downstream systems require the output to be plain, uncompressed CSV files (no GZIP), and the team wants each file kept under 100 MB to parallelize downstream reads. Which COPY INTO <location> configuration meets these requirements?

Accepted Answer

COPY INTO @s3_stage FROM fact_table FILE_FORMAT = (TYPE = CSV COMPRESSION = NONE) MAX_FILE_SIZE = 100000000;

Answer

COPY INTO @s3_stage FROM fact_table FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP) SINGLE = TRUE;

Answer

COPY INTO @s3_stage FROM fact_table FILE_FORMAT = (TYPE = CSV) MAX_FILE_SIZE = 100;

Answer

COPY INTO @s3_stage FROM fact_table FILE_FORMAT = (TYPE = PARQUET COMPRESSION = NONE) MAX_FILE_SIZE = 100000000;

Question 24

A data engineer must unload a large fact table to an external S3 stage. The downstream analytics team requires that the exported files be organized into subfolders by region and load date (for example, region=US/dt=2024-01-15/) so their partition-aware query engine can prune efficiently. Which approach in a single COPY INTO <location> statement produces this folder structure?

Accepted Answer

Use the PARTITION BY clause with an expression that builds the folder path from the region and date columns

Answer

Set SINGLE = TRUE so Snowflake writes one file per partition automatically

Answer

Specify MAX_FILE_SIZE with a small value so each region's rows land in a separate file

Answer

Add the OVERWRITE = TRUE option to force Snowflake to create date-based directories

Question 25

A data engineer is preparing to bulk load a large batch of CSV files from a named internal stage into a target table using COPY INTO. Before committing the load, they want to detect any data type mismatches or malformed rows without actually inserting any data into the table. Which approach should they use?

Accepted Answer

Run COPY INTO with the VALIDATION_MODE = 'RETURN_ERRORS' parameter to inspect errors without loading data

Answer

Run COPY INTO with ON_ERROR = 'CONTINUE' to load valid rows and skip the bad ones

Answer

Run COPY INTO with PURGE = TRUE to remove staged files after validation

Answer

Run COPY INTO with FORCE = TRUE to reprocess files and surface errors

Question 26

A business intelligence team wants to let non-technical analysts ask questions about sales data in plain English and receive accurate SQL-generated answers, without writing SQL themselves. The team wants to use a managed Snowflake capability that translates natural language questions into SQL against a defined semantic model. Which Snowflake feature should they use?

Accepted Answer

Cortex Analyst

Answer

Snowpark DataFrames

Answer

Streamlit in Snowflake

Answer

Snowflake Native App Framework

Question 27

A data engineer at a legal firm needs to automatically generate concise summaries of thousands of lengthy contract documents already stored as text in a Snowflake table. They want to use a fully managed, serverless capability that requires no infrastructure setup and can be invoked directly within a SQL statement. Which Snowflake feature should they use?

Accepted Answer

The Snowflake Cortex SUMMARIZE (LLM) function called via SQL

Answer

A Snowpark Python UDF that loads an open-source model into a virtual warehouse

Answer

A Streamlit in Snowflake app that manually paginates through each document

Answer

The Snowflake Native App Framework to distribute a summarization application

Question 28

A data scientist wants to build a semantic similarity feature entirely within Snowflake SQL. They need to convert a column of product description text into numerical vector representations so they can later compute cosine distance between products. They want to avoid deploying any external model infrastructure or writing Snowpark UDFs. Which Snowflake Cortex capability directly produces the vector representations from the text?

Accepted Answer

The AI_EMBED (Cortex embedding) function, which returns a VECTOR data type from input text

Answer

The COMPLETE function, which generates a chat response summarizing the text

Answer

The SENTIMENT function, which returns a numeric polarity score for the text

Answer

A Streamlit in Snowflake app that calls an external embedding API

Question 29

A data science team at an insurance company wants to adapt a base large language model so it produces claim-classification outputs that match their internal category labels and tone. They want to do this entirely within Snowflake without exporting data to an external service, using their own labeled training rows stored in a Snowflake table. Which Snowflake Cortex capability should they use?

Accepted Answer

Cortex Fine-tuning, which trains a customized version of a base model using the team's labeled data inside Snowflake

Answer

The COMPLETE function with a longer prompt that includes all labeled examples on every call

Answer

Cortex Search, which indexes the labeled rows to retrieve the closest matching category

Answer

The EMBED_TEXT function to convert labels into vectors that permanently reprogram the base model

Question 30

A retail analytics team wants to analyze thousands of customer product reviews stored in a Snowflake table to determine whether each review expresses positive or negative feelings. They want to do this using SQL, without training or deploying their own model, and without provisioning any external infrastructure. Which Snowflake capability best meets this requirement?

Accepted Answer

Snowflake Cortex LLM functions, which provide serverless access to pre-built AI functions such as AI_SENTIMENT callable directly from SQL

Answer

Snowpark, which requires the team to write and register a custom Python UDF that loads a sentiment model into a virtual warehouse

Answer

The Snowflake Native App Framework, which packages and distributes third-party applications through the Marketplace

Answer

Streamlit in Snowflake, which builds an interactive dashboard to visualize the reviews before analysis

Question 31

A development team is building an internal support chatbot that must retrieve relevant passages from thousands of PDF knowledge-base documents already loaded into Snowflake, then feed those passages to an LLM to generate answers. They want a fully managed retrieval service that combines semantic (vector) and keyword search, handles embedding and indexing automatically, and keeps the index fresh as documents change. Which Snowflake Cortex feature should they use for the retrieval component?

Accepted Answer

Cortex Search

Answer

Cortex Analyst

Answer

The COMPLETE function in Cortex LLM Functions

Answer

Snowpark ML Modeling API

Question 32

A data provider on Snowflake runs in AWS us-east-1 and has created a direct share containing several secure views. A prospective consumer runs their Snowflake account in Azure West Europe. The provider wants to share the data with this consumer with the least ongoing management overhead while ensuring the data remains accessible. What must the provider do to enable this cross-cloud, cross-region sharing?

Accepted Answer

Set up database replication to a secondary database in an account located in the consumer's region/cloud, then create and share from that replicated database (or use an auto-fulfillment listing).

Answer

Nothing special — direct shares automatically work across any cloud and region because sharing does not copy data.

Answer

Create a reader account for the consumer so they can access the share without their own Snowflake account.

Answer

Export the secure views to an external stage in Azure and grant the consumer access to the stage.

Question 33

A large enterprise has 12 separate Snowflake accounts across its business units. The central data governance team wants to establish a private, curated hub where each business unit can both publish datasets and discover datasets from other business units, but only accounts explicitly invited by the enterprise should be able to participate. Which Snowflake data collaboration feature best fits this requirement?

Accepted Answer

A Data Exchange, where the enterprise acts as the exchange administrator and invites specific member accounts to publish and consume listings

Answer

A public listing on the Snowflake Marketplace so any of the 12 accounts can freely subscribe

Answer

A set of reader accounts created for each business unit to consume shared data

Answer

A single direct share created by the governance team and shared once with all 12 accounts

Question 34

A data engineer is explaining to a new team member how Snowflake physically stores loaded table data in the Database Storage layer. The team member asks in what form Snowflake actually keeps the data on the underlying cloud object storage. Which statement accurately describes how Snowflake stores this data?

Accepted Answer

Data is stored in an optimized, compressed, columnar format that Snowflake manages internally and that customers cannot directly access or modify.

Answer

Data is stored as raw, uncompressed row-based files that customers can browse and edit directly in the cloud provider's storage console.

Answer

Data is stored in the customer's own external cloud storage bucket in open Parquet format so it can be read by external tools.

Answer

Data is stored in the virtual warehouse's local SSD cache and is lost whenever the warehouse is suspended.

Question 35

A data engineer creates a table named ORDERS in the SALES schema of the ANALYTICS database. Later, another team creates a table also named ORDERS in the MARKETING schema of the same ANALYTICS database. When troubleshooting, the engineer asks whether these two tables conflict. Based on Snowflake's object hierarchy, which statement is correct?

Accepted Answer

The two tables coexist without conflict because a table is a schema-scoped object, and names only need to be unique within a schema.

Answer

The two tables cannot coexist because table names must be unique within a database.

Answer

The two tables cannot coexist because table names must be unique across the entire Snowflake account.

Answer

The second CREATE TABLE statement will silently overwrite the first table since they share the same name.

Question 36

A provider account wants to share a curated database with a business partner who already has their own Snowflake account in the same cloud region. The provider wants the partner to query the data in real time without the provider paying for the partner's compute or creating a managed account. Which sharing approach should the provider use?

Accepted Answer

Create a direct share and add the partner's account as a consumer to the share

Answer

Create a reader account for the partner and grant it access to the share

Answer

Use database replication to copy the database into the partner's account

Answer

Publish the data as a public listing on the Snowflake Marketplace

Question 37

A data engineer creates a new table called SALES.RAW.ORDERS while operating under a custom role named LOAD_ROLE. Later, an analyst using the REPORTING_ROLE reports they cannot query the table, even though an ACCOUNTADMIN confirms the table exists. Aside from using a higher system-defined role, which action correctly reflects Snowflake's discretionary access control model to grant the analyst access?

Accepted Answer

LOAD_ROLE, as the owning role of the table, grants SELECT on SALES.RAW.ORDERS to REPORTING_ROLE.

Answer

The analyst must be granted the LOAD_ROLE, because only the creating role can ever query objects it owns.

Answer

SECURITYADMIN must transfer ownership of the table to REPORTING_ROLE before any SELECT can succeed.

Answer

PUBLIC role must be granted SELECT on the table so all roles automatically inherit query access.

Question 38

A data engineer needs to build a continuously refreshing transformation that joins raw order and customer tables and materializes the aggregated result. The business requires the transformed data to never be more than 5 minutes stale, and the team wants to avoid writing and scheduling custom tasks or streams to manage incremental refresh logic. Which Snowflake object best meets these requirements?

Accepted Answer

A dynamic table with TARGET_LAG set to 5 minutes

Answer

A materialized view referencing the joined tables

Answer

A standard view refreshed by a scheduled task every 5 minutes

Answer

A secure view with the result cache enabled

Question 39

A data engineer needs to maintain a pre-computed table that joins three large source tables and applies several aggregations. The result must be automatically refreshed as the source data changes, without writing custom scheduling or task logic, and must support multi-table joins. Which Snowflake object best fits these requirements?

Accepted Answer

A dynamic table with an appropriate TARGET_LAG defined on the join and aggregation query

Answer

A materialized view built on the join of the three tables

Answer

A secure view that references all three tables

Answer

A standard view combined with the search optimization service

Question 40

A data engineering team on Snowflake Standard Edition wants to enable materialized views to speed up repeated aggregate queries on a large reporting table. Their manager asks what is the minimum Snowflake edition required to use materialized views, and how this compares to the team's current edition. Which statement is correct?

Accepted Answer

Materialized views require at least Enterprise Edition, so the team must upgrade from Standard.

Answer

Materialized views are available in all editions including Standard, so no upgrade is needed.

Answer

Materialized views require Business Critical Edition because they involve additional data replication.

Answer

Materialized views are only available in Virtual Private Snowflake (VPS) for security reasons.

Question 41

A data engineer needs to load files from an Amazon S3 bucket into Snowflake tables. She wants to create a reusable reference to the S3 location, along with the credentials and file format, so multiple loading jobs across different schemas can reference it. Within the Snowflake object hierarchy, which type of object should she create to point to the external S3 location?

Accepted Answer

An external stage

Answer

A named internal stage

Answer

A table stage

Answer

A user stage

Question 42

A data engineer creates an external table over Parquet files stored in an Amazon S3 bucket. New files are added to the bucket by an upstream process several times per hour, and the team needs the external table's metadata to reflect newly added files automatically, without manual intervention. Which configuration should the engineer implement?

Accepted Answer

Enable AUTO_REFRESH on the external table and configure S3 event notifications to an SQS queue provided by Snowflake

Answer

Schedule a task that runs COPY INTO from the external stage every few minutes to reload the files

Answer

Create a Snowpipe with auto-ingest to continuously copy the Parquet files into the external table

Answer

Run ALTER EXTERNAL TABLE ... REFRESH manually after each upstream load completes

Question 43

A data engineer has a table EVENTS with a VARIANT column PAYLOAD. Each row's PAYLOAD contains a JSON object with a top-level array field named 'items', where each element has 'sku' and 'qty' attributes. The engineer needs a result set with one row per item, exposing the SKU and quantity as separate columns alongside the event's ID. Which query construct correctly produces this output?

Accepted Answer

SELECT e.id, f.value:sku::string AS sku, f.value:qty::number AS qty FROM events e, LATERAL FLATTEN(input => e.payload:items) f

Answer

SELECT e.id, e.payload:items:sku::string AS sku, e.payload:items:qty::number AS qty FROM events e

Answer

SELECT e.id, PARSE_JSON(e.payload:items):sku AS sku, PARSE_JSON(e.payload:items):qty AS qty FROM events e

Answer

SELECT e.id, f.sku, f.qty FROM events e GROUP BY e.id, FLATTEN(e.payload:items)

Question 44

A data engineer is transforming a raw table named ORDERS that has a VARIANT column ITEMS containing a JSON array of line items. Some order rows have an empty ITEMS array or a NULL ITEMS value. The engineer writes a query using LATERAL FLATTEN to explode the array into one row per line item, but notices that orders with empty or NULL arrays disappear entirely from the result set. The requirement is to keep exactly one row for every order, even when it has no line items (returning NULL for the line-item columns in that case). Which change should the engineer make to the FLATTEN call?

Accepted Answer

Add the OUTER => TRUE argument to the FLATTEN function.

Answer

Add the RECURSIVE => TRUE argument to the FLATTEN function.

Answer

Change the join from LATERAL to a CROSS JOIN so all rows are preserved.

Answer

Set the MODE => 'ARRAY' argument to force array-only expansion.

Question 45

A data engineer loads deeply nested JSON documents into a VARIANT column. Each document contains an 'employees' array, and each employee element itself contains a nested 'projects' array of objects. The engineer needs a single query that returns one row per project, with columns for the employee name and the project name. Which approach correctly produces this result?

Accepted Answer

Use LATERAL FLATTEN on the 'employees' array, then chain a second LATERAL FLATTEN on the 'projects' array of each flattened employee record

Answer

Use a single FLATTEN with RECURSIVE => TRUE to automatically expand every level of nesting into individual rows

Answer

Use FLATTEN on the 'employees' array only, then apply PARSE_JSON to the projects column to split it into rows

Answer

Use two separate queries joined by UNION ALL, one flattening employees and one flattening projects

Question 46

A data engineering team creates dozens of new tables in the ANALYTICS schema each week. The security administrator wants the REPORTING role to automatically have SELECT privileges on every table created in that schema going forward, without manually granting access to each new table. Which command accomplishes this?

Accepted Answer

GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics TO ROLE reporting;

Answer

GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE reporting;

Answer

GRANT SELECT ON SCHEMA analytics TO ROLE reporting;

Answer

GRANT OWNERSHIP ON SCHEMA analytics TO ROLE reporting;

Question 47

A data engineer used COPY INTO to unload query results into files in a named internal stage. They now need to download those files from the stage to their local workstation for offline inspection. Which command should they run from SnowSQL?

Accepted Answer

GET @my_stage file:///Users/engineer/downloads/;

Answer

PUT @my_stage file:///Users/engineer/downloads/;

Answer

COPY INTO file:///Users/engineer/downloads/ FROM @my_stage;

Answer

SELECT * FROM @my_stage INTO 'file:///Users/engineer/downloads/';

Question 48

A data engineer is organizing objects for a new project. She needs a location to temporarily hold raw CSV files uploaded from her laptop via the PUT command before loading them into tables. She wants this staging location to be reusable across multiple load jobs, governed by the same access privileges as the tables it feeds, and contained entirely within Snowflake without referencing external cloud storage. Which object should she create?

Accepted Answer

A named internal stage created within the target schema

Answer

An external stage pointing to an S3 bucket in the same region

Answer

The user stage associated with her Snowflake login

Answer

A table stage automatically tied to the destination table

Question 49

A data engineer needs to create a scalar UDF that parses a URL string, extracts the query parameters into a set of key-value pairs, and returns a formatted VARIANT. The logic requires procedural string manipulation with loops and conditional branching that is awkward to express in pure SQL. Performance on large row counts is a concern, but the function must remain a scalar UDF callable in SELECT statements. Which approach best fits these requirements?

Accepted Answer

Create a JavaScript UDF, since it supports procedural constructs like loops and conditionals while returning a VARIANT and remaining callable inline in queries

Answer

Create a SQL UDF, because SQL UDFs can execute arbitrary procedural loops and are always faster than other UDF languages

Answer

Create a stored procedure, because only stored procedures can return VARIANT values and process complex logic

Answer

Create a JavaScript UDTF, because scalar transformations of a single input value require a tabular function

Question 50

A data analytics team runs a legacy on-premises reporting application written in C++ that connects to databases using a standard native interface. They need to query Snowflake from this application with minimal code changes. Separately, a Java-based ETL microservice must also connect to Snowflake to run queries. Which combination of Snowflake drivers should the team use for these two applications?

Accepted Answer

Use the ODBC driver for the C++ application and the JDBC driver for the Java microservice.

Answer

Use the JDBC driver for the C++ application and the ODBC driver for the Java microservice.

Answer

Use the Python connector for both applications through a wrapper layer.

Answer

Use the Node.js driver for the C++ application and the JDBC driver for the Java microservice.

Question 51

A data engineering team is streaming clickstream events from Apache Kafka topics into Snowflake. They require the lowest possible ingestion latency (seconds, not minutes) and want to minimize per-file overhead and storage of intermediate files in a stage. Which configuration of the Snowflake Kafka connector should they use?

Accepted Answer

Configure the Kafka connector to use Snowpipe Streaming, which writes rows directly into target tables via the streaming ingest API without staging files

Answer

Configure the Kafka connector to use file-based Snowpipe, buffering records into files in an internal stage that are then loaded with COPY INTO

Answer

Use the Kafka connector with a scheduled task that runs COPY INTO from an external stage every minute

Answer

Replace the Kafka connector with the Spark connector and write micro-batches using overwrite mode

Question 52

A data engineering team runs an automated ELT pipeline using a dedicated Snowflake service account. Security policy prohibits interactive password prompts and requires MFA for all human logins, but the service account must authenticate non-interactively from a scheduled job. Which authentication method should the team configure for the service account, and what best practice applies?

Accepted Answer

Key pair authentication, generating an RSA key pair and periodically rotating keys using RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2

Answer

Password authentication with MFA enrolled, caching the MFA token in the job scheduler

Answer

SSO through the corporate identity provider, using the SAML assertion in the job configuration

Answer

OAuth with an interactive browser consent flow triggered at the start of each scheduled run

Question 53

A data provider wants to publish a curated dataset so that any Snowflake customer across multiple regions and cloud platforms can discover it, request access, and consume it without the provider having to know each consumer's account identifier in advance. Which data collaboration mechanism best meets this requirement?

Accepted Answer

Publish a listing on the Snowflake Marketplace

Answer

Create a direct share and add each consumer account to the share individually

Answer

Set up reader accounts for each prospective consumer

Answer

Create a private data exchange and invite each member by account

Question 54

A data provider publishes a free public listing on the Snowflake Marketplace from their account in AWS US East. A prospective consumer running a Snowflake account in Azure West Europe wants to access the listed dataset with the lowest ongoing effort and no data copying on the provider's side. What must happen for the consumer to successfully query this listing's data?

Accepted Answer

The provider must enable auto-fulfillment (cross-cloud auto-fulfillment) so Snowflake automatically replicates the listing's data to the consumer's region when it is requested.

Answer

The consumer must create a reader account in AWS US East and query the data from there, since Marketplace listings cannot cross cloud regions.

Answer

The provider must manually export the dataset to external cloud storage and grant the consumer a storage integration to load it.

Answer

The consumer can query the shared database directly because Marketplace listings are globally accessible in all regions without any replication.

Question 55

A data governance team must ensure that a customer email column in the CUSTOMERS table shows real values only to users with the PII_ADMIN role, while all other users see a fully masked value. The solution must apply automatically to every query, including SELECT *, and must be reusable across multiple columns. Which Snowflake feature should the team implement?

Accepted Answer

A masking policy assigned to the email column that returns the unmasked value when CURRENT_ROLE() is PII_ADMIN and a masked string otherwise

Answer

A row access policy on the CUSTOMERS table that filters rows based on the querying user's role

Answer

A secure view over CUSTOMERS that applies a CASE expression on the email column, granted only to PII_ADMIN

Answer

An object tag applied to the email column identifying it as PII-sensitive

Question 56

A data engineer creates a materialized view on a very large fact table to speed up a frequently run dashboard aggregation. After deployment, the base table receives heavy, continuous DML (thousands of inserts and updates per hour). The team notices unexpected serverless credit consumption even during periods when no one is querying the dashboard. What is the MOST likely cause of this credit consumption?

Accepted Answer

The materialized view is being automatically maintained by a Snowflake-managed background service that consumes serverless credits as the base table changes.

Answer

The materialized view keeps the querying virtual warehouse running continuously to serve results from cache.

Answer

Each dashboard query against the materialized view triggers a full re-scan of the base table, consuming warehouse credits.

Answer

The result cache backing the materialized view is being refreshed every 24 hours, incurring recurring compute charges.

Question 57

A data engineer maintains a large fact table with a materialized view that pre-aggregates daily sales by region. Queries against the materialized view have grown slow because they filter on a specific region, and the engineer wants to improve partition pruning on the materialized view itself. Which action is supported in Snowflake to address this?

Accepted Answer

Define a clustering key directly on the materialized view to co-locate rows by region

Answer

Enable Time Travel on the materialized view to reorganize its micro-partitions

Answer

Create a second materialized view that references the first materialized view

Answer

Add a search optimization service configuration to the base table's aggregated columns

Question 58

An analytics team runs the same expensive aggregation query dozens of times per hour against a very large, slowly-changing fact table. The query scans billions of rows and computes SUM and COUNT grouped by a low-cardinality region column. They want to dramatically reduce compute cost and latency for these repeated aggregations, and they accept a small amount of maintenance cost when the base table changes. Which Snowflake feature best fits this requirement?

Accepted Answer

Create a materialized view that pre-computes the grouped aggregation, so queries read the pre-aggregated results instead of scanning the full fact table

Answer

Create a standard (non-materialized) view over the aggregation so the definition is reused and Snowflake caches the results permanently

Answer

Rely solely on the result cache by re-running the identical query, since results are always served from cache for 24 hours regardless of base table changes

Answer

Add a clustering key on the region column so the aggregation no longer needs to scan any micro-partitions

Question 59

A data analyst runs 'SELECT COUNT(*) FROM sales_transactions;' against a very large table with no filters. The query returns almost instantly, and the Query Profile shows a single 'METADATA-BASED RESULT' step with no compute processing. The warehouse was suspended at the time and did not resume. Which Snowflake caching or optimization mechanism is responsible for this behavior?

Accepted Answer

The metadata cache in the cloud services layer, which stores row counts and min/max statistics per micro-partition

Answer

The warehouse local disk (SSD) cache, which retained the column data from a prior scan

Answer

The result cache, which returned the previously computed result set from the last 24 hours

Answer

A materialized view that Snowflake automatically created to precompute the aggregate

Question 60

A data engineer is investigating why a query with a WHERE clause filtering on an ORDER_DATE column scans far fewer micro-partitions on one large table than on another table of similar size. The well-performing table's data was loaded in ORDER_DATE order, while the other table's rows were loaded in random order. What Snowflake storage mechanism explains the difference in the number of micro-partitions scanned?

Accepted Answer

Snowflake stores min/max metadata per micro-partition and prunes partitions whose value ranges do not overlap the filter; naturally ordered data yields tighter, non-overlapping ranges

Answer

Snowflake automatically re-sorts every table on each query using the compute layer, so load order has no effect on scanning

Answer

The well-performing table must have a materialized view that the optimizer transparently substitutes for the base table

Answer

Micro-partitions are cached in the cloud services layer, and only tables loaded in sorted order are eligible for that cache

Question 61

A data engineering team runs a multi-cluster virtual warehouse (min 1, max 5 clusters) on Snowflake Enterprise Edition to handle unpredictable bursts of BI dashboard queries. Management complains that clusters sometimes spin up for very short traffic spikes, driving up credit consumption. They want Snowflake to be more conservative about starting additional clusters, favoring lower cost even if some queries wait slightly longer in the queue. Which configuration change should the team make?

Accepted Answer

Set the multi-cluster warehouse scaling policy to 'Economy'.

Answer

Set the multi-cluster warehouse scaling policy to 'Standard'.

Answer

Increase the warehouse size from Medium to Large.

Answer

Reduce the auto-suspend timer to 60 seconds.

Question 62

A security administrator at a financial services firm wants to reduce the risk of compromised credentials for a small group of privileged users who authenticate with username and password. The firm does not use an identity provider for SSO. Management asks for the fastest way to add a second authentication factor for these specific users without changing how the rest of the organization logs in. What should the administrator do?

Accepted Answer

Have each of the privileged users enroll in Snowflake's built-in multi-factor authentication (MFA) from their Snowsight user preferences (current methods include passkeys, authenticator apps, and Duo)

Answer

Create a network policy that restricts the privileged users to a specific IP allowlist

Answer

Configure key pair authentication and rotate the keys for the privileged users on a 90-day schedule

Answer

Enable SAML SSO federation for the account so all users authenticate through an external identity provider

Question 63

A data engineer runs several COPY INTO commands each day to load pipe-delimited files that all share the same field delimiter, compression, and date format settings. To avoid repeating these settings in every COPY statement and to centrally manage them, what should the engineer create?

Accepted Answer

A named file format object that is referenced by each COPY INTO command

Answer

A separate external stage for each file with the parsing options embedded

Answer

A validation-mode COPY that stores the parsing rules for later reuse

Answer

A stream on the target table that inherits the parsing options automatically

Question 64

A software vendor wants to package proprietary stored procedures, UDFs, and Streamlit UI together and distribute this application to customer Snowflake accounts through the Snowflake Marketplace, while keeping the underlying business logic hidden from consumers and running entirely inside each consumer's account. Which Snowflake feature is designed for this use case?

Accepted Answer

Snowflake Native App Framework

Answer

Secure data sharing with a reader account

Answer

Snowpark Container Services deployed in the provider account

Answer

A database clone shared via a private listing

Exam domain	Exam weight	Practice
Snowflake AI Data Cloud Features and Architecture	31%	Practice this topic
Account Management and Data Governance	20%	Practice this topic
Data Loading, Unloading, and Connectivity	18%	Practice this topic
Performance Optimization, Querying, and Transformation	21%	Practice this topic
Data Collaboration	10%	Practice this topic

Snowflake SnowPro Core (COF-C03) (COF-C03) practice exam & study guide

Start studying COF-C03

All COF-C03 study resources

COF-C03 exam domains

Sample COF-C03 questions

Key COF-C03 terms

COF-C03 frequently asked questions

Snowflake AI Data Cloud Features and Architecture (31%)

Account Management and Data Governance (20%)

Data Loading, Unloading, and Connectivity (18%)

Performance Optimization, Querying, and Transformation (21%)

Data Collaboration (10%)