COF-C02 SnowPro Core Certification Exam Question and Answers

No access

Read access

Write access

Full access

Use SECONDARY ROLES RESUME

USE SECONDARY ROLES SUSPEND

USE SECONDARY RLES ALL

USE SECONDARY ROLES ADD

Use SECONDARY ROLES NONE

It prunes unnecessary micro-partitions based on clustering metadata.

It compresses the data within micro-partitions for faster querying.

It automatically allocates additional resources to improve query execution.

It organizes clustering information to speed-up data retrieval from storage

The type of SQL statements executed

The number of tables or databases queried

The amount of data stored in the warehouse

The length of time the compute resources in each cluster run

Private listing

Secure view

Sequence

Share

Clone the table schema as it existed before the table was dropped.

Clone the database as it existed before the table was dropped.

Recreate the table and reload the data.

Run the UNDROP command against the table.

BUILD_STAGE_FILE_URL

BUILD_SCOPED_FILE_URL

GET_PRESIGNED_URL

GET STAGE LOCATION

Data backups

Cost monitoring

Compliance auditing

Performance optimization

DELETE

REMOVE

TRUNCATE

DROP

Directory table

Temporary table

Transient table

A table with a materialized view

Workloads with a predictable data volume for each query

Workloads that include on-demand data analyses

Queries with small scans and non-selective filters

Queries that do not have filters or aggregation

OWNERSHIP

MODIFY

CREATE

USAGE

Area chart

Box plot

Heat grid

Pie chart

1 Kilobyte (KB)

1 Megabyte (MB)

1 Gigabyte (GB)

1 Terabyte (TB)

ACCOUNTADMIN

SECURITYADMIN

SYSADMIN

USERADMIN

Bytes scanned

Partitions scanned

Bytes spilled to local storage

Bytes spilled to remote storage

Percentage scanned from cache

COPY INTO

GET

LIST

PUT

REMOVE

Data:employee.name

DATA:employee.name

data:Employee.name

data:employee.name

TABLE

FLATTEN

PARSE_JSON

JSON EXTRACT PATH TEXT

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

Use clustering keys

Create materialized views

Use the search optimization service

Use the query acceleration service

USAGE_VIEWER

GOVERNANCE_VIEWER

OBJECT_VIEWER

SECURITY_VIEWER

INTEGER

OBJECT

VARIANT

VARCHAR

LIST

Network policy

End-to-end encryption

Multi-Factor Authentication (MFA)

Role-Based Access Control (RBAC)

OAuth

Network policies

Single Sign-On (SSO)

Multi-Factor Authentication (MFA)

Review the granted privileges to the database object.

Review the row access policy for the database object.

Query the ACCESS_HlSTORY view in the ACCOUNT_USAGE schema.

Query the REPLICATION USAGE HISTORY view in the ORGANIZATION USAGE schema.

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.

Load the data into a CSV file.

Load the data into a relational table.

Use the VALlDATlON_MODE - SQL statement.

Use the validation mode = return rows statement.

Tag

Task

Stream

Stored procedure

Standard Edition

Enterprise Edition

Business Critical Edition

Virtual Private Snowflake Edition

Tags

Comments

Internal tokenization

Row access policies

SHOW

MERGE

INSERT

DELETE

DESCRIBE

A single data type, with one or more conditions, and one or more masking functions

A single data type, with only one condition, and only one masking function

Multiple data types, with only one condition, and one or more masking functions

Multiple data types, with one or more conditions, and one or more masking functions

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.

Automatic clustering

Materialized views

Query acceleration service

Search optimization service

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

GET_PRESIGNED_URL

GET_ABSOLUTE_PATH

BUILD_STAGE_FILE_URL

BUILD SCOPED FILE URL

Bytes scanned

Bytes written to result

Partitions scanned

Partitions total

Percentage scanned from cache

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

CREATE STAGE

COPY INTO

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.

CREATE TABLE LIKE TABLE_A;

CREATE VIEW AS SELECT * FROM TABLE_A;

CREATE TABLE AS SELECT * FROM TABLE_A;

CREATE MATERIALIZED VIEW AS SELECT * FROM TABLE A;

ACCESS_HISTORY

COPY_HISTORY

QUERY_HISTORY

ROW ACCESS POLICIES

View

Materialized view

External table

User-Defined Function (UDF)

INSERT

JOIN

RENAME

ALTER

The account type

The storage file format

The cloud region used by the account

The type of data being stored

The cloud platform being used

TO_OBJECT

TO_JSON

TO_VARIANT

OBJECT CONSTRUCT

Column compression

Data clustering

Micro-partitioning

Metadata management

FLATTEN

GET_PATH

CHECK_JSON

PARSE JSON

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.

ARRAY

BLOB

CLOB

JSON

VARIANT

Security

Data storage

Data visualization

Query computation

Metadata management

B-tree

Indexing

Map reduce

Pruning

list @bronzestage;

select * from directory(@bronzestage);

select metadata$filename from @bronzestage;

select * from table(information_schema.stage_directory_file_registration_history(

stage name=>'bronzestage1));

ARRAY

OBJECT

VARCHAR

VARIANT

Schema Stage

User Stage

Database Stage

Table Stage

External Named Stage

Internal Named Stage

USERADMIN

PUBLIC

ORGADMIN

SYSADMIN

1 GB

5 GB

10 GB

16 GB

ALTER FILE FORMAT my_format SET TYPE=JSON;

ALTER FILE FORMAT my format SWAP TYPE WITH JSON;

CREATE OR REPLACE FILE FORMAT my format TYPE-JSON;

REPLACE FILE FORMAT my format TYPE-JSON;

@

&

$

#

A VARIANT column

A column with very low cardinality

A column with very high cardinality

A column that is most actively used in selective filters

A column that is most actively used in join predicates

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;

Swift

JavaScript

Python

SQL

It can provide statistics on a maximum number of 100 queries per week.

It provides a graphic representation of the main components of the query processing.

It provides detailed statistics about which queries are using the greatest number of compute resources.

It can be used by third-party software using the Query Profile API.

USAGE

OPERATE

MONITOR

MODIFY

JSON

BINARY

VARCHAR

VARIANT

SnowCD

Snowpark

Snowsight

SnowSQL

7 days

14 days

28 days

365 days

1

2

3

4

Include the SQL statements in the body of the task CREATE TASK mytask .. AS INSERT INTO target1 SELECT .. FROM stream_s1 WHERE .. INSERT INTO target2 SELECT .. FROM stream_s1

WHERE ..

A stored procedure can have only one DML statement per stored procedure invocation and therefore the user should sequence stored procedure calls in the task definition CREATE TASK mytask .... AS

call stored_proc1(); call stored_proc2();

Use a stored procedure executing multiple SQL statements and invoke the stored procedure from the task. CREATE TASK mytask .... AS call stored_proc_multiple_statements_inside();

Create a task for each SQL statement (e.g. resulting in task1, task2, etc.) and string the series of SQL statements by having a control task calling task1, task2, etc. sequentially.

Directory

File

Pre-signed

Scoped

SnowCLI

SnowUI

SnowSQL

SnowCD

Scale up the existing warehouse.

Create separate warehouses for each user.

Create separate warehouses for each workload.

Stop loading and querying data at the same time.

Scale up the virtual warehouse.

Scale out the virtual warehouse.

Enable standard warehouse scaling.

Enable economy warehouse scaling.

When the data cache clears.

When the persisted query result period ends.

The encoded URL access is permanent.

The length of time is specified in the expiration_time argument.

Roles

Databases

Warehouses

Users

Accounts

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...

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

Pipe

File format

Resource monitor

Storage integration

Virtual warehouse

The data from the permanent table.

The data from the temporary table.

An error stating that the view could not be compiled.

An error stating that the referenced object could not be uniquely identified.

Create a view.

Cluster a table.

Enable the search optimization service.

Enable Time Travel.

Index a table.

Cross join

Inner join

Natural join

Outer join

Infrastructure management

Metadata management

Query execution

Query parsing and optimization

Permanent storage of micro-partitions

Configure the user to be initially disabled.

Force an immediate password change.

Set a default role for the user.

Set the number of minutes to unlock to 15 minutes.

Set the user's access to expire within a specified timeframe.

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.

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.

If a role was specified as part of the connection and that role has been granted to the Snowflake user, the specified role becomes the current role.

If no role was specified as part of the connection and a default role has been defined for the Snowflake user, that role becomes the current role.

If no role was specified as part of the connection and a default role has not been set for the Snowflake user, the session will not be initiated and the log in will fail.

If a role was specified as part of the connection and that role has not been granted to the Snowflake user, it will be ignored and the default role will become the current role.

If a role was specified as part of the connection and that role has not been granted to the Snowflake user, the role is automatically granted and it becomes the current role.

BINARY_FORMAT

ESCAPE_UNENCLOSED_FIELD

FIELD_OPTIONALLY_ENCLOSED_BY

SKIP BYTE ORDER MARK

MODIFY

READ

SELECT

USAGE

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.

Standard

Enterprise

Business Critical

Virtual Private Snowflake (VPS)

Last_QUERY_ID

CURRENT_STATEMENT

UUID_STRING

JOB_ID

A single-cluster shared data architecture using a central data repository and massively parallel processing (MPP)

A multi-duster shared nothing architecture using a soloed data repository and massively parallel processing (MPP)

A single-cluster shared nothing architecture using a sliced data repository and symmetric multiprocessing (SMP)

A multi-cluster shared nothing architecture using a siloed data repository and symmetric multiprocessing (SMP)

ACCOUNTADMIN

SECURITYADMIN

SYSADMIN

ORGADMIN

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

Reader account users cannot add new data to the account.

Reader account users can share data to other reader accounts.

A single reader account can consume data from multiple provider accounts.

Data consumers are responsible for reader account setup and data usage costs.

Reader accounts enable data consumers to access and query data shared by the provider.

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.

Clone the data from the production account to the non-production account.

Create a data share from the production account to the non-production account.

Create a subscription in the production account and have it publish to the non-production account.

Create a reader account using the production account and link the reader account to the non-production account.

Disjuncts (OR) in join predicates

LIKE/ILIKE/RLIKE join predicates

Join predicates on VARIANT columns

Conjunctions (AND) of multiple equality predicates

TABLES

TABLE_STORAGE_METRICS

STORAGE_DAILY_HISTORY

STORAGE USAGE

ALTER ROLE

REVOKE ROLE

USE ROLE

USE SECONDARY ROLES

ARRAY_AGG

OBJECT_AGG

ARRAY_CONSTRUCT

OBJECT_CONSTRUCT

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.

Use the SECURITYADMIN role.

Use the SYSADMIN role.

Use the USERADMIN role.

Contact Snowflake Support.

(12,2)

(10,4)

(14,8)

(15,9)

Data storage

Dynamic data masking

Partition scanning

User authentication

Infrastructure management

File URL

Scoped URL

Pre-signed URL

Scoped file URL

Data classification

Query acceleration service

Search optimization service

Tagging

1 hour

2 hours

4 hours

8 hours

TEXT

BINARY

STRING

VARIANT

0 days

1 day

7 days

14 days

GROUP BY

LIMIT

ORDER BY

PARTITION BY

UNION ALL

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

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

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.

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.

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.

FLATTEN

CHECK_JSON

PARSE_JSON

TO_VARIANT

UTF-8

UTF-16

ISO-8859-1

WINDOWS-1253

1 minutes

5 minutes

10 minutes

15 minutes

Standard

Enterprise

Business Critical

Virtual Private Snowflake

Materialized view

Sequence

Secure view

Transient table

Clustered table

60

91

120

121

Privileges on the database

Existing child objects within the database

Future child objects within the database

Privileges on the schemas within the database

Only schemas and tables within the database

1 day

7 days

12 hours

0 days

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

XML

JSON

Parquet

ORC

AVRO

CSV

10-50 MB

100-250 MB

300-500 MB

1000-1500 MB

SINGLE=TRUE

MULTIPLE=TRUE

MULTIPLE=FALSE

SINGLE=FALSE

4 minutes

10 minutes

14 minutes

24 minutes

Auto-resume applies only to the last warehouse that was started in a multi-cluster warehouse.

The ability to auto-suspend a warehouse is only available in the Enterprise edition or above.

SnowSQL supports both a configuration file and a command line option for specifying a default warehouse.

A user cannot specify a default warehouse when using the ODBC driver.

The default virtual warehouse size can be changed at any time.

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.

Use data encryption.

Use dynamic data masking.

Use secure materialized views.

Revoke all roles for functional managers and end users.

There is no Fail-safe period for these tables.

1 day

7 days

31 days

90 days

Optimizes the virtual warehouse size and multi-cluster setting to economy mode

Allows a user to import the files in a sequential order

Increases the latency staging and accuracy when loading the data

Allows optimization of parallel operations

True

False

Clustering

Materialized Views

Zero-copy Cloning

Search Optimization Service

There is no need to have a Snowflake account in the target region, a share will be created for each user.

The listing is replicated into all selected regions automatically, the data is not.

The user must have the ORGADMIN role available in at least one account to link accounts for replication.

Shares attached to listings in remote regions can be viewed from any account in an organization.

For a standard listing the user can wait until the first customer requests the data before replicating it to the target region.

Tri-Secret Secure

Time Travel

Fail-safe

Data encryption

External stages

Compute resources

Query execution

Authentication

Data storage

Metadata

1 second

60 seconds

5 minutes

60 minutes

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.

SELECT SUM (ORDER_AMT) FROM SALES;

SELECT AVG(ORDER_QTY) FROM SALES;

SELECT MIN(ORDER_AMT) FROM SALES;

SELECT ORDER_AMT * ORDER_QTY FROM SALES;

PURGE

MODIFY

REMOVE

DELETE

The cloned views and the stored procedures will reference the cloned tables in the cloned database.

An error will occur, as views with qualified references cannot be cloned.

An error will occur, as stored objects cannot be cloned.

The stored procedures and views will refer to tables in the source database.

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

SnowSQL

Snowflake classic web interface (Ul)

Snowsight

.NET driver

A read-only copy of the cloned tables is stored.

The replication will not be successful.

The physical data is replicated

Additional costs for storage are charged to a secondary account

Metadata pointers to cloned tables are replicated

STATEMENT_TIMEOUT__IN__SECONDS

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

MAX_CONCURRENCY__LEVEL

QUERY_TIMEOUT_IN_SECONDS

The load HISTORY view

The Query history view

The show warehouses command

The WAREHOUSE_METERING__HISTORY View

The billing and usage tab in the Snowflake web Ul

Querying data-related objects that were created within the past 365 days

Restoring data-related objects that have been deleted within the past 90 days

Conducting point-in-time analysis for Bl reporting

Analyzing data usage/manipulation over all periods of time

Pipes

Streams

Tasks

Procedures

Credits are billed per-minute with a 60-minute minimum

Credits are used to pay for cloud data storage usage

Credits are consumed based on the number of credits billed for each hour that a warehouse runs

Credits are consumed based on the warehouse size and the time the warehouse is running

When a column is added to the base table

When a column is dropped from the base table

When a DML operation is run on the base table

When the base table is reclustered

Tables

User-Defined Table Functions (UDTFs)

Secure views

Stored procedures

Worksheets

Set the WAREHOUSES_SIZE to 6XL.

Set the STATEMENT_TIMEOUT_1M_SECOMES to 0.

Set the MAX_CONCURRENCY_LEVEL to a value of 12 or large.

Set the same value for both MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT.

PARSE_JSON

JSON_EXTRACT_PATH_TEXT

OBJECT_CONSTRUCT

TO_JSON

COPY INTO

Select LAST_QUERY_ID(-2)

Select LAST_QUERY_ID(2)

Select LAST_QUERY_ID(1)

Select LAST_QUERY_ID(2)