COF-C02 Questions and Answers

Access history

Data classification

Dynamic Data Masking

Object tagging

A separate database object that is used to store file-level metadata

An object layered on a stage that is used to store file-level metadata

A database object with grantable privileges for unstructured data tasks

A Snowflake table specifically designed for storing unstructured files

Tags

Comments

Internal tokenization

Row access policies

GET_PRESIGNED_URL

GET_ABSOLUTE_PATH

BUILD_STAGE_FILE_URL

BUILD SCOPED FILE URL

Data Replication

Secure Data Sharing

Data Exchange

Snowflake Marketplace

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

TO_OBJECT

TO_JSON

TO_VARIANT

OBJECT CONSTRUCT

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

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

Use the automatic sharing feature for seamless access.

Drop the object and then re-add it to the database to trigger sharing.

Recreate the object with a different name in the database before sharing.

Use the grant privilege ... TO share command to grant the necessary privileges.

Grant

Privilege

Role

Session

External

Permanent

Temporary

Transient

Roles

Users

Databases

Schemas

Virtual warehouses

Persisted query results

QUERY_HISTORY View

Query acceleration service

Query Profile

Both tables have no Fail-safe period.

Both tables have data retention period maximums of one day.

Both tables are visible only to a single user session.

For both tables the retention period ends when the tables are dropped.

For both tables, the retention period does not end when the session ends

PUT

INSERT

COPY INTO

GET

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.

To sort the contents of the output file by the specified expression.

To delimit the records in the output file using the specified expression.

To include a new column in the output using the specified window function expression.

To split the output into multiple files, one for each distinct value of the specified expression.

No details of the query execution plan will be available in the query profiler.

Once created there is no way to determine if a view is secure or not.

Secure views do not take advantage of the same internal optimizations as standard views.

It is not possible to create secure materialized views.

The view definition of a secure view is still visible to users by way of the information schema.

Multi-Factor Authentication (MFA)

Default database

Default schema

Notification

Username

User

Role

Account

Organization

SYSADMIN only

ORCADMIN only

SECURITYADMIN or higher roles

A role with the CREATE NETWORK POLICY privilege

A role with the CREATE SECURITY INTEGRATION privilege

2

3

5

7

The DATA_RETENTI0N_TIME_IN_DAYS parameter in the consumer account is set to 0.

The consumer account runs the GRANT IMPORTED PRIVILEGES command on the data share every 24 hours.

The objects in the data share are being deleted and the grant pattern is not re-applied systematically.

The consumer account acquires the data share through a private data exchange.

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.

No active warehouse to needed to query account usage views but one is needed to query information schema views.

Account usage views do not contain data about tables but information schema views do.

Account issue views contain dropped objects but information schema views do not.

Data retention for account usage views is 1 year but is 7 days to 6 months for information schema views, depending on the view.

Information schema views are read-only but account usage views are not.

Load now data

Update existing data

Create a new snare

Query shared data

ACCOUNTADMIN

SECURITYADMIN

SYSADMIN

ORGADMIN

TASK_HISTORY

TASK_DEPENDENTS

CURRENT_TASK_GRAPHS

COMPLETE_TASK_GRAPHS

Faster query performance and increased storage consumption

Slower query performance and increased storage consumption

Faster query performance and decreased storage consumption

Slower query performance and decreased storage consumption

ACCESS_HISTORY

LOAD_HISTORY

QUERY_HISTORY

REST EVENT HISTORY

ALTER TABLE

ARRAY

BINARY

TEXT

VARIANT

VARCHAK

Directory

Transient

Temporary

Permanent

ACCOUNTADMIN

SECURITYADMIN

SYSADMIN

ORGADMIN

The data retention period is reduced to a minimum of 14 days.

The data retention period is permanently extended for the table.

The data retention period is temporarily extended to the stream's offset.

The data retention period is not affected by the stream consumption.

They can be set for any Snowflake Edition.

They can be applied to roles.

They restrict or enable access to specific IP addresses.

They are activated using ALTER DATABASE SQL commands.

They can only be managed using the ORGADMIN role.

32

64

128

256

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

Each Snowflake account can be hosted in a different cloud vendor and region.

Each Snowflake account must be hosted in a different cloud vendor and region

All Snowflake accounts must be hosted in the same cloud vendor and region

Each Snowflake account can be hosted in a different cloud vendor, but must be in the same region.

LOAD

copy

GET

PUT

ORC

XML

Avro

Parquet

JSON

Standard Edition

Enterprise Edition

Business Critical Edition

Virtual Private Snowflake Edition

Compute layer

Storage layer

Cloud infrastructure layer

Cloud services layer

Use multi-clustered warehousing to scale out warehouse capacity.

Scale up the warehouse size to allow Queries to execute faster.

Stop and start the warehouse to clear the queued queries

Migrate some queries to a new warehouse to reduce load

Limit user access to the warehouse so fewer queries are run against it.

Create the custom role using the SYSADMIN role.

Assign the custom role to the SYSADMIN role

Assign the custom role to the PUBLIC role

Add__CUSTOM to all custom role names

Alation

DataRobot

dbt

Tableau

The user has the appropriate privileges on the objects associated with the query

Micro-partitions have been reclustered since the query was last run

The new query is run using the same virtual warehouse as the previous query

The query includes a User Defined Function (UDF)

The query has been run within 24 hours of the previously-run query

An FTP server with TLS encryption

An HTTPS server with WebDAV

A Google Cloud storage bucket

A Windows server file share on Azure

VARIANT

ARRAY

VARCHAR

XML

BLOB

Micro-partitions

Clustering keys

Key partitions

Clustered partitions

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

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

Load files that are approximately 25 MB or smaller.

Remove all dates and timestamps.

Load files that are approximately 100-250 MB (or larger)

Avoid using embedded characters such as commas for numeric data types

Remove semi-structured data types

Okta Verify

Duo Mobile

Microsoft Authenticator

Google Authenticator

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

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

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.

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

Metadata cache

Result cache

Remote cache

Local file cache

Pipe

External stage

Task

Stream

Internal stages

Incremental backups

Time Travel

Zero-copy clones

Fail-safe

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;

Column-level security

SOC 2 type II certification

Multi-factor Authentication (MFA)

Object-level access control

Via the Snowflake Web Interface (Ul)

Via the data_share=true parameter

Via SQL commands

Via Virtual Warehouses

Pipes

Streams

Tasks

Procedures

Data can be filtered by an optional where clause

Incoming data can be joined with other tables

Columns can be reordered

Columns can be omitted

Row level access can be defined

ALLOW RESIZE ON WAREHOUSE MKTG__WH TO USER MKTG__LEAD;

GRANT MODIFY ON WAREHOUSE MKTG WH TO ROLE MARKETING;

GRANT MODIFY ON WAREHOUSE MKTG__WH TO USER MKTG__LEAD;

GRANT OPERATE ON WAREHOUSE MKTG WH TO ROLE MARKETING;

Database replication and failover

Automated index management

Customer managed keys (Tri-secret secure)

Extended time travel

Native support for geospatial data

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

PURGE

MODIFY

REMOVE

DELETE

JOIN

COPY INTO

PUT

GET

The user who created the object

The SYSADMIN role

Ownership depends on the type of object

The role used to create the object

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