COF-C02 Sample Questions Answers

Ensure all ACCOUNTADMIN roles use Multi-factor Authentication (MFA).

All users granted ACCOUNTADMIN role must be owned by the ACCOUNTADMIN role.

The ACCOUNTADMIN role must be granted to only one user.

Assign the ACCOUNTADMIN role to at least two users, but as few as possible.

All users granted ACCOUNTADMIN role must also be granted SECURITYADMIN role.

VIEWS_HISTORY

OBJECT_HISTORY

ACCESS_HISTORY

LOGIN_HISTORY

Shared-disk

Shared-nothing

Multi-cluster shared data

Serverless query engine

parse_json()

json_extract_path_text()

object_construct()

flatten

PURGE

MODIFY

REMOVE

DELETE

Standard

Enterprise

Business Critical

Virtual Private Snowflake

SYSADMIN

ORGADMIN

ACCOUNTADMIN

SECURITYADMIN

When it is unknown how much compute power is needed

If the select statement contains a large number of temporary tables or Common Table Expressions (CTEs)

If the runtime of the executed query is very slow

When a large number of concurrent queries are run on the same warehouse

A query that uses only disjunction (i.e., OR) predicates

A query that includes analytical expressions

A query that uses equality predicates or predicates that use IN

A query that filters on semi-structured data types

Data expires from Time Travel

Data expires from Fail-safe

Data is deleted from a table

Data is truncated from a table

The Pipe load history is reset to empty.

The REFRESH command is executed.

The stage will be purged.

The destination table is truncated.

Fail-safe makes data available up to 1 day, recoverable by user operations.

Fail-safe makes data available for 7 days, recoverable by user operations.

Fail-safe makes data available for 7 days, recoverable only by Snowflake Support.

Fail-safe makes data available up to 1 day, recoverable only by Snowflake Support.

Schema

View

Internal stage

Table

User

Role

Row level access policies

Data masking policies

Data encryption

Time Travel

External tokenization

Tri-Secret Secure

Time Travel

Fail-safe

Data encryption

External stages

Per second multiplied by an automatic sizing for the job

Per minute multiplied by an automatic sizing for the job, with a minimum of one minute

Per second multiplied by the size, as determined by the SERVERLESS_FEATURES_SIZE account parameter

Serverless features are not billed, unless the total cost for the month exceeds 10% of the warehouse credits, on the account

Protegrity

Tableau

DBeaver

SAP

Use Time Travel, as long as DATA_RETENTION_TIME_IN_DAYS was set to at least 3 days.

The transient table version cannot be retrieved after 24 hours.

Contact Snowflake Support to have the data retrieved from Fail-safe storage.

Use the FAIL_SAFE parameter for Time Travel to retrieve the data from Fail-safe storage.

Role

Stream

Warehouse

External table

User

Share

PUT

GET

COPY

SELECT

ORGADMIN

SECURITYADMIN

SHAREADMIN

ACCOUNTADMIN

select src:dealership from car_sales;

select src.dealership from car_sales;

select src:Dealership from car_sales;

select dealership from car_sales;

When the system has enough load for 2 minutes

When the system has enough load for 6 minutes

When the system has enough load for 8 minutes

When the system has enough load for 10 minutes

SINGLE=TRUE

MULTIPLE=TRUE

MULTIPLE=FALSE

SINGLE=FALSE

1 day

7 days

12 hours

0 days

COPY WAREHOUSE FROM <>;

SET WAREHOUSE = <>;

USE WAREHOUSE <>;

USE VIRTUAL_WAREHOUSE <>;

Within the policy DDL

Within the create table or create view DDL

By future APPLY for all objects in a schema

Within a control table

Using the command ALTER

SELECT

INSERT

UPDATE

DELETE

Create a view on the table.

Add a clustering key to the table

Add a limit clause to the query.

Add a Dynamic Data Masking policy to the table.

Snowsight

SnowCD

Snowpark

SnowSQL

DISABLE_MFA

EXT_AUTHN_DUO

MINS_TO_BYPASS_MFA

ALLOW_CLIENT_MFA_CACHING

A stage with a directory table set to automatically refresh will be created

A stage with a directory table that has metadata that must be manually refreshed will be created.

An error will be received stating that the storage location for the stage must be identified when creating a stage with a directory table.

The command will fail to run because the name of the directory table is not specified.

Query Profile

Query history

Visualization chart

Query acceleration service

History of the files that have been loaded into Snowflake

Names and owners of the roles that are currently enabled in the session

Query details such as the objects included and the user who executed the query

Details around the privileges that have been granted for all objects in an account

The stage definition

The table definition

The use of a copy into

Insufficient credit quota

Inefficient query pruning

Excessive query pruning

Exploding joins

Credit usage that exceeds a set threshold

PARSE_JSON

JSON_EXTRACT_PATH_TEXT

OBJECT_CONSTRUCT

TO_JSON

SECURTTYADMTN

ACCOUNTADMIN

SYSADMIN

ORGADMIK

Fail-safe

Time Travel

Cloning

Account replication

ENABLE_QUERY_ACCELERATTON

INITIALLY_SUSPENDED

RES0URCE_M0NTTOR

AUTO_RESUME

AUTO_SUSPEND

Poor query optimization

Expired temporary tables

Improper virtual warehouse sizing

Improper Common Table Expressions (CTEs)

Using a multi-cluster virtual warehouse

Using the query acceleration service

Using the search optimization service

Disabling auto-suspend

By creating an external integration

By configuring a security integration

By establishing IP allowed lists and IP blocked lists

By using SnowSQL to enable an external OAuth using the Snowflake protocol

1 second

60 seconds

5 minutes

60 minutes

Identifying queries that will likely run very slowly before executing them

Locating queries that consume a high amount of credits

Identifying logical issues with the queries

Identifying inefficient micro-partition pruning

Data spilling to a local or remote disk

Whether or not it is also a secure view

How often the underlying base table is queried

How often the base table changes

Whether the materialized view has a cluster key defined

How often the materialized view is queried

Increase the warehouse size from Medium to 2XL.

Increase the value for the parameter MAX_CONCURRENCY_LEVEL.

Configure the warehouse to a multi-cluster warehouse.

Lower the value of the parameter STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.

Tables, secure views, and secure UDFs can be shared

Shares can be shared

Data consumers can clone a new table from a share

Access to a share cannot be revoked once granted

Use data encryption.

Use dynamic data masking.

Use secure materialized views.

Revoke all roles for functional managers and end users.

ACCOUNTADMIN

ORGADMIN

SECURITYADMIN

SYSADMIN

USERADMIN

PUBLIC

ORGADMIN

SYSADMIN

history

config

snowsqLcnf

snowsql.pubkey

list @bronzestage;

select * from directory(@bronzestage);

select metadata$filename from @bronzestage;

select * from table(information_schema.stage_directory_file_registration_history(

stage name=>'bronzestage1));

Grant the OWNERSHIP privilege on the schema.

Grant the OWNERSHIP privilege on the database.

Grant the MANAGE GRANTS global privilege.

Grant ALL privileges on the schema.

Snowpark uses a Spark engine to generate optimized SQL query plans.

Snowpark automatically sets up Spark within Snowflake virtual warehouses.

Snowpark does not require that a separate cluster be running outside of Snowflake.

Snowpark allows users to run existing Spark code on virtual warehouses without the need to reconfigure the code.

Snowpark executes as much work as possible in the source databases for all operations including User-Defined Functions (UDFs).

Cross join

Inner join

Natural join

Outer join

GRANT ROLE MYROLE TO USER USER1;

GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO ROLE MYROLE;

GRANT ROLE MYROLE TO USER USER1;

GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER USER1;

GRANT ROLE MYROLE TO USER USER1;

GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB. K"-'SCHEMA TO USER! ;

GRANT ROLE MYROLE TO USER USER1;

GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO MYROLE;

The reader account will automatically use the Standard edition of Snowflake.

The reader account compute will be billed to the provider account.

The reader account can clone data the provider has shared, but cannot re-share it.

The reader account can create a copy of the shared data using CREATE TABLE AS...

Virtual Private Snowflake

Business Critical

Enterprise

Standard

Analyze query performance.

Write queries and execute them.

Provide an overview of the listings in the Snowflake Marketplace.

Connect to Snowflake partners to explore extended functionality.

Bytes spilled to local cache.

Bytes spilled to local storage.

Bytes spilled to remote cache.

Bytes spilled to remote storage.

Bytes spilled to remote metastore.

ROW_NUMBER

TABLE

TABULAR

VALUES

All columns in a micro-partition

Any columns accessed during the query

The columns in the result set of the query

All rows accessed during the query

Java

Javascript

Scala

Python

SQL

A logical grouping of objects that belongs to a single database

A logical grouping of objects that belongs to multiple databases

A named Snowflake object that includes all the information required to share a database

A uniquely identified Snowflake account within a business entity

src:salesperson.name

src:sa1esPerson. name

src:salesperson.Name

SRC:salesperson.name

SRC:salesperson.Name

A User-Defined Table Function (UDTF)

A Scalar User Function (UDF)

A stored procedure

A task

Use Snowpipe.

Use the INSERT command.

Use an external table.

Use the COPY command.

When unloading semi-structured data, it is recommended that the STRIP_OUTER_ARRAY option be used.

Use the ENCODING file format option to change the encoding from the default UTF-8.

The OBJECT_CONSTRUCT function can be used to convert relational data to semi-structured data.

By using the SINGLE = TRUE parameter, a single file up to 5 GB in size can be exported to the storage layer.

Use the PARSE_JSON function to ensure structured data will be unloaded into the VARIANT data type.

The clone always stays in sync with the original table.

The clone has better query performance.

The clone is created almost instantly.

The clone will have time travel history from the original table.

The clone saves space by not duplicating storage.

Reorder the rows in a relational table and then unload the rows into a file

Convert the rows in a relational table lo a single VARIANT column and then unload the rows into a file.

Reorder the data columns according to a target table definition and then unload the rows into the table.

Convert the rows in a source file to a single variant column and then load the rows from the file to a variant table.

Pre-signed URL

Scoped URL

Signed URL

File URL

Enterprise

Business Critical

Standard

Virtual Private Snowflake

Auto-scale mode

Maximized mode

Economy scaling policy

Standard scaling policy

CSV

JSON

ORC

Parquet

Scale up the virtual warehouse.

Scale out the virtual warehouse.

Enable standard warehouse scaling.

Enable economy warehouse scaling.

Query execution

Data loading

Time Travel data

Security

Authentication and access control

DISABLED

MINS TO BYPASS MFA

MINS TO BYPASS NETWORK POLICY

MINS TO UNLOCK

Standard

Enterprise

Business Critical

Virtual Private Snowflake

There is minimal cost associated with running the query.

The query consumes many compute resources every time it runs.

The base table gets updated frequently.

The query is highly optimized and does not consume many compute resources.

The results of the query do not change often and are used frequently.

Pipe

File format

Resource monitor

Storage integration

Virtual warehouse

Account

Database

Schema

Table

Virtual warehouse

When using a paid listing on the Snowflake Marketplace

When using a private listing on the Snowflake Marketplace

When using a personalized listing on the Snowflake Marketplace

When using a Direct Share with another account

Cloud services layer

Compute layer

Storage layer

Cloud agnostic layer

Caching query results

Running EXPLAIN and SHOW commands

Cloning a database

Running a custom query

Running COPY commands

The filtering or disregarding of micro-partitions that are not needed to return a query.

The return of micro-partitions values that overlap with each other to reduce a query's runtime.

A service that is handled by the Snowflake Cloud Services layer to optimize caching.

The ability to allow the result of a query to be accessed as if it were a table.

SHOW GRANTS ON ROLE DATA_SCIENCE

SHOW GRANTS ON SCHEMA ANALYTICS_DW.MARKETING

SHOW GRANTS TO USER INQUISITIVE_PERSON

SHOW GRANTS OF ROLE DATA_SCIENCE

Table definition

Stage definition

Session level

COPY INTO TABLE statement

8-16 MB

16-24 MB

10-99 MB

100-250 MB

Use auto-ingest Snowpipes to load large files in a serverless model.

Produce the largest files possible, reducing the overall number of files to process.

Produce a larger number of smaller files and process the ingestion with size Small virtual warehouses.

Use an external tool to issue batched row-by-row inserts within BEGIN TRANSACTION and COMMIT commands.

Add or remove users from a worksheet.

Move a worksheet to a folder or a dashboard.

Combine multiple worksheets into a single worksheet.

Tag frequently accessed worksheets for ease of access.

COPY INTO

GET

INSERT INTO

PUT

Disaster recovery

Time Travel

Fail-safe

Database failover and fallback

Data security

Grant the additional privileges to a custom role.

Grant the additional privileges to the ACCOUNTADMIN role.

Grant the additional privileges to the SYSADMIN role.

Grant the additional privileges to the ORGADMIN role.

ARRAY_AGG

OBJECT_AGG

ARRAY_CONSTRUCT

OBJECT_CONSTRUCT

ARRAY

CHARACTER

STRING

VARCHAR

VARIANT

UTF-8

UTF-16

ISO-8859-1

WINDOWS-1253

60

91

120

121

GRANT SEARCH OPTIMIZATION ON SCHEMA TO ROLE

GRANT SEARCH OPTIMIZATION ON DATABASE TO ROLE

GRANT ADD SEARCH OPTIMIZATION ON SCHEMA TO ROLE

GRANT ADD SEARCH OPTIMIZATION ON DATABASE TO ROLE

The provisioning of new compute resources for the warehouse will begin immediately.

The warehouse will remain suspended but new resources will be added to the query acceleration service.

The provisioning of additional compute resources will be in effect when the warehouse is next resumed.

The warehouse will resume immediately and start to share the compute load with other running virtual warehouses.

ARRAY

OBJECT

TEXT

VARIANT

Cloud services

Query processing

Elastic memory

Database storage

User stage

Table stage

Named internal stage

Named external stage

LIST

ALTER FILE FORMAT

DESCRIBE FILE FORMAT

SHOW FILE FORMATS

Secure view

Materialized view

Temporary table

Transient table

Snowflake will resolve them according to the SEARCH_PATH parameter.

Snowflake will only check the schema the UDF belongs to.

Snowflake will first check the current schema, and then the schema the previous query used

Snowflake will first check the current schema, and them the PUBLIC schema of the current database.

Worksheets can be grouped under folder, and a folder of folders.

Each worksheet is a unique Snowflake session.

Users are limited to running only one on a worksheet.

The Snowflake session ends when a user switches worksheets.

Users can import worksheets and share them with other users.

Data storage

Dynamic data masking

Partition scanning

User authentication

Infrastructure management

Binary format

Columnar format

Compressed format

Raw format

Zipped format

Standard

Enterprise

Business Critical

Virtual Private Snowflake (VPS)

FLATTEN

CHECK_JSON

PARSE_JSON

TO_VARIANT

GET_STATE_LOCATION

GET_PRESENT_URL

BUILD_SCOPED_FILE_URL

BUILD_STAGE_FILE_URL

ALTER ROLE

REVOKE ROLE

USE ROLE

USE SECONDARY ROLES

Those IP addresses are allowed access to the Snowflake account as Snowflake applies the allowed IP address list first.

Those IP addresses are denied access lei the Snowflake account as Snowflake applies the blocked IP address list first.

Snowflake issues an alert message and adds the duplicate IP address values lo both 'he allowed and blocked IP address lists.

Snowflake issues an error message and adds the duplicate IP address values to both the allowed and blocked IP address list

60 minutes

24 hours

14 days

30 days

The column name and element names are both case-sensitive.

The column name and element names are both case-insensitive.

The column name is case-sensitive but element names are case-insensitive.

The column name is case-insensitive but element names are case-sensitive.

Graphical representation of the data model

Statistics for each component of the processing plan

Detailed Information about I he database schema

Real-time monitoring of the database operations

SHOW GRANTS ON ROLE

SHOW ROLES

SHOW GRANTS TO ROLE

SHOW GRANTS FOR ROLE

Create an account for an organization.

Edit the account data for an organization.

Delete the account data for an organization.

View usage information for all accounts in an organization.

Select all the data in tables for all accounts in an organization.

Last_QUERY_ID

CURRENT_STATEMENT

UUID_STRING

JOB_ID

Temporary table

External table

Secure view

Materialized view

The base tables do not change frequently.

The results of the view change often.

The query is not resource intensive.

The query results are not used frequently.

1

5

10

100

1 minutes

5 minutes

10 minutes

15 minutes

BINARY

GEOGRAPHY

GEOMETRY

OBJECT

VARIANT

SHOW TABLES command

SHOW EXTERNAL TABLES command

TABLES view

TABLE STORAGE METRICS view

File URL

Scoped URL

Pre-signed URL

Scoped file URL

BINARY_FORMAT

ESCAPE_UNENCLOSED_FIELD

FIELD_OPTIONALLY_ENCLOSED_BY

SKIP BYTE ORDER MARK

The access to a single table

The access to data stored in stage objects

The list of values provided with the VALUES clause

The records generated using the TABLE (GENERATOR (...)) construct

Data classification

Query acceleration service

Search optimization service

Tagging

0 days

1 day

7 days

14 days

To store actual data from external stages

To automatically expire file URLs for security

To manage user privileges and access control

To store file-level metadata about data files in a stage

Using a larger virtual warehouse

Increasing the virtual warehouse maximum timeout limit

Increasing the amount of remote storage for the virtual warehouse

Using a common table expression (CTE) instead of a temporary table

Queries without filters or aggregation

Queries with large scans and selective filters

Queries where the GROUP BY has high cardinality

Queries of tables that have search optimization service enabled

A Query Profile can help users right-size virtual warehouses.

A Query Profile defines the hardware specifications of the virtual warehouse.

A Query Profile can help determine the number of virtual warehouses available.

A Query Profile automatically scales the virtual warehouse based on the query complexity.

CREATE STAGE

COPY INTO

Enable Time Travel.

Clone the dropped stage.

Execute the UNDROP command.

Recreate the dropped stage.

DATABASE_STORAGE_USAGE_HISTORY

TABLE_STORAGE_METRICS

STORAGE_DAILY_HISTORY

STAGE STORAGE USAGE HISTORY

Insert a colon (:) between the VARIANT column name and any first-level element.

Insert a colon (:) between the VARIANT column name and any second-level element. C. Insert a double colon (: :) between the VARIANT column name and any first-level element.

Insert a double colon (: :) between the VARIANT column name and any second-level element.

CREATE SESSION POLICY

CREATE NETWORK RULE

CREATE SECURITY INTEGRATION

CREATE PASSWORD POLICY

SHOW

MERGE

INSERT

DELETE

DESCRIBE

DATA_RETENTION_TIME_IN_DAYS

MAX_DATA_EXTENSION_TIME_IN_DAYS

MIN_DATA_RETENTION_TIME_IN_DAYS

MAX CONCURRENCY LEVEL

SQL functions like the GET command can be used to copy the unstructured data to a location on the client.

SQL functions can be used to create different types of URLs pointing to the unstructured data. These URLs can be used to download the data to a client.

SQL functions can be used to retrieve the data from the query results cache. When the query results are output to a client, the unstructured data will be output to the client as files.

SQL functions can call on different web extensions designed to display different types of files as a web page. The web extensions will allow the files to be downloaded to the client.

SELECT

OWNERSHIP

MONITOR

MODIFY

USAGE

Warehouses that are multi-cluster can have nodes of different sizes.

Warehouses can be started and stopped at any time.

Warehouses can be resized at any time, even while running.

Warehouses are billed on a per-minute usage basis.

Warehouses can only be used for querying and cannot be used for data loading.

Bytes scanned

Bytes written to result

Partitions scanned

Partitions total

Percentage scanned from cache

FLATTEN

GET_PATH

CHECK_JSON

PARSE JSON

ARRAY

BLOB

CLOB

JSON

VARIANT

Manually re-cluster the table regularly.

Choose one high cardinality column as the clustering key.

Use the column that is most-frequently used in query select clauses as the clustering key.

Assess the average table depth to identify how clustering is impacting the query.

File URL

Staged URL

Scoped URL

Pre-signed URL

The temporary table will take precedence over the permanent table.

The permanent table will take precedence over the temporary table.

An error will say there cannot be two tables with the same name in a schema.

The table that was created most recently will take precedence over the older table.

Use clustering keys

Create materialized views

Use the search optimization service

Use the query acceleration service

Temporary tables

Permanent tables

Transient tables

External tables

User-Defined Table Functions (UDTFs)

USERADMIN

ORGADMIN

SYSADMIN

SECURITYADMIN

FOR

LOOP

REPEAT

WHILE

It removes null values from all columns in the data.

It converts null values to empty strings during loading.

It skips rows with null values during the loading process.

It removes object or array elements containing null values.

Send an alert notification to all account users who have notifications enabled.

Send an alert notification to all virtual warehouse users when thresholds over 100% have been met.

Send a notification to all account administrators who have notifications enabled, and suspend all assigned warehouses after all statements being executed by the warehouses have completed.

Send a notification to all account administrators who have notifications enabled, and suspend all assigned warehouses immediately, canceling any statements being executed by the warehouses.

INSERT

JOIN

RENAME

ALTER

When creating a table

When loading data into a table

When unloading data from a table

When executing a show command

When executing a list command

Flatten the data and store it in structured data types in a flattened table. Query the table.

Store the data in a table with a variant data type. Query the table.

Store the data in a table with a vai : ant data type and include STRIP_NULL_VALUES while loading the table. Query the table.

Store the data in an external stage and create views on top of it. Query the views.

Data Replication

Secure Data Sharing

Data Exchange

Snowflake Marketplace

To count the number of characters in a string

To split a string into an array of sub-strings

To split a string and flatten the results into rows

To split a string and flatten the results into columns

Network policy

End-to-end encryption

Multi-Factor Authentication (MFA)

Role-Based Access Control (RBAC)

Object owner

Schema owner

Database owner

SYSADMIN

Materialized views do not allow joins.

Clones of materialized views can be created directly by the user.

Multiple tables can be joined in the underlying query of a materialized view.

Aggregate functions can be used as window functions in materialized views.