Snowflake Assessment: 65 Practice Questions for Tech Interviews

Welcome to your Snowflake Test-15

Name

Phone

Q1. Loading using SNOWPIPE is best useful for:

Select 1 option(s):

Load large files with size of 500 MB

None of these

Data Files which takes at least 30 to 60 Minutes.

Load small volumes of data incrementally

Q2. John wants to share a VIEW with Monica but John wants to hide View definition and details from Monica. What type of view John should create and share with Monica?

Select 1 option(s):

Secured

Standard

Materialized

None

Q3. Monica is an ACCOUNTADMIN and wants to change the Time Travel retention period of table T1 of database DB1 to 15 days. Which options will help her to set the new Time Travel retention period?

Select 1 option(s):

Execute SQL Command - ALTER TABLE T1 SET DATA_RETENTION_TIME_IN_DAYS = 15;

Execute SQL Command - ALTER TABLE T1 SET DATA_RETENTION_TIME_IN_DAYS = 75;

Monica can change the retention period through ACCOUNT tab on UI

Monica should contact Salesforce support to get it done

Q4. 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):

DROPBOX

MS Azure Blob

GCP Buckets

AWS S3

BOX

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

Select 1 option(s):

Virtual warehouses

Roles

Schemas

Users

Users or roles

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

Select 3 option(s):

In files on an AWS EC2 server

In files in an on-prem file server

In a permanent table using the variant data type

In files in a internal stage

In files in an external stage

Q7. 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):

Only COPY INTO command

PUT COMMAND followed by COPY INTO

COPY INTO followed by PUT command

Only PUT command

Q8. John created a Database and set the DATA_RETENTION_TIME_IN_DAYS as 30 days, but he did not set the DATA_RETENTION_TIME_IN_DAYS in one of the table ‘T1‘. After 5 days, he accidently dropped table ‘T1‘. Will he able to recover the table T1?

Select 1 option(s):

Yes, as the retention period is specified at database level

John can recover the table T1 after 30 days

John should call Snowflake Support to recover the table from Fail-Safe.

No, John cant recover the dropped table T1 because he didn‘t set DATA_RETENTION_TIME_IN_DAYS for Table T1

Q9. Multi-cluster warehouse credit billing is based on which factors? (Select 2)

Select 2 option(s):

Number of servers per cluster

Number of queries it processes per hours

Number of users

Number of Clusters

Q10. If you are ACCOUNTADMIN, you can query and recover data from Fail-safe without the help of Snowflake support. (TRUE/FALSE)

Select 1 option(s):

TRUE

FALSE

Q11. If a DBA_ROLE creates a Database. Later the DBA_ROLE was dropped. Who will own the Database now which was created by DBA_ROLE?

Select 1 option(s):

No one can access

Database will be dropped

Role who dropped the DBA_ROLE

DBA_ROLE cant be dropped as it is the owner of the Database.

Q12. Which cache type gets purged regularly?

Select 1 option(s):

Results Cache

Metadata Cache

Warehouse Cache

Q13. Stored Procedure supports

Select 1 option(s):

Java (Using Snowpark)

All of them

Python

Q14. The VARIANT data type imposes a 10 MB to 100 MB (compressed) size limit on individual rows.(TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q15. Snowflake is a Cloud Data Platform delivered as a service.

Select 1 option(s):

FALSE

TRUE

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

Select 3 option(s):

External

Transient

Temporary

Permanent

Q17. The data stored as part of fail-safe is part of storage costs charged to customers.

Select 1 option(s):

FALSE

TRUE

Q18. Please choose the correct statements about Micro-Partitions (Select 2)

Select 2 option(s):

Snowflake micro-partitions are derived automatically

Micro-partitions are small in size (50 to 500 MB, after compression)

Snowflake automatically determines the most efficient compression algorithm for the columns in each micro-partition.

Micro-partitions can‘t overlap in their range of values

Q19. What are the best practices for JOIN on unique keys?

Select 3 option(s):

Avoid unintentional cross join

Use distinct keys

Avoid MANY-TO-MANY Join

Unattempted

Q20. The following SQL statement will require a running warehouse – CREATE TABLE T1 (NAME (VARCHAR20), ADDRESS VARCHAR (50));

Select 1 option(s):

FALSE

TRUE

Q21. User A and User B can access one another‘s result sets from the Results Cache, as long as which of the following are true? (Choose two)

Select 2 option(s):

They use the same Warehouse.

They run the exact same SQL Text/Query.

They sign in to the same Session.

They use the same Role.

They run with 59 minutes of one another.

Q22. Which command will return information about the current database?

Select 1 option(s):

Q23. There are two types of Snowflake Planned releases. Please choose all applicable.

Select 2 option(s):

Installation release

Upgrade Release

Patch Release

Full Release

Q24. How often does Snowflake release new features?

Select 1 option(s):

Yearly

Monthly

Bi-Weekly

Weekly

Q25. Who can create shares?

Select 2 option(s):

ROLE with CREATE SHARES global Privilege

ACCOUNTADMIN

Unattempted

Q26. Which is not the function of Optimization activities of Cloud Services layer?

Select 1 option(s):

Automatic JOIN order optimization

Automatic statistic gathering

Pruning using metadata about micro-partitions

Handles Queries that can be processes completely from metadata

Q27. John created a table EMPLOYEE in worksheet 1 (Session1 ) but when he ran SELECT * FROM EMPLOYEE in worksheet 2 (Session 2) after setting up the correct Role, Database and Schema, he received error ‘ Table doesn‘t exist‘. What could be the reason?

Select 1 option(s):

EMPLOYEE is Temporary table

EMPLOYEE is a Permanent Table

EMPLOYEE was created as EXTERNAL TABLE

XYZ is Transient Table

Q28. You have a dashboard that connects to Snowflake via JDBC. The dashboard is refreshed hundreds of times per day. The data is very stable, only changing once or twice per day. The query run by the dashboard connector user never changes. How will Snowflake manage changing and non-changing data? Mark all true statements.

Select 3 option(s):

Snowflake will re-use data from the Results Cache as long as it is still the most up-to-date data available

Snowflake will spin up a warehouse each time the dashboard is refreshed

Snowflake will compile results cache data from all user results so no warehouse is needed

Snowflake will show the most up-to-date data each time the dashboard is refreshed

Snowflake will spin up a warehouse only if the underlying data has changed

Q29. John wants to create a Warehouse which can AUTO_SUSPEND in 3 Minutes. There is no option in UI where he can select 3 Minutes. What will you suggest him to do?

Select 1 option(s):

He should select Size as X-SMALL and then he will be able to set AUTO_SUSPEND to 3 Minutes

He should contact Snowflake Support to get it created

It is not possible to create a warehouse with AUTO_SUSPEND less than 5 Minutes

He can create the Warehouse using SQL command with AUTO_SUSPEND = 180

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

Select 1 option(s):

FALSE

TRUE

Q31. AWS PrivateLink is an AWS service for creating private VPC endpoints that allow direct, secure connectivity between AWS VPCs without traversing the public Internet. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q32. Which of the following object types are child objects within schemas?

Select 5 option(s):

User Defined Functions

Sequences

Stored Procedures

Roles

File Formats

Stages

Q33. John has setup process to load specific set of files using both Bulk and Snowpipe. This is best practice to avoid any missed loading either by Bulk loading or Snowpipe. (TRUE /FALSE)

Select 1 option(s):

FALSE

TRUE

Q34. How much is the Time-Travel retention period of Permanent Table (in Standard Edition)?

Select 1 option(s):

1 day

7 days

0 days

90 days

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

Select 5 option(s):

ORC

JSON

PARQUET

AVRO

XML

Unattempted

Q36. Scaling a Warehouse DOWN will decrease the number of servers (e.g., Large to Medium).

Select 1 option(s):

TRUE

FALSE

Q37. How many maximum columns (or expressions) are recommended for a cluster key?

Select 1 option(s):

7 to 8

Higher the number of columns (or expressions) in the key, better will be the performance

12 to 16

3 to 4

Q38. Snowflake Administrators should utilize resource monitors to help control costs and avoid unexpected credit usage. Which of the following actions can Snowflake‘s resource monitor triggers initiate automatically? (Check all that apply)

Select 3 option(s):

Trigger alert notifications for high usage

Roll over query executions to under-utilized warehouses

Trigger warehouse suspension for high usage

Impose limits on the number of credits that warehouses consume each month

Q39. Which are the correct statements about Snowflake data Storage?

Select 3 option(s):

Encryption on demand

Automatic Micro-partitioning

Hybrid Columnar Storage

Natural data clustering and optimization

Q40. Which of the following are performed by the Cloud Services layer? (Select 4)

Select 4 option(s):

User Authentication

Data Security

Metadata Management

Metadata Storage

Availability Zone Management

Q41. Which of the following options would result in a column named FIRST NAME being sandwiched between to percent signs? (Check all that apply)

Select 2 option(s):

Q42. Auto-suspend and auto-resume apply only to the entire warehouse and not to the individual clusters in the warehouse.

Select 1 option(s):

FALSE

TRUE

Q43. What is the maximum Time-Travel retention period of Standard Edition?

Select 1 option(s):

90 days

0 days

7 days

1 day

Q44. A user can connect Snowflake in multiple ways. Please select

Select 4 option(s):

A web-based user based

Native connector (e.g. Python) that can be used to develop applications

ODBC and JDBC drivers that can be used by other applications

Command line clients (e.g. SnowSQL)

Unattempted

Q45. Stages which do not support File Formats are :

Select 2 option(s):

Internal named Stage

External Named Stage

Internal User Stage

Internal Table Stage

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

Select 1 option(s):

ACCOUNTADMIN

PUBLIC

CUSTOM

SYSADMIN

SECURITYADMIN

Q47. Snowflake data storage costs include which types of data?

Select 2 option(s):

Data retained to enable data recovery (time travel and fail-safe)

Semi-Structured data - additional fees

Cached Results

Persistent data stored in permanent tables

Metadata

Q48. Normally a warehouse begins to consume credits once all the servers are provisioned for the warehouse.

Select 1 option(s):

TRUE

FALSE

Q49. How many virtual warehouses can be created in a Snowflake Account?

Select 1 option(s):

As many as needed

Q50. Which type of view has an extra layer of protection to hide the SQL code from unauthorized viewing?

Select 1 option(s):

Permanent

Secure

Standard

Materialized

Q51. Scaling up can be configured as automated process.

Select 1 option(s):

FALSE

TRUE

Q52. John is having SECURITYADMIN role. He created a custom DBA_ROLE. He Granted SYSADMIN role to DBA_ROLE. He created a user ‘Monica‘. John granted DBA_ROLE to Monica. Monica creates a Database Monica_DB. Monica then created a Table T1 in Monica_DB under PUBLIC schema. What should John do to access Table T1 created by Monica?

Select 1 option(s):

GRANT ROLE DBA_ROLE TO John; USE DATABASE monica_db; Select * from t1;

GRANT TOLE DBA_ROLE TO John; USE ROLE DBA_ROLE; USE DATABASE monica_db; Select * from t1;

USE ROLE SECURITYADMIN; USE DATABASE monica_db; Select * from t1;

USE ROLE dba_role; USE DATABASE monica_db; Select * from t1;

Q53. Monica is an admin and wants to see the usage of Fail-Safe. How can she get details on Fail-Safe usage?

Select 1 option(s):

Admins can view Fail-safe use in the Snowflake Web UI under Account > Billing & Usage

None of these

Monica should contact Snowflake support

Fail Safe usage details are never available to customer

Q54. John has a same query which runs for around 40 minutes every hour. Most of the time the data in the table remain same. What could be best option for John to keep warehouse running or AUTO_SUSPEND?

Select 1 option(s):

John should set AUTO_SUSPEND to 40 minutes

John should not set AUTO_SUSPEND

John should set AUTO_SUSPEND to 10 minutes

John doesn‘t have to set AUTO_SUSPEND. Snowflake will take care of suspension as needed.

Q55. As an ACCOUNTADMIN, how can you find the credit usage of a warehouse?

Select 3 option(s):

Run SQL query on ACCOUNT_USAGE table under Snowflake Database

Run SQL query on WAREHOUSE_METERING_HISTORY view under ACCOUNT_USAGE Schema

Using Web interface > Account > Usage

Run SQL query on METERING_HISTORY view under ACCOUNT_USAGE Schema

Q56. Choose the right hierarchy of Snowflake objects from the given options.

Select 1 option(s):

ACCOUNT > DATABASE > SCHEMA > TABLE

ACCOUNT > SCHEMA > DATABASE > TABLE

ACCOUNT > DATABASE > STAGE > SCHEMA

ACCOUNT > STAGE > DATABASE > SCHEMA > TABLE

Q57. Scaling Out is an automated process. It automatically scale out during peak times and scale back during slow times

Select 1 option(s):

TRUE

FALSE

Q58. Zero Copy Cloning allows users to have multiple copies of your data without the additional cost of storage usually associated with replicating data. Which other statements about the Cloning features in Snowflake are True?

Select 3 option(s):

Cloning is an efficient and cost effective approach for code migration for Agile Release Management

Clone is a point in time version of the table data as of the time the clone was made

The clone is a pointer to the original table data

Any new record in the parent table gets available in the cloned table

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

Select 1 option(s):

TRUE

FALSE

Q60. Which objects are securable objects in Snowflake?

Select 4 option(s):

Table

Database

File Format

Warehouse

Unattempted

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

Select 1 option(s):

OWNERSHIP

OPERATE

MONITOR

USAGE

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

Select 3 option(s):

Only MFA is supported by all the Snowflake editions

MFA (Multi-factor authentication)

OAuth

Only MFA and SSO are supported by all the Snowflake editions

SSO

Q63. When a warehouse is resized, which queries make use of the new size?

Select 1 option(s):

Only currently running queries

Only subsequent queries

Both current and subsequent queries

Q64. What actions can be performed when an error is encountered while loading data from a file?

Select 4 option(s):

Continue loading the file.

Abort the load operation

skip file when the number of errors in the file is equal to 10

Skip file

Unattempted

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

Select 1 option(s):

Yes

Snowflake Assessment: 65 Practice Questions for Tech Interviews

Q1. Loading using SNOWPIPE is best useful for:

Q2. John wants to share a VIEW with Monica but John wants to hide View definition and details from Monica. What type of view John should create and share with Monica?

Q3. Monica is an ACCOUNTADMIN and wants to change the Time Travel retention period of table T1 of database DB1 to 15 days. Which options will help her to set the new Time Travel retention period?

Q4. 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?

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

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

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

Q8. John created a Database and set the DATA_RETENTION_TIME_IN_DAYS as 30 days, but he did not set the DATA_RETENTION_TIME_IN_DAYS in one of the table ‘T1‘. After 5 days, he accidently dropped table ‘T1‘. Will he able to recover the table T1?

Q9. Multi-cluster warehouse credit billing is based on which factors? (Select 2)

Q10. If you are ACCOUNTADMIN, you can query and recover data from Fail-safe without the help of Snowflake support. (TRUE/FALSE)

Q11. If a DBA_ROLE creates a Database. Later the DBA_ROLE was dropped. Who will own the Database now which was created by DBA_ROLE?

Q12. Which cache type gets purged regularly?

Q13. Stored Procedure supports

Q14. The VARIANT data type imposes a 10 MB to 100 MB (compressed) size limit on individual rows.(TRUE / FALSE)

Q15. Snowflake is a Cloud Data Platform delivered as a service.

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

Q17. The data stored as part of fail-safe is part of storage costs charged to customers.

Q18. Please choose the correct statements about Micro-Partitions (Select 2)

Q19. What are the best practices for JOIN on unique keys?

Q20. The following SQL statement will require a running warehouse – CREATE TABLE T1 (NAME (VARCHAR20), ADDRESS VARCHAR (50));

Q21. User A and User B can access one another‘s result sets from the Results Cache, as long as which of the following are true? (Choose two)

Q22. Which command will return information about the current database?

Q23. There are two types of Snowflake Planned releases. Please choose all applicable.

Q24. How often does Snowflake release new features?

Q25. Who can create shares?

Q26. Which is not the function of Optimization activities of Cloud Services layer?

Q27. John created a table EMPLOYEE in worksheet 1 (Session1 ) but when he ran SELECT * FROM EMPLOYEE in worksheet 2 (Session 2) after setting up the correct Role, Database and Schema, he received error ‘ Table doesn‘t exist‘. What could be the reason?

Q29. John wants to create a Warehouse which can AUTO_SUSPEND in 3 Minutes. There is no option in UI where he can select 3 Minutes. What will you suggest him to do?

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

Q31. AWS PrivateLink is an AWS service for creating private VPC endpoints that allow direct, secure connectivity between AWS VPCs without traversing the public Internet. (TRUE / FALSE)

Q32. Which of the following object types are child objects within schemas?

Q33. John has setup process to load specific set of files using both Bulk and Snowpipe. This is best practice to avoid any missed loading either by Bulk loading or Snowpipe. (TRUE /FALSE)

Q34. How much is the Time-Travel retention period of Permanent Table (in Standard Edition)?

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

Q36. Scaling a Warehouse DOWN will decrease the number of servers (e.g., Large to Medium).

Q37. How many maximum columns (or expressions) are recommended for a cluster key?

Q38. Snowflake Administrators should utilize resource monitors to help control costs and avoid unexpected credit usage. Which of the following actions can Snowflake‘s resource monitor triggers initiate automatically? (Check all that apply)

Q39. Which are the correct statements about Snowflake data Storage?

Q40. Which of the following are performed by the Cloud Services layer? (Select 4)

Q41. Which of the following options would result in a column named FIRST NAME being sandwiched between to percent signs? (Check all that apply)

Q42. Auto-suspend and auto-resume apply only to the entire warehouse and not to the individual clusters in the warehouse.

Q43. What is the maximum Time-Travel retention period of Standard Edition?

Q44. A user can connect Snowflake in multiple ways. Please select

Q45. Stages which do not support File Formats are :

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

Q47. Snowflake data storage costs include which types of data?

Q48. Normally a warehouse begins to consume credits once all the servers are provisioned for the warehouse.

Q49. How many virtual warehouses can be created in a Snowflake Account?

Q50. Which type of view has an extra layer of protection to hide the SQL code from unauthorized viewing?

Q51. Scaling up can be configured as automated process.

Q53. Monica is an admin and wants to see the usage of Fail-Safe. How can she get details on Fail-Safe usage?

Q54. John has a same query which runs for around 40 minutes every hour. Most of the time the data in the table remain same. What could be best option for John to keep warehouse running or AUTO_SUSPEND?

Q55. As an ACCOUNTADMIN, how can you find the credit usage of a warehouse?

Q56. Choose the right hierarchy of Snowflake objects from the given options.

Q57. Scaling Out is an automated process. It automatically scale out during peak times and scale back during slow times

Q58. Zero Copy Cloning allows users to have multiple copies of your data without the additional cost of storage usually associated with replicating data. Which other statements about the Cloning features in Snowflake are True?

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

Q60. Which objects are securable objects in Snowflake?

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

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

Q63. When a warehouse is resized, which queries make use of the new size?

Q64. What actions can be performed when an error is encountered while loading data from a file?

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

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