Practice Tests COF-C02 - Mock Exam 3

Question 1

True or False

The search optimization service uses a persistent data structure

Answer 1

True

The search optimization service uses a persistent data structure as an optimized search access path to speed up point lookups. When the data in the table is changed (for example, by loading new data sets or performing their DML operations), the maintenance service updates the search access path to reflect the changes. The search optimization configuration on a table and the maintenance service are transparent to the users.

https://docs.snowflake.com/en/user-guide/search-optimization-service

Question 2

True or False
s3:ListBucket is needed to unload data to S3 location

Answer 2

FALSE

Snowflake requires s3:DeleteObject & s3:PutObject permissions on the target S3 bucket.

https://docs.snowflake.com/en/user-guide/data-unload-s3#configuring-an-s3-bucket-for-unloading-data

Question 3

Can VARIANT data type be used as clustering key?

Answer 3

NO

Clustering keys can be of any data type except GEOGRAPHY, VARIANT, OBJECT, or ARRAY

https://docs.snowflake.com/en/user-guide/tables-clustering-keys#defining-a-clustering-key-for-a-table

Question 4

True or False

Regarding Snowflake virtual warehouse’s high availability, Snowflake replicates virtual warehouses across three availability zones.

Answer 4

FALSE

The compute layer, i.e., the virtual warehouses, is not replicated. The virtual warehouses do not permanently store data and thus don’t require replication.

Snowflake typically runs each virtual warehouse in a single availability zone. However, in case of an availability zone failure, Snowflake’s cloud services layer can re-provision impacted warehouses in a different availability zone.

https://developers.snowflake.com/wp-content/uploads/2021/06/Snowflake-High-Availability-for-Data-Apps-Whitepaper.pdf

Question 5

True or False

How frequently the base table is queried drive the costs associated with materialized view maintenance

Answer 5

FALSE

The costs of keeping data in materialized views are impacted by

1) The number of materialized views created for each base table.

2) The extent of data changes occurring in these materialized views when changes are made to the base table.

3) The number of these materialized views with a clustering key is defined.

https://docs.snowflake.com/en/user-guide/views-materialized#label-materialized-views-maintenance-billing

Question 6

True or False

Tasks can be cloned.

Answer 6

TRUE

Virtual warehouses & Share objects cannot be cloned.

Tables, Schemas & Databases can be cloned.

Other objects that can be cloned include Stages, File Formats, Tasks, Sequences, and Streams.

https://docs.snowflake.com/en/user-guide/object-clone

Question 7

True or False

After a virtual warehouse has been suspended by a resource monitor, an account administrator can resume the virtual warehouse.

Answer 7

FALSE

If a monitor has a Suspend or Suspend Immediately action, and its used credits hit the threshold for the action, any warehouses assigned to the monitor are put on hold and can’t be used again until one of the following happens:

• The next interval starts as per the monitor configuration. A monitor credit limit is applicable within a defined time interval (days, months, etc.)
• The credit quota for the monitor is increased.
• The credit threshold needed to suspend is increased.
• The virtual warehouse is removed from the monitor configuration (does not apply to account-level monitors)
• The monitor is dropped altogether.

https://docs.snowflake.com/en/user-guide/resource-monitors

Question 8

True or False

Privileges can be granted on securable objects.

Answer 8

TRUE

Objects or entities that can be granted privileges are called securable objects.

Each securable object can be assigned a set of rights. Privileges can only be granted to roles; they cannot be granted directly to individual users. Therefore, it is possible to grant roles to other users or other roles.

https://docs.snowflake.com/en/user-guide/security-access-control-overview

Question 9

True or False

Transient tables can be cloned to permanent tables.

Answer 9

FALSE

Transient tables can NOT be cloned to a permanent table.

Doing so will typically show the following error “Transient object cannot be cloned to a permanent object.”

However, a transient table may be cloned to a transient table or another temporary table.

Question 10

What objects may be shared via direct data sharing?

Answer 10

You can share the following Snowflake objects:

Databases

Tables

Dynamic tables

External tables

Apache Iceberg™ tables

Secure views

Secure materialized views

Secure user-defined functions (UDFs)

https://docs.snowflake.com/en/user-guide/data-sharing-intro

Question 11

True or False

If a database or a schema Is cloned, the child object privileges are automatically copied.

Answer 11

TRUE

A cloned object does not inherit any privileges from its source object; for instance, a cloned table does not inherit any privileges from its source table.

However, if a database or schema is cloned, privileges are inherited by the child objects.

https://docs.snowflake.com/en/user-guide/object-clone#access-control-privileges-for-cloned-objects

Question 12

What is the minimum Snowflake edition required for securely sharing data across regions and cloud platforms (via replication)?

Answer 12

Standard

Database sharing across regions and clouds (via replication) is supported in all Snowflake editions; thus, the minimum edition that supports it is the Standard edition.

https://docs.snowflake.com/en/user-guide/intro-editions.html

Question 13

True or False

Add or delete data contribute to total storage when cloning objects.

Answer 13

TRUE

When tables, schemas, or databases are cloned, the cloning operation does not contribute to total storage until data manipulation language (DML) operations are performed on the source or target, which modify or delete existing data or add additional data.

https://docs.snowflake.com/en/user-guide/tables-storage-considerations#label-cloning-tables

Question 14

True or False

SECURITYADMIN can create & manage users

Answer 14

TRUE

The USERADMIN role is typically meant for creating and managing users.

However, the privileges of the USERADMIN role are inherited by SECURITYADMIN and ACCOUNTADMIN; therefore, they also get the privileges to create users. ACCOUNTADMIN is the most powerful role anyway and can do anything in a Snowflake account.

https://docs.snowflake.com/en/user-guide/security-access-control-overview#system-defined-roles.

Question 15

True or False

The data in the views in the INFORMATION_SCHEMA can have a latency of up to 3 hours.

Answer 15

FALSE

The data provided via the INFORMATION_SCHEMA views is real-time, and there is no latency in the information provided. So, if you are asked which schema should be used if there is a requirement to view real-time data, then the views in INFORMATION SCHEMA should be used as they contain real-time information.

https://docs.snowflake.com/en/sql-reference/account-usage#differences-between-account-usage-and-information-schema

Question 16

You can upload data into which stages using the PUT command?

Answer 16

The PUT command uploads data from an on-premises system to an internal stage (including named internal stages, table stages & user stages).

The GET command is used to download data from an internal stage to an on-premises system.

To download or upload data to an external stage, cloud provider utilities or other tools are used to interact with data in the cloud storage pointed to by the external stage.

https://docs.snowflake.com/en/user-guide/data-unload-overview#bulk-unloading-process

Question 17

What is the minimum Snowflake edition that supports Column Level Masking?

Answer 17

Enterprise

The Enterprise edition has several additional capabilities not provided in the Standard edition. These include multi-cluster virtual warehouses, column-level masking, row access policies, materialized views, and search optimization.

https://docs.snowflake.com/en/user-guide/intro-editions.html

Question 18

What is the maximum period for which Time Travel is permitted for Transient tables?

Answer 18

1 day

Transient and Temporary tables in Snowflake support Time Travel for up to 1 day, irrespective of the Snowflake edition used.

https://docs.snowflake.com/en/user-guide/tables-temp-transient

Question 19

What is the minimum required role to create a trial account through Partner Connect?

Answer 19

Only users with the ACCOUNTADMIN role and a verified email address in Snowflake can access Partner Connect.

https://docs.snowflake.com/en/user-guide/ecosystem-partner-connect#connecting-with-a-snowflake-partner

Question 20

What happens to the MV when columns are changed or dropped from a base table with a materialized view on top

Answer 20

When columns are changed or dropped from a base table with a materialized view on top, the change is not propagated to the materialized view.

The materialized view is suspended and can NOT be resumed. It must be re-created with the corrected definition that reflects the changed/dropped columns.

https://docs.snowflake.com/en/user-guide/views-materialized#changing-or-dropping-columns-in-the-base-table

Question 21

True or False

When a Snowflake account shares data with another Snowflake account, The data provider is charged for the compute charges for queries the data consumer runs.

Answer 21

FALSE

Since the provider account stores and pays for the data storage, the data consumer doesn’t have to pay anything extra for storage.

However, the data consumer pays for the compute used to run queries on shared data. When queries are run on shared data, the compute of the data consumer is used

Question 22

To create an external UDF, what is the minimum Snowflake edition required?

Answer 22

Standard

Question 23

True or False

Each micro-partition generally contains 50MB to 500MB of uncompressed data.

Answer 23

True

Micro-partitions are small and typically store 50 MB to 500 MB of uncompressed data.

https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html

Question 24

True or False

Consumer accounts cannot clone a shared database

Answer 24

True

Consumer accounts can only access and query data but cannot add, modify, or create database objects to a shared database.

Consumer accounts cannot clone a shared database, its schemas, or any of its tables.

Consumer accounts cannot use Time Travel on the shared data.

Consumer accounts cannot further share a shared database.

https://docs.snowflake.com/en/user-guide/data-share-consumers#general-limitations-for-shared-databases

Question 25

True or False

Snowpark automatically converts the data-processing programming constructs to SQL and pushes them down to Snowflake for execution.

Answer 25

TRUE

Snowpark is a library created by Snowflake that provides APIs for accessing and processing data in applications written in a programming language other than SQL.

Snowpark allows programmers to utilize common programming languages such as Java, Scala, and Python to construct apps that handle data using standard programming structures.

Snowpark automatically converts the data-processing programming constructs to SQL and sends them to Snowflake for execution.

https://docs.snowflake.com/en/developer-guide/snowpark/ind

Question 26

True or False

Find the total of a numeric column can be fulfilled without needing an active virtual warehouse

Answer 26

FALSE

Snowflake stores information about micro-partitions in the metadata. It stores the range of column values in its metadata, which includes the maximum and minimum values for each column in each micro-partition.

Snowflake also stores the count of distinct values for each column in the metadata and certain other information to optimize a query. Because this information is stored in the metadata cache, Snowflake does not have to read the data from the tables for specific queries; instead, it may retrieve the information it needs directly from the metadata. These queries include things like count queries and queries containing functions like MIN or MAX.

The metadata cache will not be used if you execute MIN or MAX on a column containing only characters.

Question 27

True or False

A share object contains the virtual warehouse that will be used to execute queries on the shared objects.

Answer 27

FALSE

A share acts as a container for objects that need to be shared and specifies the consumer accounts.

A share contains USAGE privileges on the database & the schema to be shared, privileges on the tables, secure views which will be shared, and the consumer account(s) to which the Share will be available. A virtual warehouse is not part of a share.

If a Snowflake customer consumes a share, they will use their own virtual warehouse. If a non-Snowflake customer is consuming the Share, they will use the data provider compute through a data provider-created virtual warehouse (which would have been separately configured)

Question 28

During a weekly release cycle, which of the following accounts may be updated on the second day of release?

Answer 28

• All standard edition accounts
• All enterprise edition (and above) accounts that have not opted into early access

Day 1 (early access): Deployed for Enterprise edition (or higher) accounts that have elected for early access. You can enroll an Enterprise edition (or higher) account for early access by contacting Snowflake support. Day 1 or 2 (regular access): Deployment of all Snowflake accounts on the Standard edition. Day 2 (last): All remaining Enterprise edition (or higher) accounts are deployed.

https://docs.snowflake.com/en/user-guide/intro-releases

Question 29

What is the minimum Snowflake edition which supports multi-factor authentication (MFA)?

Answer 29

Standard

All Snowflake editions support MFA; thus, the minimum edition that supports it is the Standard edition.

https://docs.snowflake.com/en/user-guide/intro-editions.html

Question 30

Which one of the stream types can you use to track data in an external table?

Answer 30

Insert-only streams in Snowflake track changes to external tables by capturing both inserts and updates as new rows, but they ignore delete operations. This means they only reflect additions and modifications to data, treating updates as new inserts.

The valid stream types are Standard, Insert-only, and append only.

https://docs.snowflake.com/en/user-guide/streams-intro#types-of-streams

Question 31

Which of the ACCOUNT_USAGE views can be used to view credit usage by hour?

Answer 31

The METERING_HISTORY provides the credit usage data at an hourly level. The view provides the start and end times during which credit usage occurred. It also provides a breakup of the information according to the service that contributed to the credit usage, such as Virtual Warehouse compute usage, Snowpipe, Automatic Clustering, etc.

https://docs.snowflake.com/en/sql-reference/account-usage/metering_history

Question 32

True or False

Basic Transformations during the COPY process are supported by Table Stages

Answer 32

FALSE

The table stages do not allow basic transformations during the COPY process; thus, basic transformations may only be performed while loading data from external stages, named internal stages or user stages.

https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage#table-stages