COF-C03 Exam Dumps - Snowflake SnowPro Core Certification Questions and Answers

To list all network policies available in an account, the correct command isSHOW NETWORK POLICIES. Network policies in Snowflake are used to define and enforce rules for how users can connect to Snowflake, including IP whitelisting and other connection requirements. TheSHOW NETWORK POLICIEScommand provides a list of all network policies defined within the account, along with their details.

TheDESCRIBE SESSION POLICYandDESCRIBE NETWORK POLICYcommands do not exist in Snowflake SQL syntax. TheSHOW SESSION POLICIEScommand is also incorrect, as it does not pertain to the correct naming convention used by Snowflake for network policy management.

UsingSHOW NETWORK POLICIESwithout any additional parameters will display all network policies in the account, which is useful for administrators to review and manage the security configurations pertaining to network access.

The correct answers are C. Bracket notation and E. Dot notation .

Snowflake supports querying semi-structured data stored in columns such as VARIANT, OBJECT, and ARRAY. When working with JSON objects, users can access elements using either dot notation or bracket notation .

Why C is correct:

Bracket notation is used to access JSON object elements by enclosing the key name in brackets.

Example:

SELECT src[ ' customer ' ][ ' name ' ]

FROM json_table;

Bracket notation is especially useful when JSON keys contain special characters, spaces, or case-sensitive names.

Why E is correct:

Dot notation is used to access JSON object elements using a period between object levels.

Example:

SELECT src:customer.name

FROM json_table;

This notation is commonly used for simple JSON key paths.

Why the other options are incorrect:

A. CIDR notation is used for IP address ranges, not JSON element access.

B. Vertical bar notation is not a Snowflake method for accessing JSON object elements.

D. Slash notation is not a Snowflake method for accessing JSON object elements.

Official Snowflake documentation reference:

Snowflake documentation explains that semi-structured data elements in VARIANT columns can be accessed using dot notation and bracket notation.

The correct answers are A. SELECT column:key_1 FROM my_table; and C. SELECT column[ ' key_1 ' ] FROM my_table; .

Snowflake supports path notation for querying semi-structured data stored in columns such as VARIANT, OBJECT, and ARRAY. For a JSON object key, Snowflake supports colon path notation and bracket notation.

Why A is correct:

The colon path notation accesses a field inside a semi-structured column.

Example:

SELECT column:key_1

FROM my_table;

This extracts the value associated with the key key_1.

Why C is correct:

Bracket notation also accesses an object element by key.

Example:

SELECT column[ ' key_1 ' ]

FROM my_table;

This is especially useful when key names require quoting or contain special characters.

Why the other options are incorrect:

B. Dot notation is supported for nested traversal after the initial colon path, such as column:key_1.subkey, but column.key_1 by itself is interpreted as regular SQL object/column qualification, not as extracting a key from a semi-structured column.

D. Parentheses are not valid JSON object key extraction syntax in Snowflake.

E. Casting a column to string and aliasing it does not extract the value for the key key_1.

Official Snowflake documentation reference:

Snowflake documentation explains that elements in semi-structured data can be accessed using colon path notation and bracket notation. Dot notation may also be used for nested paths after the semi-structured column reference.

Zero-copy cloning initially shares micro-partitions between the source object and the clone. However, when data is modified in either the original or cloned object, Snowflake creates new micro-partitions for the changed data. Those modified micro-partitions consume additional storage.

The correct answer is C. To track how data has been transformed among columns .

Snowflake’s data lineage capability is used to understand how data flows and changes across objects. It helps users see relationships between source and target objects and understand how data is transformed, especially at the column level.

Corrected wording:

The original option said “records,” but Snowflake data lineage is more accurately described as tracking how data is transformed among columns, not individual row-level record transformations.

Why C is correct:

Data lineage helps track how data moves from source columns to target columns through operations such as views, CTAS statements, INSERT statements, and other transformations. This is useful for governance, impact analysis, auditing, and understanding downstream dependencies.

Why the other options are incorrect:

A. Tracking how records are read by a user is more closely related to access history or query history, not data lineage.

B. Tracking dependencies at the object level is related to object dependencies. Data lineage is more specific because it can show how data flows between columns and objects.

D. Snowflake data lineage is not primarily a row-level feature for tracking the impact of transformations on individual records.

Official Snowflake documentation reference:

Snowflake documentation describes data lineage as a governance capability that helps track how data moves and transforms across Snowflake objects, including column-level relationships.

Two key metrics in Snowflake’sQuery Profilethat provide insights for performance improvement are:

Remote Disk IO: This measures the time the query spends waiting on remote disk access, indicating potential performance issues related to I/O bottlenecks.

Pruning: This metric reflects how effectively Snowflake’s micro-partition pruning is reducing the data scanned. Better pruning (more partitions excluded) leads to faster query performance, as fewer micro-partitions need to be processed.

These metrics are essential for identifying and addressing inefficiencies in data retrieval and storage access, optimizing overall query performance.

In Snowflake, the authorization to executeCREATE < object > statements, such as creating tables, views, databases, etc., is determined by the role currently set as the user ' s primary role. The primary role of a user or session specifies the set of privileges (including creation privileges) that the user has. While users can have multiple roles, only the primary role is used to determine what objects the user can create unless explicitly specified in the session.

With reader accounts , the provider pays for:

Storage of shared data

Compute used by the reader account

The consumer does not pay , as the infrastructure is provisioned and managed by the provider.

Fail-safe is a non-configurable feature in Snowflake that provides an additional layer of data protection beyond Time Travel. Time Travel allows users to access historical data within a configurable period (up to 90 days), while Fail-safe provides an additional 7-day period during which Snowflake retains historical data to recover from significant data loss or corruption incidents. This period is not accessible by users but can be used by Snowflake support to assist in data recovery efforts.

For Snowpipe auto-ingest on Amazon S3, cloud event notifications are configured so Snowflake is notified when new files arrive. The pipe must also be created with AUTO_INGEST = TRUE.

Why A and D are correct:

Amazon S3 event notifications can publish file arrival events through Amazon SNS/SQS integration, and Snowpipe uses those notifications when AUTO_INGEST is enabled.

The correct answer is B. Snowpipe references the metadata of previously loaded files and will not load new files with the same metadata .

Snowflake keeps load metadata for files loaded into a table. Snowpipe uses this metadata to prevent duplicate loading of the same file into the same table.

Why B is correct:

Snowpipe tracks metadata for loaded files, including information that identifies files already processed. If the same file is encountered again, Snowpipe can use the load metadata to avoid loading it again.

Why the other options are incorrect:

A. Snowpipe does not automatically delete files from the stage after loading.

C. Snowpipe does not automatically rename staged files after loading.

D. Snowpipe does not automatically move loaded files to a temporary stage.

Official Snowflake documentation reference:

Snowflake documentation explains that Snowpipe maintains load metadata and prevents the same files from being loaded multiple times into the same table by default.

The correct answer is A. The table data does not require Fail-safe .

Transient tables are designed for data that does not need the same level of data recovery protection as permanent tables. Unlike permanent tables, transient tables do not have a Fail-safe period.

Why A is correct:

Fail-safe storage can add cost because Snowflake retains historical data for recovery by Snowflake support after the Time Travel retention period ends. Since transient tables do not have Fail-safe, they can reduce storage costs for data that does not require this additional protection.

Common use cases include:

Temporary ETL data

Staging data

Intermediate transformation data

Reproducible data

Non-critical working data

Why the other options are incorrect:

B. Long-term critical storage is usually better suited for permanent tables because they support Fail-safe.

C. Frequent access and modification alone does not make a transient table the best cost choice. The key factor is whether Fail-safe is needed.

D. Sharing data with external accounts is not the primary cost-effective use case for transient tables.

Official Snowflake documentation reference:

Snowflake documentation explains that transient tables persist until explicitly dropped, support Time Travel with limited retention, and do not have a Fail-safe period.