Snowflake Certification Practice Test: Advance Your Data Skills

Welcome to your Snowflake Test-24

Name

Phone

Q1. What is the purpose of multi-cluster virtual warehouses?

Select 1 option(s):

To allow the warehouse to resize automatically

To create separate data warehouses to increase query optimization

To eliminate or reduce queuing of concurrent queries

To allow users the ability to choose the type of compute nodes that make up a virtual warehouse cluster

Q2. Which command can be used to load data into an internal stage?

Select 1 option(s):

PUT

GET

LOAD

COPY

Q3. A user needs to create a materialized view in the schema MYDB.MYSCHEMA.

Which statements will provide this access?

Select 1 option(s):

GRANT ROLE MYROLE TO USER USER1;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER1;

GRANT ROLE MYROLE TO USER USER1;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO ROLE MYROLE;

GRANT ROLE MYROLE TO USER USER1;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER USER1;

GRANT ROLE MYROLE TO USER USER1;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO MYROLE;

Q4. When loading data into Snowflake, the COPY command supports which of the following?

Select 1 option(s):

Filters

Aggregates

Joins

Column reordering

Q5. Which command is used to unload data from a Snowflake table into a file in a stage?

Select 1 option(s):

COPY INTO

WRITE

EXTRACT INTO

GET

Q6. What is the minimum Snowflake edition needed for database failover and fail-back between Snowflake accounts for business continuity and disaster recovery?

Select 1 option(s):

Enterprise

Standard

Virtual Private Snowflake

Business Critical

Q7. What file formats does Snowflake support for loading semi-structured data? (Choose three.)

Select 3 option(s):

JSON

PDF

TSV

Parquet

JPEG

Avro

Q8. Which of the following commands cannot be used within a reader account?

Select 1 option(s):

SHOW SCHEMAS

CREATE SHARE

DROP ROLE

DESCRIBE TABLE

ALTER WAREHOUSE

Q9. Where is Snowflake metadata stored?

Select 1 option(s):

Within the data files

In the cloud services layer

In the virtual warehouse layer

In the remote storage layer

Q10. Where would a Snowflake user find information about query activity from 90 days ago?

Select 1 option(s):

information_schema.query_history_by_session view

information_schema.query_history view

account_usage.query_history view

account_usage.query_history_archive view

Q11. Which minimum Snowflake edition allows for a dedicated metadata store?

Select 1 option(s):

Business Critical

Enterprise

Virtual Private Snowflake

Standard

Q12. Which snowflake objects will incur both storage and cloud compute charges? (Choose two.)

Select 2 option(s):

Secure view

Clustered table

Sequence

Transient table

Materialized view

Q13. Why does Snowflake recommend file sizes of 100-250 MB compressed when loading data?

Select 1 option(s):

Increases the latency staging and accuracy when loading the data

Allows optimization of parallel operations

Allows a user to import the files in a sequential order

Optimizes the virtual warehouse size and multi-cluster setting to economy mode

Q14. How are serverless features billed?

Select 1 option(s):

Serverless features are not billed, unless the total cost for the month exceeds 10% of the warehouse credits, on the account

Per second multiplied by the size, as determined by the SERVERLESS_FEATURES_SIZE account parameter

Per minute multiplied by an automatic sizing for the job, with a minimum of one minute

Per second multiplied by an automatic sizing for the job

Q15. Which statements are true of micro-partitions? (Choose two.)

Select 2 option(s):

They are only encrypted in the Enterprise edition and above

They are immutable

They are stored compressed only if COMPRESS=TRUE on Table

They are approximately 50-500MB, before compression

They are approximately 50-500MB, after compression

Q16. A table needs to be loaded. The input data is in JSON format and is a concatenation of multiple JSON documents. The file size is 3 GB. A warehouse size S is being used.

The following COPY INTO command was executed:
COPY INTO SAMPLE FROM @~/SAMPLE.JSON (TYPE=JSON)
The load failed with this error:
Max LOB size (16777216) exceeded, actual size of parsed column is 17894470.
How can this issue be resolved?

Select 1 option(s):

Split the file into multiple files in the recommended size range (100 MB - 250 MB).

Compress the file and load the compressed file.

Set STRIP_OUTER_ARRAY=TRUE in the COPY INTO command.

Use a larger-sized warehouse.

Q17. A user is preparing to load data from an external stage.

Which practice will provide the MOST efficient loading performance?

Select 1 option(s):

Use pattern matching for regular expression execution

Store the files on the external stage to ensure caching is maintained

Organize files into logical paths

Load the data in one large file

Q18. Which statement MOST accurately describes clustering in Snowflake?

Select 1 option(s):

The database ACCOUNTADMIN must define the clustering methodology for each Snowflake table.

The clustering key must be included in the COPY command when loading data into Snowflake.

Clustering can be disabled within a Snowflake account.

Clustering is the way data is grouped together and stored within Snowflake micro-partitions.

Q19. Which Snowflake layer is always leveraged when accessing a query from the result cache?

Select 1 option(s):

Metadata

Compute

Cloud Services

Data Storage

Q20. A data provider wants to share data with a consumer who does not have a Snowflake account. The provider creates a reader account for the consumer following these steps:

1. Created a user called “CONSUMER“
2. Created a database to hold the share and an extra-small warehouse to query the data
3. Granted the role PUBLIC the following privileges: Usage on the warehouse, database, and schema, and SELECT on all the objects in the share
Based on this configuration what is true of the reader account?

Select 1 option(s):

The reader account can clone data the provider has shared, but cannot re-share it.

The reader account compute will be billed to the provider account.

The reader account will automatically use the Standard edition of Snowflake.

The reader account can create a copy of the shared data using CREATE TABLE AS...

Q21. Which of the following conditions must be met in order to return results from the results cache? (Choose two.)

Select 2 option(s):

The query includes a User Defined Function (UDF).

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

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

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

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

Q22. Which query profile statistics help determine if efficient pruning is occurring? (Choose two.)

Select 2 option(s):

Bytes spilled to local storage

Bytes sent over network

Partitions scanned

Percentage scanned from cache

Partitions total

Q23. Which of the following can be used when unloading data from Snowflake? (Choose two.)

Select 2 option(s):

When unloading semi-structured data, it is recommended that the STRIP_OUTER_ARRAY option be used.

The OBJECT_CONSTRUCT function can be used to convert relational data to semi-structured data.

Use the PARSE_JSON function to ensure structured data will be unloaded into the VARIANT data type.

Use the ENCODING file format option to change the encoding from the default UTF-8.

By using the SINGLE = TRUE parameter, a single file up to 5 GB in size can be exported to the storage layer.

Q24. Which columns are part of the result set of the Snowflake LATERAL FLATTEN command? (Choose two.)

Select 2 option(s):

INDEX

CONTENT

DATATYPE

PATH

BYTE_SIZE

Q25. Which methods can be used to delete staged files from a Snowflake stage? (Choose two.)

Select 2 option(s):

Specify the PURGE copy option in the COPY INTO command.

Use the DROP command after the load completes.

Use the REMOVE command after the load completes.

Specify the TEMPORARY option when creating the file format.

Use the DELETE LOAD HISTORY command after the load completes.

Q26. Which privilege is required for a role to be able to resume a suspended warehouse if auto-resume is not enabled?

Select 1 option(s):

OPERATE

MODIFY

MONITOR

USAGE

Q27. Which stage type can be altered and dropped?

Select 1 option(s):

External stage

Database stage

User stage

Table stage

Q28. A materialized view should be created when which of the following occurs? (Choose two.)

Select 2 option(s):

The results of the query do not change often and are used frequently.

The base table gets updated frequently.

The query is highly optimized and does not consume many compute resources.

There is minimal cost associated with running the query.

The query consumes many compute resources every time it runs.

Q29. Which TABLE function helps to convert semi-structured data to a relational representation?

Select 1 option(s):

PARSE_JSON

TO_JSON

CHECK_JSON

FLATTEN

Q30. Which of the following are valid methods for authenticating users for access into Snowflake? (Choose three.)

Select 3 option(s):

Federated authentication

OCSP authentication

TLS 1.2

OAuth

Key-pair authentication

SCIM

Q31. What is an advantage of using an explain plan instead of the query profiler to evaluate the performance of a query?

Select 1 option(s):

An explain plan will handle queries with temporary tables and the query profiler will not.

An explain plan can be used to conduct performance analysis without executing a query.

An explain plan‘s output will display automatic data skew optimization information.

The explain plan output is available graphically.

Q32. What internal stages are available in Snowflake? (Choose three.)

Select 3 option(s):

Named stage

Schema stage

Table stage

Stream stage

User stage

Database stage

Q33. What feature can be used to reorganize a very large table on one or more columns?

Select 1 option(s):

Clustering keys

Clustered partitions

Key partitions

Micro-partitions

Q34. What is the MINIMUM Snowflake edition required to use the periodic rekeying of micro-partitions?

Select 1 option(s):

Virtual Private Snowflake

Enterprise

Business Critical

Standard

Q35. Which of the following statements describes a schema in Snowflake?

Select 1 option(s):

A logical grouping of objects that belongs to multiple databases

A named Snowflake object that includes all the information required to share a database

A uniquely identified Snowflake account within a business entity

A logical grouping of objects that belongs to a single database

Q36. Which of the following are characteristics of security in Snowflake?

Select 1 option(s):

Private communication to internal stages is allowed in the Snowflake Enterprise edition and higher.

Support for HIPAA and GDPR compliance is available for UI Snowflake editions.

Periodic rekeying of encrypted data is available with the Snowflake Enterprise edition and higher

Account and user authentication is only available with the Snowflake Business Critical edition.

Q37. User INQUISITIVE_PERSON has been granted the role DATA_SCIENCE. The role DATA_SCIENCE has privileges OWNERSHIP on the schema MARKETING of the database ANALYTICS_DW.

Which command will show all privileges granted to that schema?

Select 1 option(s):

SHOW GRANTS ON ROLE DATA_SCIENCE

SHOW GRANTS TO USER INQUISITIVE_PERSON

SHOW GRANTS ON SCHEMA ANALYTICS_DW.MARKETING

SHOW GRANTS OF ROLE DATA_SCIENCE

Q38. Which of the following is an example of an operation that can be completed without requiring compute, assuming no queries have been executed previously?

Select 1 option(s):

Q39. Which of the following Snowflake objects can be shared using a secure share? (Choose two.)

Select 2 option(s):

Sequences

Secure User Defined Functions (UDFs)

Tables

Materialized views

Procedures

Q40. What is the default file size when unloading data from Snowflake using the COPY command?

Select 1 option(s):

8 GB

32 MB

4 MB

16 MB

Q41. Users are responsible for data storage costs until what occurs?

Select 1 option(s):

Data expires from Fail-safe

Data expires from Time Travel

Data is truncated from a table

Data is deleted from a table

Q42. What is the minimum Snowflake edition required to create a materialized view?

Select 1 option(s):

Standard Edition

Virtual Private Snowflake Edition

Business Critical Edition

Enterprise Edition

Q43. Which of the following are handled by the cloud services layer of the Snowflake architecture? (Choose two.)

Select 2 option(s):

Data loading

Security

Time Travel data

Authentication and access control

Query execution

Q44. Credit charges for Snowflake virtual warehouses are calculated based on which of the following considerations? (Choose two.)

Select 2 option(s):

The size of the virtual warehouse

The length of time the warehouse is running

The duration of the queries that are executed

The number of active users assigned to the warehouse

The number of queries executed

Q45. How often are the Account and Table master keys automatically rotated by Snowflake?

Select 1 option(s):

60 Days

30 Days

365 Days

90 Days

Q46. Which Snowflake architectural layer is responsible for a query execution plan?

Select 1 option(s):

Compute

Data storage

Cloud provider

Cloud services

Q47. What is a feature of a stored procedure in Snowflake?

Select 1 option(s):

They can access tables from a single database.

They can be created as secure and hide the underlying metadata from all users.

They can only contain a single SQL statement.

They can be created to run with a caller‘s rights or an owner‘s rights.

Q48. What are the default Time Travel and Fail-safe retention periods for transient tables?

Select 1 option(s):

Time Travel - 1 day, Failsafe - 0 days

Transient tables are retained in neither Fail-safe nor Time Travel.

Time Travel - 0 days, Fail-safe - 1 day

Time Travel - 1 day, Fail-safe - 1 day

Q49. Which URL type allows users to access unstructured data without authenticating into Snowflake or passing an authorization token?

Select 1 option(s):

File URL

Pre-signed URL

Scoped URL

Signed URL

Q50. Which of the following statements describe features of Snowflake data caching? (Choose two.)

Select 2 option(s):

A user can only access their own queries from the query result cache.

When the data cache is full, the least-recently used data will be cleared to make room.

The RESULT_SCAN table function can access and filter the contents of the query result cache.

A user must set USE_METADATA_CACHE to TRUE to use the metadata cache in queries.

When a virtual warehouse is suspended, the data cache is saved on the remote storage layer.

Q51. In which use case does Snowflake apply egress charges?

Select 1 option(s):

Database replication

Loading data into Snowflake

Data sharing within a specific region

Query result retrieval

Q52. A virtual warehouse is created using the following command:

Create warehouse my_WH with –
warehouse_size = MEDIUM
min_cluster_count = 1
max_cluster_count = 1
auto_suspend = 60
auto_resume = true;
The image below is a graphical representation of the warehouse utilization across two days.
Snowflake practice test graph illustrating data loading and performance optimization metrics.

What action should be taken to address this situation?

Select 1 option(s):

Configure the warehouse to a multi-cluster warehouse.

Increase the value for the parameter MAX_CONCURRENCY_LEVEL.

Increase the warehouse size from Medium to 2XL.

Lower the value of the parameter STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.

Q53. What is cached during a query on a virtual warehouse?

Select 1 option(s):

Any columns accessed during the query

All rows accessed during the query

The columns in the result set of the query

All columns in a micro-partition

Q54. A Snowflake user executed a query and received the results. Another user executed the same query 4 hours later. The data had not changed.

What will occur?

Select 1 option(s):

No virtual warehouse will be used, data will be read from the result cache.

No virtual warehouse will be used, data will be read from the local disk cache.

The default virtual warehouse will be used to read all data.

The virtual warehouse that is defined at the session level will be used to read all data.

Q55. How would a user run a multi-cluster warehouse in maximized mode?

Select 1 option(s):

Configure the maximum clusters setting to “Maximum.“

Set the minimum clusters and maximum clusters settings to different values.

Set the minimum Clusters and maximum Clusters settings to the same value.

Turn on the additional clusters manually after starting the warehouse.

Q56. Which statement about billing applies to Snowflake credits?

Select 1 option(s):

Credits are consumed based on the warehouse size and the time the warehouse is running.

Credits are used to pay for cloud data storage usage.

Credits are consumed based on the number of credits billed for each hour that a warehouse runs.

Credits are billed per-minute with a 60-minute minimum.

Q57. Which of the following describes how multiple Snowflake accounts in a single organization relate to various cloud providers?

Select 1 option(s):

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.

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.

Q58. When unloading to a stage, which of the following is a recommended practice or approach?

Select 1 option(s):

Avoid the use of the CAST function.

Define an individual file format.

Set SINGLE = TRUE for larger files.

Use OBJECT_CONSTRUCT(*) when using Parquet.

Q59. Which of the following compute resources or features are managed by Snowflake? (Choose two.)

Select 2 option(s):

Execute a COPY command

Snowpipe

Scaling up a warehouse

Updating data

AUTOMATIC_CLUSTERING

Q60. Which of the following features are available with the Snowflake Enterprise edition? (Choose two.)

Select 2 option(s):

Customer managed keys (Tri-secret secure)

Extended time travel

Automated index management

Native support for geospatial data

Database replication and failover