[COF-C02] SnowPro Core Certification Mock Exam - 3

Question 1

A DBA_ROLE created a database. Later the DBA_ROLE was dropped. Who will own the database now, which was created by the DBA_ROLE?

Answer 1

The role that dropped the DBA_ROLE will own the database. It is an important question for the exam.

Question 2

Which AWS service is used to create private VPC endpoints that allow direct, secure connectivity between your AWS VPCs and the Snowflake VPC without traversing the public internet?

Answer 2

AWS PrivateLink is an AWS service for creating private VPC endpoints that allow direct, secure connectivity between your AWS VPCs and the Snowflake VPC without traversing the public internet. The connectivity is for AWS VPCs in the same AWS region.

For External Functions, you can also use AWS PrivateLink with private endpoints.

In addition, if you have an on-premises environment (e.g. a non-hosted data center), you can choose to use AWS Direct Connect, in conjunction with AWS PrivateLink, to connect all your virtual and physical environments in a single, private network.

Question 3

Which is the default timestamp in Snowflake?

Answer 3

TIMESTAMP_NTZ is the default timestamp type if you just define a column as a timestamp. Hint to remember: NTZ represents NO TIME ZONES.

Question 4

Which roles can configure a network policy?

Answer 4

Only security administrators (i.e., users with the SECURITYADMIN role) or higher or a role with the global CREATE NETWORK POLICY privilege can create network policies.

Question 5

What are the three interfaces in Snowsight.

Answer 5

Left Navigation consists of Worksheets, Dashboards, Data, Marketplace, Activity, Admin, Help & Support.

User Menu lets you Switch Roles, Profile including multi-factor authentication (MFA), Partner Connect, Documentation, Support and Sign Out.

The account selector, located at the bottom of the left nav, lets you sign in to other Snowflake accounts.

Question 6

Which type of object key is only used for decryption?

Answer 6

Retired Key is used for decryption only.

Active Key is used for both encryption and decryption.

Destroyed Key is no longer used.

Question 7

Direct data sharing can only be done with accounts in the same region and the same cloud provider. (TRUE/FALSE)

Answer 7

Direct data sharing can only be done with accounts in the same region and the same cloud provider. Suppose you want to share with someone outside of your region. In that case, you simply do a replication of that database into the region you want to share with and share from there.

Question 8

Monica is confused about which sampling method she should use with one of the very large tables, considering better performance. Which sampling method would you recommend from BERNOULLI | ROW and SYSTEM | BLOCK?

Answer 8

SYSTEM | BLOCK sampling is often faster than BERNOULLI | ROW sampling. Also, BERNOULLI | ROW method is good for Smaller Tables, and SYSTEM | BLOCK method is for Larger Tables.

Question 9

Which copy option is used to delete the file from the Snowflake stage when data from staged files are loaded successfully?

Answer 9

Staged files can be deleted from a Snowflake stage (user stage, table stage, or named stage) using the following methods:

1. Files that were loaded successfully can be deleted from the stage during a load by specifying the PURGE copy option in the COPY INTO <table> command.
2. After the load completes, use the REMOVE command to remove the files in the stage.

Please note, DELETE or REMOVE are not COPY command options. REMOVE is a different DML command which is used to remove files in the stage.

Question 10

How long does Snowflake keep Snowpipe’s load history?

Same of Snowsight

Answer 10

Snowflake keeps the Snowpipe’s load history for 14 days. If you recreate [CREATE OR REPLACE ..] the PIPE then the load history will reset to empty [ very important for the exam ].

Question 11

John wants to load data files from an external stage to Snowflake. He has split the large file into smaller 100 - 250 MB data files, and there is a total of 16 smaller data files. What warehouse size would you recommend him to use for loading these data files quickly and cost-effectively?

Answer 11

XS sized warehouse can load eight files parallelly. S sized warehouse can load sixteen files parallelly. M sized warehouse can load thirty-two files parallelly. L sized warehouse can load sixty-four files parallelly. XL sized warehouse can load one hundred twenty-eight files parallelly and so on.

Question 12

Snowflake prunes micro-partitions based on a predicate with a subquery, even if the subquery result is constant. (TRUE/FALSE)

Answer 12

False

Please note, not all predicate expressions can be used to prune. Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant.

Question 13

What are the key benefits of The Data Cloud?

Answer 13

The benefits of The Data Cloud are Access, Governance, and Action.

Access means that organizations can easily discover data and share it internally or with third parties without regard to geographical location.

Governance is about setting policies and rules and protecting the data in a way that can unlock new value and collaboration while maintaining the highest levels of security and compliance.

Action means you can empower every part of your business with data to build better products, make faster decisions, create new revenue streams and realize the value of your greatest untapped asset, your data.

Question 14

What is the best way to analyze the optimum warehouse size?

Answer 14

To achieve the best results, try to execute relatively homogeneous queries (size, complexity, data sets, etc.) on the same warehouse; executing queries of widely-varying size and/or complexity on the same warehouse makes it more difficult to analyze warehouse load, which can make it more difficult to select the best size to match the size, composition, and number of queries in your workload.

Question 15

What size of the virtual warehouse needs to be created by the sysadmin while loading using Snowpipe?

Answer 15

Snowpipe uses compute resources provided and managed by Snowflake (i.e. a serverless compute model). These Snowflake-provided resources are automatically resized and scaled up or down as required, and are charged and itemized using per-second billing. Data ingestion is charged based upon the actual workloads. User doesn’t need to create any warehouse as it is taken care by Snowflake.

Question 16

An HTTP client that sends a URL (either scoped URL or file URL) to the REST API must be configured to allow redirects. (True/False)

Answer 16

True

An HTTP client that sends a URL (either scoped URL or file URL) to the REST API must be configured to allow redirects.

Question 17

Scoped URL is ideal for

Answer 17

Scoped URL: Encoded URL that permits temporary access to a staged file without granting privileges to the stage. The URL expires when the persisted query result period ends (i.e., the results cache expires), which is currently 24 hours. Ideal for use in custom applications, providing unstructured data to other accounts via a share, or for downloading and ad hoc analysis of unstructured data via Snowsight.

File URL: URL that identifies the database, schema, stage, and file path to a set of files. A role that has sufficient privileges on the stage can access the files. Ideal for custom applications that require access to unstructured data files.

Pre-signed URL: Simple HTTPS URL used to access a file via a web browser. A file is temporarily accessible to users via this URL using a pre-signed access token. The expiration time for the access token is configurable. Ideal for business intelligence applications or reporting tools that need to display unstructured file contents.

Question 18

Which is the fastest option for selecting staged data files to load from a stage?

Answer 18

Providing a discrete list of files is generally the fastest; however, the FILES parameter supports a maximum of 1,000 files, meaning a COPY command executed with the FILES parameter can only load up to 1,000 files. Example: copy into load1 from @%load1/data1/ files=(‘test1.csv’, ‘test2.csv’, ‘test3.csv’, ‘test4.csv’)

Question 19

Federated Authentication is supported in which versions?

Answer 19

Federated authentication is supported by all of the Snowflake editions.

Question 20

What key insights can we get from the Explain plan in Snowflake?

Answer 20

The key insights that the explain plan gives us in its results output are information on partition pruning, join ordering, and join types.

The explain plan is a useful tool for determining the efficiency of your query. It’s a command that compiles your query to figure out all the steps Snowflake would have to work through if it were actually to run the query.

Question 20

Which are types of Snowflake releases?

Answer 20

There are three types of releases:

1. Full Release: A full release may include any of the following:

• New features
• Feature enhancements or updates
• Fixes

2. Patch Release: A patch release includes fixes only.
3. Behavior Release: Every month, Snowflake deploys one behavior change release. Behavior change releases contain changes to existing behaviors that may impact customers.

Question 21

What are all operations performed using Snowflake SQL API?

Answer 21

The Snowflake SQL API provides operations that we can use to:

• Submit SQL statements for execution.
• Check the status of the execution of a statement.
• Cancel the execution of a statement.
• Fetch query results concurrently.

Currently, Snowflake SQL API has limitations for the call command with stored procedures that return a table (stored procedures with the RETURNS TABLE clause).

Question 22

What actions can a consumer perform on a share?

Answer 22

Shared databases are read-only. A consumer cannot UPDATE a share. However, the consumer can do a CREATE TABLE AS to make a point-in-time copy of the data that’s been shared. The consumer cannot clone and re-share a share or forward it. And also, time travel data on a share is not available to the consumer. A share can be imported into one database.

Note: In the exam, you may be asked for Reader Account as well.

Question 23

Fail-safe helps access historical data after the Time Travel retention period has ended. (True/False)

Answer 23

False

Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended. It is for use only by Snowflake to recover data that may have been lost or damaged due to extreme operational failures. Data recovery through Fail-safe may take from several hours to several days to complete.

Question 24

What size limit does VARIANT data type impose on individual rows?

Answer 24

The VARIANT data type imposes a 16 MB size limit on individual rows.