Snowflake Practice Test: Assess Your Data Cloud Expertise

Welcome to your Snowflake Test-12

Name

Phone

Q1. Which stage is allocated to a user?

Select 1 option(s):

Internal named stage

External names stage

Internal user stage

Internal table stage

Q2. If an account has federated authentication enabled. Can Snowflake admins still maintain user id and passwords in Snowflake?

Select 1 option(s):

Yes

Q3. Snowflake can query the data from External Tables. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q4. What all file formats are supported by Snowflake for unloading data from Snowflake?

Select 3 option(s):

PARQUET

AVRO

JSON

XML

CSV, TSV

Q5. What is the best recommended size of data file in case of SNOWPIPE continuous loading?

Select 2 option(s):

Same as of Bulk Loading (10 MB - 100 MB compressed)

if file taking more than a minute, then split the files into more files

1 GB Compressed

Same as of Bulk Loading (10 MB - 100 MB uncompressed)

Q6. What all commands will you use to load data from External Stage (for example, AWS S3 bucket) System to Snowflake table?

Select 1 option(s):

PUT COMMAND followed by COPY INTO

Only COPY INTO command

Only PUT command

COPY INTO followed by PUT command

Q7. How can you remove the outer array structure of JSON data?

Select 1 option(s):

Set STRIP_OUTER_ARRAY = 1

Set STRIP_OUTER_ARRAY = TRUE

Set STRIP_OUTER_BRACKET = TRUE

Set STRIP_INNER_ARRAY = TRUE

Q8. What all commands will you use to unload data from your Snowflake table to External Stages?

Select 1 option(s):

COPY INTO command followed by GET command

COPY INTO followed by PUT command

Only GET command

Only COPY INTO command

Q9. MFA is automatically enabled for all the users. Please select the true statement.

Select 1 option(s):

MFA is only enabled for ACCOUNTADMIN by default. ACCOUNTADMIN can enable it for other users.

MFA is available for all the users by default.

Q10. Please select the best option.

Select 1 option(s):

All of these

Internal Table Stage is referenced as @%

None of these are correct

Internal Named Stage is referenced as @

Internal user stage is referenced using @~

Q11. Snowflake supports transforming data while loading it into a table using the COPY command. What all options you have?

Select 4 option(s):

Column omission

Casts

Join

Column reordering

String Truncation

Q12. What are the security layers that Snowflake takes care of?

Select 4 option(s):

Access

Infrastructure

Authorization

Data Protection

Authentication

Q13. A user cannot view the result set from a query that another user executed except ACCOUNTADMIN role. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q14. Loading using SNOWPIPE is best useful for:

Select 1 option(s):

Load small volumes of data incrementally

Load large files with size of 500 MB

None of these

Data Files which takes at least 30 to 60 Minutes.

Q15. What is the recommendation for file size for Parquet files for loading?

Select 1 option(s):

2 GB

1 GB

16 MB

3 GB

Q16. What is the data size limit while loading data into VARIANT column?

Select 1 option(s):

16 MB (compressed)

1 GB Compressed

16 MB (uncompressed)

10 MB - 100 MB compressed

10 MB - 100 MB uncompressed

Q17. What is default size of unload file?

Select 1 option(s):

32 MB

10 MB

100 MB

16 MB

Q18. When using a LATERAL FLATTEN in a semi-structured data query, what does the LATERAL do?

Select 1 option(s):

Causes the FLATTEN command to read an array in reverse order

Causes the FLATTEN command to be recursive and drill down into all levels of the record

Modifies the data in an array to a variant data type so it will work with the FLATTEN command

Breaks an array into multiple elements

Works as a self join to join the current record key / value pairs to an array in the same record

Q19. Which key concepts should be considered when loading data into Snowflake?

Select 1 option(s):

Transformation and error validation

Stage Object

All of these

File Format

Q20. STRIP_NULL_VALUE converts a JSON null value to a SQL NULL value. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q21. Which Snowflake edition does allow for customer-managed keys encryption.

Select 1 option(s):

Standard (or higher)

Enterprise (or higher)

Only VPS

Business Critical (of higher)

Q22. Time travel is available for which table types? (Select 3)

Select 3 option(s):

Transient

Permanent

External

Temporary

Q23. What is the column datatype used for ingesting semi-structured data?

Select 1 option(s):

NUMBER

VARIANT

FLOAT

STRING

VARCHAR

Q24. What all commands will you use to load data from your Local File System to Snowflake table?

Select 1 option(s):

Only COPY INTO command

COPY INTO followed by PUT command

PUT COMMAND followed by COPY INTO

Only PUT command

Q25. We loaded an XML file that included a header entity called . In order to bypass this entity and treat each EMPLOYEE object as a separate record (loading each into a separate row), what FILE_FORMAT property did we change?

Select 1 option(s):

DISABLE AUTO CONVERT

STRIP OUTER ELEMENT

DISABLE SNOWFLAKE DATA

PRESERVE SPACE

IGNORE UTF-8 ERROR

Q26. FORCE option is used to load all files, ignoring load metadata if it exists. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q27. A user‘s default role is

Select 1 option(s):

The role a user is set to each time the user logs in to the system

The user‘s default role is always PUBLIC

The name used to log in to the WebUI

The user‘s default role changes each time the user logs in to the system.

Q28. What are the three Snowflake Stage types? (Select 3)

Select 3 option(s):

Secure

Permanent

Materialized

User

Named

Table

Q29. What are the features of Column-level security? (Select 2)

Select 2 option(s):

Internal Tokenization

External Tokenization

Dynamic Data Masking

Column Masking

Q30. When setting the password policy, what options are available?

Select 1 option(s):

Set the required length for a password

Force the use of a special character in a password

All of the above

Prevent the reuse of a previous password

Snowflake password policy cannot be changed

Q31. The FLATTEN command will parse nested entities into separate rows. One version of the FLATTEN command uses a join and the other uses an object keyword. Select the two words that represent the options used with the FLATTEN command.

Select 2 option(s):

TABLE

CONVERT

LATERAL

SIDEWAYS

Q32. Stages which do not support File Formats are :

Select 2 option(s):

Internal Table Stage

Internal User Stage

External Named Stage

Internal named Stage

Q33. SNOWPIPE AUTO_INGEST method only works with External Stages. (TRUE/FALSE)

Select 1 option(s):

TRUE

FALSE

Q34. Which role is inherited to every other role in the account?

Select 1 option(s):

DEFAULT ROLE

STANDARD ROLE

CONTROL ROLE

PUBLIC ROLE

PREFERED ROLE

Q35. Which objects are securable objects in Snowflake?

Select 4 option(s):

Database

File Format

Table

Warehouse

Unattempted

Q36. How can you ignore metadata and load the already loaded staged file into table?

Select 1 option(s):

Using ACCEPT = TRUE parameter with COPY INTO command

Using LOAD = TRUE parameter with COPY INTO command

Using FORCE = TRUE parameter with COPY INTO command

Using LOAD = FORCE parameter with COPY INTO command

Using INSERT = TRUE parameter with COPY INTO command

Using FORCE = YES parameter with COPY INTO command

Q37. How many files each server in a cluster can process in parallel?

Select 1 option(s):

16 Files

4 Files

1 File

Depends on how large is the file. If file is 32 MB in size, the server can process 4 such files in parallel

8 Files

Q38. When unloading data into multiple files, use the MAX_FILE_SIZE copy option to specify the maximum size of each file created. (TRUE / FALSE)

Select 1 option(s):

TRUE

FALSE

Q39. Which type of the object key is only used for decryption?

Select 1 option(s):

Active key

Retired Key

None of these

Destroyed key

Q40. Named stages come in two varieties, what are they?

Select 2 option(s):

Internal

Permanent

Secure

Materialized

External

Q41. Permissions on database objects such as a database or table are granted to:

Select 1 option(s):

Users or roles

Users

Schemas

Roles

Virtual warehouses

Q42. Which security feature is supported in Snowflake?

Select 1 option(s):

Role-Based Access Control

Multi-Factor Authentication

All of these

Tri-Secret Secure Encryption

Q43. Semi Structured data can be accessed: (Select 3)

Select 3 option(s):

In a permanent table using the variant data type

In files in an external stage

In files on an AWS EC2 server

In files in a internal stage

In files in an on-prem file server

Q44. Federated Authentication is supported by:

Select 4 option(s):

Business Critical

Standard Edition

VPS

Enterprise Edition

Unattempted

Q45. Snowflake provides native support for semi-structured data. Select true option about Snowflake native support for Semi-Structure data.

Select 1 option(s):

Automatic conversion of data to optimize internal storage format

All of these

Flexible-Schema data types for loading semi-structured data without transformation

Database Optimization for fast and efficient SQL querying.

Q46. How can you create a user in Snowflake without password?

Select 1 option(s):

Using Salesforce Web interface

User creation without password is not possible

Using SQL Command - CREATE USER

Q47. Which role is not a Snowflake System-defined role assigned to a new account?

Select 1 option(s):

ACCOUNTADMIN

PUBLIC

CUSTOM

SYSADMIN

SECURITYADMIN

Q48. Which approach would result in improved performance through linear scaling of data ingestion workload?

Select 1 option(s):

Consider the practice of splitting input file batch within the recommended range of 10MB to 100MB

Resize virtual warehouse

All of the above

Consider the practice of organizing data by granular path

Q49. Two of the key attributes that distinguish semi-structure data from structured data are :

Select 2 option(s):

Lack of a Fixed Schema

Nested data structure

Fixed Schema

Q50. How can you create a “Super-User“ or “Super-Role“ in Snowflake who can bypass all the authorization checks?

Select 1 option(s):

There is no concept of SUPER-ROLE or SUPER-USER in Snowflake

Contact Snowflake personnel to create a Super-Role or Super-User for your account

CREATE ROLE SUPER_ROLE;

ACCOUNTADMIN role is same as Super-Role

Q51. You set up a Snowflake account, choosing AWS as your cloud platform provider. What stages can you use to load data files? (Check all that apply)

Select 6 option(s):

NAMED INTERNAL

NAMED EXTERNAL - using GCS/GCP Buckets

TABLE

USER

NAMED EXTERNAL - using Azure BLOB storage

NAMED EXTERNAL - using S3 Buckets

Unattempted

Q53. Which are the correct statements about TASKS?

Select 1 option(s):

TASKS is used to scheduled SQL execution

Can not be triggered manually

TASKS is used to identify and act on changed table records

It is used for Change Data Capture (CDC)

it can be used with STREAMS

Q53. What is the default compression algorithm Snowflake apply while unloading data?

Select 1 option(s):

NONE

GZIP

GZIP3

ZSTD

BZ2

Q54. External Stages require customers to have an account with a cloud storage service provider. Which of the following are available currently or have been announced by Snowflake as under development?

Select 3 option(s):

GCP Buckets

AWS S3

DROPBOX

MS Azure Blob

BOX

Q55. The compute resource used by Snowflake for data loading jobs can be provided by:

Select 2 option(s):

User managed virtual warehouse

Hardware provisioned by user directly from cloud providers

All of these

Snowflake managed service

Q56. Which command will help you the current permissions on a Schema?

Select 1 option(s):

SHOW ROLES ON SCHEMA .;

SHOW ALL PREIVILEGE ON SCHEMA .;

SHOW GRANTS ON SCHEMA .;

SHOW GRANTS OF SCHEMA .;

Q57. What data structure types can be ingested into a VARIANT column in a Snowflake table? (Check all that apply)

Select 5 option(s):

XML

PARQUET

AVRO

ORC

JSON

Unattempted

Q58. Which are the different ways a user can get permissions to a database object?

Select 3 option(s):

The user is granted a role that inherits another role with permission to the object

Grant the role with the permissions on the database object to the user

The user creates the objects and the current role becomes the owner of the object

Grant the user permissions to the object

Q59. Which three objects did we explicitly refer to using the COPY INTO command in the lesson on using external stages?

Select 3 option(s):

SCHEMA

DATABASE

VIEW

STAGE

FILE FORMAT

TABLE

Q60. What level of Privilege do you need to view Resource monitor?

Select 1 option(s):

OWNERSHIP

USAGE

OPERATE

MONITOR

Q61. At what frequency does Snowflake rotate the object keys?

Select 1 option(s):

1 Year

30 Days

16 Days

60 Days

Q62. Snowflake supports many methods of authentication. Which are the supported authentication methods in ALL Snowflake Editions?

Select 3 option(s):

MFA (Multi-factor authentication)

Only MFA is supported by all the Snowflake editions

OAuth

SSO

Only MFA and SSO are supported by all the Snowflake editions

Q63. When key: value pairs are queried from a semi-structured record and the data is not cast, what is the data type returned?

The system automatically cast the value to the correct data type based on the structure

Select 1 option(s):

Binary

Varchar

Variant

String

Q64. What is the best practice after creating a custom role in a Snowflake account?

Select 1 option(s):

Grant ownership of the role to itself so a member of the role can control access to the role

Grant ownership to the SYSADMIN role or a child role under SYSADMIN

Grant the role to the USERADMIN role so users can be added to the role.

Grant PUBLIC to the role so all database objects owned by PUBLIC will be available to the new role

Q65. At what places can you specify File Format?

Select 3 option(s):

All of these

As part of a named stage or pipe definition

As part of a table definition

As part of Internal Table stage

In the COPY INTO command

Snowflake Practice Test: Assess Your Data Cloud Expertise

Q1. Which stage is allocated to a user?

Q2. If an account has federated authentication enabled. Can Snowflake admins still maintain user id and passwords in Snowflake?

Q3. Snowflake can query the data from External Tables. (TRUE / FALSE)

Q4. What all file formats are supported by Snowflake for unloading data from Snowflake?

Q5. What is the best recommended size of data file in case of SNOWPIPE continuous loading?

Q6. What all commands will you use to load data from External Stage (for example, AWS S3 bucket) System to Snowflake table?

Q7. How can you remove the outer array structure of JSON data?

Q8. What all commands will you use to unload data from your Snowflake table to External Stages?

Q9. MFA is automatically enabled for all the users. Please select the true statement.

Q10. Please select the best option.

Q11. Snowflake supports transforming data while loading it into a table using the COPY command. What all options you have?

Q12. What are the security layers that Snowflake takes care of?

Q13. A user cannot view the result set from a query that another user executed except ACCOUNTADMIN role. (TRUE / FALSE)

Q14. Loading using SNOWPIPE is best useful for:

Q15. What is the recommendation for file size for Parquet files for loading?

Q16. What is the data size limit while loading data into VARIANT column?

Q17. What is default size of unload file?

Q18. When using a LATERAL FLATTEN in a semi-structured data query, what does the LATERAL do?

Q19. Which key concepts should be considered when loading data into Snowflake?

Q20. STRIP_NULL_VALUE converts a JSON null value to a SQL NULL value. (TRUE / FALSE)

Q21. Which Snowflake edition does allow for customer-managed keys encryption.

Q22. Time travel is available for which table types? (Select 3)

Q23. What is the column datatype used for ingesting semi-structured data?

Q24. What all commands will you use to load data from your Local File System to Snowflake table?

Q25. We loaded an XML file that included a header entity called . In order to bypass this entity and treat each EMPLOYEE object as a separate record (loading each into a separate row), what FILE_FORMAT property did we change?

Q26. FORCE option is used to load all files, ignoring load metadata if it exists. (TRUE / FALSE)

Q27. A user‘s default role is

Q28. What are the three Snowflake Stage types? (Select 3)

Q29. What are the features of Column-level security? (Select 2)

Q30. When setting the password policy, what options are available?

Q31. The FLATTEN command will parse nested entities into separate rows. One version of the FLATTEN command uses a join and the other uses an object keyword. Select the two words that represent the options used with the FLATTEN command.

Q32. Stages which do not support File Formats are :

Q33. SNOWPIPE AUTO_INGEST method only works with External Stages. (TRUE/FALSE)

Q34. Which role is inherited to every other role in the account?

Q35. Which objects are securable objects in Snowflake?

Q36. How can you ignore metadata and load the already loaded staged file into table?

Q37. How many files each server in a cluster can process in parallel?

Q38. When unloading data into multiple files, use the MAX_FILE_SIZE copy option to specify the maximum size of each file created. (TRUE / FALSE)

Q39. Which type of the object key is only used for decryption?

Q40. Named stages come in two varieties, what are they?

Q41. Permissions on database objects such as a database or table are granted to:

Q42. Which security feature is supported in Snowflake?

Q43. Semi Structured data can be accessed: (Select 3)

Q44. Federated Authentication is supported by:

Q45. Snowflake provides native support for semi-structured data. Select true option about Snowflake native support for Semi-Structure data.

Q46. How can you create a user in Snowflake without password?

Q47. Which role is not a Snowflake System-defined role assigned to a new account?

Q48. Which approach would result in improved performance through linear scaling of data ingestion workload?

Q49. Two of the key attributes that distinguish semi-structure data from structured data are :

Q50. How can you create a “Super-User“ or “Super-Role“ in Snowflake who can bypass all the authorization checks?

Q51. You set up a Snowflake account, choosing AWS as your cloud platform provider. What stages can you use to load data files? (Check all that apply)

Q53. Which are the correct statements about TASKS?

Q53. What is the default compression algorithm Snowflake apply while unloading data?

Q54. External Stages require customers to have an account with a cloud storage service provider. Which of the following are available currently or have been announced by Snowflake as under development?

Q55. The compute resource used by Snowflake for data loading jobs can be provided by:

Q56. Which command will help you the current permissions on a Schema?

Q57. What data structure types can be ingested into a VARIANT column in a Snowflake table? (Check all that apply)

Q58. Which are the different ways a user can get permissions to a database object?

Q59. Which three objects did we explicitly refer to using the COPY INTO command in the lesson on using external stages?

Q60. What level of Privilege do you need to view Resource monitor?

Q61. At what frequency does Snowflake rotate the object keys?

Q62. Snowflake supports many methods of authentication. Which are the supported authentication methods in ALL Snowflake Editions?

Q63. When key: value pairs are queried from a semi-structured record and the data is not cast, what is the data type returned?

Q64. What is the best practice after creating a custom role in a Snowflake account?

Q65. At what places can you specify File Format?

Q20. STRIP_NULL_VALUE converts a JSON null value to a SQL NULL value. (TRUE / FALSE)