COF-C02 SnowPro Core Certification Exam Questions and Answers

Create a task that runs a copy into operation from an external stage every 5 minutes

Create a task that puts the files in an internal stage and automate the data loading wizard

Create a task that runs a GET operation to intermittently check for new files

Set up cloud provider notifications on the Tile location and use Snowpipe with auto-ingest

Bytes scanned

Bytes written to result

Partitions scanned

Partitions total

Percentage scanned from cache

Tags

Comments

Internal tokenization

Row access policies

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.

FLATTEN

GET_PATH

CHECK_JSON

PARSE JSON

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.

FOR

LOOP

REPEAT

WHILE

File URL

Scoped URL

Relative URL

Pre-Signed URL

Data Replication

Secure Data Sharing

Data Exchange

Snowflake Marketplace

DATA_RETENTION_TIME_IN_DAYS

MAX_DATA_EXTENSION_TIME_IN_DAYS

MIN_DATA_RETENTION_TIME_IN_DAYS

MAX CONCURRENCY LEVEL

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)

SHOW

MERGE

INSERT

DELETE

DESCRIBE

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;

Add and remove members.

Delete data categories.

Approve and deny listing approval requests.

Transfer listing ownership.

Transfer ownership of a provider profile.

Data:employee.name

DATA:employee.name

data:Employee.name

data:employee.name

Enable Time Travel.

Clone the dropped stage.

Execute the UNDROP command.

Recreate the dropped stage.

Use COPY INTO followed by a GET command to download the file.

Use COPY INTO followed by a put command to download the file.

Use COPY INTO with SINGLE = TRUE followed by a GET command to download the file.

Use COPY INTO with SINGLE = TRUE followed by a PUT command to download the file.

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.

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.

INSERT

JOIN

RENAME

ALTER

The database owner

The object owner

The schema owner

The Snowflake user's role

A user with an USERADMIN or higher role

A user with a SECURITYADMIN or higher role

A role that has been granted the ATTACH POLICY privilege

A role that has the NETWORK_POLlCY account parameter set

A role that has the ownership of the network policy

SELECT

OWNERSHIP

MONITOR

MODIFY

USAGE

ACCESS_HISTORY

DATA_TRANSFER_HISTORY

WAREHOUSE_METERING_HISTORY

MONETIZED_USAGE_DAILY

LISTING TELEMETRY DAILY

Object owner

Schema owner

Database owner

SYSADMIN

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

TO_CHAR

TO_VARIANT

TO_VARCHAR

STRIP NULL VALUE

Automatic clustering

Materialized views

Query acceleration service

Search optimization service

The user will be unable to enter a password.

The user will encounter an error, and will not be able to log in.

The user will be able to log into Snowflake successfully.

After entering the username and password, the user will be redirected to an Identity Provider (IdP) login page.

1 hour

2 hours

4 hours

8 hours

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.

USAGE_VIEWER

GOVERNANCE_VIEWER

OBJECT_VIEWER

SECURITY_VIEWER

OAuth

Network policies

Single Sign-On (SSO)

Multi-Factor Authentication (MFA)

Bytes scanned

Partitions scanned

Bytes spilled to local storage

Bytes spilled to remote storage

Percentage scanned from cache

Column compression

Data clustering

Micro-partitioning

Metadata management

Operator tree

Table pruning

Most expensive nodes

Nodes by execution time

A user with a role that has been granted the global ATTACH POLICY privilege

A user with a role that has been granted the create session policy privilege

A user with a role that has been granted the CREATE NETWORK POLICY privilege

A user with a role that has been granted the ownership privilege on both the user and the network policy

Implementation of column-level security will optimize query performance.

Column-level security supports encryption of the entire database.

Column-level security ensures that only the table owner can access the data.

Column-level security limits access to specific columns within a table based on user privileges

Column-level security allows the application of a masking policy to a column within a table or view.

Setting up a multi-cluster virtual warehouse

Resizing the virtual warehouse to a larger size

Automating the virtual warehouse suspension and resumption settings

Increasing the maximum cluster count parameter for a multi-cluster virtual warehouse

TRUNC

TRUNC(ID_NUMBER, 5)

ID_NUMBER*100

TO_CHAR

Persisted query results

QUERY_HISTORY View

Query acceleration service

Query Profile

Free listing

Paid listing

Direct share

Data Exchange

ESCAPE_UNENCLOSED_FIELD

REPLACE_INVALID_CHARACTERS

FIELD_OPTIONALLY_ENCLOSED_BY

EMPTY_FIELD_AS_NULL

SKIP_BLANK_LINES

SELECT parse_json (NULL)

SELECT parse_json('null*)

SELECT parse_json('[ null ]')

SELECT parse json('{"a": null)'):a

Tasks

Pipes

Secure User-Defined Functions (UDFs)

Stored Procedures

Standard

Enterprise

Business Critical

Virtual Private Snowflake (VPS)

WAREH0USE_SIZE = LARGE

WAREHOUSEJTYPE = SNOWPARK-OPTIMIZED

MAX_CLUSTER_COUHT - 10

ENABLE_QUERY _ACCELERATION = TRUE

Materialized views

External functions

Multi-cluster virtual warehouses

Fail-safe

Row-level security

DESCRIBE STREAM

ALTER STREAM

DROP STREAM

CREATE OR REPLACE STREAM

Account role

Custom role

Database role

Secondary role

Use sequence-gen era ted values

Programmatically reveal the identifiers.

Use secure views solely for query convenience.

Do not expose the sequence-generated column(s)

User roles

Query history

Object tagging

Resource monitors

Virtual warehouses

Schedule automated data backups.

Explore each step of an executed query.

Monitor queries executed by users in an account.

Create and manage user roles and permissions.

Access Snowflake Marketplace to find and integrate datasets.

IMMUTABLE

RETURNS

COMMENT

VOLATILE

SYSTEMSALLOWLIST

SYSTEM$ALLOWLIST_PRIVATELINK

SYSTEM$AUTHORIZE_PRIVATELINK

SYSTEM$IS LISTING PURCHASED

FLATTEN

OBJECT_AS

OBJECT_CONSTRUCT

TO VARIANT

An NVL function used to change the NULL values to another value

An equal_null function

A NULLIF function

An I FNULL function used to change the NULL values to another value

Authorisation

Access delegation

Data pruning

Data compression

Query parsing and optimization

USAGE on the specific tables in the database.

USAGE on the specific tables in the database.

MODIFY on 1Mb specific tables in the database.

USAGE on the database and the schema containing the tables to share

OPEBATE on the database and the schema containing the tables to share.

OWNERSHIP

MODIFY

CREATE

USAGE

The stage definition

The table definition

The use of a copy into

OPERATE

READ

SELECT

USAGE

An internal stage

An SFTP location

An external stage

A network share folder

A local disk

By clustering the tables

By creating a persistent data structure

By using caching

By optimizing the use of micro-partitions

32

64

128

256

The result set will be successfully downloaded from Snowsight.

The result set will be automatically compressed and the data will be downloaded as individual files.

The download will fail because the result set needs to be be broken up into files no greater than 50 MB before downloading.

The download will result in an error because the filters of the SELECT query need to be changed so that Snowsight returns a smaller result set.

A defined level of access to an object

An entity in which access can be granted

Each object has an owner, who can in turn grail access to that object.

Access privileges are assigned to roles. which are in turn assigned to use's

Cloning a shared table

Modifying the data in a shared table

Using Time Travel on a shared table

Executing the select statement on a shared table

Joining the data from a shared table with another table

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.

Auto-scale mode

Maximized mode

Standard scaling policy

Economy scaling policy

The privileges cannot be revoked.

The privileges can be revoked by an ACCOUNTADMIN.

The privileges can be revoked by an orgadmin.

The privileges can be revoked by any user-defined role with appropriate privileges.

Use the DROP command

Use the purge copy option.

Use the FORCE = TRUE parameter

Use the LOAD UNCERTAIN FILES copy option.

Insufficient credit quota

Inefficient query pruning

Excessive query pruning

Exploding joins

Credit usage that exceeds a set threshold

ACCOUNTADMIN

SECURITYADMIN

SYSADMIN

ORGADMIN

USAGE

SELECT

MONITOR

OPERATE

Avro

JSON

TSV

Parquet

Convert the virtual warehouse to Maximized mode.

Increase the size of the virtual warehouse.

Increase the auto suspend time in the virtual warehouse.

Increase the number of clusters in the virtual warehouse.

60

91

120

121

The warehouse size does not change until all queries currently running in the warehouse have completed.

The warehouse size does not change until all queries currently in the warehouse queue have completed.

The warehouse size does not change until the warehouse is suspended and restarted.

It does not return from the command until the warehouse has finished changing its size.

Schema Stage

User Stage

Database Stage

Table Stage

External Named Stage

Internal Named Stage

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.

Pipe

File format

Resource monitor

Storage integration

Virtual warehouse

Account

Database

Schema

Table

Virtual warehouse

8MB

16MB

32MB

128MB

Bytes sent over network

Percentage scanned from cache

Partitions total

Bytes spilled to local storage

Partitions scanned

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.

Append-only

External

Insert-only

Standard

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

Search optimization service

Automatic clustering

Row access policies

Multi-cluster virtual warehouses

Secure views

ACCOUNTADMIN

ORGADMIN

SECURITYADMIN

SYSADMIN

COPY INTO

CREATE PIPE

INSERT INTO

TABLE STREAM

SHOW MASKING POLICIES

DESCRIBE MASKING POLICY

GET_DDL

LIST MASKING POLICIES

src:salesperson.name

src:sa1esPerson. name

src:salesperson.Name

SRC:salesperson.name

SRC:salesperson.Name

Contacts

Sharing settings

Copy History

Query History

Automatic Clustering History

DISABLED

MINS TO BYPASS MFA

MINS TO BYPASS NETWORK POLICY

MINS TO UNLOCK

USERADMIN

PUBLIC

ORGADMIN

SYSADMIN

O days

1 day

7 days

90 days

CHECK_OPTION

IS_SECURE

IS_UPDATEABLE

TABLE_NAME

Account

Database

Organization

Schema

Virtual warehouse

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

Caching query results

Running EXPLAIN and SHOW commands

Cloning a database

Running a custom query

Running COPY commands

READ

OWNERSHIP

USAGK

WRTTF

A reader account can consume data from the provider account that created it and combine it with its own data.

A consumer needs to become a licensed Snowflake customer as data sharing is only supported between Snowflake accounts.

The users in a reader account can query data that has been shared with the reader account and can perform DML tasks.

The SHOW MANAGED ACCOUNTS command will view all the reader accounts that have been created for an account.

Data storage

Dynamic data masking

Partition scanning

User authentication

Infrastructure management

External tables

Secure views

Materialized views

User-Defined Functions (UDFs)

Determine the total duration of the query.

Determine the role of the user who ran the query.

Determine the source system that the queried table is from.

Determine if the query was on structured or semi-structured data.

Range Partitioning

Search Optimization Service

Automatic Clustering

Horizontal Partitioning

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

It does not retain any privileges granted on the source object.

It replicates all granted privileges on the corresponding source objects.

It replicates all granted privileges on the corresponding child objects.

It replicates all granted privileges on the corresponding child schema objects.

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.

Account master keys

Database master keys

File keys

Secure view keys

Schema master keys

Infrastructure management

Metadata management

Query execution

Query parsing and optimization

Permanent storage of micro-partitions

Rename listings.

Delete provider profiles.

Modify listings properties.

Modify incoming listing access requests.

Submit listings for approval/publishing.

Java

Javascript

Scala

Python

SQL

The shared objects are transferred.

The shared objects are copied.

The shared objects become accessible.

The shared objects can be re-shared.

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;

VARIANT

VARRAY

STRUCT

ARRAY

QUEUE

SHOW GRANTS OF ROLE MY_ROLE

SHOW GRANTS TO ROLE MY_ROLE

SHOW GRANTS FOR ROLE MY_ROLE

SHOW GRANTS ON ROLE MY_ROLE

SYSADMIN

ORGADMIN

ACCOUNTADMIN

SECURITYADMIN

Row access policies

Data masking policies

Data encryption

Role based access control

Authentication

Resource management

Virtual warehouse caching

Query parsing and optimization

Query execution

Physical storage of micro-partitions

Compress the file and load the compressed file.

Split the file into multiple files in the recommended size range (100 MB - 250 MB).

Use a larger-sized warehouse.

Set STRIP_OUTER_ARRAY=TRUE in the COPY INTO command.

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.

PUT

GET

COPY

SELECT

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

JOIN

COPY INTO

PUT

GET

A task can be called using a CALL statement to run a set of predefined SQL commands.

A task allows a user to execute a single SQL statement/command using a predefined schedule.

A task allows a user to execute a set of SQL commands on a predefined schedule.

A task can be executed using a SELECT statement to run a predefined SQL command.

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

Schema

View

Internal stage

Table

User

Role

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;

Removing a column from the query SELECT list

Stopping the virtual warehouse that the query is running against

Clustering of the data used by the query

Executing the RESULTS_SCAN() table function

Changing a column that is not in the cached query

Snowflake leverages a Role-Based Access Control (RBAC) model.

Snowflake requires a user to configure an IAM user to connect to the database.

All data in Snowflake is encrypted.

Snowflake can run within a user's own Virtual Private Cloud (VPC).

All data in Snowflake is compressed.

The explain plan output is available graphically.

An explain plan can be used to conduct performance analysis without executing a query.

An explain plan will handle queries with temporary tables and the query profiler will not.

An explain plan's output will display automatic data skew optimization information.

1 day

7 days

12 hours

0 days

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.

Tri-Secret Secure

Time Travel

Fail-safe

Data encryption

External stages

Queries against large tables where frequent DML occurs

Queries against tables larger than 1 TB

Selective point lookup queries

Queries against a subset of columns in a table

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

Database replication and failover

Automated index management

Customer managed keys (Tri-secret secure)

Extended time travel

Native support for geospatial data

Into single files with 100-250 MB of compressed data per file

Into single files with 1-100 MB of compressed data per file

Into files of maximum size of 1 GB of compressed data per file

Into files of maximum size of 4 GB of compressed data per file

Metadata

Data Storage

Compute

Cloud Services

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