COF-R02 Sample Questions Answers

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

Query against the result set cache

Query against a virtual warehouse cache

Query against the most-recently created micro-partition

Query against the metadata excite

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

Time Travel - 1 day. Fail-safe - 1 day

Time Travel - 0 days. Fail-safe - 1 day

Time Travel - 1 day. Fail-safe - 0 days

Transient tables are retained in neither Fail-safe nor Time Travel

External Tokenization

External Tables

Materialized Views

User-Defined Table Functions (UDTF)

CHECK_JSON

TO_JSON

FLATTEN

PARSE_JSON

Row access policies

Data masking policies

Data encryption

Role based access control

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

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.

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.

Coordinates activities in the Snowflake account

Executes queries submitted by the Snowflake account users

Manages quotas on the Snowflake account storage

Manages the virtual warehouse cache to speed up queries

Schemas

Roles

Secure Views

Stored Procedures

Tables

Secure User-Defined Functions (UDFs)

1

2

3

4

Avro

JSON

ORC

XML

Delimited (CSV, TSV, etc.)

COPY WAREHOUSE FROM <>;

SET WAREHOUSE = <>;

USE WAREHOUSE <>;

USE VIRTUAL_WAREHOUSE <>;

Management of metadata

Computing the data

Maintaining Availability Zones

Infrastructure security

Parsing and optimizing queries

Data expires from Time Travel

Data expires from Fail-safe

Data is deleted from a table

Data is truncated from a table

Role

Schema

User

Database

Account

Tables

Account and user authentication is only available with the Snowflake Business Critical edition.

Support for HIPAA and GDPR compliance is available for UI Snowflake editions.

Periodic rekeying of encrypted data is available with the Snowflake Enterprise edition and higher

Private communication to internal stages is allowed in the Snowflake Enterprise edition and higher.

1 day

7 days

12 hours

0 days

Aim to produce data files that are between 100 MB and 250 MB in size, compressed.

Load data from files in a cloud storage service in a different region or cloud platform from the service or region containing the Snowflake account, to save on cost.

Enclose fields that contain delimiter characters in single or double quotes.

Split large files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse.

When planning which warehouse(s) to use for data loading, start with the largest warehouse possible.

Partition the staged data into large folders with random paths, allowing Snowflake to determine the best way to load each file.

The columns that are queried in the select clause.

The columns with very high cardinality.

The columns with many different values.

The columns most actively used in the select filters.

Infrastructure management

Metadata management

Query execution

Query parsing and optimization

Permanent storage of micro-partitions

The number of queries executed

The number of active users assigned to the warehouse

The size of the virtual warehouse

The length of time the warehouse is running

The duration of the queries that are executed

Compute

Data storage

Cloud services

Cloud provider

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.

Scaling out adds clusters of the same size to a virtual warehouse to handle more concurrent queries.

Scaling out adds clusters of varying sizes to a virtual warehouse.

Scaling out adds additional database servers to an existing running cluster to handle more concurrent queries.

Snowflake recommends using both scaling up and scaling out to handle more concurrent queries.

Scaling up resizes a virtual warehouse so it can handle more complex workloads.

Scaling up adds additional database servers to an existing running cluster to handle larger workloads.

Configure the warehouse to a size larger than generally required

Set the minimum and maximum clusters to autoscale

Use the standard warehouse scaling policy

Use the economy warehouse scaling policy

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

The Data Consumer pays for data storage as well as for data computing.

The shared data is copied into the Data Consumer account, so the Consumer can modify it without impacting the base data of the Provider.

A Snowflake account can both provide and consume shared data.

The Provider is charged for compute resources used by the Data Consumer to query the shared data.

The Data Consumer pays only for compute resources to query the shared data.

Standard

Enterprise

Business Critical

Virtual Private Snowflake

SnowCLI

SnowUI

SnowSQL

SnowCD

INSERT

PUT

GET

COPY

Metadata

Data Storage

Compute

Cloud Services

JDBC

SnowSQL

Snowflake Web Interface (UI)

ODBC

Python

D18912E1457D5D1DDCBD40AB3BF70D5D

True

False

True

False

The complexity of each query

The CONCURRENT_QUERY_UMIT parameter set on the Snowflake account

The size of the data required for each query

The tool that s executing the query

True

False

True

False

Standard

Premier

Enterprise

Business Critical Edition

Compute

Tri-Secret Secure

Storage

Cloud Services

True

False

A Schema may contain one or more Databases

A Database may contain one or more Schemas

A Schema is a logical grouping of Database Objects

Each Schema is contained within a Warehouse

True

False

Standard

Premier

Enterprise

Business Critical Edition

To accommodate more queries

To accommodate more users

To accommodate fluctuations in workload

To accommodate a more complex workload

True

False

True

False

True

False

More micro-partitions

Better query scheduling

Double the numbers of servers In the compute duster

Higher storage costs

True

False

On-premise availability

Simplicity

Open source based

Concurrency

Performance

True

False

COPY INTO @stage

EXPORT TO @stage

INSERT INTO @stage

EXPORT_TO_STAGE(stage = > @Wage, select = > 'select * from t1);

Custom

Economy

Optimized

Standard

True

False

True

False

True

False

Auto-suspend

Auto-resume

Local SSD size

User count

Automatically creates a File Format object

Automatically uses the last Stage created

Automatically compresses files using Gzip

Automatically encrypts files

Growth of storage and compute are tightly coupled together

Storage expands without the requirement to add more compute

Compute can be scaled up or down without the requirement to add more storage

Multiple compute clusters can access stored data without contention

Number of users

Warehouse size

Amount of data processed

# of clusters for the Warehouse

SYSADMIN

SECURITYADMIN

PUBLIC

ACCOUNTADMIN

12am-5am

Only during administrator defined time slots

When its provisioning is complete

After replication

Tables

Named File Formats

Schemas

Shares

Databases

Users

Temporary

Translent

Provisional

Permanent

Micro-partitions

Clustering keys

Key partitions

Clustered partitions

Data sharing within a specific region

Query result retrieval

Database replication

Loading data into Snowflake

SELECT LAST_SUCCESS_LOGIN, LOGIN_NAME

FROM ACCOUNT_USAGE.USERS;

SELECT EVENT_TIMESTAMP, USER_NAME

FROM table(information_schema.login_history_by_user())

SELECT EVENT_TIMESTAMP, USER_NAME

FROM ACCOUNT_USAGE.ACCESS_HISTORY;

SELECT EVENT_TIMESTAMP, USER_NAME

FROM ACCOUNT_USAGE.LOGIN_HISTORY;

Through the Snowflake web interface (Ul)

Through the DATA_SHARE=TRUE parameter

Through SQL commands

Through the enable__share=true parameter

Using the CREATE SHARE AS SELECT * TABLE command

As configured in the create pipe settings

Until the pipe is dropped

64 days

14 days

Cluster keys

Time Travel

Fail-safe

Cloning

show grants to user USER1;

show grants of user USER1;

describe user USER1;

show grants on user USER1;

CSV

JSON

Parquet

XML

CREATE SHARE

ALTER WAREHOUSE

DROP ROLE

SHOW SCHEMAS

DESCRBE TABLE

True

False

COPY INTO

GET

WRITE

EXTRACT INTO

Compute resources

Query execution

Authentication

Data storage

Metadata

Okta Verify

Duo Mobile

Microsoft Authenticator

Google Authenticator

A compressed file size greater than 100 MB, and up to 250 MB

A compressed file size greater than 100 GB, and up to 250 GB

A compressed file size greater than 10 MB, and up to 100 MB

A compressed file size greater than 1 GB, and up to 2 GB

Variant

Object

Geometry

Geography

LOAD

copy

GET

PUT

Zero-copy cloning creates a mirror copy of a database that updates with the original

Software updates are automatically applied on a quarterly basis

Snowflake eliminates resource contention with its virtual warehouse implementation

Multi-cluster warehouses allow users to run a query that spans across multiple clusters

Snowflake automatically sorts DATE columns during ingest for fast retrieval by date

Columns can be aggregated

Columns can be joined with an existing table

Columns can be reordered

Columns can be omitted

Data can be loaded without the need to spin up a virtual warehouse

Root task may be executed concurrently (i.e. multiple instances), it is recommended to leave some margins in the execution window to avoid missing instances of execution

Querying (select) the size of the stream content would help determine the warehouse size. For example, if querying large stream content, use a larger warehouse size

If using the stored procedure to execute multiple SQL statements, it's best to test run the stored procedure separately to size the compute resource first

Since task infrastructure is based on running the task body on schedule, it's recommended to configure the virtual warehouse for automatic concurrency handling using Multi-cluster warehouse (MCW) to match the task schedule

Clustering keys update the micro-partitions in place with a full sort, and impact the DML operations.

Clustering keys sort the designated columns over time, without blocking DML operations

Clustering keys create a distributed, parallel data structure of pointers to a table's rows and columns

Clustering keys establish a hashed key on each node of a virtual warehouse to optimize joins at run-time

Internal stages

Incremental backups

Time Travel

Zero-copy clones

Fail-safe

Via the Snowflake Web Interface (Ul)

Via the data_share=true parameter

Via SQL commands

Via Virtual Warehouses

To call code that executes outside of Snowflake

To run a function in another Snowflake database

To share data in Snowflake with external parties

To ingest data from on-premises data sources

ROLES

POLICY_REFERENCES

QUERY_HISTORY

RESOURCE_MONIT ORS

ACCESS_HISTORY

ROLEADMIN

ACCOUNTADMIN

SYSADMIN

SECURITYADMIN

USERADMIN

The ability to scale up warehouses

The use of warehouse auto scaling

The ability to resize warehouses

Use of multi-clustered warehouses

The use of warehouse indexing

An FTP server with TLS encryption

An HTTPS server with WebDAV

A Google Cloud storage bucket

A Windows server file share on Azure

All data from all of the files on the stage will be appended to the table

Only data about new customers from the new files will be appended to the table

The operation will fail with the error uncertain files in stage.

All data from only the newly-added files will be appended to the table.

Database stage

External stage

Table stage

User stage