60 Snowflake Certification Practice Questions: Master Data Warehousing

Welcome to your Snowflake Test-25

Name

Phone

Q1. What effect does WAIT_FOR_COMPLETION = TRUE have when running an ALTER WAREHOUSE command and changing the warehouse size?

Select 1 option(s):

It does not return from the command until the warehouse has finished changing its size.

The warehouse size does not change until all queries currently in the warehouse queue have completed.

The warehouse size does not change until all queries currently running in the warehouse have completed.

The warehouse size does not change until the warehouse is suspended and restarted.

Q2. Which of the following practices are recommended when creating a user in Snowflake? (Choose two.)

Select 2 option(s):

Set a default role for the user.

Set the user‘s access to expire within a specified timeframe.

Force an immediate password change.

Set the number of minutes to unlock to 15 minutes.

Configure the user to be initially disabled.

Q3. Which of the following can be executed/called with Snowpipe?

Select 1 option(s):

A stored procedure

A single INSERT_INTO statement

A User Defined Function (UDF)

A single COPY_INTO statement

Q4. A user has an application that writes a new file to a cloud storage location every 5 minutes.

What would be the MOST efficient way to get the files into Snowflake?

Select 1 option(s):

Create a task that runs a GET operation to intermittently check for new files.

Set up cloud provider notifications on the file location and use Snowpipe with auto-ingest.

Create a task that PUTS the files in an internal stage and automate the data loading wizard.

Create a task that runs a COPY INTO operation from an external stage every 5 minutes.

Q5. What happens to the underlying table data when a CLUSTER BY clause is added to a Snowflake table?

Select 1 option(s):

Smaller micro-partitions are created for common data values to allow for more parallelism

Data may be colocated by the cluster key within the micro-partitions to improve pruning performance

Larger micro-partitions are created for common data values to reduce the number of partitions that must be scanned

Data is hashed by the cluster key to facilitate fast searches for common data values

Q6. Which ACCOUNT_USAGE views are used to evaluate the details of dynamic data masking? (Choose two.)

Select 2 option(s):

QUERY_HISTORY

RESOURCE_MONITORS

ROLES

ACCESS_HISTORY

POLICY_REFERENCES

MASKING_POLICIES

Q7. Which of the following are considerations when using a directory table when working with unstructured data? (Choose two.)

Select 2 option(s):

Directory tables do not have their own grantable privileges.

A directory table will be automatically added to a stage.

Directory tables store data file metadata.

Directory table data can not be refreshed manually.

A directory table is a separate database object.

Q8. Which privilege must be granted to a share to allow secure views the ability to reference data in multiple databases?

Select 1 option(s):

SHARE on databases and schemas

CREATE_SHARE on the account

REFERENCE_USAGE on databases

SELECT on tables used by the secure view

Q9. On which of the following cloud platforms can a Snowflake account be hosted? (Choose three.)

Select 3 option(s):

Google Cloud Platform

Amazon Web Services

Microsoft Azure Cloud

Private Virtual Cloud

Oracle Cloud

Alibaba Cloud

Q10. What is the default character set used when loading CSV files into Snowflake?

Select 1 option(s):

UTF-8

UTF-16

ANSI_X3.4

ISO 8859-1

Q11. What happens when a Data Provider revokes privileges to a share on an object in their source database?

Select 1 option(s):

The Data Consumers stop seeing data updates and become responsible for storage charges for the object.

The object immediately becomes unavailable for all Data Consumers.

Any additional data arriving after this point in time will not be visible to Data Consumers.

A static copy of the object at the time the privilege was revoked is created in the Data Consumers account.

Q12. Which data types are supported by Snowflake when using semi-structured data? (Choose two.)

Select 2 option(s):

ARRAY

QUEUE

VARRAY

VARIANT

STRUCT

Q13. A company needs to read multiple terabytes of data for an initial load as part of a Snowflake migration. The company can control the number and size of CSV extract files.

How does Snowflake recommend maximizing the load performance?

Select 1 option(s):

Use auto-ingest Snowpipes to load large files in a serverless model.

Produce a larger number of smaller files and process the ingestion with size Small virtual warehouses.

Use an external tool to issue batched row-by-row inserts within BEGIN TRANSACTION and COMMIT commands.

Produce the largest files possible, reducing the overall number of files to process.

Q14. Which of the following is the Snowflake Account_Usage.Metering_History view used for?

Select 1 option(s):

Calculating the funds left on an account‘s contract

Compiling an account‘s average cloud services cost over the previous month

Gathering the hourly credit usage for an account

Summarizing the throughput of Snowpipe costs for an account

Q15. Network policies can be set at which Snowflake levels? (Choose two.)

Select 2 option(s):

Tables

Database

Role

Schema

User

Account

Q16. Which statements reflect key functionalities of a Snowflake Data Exchange? (Choose two.)

Select 2 option(s):

If an account is enrolled with a Data Exchange, it will lose its access to the Snowflake Marketplace.

A Data Exchange allows accounts to share data with third, non-Snowflake parties.

Data Exchange functionality is available by default in accounts using the Enterprise edition or higher.

A Data Exchange allows groups of accounts to share data privately among the accounts.

The sharing of data in a Data Exchange is bidirectional. An account can be a provider for some datasets and a consumer for others.

Q17. A company strongly encourages all Snowflake users to self-enroll in Snowflake‘s default Multi-Factor Authentication (MFA) service to provide increased login security for users connecting to Snowflake.

Which application will the Snowflake users need to install on their devices in order to connect with MFA?

Select 1 option(s):

Microsoft Authenticator

Google Authenticator

Okta Verify

Duo Mobile

Q18. What features does Snowflake Time Travel enable?

Select 1 option(s):

Analyzing data usage/manipulation over all periods of time

Querying data-related objects that were created within the past 365 days

Conducting point-in-time analysis for BI reporting

Restoring data-related objects that have been deleted within the past 90 days

Q19. Which SQL command can be used to see the CREATE definition of a masking policy?

Select 1 option(s):

DESCRIBE MASKING POLICY

GET_DDL

SHOW MASKING POLICIES

LIST MASKING POLICIES

Q20. Which identity providers are valid type values for federated authentication on the SAML_IDENTITY_PROVIDER parameter? (Choose two.)

Select 2 option(s):

Okta

PingFederate

Microsoft Active Directory Federation Services (AD FS)

OAuth

Identity Access Management (IAM)

Users

Q21. As a best practice, all custom roles should be granted to which system-defined role?

Select 1 option(s):

SYSADMIN

ACCOUNTADMIN

ORGADMIN

SECURITYADMIN

Q22. What is SnowSQL?

Select 1 option(s):

Snowflake‘s proprietary extension of the ANSI SQL standard, including built-in keywords and system functions.

Snowflake‘s new user interface where users can visualize data into charts and dashboards.

Snowflake‘s library that provides a programming interface for processing data on Snowflake without moving it to the system where the application code runs.

Snowflake‘s command line client built on the Python connector which is used to connect to Snowflake and execute SQL.

Q23. What is the difference between a stored procedure and a User-Defined Function (UDF)?

Select 1 option(s):

Values returned by a stored procedure can be used directly in a SQL statement while the values returned by a UDF cannot.

Returning a value is required in a stored procedure while returning values in a UDF is optional.

Stored procedures can execute database operations while UDFs cannot.

Multiple stored procedures can be called as part of a single executable statement while a single SQL statement can only call one UDF at a time.

Q24. Which function will return a row for each for each object in a VARIANT, OBJECT, or ARRAY column?

Select 1 option(s):

PARSE_JSON

GET

FLATTEN

CAST

Q25. A Snowflake user wants to unload data from a relational table sized 5 GB using CSV. The extract needs to be as performant as possible.

What should the user do?

Select 1 option(s):

Use a regular expression in the stage specification of the COPY command to restrict parsing time.

Leave the default MAX_FILE_SIZE to 16 MB to take advantage of parallel operations.

Use Parquet as the unload file format, using Parquet‘s default compression feature.

Increase the default MAX_FILE_SIZE to 5 GB and set SINGLE = true to produce a single file.

Q26. A custom role owns multiple tables. If this role is dropped from the system, who becomes the owner of these tables?

Select 1 option(s):

Tables will be standalone or orphaned.

ACCOUNTADMIN

SYSADMIN

The role that dropped the custom role.

Q27. Which chart type does Snowsight support to visualize worksheet data?

Select 1 option(s):

Bubble chart

Pie chart

Box plot

Scatterplot

Q28. Which function produces a lateral view of a VARIANT column?

Select 1 option(s):

GET

PARSE_JSON

GET_PATH

FLATTEN

Q29. Which command should be used when loading many flat files into a single table?

Select 1 option(s):

MERGE

COPY INTO

PUT

INSERT

Q30. What JavaScript delimiters are available in Snowflake stored procedures? (Choose two.)

Select 2 option(s):

Single quote (’)

Double quotes (“)

Double forward slash (//)

Double backslash (\\)

Double dollar sign ($$)

Q31. Which parameter prevents streams on tables from becoming stale?

Select 1 option(s):

LOCK_TIMEOUT

MIN_DATA_RETENSION_TIME_IN_DAYS

MAX_DATA_EXTENSION_TIME_IN_DAYS

STALE_AFTER

Q32. How is the hierarchy of database objects organized in Snowflake?

Select 1 option(s):

A database consists of one or more schemas. A schema contains tables and views.

A schema consists of one or more databases. A database contains tables, views, and warehouses.

A schema consists of one or more databases. A database contains tables and views.

A database consists of one of more schemas and warehouses. A schema contains tables and views.

Q33. Which Snowflake objects track DML changes made to tables, like inserts, updates, and deletes?

Select 1 option(s):

Streams

Procedures

Tasks

Pipes

Q34. Why should a user select the economy scaling policy for a multi-cluster warehouse?

Select 1 option(s):

To conserve credits by keeping running clusters fully loaded

To prevent/minimize query queuing

To reduce queuing concurrent user queries

To increase performance of the clusters

Q35. Several users are using the same virtual warehouse. The users report that the queries are running slowly, and that many queries are being queued.

What is the recommended way to resolve this issue?

Select 1 option(s):

Reduce the warehouse AUTO_SUSPEND parameter.

Increase the warehouse MAX_CLUSTER_COUNT parameter.

Reduce the warehouse STATEMENT_QUEUED_TIMEOUT_IN SECONDS parameter.

Increase the warehouse MAX_CONCURRENCY_LIMIT parameter.

Q36. Which user object property requires contacting Snowflake Support in order to set a value for it?

Select 1 option(s):

MINS_TO_UNLOCK

MINS_TO_BYPASS_MFA

DISABLED

MINS_TO_BYPASS_NETWORK_POLICY

Q37. Why do Snowflake’s virtual warehouses have scaling policies?

Select 1 option(s):

To help control the credits consumed by a multi-cluster warehouse running in maximized mode

To help save extra storage costs

To help increase the performance of serverless computing features

To help control the credits consumed by a multi-cluster warehouse running in auto-scale mode

Q38. What actions does the use of the PUT command do automatically? (Choose two.)

Select 2 option(s):

It encrypts the file data in transit.

It creates an empty target table.

It uses the last stage created.

It creates a file format object.

It compresses all files using GZIP.

Q39. Which semi-structured data formats can be loaded into Snowflake with a COPY command? (Choose two.)

Select 2 option(s):

XML

HTML

ORC

EDI

CSV

Q40. If a virtual warehouse runs for 30 seconds after it is provisioned, how many seconds will the customer be billed for?

Select 1 option(s):

60 seconds

1 hour

121 seconds

30 seconds

Q41. Which result shows efficient pruning?

Select 1 option(s):

Partitions scanned is greater than partitions total.

Partitions scanned is less than partitions total.

Partitions scanned is greater than or equal to the partitions total.

Partitions scanned is equal to the partitions total.

Q42. How does a Snowflake user execute an anonymous block of code?

Select 1 option(s):

The SUBMIT command must run immediately after the block is defined

The block must be saved to a worksheet and executed using a connector.

The user must run the CALL command to execute the block.

The statements that define the block must also execute the block.

Q43. A user wants to add additional privileges to the system-defined roles for their virtual warehouse.

How does Snowflake recommend they accomplish this?

Select 1 option(s):

Grant the additional privileges to a custom role.

Grant the additional privileges to the SYSADMIN role.

Grant the additional privileges to the ORGADMIN role.

Grant the additional privileges to the ACCOUNTADMIN role.

Q44. Which function is used to profile warehouse credit usage?

Select 1 option(s):

MATERIALIZED_VIEW_REFRESH_HISTORY

AUTOMATIC_CLUSTERING_HISTORY

WAREHOUSE_LOAD_HISTORY

WAREHOUSE_METERING_HISTORY

Q45. How can a Snowflake user share data with another user who does not have a Snowflake account?

Select 1 option(s):

Grant the READER privilege to the database that is going to be shared

Share the data by implementing User-Defined Functions (UDFs)

Move the Snowflake account to a region where data sharing is enabled

Create a reader account and create a share of the data

Q46. A JSON object is loaded into a column named data using a Snowflake variant datatype. The root node of the object is BIKE. The child attribute for this root node is BIKEID.

Which statement will allow the user to access BIKEID?

Select 1 option(s):

Q47. What are the main differences between the account usage views and the information schema views? (Choose two.)

Select 2 option(s):

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

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

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

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

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

Q48. Which command would return an empty sample?

Select 1 option(s):

Q49. What type of function can be used to estimate the approximate number of distinct values from a table that has trillions of rows?

Select 1 option(s):

MD5

Window

HyperLogLog (HLL)

External

Q50. A user needs to MINIMIZE the cost of large tables that are used to store transitory data. The data does not need to be protected against failures, because the data can be reconstructed outside of Snowflake.

What table type should be used?

Select 1 option(s):

Temporary

Transient

Permanent

External

Q51. Where can a Snowflake user find the query history in Snowsight?

Select 1 option(s):

Dashboards

Data

Admin

Activity

Q52. Which function returns the URL of a stage using the stage name as the input?

Select 1 option(s):

BUILD_SCOPED_FILE_URL

GET_STAGE_LOCATION

GET_PRESIGNED_URL

BUILD_STAGE_FILE_URL

Q53. Snowflake Partner Connect is limited to users with a verified email address and which role?

Select 1 option(s):

SECURITYADMIN

USERADMIN

SYSADMIN

ACCOUNTADMIN

Q54. What does it mean when the sample function uses the Bernoulli sampling method?

Select 1 option(s):

The data is based on sampling 1000 rows of the source data.

The data is based on sampling 10% of the source data.

The data is based on sampling every row.

The data is based on sampling blocks of the source data.

Q55. Which virtual warehouse privilege is required to view a load-monitoring chart?

Select 1 option(s):

USAGE

MODIFY

OPERATE

MONITOR

Q56. Which view will return users who have queried a table?

Select 1 option(s):

SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY

SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES

SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENT_HISTORY

SNOWFLAKE.ACCOUNT_USAGE.COLUMNS

Q57. Which constraint type is enforced in Snowflake from the ANSI SQL standard?

Select 1 option(s):

FOREIGN KEY

PRIMARY KEY

NOT NULL

UNIQUE

Q58. What is the default value in the Snowflake Web Interface (UI) for auto suspending a Virtual Warehouse?

Select 1 option(s):

5 minutes

15 minutes

10 minutes

1 minute

Q59. Which role can execute the SHOW ORGANIZATION ACCOUNTS command successfully?

Select 1 option(s):

SECURITYADMIN

USERADMIN

ORGADMIN

ACCOUNTADMIN

Q60. What is the recommended way to obtain a cloned table with the same grants as the source table?

Select 1 option(s):

Use an ALTER TABLE command to copy the grants.

Clone the table with the COPY GRANTS command.

Clone the schema then drop the unwanted tables.

Create a script to extract grants and apply them to the cloned table.