Free Practice Questions for Snowflake COF-C02 Exam

The COPY INTO < location > command is used to unload data from Snowflake tables to external locations , typically cloud storage or a Snowflake stage. Supported destinations include:

External stages : e.g., S3, Azure Blob Storage, Google Cloud Storage

Internal named stages : Snowflake-managed storage

Local file systems are not supported as unload targets.

Details around the privileges that have been granted for all objects in an account. TheACCESS_HISTORYview in Snowflake provides a comprehensive log of access control changes, including grants and revocations of privileges on all securable objects within the account. This information is crucial for auditing and monitoring the security posture of your Snowflake environment.

Here ' s how to understand and use theACCESS_HISTORYview:

Purpose of ACCESS_HISTORY View:It is designed to track changes in access controls, such as when a user or role is granted or revoked privileges on various Snowflake objects. This includes tables, schemas, databases, and more.

Querying ACCESS_HISTORY:To access this view, you can use the following SQL query pattern:

SELECT*FROMSNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORYWHEREEVENT_TYPE = ' GRANT ' OREVENT_TYPE = ' REVOKE ' ;

Interpreting the Results:The results from theACCESS_HISTORYview include the object type, the specific privilege granted or revoked, the grantee (who received or lost the privilege), and the timestamp of the event. This data is invaluable for audits and compliance checks.

The correct answer is B. Files that have already been successfully loaded into the table will be loaded again .

By default, Snowflake uses load metadata to prevent the same files from being loaded into a table more than once. Setting FORCE = TRUE overrides this behavior and reloads files even if Snowflake load metadata indicates that the files were already successfully loaded.

Why B is correct:

FORCE = TRUE tells Snowflake to ignore previous load history and load files again.

Why the other options are incorrect:

A. FORCE = TRUE is not specifically about files with unknown load status.

C. This describes the default behavior when FORCE = FALSE, not when FORCE = TRUE.

D. Loading strings longer than the target column length is controlled by other loading behavior and error handling, not by FORCE.

Official Snowflake documentation reference:

Snowflake documentation for COPY INTO < table > describes FORCE as an option that loads all files, regardless of whether they have been loaded previously.

When deciding to use a secure view, several considerations come into play, especially concerning security and performance:

A.No details of the query execution plan will be available in the query profiler: Secure views are designed to prevent the exposure of the underlying data and the view definition to unauthorized users. Because of this, the detailed execution plans for queries against secure views are not available in the query profiler. This is intended to protect sensitive data from being inferred through the execution plan.

C.Secure views do not take advantage of the same internal optimizations as standard views: Secure views, by their nature, limit some of the optimizations that can be applied compared to standard views. This is because they enforce row-level security and mask data, which can introduce additional processing overhead and limit the optimizer ' s ability to apply certain efficiencies that are available to standard views.

B.Once created, there is no way to determine if a view is secure or notis incorrect because metadata about whether a view is secure can be retrieved from the INFORMATION_SCHEMA views or by using theSHOW VIEWScommand.

D.It is not possible to create secure materialized viewsis incorrect because the limitation is not on the security of the view but on the fact that Snowflake currently does not support materialized views with the same dynamic data masking and row-level security features as secure views.

E.The view definition of a secure view is still visible to users by way of the information schemais incorrect because secure views specifically hide the view definition from users who do not have the privilege to view it, ensuring that sensitive information in the definition is not exposed.

When using theCOPY INTO < table > command to load CSV data from a stage into a Snowflake table, one of the crucial guidelines to follow is that the number of columns in each row within the CSV file should be consistent. This ensures data integrity and allows for a smooth data loading process, as Snowflake expects each row in the CSV file to map directly to a row in the target table based on the number of columns. If there ' s a mismatch in the number of columns between any row in the file and the target table, Snowflake might return an error or produce unexpected results during the load operation.

It ' s important to note that while the CSV field delimiter can be specified to something other than a comma, ensuring the consistency in the number of columns across all rows is fundamental to successfully loading data.

The correct answer is B. Loading and unloading data .

SnowSQL is Snowflake’s command-line client. It is used to connect to Snowflake, execute SQL statements, run SQL scripts, and perform data loading and unloading operations.

Why B is correct:

SnowSQL supports data loading and unloading workflows. For example, users can use SnowSQL to run PUT, GET, and COPY commands.

Example loading workflow:

PUT file:///local/path/data.csv @my_stage;

COPY INTO my_table

FROM @my_stage

FILE_FORMAT = (TYPE = CSV);

Example unloading workflow:

COPY INTO @my_stage/output/

FROM my_table;

GET @my_stage/output/ file:///local/path/output/;

Why the other options are incorrect:

A. Dashboards are usually developed in BI tools or applications, not SnowSQL.

C. Snowflake Native Apps are developed and managed using Snowflake Native App Framework tools and Snowflake CLI, not primarily SnowSQL.

D. Monitoring transformation activity is usually done through Snowflake views, query history, task history, event tables, or monitoring features, not as the primary purpose of SnowSQL.

Official Snowflake documentation reference:

Snowflake documentation describes SnowSQL as the command-line client for connecting to Snowflake and executing SQL queries, including data loading and unloading commands.

The correct answer is A. A staged file needs to be unloaded to a local machine .

The Snowflake GET command is used to download files from an internal Snowflake stage to a local file system. It is commonly used after data has been unloaded from a table into an internal stage and the user wants to retrieve those staged files locally.

Why A is correct:

The GET command downloads files from a Snowflake internal stage to a local directory.

Example:

GET @my_stage file:///tmp/data/;

This command downloads files from the internal stage @my_stage to the local /tmp/data/ directory.

Why the other options are incorrect:

B. Loading a local file onto a stage is done with the PUT command, not GET.

C. Opening query results in a third-party tool is not the purpose of the GET command.

D. Permanently deleting files from a Snowflake stage is done with the REMOVE command, not GET.

Official Snowflake documentation reference:

Snowflake documentation describes the GET command as the command used to download data files from a Snowflake stage to a local directory or folder.

In Snowflake, theDOUBLEdata type is used for floating-point numbers and can represent a wide range of values without losing precision. This data type is ideal for storing numerical values that require decimal precision.

Define the Column:When creating a table, specify the column with theDOUBLEdata type to store floating-point numbers.

CREATE TABLE example_table (

id INTEGER,

value DOUBLE

);

Insert Data:Insert floating-point numbers into theDOUBLEcolumn.

INSERT INTO example_table (id, value) VALUES (1, 123.456);

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.

Session policies allow account administrators to define rules such as session timeout , requiring users to re-authenticate after inactivity . This is different from password or network policies, which manage login credentials and IP filtering.

Snowflake primarily uses RBAC , where access is granted to roles, and roles are assigned to users. Snowflake also supports aspects of DAC , which allows privileges to be granted by users who have GRANT OPTION.

Snowflake allows users to write custom application logic in JavaScript for two types of objects: Stored Procedures and User-Defined Functions (UDFs).

Stored Procedures:Snowflake stored procedures can be written in JavaScript to encapsulate complex business logic and procedural operations.

CREATE OR REPLACE PROCEDURE my_procedure()

RETURNS STRING

LANGUAGE JAVASCRIPT

EXECUTE AS CALLER

AS

$$

// JavaScript logic here

$$;

User-Defined Functions (UDFs):Snowflake UDFs can be written in JavaScript to perform custom calculations or operations on data.

CREATE OR REPLACE FUNCTION my_function(x FLOAT)

RETURNS FLOAT

LANGUAGE JAVASCRIPT

AS

$$

return x * 2;

$$;

The correct answer is C. Queries will continue running, using the current warehouse size .

When a virtual warehouse is resized while it is running, the resize does not affect queries that are already in progress. Running queries continue using the warehouse resources that were available when they started. The new warehouse size applies to queries that start after the resize operation.

Why C is correct:

Snowflake does not cancel or restart existing queries when a warehouse is resized. In-progress queries continue executing with the current warehouse size.

Why the other options are incorrect:

A. Resizing a warehouse does not automatically cancel in-progress queries.

B. In-progress queries do not switch to the new warehouse size.

D. Queries are not paused and reprocessed after resizing.

Official Snowflake documentation reference:

Snowflake documentation explains that resizing a running warehouse affects only new queries and queued queries. Currently running queries continue to use the warehouse size that was in effect when they started.

Users must self-enroll for MFA by logging into the web UI and scanning a QR code with an authenticator app. Administrators cannot do this on their behalf.

TheRESULT_SCANtable function is used in Snowflake to perform additional processing on the results of a previously-run query. It allows users to reference the result set of a previous query by its query ID, enabling further analysis or transformations without re-executing the original query.

Snowflake Documentation: RESULT_SCAN