View Full Snowflake SnowPro Core COF-C03 Exam Dumps and Practice Test Dumps.

 

Question 101

Which Snowflake table type exists only for the duration of the session in which it is created?

1. Permanent table
2. Temporary table
3. Transient table
4. External table

Correct Answer: 2

Explanation

A temporary table is designed for session-specific data and exists only during the session in which it was created. When that session ends, the temporary table is automatically removed. Temporary tables are useful for intermediate calculations, testing, temporary transformations, and ETL processing where persistent storage is unnecessary. They are not intended for long-term business data. A temporary table can have the same name as another table in the same schema, which can create naming considerations. Because its lifetime is tied to the session, users should not depend on it for information that must remain available after the session ends.

Question 102

A data engineer needs a table that persists after sessions end but does not require Fail-safe protection. Which table type is appropriate?

1. Temporary
2. External
3. Transient
4. Permanent

Correct Answer: 3

Explanation

A transient table persists until it is explicitly dropped, so it remains available across user sessions. Unlike a permanent table, however, a transient table does not have Fail-safe protection. This makes transient tables useful for staging information, intermediate ETL results, temporary business processes, and datasets that can be recreated if necessary. They can provide persistence without the additional recovery characteristics associated with permanent tables. When selecting a table type, administrators should consider how long the information must remain available, whether it needs Time Travel, and whether additional recovery protection is required.

Question 103

What is the primary benefit of zero-copy cloning in Snowflake?

1. It creates a physical duplicate immediately
2. It automatically compresses all source data
3. It eliminates the need for metadata
4. It initially shares existing storage rather than copying all data

Correct Answer: 4

Explanation

Zero-copy cloning allows Snowflake to create a clone without immediately making a complete physical copy of the source data. The clone initially references and shares the existing micro-partitions with the source object. This allows databases, schemas, and tables to be cloned quickly while avoiding unnecessary duplication of unchanged data. If modifications are later made to the clone, Snowflake creates additional storage for the changed information as needed. This approach is useful for development, testing, reporting, and experimentation because users can create independent environments efficiently without initially duplicating the entire dataset.

Question 104

Which SQL statement correctly creates a zero-copy clone of an existing table named SALES?

1. CREATE TABLE SALES_CLONE CLONE SALES;
2. CREATE TABLE SALES_CLONE COPY SALES;
3. CLONE TABLE SALES INTO SALES_CLONE;
4. CREATE TABLE SALES_CLONE AS DUPLICATE SALES;

Correct Answer: 1

Explanation

The CREATE TABLE statement with the CLONE keyword is used to create a table clone in Snowflake. The statement CREATE TABLE SALES_CLONE CLONE SALES; creates SALES_CLONE based on the existing SALES table. The operation initially uses Snowflake’s zero-copy cloning mechanism rather than physically duplicating every micro-partition. The cloned table can subsequently be modified independently from the original table. This functionality is particularly useful for creating development or testing tables quickly. It also avoids the immediate storage overhead that would normally result from creating a complete physical copy of a large dataset.

Question 105

A cloned table is modified after creation. What generally happens to the affected data storage?

1. The source table is automatically modified
2. New micro-partitions can be created for the clone
3. The clone becomes a view
4. The entire source table is copied

Correct Answer: 2

Explanation

A zero-copy clone initially shares the source table’s existing micro-partitions. When the cloned table is subsequently changed through operations such as inserts, updates, or deletes, Snowflake can create new micro-partitions for the changed data. These modifications do not automatically alter the original source table. The source and clone therefore become independent as changes accumulate. This behavior is one of the main advantages of zero-copy cloning because unchanged information continues to use shared storage while modified information is stored separately. As a result, cloning can provide isolated working environments without immediately duplicating the entire source dataset.

Question 106

Which Snowflake table type provides Fail-safe protection as part of its standard data lifecycle?

1. Permanent
2. Temporary
3. Transient
4. External

Correct Answer: 1

Explanation

Permanent tables provide Fail-safe protection as part of Snowflake’s standard lifecycle for persistent data. After the applicable Time Travel period ends, eligible historical data associated with permanent tables can enter Fail-safe. Fail-safe is intended primarily for disaster recovery rather than normal user-controlled historical querying. Temporary and transient tables do not receive this Fail-safe protection. Permanent tables are therefore commonly used for important business datasets that require stronger recovery characteristics. The choice between permanent, transient, and temporary storage should reflect data importance, expected lifetime, recovery requirements, and operational needs.

Question 107

Which table type is normally suitable for short-lived ETL work data that can be recreated if lost?

1. Permanent
2. Temporary
3. Transient
4. External

Correct Answer: 3

Explanation

Transient tables are well suited for datasets that need to persist beyond an individual session but can be recreated if necessary. They are commonly useful for intermediate ETL results, staging information, temporary processing areas, and other data that does not require Fail-safe protection. Unlike temporary tables, transient tables remain available after the session that created them ends. However, they provide fewer recovery characteristics than permanent tables. Organizations should therefore avoid using transient tables for critical information that requires stronger recovery protection. Their main advantage is providing persistent storage with a simpler recovery lifecycle.

Question 108

A temporary table and a permanent table in the same schema have the same name. Which object takes precedence within the creating session?

1. The permanent table
2. The temporary table
3. Both objects are inaccessible
4. The permanent table is automatically renamed

Correct Answer: 2

Explanation

Snowflake allows a temporary table to have the same name as an existing permanent table in the same schema. Within the session where the temporary table exists, references to that name resolve to the temporary table. The permanent table remains present, but it is effectively hidden by the temporary object for name resolution during that session. This behavior can be useful in some workflows but may also cause confusion if users are unaware of the duplicate name. Clear naming conventions are therefore recommended when working with temporary and permanent objects.

Question 109

Which clause allows a Snowflake clone to represent an object at a historical point in time?

1. HISTORY
2. SNAPSHOT
3. AT | BEFORE
4. RETENTION

Correct Answer: 3

Explanation

The AT or BEFORE clause can be used with supported Snowflake cloning operations to create a clone representing an object at a previous point in time. The historical reference can be based on information such as a timestamp, statement identifier, or relative time offset, depending on the syntax used. This capability relies on data remaining available within the applicable Time Travel retention period. Historical cloning can be useful for investigating earlier data states, reproducing an environment for testing, or recovering a previous version of information without changing the current source object.

Question 110

What happens to historical data from a transient table after its Time Travel retention period expires?

1. It automatically enters a seven-day Fail-safe period
2. It remains fully recoverable indefinitely
3. Snowflake converts it to a permanent table
4. It is no longer recoverable through Snowflake Time Travel

Correct Answer: 4

Explanation

Transient tables do not have Fail-safe protection. Their historical data can remain available during the configured Time Travel retention period, but once that period expires, the historical information is no longer recoverable through Snowflake Time Travel. This is an important distinction between transient and permanent tables. Transient storage is therefore better suited to data that is temporary, reproducible, or otherwise not dependent on extended recovery capabilities. Organizations should carefully evaluate recovery requirements before storing important business information in transient tables. Data requiring stronger protection should generally use an appropriate permanent table configuration.

Question 111

Which statement about a permanent Snowflake table is correct?

1. It is the default table type when no temporary or transient keyword is specified
2. It automatically disappears at session end
3. It never supports Time Travel
4. It cannot be cloned

Correct Answer: 1

Explanation

Permanent is the default table type in Snowflake when CREATE TABLE is used without specifying TEMPORARY or TRANSIENT. A permanent table remains available until it is explicitly dropped and can participate in Snowflake features such as Time Travel and Fail-safe according to the applicable configuration and lifecycle rules. This makes permanent tables suitable for long-lived business data and important analytical datasets. Temporary and transient tables require their respective keywords during creation. Understanding the default behavior helps users avoid unintentionally selecting a table type with a different persistence or recovery lifecycle.

Question 112

Which SQL command can create a transient table?

1. CREATE TEMPORARY TABLE
2. CREATE TRANSIENT TABLE
3. CREATE SESSION TABLE
4. CREATE RECOVERABLE TABLE

Correct Answer: 2

Explanation

Snowflake uses the TRANSIENT keyword with CREATE TABLE to create a transient table. A statement such as CREATE TRANSIENT TABLE staging_data (…) creates a table that persists until explicitly dropped but does not have Fail-safe protection. This differs from a temporary table, whose lifetime is tied to the session that created it. Transient tables are useful for persistent staging and intermediate datasets that do not require the full recovery lifecycle of permanent tables. Selecting this table type can help align storage behavior with the actual business importance and expected lifetime of the data.

Question 113

Which statement can create a clone of an entire Snowflake database?

1. CREATE DATABASE … CLONE
2. COPY DATABASE
3. DATABASE DUPLICATE
4. ALTER DATABASE … COPY

Correct Answer: 1

Explanation

Snowflake supports cloning at the database level through the CREATE DATABASE statement with the CLONE keyword. Database cloning can create a new database containing cloned schemas and supported child objects from the source database. The operation benefits from zero-copy cloning, so the initial clone does not require a complete physical duplication of unchanged data. Database-level cloning is useful when organizations need isolated environments for development, testing, analysis, or other workloads. Historical cloning can also be used when supported and when the required source state remains available within the relevant Time Travel retention period.

Question 114

When cloning a database or schema, what can happen to privileges granted on cloned child objects?

1. All privileges are always removed
2. Relevant child-object privileges can be inherited by corresponding cloned objects
3. Only ACCOUNTADMIN privileges are copied
4. All privileges are converted into ownership

Correct Answer: 2

Explanation

When Snowflake clones a database or schema, privileges associated with child objects can be inherited by the corresponding cloned child objects under the applicable cloning rules. This behavior differs from simply assuming that every privilege on the original container will automatically be reproduced in exactly the same way. Administrators should understand how grants behave for the specific object hierarchy being cloned and verify access after creating the clone. This is especially important when cloned databases or schemas are used for development and testing because users may otherwise receive unexpected access or lack permissions they require.

Question 115

Which Snowflake data type is designed for fixed-point numeric values with configurable precision and scale?

1. VARCHAR
2. BOOLEAN
3. NUMBER
4. BINARY

Correct Answer: 3

Explanation

NUMBER is Snowflake’s fixed-point numeric data type and supports configurable precision and scale. Precision represents the total number of digits that can be stored, while scale identifies how many digits are maintained after the decimal point. NUMBER is commonly used for financial values, quantities, measurements, identifiers requiring numeric storage, and other calculations where predictable decimal behavior is important. VARCHAR is intended for character data, BOOLEAN represents logical true or false values, and BINARY stores binary information. Choosing an appropriate numeric data type helps maintain accuracy and consistent behavior during calculations and comparisons.

Question 116

Which SQL expression is commonly used to explicitly convert a value from one data type to another?

1. CAST
2. GROUP
3. ORDER
4. FILTER

Correct Answer: 1

Explanation

CAST is a standard SQL expression used to explicitly convert a value from one data type to another. For example, a query can use CAST(column_name AS NUMBER) when text or another compatible value needs to be interpreted as a numeric value. Explicit conversion is useful when calculations, comparisons, or functions require a particular data type. Snowflake also provides other conversion functions and syntax, but CAST is a broadly applicable method. Using explicit conversions can make SQL behavior clearer and help prevent unexpected results caused by implicit data type conversion.

Question 117

Which SQL operation can update matching rows and insert nonmatching rows during a data integration process?

1. UNION
2. MERGE
3. DISTINCT
4. ORDER BY

Correct Answer: 2

Explanation

MERGE is designed to combine information from a source dataset into a target table according to a defined matching condition. Depending on the conditions specified, a MERGE statement can update rows that already exist in the target and insert rows that do not match. This makes MERGE useful for incremental data integration and synchronization workflows. Instead of performing separate operations for every possible matching scenario, users can express the required actions within a single statement. The exact result depends on the matching condition and the WHEN MATCHED or WHEN NOT MATCHED clauses included.

Question 118

Which Snowflake function can be used to retrieve information about errors from a previous COPY INTO load operation?

1. VALIDATE
2. VERIFY FILE
3. COPY VALIDATION
4. CHECK STAGE

Correct Answer: 1

Explanation

The VALIDATE function can be used to obtain information about errors generated during a previous COPY INTO operation. It is useful when administrators need to investigate why particular rows or files failed to load successfully. This helps identify problems such as malformed records, incorrect values, or other issues encountered during ingestion. VALIDATE is different from simply querying the destination table because its purpose is to inspect load-related error information. Using this capability can make troubleshooting more efficient and help data engineers identify problems before repeating or modifying a loading process.

Question 119

Which Snowflake feature is designed to continuously load newly arrived files from a cloud storage location?

1. Snowpipe
2. Time Travel
3. Resource Monitor
4. Search Optimization Service

Correct Answer: 1

Explanation

Snowpipe is designed for continuous or near-continuous ingestion of newly arrived files into Snowflake tables. It can automatically load files as they become available in a supported stage, reducing the need for users to repeatedly execute manual batch-loading commands. Snowpipe is particularly useful when data arrives throughout the day and should become available for analysis soon after ingestion. It differs from traditional batch loading, where users commonly execute COPY INTO operations on a scheduled or manual basis. Snowpipe therefore supports event-driven ingestion patterns for continuously arriving file-based data.

Question 120

Which notation is commonly used to navigate into nested data stored in a VARIANT column?

1. Colon and dot path notation
2. Hash notation only
3. Pipe notation
4. Equal-sign notation

Correct Answer: 1

Explanation

Snowflake supports path notation for accessing nested elements stored in semi-structured data types such as VARIANT. The colon operator can be used to navigate from a VARIANT column to an object attribute or array element, while dot notation can further reference nested object fields where appropriate. This allows users to query JSON-like structures without first converting every nested value into separate relational columns. Semi-structured path expressions are especially useful when working with JSON documents containing multiple levels of objects and arrays. They provide a practical way to combine relational SQL with semi-structured data.