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

 

Question 221

What is the primary purpose of the AUTOCOMMIT parameter in Snowflake?

1. It controls warehouse size
2. It controls whether DML statements are automatically committed
3. It controls query result retention
4. It controls data sharing

Correct Answer: 2

Explanation

AUTOCOMMIT controls whether transactions are automatically committed after individual statements. When AUTOCOMMIT is enabled, statements that are eligible for transaction processing can be committed automatically rather than requiring an explicit COMMIT. When it is disabled, users can explicitly manage transactions with commands such as BEGIN, COMMIT, and ROLLBACK. This setting is relevant when applications need predictable transaction boundaries or when multiple DML operations must be treated as a unit. AUTOCOMMIT does not control warehouse capacity, query-result retention, or data-sharing behavior.

Question 222

Which SQL command explicitly begins a transaction in Snowflake?

1. BEGIN
2. CREATE
3. START WAREHOUSE
4. OPEN

Correct Answer: 1

Explanation

The BEGIN command explicitly starts a transaction in Snowflake. Once a transaction is started, supported SQL operations can be grouped together so that they can be committed or rolled back according to the application’s requirements. Explicit transaction control is useful when several related changes must be handled as one logical unit. COMMIT makes the transaction’s changes permanent, while ROLLBACK reverses changes that can be rolled back within the transaction. Commands such as CREATE and warehouse management statements serve different purposes and do not represent the standard command for beginning a transaction.

Question 223

Which command makes the changes in the current transaction permanent?

1. CANCEL
2. RESET
3. COMMIT
4. REVERT

Correct Answer: 3

Explanation

COMMIT makes the changes performed within the current transaction permanent. It is commonly used after a series of supported DML operations has completed successfully and the application or user is ready to finalize those changes. If the transaction should instead be abandoned, ROLLBACK can be used to reverse applicable uncommitted changes. Transaction control is important when multiple related modifications must be coordinated. COMMIT does not affect warehouse suspension, metadata retention, or query caching. Its purpose is specifically to finalize the current transaction’s changes.

Question 224

Which command is used to undo uncommitted changes in the current transaction?

1. DELETE
2. PURGE
3. RESET
4. ROLLBACK

Correct Answer: 4

Explanation

ROLLBACK is used to undo changes made during the current transaction that have not yet been committed. This is particularly useful when a series of related DML operations encounters an error or when a user decides that the changes should not be finalized. ROLLBACK provides transaction-level control rather than simply deleting rows affected by a particular statement. Once a transaction has been committed, ROLLBACK cannot generally be used to undo those committed changes. Applications can therefore use explicit transaction boundaries when consistency across multiple operations is important.

Question 225

A transaction contains several successful DML statements, but the final validation fails. Which action can discard the uncommitted changes?

1. ROLLBACK
2. COMMIT
3. COPY
4. GRANT

Correct Answer: 1

Explanation

ROLLBACK can discard applicable uncommitted changes made during the transaction. This is useful when multiple DML statements form one logical operation and a validation step determines that the entire operation should not be finalized. COMMIT would instead make the transaction permanent. COPY is primarily used for loading data from staged files, while GRANT manages privileges. Transaction control allows applications to avoid leaving partially completed changes when a later operation fails. The exact behavior also depends on the statements involved and Snowflake’s transaction semantics.

Question 226

What does SQL three-valued logic use to represent a comparison involving an unknown value such as NULL?

1. TRUE, FALSE, and UNKNOWN
2. TRUE and FALSE only
3. YES, NO, and MAYBE
4. MATCHED and UNMATCHED

Correct Answer: 1

Explanation

SQL uses three-valued logic, consisting of TRUE, FALSE, and UNKNOWN. Comparisons involving NULL commonly produce UNKNOWN because NULL represents an absence or unknown value rather than an ordinary value that can be compared directly. For example, comparing a column containing NULL with a specific value does not produce TRUE. This behavior affects WHERE conditions, logical operators, and filtering results. Understanding UNKNOWN is important because a WHERE clause returns rows only when its condition evaluates to TRUE. Users should therefore handle NULL explicitly when appropriate.

Question 227

Which condition should normally be used to test whether a column contains NULL?

1. column = NULL
2. column IS NULL
3. column == NULL
4. column EQUALS NULL

Correct Answer: 2

Explanation

The IS NULL condition is used to test whether an expression contains the SQL NULL value. A comparison such as column = NULL does not provide the intended test because NULL represents an unknown or missing value and ordinary equality comparisons with NULL evaluate to UNKNOWN. Similarly, SQL does not use == as the standard operator for this purpose. IS NULL and IS NOT NULL are specifically designed for NULL testing. Using these predicates correctly is essential when filtering records that contain missing or undefined values.

Question 228

What result does COALESCE return when its first expression is NULL and its second expression contains a value?

1. NULL
2. An error
3. The second expression’s value
4. The first expression’s data type only

Correct Answer: 3

Explanation

COALESCE returns the first non-NULL expression from its argument list. If the first expression evaluates to NULL and the second expression contains a value, COALESCE returns that second value. This makes the function useful for supplying fallback values when data is missing. Multiple expressions can be supplied, and Snowflake evaluates them to identify the first non-NULL result. COALESCE is different from simply comparing values because its primary purpose is handling NULL values and selecting an available alternative from several expressions.

Question 229

Which function returns NULL when an expression evaluates to NULL and otherwise returns the expression’s value?

1. NULLIF
2. NVL
3. IFF
4. ZEROIFNULL

Correct Answer: 2

Explanation

NVL returns the first expression when it is not NULL; otherwise, it returns the second expression. It is commonly used to substitute a fallback value for missing data. For example, a query can use NVL to replace a NULL numeric or text value with an alternative value appropriate for reporting. NULLIF has a different purpose because it returns NULL when two expressions are equal. IFF evaluates a Boolean condition, while ZEROIFNULL specifically substitutes zero for NULL numeric values. Understanding these functions helps users handle missing data correctly.

Question 230

Which Snowflake timestamp type stores both date and time but does not retain a time zone?

1. TIMESTAMP_TZ
2. TIMESTAMP_LTZ
3. TIMESTAMP_NTZ
4. DATE

Correct Answer: 3

Explanation

TIMESTAMP_NTZ represents a timestamp without time-zone information. It stores date and time values without retaining a time-zone offset as part of the value. TIMESTAMP_LTZ stores UTC internally and displays values according to the session time zone, while TIMESTAMP_TZ stores a timestamp together with an associated time-zone offset. DATE stores only a calendar date without a time component. Choosing the appropriate timestamp type depends on whether the application needs time-zone-aware behavior or simply needs to represent a date and time without time-zone information.

Question 231

Which Snowflake timestamp type uses the session time zone when displaying a stored timestamp?

1. TIMESTAMP_LTZ
2. TIMESTAMP_NTZ
3. DATE
4. TIME

Correct Answer: 1

Explanation

TIMESTAMP_LTZ represents a timestamp with local time-zone semantics. Snowflake stores the timestamp in UTC and displays it according to the current session time zone. Consequently, the same underlying instant can appear differently when viewed in sessions configured with different time zones. TIMESTAMP_NTZ does not use this local time-zone display behavior, while DATE contains no time-of-day information and TIME represents a time without a date. TIMESTAMP_LTZ is therefore useful when an application needs to represent an absolute point in time while presenting it according to session-specific time-zone settings.

Question 232

Which data type is appropriate for storing a calendar date without a time-of-day component?

1. TIME
2. DATE
3. TIMESTAMP_TZ
4. BINARY

Correct Answer: 2

Explanation

The DATE data type represents a calendar date without a time-of-day component. It is appropriate for values such as a customer’s birth date, an invoice date, or a reporting period date when hours, minutes, and seconds are unnecessary. TIME represents a time of day without a date, while timestamp types represent both date and time. BINARY is used for binary data rather than calendar values. Selecting DATE when time information is not needed can make data models clearer and prevent unnecessary timestamp handling.

Question 233

Which function can extract a specific date or time component, such as year or month, from a date or timestamp?

1. GET
2. EXTRACT
3. FLATTEN
4. PARSE_URL

Correct Answer: 2

Explanation

EXTRACT can retrieve a specified date or time component from a date, time, or timestamp expression. Components such as year, month, day, hour, and other supported fields can be extracted for analysis or transformation. This is useful when reports need to group or filter records based on individual temporal components. Functions such as FLATTEN are intended for semi-structured data, while PARSE_URL processes URL information. EXTRACT therefore provides a direct SQL mechanism for obtaining relevant calendar or time components from temporal values.

Question 234

Which COPY INTO option can force Snowflake to load files again even when they appear to have been previously loaded?

1. FORCE
2. RETRY
3. RELOAD
4. REPEAT

Correct Answer: 1

Explanation

The FORCE option can instruct COPY INTO to load files even when Snowflake’s load metadata indicates that the files have already been loaded. Normally, Snowflake tracks loaded files to help prevent unintended duplicate loading. FORCE can be useful when a file needs to be deliberately reprocessed, but users should understand that reloading a file can create duplicate rows depending on the target table and transformation logic. Therefore, FORCE should be used carefully and only when intentional reprocessing is required.

Question 235

Which COPY INTO option allows files to be removed from a stage after successful loading?

1. PATTERN
2. FILES
3. PURGE
4. FORCE

Correct Answer: 3

Explanation

The PURGE option can remove successfully loaded data files from the stage after COPY INTO processing. This can help manage staged-file storage when source files no longer need to remain available there. PURGE should be used carefully because removing staged files can affect later workflows that depend on those files. PATTERN is used to select files according to a regular expression, FILES specifies particular files to load, and FORCE can cause previously loaded files to be processed again. Each option therefore serves a different loading requirement.

Question 236

Which COPY INTO option can restrict loading to staged files whose names match a regular expression?

1. FILES
2. PATTERN
3. PURGE
4. FORCE

Correct Answer: 2

Explanation

PATTERN allows COPY INTO to select staged files whose names match a specified regular expression. This is useful when a stage contains many files but only a subset should be loaded during a particular operation. For example, an ingestion process can use a pattern to select files with a particular naming convention or extension. FILES can explicitly identify files, while PURGE controls removal after successful loading and FORCE controls whether previously loaded files may be loaded again. PATTERN is therefore the appropriate option for regular-expression-based file selection.

Question 237

Which COPY INTO option can explicitly identify a list of files that should be loaded?

1. FILES
2. PATTERN
3. PURGE
4. MATCH_BY_COLUMN_NAME

Correct Answer: 1

Explanation

The FILES option in COPY INTO can specify particular staged files for loading. This provides direct file selection when the user knows which files should be processed rather than wanting to select files through a regular expression. PATTERN provides regular-expression-based selection, while PURGE controls removal of successfully loaded files. MATCH_BY_COLUMN_NAME is related to matching columns from files to table columns rather than identifying which files to process. Using FILES can therefore provide precise control over the source files included in a specific load operation.

Question 238

Which COPY INTO option can match columns from staged data to table columns by column name?

1. ON_ERROR
2. PURGE
3. MATCH_BY_COLUMN_NAME
4. SKIP_HEADER

Correct Answer: 3

Explanation

MATCH_BY_COLUMN_NAME allows COPY INTO to match columns in staged files with columns in the target table by their names rather than relying solely on positional ordering. This can be useful when incoming files have columns arranged differently from the target table or when ingestion workflows need name-based alignment. ON_ERROR controls how loading errors are handled, PURGE concerns removal of successfully loaded files, and SKIP_HEADER controls how many initial rows are skipped in supported file formats. MATCH_BY_COLUMN_NAME therefore addresses column-to-column mapping during data loading.

Question 239

Which Snowflake object is designed to represent a query that automatically refreshes based on changes in its underlying data?

1. Dynamic table
2. File format
3. Resource monitor
4. External stage

Correct Answer: 1

Explanation

A dynamic table is designed to maintain the results of a specified query and automatically refresh those results toward a defined target lag. It can simplify data transformation pipelines by allowing users to define the desired result rather than manually coordinating every transformation step. Dynamic tables are distinct from standard tables because their contents are maintained through the defined query. They are also different from streams, which track changes, and tasks, which schedule SQL execution. Dynamic tables can therefore support declarative data transformation and pipeline workflows.

Question 240

What does a dynamic table’s target lag primarily describe?

1. The maximum warehouse size
2. The desired freshness of the dynamic table’s data
3. The number of columns allowed
4. The retention period for deleted files

Correct Answer: 4

Explanation

A dynamic table’s target lag describes the desired freshness of its data relative to changes in the underlying data. Snowflake uses this target to manage refresh behavior so that the dynamic table can remain within the requested freshness objective, subject to system conditions and available resources. Target lag is therefore related to data freshness rather than warehouse sizing, table column limits, or staged-file retention. This concept is important when designing pipelines where downstream users need transformed data to remain reasonably current without manually scheduling each individual refresh operation.