Snowflake Certification Practice Quiz: 60 Essential Questions

Welcome to your Snowflake Test-23

Name

Phone

Q1. While using a COPY command with a Validation_mode parameter, which of the following statements will return an error?

Select 1 option(s):

Statements that insert a duplicate record during a load

Statements that transform data during a load

Statements that have a specific data type in the source

Statements that have duplicate file names

Q2. Snowflake’s hierarchical key mode includes which keys? (Choose two.)

Select 2 option(s):

Secure view keys

Schema master keys

Database master keys

File keys

Account master keys

Q3. The bulk data load history that is available upon completion of the COPY statement is stored where and for how long?

Select 1 option(s):

In the metadata of the pipe for 64 days

In the metadata of the pipe for 14 days

In the metadata of the target table for 64 days

In the metadata of the target table for 14 days

Q4. Which SQL command can be used to verify the privileges that are granted to a role?

Select 1 option(s):

SHOW GRANTS FOR ROLE

SHOW ROLES

SHOW GRANTS TO ROLE

SHOW GRANTS ON ROLE

Q5. What happens when the values for both an ALLOWED_IP_LIST and a BLOCKED_IP_LIST are used in a network policy?

Select 1 option(s):

Snowflake ignores the ALLOWED_IP_LIST first.

Snowflake applies the ALLOWED_IP_LIST first.

Snowflake applies the BLOCKED_IP_LIST first.

Snowflake ignores the BLOCKED_IP_LIST first.

Q6. Which cache type is used to cache data output from SQL queries?

Select 1 option(s):

Remote cache

Local file cache

Result cache

Metadata cache

Q7. Which commands should be used to grant the privilege allowing a role to select data from all current tables and any tables that will be created later in a schema? (Choose two.)

Select 2 option(s):

grant SELECT on all tables in schema DB1.SCHEMA to role MYROLE;

grant SELECT on future tables in database DB1 to role MYROLE;

grant USAGE on future tables in schema DB1.SCHEMA to role MYROLE;

grant USAGE on all tables in schema DB1.SCHEMA to role MYROLE;

grant SELECT on all tables in database DB1 to role MYROLE;

grant SELECT on future tables in schema DB1.SCHEMA to role MYROLE;

Q8. Which of the following SQL statements will list the version of the drivers currently being used?

Select 1 option(s):

Q9. Which schema has the RESOURCE_MONITORS view?

Select 1 option(s):

INFORMATION_SCHEMA

ACCOUNT_USAGE

WAREHOUSE_USAGE_SCHEMA

READER_ACCOUNT_USAGE

Q10. What is the output of the command.

SELECT TOP 100 AGE
FROM USERS;

Select 1 option(s):

The TOP 100 grades in descendent order.

Non-deterministic list of 100 grades.

The TOP 100 grades in ascendent order.

The TOP 100 grades ordered by the creation date of the data.

Q11. What actions will prevent leveraging of the ResultSet cache?

Select 1 option(s):

Executing the RESULTS_SCAN() table function

Stopping the virtual warehouse that the query is running against

If the result has not been reused within the last 12 hours

Removing a column from the query SELECT list

Q12. A user executes the following SQL query:

create table SALES_BKP like SALES;
What are the cost implications for processing this query?

Select 1 option(s):

Storage costs will be generated based on the size of the data.

The cost for running the virtual warehouse will be charged by the second.

Processing costs will be generated based on how long the query takes.

No costs will be incurred as the query will use metadata.

Q13. Which of the following are best practice recommendations that should be considered when loading data into Snowflake? (Choose two.)

Select 2 option(s):

Load files that are approximately 25 MB or smaller.

Remove semi-structured data types.

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

Remove all dates and timestamps.

Avoid using embedded characters such as commas for numeric data types.

Q14. When can a newly configured virtual warehouse start running SQL queries?

Select 1 option(s):

After the warehouse replication is completed

When the warehouse provisioning is completed

During the time slots defined by the ACCOUNTADMIN

After 50% of the warehouse provisioning has completed

Q15. Which of the following indicates that it may be appropriate to use a clustering key for a table? (Choose two.)

Select 2 option(s):

The clustering depth for the table is large.

The table contains a column that has very low cardinality.

DML statements that are being issued against the table are blocked.

Queries on the table are running slower than expected.

The table has a small number of micro-partitions.

Q16. Which parameter allows us to schedule the task_1 to run every day with a CRON expression?

Select 1 option(s):

SET SCHEDULE

SET INITIALIZATION

SET FIXED_TIME

SET CRON

Q17. Snowflake‘s approach to the management of system access combines which of the following models? (Choose two.)

Select 2 option(s):

Discretionary Access Control (DAC)

Identity Access Management (IAM)

Mandatory Access Control (MAC)

Create, Read, Update, and Delete (CRUD)

Security Assertion Markup Language (SAML)

Role-Based Access Control (RBAC)

Q18. How many credits will consume a medium-size warehouse with 2 clusters running in maximized mode for 3 hours?

Select 1 option(s):

Q19. Which command can we use to refresh a materialized view?

Select 1 option(s):

ALTER MATERIALIZED_VIEW REFRESH=TRUE

Materialized views are automatically refreshed by Snowflake.

DROP VIEW ;

CREATE MATERIALIZED VIEW ;

RESTART MATERIALIZED_VIEW

Q20. Which of the following operations require the use of a running virtual warehouse? (Choose two.)

Select 2 option(s):

Executing a stored procedure

Altering a table

Listing files in a stage

Querying data from a materialized view

Downloading data from an internal stage

Q21. How is the MANAGE GRANTS privilege applied?

Select 1 option(s):

At the table level

Globally

At the database level

At the schema level

Q22. What does the orange bar on an operator represent when reviewing the Query Profile?

Snowflake SecureView usage dashboard with 66.7% progress bar.
Select 1 option(s):

The cost of the operator in terms of the virtual warehouse CPU utilization.

The fraction of time that this operator consumed within the query step.

A measure of progress of the operator‘s execution.

The fraction of data scanned from cache versus remote disk for the operator.

Q23. What tasks can be completed using the COPY command? (Choose two.)

Select 2 option(s):

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

Columns can be reordered.

Columns can be aggregated.

Columns can be joined with an existing table.

Columns can be omitted.

Q24. What happens to the shared objects for users in a consumer account from a share, once a database has been created in that account?

Select 1 option(s):

The shared objects become accessible.

The shared objects are transferred.

The shared objects can be re-shared.

The shared objects are copied.

Q25. What will happen to ALTER a column setting it to NOT NULL if it contains NULL values?

Select 1 option(s):

NULL values are changed to 0.

Snowflake deletes the rows with NULL values.

Snowflake returns an error.

NULL values are changed to an empty string “ “

Q26. At which point is data encrypted when using a PUT command?

Select 1 option(s):

Before it is sent from the user‘s machine

When it gets micro-partitioned

After it reaches the internal stage

When it reaches the virtual warehouse

Q27. Which of the following Snowflake capabilities are available in all Snowflake editions? (Choose two.)

Select 2 option(s):

Customer-managed encryption keys through Tri-Secret Secure

Object-level access control

Automatic encryption of all data

Column-level security to apply data masking policies to tables and views

Up to 90 days of data recovery through Time Travel

Q28. You have the following data in a variant column called “person_data” from the table “myTable”. How can you query the second hobby of Chris (“music”)?

{
“name“: “Chris“,
“favouriteTechnology“: Snowflake,
“hobbies“:[
{“name“: “soccer“},
{“name“: “music“},
{“name“: “hiking“}
]}

Select 1 option(s):

SELECT person _ data.hobbies( 1 )

SELECT person _ data.hobbies[ 1 ]

SELECT person _ data.hobbies[ 1 ].name

SELECT person _ data.hobbies( 1 ).name

Q29. Masking policies can be applied to which of the following Snowflake objects? (Choose two.)

Select 2 option(s):

A stored procedure

A pipe

A stream

A materialized view

A User-Defined Function (UDF)

A table

Q30. For non-materialized views, what column in Information Schema and Account Usage identifies whether a view is secure or not?

Select 1 option(s):

IS_UPDATEABLE

TABLE_NAME

IS_SECURE

CHECK_OPTION

Q31. Which Snowflake URL type is used by directory tables?

Select 1 option(s):

Virtual-hosted style

File

Scoped

Pre-signed

Q32. Which is the MINIMUM required Snowflake edition that a user must have if they want to use AWS/Azure Privatelink or Google Cloud Private Service Connect?

Select 1 option(s):

Premium

Standard

Business Critical

Enterprise

Q33. Which statements are true concerning Snowflake’s underlying cloud infrastructure? (Choose three.)

Select 3 option(s):

Snowflake data and services are available in a single cloud provider and a single region; the use of multiple cloud providers is not supported.

Snowflake data and services are deployed in at least three availability zones within a cloud provider’s region.

Snowflake can be deployed in a customer’s private cloud using the customer’s own compute and storage resources for Snowflake compute and storage.

Snowflake data and services are deployed in a single availability zone within a cloud provider’s region.

Snowflake uses the core compute and storage services of each cloud provider for its own compute and storage.

All three layers of Snowflake’s architecture (storage, compute, and cloud services) are deployed and managed entirely on a selected cloud platform.

Q34. Which data type can be used to store geospatial data in Snowflake?

Select 1 option(s):

Object

Variant

Geometry

It is not possible to store Geospatial data in Snowflake.

Q35. Which types of tables typically benefit from creating a cluster key?

Select 1 option(s):

Large tables (around 100GB of data)

Very large tables (multi-terabytes of data)

Medium tables (around 10GB of data)

Small tables (around 1GB of data)

Q36. Which functionality is not provided by the query profile?

Select 1 option(s):

Details and statistics for the overall query.

Statistics for each component of the query.

Graphical representation of the main components of the processing plan of the query.

Hints for improving the query performance.

Q37. What is the recommended file sizing for data loading using Snowpipe?

Select 1 option(s):

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

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

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

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

Q38. Which of these commands require a running warehouse?

Select 1 option(s):

Q39. What is the purpose of enabling Federated Authentication on a Snowflake account?

Select 1 option(s):

Allows dual Multi-Factor Authentication (MFA) when connecting to Snowflake

Allows users to connect using secure single sign-on (SSO) through an external identity provider

Forces users to connect through a secure network proxy

Disables the ability to use key pair and basic authentication (e.g., username/password) when connecting

Q40. What is used to limit the credit usage of a virtual warehouse within a Snowflake account?

Select 1 option(s):

Load monitor

Stream

Resource monitor

Query Profile

Q41. How many days is load history for Snowpipe retained?

Select 1 option(s):

14 days

1 day

64 days

7 days

Q42. Which SQL command, when committed, will consume a stream and advance the stream offset?

Select 1 option(s):

Q43. What are advantages clones have over tables created with CREATE TABLE AS SELECT statement? (Choose two.)

Select 2 option(s):

The clone is created almost instantly.

The clone saves space by not duplicating storage.

The clone always stays in sync with the original table.

The clone has better query performance.

The clone will have time travel history from the original table.

Q44. Which of the following activities consume virtual warehouse credits in the Snowflake environment? (Choose two.)

Select 2 option(s):

Running a custom query

Cloning a database

Running COPY commands

Caching query results

Running EXPLAIN and SHOW commands

Q45. A marketing co-worker has requested the ability to change a warehouse size on their medium virtual warehouse called MKTG_WH.

Which of the following statements will accommodate this request?

Select 1 option(s):

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

Q46. What affects whether the query results cache can be used?

Select 1 option(s):

If the referenced data in the table has changed

If multiple users are using the same virtual warehouse

If the query contains a deterministic function

If the virtual warehouse has been suspended

Q47. Assume there is a table consisting of five micro-partitions with values ranging from A to Z.

Which diagram indicates a well-clustered table?

A)
Snowflake data platform concepts quiz illustration for data engineers and SynergisticIT training.
B)
Snowflake data platform practice quiz illustration, data loading and virtual warehouse concepts.
C)
Snowflake data platform quiz questions and answers for data engineers and certification preparation.
D)
Snowflake data platform concept quiz illustration for data engineers and SynergisticIT training.

Select 1 option(s):

Q48. What are the correct parameters for time travel and fail-safe in the Snowflake Enterprise Edition?

Select 1 option(s):

Default Time Travel Retention is set to 90 days. Maximum Time Travel Retention is 7 days. Fail Safe retention time is 356 days.

Default Time Travel Retention is set to 0 days. Maximum Time Travel Retention is 30 days. Fail Safe retention time is 1 day.

Default Time Travel Retention is set to 7 days. Maximum Time Travel Retention is 1 day. Fail Safe retention time is 90 days.

Default Time Travel Retention is set to 1 day. Maximum Time Travel Retention is 365 days. Fail Safe retention time is 7 days.

Default Time Travel Retention is set to 1 day. Maximum Time Travel Retention is 90 days. Fail Safe retention time is 7 days.

Default Time Travel Retention is set to 0 days. Maximum Time Travel Retention is 90 days. Fail Safe retention time is 7 days.

Q49. Which of the following is a valid source for an external stage when the Snowflake account is located on Microsoft Azure?

Select 1 option(s):

A Windows server file share on Azure

An FTP server with TLS encryption

An HTTPS server with WebDAV

A Google Cloud storage bucket

Q50. Which of the following objects are contained within a schema? (Choose two.)

Select 2 option(s):

Stream

User

External table

Role

Warehouse

Q51. Which feature allows a user the ability to control the organization of data in a micro-partition?

Select 1 option(s):

Search Optimization Service

Horizontal Partitioning

Automatic Clustering

Range Partitioning

Q52. If a Snowflake user decides a table should be clustered, what should be used as the cluster key?

Select 1 option(s):

The columns with very high cardinality.

The columns with many different values.

The columns that are queried in the select clause.

The columns most actively used in the select filters.

Q53. Which of the following statements about data sharing are true? (Choose two.)

Select 2 option(s):

New objects created by a Data Provider are automatically shared with existing Data Consumers and Reader Accounts.

Reader Accounts are charged for warehouse usage.

All database objects can be included in a shared database.

Reader Accounts are created by Data Providers.

Shared databases are read-only.

Q54. How long is Snowpipe data load history retained?

Select 1 option(s):

As configured in the CREATE PIPE settings

14 days

64 days

Until the pipe is dropped

Q55. What is the recommended compressed file size range for continuous data loads using Snowpipe?

Select 1 option(s):

100-250 MB

8-16 MB

10-99 MB

16-24 MB

Q56. What Snowflake role must be granted for a user to create and manage accounts?

Select 1 option(s):

ACCOUNTADMIN

ORGADMIN

SYSADMIN

SECURITYADMIN

Q57. What features that are part of the Continuous Data Protection (CDP) feature set in Snowflake do not require additional configuration? (Choose two.)

Select 2 option(s):

Time Travel

External tokenization

Data encryption

Row level access policies

Data masking policies

Q58. Which Snowflake function will interpret an input string as a JSON document, and produce a VARIANT value?

Select 1 option(s):

flatten

object_construct()

json_extract_path_text()

parse_json()

Q59. A Snowflake Administrator needs to ensure that sensitive corporate data in Snowflake tables is not visible to end users, but is partially visible to functional managers.

How can this requirement be met?

Select 1 option(s):

Use data encryption.

Revoke all roles for functional managers and end users.

Use dynamic data masking.

Use secure materialized views.

Q60. Query parsing and compilation occurs in which architecture layer of the Snowflake Cloud Data Platform?

Select 1 option(s):

Compute layer

Cloud services layer

Storage layer

Cloud agnostic layer