Snowflake Certification Practice Test: 60 Technical Questions

Welcome to your Snowflake Test-22

Name

Phone

Q1. Which ALTER commands will impact a column‘s availability in Time Travel?

Select 1 option(s):

ALTER TABLE … RENAME COLUMN …

ALTER TABLE … SET NOT NULL …

ALTER TABLE … DROP COLUMN …

ALTER TABLE … SET DATA TYPE …

Q2. Which statement describes pruning?

Select 1 option(s):

The ability to allow the result of a query to be accessed as if it were a table.

A service that is handled by the Snowflake Cloud Services layer to optimize caching.

The return of micro-partitions values that overlap with each other to reduce a query‘s runtime.

The filtering or disregarding of micro-partitions that are not needed to return a query.

Q3. Which Snowflake object enables loading data from files as soon as they are available in a cloud storage location?

Select 1 option(s):

Stream

Pipe

External stage

Task

Q4. What is the maximum size supported by the VARIANT column?

Select 1 option(s):

128 MB.

64 MB.

8 MB.

16 MB.

Q5. What actions are supported by Snowflake resource monitors? (Choose two.)

Select 2 option(s):

Abort

Suspend immediately

Notify

Alert

Notify and suspend

Q6. Which Snowflake partner category is represented at the top of this diagram (labeled 1)?

Select 1 option(s):

Machine Learning and Data Science

Business Intelligence

Data Integration

Q7. You have a multi-cluster warehouse running with the standard scaling policy. The maximum number of clusters is set to 8. If a lot of queries are queried, and the warehouse is constantly starting new clusters, what is the maximum time the warehouse will start all the clusters?

Select 1 option(s):

8 minutes.

They all start at the same time.

160 seconds.

80 seconds.

Q8. If all virtual warehouse resources are maximized while processing a query workload, what will happen to new queries that are submitted to the warehouse?

Select 1 option(s):

All queries will terminate when the resources are maximized.

New queries will be queued and executed when capacity is available.

The warehouse will scale out automatically

The warehouse will move to a suspended state.

Q9. A user has 10 files in a stage containing new customer data. The ingest operation completes with no errors, using the following command:

COPY INTO my_table FROM @my_stage;
The next day the user adds 10 files to the stage so that now the stage contains a mixture of new customer data and updates to the previous data. The user did not remove the 10 original files.
If the user runs the same COPY INTO command what will happen?

Select 1 option(s):

All data from all of the files on the stage will be appended to the table.

All data from only the newly-added files will be appended to the table.

The operation will fail with the error UNCERTAIN FILES IN STAGE.

Only data about new customers from the new files will be appended to the table.

Q10. For a multi-cluster virtual warehouse, which parameters are used to calculate the number of credits billed? (Choose two.)

Select 2 option(s):

Number of clusters

Cache size

Warehouse size

Volume of data processed

Number of queries executed

Q11. A task is still being executed before the next scheduled task. What is going to happen with the new scheduled task?

Select 1 option(s):

Snowflake will execute it.

Snowflake will skip it.

Snowflake will wait for the previous task to complete.

Snowflake will abort it.

Q12. Which pipes are cloned when cloning a database or schema?

Select 1 option(s):

Pipes that reference external stages.

Pipes that reference internal stages.

Pipes cannot be cloned.

Both.

Q13. Which command will use warehouse credits?

Select 1 option(s):

Q14. When unloading data to an external stage, which compression format can be used for Parquet files with the COPY INTO command?

Select 1 option(s):

ZSTD

LZO

BROTLI

GZIP

Q15. By default, how many inbound share(s) have every Snowflake account?

Select 1 option(s):

Three, the ACCOUNT_USAGE, the DATA, and the INFORMATION shares.

One, the ACCOUNT_USAGE share.

Two, the ACCOUNT_USAGE and the INFORMATION shares.

Two, the ACCOUNT_USAGE and the SAMPLE_DATA shares.

Q16. The owner of a task (the one who has the OWNERSHIP privilege) is deleted. What will happen to the task?

Select 1 option(s):

The task is deleted.

The task will belong to the ACCOUNTADMIN role.

The task is suspended.

The task will belong to the role that dropped the owner’s role.

Q17. Which of the following are benefits of micro-partitioning? (Choose two.)

Select 2 option(s):

Micro-partitions are immutable objects that support the use of Time Travel.

Micro-partitions cannot overlap in their range of values.

Micro-partitions can reduce the amount of I/O from object storage to virtual warehouses.

Micro-partitions can be defined on a schema-by-schema basis.

Rows are automatically stored in sorted order within micro-partitions.

Q18. We want to generate a JSON object with the data from a table called users_table, composed of two columns (AGE and NAME), ordered by the name column. How can we do it?

Select 1 option(s):

SELECT object_deconstruct(*) as users_object
FROM users_table
order by users_object[‘NAME’];

SELECT to_object(*) as users_object
FROM users_table
order by users_object[‘NAME’];

SELECT object_construct(*) as users_object
FROM users_table
order by users_object[‘NAME’];

SELECT to_json_object(*) as users_object
FROM users_table
order by users_object[‘NAME’];

Q19. What is the maximum number of clusters in a multi-cluster warehouse?

Select 1 option(s):

Unlimited, there is not a maximum number of clusters.

100

Q20. Which of the following describes how clustering keys work in Snowflake?

Select 1 option(s):

Clustering keys sort the designated columns over time, without blocking DML operations.

Clustering keys update the micro-partitions in place with a full sort, and impact the DML operations.

Clustering keys create a distributed, parallel data structure of pointers to a table‘s rows and columns.

Clustering keys establish a hashed key on each node of a virtual warehouse to optimize joins at run-time.

Q21. Using variables in Snowflake is denoted by using which SQL character?

Select 1 option(s):

Q22. Which data modeling concepts can be used in Snowflake (Choose two.)

Select 2 option(s):

Distribution Key.

Non-Unique Index.

Unique Index.

Foreign Key.

Primary Key.

Q23. When is the result set cache no longer available? (Choose two.)

Select 2 option(s):

When another user executes the query

When another warehouse is used to execute the query

When the warehouse used to execute the query is suspended

When the underlying data has changed

When it has been 24 hours since the last query

Q24. What are the two models that Snowflake combines as an approach to access control?

Select 1 option(s):

DAC & RBAC.

MAC & RBAC.

MAC & ABAC.

DAC & ABAC.

Q25. To which entity do we grant privileges?

Select 1 option(s):

Account.

Groups.

Roles.

Users.

Q26. Which of the following file formats is not supported by Snowflake to unload data?

Select 1 option(s):

CSV.

JSON.

Parquet.

Avro.

Q27. How many cluster keys can we create for a Snowflake table?

Select 1 option(s):

One.

Unlimited.

Two.

A maximum of three or four cluster keys.

Q28. What is the maximum time that Snowflake can run a query?

Select 1 option(s):

2 days.

Unlimited.

7 days.

1 day.

Q29. Which features make up Snowflake‘s column level security? (Choose two.)

Select 2 option(s):

Dynamic Data Masking

Continuous Data Protection (CDP)

Row access policies

External Tokenization

Key pair authentication

Q30. What is a best practice after creating a custom role?

Select 1 option(s):

Add _CUSTOM to all custom role names.

Assign the custom role to the PUBLIC role.

Assign the custom role to the SYSADMIN role.

Create the custom role using the SYSADMIN role.

Q31. The time-travel retention period of a table is configured to be ten days. You now increase the retention period to 20 days. What will happen with the table‘s data after this increment? (Choose two.)

Select 2 option(s):

Changes will impact only new data.

Any data which has not reached the ten days time-travel period will now have time-travel extended for 20 days.

Any data that is between 10 and 20 days older will have time-travel extended.

Any data that is ten days older and moved to fail-safe will not have any impact.

Q32. Which command can we use to access sequences in queries as expressions?

Select 1 option(s):

.NEXTVAL

.CURRENTVAL

.GETVAL

.THISVAL

Q33. In which Snowflake editions is the Snowflake Marketplace not available?

Select 1 option(s):

Standard edition.

Enterprise edition.

Snowflake Marketplace is available in all Snowflake editions.

Business Critical Edition.

Virtual Private Snowflake (VPS) edition.

Q34.Which of the following roles are recommended to create and manage users and roles? (Choose two.

Select 2 option(s):

SYSADMIN

USERADMIN

ACCOUNTADMIN

PUBLIC

SECURITYADMIN

Q35. Which of the following listing types are not available in the Snowflake Data Marketplace?

Select 1 option(s):

Personalized Listing.

Free Listing.

Paid Listing.

Private Listing.

Q36. What are benefits of using Snowpark with Snowflake? (Choose two.)

Select 2 option(s):

Snowpark does not require that a separate cluster be running outside of Snowflake.

Snowpark pushes down all data transformation and heavy lifting to the Snowflake data cloud.

Snowpark automatically sets up Spark within Snowflake virtual warehouses.

Snowpark executes as much work as possible in the source databases for all operations including User-Defined Functions (UDFs).

Snowpark uses a Spark engine to generate optimized SQL query plans.

Q37. Which command can we use to query the table as it was 15 minutes ago?

Select 1 option(s):

SELECT *
FROM my_table
AT(offset => -60*15);

SELECT *
FROM my_table
AT(offset => 15);

SELECT *
FROM my_table
AT(offset => -3600*15);

We should’ve created a backup of that table. Otherwise, we are not able to do it.

Q38. To add or remove search optimization for a table, a user must have which of the following privileges or roles? (Choose two.)

Select 2 option(s):

The ADD SEARCH OPTIMIZATION privilege on the schema that contains the table

The OWNERSHIP privilege on the table

The MODIFY privilege on the table

A SECURITYADMIN role

The SELECT privilege on the table

Q39. Which privileges does a role need to clone a table? (Choose two.)

Select 2 option(s):

USAGE privilege on the schema of the source table.

SHARE privilege on the database of the source table.

CREATE privilege on the source table.

SELECT privilege on the database of the source table.

SELECT privilege on the source table.

Q40. Which command will fail if you have a table created with the following DDL query?

CREATE TABLE MYTABLE
(ID INTEGER, NAME VARCHAR)

Select 1 option(s):

Q41. Which Snowflake object stores DML change made to tables and metadata about each change?

Select 1 option(s):

Pipes.

Account Streams.

Table Streams.

Tables.

Q42. What statements are true about the Snowflake History Page? (Choose two.)

Select 2 option(s):

The History page allows you to view and drill into the details of all queries executed in the last 24 days.

You cannot see the queries of other users.

You cannot see the results of other users.

The History page allows you to view and drill into the details of all queries executed in the last 14 days.

You can see the results of other users.

Q43. According to Snowflake best practice recommendations, which role should be used to create databases?

Select 1 option(s):

SECURITYADMIN

ACCOUNTADMIN

USERADMIN

SYSADMIN

Q44. What is a responsibility of Snowflake’s virtual warehouses?

Select 1 option(s):

Metadata management

Query parsing and optimization

Query execution

Management of the storage layer

Infrastructure management

Q45. Which programming languages are supported for Snowflake User-Defined Functions (UDFs)? (Choose two.)

Select 2 option(s):

TypeScript

Python

JavaScript

PHP

Q46. Which of the following query profiler variables will indicate that a virtual warehouse is not sized correctly for the query being executed?

Select 1 option(s):

Initialization

Bytes sent over the network

Remote spillage

Synchronization

Q47. After how many hours does Snowflake cancel our running SQL statement by default?

Select 1 option(s):

1 hour.

24 hours.

48 hours.

10 hours.

Q48. How can a user change which columns are referenced in a view?

Select 1 option(s):

Use the ALTER VIEW command to update the view

Modify the columns in the underlying table

Recreate the view with the required changes

Materialize the view to perform the changes

Q49. A PUT command can be used to stage local files from which Snowflake interface?

Select 1 option(s):

SnowSQL

Snowflake classic web interface (UI)

.NET driver

Snowsight

Q50. Which parameter can be used to instruct a COPY command to verify data files instead of loading them into a specified table?

Select 1 option(s):

REPLACE_INVALID_CHARACTERS

VALIDATION_MODE

STRIP_NULL_VALUES

SKIP_BYTE_ORDER_MARK

Q51. What is the recommended file size for the best load performance and to avoid size limitations?

Select 1 option(s):

Files shouldn’t exceed 10-100 MB (or larger) in size compressed.

Files shouldn’t exceed 10-100 MB (or larger) in size uncompressed.

Files shouldn’t exceed 100-250 MB (or larger) in size compressed.

Files shouldn’t exceed 100-250 MB (or larger) in size uncompressed.

Q52. Which of the below columns are usually a good choice for clustering keys?

Select 1 option(s):

Timestamp in a 10TB table.

Gender male/female in a 20TB table.

Store_id in a 2TB table.

UUID column from a Customer in a 10TB table.

Q53. What happens when an external or an internal stage is dropped? (Choose two.)

Select 2 option(s):

When dropping an external stage, the files are not removed and only the stage is dropped.

When dropping an internal stage, the files are deleted with the stage and the files are recoverable.

When dropping an internal stage, only selected files are deleted with the stage and are not recoverable.

When dropping an internal stage, the files are deleted with the stage and the files are not recoverable.

When dropping an external stage, both the stage and the files within the stage are removed.

Q54. What is the maximum length of time travel available in the Snowflake Standard Edition?

Select 1 option(s):

90 Days

30 Days

1 Day

7 Days

Q55. A user unloaded a Snowflake table called mytable to an internal stage called mystage.

Which command can be used to view the list of files that has been uploaded to the stage?

Select 1 option(s):

list @mytable;

list @mystage;

list @%mytable;

list @%mystage;

Q56. Which of these Snowflake components/objects is NOT typically used in building continuous ELT pipelines?

Select 1 option(s):

Snowflake Connector for Kafka.

Snowpipe.

Data Exchange.

Streams.

Q57. Which roles can create, alter or drop network policies? (Choose two.)

Select 2 option(s):

ORGADMIN

ACCOUNTADMIN.

SYSADMIN.

USERADMIN.

SECURITYADMIN.

Q58. When creating a table using the command:

CREATE TABLE MY_TABLE
(NAME STRING(100));

What would the command “DESC TABLE MY_TABLE;“ display as the column type?

Select 1 option(s):

Text.

String.

Varchar.

Char.

Q59. What is the function of the PUBLIC schema in Snowflake?

Select 1 option(s):

Default schema where objects are going to be created.

It’s the schema that even non-Snowflake users will be able to access, as it’s public.

It’s the schema where non-PII data will be stored.

It’s the schema where information about pricing will be stored.

Q60. What are the benefits of the replication feature in Snowflake? (Choose two.)

Select 2 option(s):

Fail-safe

Time Travel

Disaster recovery

Data security

Database failover and failback