SnowPro Certification

Ace your homework & exams now with Quizwiz!

Which of the following are options when creating a Virtual Warehouse? A. Auto-suspend B. Storage size C. Auto-resume D. Server Count

A & C

Which of the following are true about the variant data type in Snowflake? A. Optimized storage based on repeated elements B. Stored in a separate file format from structured data C. Can be queried using JSON path notation D. Requires a customer mapping for each record type

A & C

Which are the supported data types for a JSON name/value pair? A number (integer or floating point) A string (in double quotes) A Boolean (true or false) An array (in square brackets) An object (in curly braces) Null complex datatype

A number (integer or floating point) A string (in double quotes) A Boolean (true or false) An array (in square brackets) An object (in curly braces) Null

Currently in snowflake, replication is supported for which type of objects A) Databases B) Users C) Roles D) Warehouses

A) Databases

What is the technique called which snowflake uses to limit the number of micro-partitions retrieved as part of a query? A) Pruning B) Selective Filter C) Indexing D) Clustering

A) Pruning

In snowflake, what are the two major cost categories A) Storage B) Compute C) Cloud services D) Network Traffic

A) Storage B) Compute

While loading data through the COPY command, you can transform the data. Which of the below transformations are allowed? A) TRUNCATECOLUMNS B) REORDER COLUMNS C) OMIT COLUMNS D) CAST E) AGGREGATE

A) TRUNCATECOLUMNS B) REORDER COLUMNS C) OMIT COLUMNS D) CAST

Which database objects can be shared using snowflake's secure data sharing A) Tables B) External tables C) Secure views D) Secure materialized views E) Secure UDFs F) Roles

A) Tables B) External tables C) Secure views D) Secure materialized views E) Secure UDFs

Select the objects which do not have a fail safe period A) Transient table B) Temporary table C) Permanent table

A) Transient table B) Temporary table

This object in snowflake returns a set of rows instead of a single, scalar value, and can be accessed in the FROM clause of a query A) UDTF B) UDF C) Stored procedure

A) UDTF

Let us imagine you have a permanent table named EMPLOYEE, you have dropped the table. Then you created another table with the same name. If you execute the UNDROP command now, what will happen? A) UNDROP command will fail B) It will work similar to insert overwrite C) The new table will be renamed with a suffix of v1.

A) UNDROP command will fail

Which of the below privileges are required to add clustering on a table A) USAGE or Ownership on the schema and database B) MONITOR USAGE on the table C) ALTER privilege on the table

A) USAGE or Ownership on the schema and database

What are the valid data loading options in snowflake? A) Using snowsql and sql B) Using snowpipe C) Using 3rd part ETL tool D) Using the cloud providers data upload tools

A) Using snowsql and sql B) Using snowpipe C) Using 3rd part ETL tool

You want to query the history of task usage within a specified date range. What will you do? A) You will query the TASK_HISTORY table function B) You will run 'SHOW TASK HISTORY' C) You will run 'DESCRIBE TASKS'

A) You will query the TASK_HISTORY table function

You have several CSV files loaded into your named snowflake internal stage. You want to load files from the stage into a table using pattern matching to only load uncompressed CSV files whose names include the string sales. Which is the command that you will use to do the same? A) copy into mytable from @my_int_stage file_format = (format_name = myformat) pattern='.*sales.*[.]csv'; B) copy into mytable from @my_int_stage regex='.*sales.*[.]csv'; C) copy into mytable from @my_int_stage match_pattern='.*sales.*[.]csv';

A) copy into mytable from @my_int_stage file_format = (format_name = myformat) pattern='.*sales.*[.]csv';

What is the default option for ON_ERROR clause of COPY command for bulk loading? CONTINUE SKIP_FILE SKIP_FILE_10 ABORT_STATEMENT

ABORT_STATEMENT

Which role in Snowflake allows a user to administer users and manage all database objects? SYSADMIN SECURITYADMIN ACCOUNTADMIN ROOT

ACCOUNTADMIN

Select the most accurate statement regarding Snowflake's transaction support ACID Compliant Requires special user configuration BASE compliant Works like a NoSQL database

ACID Compliant

If you would like to disable the query cache, what will you do? ALTER SESSION SET USE_CACHED_RESULT = FALSE; ALTER SESSION SET USE_CACHED_RESULT = OFF; ALTER SESSION SET USE_QUER_CACHE = TRUE;

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

Choose the answer which true about key rotation in snowflake Account and table master keys are automatically rotated by Snowflake when they are more than 30 days old Only account master key is rotated by snowflake when it is required Only table master key is rotated when it is required Key rotation is an expensive operation hence it is done manually when required

Account and table master keys are automatically rotated by Snowflake when they are more than 30 days old

When you sign up for a snowflake trial account, what are three snowflake editions that are offered A) Free-Tier B) Standard C) Enterprise Data Security D) Enterprise E) Business critical

B) Standard D) Enterprise E) Business critical

A task can execute multiple SQL statement. TRUE FALSE

FALSE

You need to contact Snowflake Support team if retrieval of data is required from fail safe (TRUE OR FALSE)

TRUE

What are the various stages available in Snowflake A) USER B) TABLE C) NAMED INTERNAL D) NAMED EXTERNAL E) ACCOUNT STAGE F) SCHEMA STAGE

A) USER B) TABLE C) NAMED INTERNAL D) NAMED EXTERNAL Note: SNOWFLAKE supports three type of stages 1. USER 2. TABLE 3. NAMED Named stages are again of two types internal and external USER and Table stages are automatically available in snowflake and are not needed to be created or configured https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage.html

After how many days do the load metadata of a table expire? A) 64 B) 14 C) 7 D) 365

A) 64

Which of the below are binary formats? AVRO JSON ORC PARQUET

AVRO ORC PARQUET

Tri-Secret Secure option is available in which snowflake edition Business critical or higher Enterprise Edition All editions

Business critical or higher

Select the term that is associated with compute layer? A) Query optimization B) Query planning C) Query processing

C) Query processing

Load performance in snowflake is fastest for which file format CSV ORC AVRO PARQUET

CSV

True or False: Snowflake charges a premium for storing semi-structured data.

False

True or False: Snowflake only works with cloud based tools.

False

True or False: The query profiler view is only available for completed queries.

False

Which parameter controls the maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. MAX_DATA_EXTENSION_TIME_IN_DAYS MAX_DATA_RETENTION_TIME_IN_DAYS MAX_DATA_EXTENSION_TIME_IN_MONTHS

MAX_DATA_EXTENSION_TIME_IN_DAYS

Which constrains are enforced in snowflake Referential integrity constraints NOT NULL constraint UNIQUE Constraint

NOT NULL constraint

Which of the two statements are true about the variant data type in SnowFlake? Optimized storage based on repeated elements Stored in a seperate file format from structured data Can be queried using json path notation Requires a custom mapping for each record type

Optimized storage based on repeated elements Can be queried using json path notation

Which are the two variant columns available in a snowflake table loaded by kafka connector RECORD_CONTENT RECORD_METADATA RECORD_KEY

RECORD_CONTENT RECORD_METADATA

When adding Consumers to Outbound Shares, what types of Consumer Accounts can be selected? Reader GCP Azure Full AWS

Reader Full Explanation Refer Link for details - https://docs.snowflake.com/en/user-guide/data-sharing-intro.html

Which are the key concepts that will need to be considered while loading data into snowflake STAGE OBJECT FILE FORMAT TRANSFORMATION AND ERROR VALIDATION FILE SIZE

STAGE OBJECT FILE FORMAT TRANSFORMATION AND ERROR VALIDATION

You want to load a JSON file but would like to remove the outer array structure and load the records into separate table rows. Which file format option would you use? STRIP_OUTER_ARRAY TRUNCATE_OUTER_ARRAY UNSET_OUTER_ARRAY DROP_OUTER_ARRAY

STRIP_OUTER_ARRAY

What is the name of the Snowflake's Command Line Interface tool? A) SnowCLI B) SnowSQL C) SnowCMD D) SnowSpark

SnowSQL

Which of the following are Snowflake view types? Standard External Transient Materialized Permanent Secure

Standard Materialized Secure https://docs.snowflake.com/en/user-guide/views-introduction.html

Insert-only streams are supported on external tables only and it is currently a preview feature (TRUE OR FALSE)

TRUE

JSON data is a hierarchical collection of name/value pairs grouped into objects and arrays TRUE FALSE

TRUE

Looker is a snowflake business intelligence partner (TRUE OR FALSE)

TRUE

True or False: Snowflake caches are automatically invalidated if the underlying data changes.

True

You have two data warehouses in your snowflake instance. You have updated the data in the storage layer using one of the warehouses. When will the other warehouse be able to see the data A) Immediately B) After 30 minutes once snowflake completes data synchronization C) You will need to trigger the data synchronization process for the other warehouse to see the data

A) Immediately

Lets say that you have scheduled a task. At time T0, the task has started executing. If it is still executing when the next scheduling execution time occurs, what happens to that task A) It will be skipped B) It will wait for the previous task to complete C) It will be aborted

A) It will be skipped

Which cloud providers are supported by snowflake A) Google Cloud Platform B) Azure C) AWS

A) Google Cloud Platform B) Azure C) AWS

Snowflake is compliant with which certifications? A) HIPAA, PCI DSS, HIPAA, PCI DSS and FedRAMP B) HIPAA, PCI DSS C) Only HIPAA D) Only FedRAMP

A) HIPAA, PCI DSS, HIPAA, PCI DSS and FedRAMP

When you use this parameter with SHOW STREAMS, The output also includes an additional dropped_on column, which displays 1. Date and timestamp (for dropped streams). 2.NULL (for active streams). A) HISTORY B) TERSE C) DROPPED=TRUE

A) HISTORY

When unloading data into multiple files, you will use this copy option to specify the maximum size of each file created A) MAX_FILE_SIZE B) MAX_SIZE C) MAX_FILE_BYTES

A) MAX_FILE_SIZE

Select the snowflake edition that allow only a maximum of 1 day of time travel A) Standard B) Enterprise C) VPS D) Business Critical

A) Standard

What should be your minimum snowflake edition for data sharing capability A) Standard B) Enterprise C) Business critical

A) Standard

Load history for bulk data load is stored in the metadata of the target table for 64 days (TRUE OR FALSE)

TRUE Note: Bulk data load Stored in the metadata of the target table for 64 days. Available upon completion of the COPY statement as the statement output. Snowpipe Stored in the metadata of the pipe for 14 days. Must be requested from Snowflake via a REST endpoint, SQL table function, or ACCOUNT_USAGE view. https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html#load-history

Client has ODBC or JDBC available in their system but they do not have snowflake drivers. Client will still be able to connect to snowflake? TRUE FALSE

FALSE

In a multi-cluster warehouse, you will need to manually scale the warehouses(minimum cluster to maximum cluster) (TRUE OR FALSE)

FALSE

JSON and PARQUET files can be loaded to columns in the same table TRUE FALSE

FALSE

Loading data using snowpipe REST API is supported for external stage only TRUE FALSE

FALSE

Once you have selected a warehouse size, you cannot resize the size of the warehouse (TRUE OR FALSE)

FALSE

Reclustering in Snowflake requires manual configuration (TRUE OR FALSE)

FALSE

Scaling down a virtual warehouse(e.g from a large warehouse to a small one) is an automated process. TRUE FALSE

FALSE

SnowPipe can load a file with same name if it has been modified later. (TRUE OR FALSE)

FALSE

Snowflake allows only loading of structured data (TRUE OR FALSE)

FALSE

Snowpipe guarantees that files are loaded in the same order they are staged (TRUE OR FALSE)

FALSE

Temporary tables cannot be created with a clustering key defined TRUE FALSE

FALSE

The insertReport SNOWPIPE API can retrieve file ingestion events and report it. The events are retained for infinite time until deleted manually. (TRUE OR FALSE)

FALSE

When migrating from Teradata to snowflake, you will also need to migrate the indexes. TRUE FALSE

FALSE

When you are loading data through the COPY command, the table and the file being loaded must have the same order of columns FALSE TRUE

FALSE

When you load a table from a file through COPY command, the file should have the same number and order of columns as the table (TRUE OR FALSE)

FALSE

When you load data using Snowpipe, loads are always performed in a single transaction FALSE TRUE

FALSE

You can load data using PUT command through worksheets in snowflake web ui (TRUE OR FALSE)

FALSE

You can map snowflake to any s3 bucket and can query the data directly as long as the data is in Parquet or ORC format TRUE FALSE

FALSE

You can use the query profiler view only for completed queries (TRUE OR FALSE)

FALSE

You have a materialized view and you want to track changes in the view. You can use streams to track that change (TRUE OR FALSE)

FALSE

You have specified PURGE=TRUE in your COPY command. Due to a network failure the data files could not be purged after successful load. You will get an error returned by snowflake due to this failure. (TRUE OR FALSE)

FALSE

The data stored in temporary table does not contribute to storage cost (TRUE OR FALSE)

FALSE Note: For the duration of the existence of a temporary table, the data stored in the table contributes to the overall storage charges that Snowflake bills your account. To prevent any unexpected storage changes, particularly if you create large temporary tables in sessions that you maintain for periods longer than 24 hours, Snowflake recommends explicitly dropping these tables once they are no longer needed. You can also explicitly exit the session in which the table was created to ensure no additional charges are accrued.

Micropartitions can be modified after creation of the table TRUE FALSE

FALSE Note: In snowflake data is physically stored into micro-partitions in proprietary columnar format. As a used you do not have direct access to micropartitions. Snowflake manages the micropartitions. Also micro partitions are immutable which means that once a micro partition is created, it cannot be changed. When a row is updated, the micro partition containing that row is copied to a new micro partition and then the updated row is inserted into that partition. The old micro-partition is marked for deletion. As a used, you however have control to create clustering on your table which helps to re-arrange the micro-partitions based on the cluster keys for faster query retrieval.

By executing the 'SHOW TABLES' command, we can list all the tables in all the schemas even if we do not have access to all the tables TRUE FALSE

FALSE Note: SHOW TABLES Lists the tables for which you have access privileges, including dropped tables that are still within the Time Travel retention period and, therefore, can be undropped. The command can be used to list tables for the current/specified database or schema, or across your entire account.

Time travel can be disabled at the account level TRUE FALSE

FALSE Note: Time Travel cannot be disabled for an account; however, it can be disabled for individual databases, schemas, and tables by specifying DATA_RETENTION_TIME_IN_DAYS with a value of 0 for the object. https://docs.snowflake.com/en/user-guide/data-time-travel.html#enabling-and-disabling-time-travel

Virtual warehouses cannot be resized while they are running TRUE FALSE

FALSE Note: Warehouses can be started and stopped at any time. They can also be resized at any time, even while running, to accommodate the need for more or less compute resources, based on the type of operations being performed by the warehouse. https://docs.snowflake.com/en/user-guide/warehouses-overview.html

Providers can list data on the Marketplace without contacting Snowflake TRUE FALSE

FALSE Explanation Refer Link - https://docs.snowflake.com/en/user-guide/data-marketplace-intro.html If you would like to request new data to be added to the Snowflake Data Marketplace you can click the Is there data you'd like to see on the Marketplace? link in the product UI or use this web registration form to make your request. Snowflake team needs to be consulted to get the data listed on Marketplace.

Information Schema Views will require a warehouse to execute whereas SHOW command does not require one. (TRUE OR FALSE)

TRUE

Multi-factor Authentication can be used for connecting to Snowflake via the Snowflake JDBC driver TRUE FALSE

TRUE

Non-native values such as dates and timestamps are stored as strings when loaded into a VARIANT column TRUE FALSE

TRUE

Only named stages (internal or external) and user stages are supported for COPY transformations (TRUE OR FALSE)

TRUE

Organizing input data by granular path can improve load performance TRUE FALSE

TRUE

Query load is calculated by dividing the execution time (in seconds) of all queries in an interval by the total time (in seconds) for the interval. TRUE FALSE

TRUE

SNOWPIPE REST APIs support both internal and external stage (TRUE OR FALSE)

TRUE

Snowflake automatically stores data in encrypted form in all editions (TRUE OR FALSE)

TRUE

Snowflake can carry out transformations after loading files staged by partner software (ELT) (TRUE OR FALSE)

TRUE

Snowflake has scalar and tabular user-defined functions (UDFs), with support for both SQL and JavaScript. (TRUE OR FALSE)

TRUE

Snowflake is a SaaS offering TRUE FALSE

TRUE

Snowflake recommends to compress your data files when you are loading large data sets. (TRUE OR FALSE)

TRUE

Snowflake supports multi-factor authentication (MFA) to provide increased login security for users connecting to Snowflake. At this time, Snowflake users are not automatically enrolled in MFA. (TRUE OR FALSE)

TRUE

Snowflake supports specifying a SELECT statement instead of a table in the COPY INTO <location> command (TRUE OR FALSE)

TRUE

Snowpipe continuously loads micro-batches of data from an external stage location (Amazon S3, Google Cloud Storage, or Microsoft Azure) into a staging table. TRUE FALSE

TRUE

Snowpipe is a serverless function (TRUE OR FALSE)

TRUE

Streams cannot track changes in materialized views TRUE FALSE

TRUE

Streams ensure exactly once semantics for new or changed data in a table (TRUE OR FALSE)

TRUE

The Kafka connector continuously loads records from one or more Apache Kafka topics into an internal (Snowflake) stage and then into a staging table using Snowpipe. TRUE FALSE

TRUE

The Snowflake Web UI data loading wizard is only intended for loading small numbers of files of limited size (up to 50 MB). (TRUE OR FALSE)

TRUE

The VALIDATION_MODE parameter does not support COPY statements that transform data during a load. (TRUE OR FALSE)

TRUE

The amount of storage used by failsafe can be viewed under Account->Billing & Usage section TRUE FALSE

TRUE

The interactions with data are initialized through the services layer TRUE FALSE

TRUE

The table functions in INFORMATION_SCHEMA can be used to return account-level usage and historical information for storage, warehouses, user logins, and queries (TRUE OR FALSE)

TRUE

To help avoid exceeding the rate limit (error code 429), snowflake recommends relying more heavily on insertReport than loadHistoryScan (TRUE OR FALSE)

TRUE

Two virtual warehouses can access the same data at the same time without any contention issues TRUE FALSE

TRUE

When a network policy includes values in both the allowed and blocked IP address lists, Snowflake applies the blocked IP address list first TRUE FALSE

TRUE

When a network policy includes values in both the allowed and blocked IP address lists, Snowflake applies the blocked IP address list first. (TRUE OR FALSE)

TRUE

When cloning a table, if the COPY GRANTS keywords are not included in the CREATE <object> statement, then the new object does not inherit any explicit access privileges granted on the original table but does inherit any future grants defined for the object type in the schema TRUE FALSE

TRUE

When staging uncompressed files in a Snowflake stage, the files are automatically compressed using gzip, unless compression is explicitly disabled. (TRUE OR FALSE)

TRUE

When you clone a table, it does not include the load history of the source table, and hence the data files that were already loaded in source table can be reloaded in the clone table TRUE FALSE

TRUE

While sharing data, consumer account and provider account must be in the same region (TRUE OR FALSE)

TRUE

With respect to snowflake security, Tri-secret requires that customers manage their keys TRUE FALSE

TRUE

You have two types of named stages, one is external stage and the other one is internal stage. External stage will always require a cloud storage provider TRUE FALSE

TRUE

You need snowflake instances in different regions. You will need to have separate account for each region (TRUE OR FALSE)

TRUE

A warehouse can be assigned to a single resource monitor only (TRUE OR FALSE)

TRUE Note: A single monitor can be set at the account level to control credit usage for all warehouses in your account. In addition, a monitor can be assigned to one or more warehouses, thereby controlling the credit usage for each assigned warehouse. Note, however, that a warehouse can be assigned to only a single resource monitor.

How do you set a return value in a task? create task set_return_value warehouse=return_task_wh schedule='1 minute' as call system$set_return_value('The quick brown fox jumps over the lazy dog'); create task set_return_value warehouse=return_task_wh schedule='1 minute' as call system$set_return_code('The quick brown fox jumps over the lazy dog'); create task set_return_value warehouse=return_task_wh schedule='1 minute' as call set_return_value('The quick brown fox jumps over the lazy dog');

create task set_return_value warehouse=return_task_wh schedule='1 minute' as call system$set_return_value('The quick brown fox jumps over the lazy dog');

To provide failover protection, how many availability zones does snowflake replicate to one two three as configured by user

two

You want to get the DDL statement of a snowflake table. What is the command that you will use? select get_ddl('table', 'mydb.public.salesorders'); show table 'mydb.public.salesorders'; show table like 'mydb.public.salesorders';

select get_ddl('table', 'mydb.public.salesorders');

Which of the below statement will you use to recreate a specified object? A) GET_DDL B) GET_SCHEMA C) SHOW SCHEMA

A) GET_DDL

Which two options mentioned below will you choose to concat a column named 'FRUIT_DESCRIPTION' between two % signs? '%'||FRUIT_DESCRIPTION||'%' CONCAT('%',CONCAT(FRUIT_DESCRIPTION,'%')) %+FRUIT_DESCRIPTION+% SUBSTR(%,FRUIT_DESCRIPTION,%)

'%'||FRUIT_DESCRIPTION||'%' CONCAT('%',CONCAT(FRUIT_DESCRIPTION,'%'))

Which command is used to download data from Snowflake stage PUT GET COPY DUMP

GET

An empty table with no micro-partition will have a clustering depth of 1 0 -1 99

0 Note: The clustering depth for a populated table measures the average depth (1 or greater) of the overlapping micro-partitions for specified columns in a table. The smaller the average depth, the better clustered the table is with regards to the specified columns. Clustering depth can be used for a variety of purposes, including: 1. Monitoring the clustering "health" of a large table, particularly over time as DML is performed on the table. 2. Determining whether a large table would benefit from explicitly defining a clustering key. A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 0.

How much Snowpipe charge for the files in Queue 0.06 credits per 100 files queued No charges for files in Queue 0.06 credits per 10000 files queued 0.06 credits per 1000 files queued

0.06 credits per 1000 files queued https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare.html#:~:text=Continuous%20Data%20Loads%20(i.e.%20Snowpipe)%20and%20File%20Sizing,-Snowpipe%20is%20designed&text=This%20overhead%20increases%20in%20relation,credits%20per%201000%20files%20queued.

What is the recommended maximum data size limitations for Parquet files? 100 MB 10 MB 1 GB 3 GB

1 GB Note: Currently, data loads of large Parquet files (e.g. greater than 3 GB) could time out. Split large files into files 1 GB in size (or smaller) for loading.

What is the maximum number of child tasks can a task have? 50 100 1000 10

100

What is the recommended file size, to optimize the number of parallel operation for a load 100-250 MB (Compressed) 1 MB to 10 MB (Compressed) None of the above 50 MB to 150 MB (Compressed)

100-250 MB (Compressed) The number of load operations that run in parallel cannot exceed the number of data files to be loaded. To optimize the number of parallel operations for a load, we recommend aiming to produce data files roughly 100-250 MB (or larger) in size compressed. Refer Link - https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare.html

What is the recommended file size to optimize the number of parallel operations for a load? 100-250 MB(or larger) in size compressed 10 MB to 100 MB in size un compressed. less than 1 MB compressed

100-250 MB(or larger) in size compressed Note: https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare.html#file-sizing-best-practices-and-limitations

What is the size limit on the variant data type in snowflake 16 MB 64 MB 32 MB

16 MB

What is the size limit imposed by VARIANT data type. 100 MB compressed 16 MB compressed 16 MB uncompressed 100 MB compressed

16 MB compressed

You are using a 2XL warehouse which has 32 nodes and 8 cores in each node. When you submit a query to this warehouse, snowflake will try to split the query into how many partitions? (Provided that the query is eligible for splitting) 256 32 8

256 Note: partitions = cores x nodes

If a transaction is running on a session and the session disconnects abruptly, the transaction will be in a detached state. It will not allow the transaction to commit or roll back. The locks on the resources will also not be released. If you do not do anything and let snowflake abort the transaction, how long will you need to wait? 4 hours 15 minutes 1 hour

4 hours Note: If a transaction is running in a session and the session disconnects abruptly, preventing the transaction from committing or rolling back, the transaction is left in a detached state, including any locks that the transaction is holding on resources. If this happens, you might need to abort the transaction. To abort a running transaction, the user who started the transaction or an account administrator can call the system function, SYSTEM$ABORT_TRANSACTION. If the transaction is left open, Snowflake typically rolls back the transaction after it has been idle for four hours. https://docs.snowflake.com/en/sql-reference/transactions.html#aborting-transactions

After how many days does the load metadata expire 30 60 64

True or False: A table in Snowflake can contain both a variant column and a timestamp

True or False: The warehouse cache may be reset if a running warehouse is suspended and then resumed.

Which of the following DML commands isn't supported by Snowflake? A. UPSERT B. MERGE C. UPDATE D. TRUNCATE TABLE

Please look at the below two queries A. SELECT * FROM TABLE1 WHERE YEAR = 2015 AND UPPER(COL1) = 'COLVAL'; B. SELECT * FROM TABLE1 WHERE YEAR = 2015 AND COL1 ='COLVAL'; Which one of these will scan more partitions? Note: COL1 stores values both in upper and lower case A B Both will scan the same number of partitions

A Note: This probably will not come in the certification exam, but it is important to know this when you are working on a project. Snowflake stores the metadata for each column in a metadata services layer which includes the range of values of the columns in the micro partition. In this case, the range of values stored in the metadata layer for COL1 is stored in its raw form which is a mix of upper and lower case. Because of this when you apply the UPPER function, the metadata services layer cannot use the information stored in the layer to fetch the micro partitions for the range of the rows

Which of the following are true of Multi-Cluster warehouses? A. Adds clusters automatically based on query activity B. Sizes each cluster optimally based on the queries C. Scales down when query activity slows down D. Multi-cluster warehouses will never auto-suspend

A & C

Which of the following statements apply to Snowflake in terms of security? A. Snowflake leverages a role-based access control model B. Snowflake requires you to configure an IAM user in order to connect to the database C. All data in Snowflake is encrypte D. Snowflake can run within a customer's VPC

A & C

Select all of the answers that describe SF micro-partitions. Micro-partitions A. Are the physical data files that comprise SF's logical tables B. Are written to a customer configured cloud storage location C. Will be updated as their row & column values are updated D. Enable horizontal and vertical query pruning

A & D

Which of the following are TRUE about SF data sharing? A. Consumers can query shared tables in the same query as their own tables B. Producers can share data to other databases such as Postgres and MySQL C. You don't need a SF virtual WH to query shared data D. Data sharing is integrated with role-based access control

A & D

What is the recommended approach for making a variant column accessible in a BI tool A pre-defined mapping A view Leveraging a json parser BI tool cannot access json

A view

You have a warehouse. It ran for 62 seconds and then was suspended. After that it resumed and ran for 20 seconds. For how many seconds will you be billed A) 122 B) 20 C) 92

A) 122 Note: When a virtual warehouse starts for the first time, the first one minute is anyway charged after that the billing is per second Warehouses are only billed for credit usage when they are running. When a warehouse is suspended, it does not accrue any credit usage. The credit numbers shown here are for a full hour of usage; however, credits are billed per-second, with a 60-second (i.e. 1-minute) minimum: Each time a warehouse is started or resized to a larger size, the warehouse is billed for 1 minute's worth of usage based on the hourly rate shown above. After 1 minute, all subsequent billing is per-second. Stopping and restarting a warehouse within the first minute does not change the amount billed; the minimum billing charge is 1 minute. When a warehouse is increased in size, credits are billed only for the additional servers that are provisioned. For example, changing from Small (2) to Medium (4) results in billing charges for 1 minute's worth of 2 credits. https://docs.snowflake.com/en/user-guide/credits.html#virtual-warehouse-credit-usage

Which character below identifies a table stage A) @% B) @ C) %

A) @%

Which character combination identifies a user stage A) @~ B) ~ C) @

A) @~

Please select the ones that are true for a snowflake task A) A task can execute a single SQL Statement B) A task can execute a call to a store procedure C) A task can execute multiple SQL statements D) A task can execute a function

A) A task can execute a single SQL Statement B) A task can execute a call to a store procedure

What can you expect if the filters specified in an INFORMATION_SCHEMA query are not sufficiently selective? A) AN ERROR B) WILL SHOW RESULTS WITH AN WARNING C) WILL SWITCH TO A LARGE WAREHOUSE

A) AN ERROR

If you logon to snowflake using https://mycompany.snowflakecomputing.com/, then what is the cloud provider and region you are logging into? A) AWS and us-west-2 B) Azure and us-east-1 C) AWS and us-east-2

A) AWS and us-west-2 Note: If an account has just the account name followed by snowflakecomputing.com, it means that the cloud provider is AWS and the region is us-west-2. This is the first cloud provider and region where snowflake was deployed

Which role or privileges are required to view TASK_HISTORY? A) Account administrator (i.e. users with the ACCOUNTADMIN role) B) Task owner (i.e. role that has the OWNERSHIP privilege on a task) C) Any role that has the global MONITOR EXECUTION privilege D) Any one with SYSADMIN role

A) Account administrator (i.e. users with the ACCOUNTADMIN role) B) Task owner (i.e. role that has the OWNERSHIP privilege on a task) C) Any role that has the global MONITOR EXECUTION privilege

Which roles can use SQL to view the task history within a specified date range? A) Account administrator (i.e. users with the ACCOUNTADMIN role). B) Task owner (i.e. role that has the OWNERSHIP privilege on a task). C) Any role that has the global MONITOR EXECUTION privilege. D) SECURITYADMIN

A) Account administrator (i.e. users with the ACCOUNTADMIN role). B) Task owner (i.e. role that has the OWNERSHIP privilege on a task). C) Any role that has the global MONITOR EXECUTION privilege.

What is the storage hierarchy in snowflake? A) Account->Database->Schemas->Objects B) Organization->folder->project->resource C) Database->Schemas->Objects

A) Account->Database->Schemas->Objects

Select all options that are true for ORDER BY in snowflake A) All data is sorted according to the numeric byte value of each character in the ASCII table. UTF-8 encoding is supported B) For numeric values, leading zeros before the decimal point and trailing zeros (0) after the decimal point have no effect on sort order. C) Unless specified otherwise, NULL values are considered to be higher than any non-NULL values. As a result, the ordering for NULLS depends on the sort order: 1. If the sort order is ASC, NULLS are returned last; to force NULLS to be first, use NULLS FIRST. 2. If the sort order is DESC, NULLS are returned first; to force NULLS to be last, use NULLS LAST.

A) All data is sorted according to the numeric byte value of each character in the ASCII table. UTF-8 encoding is supported B) For numeric values, leading zeros before the decimal point and trailing zeros (0) after the decimal point have no effect on sort order. C) Unless specified otherwise, NULL values are considered to be higher than any non-NULL values. As a result, the ordering for NULLS depends on the sort order: 1. If the sort order is ASC, NULLS are returned last; to force NULLS to be first, use NULLS FIRST. 2. If the sort order is DESC, NULLS are returned first; to force NULLS to be last, use NULLS LAST.

You have a schema with a table and a stream. You cloned the schema. What will happen to the stream A) Any unconsumed records in the stream (in the clone) are inaccessible B) Any unconsumed records in the stream (in the clone) will also be accessible in the clone C) Any unconsumed records in the stream (in the clone) will automatically flow into the table

A) Any unconsumed records in the stream (in the clone) are inaccessible

To download files from the stage/location loaded through COPY INTO <LOCATION> command, you will use A) GET B) UNLOAD C) COPY INTO

A) GET

How is the data storage cost computed for snowflake? A) Based on amount of compressed data B) Based on amount of un-compressed data C) Based on daily average of data stored D) Amount stored on first day of month E) Amount stored on last day of month

A) Based on amount of compressed data C) Based on daily average of data stored Note: Usage for data storage is calculated on the daily average amount of data (in bytes) stored in the system for: 1. Files staged for bulk data loading/unloading (can be stored compressed or uncompressed). 2. Database tables, including historical data for Time Travel (always compressed by Snowflake). 3. Fail-safe for database tables (always compressed by Snowflake). 4. Clones of database tables that reference data deleted in the table that owns the clones. Please look at point #3 above. Snowflake has a storage cost for fail-safe. Now by this time you may already know that Transient tables do not have fail-safe. So for use cases where it makes sense to use transient tables, you can choose transient table to save some costs. Note that storage costs are not that much so weigh in on what you are gaining by going with transient table.

Which one of the below requires an user-specified warehouse to execute COPY statements A) Bulk data load B) Snowpipe C) Both

A) Bulk data load

You can specify the below action to perform when an error is encountered while loading data from a file A) CONTINUE B) SKIP_FILE C) SKIP_FILE_<num> D) SKIP_FILE_<num>% E) ABORT_STATEMENT F) ERROR_STATEMENT

A) CONTINUE B) SKIP_FILE C) SKIP_FILE_<num> D) SKIP_FILE_<num>% E) ABORT_STATEMENT

What will you use to query Snowflake data loading history for the last 365 days (1 year)? A) COPY_HISTORY View B) LOAD_HISTORY View from INFORMATION SCHEMA C) INSERT_HISTORY View

A) COPY_HISTORY View Note: COPY_HISTORY View This Account Usage view can be used to query Snowflake data loading history for the last 365 days (1 year). The view displays load activity for both COPY INTO <table> statements and continuous data loading using Snowpipe. The view avoids the 10,000 row limitation of the LOAD_HISTORY View. https://docs.snowflake.com/en/sql-reference/account-usage/copy_history.html#copy-history-view LOAD_HISTORY View from Information schema This Information Schema view enables you to retrieve the history of data loaded into tables using the COPY INTO <table> command within the last 14 days. The view displays one row for each file loaded. https://docs.snowflake.com/en/sql-reference/info-schema/load_history.html#load-history-view Please also read about the LOAD_HISTORY view from ACCOUNT USAGE schema https://docs.snowflake.com/en/sql-reference/account-usage/load_history.html#load-history-view

Which of the below will you consider while choosing a cluster key A) Columns that are most actively used in selective filters B) Columns frequently used in join predicates C) Columns with extremely high cardinality D) Columns with extremely low cardinality

A) Columns that are most actively used in selective filters B) Columns frequently used in join predicates

Which of the below statements are true about snowflake data sharing? A) Consumers can query shared tables in the same query as their own tables B) Producers can share data to other databases such as Postgres and MySQL C) You don't need a Snowflake Virtual Warehouse to query shared data D) Data Sharing is integrated with role-based access controls

A) Consumers can query shared tables in the same query as their own tables D) Data Sharing is integrated with role-based access controls Note: https://docs.snowflake.com/en/user-guide/data-sharing-intro.html#how-does-secure-data-sharing-work

Which role permissions are required to create and manage streams ? Please specify the OBJECT and the PERMISSION. A) DATABASE - USAGE B) SCHEMA - USAGE, CREATE STREAM C) TABLE - SELECT D) TABLE - DELETE

A) DATABASE - USAGE B) SCHEMA - USAGE, CREATE STREAM C) TABLE - SELECT Note: https://docs.snowflake.com/en/user-guide/streams.html#required-access-privileges

Which type of data incur snowflake storage cost? A) Data stored in permanent tables B) Data retained to enable data recovery(fail-safe and time travel) C) Cached results D) Semi structured data stored in the cloud storage(like AWS S3, GCS)

A) Data stored in permanent tables B) Data retained to enable data recovery(fail-safe and time travel) Note: Storage cost is charged for below type of data 1. Files stored in Snowflake locations (i.e. user and table stages or internal named stages) for bulk data loading/unloading. The user who stages a file can choose whether or not to compress the file to reduce storage. 2. Data stored in database tables, including historical data maintained for Time Travel. Snowflake automatically compresses all data stored in tables and uses the compressed file size to calculate the total storage used for an account. 3. Historical data maintained for Fail-safe.

What is the best way to get the latest ODBC connector for use with Snowflake? A) Download it from snowflake web UI B) Search google and download any version of ODBC C) Compile in .NET

A) Download it from snowflake web UI

Column level security in Snowflake allows the application of a masking policy to a column within a table or view. Which two features are related to column level security A) Dynamic data masking B) External tokenization C) Data loss prevention(DLP)

A) Dynamic data masking B) External tokenization

Which of the below are true with respect to snowflake web ui? A) Each worksheet can have different role, warehouse, schema and database B) Each worksheet is a separate session C) You will be able to run a 'PUT' command from worksheet D) You must use the same role and warehouse for all worksheets

A) Each worksheet can have different role, warehouse, schema and database B) Each worksheet is a separate session

By default, result reuse is enabled in snowflake. if you want to disable it what will you do? A) Execute ALTER SESSION SET USE_CACHED_RESULT=FALSE B) Execute ALTER SESSION SET QUERY_CACHE=FALSE C) Execute ALTER SESSION SET QUERY_RESULT_CACHE =FALSE

A) Execute ALTER SESSION SET USE_CACHED_RESULT=FALSE

How will you know if a stream has become stale? A) Execute DESCRIBE STREAM or SHOW STREAMS B) Execute SHOW TABLE STREAMS C) Execute SHOW PIPES

A) Execute DESCRIBE STREAM or SHOW STREAMS

Following transformations are not supported in a COPY command. Select three. A) FLATTEN B) JOIN C) GROUP BY D) NVL

A) FLATTEN B) JOIN C) GROUP BY

A stream stores data in the same shape as the source table with some additional columns. Which are those additional columns? A) METADATA$ACTION B) METADATA$ISUPDATE C) METADATA$ROW_ID D) METADATA$COLUMN_ID

A) METADATA$ACTION B) METADATA$ISUPDATE C) METADATA$ROW_ID

Which are the two metadata columns for staged files A) METADATA$FILENAME B) METADATA$FILEFORMAT C) METADATA$FILE_ROW_NUMBER

A) METADATA$FILENAME C) METADATA$FILE_ROW_NUMBER

Which of the below services are provided by cloud services A) Metadata management B) Authentication C) Infrastructure management D) Query execution

A) Metadata management B) Authentication C) Infrastructure management

Zero-copy cloning in snowflake is powered by which service? A) Metadata store of the service layer B) SSD cache of Virtual warehouse C) Query result cache

A) Metadata store of the service layer

Using COPY INTO <location> command, you can unload data from a table (or query) into the below locations. A) Named internal stage (or table/user stage) B) Named external stage C) External location (Amazon S3, Google Cloud Storage, or Microsoft Azure) D) Local drive

A) Named internal stage (or table/user stage) B) Named external stage C) External location (Amazon S3, Google Cloud Storage, or Microsoft Azure)

You will use this parameter to specify the number of threads to use for downloading the files using GET command A) PARALLEL = <INTEGER> B) DOWNLOAD_PARALLEL=<INTEGER> C) DUMP=<INTEGER>

A) PARALLEL = <INTEGER>

What table types are available in snowflake A) PERMANENT B) TEMPORARY C) TRANSIENT D) EXTERNAL E) INTERNAL

A) PERMANENT B) TEMPORARY C) TRANSIENT D) EXTERNAL

COPY does not purge loaded files from the location. What option will you specify to purge files after successful load of a table from table stage A) PURGE=TRUE B) DELETE=TRUE C) REMOVE=TRUE D) TRUNCATE=TRUE

A) PURGE=TRUE

Following commands cannot be executed from worksheets A) PUT B) GET C) SHOW D) LIST <STAGE>

A) PUT B) GET

What types of view are available in snowflake? A) REGULAR B) SECURE C) MATERIALIZED D) HASH VIEW E) TABLE VIEW

A) REGULAR B) SECURE C) MATERIALIZED Note: REGULAR Views are the default view type. The underlying DDL is accessible to any role who has access to the view SECURE VIEWS are those where the query definition and the details are accessible to authorized users only. Please note that since the underlying DDL is not visible to the optimizer, this view type will be slower than a normal view Materialized View - These are more like tables, results are store like it is in a table. Snowflake can auto refresh a materialized view. Please take extra care while using materialized views, it may increase your cost.

In you organization, you have snowflake Enterprise addition. You notice that consistently, your queries are getting queued on the warehouses and delaying your ETL process. What are the possible solution options that you can think of? A) Resize the warehouse B) Use multi-cluster warehouse C) Set the auto-resize parameter of the warehouse D) Contact snowflake support to increase the number of servers for the warehouse

A) Resize the warehouse B) Use multi-cluster warehouse

Which command will you run to list all users and roles to which a role has been granted. A) SHOW GRANTS OF ROLE <role_name> B) SHOW GRANTS TO ROLE <role_name> C) SHOW GRANTS ON ACCOUNT

A) SHOW GRANTS OF ROLE <role_name>

This command can be used to list streams for the current/specified database or schema, or across your entire account. A) SHOW STREAMS B) DISPLAY STREAMS C) LIST STREAMS

A) SHOW STREAMS

Which feature in snowflake allows you to load data in micro batches? A) SNOWPIPE B) COPY INTO COMMAND C) LOAD COMMAND

A) SNOWPIPE

Which SNOWFLAKE object stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations (Amazon S3, Google Cloud Storage, or Microsoft Azure)? A) STORAGE INTEGRATION B) STORAGE GROUP C) STORAGE CLUSTERS

A) STORAGE INTEGRATION Note: A storage integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations (Amazon S3, Google Cloud Storage, or Microsoft Azure). Cloud provider administrators in your organization grant permissions on the storage locations to the generated entity. This option allows users to avoid supplying credentials when creating stages or when loading or unloading data

Which parameter will you use while creating a STORAGE integration to limit external stages that use the integration to reference one or more storage locations. A) STORAGE_ALLOWED_LOCATIONS B) STORAGE_ALLOWED_SITES C) STORAGE_OBJECTS_ALLOWED_LOCATIONS

A) STORAGE_ALLOWED_LOCATIONS

Which system functions are available in snowflake to view/monitor the clustering metadata for a table A) SYSTEM$CLUSTERING_DEPTH B) SYSTEM$CLUSTERING_INFORMATION (including clustering depth) C) CLUSTERING_DEPTH_VIEW

A) SYSTEM$CLUSTERING_DEPTH B) SYSTEM$CLUSTERING_INFORMATION (including clustering depth)

The current offset for a stream can be determined by querying which function? A) SYSTEM$STREAM_GET_TABLE_TIMESTAMP B) SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE C) SYSTEM$TASK_DEPENDENTS_ENABLE D) SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS

A) SYSTEM$STREAM_GET_TABLE_TIMESTAMP

What are the installment options available for snowflake? A) Snowflake hosted accounts on AWS B) Hybrid on-premise and cloud C) On-premise D) Snowflake hosted accounts on Azure

A) Snowflake hosted accounts on AWS D) Snowflake hosted accounts on Azure

What are the usual data loading steps in snowflake? A) Source -> Snowflake Stage -> Snowflake table B) Source -> Cloud storage -> Snowflake stage -> Snowflake table C) Source -> Snowflake temp table -> Snowflake transient table-> Snowflake permanent table

A) Source -> Snowflake Stage -> Snowflake table

What are the various types of table streams? A) Standard B) Append-only C) Insert-only D) Upsert

A) Standard B) Append-only C) Insert-only Note: Standard A standard (i.e. delta) table stream tracks all DML changes to the source table, including inserts, updates, and deletes (including table truncates). This stream type performs a join on inserted and deleted rows in the change set to provide the row level delta. As a net effect, for example, a row that is inserted and then deleted between two transactional points of time in a table is removed in the delta (i.e. is not returned when the stream is queried). Append-only An append-only table stream tracks row inserts only. Update and delete operations (including table truncates) are not recorded. For example, if 10 rows are inserted into a table and then 5 of those rows are deleted before the offset for an append-only stream is advanced, the stream records 10 rows. An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream for extract, load, transform (ELT) and similar scenarios that depend exclusively on row inserts. For example, the source table can be truncated immediately after the rows in an append-only stream are consumed, and the record deletions do not contribute to the overhead the next time the stream is queried or consumed. Insert-only Supported on external tables only. An insert-only stream tracks row inserts only; they do not record delete operations that remove rows from an inserted set (i.e. no-ops). For example, in-between any two offsets, if File1 is removed from the cloud storage location referenced by the external table, and File2 is added, the stream returns records for the rows in File2 only. Unlike when tracking CDC data for standard tables, Snowflake cannot access the historical records for files in cloud storage.

What are the snowflake editions available as of today? A) Standard Edition,Enterprise Edition,Business Critical Edition,Virtual Private Snowflake B) Standard Edition,Enterprise Edition,ESD,Business Critical Edition,Virtual Private Snowflake C) Standard Edition,Enterprise Edition

A) Standard Edition,Enterprise Edition,Business Critical Edition,Virtual Private Snowflake

You would like to execute a merge statement in snowflake every one hour. If you want to schedule this query using a snowflake object what will you use? A) TASK B) STREAM C) SNOWPIPE

A) TASK

One of your team members accidentally truncated a table in your snowflake database? What feature of snowflake will you use to restore data into the table A) TIME TRAVEL B) FAIL SAFE C) UNDROP TABLE

A) TIME TRAVEL

Which of the following object types are stored within schema A) Tables B) Views C) User defined functions D) File formats E) Sequences F) Roles

A) Tables B) Views C) User defined functions D) File formats E) Sequences

After you have granted a share to a consumer, you added more objects to the share. You have provided the required access by using the GRANT <privilege> ... TO SHARE command. Please select the true statement A) The added objects will be accessible to the consumers immediately B) You will need to recreate the share

A) The added objects will be accessible to the consumers immediately Note: objects in the share flow in, but you cannot change access on the share

You are trying to set a variable by suing the set variable function SET MY_VARIABLE ='XXXX' . You got an error as below Assignment to 'MY_VARIABLE' not done because value exceeds size limit of variables. Its size is 312; the limit is 256 What is the reason for this error? A) The size of string or binary variables is limited to 256 bytes B) This is a temporary error due to insufficient memory in VM instance C) Your warehouse is not big enough to accomodate this

A) The size of string or binary variables is limited to 256 bytes

When the owner role of a given task (i.e. the role with the OWNERSHIP privilege on the task) is deleted, what happens to the task? A) The task is "re-possessed" by the role that dropped the owner role B) The task is deleted immediately C) The task is suspended immediately

A) The task is "re-possessed" by the role that dropped the owner role

Which of the below mentioned clustering metadata for the micro-partitions is maintained by snowflake in a table? A) The total number of micro-partitions that comprise the table B) The number of micro-partitions containing values that overlap with each other C) The depth of the overlapping micro-partitions D) None of the above

A) The total number of micro-partitions that comprise the table B) The number of micro-partitions containing values that overlap with each other C) The depth of the overlapping micro-partitions

From a snowflake perspective what is common about Fivetran, Informatica, Matillion, Segment, Stitch and Talend A) They are all snowflake data integration partner B) They are all snowflake competitors C) They are all programming interface patterns

A) They are all snowflake data integration partner

You have a multi-threaded client program that interacts with snowflake. If you are following snowflake recommendations what will you do A) Use a separate connection for each thread B) Execute the threads synchronously C) Execute the threads asynchronously D) Use one connection for all the threads

A) Use a separate connection for each thread B) Execute the threads synchronously Note: Although multiple sessions cannot share the same transaction, multiple threads using a single connection share the same session, and thus share the same transaction. This can lead to unexpected results, such as one thread rolling back work done in another thread. This situation can occur when a client application using a Snowflake driver (such as the Snowflake JDBC Driver) or connector (such as the Snowflake Connector for Python) is multi-threaded. If two or more threads share the same connection, then those threads also share the current transaction in that connection. A BEGIN, COMMIT, or ROLLBACK by one thread affects all threads using that shared connection. If the threads are running asynchronously, the results can be unpredictable. Similarly, changing the AUTOCOMMIT setting in one thread affects the AUTOCOMMIT setting in all other threads that use the same connection. Note that changing the state of the AUTOCOMMIT setting commits and existing transaction. Snowflake recommends that multi-threaded client programs do at least one of the following: Use a separate connection for each thread. Note that even with separate connections, your code can still hit race conditions that generate unpredicable output; for example, one thread might delete data before another thread tries to update it. Execute the threads synchronously rather than asynchronously, to control the order in which steps are performed. https://docs.snowflake.com/en/sql-reference/transactions.html#transactions-and-multi-threading

Which of the below semi structured data types are supported by snowflake? A) VARIANT B) OBJECT C) ARRAY D) GEOGRAPHY

A) VARIANT B) OBJECT C) ARRAY Note: Snowflake supports three types of semi structured data types VARIANT - A tagged universal type, which can store values of any other type, including OBJECT and ARRAY, up to a maximum size of 16 MB compressed. OBJECT - Used to represent collections of key-value pairs, where the key is a non-empty string, and the value is a value of VARIANT type. Snowflake does not currently support explicitly-typed objects. ARRAY - Used to represent dense or sparse arrays of arbitrary size, where index is a non-negative integer (up to 2^31-1), and values have VARIANT type. Snowflake does not currently support fixed-size arrays or arrays of elements of a specific non-VARIANT type.

If a query is running slow, to check if this is for an overloaded warehouse where in the Snowflake UI you will go to check the same A) Warehouse Tab B) Database Tab C) Query Tab

A) Warehouse Tab

What are the different caching mechanisms that are available in snowflake? A) Warehouse caching B) Index Caching C) Metadata caching D) Query result caching

A) Warehouse caching C) Metadata caching D) Query result caching Note: https://blog.ippon.tech/innovative-snowflake-features-caching/#:~:text=Metadata%20Cache,-Fully%20Managed%20in&text=Snowflake%20automatically%20collects%20and%20manages,Table%20Size%20in%20Bytes

You ran a query in snowflake and it took 2 minute 3 seconds to run, you ran the query again and it returned the results in less than a second. What might have happened? A) When the query ran for the second time, it used the persisted query results from the query result cache B) The query used the meta data cache C) All the partitions were scanned from the SSD cache

A) When the query ran for the second time, it used the persisted query results from the query result cache

Snowpipe is recommended to load data in which of the below scenario? A) You have small volume of frequent data B) You have a huge volume of data generated as part of a batch schedule

A) You have small volume of frequent data

Which of the below are SNOWPIPE REST APIs. Select three. A) insertFiles B) insertReport C) loadHistoryScan D) loadHistoryProgress

A) insertFiles B) insertReport C) loadHistoryScan

When calling the rest endpoints in snowpipe, below authentication methods are supported A) key pair authentication with JSON Web Token B) user id and password C) Both

A) key pair authentication with JSON Web Token

Which command will you run to view a list of files that have been unloaded to a stage named mystage. A) list @mystage; B) show @mystage; C) display @mystage;

A) list @mystage;

Which permissions are required by Snowflake in AWS S3 to unload data to S3? A) s3:DeleteObject B) s3:PutObject C) s3:ListObject

A) s3:DeleteObject B) s3:PutObject

You have stored the below JSON in a table named car_sales as a variant column { "customer": [ { "address": "San Francisco, CA", "name": "Joyce Ridgely", "phone": "16504378889" } ], "date": "2017-04-28", "dealership": "Valley View Auto Sales", "salesperson": { "id": "55", "name": "Frank Beasley" }, "vehicle": [ { "extras": [ "ext warranty", "paint protection" ], "make": "Honda", "model": "Civic", "price": "20275", "year": "2017" } ] } How will you query the table to get the dealership data? A) select src:dealership from car_sales; B) select src:$dealership from car_sales; C) select dealership from car_sales;

A) select src:dealership from car_sales;

Which GCP regions are supported by Snowflake in US and Canada A) us-central1 B) us-central2 C) us-east1

A) us-central1

Which AWS regions are supported by snowflake in US and Canada A) us-west-2 B) us-east-2 C) us-east-1 D) ca-central-1 E) us-central-2

A) us-west-2 B) us-east-2 C) us-east-1 D) ca-central-1 Note: https://docs.snowflake.com/en/user-guide/intro-regions.html#government-regions

If file format options are specified in multiple locations, the load operation applies the options in the following order of precedence. A) 1. COPY INTO TABLE statement. 2. Stage definition. 3. Table definition. B) 1. Stage definition. 2. COPY INTO TABLE statement. 3. Table definition. C) 1. COPY INTO TABLE statement. 2. Table definition. 3. Stage definition.

A) 1. COPY INTO TABLE statement. 2. Stage definition. 3. Table definition.

Snowflake supports which of the following file formats for data loading? A. Parquet B. ORC C. CSV D. PDF

A, B, & C

The Snowflake metadata repository contains which of the following? A. Table definitions B. References to all of the micro partition files for that table C. Tracking of all versions of the table data within the data retention window D. Aggregated data from the table

A, B, & C

Which of the following are true of multi-cluster Warehouses? Select all that apply below. A. A multi-cluster Warehouse can add clusters automatically based on query activity B. A multi-cluster Warehouse can automatically turn itself off after a period of inactivity C. A multi-cluster Warehouse can scale down when query activity slows D. A multi-cluster Warehouse can automatically turn itself on when a query is executed against it

A, B, C, D

Which of the following are types of caching used by SF? A. Warehouse Caching B. Index Caching C. Metadata Caching D. Query result Caching

A, C, & D

On which of the following cloud platform can a Snowflake account be hosted? Choose 2 answers A. Amazon Web Services B. Private Virtual Cloud C. Oracle Cloud D. Microsoft Azure Cloud

A. Amazon Web Services & D. Microsoft Azure Cloud

What command is used to load files into an Internal Stage within Snowflake? A. PUT B. COPY INTO C. TRANSFER D. INSERT

A. PUT

True or False: Snowpipe via RFST API can only reference External Stages as source.

A. True

Select the statements which are true for an external table External tables are read-only External tables can be used for query and join operations Views can be created against external tables Data can be updated in external tables

External tables are read-only External tables can be used for query and join operations Views can be created against external tables

The PIPE_EXECUTION_PAUSED parameter can be set at which levels DATABASE ACCOUNT SCHEMA PIPE

ACCOUNT SCHEMA PIPE Note: Complete the following steps to transfer ownership of a pipe: Set the PIPE_EXECUTION_PAUSED parameter to TRUE. This parameter enables pausing or resuming a pipe. The parameter is supported at the following levels: Account Schema Pipe At the pipe level, the object owner (or a parent role in a role hierarchy) can set the parameter to pause or resume an individual pipe. An account administrator (user with the ACCOUNTADMIN role) can set this parameter at the account level to pause or resume all pipes in the account. Likewise, a user with the MODIFY privilege on the schema can pause or resume pipes at the schema level. Note that this larger domain control only affects pipes for which the parameter was not already set at a lower level; e.g., by the owner at the object level. https://docs.snowflake.com/en/user-guide/data-load-snowpipe-manage.html#transferring-pipe-ownership

Which roles create, alter, or drop network policies? ACCOUNTADMIN SECURITYADMIN SYSADMIN

ACCOUNTADMIN SECURITYADMIN

Who can setup a data share SECURITYADMIN SYSADMIN ACCOUNTADMIN or a role granted the CREATE SHARES global privilege

ACCOUNTADMIN or a role granted the CREATE SHARES global privilege Note: https://docs.snowflake.com/en/user-guide/data-sharing-provider.html

What is the encryption algorithm used by snowflake? AES 256-bit encryption DES BLOWFISH

AES 256-bit encryption

When working with a cloned table, you can use the below SQL statements SELECT DROP SHOW ALL OF THE ABOVE

ALL OF THE ABOVE

You have created a TASK in snowflake. How will you resume it? No need to resume, the creation operation automatically enables the task ALTER TASK mytask1 RESUME; ALTER TASK mytask1 START;

ALTER TASK mytask1 RESUME; Note: After creating a task, you must execute ALTER TASK ... RESUME before the task will run based on the parameters specified in the task definition.

You want to scale out query performance. Which strategy will you follow with respect to your warehouses? Resize the warehouse Add clusters to the warehouse Create another warehouse

Add clusters to the warehouse Note: Scale out = add clusters scale up = make WH larger

Your business team runs a set of identical queries every day after the batch ETL run is complete. From the following actions, what is the best action that you will recommend. After the ETL run, execute the identical queries so that they remain in the result cache After the ETL run, resize the warehouse to a larger warehouse After the ETL run, copy the tables to another schema for the business users to query

After the ETL run, execute the identical queries so that they remain in the result cache Note: Please note the key word here which is IDENTICAL queries. When a query is run the first time, and the same query is run the second time it picks up the results from the query result cache and it does not cost you any compute. The query result cache is valid for 24 hours and it gets extended for another 24 hours, every time you access it(even if you access it at 23 hours 59 seconds).

Having separate accounts in snowflake enables users to have Different editions of snowflake and different regions Billing can be done at account level Database object deployment between environments become simpler All of the above

All of the above

Which approach would result in improved performance through linear scaling of data ingestion workload? Resize virtual warehouse Consider practice of organizing data by granular path Consider practice of splitting input file batch within the recommended size of 10 MB to 100 MB All of the above

All of the above

Which best describes zero-copy cloning? Metadata only operation No replication of data Unmodified data stored once and modified data stored as new micro partitions All of the above

All of the above Note: When a clone is created of a table, the clone utilizes no data storage because it shares all the existing micro-partitions of the original table at the time it was cloned; however, rows can then be added, deleted, or updated in the clone independently from the original table. Each change to the clone results in new micro-partitions that are owned exclusively by the clone and are protected through CDP(continuous data protection).

OVERWRITE option for PUT is supported in which cloud providers Amazon AWS Microsoft Azure Google cloud platform

Amazon AWS Microsoft Azure

A Stream has been created on a table. A row in the table got inserted and subsequently got updated. The stream will capture both the events. (TRUE OR FALSE)

FALSE

Select the two true statements about multi cluster warehouse These type of warehouses cannot be resumed or suspended Multi cluster warehouses can be created on snowflake hosted on AWS only As query demand decreases, clusters are removed from this warehouse When query workload increases, clusters are automatically added

As query demand decreases, clusters are removed from this warehouse When query workload increases, clusters are automatically added Note: In Auto-scale mode, a multi-cluster warehouse eliminates the need for resizing the warehouse or starting and stopping additional warehouses to handle fluctuating workloads. Snowflake automatically starts and stops additional clusters as needed. In Maximized mode, you can control the capacity of the warehouse by increasing or decreasing the number of clusters as needed.

Snowflake on GCP is not available in which region currently (as of Year 2020) Europe Asia Pacific North America

Asia Pacific Refer link for details of snowflake availablity in differenent regions - https://docs.snowflake.com/en/user-guide/intro-regions.html

Cloud services layer is one of the layers in snowflake. Which services are provided by this layer Authentication Infrastructure management Metadata management Query parsing and optimization Access control Query processing

Authentication Infrastructure management Metadata management Query parsing and optimization Access control

Which of the following are options when creating a virtual warehouse Auto Suspend Auto Resume Storage Size Server count

Auto Suspend Auto Resume

Which two modes are available in Multi cluster warehouse Auto-scale Peak Scale Flexible Maximized

Auto-scale Maximized

What are the two mechanisms to detect if new stage file is there in a snowpipe? Automating Snowpipe using cloud messaging Calling Snowpipe REST endpoints Calling the custom APIs exposed through AWS EKS

Automating Snowpipe using cloud messaging Calling Snowpipe REST endpoints

What is the maximum number of consumer accounts that can be added to a Share object? A. One B. Unlimited C. 10 D. 100

What is the recommended approach for making a variant column accessible in a BI tool? A. A pre-defined mapping B. A View C. Leveraging a JSON parser D. There is no way to do this

What technique does Snowflake use to limit the number of micro-partitions scanned by each query? A. Indexing B. Pruning C. Map Reduce D. B-True

Which of the following techniques can be in SF to improve query performance? A. Index Definition B. Cluster Keys/Reclustering C. Distribution Keys D. Query Hints

Which type of data integration tools leverage SF's scalable compute for data transformation? A. Data Replication B. ELT C. ETL D. Streaming

Load history of Snowpipe expires after how many days. A) 30 B) 14 C) 64

B) 14

You have executed a task. Due to some coding issue, the task went into a loop. How long the task will run before it is terminated by Snowflake A) 24 hours B) 60 minutes C) 30 minutes

B) 60 minutes

One of your user complained that a query is taking a long time to return any result. What techniques would you consider to improve performance? A) Create an index on the search columns B) Create cluster keys and turn on auto clustering on the table C) Enable query hints on the table D) Define the partition keys for the table

B) Create cluster keys and turn on auto clustering on the table

Tick the statements which are true for fail safe A) Fail safe period is configurable B) Data can be recovered from a fail-safe storage only by snowflake employee C) Using fail-safe you can create dev/test environments D) Tables with fail-safe turned on incurs additional storage costs compared to tables where fail-safe is not turned on

B) Data can be recovered from a fail-safe storage only by snowflake employee D) Tables with fail-safe turned on incurs additional storage costs compared to tables where fail-safe is not turned on

The three key layers of snowflake are A) Extraction, Ingestion, Load B) Database storage, Query Processing, Cloud Services C) Database, Virtual Warehouse, Data Experience

B) Database storage, Query Processing, Cloud Services Note: https://docs.snowflake.com/en/user-guide/intro-key-concepts.html

You are an account administrator and you want to use a third party product in snowflake. Where will you go to enable the third party product? A) The third party product's webpage and contact them through contact us B) Call snowflake support to enable the product C) Enable the product through Partner Connect in Snowflake web console

C) Enable the product through Partner Connect in Snowflake web console

A snowflake account hosted on AWS cannot load data from a file that is staged in GCP or Azure FALSE TRUE

FALSE

Select two true statements about fail safe from the following A) Fail safe is same as time travel B) Fail-safe provides a (non-configurable) 7-day period during which historical data is recoverable by Snowflake C) Fail-safe provides a (non-configurable) 90-day period during which historical data is recoverable by Snowflake D) Fail-safe ensures historical data is protected in the event of a system failure or other catastrophic event, e.g. a hardware failure or security breach

B) Fail-safe provides a (non-configurable) 7-day period during which historical data is recoverable by Snowflake D) Fail-safe ensures historical data is protected in the event of a system failure or other catastrophic event, e.g. a hardware failure or security breach

Which of the below is true about snowflake architecture A) One node shared data B) Multi Cluster Shared data C) Multi Node distributed data

B) Multi Cluster Shared data

What is the default action to perform when an error is encountered while loading data from a file using SNOWPIPE? A) ABORT_STATEMENT B) SKIP_FILE C) CONTINUE

B) SKIP_FILE

Select two choices that are true about snowflake roles A) Snowflake users has a limit on the number of roles that they can assume B) Snowflake user can have one or more roles C) Privileges can be directly assigned to a user D) For a particular session, only one role can be active at a given time

B) Snowflake user can have one or more roles D) For a particular session, only one role can be active at a given time

Which object in snowflake records data manipulation language changes made to tables, including inserts, updates, and deletes, as well as metadata change about each change? A) Tasks B) Table stream C) Snowpipe D) Change Data Capture

B) Table stream Note: A stream object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change, so that actions can be taken using the changed data. This process is referred to as change data capture (CDC). An individual table stream tracks the changes made to rows in a source table. A table stream (also referred to as simply a "stream") makes a "change table" available of what changed, at the row level, between two transactional points of time in a table. This allows querying and consuming a sequence of change records in a transactional fashion.

When you remove the predecessor of a child task, what may happen to the child task. Select two possibilities? A) The child task is removed from the system B) The child task may become the root task C) The child task may become a stand-alone task

B) The child task may become the root task C) The child task may become a stand-alone task

How often does snowflake release new feature A) Daily B) Weekly C) Monthly D) Annually

B) Weekly

What are the three things customers want most from their enterprise data warehouse solution? Choose 3 answers A. On-premise availability B. Simplicity C. Open source based D. Concurrency E. Performance

B, D, E

What are the two data loading approaches in snowflake BULK LOADING CONTINUOUS LOADING INGEST LOADING

BULK LOADING (COPY command) CONTINUOUS LOADING (Snowpipe)

How would you execute a series of SQL statements using Task? A. include the SQL statements in the body of the task create task mytask.. as insert into target1 select., from stream_s1 where ..INSERT INTO target2 SELECT .. FROM stream .s1 where .. B. A stored procedure can have only one DML statement per stored procedure invocation and therefore you should sequence stored procedures calls in the task definition CREATE TASK mytask.... AS call stored_prc1(); call stored_proc2t); C. A stored procedure executing multiple SQL statements and invoke the stored procedure from the task. CREATE TASK mytask.... AS callstored_proc_multiple_statements_inside(): D. Create a task for each SQL statement (e.g. resulting in task1. task2, etc) and string the series of SQL statements by having a control taskcalling task 1, task 2 sequentially.

Which of the following terms best describes Snowflake's database architecture? A. Columnar shared nothing B. Shared disk C. Multi-cluster shared data D. Cloud native shared memory

Lets say you executed a transaction in a snowflake session. Due to some reason the session disconnects and the transaction now is in a detached state. The transaction cannot be committed or rolled back. The object on which the transaction was applied is also now locked. if you do not do anything and let snowflake eventually abort the transaction, how long will you need to wait? A) 60 minutes B) 15 minutes C) 4 hours

C) 4 hours

Your customer needs to setup Failover/Failback mechanism in their snowflake instance. Which edition of Snowflake will you suggest? A) STANDARD B) PREMIER C) BUSINESS CRITICAL(OR HIGHER)

C) BUSINESS CRITICAL(OR HIGHER) Note: https://docs.snowflake.com/en/user-guide/database-replication-failover.html

Your customer deals with extremely sensitive data like PHI data. They need to be compliant with HIPAA. What is the minimum edition that you will recommend A) STANDARD B) PREMIUM C) Business Critical Edition D) Virtual Private Snowflake

C) Business Critical Edition

What is the easiest way to monitor the queries that are run on snowflake? A) Create a tableau dashboard and connect to snowflake B) All queries go to cloudwatch and use cloudwatch to monitor C) Click on the the History tab and monitor all queries that are executed in the last 14 days

C) Click on the the History tab and monitor all queries that are executed in the last 14 days

Pick the true statement for snowflake architecture A) Shared nothing architecture B) Shared disk architecture C) Multi-Cluster Shared Data architecture

C) Multi-Cluster Shared Data architecture Note: nowflake's architecture is a hybrid of traditional shared-disk database architectures and shared-nothing database architectures. Similar to shared-disk architectures, Snowflake uses a central data repository for persisted data that is accessible from all compute nodes in the data warehouse. But similar to shared-nothing architectures, Snowflake processes queries using MPP (massively parallel processing) compute clusters where each node in the cluster stores a portion of the entire data set locally. This approach offers the data management simplicity of a shared-disk architecture, but with the performance and scale-out benefits of a shared-nothing architecture. https://docs.snowflake.com/en/user-guide/intro-key-concepts.html

What happens when you execute the below query CREATE OR REPLACE TABLE MyTable_V2 CLONE MyTable A) MyTable_V2 is created and all the data from MyTable is copied into MyTable_V2 B) MyTable_V2 is created and a batch job is submitted to copy data from MyTable C) Snowflake creates a new entry in the metadata store to keep track of the new clone. The existing micro partitions of table MyTable are also mapped to the new table. This is called zero copy clone.

C) Snowflake creates a new entry in the metadata store to keep track of the new clone. The existing micro partitions of table MyTable are also mapped to the new table. This is called zero copy clone.

What influences snowflake pricing? A) Amount of data queried from snowflake B) Amount of data scanned during querying snowflake C) Snowflake pricing is based on usage and it charges only for storage and compute

C) Snowflake pricing is based on usage and it charges only for storage and compute

Query processing in snowflake is done by... A) Snowflake processes queries using AWS EMR running spark B) Snowflake processes queries running spark on a EC2 instance C) Snowflake process queries using 'Virtual Warehouses'

C) Snowflake process queries using 'Virtual Warehouses'

How is data loaded into snowflake? A) Snowflake loads the data in parquet format on the underlying cloud storage B) Snowflake loads the data in JSON format on the underlying cloud storage C) Snowflake reorganizes the data into its internal optimized, compressed, columnar format at stores on the underlying cloud storage

C) Snowflake reorganizes the data into its internal optimized, compressed, columnar format at stores on the underlying cloud storage

If you want customer dedicated virtual warehouse, which is the lowest snowflake edition that you should opt for A) Business Critical B) Enterprise C) Standard

C) Standard

You are developing an ETL query. one of your ETL logic requires you to store the intermediate data temporarily. This data will be used only by your ETL. The data will not be required outside the ETL. If you want to optimize storage cost, what type of table will you create to store this data A) PERMANENT B) TRANSIENT C) TEMPORARY D) EXTERNAL

C) TEMPORARY

You have created a lot of shares with role as ACCOUNTADMIN. You want to create the shares with SYSADMIN as role, so you have granted 'CREATE SHARE' privilege to SYSADMIN. How do you change the ownership of the existing shares? A) Execute 'GRANT OWNERSHIP ON <SHARE NAME> TO ROLE SYSADMIN' B) Execute 'GRANT OWNERSHIP TO ROLE SYSADMIN ON <SHARE NAME> C) The only option is to drop and recreate the share. Ownership of a share cannot be granted to another role

C) The only option is to drop and recreate the share. Ownership of a share cannot be granted to another role

When a warehouse does not have enough resources available to process the queries, what happens to the incoming queries? A) Queries are aborted immediately B) Snowflake automatically resizes the warehouse C) The queries are queued and then executed when the resources are available again

C) The queries are queued and then executed when the resources are available again

Compute cost in snowflake depends on A) The actual query execution time B) The query execution time and the time the query waits for the resource C) The warehouse size and how long the warehouse runs

C) The warehouse size and how long the warehouse runs

How do you truncate a date (from a timestamp) down to the year, month, and day A) Use concatenation of the date part B) Split the date into year, month and day C) Use date_trunc and execute a query as below select to_date('2015-05-08T23:39:20.123-07:00') as "DATE1", date_trunc('YEAR', "DATE1") as "TRUNCATED TO YEAR", date_trunc('MONTH', "DATE1") as "TRUNCATED TO MONTH", date_trunc('DAY', "DATE1") as "TRUNCATED TO DAY";

C) Use date_trunc and execute a query as below select to_date('2015-05-08T23:39:20.123-07:00') as "DATE1", date_trunc('YEAR', "DATE1") as "TRUNCATED TO YEAR", date_trunc('MONTH', "DATE1") as "TRUNCATED TO MONTH", date_trunc('DAY', "DATE1") as "TRUNCATED TO DAY";

Your customer required the highest level of data protection and needs a completely isolated environment for snowflake. Which edition will you recommend A) STANDARD B) PREMIER C) Virtual Private Snowflake D) Business Critical Edition

C) Virtual Private Snowflake

Compute in snowflake is provided by A) The cloud providers VM instances B) Only EC2 instances on AWS C) Virtual warehouses

C) Virtual warehouses

In your snowflake environment, you have a medium warehouse. The medium warehouse is used by business to run adhoc queries. The warehouse has auto suspend set at 15 minutes. You have noticed that all of the queries that run on this warehouse finishes within a minute. What will you do to optimize cost of compute in this case? A) Since all the queries are completing in a minute, you should delete this warehouse and tell the users to use another existing warehouse B) You will tell the users that they do not need snowflake to run their queries, they should load their data in another on premise database and run query from there C) You will recommend to reduce the auto suspend time to 1 minute

C) You will recommend to reduce the auto suspend time to 1 minute

A successful response from the SNOWPIPE insertFiles API means that the files are ingested. (TRUE OR FALSE)

FALSE

A table in snowflake can only be queried using the virtual warehouse used to load the data TRUE FALSE

FALSE

You are a snowflake architect hired by an organization. They want you to design their warehouse strategy. Which one of the strategy will you pick up. A) Both loading and analysis of data will be done by a single warehouse to reduce cost B) You do not need to use warehouse for loading data, only query analysis will require a warehouse C) You will recommend to use a multi-warehouse strategy. The load workload will be done by one ware house and the query analysis workload will be done by another warehouse

C) You will recommend to use a multi-warehouse strategy. The load workload will be done by one ware house and the query analysis workload will be done by another warehouse

When loading data into Snowflake, the COPY command supports: Choose 2 answers A. Joins B. Fitters C. Data type conversions D. Column reordering E. Aggregates

C. Data Type Conversion & D. Column Reordering

The commands to load data into snowflake are COPY AND PUT COPY AND INSERT INSERT AND PUT NONE OF THE ABOVE

COPY AND PUT COPY AND INSERT INSERT AND PUT

Which of the below function returns the name of the warehouse of the current session? ACTIVE_WAREHOUSE() SESSION_WAREHOUSE() CURRENT_WAREHOUSE() WAREHOUSE()

CURRENT_WAREHOUSE()

What makes a Partner Connect Partner different from other partners? Can be connected to Snowflake using a streamlined wizard Requires Enterprise Edition Can be connected from within the WebUI Includes automated role, user and staging database set up Includes a streamlined Partner Trial Account Signup

Can be connected to Snowflake using a streamlined wizard Can be connected from within the WebUI Includes automated role, user and staging database set up Includes a streamlined Partner Trial Account Signup Explanation Refer Link for details - https://docs.snowflake.com/en/user-guide/ecosystem-partner-connect.html

In snowflake data storage cost depends on which factors Cloud service provider Region Type of account we chose (on-demand/capacity) Edition

Cloud service provider Region Type of account we chose (on-demand/capacity)

To improve the query performance, which of the below techniques can be used in snowflake Indexes Distribution keys Query hints Cluster keys/Reclustering

Cluster keys/Reclustering

Why would you not recommend a high cardinality column for including in the clustering key? (Select 2) Extremely high cardinality column results in skewed distribution across micro-partitions and hence will impact query pruning High cardinality will lead to low clustering depth, so as you add more data query performance will degrade Cluster maintenance with high cardinality keys is expensive Point lookup queries will only benefit, whereas range queries will not be effectively utilize the clustering key

Cluster maintenance with high cardinality keys is expensive Point lookup queries will only benefit, whereas range queries will not be effectively utilize the clustering key

Which transformations are available when using the COPY INTO command to load data files into Snowflake from a stage? Filters Aggregates Column data type conversion Column concatention

Column data type conversion Column concatention

Select the options that differentiates a Partner Connect partner from a regular partner Connect with snowflake through a wizard Includes a partner trial account signup Can be connected from the WEB UI Includes automated role, user and staging database setup None of the above

Connect with snowflake through a wizard Includes a partner trial account signup Can be connected from the WEB UI Includes automated role, user and staging database setup Note: https://docs.snowflake.com/en/user-guide/ecosystem-partner-connect.html#snowflake-partner-connect

Which statements are true about a micro partition in snowflake Contiguous unit of storage Each micro partition contains between 50 MB and 500 MB of uncompressed data Organized in a columnar fashion Micro partitions can be updated using snowflake query

Contiguous unit of storage Each micro partition contains between 50 MB and 500 MB of uncompressed data Organized in a columnar fashion Note: Micro partitions are immutable which means once created they cannot be changed. If a row is updated in snowflake, the micro partition holding the row is copied into a new micro partition and the updated row is inserted in that micro partition. The olde micro partition is marked for deletion All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage. Each micro-partition contains between 50 MB and 500 MB of uncompressed data (note that the actual size in Snowflake is smaller because data is always stored compressed). Groups of rows in tables are mapped into individual micro-partitions, organized in a columnar fashion. This size and structure allows for extremely granular pruning of very large tables, which can be comprised of millions, or even hundreds of millions, of micro-partitions. Snowflake stores metadata about all rows stored in a micro-partition, including: The range of values for each of the columns in the micro-partition. The number of distinct values. Additional properties used for both optimization and efficient query processing. https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html

What are the three features provided by Snowflake for continuous data pipeline Continuous data loading Change data tracking Recurring tasks Cloud functions Table Pipes

Continuous data loading Change data tracking Recurring tasks

What is the best practice for handling semi-structured data with 3rd party BI tools?

Create a Snowflake view that parse the semi-structured column into structure columns for the BI tool to retrieve

You want to convert an existing permanent table to a transient table (or vice versa) while preserving data and other characteristics such as column defaults and granted privileges. What is the best way to do it? Run an ALTER TABLE command to convert the tables Unload the data from the existing table into a CSV file. Create the new table and then load the data back in Create a new table and use the COPY GRANTS clause

Create a new table and use the COPY GRANTS clause

Your business team wants to load JSON data into a table in snowflake. What will you recommend them? Create a table with a variant column and load the JSON data in that column Tell the team to convert JSON to CSV Construct a python program to convert the JSON into CSV and then load the data.

Create a table with a variant column and load the JSON data in that column

Which of the below features are only available to Business Critical edition and above? Customer-managed encryption keys through Tri-Secret Secure. Support for secure, direct proxy to your other virtual networks or on-premises data centers using AWS PrivateLink or Azure Private Link. Support for PHI data (in accordance with HIPAA regulations). Support for PCI DSS. Extended Time Travel (up to 90 days).

Customer-managed encryption keys through Tri-Secret Secure. Support for secure, direct proxy to your other virtual networks or on-premises data centers using AWS PrivateLink or Azure Private Link. Support for PHI data (in accordance with HIPAA regulations). Support for PCI DSS.

You have cloned a table. Which of the below queries will work on the cloned table? A) DROP TABLE <TABLE_NAME> B) SELECT * FROM <TABLE_NAME> C) SHOW TABLES LIKE '<TABLE_NAME>' D) ALL OF THE ABOVE

D) ALL OF THE ABOVE

When the snowflake staged release happens, which accounts get the updates first A) Enterprise or above B) Trial accounts C) Premier accounts D) Accounts who has opted for early access

D) Accounts who has opted for early access

Which snowflake edition supports private communication between Snowflake and your other VPCs through AWS PrivateLink A) Standard B) Premier C) Enterprise D) Business critical

D) Business critical

Which of the following best describes snowflakes processing engine? A) Leverages Apache Spark B) Leverages Map Reduce C) A derivative of presto D) Native SQL

D) Native SQL

For federated authentication and MFA, what is the minimum snowflake edition required? A) Business critical B) Enterprise C) Premier D) Standard

D) Standard

What are the product offerings for secure data sharing? DIRECT SHARE DATA MARKETPLACE DATA EXCHANGE DATA STORE

DIRECT SHARE DATA MARKETPLACE DATA EXCHANGE Note: https://docs.snowflake.com/en/user-guide/data-sharing-product-offerings.html#direct-share

Which of the following is not a layer in SF's Architecture? Storage Cloud Services Data Catalog Virtual Warehouses

Data Catalog

What are the available partner categories in snowflake? Data integration Business Intelligence Security and governance ML and data science Data Warehousing

Data integration Business Intelligence Security and governance ML and data science Note: https://docs.snowflake.com/en/user-guide/ecosystem.html

There is no query performance difference between a column with a maximum length declaration (e.g. VARCHAR(16777216)), and a smaller precision. Still it is recommended to define an appropriate column length because of the below reasons Data loading operations are more likely to detect issues such as columns loaded out of order, e.g. a 50-character string loaded erroneously into a VARCHAR(10) column. Such issues produce errors When the column length is unspecified, some third-party tools may anticipate consuming the maximum size value, which can translate into increased client-side memory usage or unusual behavior Data unloading will be performant if appropriate column lengths are defined

Data loading operations are more likely to detect issues such as columns loaded out of order, e.g. a 50-character string loaded erroneously into a VARCHAR(10) column. Such issues produce errors When the column length is unspecified, some third-party tools may anticipate consuming the maximum size value, which can translate into increased client-side memory usage or unusual behavior

Which of the following conditions are required for sharing data in Snowflake? Note - Here Data Sharing being referred is not via Data Replication. Data providers with ACCOUNTADMIN role can set up shares Consumer accounts must be in same Snowflake region as the provider account Secure views are not required when query performance is priority Each data share must contain a single database

Data providers with ACCOUNTADMIN role can set up shares Consumer accounts must be in same Snowflake region as the provider account Details for setting up a share can be referred at following link - https://docs.snowflake.com/en/user-guide/data-share-providers.html

What is the best way to store semi-structured data?

Data should be loaded and stored in a VARIANT type column

Which layer in snowflake contains data in compressed, columnar format Cloud services Query processing Database storage None

Database storage

Which feature is only available in VPS edition? Dedicated metadata store and pool of virtual servers (used in virtual warehouses). SOC 2 Type II certification. Automatic encryption of all data.

Dedicated metadata store and pool of virtual servers (used in virtual warehouses). Note: https://docs.snowflake.com/en/user-guide/intro-editions.html#business-critical-edition

Each object has an owner, who can in turn grant access to that object. What is this model called? Discretionary Access Control (DAC) Role-based Access Control (RBAC) Object ownership model

Discretionary Access Control (DAC) Note: Snowflake's approach to access control combines aspects from both of the following models: 1. Discretionary Access Control (DAC): Each object has an owner, who can in turn grant access to that object. 2. Role-based Access Control (RBAC): Access privileges are assigned to roles, which are in turn assigned to users. https://docs.snowflake.com/en/user-guide/security-access-control-overview.html#access-control-framework

To avoid unexpected task executions due to daylight saving time, you will do one of the below activities. Do not schedule tasks to run at a specific time between 1 AM and 3 AM (daily, or on days of the week that include Sundays) Manually adjust the cron expression for tasks scheduled during those hours twice each year to compensate for the time change due to daylight saving time Add the USE DAYLIGHT SAVINGS parameter for task

Do not schedule tasks to run at a specific time between 1 AM and 3 AM (daily, or on days of the week that include Sundays) Manually adjust the cron expression for tasks scheduled during those hours twice each year to compensate for the time change due to daylight saving time Note: The cron expression in a task definition supports specifying a time zone. A scheduled task runs according to the specified cron expression in the local time for a given time zone. Special care should be taken with regard to scheduling tasks for time zones that recognize daylight saving time. Tasks scheduled during specific times on days when the transition from standard time to daylight saving time (or the reverse) occurs can have unexpected behaviors. For example: During the autumn change from daylight saving time to standard time, a task scheduled to start at 1 AM in the America/Los_Angeles time zone (i.e. 0 1 * * * America/Los_Angeles) would run twice: once at 1 AM and then again when 1:59:59 AM shifts to 1:00:00 AM local time. That is, there are two points in time when the local time is 1 AM. During the spring change from standard time to daylight saving time, a task scheduled to start at 2 AM in the America/Los_Angeles time zone (i.e. 0 2 * * * America/Los_Angeles) would not run at all because the local time shifts from 1:59:59 AM to 3:00:00 AM. That is, there is no point during that day when the local time is 2 AM.

To have multi cluster data virtual warehouse, what should be the minimum snowflake subscription ENTERPRISE BUSINESS CRITICAL STANDARD

ENTERPRISE

Your organization has a need for Column-level security by applying masking policies to columns in tables or views. Which editions will be suitable for this requirement? Standard Enterprise Business Critical VPS

Enterprise Business Critical VPS

Extended Time Travel (up to 90 days) is available in which editions Standard Enterprise Business Critical VPS

Enterprise Business Critical VPS Note: https://docs.snowflake.com/en/user-guide/intro-editions.html#security-data-protections

What was Business Critical Edition formerly known as Standard Edition Enterprise Edition Business Edition Enterprise for Sensitive Data (ESD)

Enterprise for Sensitive Data (ESD)

With respect to Snowflake UI, which of the following is true? A single session can be shared between multiple worksheets Every worksheet can have a different role, warehouse and a database Worksheets cannot have different role, warehouse and database Every worksheet has its own session

Every worksheet can have a different role, warehouse and a database Every worksheet has its own session

How will you check if a share is inbound or outbound? Execute SHOW SHARES and look at kind column Execute describe share and look at location column Execute DISPLAY SHARE and look at kind column

Execute SHOW SHARES and look at kind column

One of your users have accidentally dropped a table T1 in production. What can you do to restore the table? Execute UNDROP TABLE T1; Execute UNDELETE TABLE T1; Call Snowflake team and request them to recover the data from cloud storage Nothing can be done, whatever is lost is lost See all questions BackSkip question

Execute UNDROP TABLE T1; Note: UNDROP is a very useful feature in Snowflake. It makes it easier to retrieve your data and that is because snowflake stores data and objects in encrypted form for 24 hours by default. You have an option to purchase even longer retention period, if required. Any lost data can be instantaneously recovered with the UNDROP features. It is a single short command UNDROP TABLE <tablename> In addition to this, you can also do UNDROP SCHEMA and UNDROP DATABASE if someone messes it up even bigger. https://docs.snowflake.com/en/sql-reference/sql/undrop-table.html#undrop-table

What are the recommendations from Snowflake to effectively and efficiently use a warehouse. Experiment with different types of queries and different warehouse sizes to determine the combinations that best meet your specific query needs and workload Don't focus on warehouse size. Snowflake utilizes per-second billing, so you can run larger warehouses (Large, X-Large, 2X-Large, etc.) and simply suspend them when not in use Be very careful on what size of warehouse you are picking up. The credits charged are based on number of queries running on the warehouse and the size of the warehouse

Experiment with different types of queries and different warehouse sizes to determine the combinations that best meet your specific query needs and workload Don't focus on warehouse size. Snowflake utilizes per-second billing, so you can run larger warehouses (Large, X-Large, 2X-Large, etc.) and simply suspend them when not in use Note: Remember one more thing. The credits are not dependent on number of queries running in your warehouse, it depends on how long your warehouse is active.

A transient table can be cloned to a permanent table TRUE FALSE

FALSE

You have a small table in snowflake which has only 10,000 rows. Specifying a clustering key will further improved the queries that run on this table FALSE TRUE

FALSE Note: Specifying a clustering key is not necessary for most tables. Snowflake performs automatic tuning via the optimization engine and micro-partitioning. In many cases, data is loaded and organized into micro-partitions by date or timestamp, and is queried along the same dimension. When should you specify a clustering key for a table? First, note that clustering a small table typically doesn't improve query performance significantly. For larger data sets, you might consider specifying a clustering key for a table when: The order in which the data is loaded does not match the dimension by which it is most commonly queried (e.g. the data is loaded by date, but reports filter the data by ID). If your existing scripts or reports query the data by both date and ID (and potentially a third or fourth column), you may see some performance improvement by creating a multi-column clustering key. Query Profile indicates that a significant percentage of the total duration time for typical queries against the table is spent scanning. This applies to queries that filter on one or more specific columns. Note that reclustering rewrites existing data with a different order. The previous ordering is stored for 7 days to provide Fail-safe protection. Reclustering a table incurs compute costs that correlate to the size of the data that is reordered. https://docs.snowflake.com/en/user-guide/table-considerations.html#when-to-set-a-clustering-key

PUT command can be run from Snowflake worksheets TRUE FALSE

FALSE Note: The PUT command cannot be executed from the Worksheets page in the Snowflake web interface; instead, use the SnowSQL client to upload data files, or check the documentation for the specific Snowflake client to verify support for this command. https://docs.snowflake.com/en/sql-reference/sql/put.html#usage-notes

A COUNT(*) and COUNT(<COLUMN_NAME>) on a table will always give you the same result TRUE FALSE

FALSE Note: A COUNT(*) will return a total count of rows in the table, while COUNT(<column_name>) will return a count of rows with a non-NULL value in that particular column. COUNT(A,B) only counts the rows that have no NULL values in either the A or B column while COUNT(<ALIAS>.*) can be used to count all the rows containing no NULL columns.

When you clone a table in snowflake, the storage requirements are doubled as it needs to now store two copies of data. TRUE FALSE

FALSE Note: A massive benefit of zero-copy cloning of snowflake is that the underlying data is never duplicated. Only the metadata/pointers of the micropartitons are replicated for the new cloned table. Hence, when you clone a table actual data is never copied over to the new cloned table.

Auto clustering can be switched off at an account level FALSE TRUE

FALSE Note: Auto clustering cannot be switched off at database of account level, it will need to be done at the table level.

Resource monitors can be used to control credit usage for the Snowflake-provided warehouses, including the snowpipe warehouse TRUE FALSE

FALSE Note: Resource monitors provide control over virtual warehouse credit usage; however, you cannot use them to control credit usage for the Snowflake-provided warehouses, including the SNOWPIPE warehouse.

A consumer of shared database can add as many tables and views they want in the shared database FALSE TRUE

FALSE Note: Shares are read only

Snowflake guarantees that only one instance of a task with a defined predecessor task is running at a given time TRUE FALSE

FALSE Note: Snowflake cannot guarantee that only one instance of a task with a defined predecessor task is running at a given time.

If you clone a database, the internal snowflake stages in that database are also cloned FALSE TRUE

FALSE Note: The following rules apply to cloning stages or objects that contain stages (i.e. databases and schemas): Individual external named stages can be cloned; internal named stages cannot be cloned. When cloning a database or schema: External named stages that were present in the source when the cloning operation started are cloned. Tables are cloned, which means their internal stages are also cloned. Internal named stages are not cloned. Regardless of how a stage was cloned, the clone does not include any of the files from the source. i.e. all cloned stages are empty.

You want to delete the snowpipe file loading metadata. You decided to truncate the table to be able to do this. This will remove the metadata from the pipe TRUE FALSE

FALSE Note: Unable to Reload Modified Data, Modified Data Loaded Unintentionally Snowflake uses file loading metadata to prevent reloading the same files (and duplicating data) in a table. Snowpipe prevents loading files with the same name even if they were later modified (i.e. have a different eTag). The file loading metadata is associated with the pipe object rather than the table. As a result: Staged files with the same name as files that were already loaded are ignored, even if they have been modified, e.g. if new rows were added or errors in the file were corrected. Truncating the table using the TRUNCATE TABLE command does not delete the Snowpipe file loading metadata. However, note that pipes only maintain the load history metadata for 14 days.

A user can be defaulted to a role which user does not have access to TRUE FALSE

FALSE Note: You will be able to create a user with a default role to which the user does not have access. However, the user will nit be able to logon to snowflake if he/she does not have access to the default role. Hence a user cannot be defaulted to a role which he/she does not have access to

You have just loaded a file named student_data.csv from your snowflake stage to snowflake table. You try to reload again using the COPY INTO command. You will be able to load the file. TRUE FALSE

FALSE Note: you can overridewith There is a way, you can use FORCE=TRUE

Which COPY INTO <table> copy options are not supported by SNOWPIPE? FILES = ( 'file_name1' [ , 'file_name2', ... ] ) ON_ERROR = ABORT_STATEMENT PURGE = TRUE | FALSE FORCE = TRUE | FALSE FILE_FORMAT =( { FORMAT_NAME = '[<namespace>.]<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]

FILES = ( 'file_name1' [ , 'file_name2', ... ] ) ON_ERROR = ABORT_STATEMENT PURGE = TRUE | FALSE FORCE = TRUE | FALSE Note: All COPY INTO <table> copy options are supported except for the following: FILES = ( 'file_name1' [ , 'file_name2', ... ] ) ON_ERROR = ABORT_STATEMENT SIZE_LIMIT = num PURGE = TRUE | FALSE (i.e. automatic purging while loading) MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE FORCE = TRUE | FALSE Note that you can manually remove files from an internal (i.e. Snowflake) stage (after they've been loaded) using the REMOVE command. RETURN_FAILED_ONLY = TRUE | FALSE VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS Please go through this link thoroughly https://docs.snowflake.com/en/sql-reference/sql/create-pipe.html#usage-notes Please note that PATTERN = 'regex_pattern' copy option is provided as a preview feature . If this is listed as one of the option, it will fall under not supported option.

Select the TABLE function which helps to convert semi-structured data to a relational representation FLATTEN CHECK_JSON PARSE_JSON

FLATTEN

Which OPTION will you set on the copy if you would like to ignore the load metadata? FORCE LOAD_UNCERTAIN_FILES IGNORE METADATA

FORCE Note: To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true. The copy option references load metadata, if available, to avoid data duplication, but also attempts to load files with expired load metadata. Alternatively, set the FORCE option to load all files, ignoring load metadata if it exists. Note that this option reloads files, potentially duplicating data in a table. https://docs.snowflake.com/en/user-guide/data-load-considerations-load.html#workarounds

What are the two types of data consumer accounts available in snowflake FULL ACCOUNT READER ACCOUNT SHARED ACCOUNT QUERY ONLY ACCOUNT

FULL ACCOUNT READER ACCOUNT

True or False: A customer using SnowSQL / native connectors will be unable to also use the Snowflake Web interface (UI) unless access to the UI is explicitly granted by supported.

False

True or False: A table in Snowflake can only be queried using the Virtual Warehouse used to load the data.

False

True or False: SF offers tools to extract data from source systems.

False

True or False: Virtual Warehouses cannot be resized while queries are running.

False

True or False: You can point Snowflake at any S3 bucket to directly query files in that bucket as long as the files are in Parquet or ORC format

False

Integration object is a first-class database object True False

False Note: Integration object is an account level object and not a database object

To improve performance materialized view can created on top of external tables without any additional cost TRUE False

False Note: Materialized views are designed to improve query performance for workloads composed of common, repeated query patterns. However, materializing intermediate results incurs additional costs. As such, before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough.

SF is available on premise? (TRUE OR FALSE)

False Note: Snowflake's data warehouse is a true SaaS offering. More specifically: There is no hardware (virtual or physical) for you to select, install, configure, or manage. There is no software for you to install, configure, or manage. Ongoing maintenance, management, and tuning is handled by Snowflake. Snowflake runs completely on cloud infrastructure. All components of Snowflake's service (other than an optional command line client), run in a public cloud infrastructure. Snowflake uses virtual compute instances for its compute needs and a storage service for persistent storage of data. Snowflake cannot be run on private cloud infrastructures (on-premises or hosted). Snowflake is not a packaged software offering that can be installed by a user. Snowflake manages all aspects of software installation and updates. https://docs.snowflake.com/en/user-guide/intro-key-concepts.html

Snowflake has administration settings for resource consumption to help with below activities Help control costs associated with unexpected warehouse credit usage Manage access to snowflake for specific users Manage availability of the product

Help control costs associated with unexpected warehouse credit usage Note: What is a resource monitor? To help control costs and avoid unexpected credit usage caused by running warehouses, Snowflake provides resource monitors. A virtual warehouse consumes Snowflake credits while it runs. Resource monitors can be used to impose limits on the number of credits that are consumed by: 1. User-managed virtual warehouses 2. Virtual warehouses used by cloud services

Which feature does not come with query profile? Graphical representation of the main components of the processing plan of the query Details and statistics for the overall query Hints for improving the query performance Statistics for each component of the query

Hints for improving the query performance

Which properties of a resource monitor can be modified? Increase or decrease the credit quota for the monitor If the monitor is monitoring your account, convert it to monitor individual warehouses If the monitor is monitoring individual warehouses: 1. Add or remove warehouses from the list. 2. Convert it to monitor your account Customize the schedule (frequency, start timestamp, and end timestamp) for the monitor Add or remove actions, or modify the threshold percentages for existing actions

Increase or decrease the credit quota for the monitor If the monitor is monitoring your account, convert it to monitor individual warehouses If the monitor is monitoring individual warehouses: 1. Add or remove warehouses from the list. 2. Convert it to monitor your account Customize the schedule (frequency, start timestamp, and end timestamp) for the monitor Add or remove actions, or modify the threshold percentages for existing actions

Which of the below are automatically provided by snowflake compared to other databases? Installation and Hardware Configurations Patch releases Physical Security Metadata and Collection statistics documentation

Installation and Hardware Configurations Patch releases Physical Security

Which of the below objects will contribute to your account level storage usage? Secure Views Internal Stage External stage Database and all tables inside it Functions

Internal Stage External stage Database and all tables inside it Note: SECURE VIEWS and FUNCTION definitions are stored in metadata store. They do not store any data physically. One of my students asked a valid question that external stage is a pointer to the cloud provider's storage and hence from a snowflake perspective there is no cost. This is absolutely true. But when this question is presented to you and it is not explicitly asking 'snowflake account', you should tick external stage also. If the question specifically asks about 'snowflake account', then you should not tick this option. This is kind of a trick question so please pay attention to the question text.

Which of the below are considered as best practices while loading data into snowflake? Isolate data loading workload into its own virtual warehouse Split large files into smaller files Compress the source files If format is in CSV, convert them into ORC

Isolate data loading workload into its own virtual warehouse Split large files into smaller files Compress the source files

In your organization, you use a 2XL warehouse to load data into Snowflake as part of your ETL workload. Your BI team reached out to you and wanted access to the data stored in Snowflake. You gave them access to the tables required for them to query the data. What will be your recommendation with respect to the warehouse that they will need to use? Since you already have a 2XL warehouse for loading data, you will let them use that warehouse Warehouse is not required for querying data in snowflake It is recommended to use a multi-warehouse approach for workload isolation in snowflake. Since you would not like the load workload to interfere with the read workload of the BI team, you will create a separate warehouse for them

It is recommended to use a multi-warehouse approach for workload isolation in snowflake. Since you would not like the load workload to interfere with the read workload of the BI team, you will create a separate warehouse for them Note: Snowflake's unique multi-cluster, shared data architecture makes it possible to allocate multiple independent, isolated clusters for processing while sharing the same data. In snowflake, each cluster can both read and write data complying to transactional consistency which is ensured by the cloud services layer. The compute clusters in Snowflake are called virtual warehouses. The size and resources of each virtual warehouse can be chosen independently by the user based on the characteristics and performance requirements for the workload(s) that will run on each virtual warehouse. It is, therefore, recommended to isolate workloads and have each workload use its own warehouse. It helps to track usage better and also through this means you will ensure that one workload does not interfere with the other workload. Hence you will create a separate workload for the BI TEAM

What is the benefit of client-side encryption in snowflake? It provides a secure system for managing data in cloud storage It helps to decrypt data faster at a later period It reduces the cost of encryption by the cloud provider

It provides a secure system for managing data in cloud storage

Select the ones that are true for snowflake kafka connector Kafka connector guarantees exactly-once delivery Kafka connector guarantees that rows are inserted in the order Kafka connector guarantees reprocessing of messages

Kafka connector guarantees exactly-once delivery

What does snowflake use for monitoring network traffic and use activity? Lacework Sumo logic Threat Stack

Lacework

You are a snowflake architect and you are investigating a long running query to optimize the query. You want to know if the query retrieved data from long-term centralized storage. If you are looking at the Query History table in the history area of the WebUI, where will you look for this information? Look in the Bytes Scanned Column for a green bar Look in the Bytes scanned column for the acronym LTCS Look in the Rows column for a red bar Look in the Rows column for the acronym LTCS

Look in the Bytes Scanned Column for a green bar

A stream stores data in the same shape as the source table (i.e. the same column names and ordering). What additional columns does it store? METADATA$ACTION METADATA$ISUPDATE METADATA$ROW_ID METADATA$STREAM_ID

METADATA$ACTION METADATA$ISUPDATE METADATA$ROW_ID

Snowflake supports multi-factor authentication (MFA) to provide increased login security for users connecting to Snowflake. Which statements are true about MFA security? MFA is not enabled by default. it is available to all, but user need to activate it MFA is an integrated feature powered by DUO Security Service MFA can be used to connect to SNOWFLAKE using Snowflake JDBC Driver MFA Login is designed to connect to snowflake only through the web interface

MFA is not enabled by default. it is available to all, but user need to activate it MFA is an integrated feature powered by DUO Security Service MFA can be used to connect to SNOWFLAKE using Snowflake JDBC Driver

Which access control privileges must be granted on specified resource monitors for roles other than account administrators to view and modify resource monitors as needed MONITOR CREATE MODIFY

MONITOR MODIFY

This property of resource monitor specifies whether the resource monitor is used to monitor the credit usage for your entire Account (i.e. all warehouses in the account) or a specific set of individual warehouses CREDIT QUOTA MONITOR LEVEL SCHEDULE

MONITOR LEVEL Note: Monitor Level This property specifies whether the resource monitor is used to monitor the credit usage for your entire Account (i.e. all warehouses in the account) or a specific set of individual warehouses. If this property is not set, the resource monitor doesn't monitor any credit usage. It simply remains dormant. https://docs.snowflake.com/en/user-guide/resource-monitors.html#monitor-level

Snowflake provides specific administration features and capabilities to support the following activities except? Manage databases and warehouses within snowflake account Manage roles and users within a snowflake account Monitor usage and manage resources to control costs in a snowflake account Manage 3rd party applications providing data to a snowflake account

Manage 3rd party applications providing data to a snowflake account

Snowflake provides specific administration features and capabilities to support the following activities Managing database and warehouses within a snowflake account Managing roles and users within a snowflake account Monitor usage and manage resources to control costs in a snowflake account Manage 3rd party applications providing data to snowflake

Managing database and warehouses within a snowflake account Managing roles and users within a snowflake account Monitor usage and manage resources to control costs in a snowflake account

What are the two modes in which a multi-cluster warehouse can run? Static Dynamic Maximized Auto-scale None of the above

Maximized Auto-scale Note: Maximized This mode is enabled by specifying the same value for both maximum and minimum clusters (note that the specified value must be larger than 1). In this mode, when the warehouse is started, Snowflake starts all the clusters so that maximum resources are available while the warehouse is running. This mode is effective for statically controlling the available resources (i.e. servers), particularly if you have large numbers of concurrent user sessions and/or queries and the numbers do not fluctuate significantly. Auto-scale This mode is enabled by specifying different values for maximum and minimum clusters. In this mode, Snowflake starts and stops clusters as needed to dynamically manage the load on the warehouse: As the number of concurrent user sessions and/or queries for the warehouse increases, and queries start to queue due to insufficient resources, Snowflake automatically starts additional clusters, up to the maximum number defined for the warehouse. Similarly, as the load on the warehouse decreases, Snowflake automatically shuts down clusters to reduce the number of running servers and, correspondingly, the number of credits used by the warehouse. To help control the usage of credits in Auto-scale mode, Snowflake provides a property, SCALING_POLICY, that determines the scaling policy to use when automatically starting or shutting down additional clusters. For more information, see Setting the Scaling Policy for a Multi-cluster Warehouse (in this topic). https://docs.snowflake.com/en/user-guide/warehouses-multicluster.html#maximized-vs-auto-scale

When configuring a Warehouse using a Snowflake edition that has Elastic Data Warehousing enabled, what facets or components will you need to configure that are not needed in accounts where Elastic Data Warehousing is not enabled Minimum and Maximum Clusters Minimum and Maximum Servers Scaling Policy Auto-Suspend Auto-Resume

Minimum and Maximum Clusters Scaling Policy In Snowflake Standard edition which is not having a support of multi cluster warehouse there is no need to configure the Min & Max clusters and also the scaling policy.

Select all characteristics of snowflake's multi-cluster environment Multiple virtual warehouses in a deployment Individual warehouses scale out/in based on query activity Multi-cluster warehouses support the same properties and actions as single-cluster warehouse User must specify which cluster each query will use

Multiple virtual warehouses in a deployment Individual warehouses scale out/in based on query activity Multi-cluster warehouses support the same properties and actions as single-cluster warehouse Note: Multiple virtual warehouse in a deployment means specifying more than 1 warehouse while creating a multi-cluster warehouse. In a multi-cluster warehouse, you can specify the maximum number of server cluster(or virtual warehouse) greater than 1 and upto 10 and also a minimum number of clusters(equal to or less than the maximum(up to 10). Additionally, multi-cluster warehouses support all the same properties and actions as single-cluster warehouses, including: 1. Specifying a warehouse size. 2. Resizing a warehouse at any time. 3. Auto-suspending a running warehouse due to inactivity; note that this does not apply to individual clusters, but rather the entire warehouse. 4. Auto-resuming a suspended warehouse when new queries are submitted. In a multi-cluster warehouse, individual warehouses can scale up or down based on query load which is not possible in a single cluster warehouse. However as mentioned above, it supports the same properties and actions like the single cluster warehouse

In load metadata, Snowflake maintains detailed metadata for each table into which data is loaded. What else is stored in this metadata Name of each file from which data was loaded File size Number of columns in the file ETag for the file Number of rows parsed in the file Timestamp of the last load for the file Information about any errors encountered in the file during loading

Name of each file from which data was loaded File size ETag for the file Number of rows parsed in the file Timestamp of the last load for the file Information about any errors encountered in the file during loading Note: https://docs.snowflake.com/en/user-guide/data-load-considerations-load.html#load-metadata

PUT command in snowflake can upload files from a local folder/directory of a client machines to the below locations Named internal stage Internal stage for a specified table Internal stage for the current user External Stage

Named internal stage Internal stage for a specified table Internal stage for the current user

Which of the following best describes SF's processing engine? Leverages Apache Spark Based on Map Reduce A derivative of Presto Native SQL

Native SQL

Which of the following best describes Snowflake's processing engine EMR(Elastic Map Reduce) Spark Engine Presto Native SQL Database engine

Native SQL Database engine Note: https://docs.snowflake.com/en/user-guide/intro-key-concepts.html#key-concepts-architecture

Which of the following is not a characteristic of micro-partitions in snowflake? New partitions are created in logical properties Avoids skews between partitions Partitioning is automatically completed on the natural ingestion order of the data The natural ingestion order maintains correlations between columns which could be useful for pruning

New partitions are created in logical properties

create or replace table result_scan_table_1 like result_scan_table; Will the above query cost compute credits? No, since it is a metadata operation only Yes, it will need compute as the table structure is also getting created It is hard to say without looking at query profile

No, since it is a metadata operation only Note: Why do you think it will not cost any compute credit? The reason behind this is that the LIKE clause only creates the definition of the table(or the structure of the table) in the metadata repository. It does not require any compute credit for any type of metadata operations.

What will the below query return SELECT TOP 10 GRADES FROM STUDENT; The top 10 highest grades The 10 lowest grades Non-deterministic list of 10 grades

Non-deterministic list of 10 grades

Resource monitors support the below actions Notify & Suspend Notify & Suspend Immediately Notify Notify and automatically call the account administrator

Notify & Suspend Notify & Suspend Immediately Notify

You are sizing a snowflake warehouse, what factors would you consider? Number of users Number of concurrent queries Number of tables being queried Data size and composition

Number of concurrent queries Number of tables being queried Data size and composition

When you size a warehouse, which of the below factors are not required to be considered Number of users Number of concurrent queries Number of tables queried Data size and composition All of the above

Number of users

If you plan to regularly unload similarly-formatted data, it is recommended to use named file formats (TRUE OR FALSE)

TRUE

If you clone a permanent table(bar) into a transient table(foo) using the below command create transient table foo clone bar copy grants; What will happen to the partitions? Old partitions will not be affected,but new partitions added to the clone will follow the transient lifecycle All the partitions will be affected Only the old partitions will be affected

Old partitions will not be affected,but new partitions added to the clone will follow the transient lifecycle

The Cloning feature in Snowflake requires less storage because Only metadata is copied; no physical data is copied The cloned data is constantly updated to synchronize with original table data Data is replicated to ensure integrity of data in the original table data

Only metadata is copied; no physical data is copied There is no data copy that happens while clone is created. Its just one more object that gets created which points to the same underlying data as that of original table. Only when changes are made to clone then it starts incurring storage costs.

The following factors affect data load rates: A. Physical location of the stage B. RAM on the virtual warehouse C. Gzip compression efficiency D. Thread size

Physical location of the stage Gzip compression efficiency

A Snowflake mechanism that is used to limit the number of micro-partitions scanned by a query is called ________?

Pruning

If you recreate a storage integration after it has been linked to one or more stages, you must reestablish the association between each stage by running the ALTER STAGE command. (TRUE OR FALSE)

TRUE

Query execution is performed by which layers of Snowflake architecture Storage Layer Query Processing Layer Cloud Services Layer

Query Processing Layer Query execution is performed using Virtual Warehouse, which is also called as compute layer or Query Processing Layer.

Which layer in the snowflake architecture contains virtual warehouse Cloud services Query processing Database Storage None

Query processing

How do you remove files from a stage? REMOVE DELETE PURGE TRUNCATE

REMOVE Note: Removes files that have been staged (i.e. uploaded from a local file system or unloaded from a table) in one of the following Snowflake internal stages: Named internal stage. Stage for a specified table. Stage for the current user. Note that using the command to remove files from an external stage might work but is not officially supported. REMOVE can be abbreviated to RM. https://docs.snowflake.com/en/sql-reference/sql/remove.html#remove

Which is not a characteristic of time travel? Protection from accidental data operations Previous versions of data automatically retained Retention period selected by customers (up to 90 days for Enterprise edition) Recover data with the cost of running backups

Recover data with the cost of running backups

Which of the below activities can be done leveraging time-travel feature of snowflake? Restoring data-related objects (tables, schemas, and databases) that may have been accidentally or intentionally deleted Duplicating and backing up data from key points in the past Analyzing data usage/manipulation over specified periods of time To backup data for compliance and regulatory requirements

Restoring data-related objects (tables, schemas, and databases) that may have been accidentally or intentionally deleted Duplicating and backing up data from key points in the past Analyzing data usage/manipulation over specified periods of time

Which cache runs on a 24 hour clock? Result Cache Metadata cache Warehouse cache

Result Cache Note: WH Cache is when WH goes offline Result Cache is 24 hours Metadata cache is always changing as tables change

You want to update the value of a specific column in a table. But the updation is not based on any specific condition. You just need to ensure that 80% of the rows in the table are updated with the new value. What snowflake feature will you use SAMPLE CONCAT AVERAGE MAX

SAMPLE

If you want to clone a table, your role must have which access on the Table SELECT USAGE READ CLONE

SELECT Note: Tables - SELECT Pipes, Streams, Tasks - OWNERSHIP Other objects - USAGE

You have a Snowflake table which is defined as below CREATE OR REPLACE TABLE FRUITS(FRUIT_NUMBER NUMBER, FRUIT_DESCRIPTION VARCHAR, AVAILABILITY VARCHAR); If you would like to convert the fruit_number column to be a decimal with a certain precision and scale, which command will you run? SELECT FRUIT_NUMBER::DECIMAL(10,5) FROM FRUITS; SELECT FRUIT_NUMBER(DECIMAL(10,5)) FROM FRUITS; SELECT FRUIT_NUMBER AS DECIMAL(10,5) FROM FRUITS; SELECT FRUIT_NUMBER.DECIMAL(10,5) FROM FRUITS;

SELECT FRUIT_NUMBER::DECIMAL(10,5) FROM FRUITS;

Which of the below operations are allowed on an inbound share data? MERGE CREATE TABLE ALTER TABLE SELECT WITH GROUP BY SELECT WITH JOIN INSERT INTO

SELECT WITH GROUP BY SELECT WITH JOIN

Output of a FLATTEN query has the below columns. Select three. SEQ PATH VALUE RECORD_METADATA

SEQ PATH VALUE

Choose the continuous data loading options from below SNOWPIPE SNOWFLAKE CONNECTOR FOR KAFKA Third-party data integration tools BULK COPY

SNOWPIPE SNOWFLAKE CONNECTOR FOR KAFKA Third-party data integration tools

Snowflake supports the following performance optimization techniques SSD CACHING/DATA CACHING QUERY RESULT CACHING B-TREE INDEXES

SSD CACHING/DATA CACHING QUERY RESULT CACHING Note: https://community.snowflake.com/s/article/Caching-in-Snowflake-Data-Warehouse

All of the below are valid executionState of a snowpipe except: RUNNING STOPPED_FEATURE_DISABLED STALLED_EXECUTION_ERROR PAUSED STOPPED

STOPPED Note: Current execution state of the pipe; could be any one of the following: RUNNING (i.e. everything is normal; Snowflake may or may not be actively processing files for this pipe) STOPPED_FEATURE_DISABLED STOPPED_STAGE_DROPPED STOPPED_FILE_FORMAT_DROPPED STOPPED_MISSING_PIPE STOPPED_MISSING_TABLE STALLED_COMPILATION_ERROR STALLED_INITIALIZATION_ERROR STALLED_EXECUTION_ERROR STALLED_INTERNAL_ERROR PAUSED PAUSED_BY_SNOWFLAKE_ADMIN PAUSED_BY_ACCOUNT_ADMIN

Snowflake adds the system defined roles to each new account. Check the system defined roles below. SYSADMIN ACCOUNTADMIN SECURITYADMIN PUBLIC USERADMIN SUPERUSER

SYSADMIN ACCOUNTADMIN SECURITYADMIN PUBLIC USERADMIN

To help control the usage of credits in Auto-scale mode, Snowflake provides a property, _________________, that determines the scaling policy to use when automatically starting or shutting down additional clusters. Select the property Auto Scale Scaling Policy Max no of cluster Min no of cluster

Scaling Policy Note: To help control the credits consumed by a multi-cluster warehouse running in Auto-scale mode, Snowflake provides scaling policies, which are used to determine when to start or shut down a cluster. The scaling policy for a multi-cluster warehouse only applies if it is running in Auto-scale mode. In Maximized mode, all clusters run concurrently so there is no need to start or shut down individual clusters. https://docs.snowflake.com/en/user-guide/warehouses-multicluster.html#setting-the-scaling-policy-for-a-multi-cluster-warehouse

An entity to which access can be granted. Unless allowed by a grant, access will be denied. in snowflake terms, what is this called? Securable object Role Privilege User

Securable object Note: Securable object: An entity to which access can be granted. Unless allowed by a grant, access will be denied. Role: An entity to which privileges can be granted. Roles are in turn assigned to users. Note that roles can also be assigned to other roles, creating a role hierarchy. Privilege: A defined level of access to an object. Multiple distinct privileges may be used to control the granularity of access granted. User: A user identity recognized by Snowflake, whether associated with a person or program. https://docs.snowflake.com/en/user-guide/security-access-control-overview.html#access-control-framework

All security related information is stored in which layer of snowflake architecture Service Storage Compute All of the above

Service Note: Service(or Cloud service) layer is the one which stores all security related information The services included in this layer are 1. Authentication 2. Infrastructure management 3. Metadata management 4. Query parsing and optimization 5. Access control

You are speaking to a CTO of an organization and would like to define the architecture of snowflake to him in two words. What will you use? Shared Disk Shared Nothing Shared Data Shared Memory

Shared Data Note: Snowflake's architecture is a hybrid of traditional shared-disk database architectures and shared-nothing database architectures. Similar to shared-disk architectures, Snowflake uses a central data repository for persisted data that is accessible from all compute nodes in the data warehouse. But similar to shared-nothing architectures, Snowflake processes queries using MPP (massively parallel processing) compute clusters where each node in the cluster stores a portion of the entire data set locally. This approach offers the data management simplicity of a shared-disk architecture, but with the performance and scale-out benefits of a shared-nothing architecture. https://docs.snowflake.com/en/user-guide/intro-key-concepts.html

Select the statements that are true for a shared database Shared databases are read-only A clone can be created from a shared database Time travel can be done on a shared database Can edit the comments for a shared database

Shared databases are read-only Note: https://docs.snowflake.com/en/user-guide/data-share-consumers.html#general-limitations-for-shared-databases

Select the statements that are true for a data shar Shares are named Snowflake objects Share consists of privileges that grant access to the database(s) and the schema containing the objects to share Share consists of privileges that grant access to the specific objects in the database Share consists of consumer accounts with which the database and its objects are shared

Shares are named Snowflake objects Share consists of privileges that grant access to the database(s) and the schema containing the objects to share Share consists of privileges that grant access to the specific objects in the database Share consists of consumer accounts with which the database and its objects are shared Note: https://docs.snowflake.com/en/user-guide/data-sharing-intro.html#what-is-a-share

Which factors influence the unit cost of snowflake credits and data storage? Snowflake Edition The region where your Snowflake account is located Whether it is an On Demand or Capacity account Number of users of snowflake

Snowflake Edition The region where your Snowflake account is located Whether it is an On Demand or Capacity account Note: The Snowflake Edition that your organization chooses determines the unit costs for the credits and the data storage you use. Other factors that impact unit costs are the region where your Snowflake account is located and whether it is an On Demand or Capacity account: 1. On Demand: Usage-based pricing with no long-term licensing requirements. 2. Capacity: Discounted pricing based on an up-front Capacity commitment.

Mark all the true statements Snowflake Stages can be defined as either External or Internal. Snowflake Stages can be defined as either FTP or SFTP. External Stages require a cloud storage provider. FTP Stages require three cloud storage providers. Cloud storage providers must be linked to internal stage objects.

Snowflake Stages can be defined as either External or Internal. External Stages require a cloud storage provider.

With default settings, how long will a query run on snowflake Snowflake will cancel the query if it runs more than 48 hours Snowflake will cancel the query if it runs more than 24 hours Snowflake will cancel the query if the warehouse runs out of memory Snowflake will cancel the query if the warehouse runs out of memory and hard disk storage

Snowflake will cancel the query if it runs more than 48 hours

Select the limitations with materialized views(Select 3) Snowflake's "Time Travel" feature is not supported on materialized views using CURRENT_TIME or CURRENT_TIMESTAMP is not permitted A materialized view cannot include Window functions Clustering keys cannot be defined in Materialized views

Snowflake's "Time Travel" feature is not supported on materialized views using CURRENT_TIME or CURRENT_TIMESTAMP is not permitted A materialized view cannot include Window functions

What is a point in time snapshot of data which can be updated by users is called Snowflakes' cloning Time travel Fail safe

Snowflakes' cloning

Which of the following is not a snowpipe feature? Snowpipe can load data from any internal or external stage It is a server-less compute model The service provides REST end points and uses snowflake provided compute resources to load the data and retrieve history reports Snowpipe loads data after it is in stage and use executes the LOADDATA command

Snowpipe loads data after it is in stage and use executes the LOADDATA command Note: There is no LOADDATA command and data is automatically loaded by snowpipe after the files are added to the stage https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html#introduction-to-snowpipe

What scaling policies are supported by snowflake? Standard Economy Premium

Standard Economy Note: Standard (default) Prevents/minimizes queuing by favoring starting additional clusters over conserving credits. Economy Conserves credits by favoring keeping running clusters fully-loaded rather than starting additional clusters, which may result in queries being queued and taking longer to complete. https://docs.snowflake.com/en/user-guide/warehouses-multicluster.html#setting-the-scaling-policy-for-a-multi-cluster-warehouse

What are the two types of snowflake data listings which define how data is shared and consumed via data marketplace? Standard Data Listings Personalized Data Listings Business Critical Data Listings Enterprise Data Listings

Standard Data Listings Personalized Data Listings Note: The Snowflake Data Marketplace offers two types of data listings which define how data is shared and consumed. Once published, both types of listings are displayed to consumers in the selected regions. The key difference between standard and personalized listings is the ability to access the data share. Personalized listings allow you to control which consumers can access the data. Standard Data Listings A standard listing provides instant access to a published data set. This type of listing is best for providing generic, aggregated, or non-customer-specific data. Each listing includes details about the shared data, sample queries, and information about the data provider. To access data from a standard data listing, consumers click Get, opening a prompt where they name the database for Snowflake users, permission access among Snowflake users, and agree to the provider's terms of use and Snowflake's consumer terms. Clicking Create Database immediately creates the database inside their Snowflake account in the Databases tab. Only users that have a role with the IMPORT SHARE privilege can create databases from the Snowflake Data Marketplace. Personalized Data Listings A personalized listing allows customers to request specific data sets. This can be premium data that a provider charges for or data that is specific to each consumer. Each listing includes details about the shared data, sample usage examples, and information about the data provider. To access data from a personalized listing, consumers must submit a request by clicking the Request button, and provide their contact information. Once a request is submitted, the data provider is notified. The provider then contacts the consumer. Each data provider can have different commercial terms. Once those are agreed to, then the personalized data set is created and shared with the consumer.

Which is the preferred way to store a generated identity and access management (IAM) entity for external cloud storage Access Token Stage Storage integration File Format

Storage integration

Which of the below columns are usually a good choice for clustering key. UUID column in a Customer fact table Gender with Male/Female values in a multi-terabyte table Timestamp column in a 10TB order fact table Store_Id in a 2 TB Item Store Sales fact table

Store_Id in a 2 TB Item Store Sales fact table Note: Remember that a cluster key should not have a very high cardinality nor it should have a very low cardinality. If it has high cardinality, there will be lot of partitions that will be created, with low cardinality very few partitions will be created and hence query perfromance will not be able take advantage of partition pruning. We need to take a middle ground.

Select two true statement related to streams Stream itself does not contain any table data A stream only stores the offset for the source table The hidden columns used by a stream does not consume any storage

Stream itself does not contain any table data A stream only stores the offset for the source table

Select two options which are true about variant data type in snowflake? Supports querying using JSON path notation Optimizes storage based on repeated elements A separate file format is used to store variant data Requires custom mapping for each type of record

Supports querying using JSON path notation Optimizes storage based on repeated elements

When a resource limit has hit the limit or is about to hit the limit, what can the resource monitor do Suspend the warehouse Send alert notifications Kill the query that is running

Suspend the warehouse Send alert notifications Kill the query that is running

The FLATTEN command in snowflake has two version. One version uses a join and the other version uses an object keyword. Please select two words that represent the options used with the command? OBJECT_CONSTRUCT TABLE TRY_CAST LATERAL

TABLE LATERAL

Snowflake has three types of stages USER, TABLE, NAMED. Named stage is again divided into External and Internal. Which of the below two stages are automatically created and does not need explicit configuration by the user TABLE EXTERNAL INTERNAL USER

TABLE USER

Select the table type that is automatically deleted after the session is closed and hence it has no fail-safe or time travel option post closure of the session TRANSIENT TEMPORARY PERMANENT EXTERNAL

TEMPORARY Note: temporary are valid for a session transient are same as permanent except they do not have a fail safe period

A simple tree of tasks is limited to a maximum of 1000 tasks total TRUE FALSE

TRUE

A simple tree of tasks is limited to a maximum of 1000 tasks total (including the root task) in a resumed state (TRUE OR FALSE)

TRUE

A single storage integration can support multiple external stages (TRUE OR FALSE)

TRUE

A stream object records the delta of change data capture (CDC) information for a table (such as a staging table), including inserts and other data manipulation language (DML) changes. TRUE FALSE

TRUE

A warehouse can be assigned to only a single resource monitor. TRUE FALSE

TRUE

ACCOUNTADMIN ROLE encapsulates the SYSADMIN and SECURITYADMIN system-defined roles and ACCOUNTADMIN sits on top of the role hierarchy TRUE FALSE

TRUE

According to snowflake architecture, data storage is independent of compute TRUE FALSE

TRUE

All virtual warehouses in snowflake has access to all data TRUE FALSE

TRUE

Automatic reclustering in snowflake is triggered only if/when the table would benefit from the operation (TRUE OR FALSE)

TRUE

Before any PHI data can be stored in Snowflake, a signed business associate agreement (BAA) must be in place between your agency/organization and Snowflake Inc TRUE FALSE

TRUE

COPY and INSERT operations in snowflake are non-blocking TRUE FALSE

TRUE

COPY transformations are supported only for named stages(both internal and external) and user stages TRUE FALSE

TRUE

Clustering is generally most cost-effective for tables that are queried frequently and do not change frequently (TRUE OR FALSE)

TRUE

Each time a persisted result for a query is reused, Snowflake resets the 24-hour retention period for the result, up to a maximum of 31 days from the date and time that the query was first executed. After 31 days, the result is purged and the next time the query is submitted, a new result is generated and persisted. (TRUE OR FALSE)

TRUE

Each time a warehouse is started or resized to a larger size, the warehouse is billed for 1 minute's worth of usage.After 1 minute, all subsequent billing is per-second TRUE FALSE

TRUE

Failsafe period for temporary and transient table is zero (TRUE OR FALSE)

TRUE

Federated authentication in snowflake is compliant with SAML2.0 TRUE FALSE

TRUE

Files that are already copied from the stage to the source table can be loaded again into a table cloned from the source table (TRUE OR FALSE)

TRUE

GET does not support downloading files from external stages (TRUE OR FALSE)

TRUE

If multiple instances of the kafka connector is started on the same topic or partitions, duplicate records may flow into snowflake table TRUE FALSE

TRUE

If query performance degrades over time, the table is likely no longer well-clustered and may benefit from clustering (TRUE OR FALSE)

TRUE

If you are on Snowflake Enterprise Edition (and higher), the time travel retention period can be set to any value from 0 up to 90 days for databases, schemas, and tables TRUE FALSE

TRUE

Metadata columns for staged files can only be queried by name (TRUE OR FALSE)

TRUE Note: Metadata cannot be inserted into existing table rows. Metadata columns can only be queried by name; as such, they are not included in the output of any of the following statements: SELECT * SHOW <objects> DESCRIBE <object> Queries on INFORMATION_SCHEMA views https://docs.snowflake.com/en/user-guide/querying-metadata.html#query-limitations

A table stream(also known as stream) itself does not contain any table data (TRUE OR FALSE)

TRUE Note: Note that a stream itself does not contain any table data. A stream only stores the offset for the source table and returns CDC records by leveraging the versioning history for the source table. When the first stream for a table is created, a pair of hidden columns are added to the source table and begin storing change tracking metadata. These columns consume a small amount of storage. The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table. https://docs.snowflake.com/en/user-guide/streams.html#overview-of-table-streams

You are loading data to a snowflake internal stage area using the PUT Command, the data will be encrypted on the client's machine and you will not be able to turn off the encryption. (TRUE OR FALSE)

TRUE Note: One of the biggest worries people have about moving to the cloud is security. One key piece of providing enterprise class security is the ability to encrypt the data in your data warehouse environment. With Snowflake, your data is automatically encrypted by default. https://www.snowflake.com/blog/automatic-encryption-data/

Snowflake does not support nested transactions (TRUE OR FALSE)

TRUE Note: Overlapping Transactions This section describes overlapping transactions. A stored procedure that contains a transaction can be called from within another transaction. The outer transaction can be in an outer stored procedure or can be outside any stored procedure. The inner transaction is not treated as nested; instead, the inner transaction is a separate transaction. Snowflake calls these "autonomous scoped transactions" (or simply "scoped transactions"), because each transaction executes in a conceptually independent scope. Note Terminology note: The terms "inner" and "outer" are commonly used when describing nested operations, such as nested stored procedure calls. Although Snowflake supports nested procedure calls, Snowflake does not support nested transactions; https://docs.snowflake.com/en/sql-reference/transactions.html#overlapping-transactions

Snowflake waits till all the servers are provisioned for a new virtual warehouse, before it executes query on that warehouse (TRUE OR FALSE)

TRUE Note: Snowflake does not begin executing SQL statements submitted to a warehouse until all of the servers for the warehouse are successfully provisioned, unless any of the servers fail to provision: 1. If any of the servers for the warehouse fail to provision during start-up, Snowflake attempts to repair the failed server(s). 2. During the repair process, the warehouse starts processing SQL statements once 50% or more of the requested servers are successfully provisioned.

Streams support repeatable read isolation (TRUE OR FALSE)

TRUE Note: Streams support repeatable read isolation. In repeatable read mode, multiple SQL statements within a transaction see the same set of records in a stream. This differs from the read committed mode supported for tables, in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed. https://docs.snowflake.com/en/user-guide/streams.html#repeatable-read-isolation

When you load data into snowflake, snowflake reorganizes the data into its internal optimized compressed columnar format TRUE FALSE

TRUE Note: When data is loaded into Snowflake, Snowflake reorganizes that data into its internal optimized, compressed, columnar format. Snowflake stores this optimized data in cloud storage. Snowflake manages all aspects of how this data is stored — the organization, file size, structure, compression, metadata, statistics, and other aspects of data storage are handled by Snowflake. The data objects stored by Snowflake are not directly visible nor accessible by customers; they are only accessible through SQL query operations run using Snowflake.

The warehouse cache may be reset if a running warehouse is suspended and then resumes TRUE FALSE

TRUE Note: You do not need to remember this, just understand the concept. Warehouses are nothing but VM instances underneath. And the cache is nothing but SSD cache attached to the instances, If the warehouse is suspended and resumed again it may not get teh same instance.

You have many queries that needs to run at the same time on a warehouse and you need high concurrency when they execute. You will go for a multi-cluster warehouse in this case. TRUE FALSE

TRUE Note: 1. Multi Cluster warehouse can scale compute resources 2. The scaling is based on concurrent users and queries Multi-cluster warehouses enable you to scale compute resources to manage your user and query concurrency needs as they change, such as during peak and off hours.

A DML statement that selects from a stream consumes all of the change data in the stream as long as the transaction commits successfully TRUE FALSE

TRUE Note: A DML statement that selects from a stream consumes all of the change data in the stream as long as the transaction commits successfully. To ensure multiple statements access the same change records in the stream, surround them with an explicit transaction statement (BEGIN .. COMMIT). This locks the stream. DML updates to the source table in parallel transactions are tracked by the change tracking system but do not update the stream until the explicit transaction statement is committed and the existing change data is consumed. This is very important to know, if you are given a TASK which consumes from the stream and a set of query statements and then if you are asked which query statement will consume all the change data then please select the DML query statement(like INSERT query)

The COPY command is more performant than the INSERT statement? TRUE FALSE

TRUE Note: Do you remember that Snowflake stores data physically into immutable micro-partitions? So, how does insert/update work in snowflake? Since the micropartitons are immutable, any insert or update will have to copy the entire micro-partition into a new copy and then insert the new record in that partition. For update the old record will be marked for deletion and the new record will be added. so, imagine if you do insert one by one, how many micropartitons will get created. Snowflake's architecture is optimized for bulk load. Otherwise, small DMLs (and at high frequency) cause issue of triggering the engine to create a lot of small partition files (this is because each DML create a new (micro)partition file; and a lot of small partition files are bad for data processing and the database engine has to regularly compact the small partition files into larger ones for more optimal query performance). Hope this clarifies the concept.

Multiple streams can be created for the same table and consumed by different tasks TRUE FALSE

TRUE Note: Multiple tasks that consume change data from a single table stream retrieve different deltas. When a task consumes the change data in a stream using a DML statement, the stream advances the offset. The change data is no longer available for the next task to consume. Currently, we recommend that only a single task consumes the change data from a stream. Multiple streams can be created for the same table and consumed by different tasks.

A share cannot be shared TRUE FALSE

TRUE Note: read-only

Time travel and fail safe requires additional storage which will cost you storage cost TRUE FALSE

TRUE Note: The fees are calculated for each 24-hour period (i.e. 1 day) from the time the data changed. The number of days historical data is maintained is based on the table type and the Time Travel retention period for the table. Also, Snowflake minimizes the amount of storage required for historical data by maintaining only the information required to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the table that changed. Full copies of tables are only maintained when tables are dropped or truncated.

Which three objects did we explicitly refer to using the COPY INTO command to load data on using external stages? Table File Format View Stage

Table File Format Stage

Select two true statements for FLATTEN ? Takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view Can be used to convert semi-structured data to a relational representation Cannot be used on permanent tables

Takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view Can be used to convert semi-structured data to a relational representation

Create a task and a stream following the below steps. So, when the system$stream_has_data('rawstream1') condition returns false, what will happen to the task ? -- Create a landing table to store raw JSON data. -- Snowpipe could load data into this table. create or replace table raw (var variant); -- Create a stream to capture inserts to the landing table. -- A task will consume a set of columns from this stream. create or replace stream rawstream1 on table raw; -- Create a second stream to capture inserts to the landing table. -- A second task will consume another set of columns from this stream. create or replace stream rawstream2 on table raw; -- Create a table that stores the names of office visitors identified in the raw data. create or replace table names (id int, first_name string, last_name string); -- Create a table that stores the visitation dates of office visitors identified in the raw data. create or replace table visits (id int, dt date); -- Create a task that inserts new name records from the rawstream1 stream into the names table -- every minute when the stream contains records. -- Replace the 'etl_wh' warehouse with a warehouse that your role has USAGE privilege on. create or replace task raw_to_names warehouse = etl_wh schedule = '1 minute' when system$stream_has_data('rawstream1') as merge into names n using (select var:id id, var:fname fname, var:lname lname from rawstream1) r1 on n.id = to_number(r1.id) when matched then update set n.first_name = r1.fname, n.last_name = r1.lname when not matched then insert (id, first_name, last_name) values (r1.id, r1.fname, r1.lname) ; -- Create another task that merges visitation records from the rawstream1 stream into the visits table -- every minute when the stream contains records. -- Records with new IDs are inserted into the visits table; -- Records with IDs that exist in the visits table update the DT column in the table. -- Replace the 'etl_wh' warehouse with a warehouse that your role has USAGE privilege on. create or replace task raw_to_visits warehouse = etl_wh schedule = '1 minute' when system$stream_has_data('rawstream2') as merge into visits v using (select var:id id, var:visit_dt visit_dt from rawstream2) r2 on v.id = to_number(r2.id) when matched then update set v.dt = r2.visit_dt when not matched then insert (id, dt) values (r2.id, r2.visit_dt) ; -- Resume both tasks. alter task raw_to_names resume; alter task raw_to_visits resume; -- Insert a set of records into the landing table. insert into raw select parse_json(column1) from values ('{"id": "123","fname": "Jane","lname": "Smith","visit_dt": "2019-09-17"}'), ('{"id": "456","fname": "Peter","lname": "Williams","visit_dt": "2019-09-17"}'); -- Query the change data capture record in the table streams select * from rawstream1; select * from rawstream2; Task will be executed but no rows will be merged Task will return an warning message Task will be skipped

Task will be skipped

Which of these are Snowflake table types? Materialized Secure Temporary Permanent Transient External

Temporary Permanent Transient External Explanation Secure and Materialized are type of views, while rest are type of tables.

In a stream, if a row is added and then updated in the current offset, how will the delta change be represented The delta change is a new row The delta change will be a pair of delete and insert record The delta change will be a pair of insert and upate record

The delta change is a new row Note: You know its an update from METADATA$ISUPDATE

What factors influence the credits charged for a warehouse? The number of servers per cluster (determined by warehouse size) The number of clusters (if using multi-cluster warehouses) The size of the query that runs on the warehouse The length of time each server in each cluster runs

The number of servers per cluster (determined by warehouse size) The number of clusters (if using multi-cluster warehouses) The length of time each server in each cluster runs

In which scenarios would you consider to use materialized views The query results contain a small number of rows and/or columns relative to the base table Query results contain results that require significant processing Query is on an external table View's base table does not change frequently None of the above

The query results contain a small number of rows and/or columns relative to the base table Query results contain results that require significant processing Query is on an external table View's base table does not change frequently

Select all the options which are true about the data that is stored in a micro partition metadata The range of values for each of the columns in the micro-partition Mean and standard deviation of each column The number of distinct values Additional properties used for both optimization and efficient query processing

The range of values for each of the columns in the micro-partition The number of distinct values Additional properties used for both optimization and efficient query processing

You ran the below query. I have a warehouse with auto suspend set at 5 seconds. Also result cache is disabled. SELECT * FROM INVENTORY; The query profile looks like as below. Please see below 'Percentage scanned from cache' is 0% You ran the query again before 5 seconds has elapsed and the query profile looks as below. Look at the 'Percentage scanned for cache', it is 75% You ran the query again after 5 seconds. The query profile looks as below. Look at the 'Percentage scanned from cache', it is zero again. Why is this happening? The second run of the query used data cache to retrieve part of the result since it ran before the warehouse was suspended The second run of the query used query result cache The third run of the query used query result cache

The second run of the query used data cache to retrieve part of the result since it ran before the warehouse was suspended Note: Virtual warehouses are an abstraction on the compute instances of the cloud provider(in case of AWS, it is EC2 instances). Each Virtual warehouse is a cluster of these compute instances(or EC2 in case of AWS). The compute instances has local SSD attached to them. When you ran the query for the first time, the results of the query were retrieved from the remote storage(which is the object store of the cloud provider, S3 in case of AWS), part of the results also got cached in the local SSD storage of the compute instance. So, when we ran the query second time, part of the results got retrieved from the SSD cache also known as Data Cache. Ok, if that is the case why did not it retrieve from data cache the third time. The third run of the query happened after 5 seconds. The virtual warehouse had a auto suspend setting of 5 seconds. So, since there were no activity for 5 seconds, the warehouse suspended itself. When the warehouse is suspended, it loses the data cache. Why? because when it resumes it may be a completely new set of compute instances which were not used in the earlier runs. Hope this clarifies and you probably will never forget this concept now. Please remember SSD cache is also called Data cache In real work scenario, how can you leverage the understanding of this concept. Well if you a nightly ETL that runs against several tables, group the ETLs which use similar tables to run at the same time. This will help you to utilize the data cache effectively. This strategy, I named it as ETL-Colocation:)

When will you consider creating a clustering key on a table. Please select all that applies The table houses multi-terabyte data The table has variant columns The query performance on the table has degraded over time

The table houses multi-terabyte data The query performance on the table has degraded over time note: 1. Clustering will be effective only when you have enough partitions in the table and the table is more than 1 TB 2. As you insert/update rows in the table, the partitions are moved around and hence it no longer remains clustered. With Auto clustering, Snowflake automatically reclusters the table based on a proprietary algorithm

Select the ones that are true about data sharing through data shares There is only one copy of data, which lives in the data provider's account Shared data is always live, real-time and immediately available to consumers Providers can establish revocable, fine-grained access grants to shares Data Sharing is supported between accounts in the same or different Snowflake Provider and Region

There is only one copy of data, which lives in the data provider's account Shared data is always live, real-time and immediately available to consumers Providers can establish revocable, fine-grained access grants to shares

The storage architecture of snowflake has two key features, they are Time travel Replication Zero-copy cloning Query tag

Time travel Zero-copy cloning

Time travel is available for which table types? Transient Permanent External Temporary

Transient Permanent Temporary Refer Link for details - https://docs.snowflake.com/en/user-guide/data-time-travel.html A key component of Snowflake Time Travel is the data retention period. When data in a table is modified, including deletion of data or dropping an object containing data, Snowflake preserves the state of the data before the update. The data retention period specifies the number of days for which this historical data is preserved and, therefore, Time Travel operations (SELECT, CREATE ... CLONE, UNDROP) can be performed on the data. The standard retention period is 1 day (24 hours) and is automatically enabled for all Snowflake accounts: For Snowflake Standard Edition, the retention period can be set to 0 (or unset back to the default of 1 day) at the account and object level (i.e. databases, schemas, and tables). For Snowflake Enterprise Edition (and higher): For transient databases, schemas, and tables, the retention period can be set to 0 (or unset back to the default of 1 day). The same is also true for temporary tables. For permanent databases, schemas, and tables, the retention period can be set to any value from 0 up to 90 days.

True or False: When data is staged to a SF internal staging area using the PUT command, the data is encrypted on the client's machine.

True

Warehouse cache size changes with warehouse size True False

True Note: The size of the cache is determined by the number of servers in the warehouse (i.e. the larger the warehouse and, therefore, the number of servers in the warehouse), the larger the cache. https://docs.snowflake.com/en/user-guide/warehouses-considerations.html#how-does-warehouse-caching-impact-queries

True or False: Micro-partitions are immutable?

True - subsequent changes of any type to the data will be written to additional micro-partitions

You are running your query in the worksheet using a large warehouse. You want to change the warehouse. How do you do that? Choose 2 answers USE WAREHOUSE ETL_WH; SET WAREHOUSE CONTEXT=ETL_WH Update the Warehouse field in the Context Menu located above the worksheet Go to the Worksheet properties page and set the Warehouse field to ETL_WH

USE WAREHOUSE ETL_WH; Update the Warehouse field in the Context Menu located above the worksheet

In snowflake, you want to compare whether two expressions are equal. The expressions can have null values. What is the best way to compare in this case Use EQUAL_NULL Run a query with WHERE EXPR1 = EXPR2 Run a query with WHERE EXPR2=EXPR1 See all questions BackNext question

Use EQUAL_NULL Note: This is an important concept to understand in Snowflake. This has caused me pains in production. Even we do not follow this, you will have incorrect data in production. EQUAL comparison operator (=), treats NULLs as unknown values. Like most SQL languages, comparing NULL = NULL does not return TRUE. In SnowFlake, it returns NULL EQUAL_NULL function is NULL-safe, meaning it treats NULLs as known values for comparing equality. https://docs.snowflake.com/en/sql-reference/functions/equal_null.html#equal-null

You want to identify the potential performance bottlenecks and improvement opportunities of a query. What will you do? Use Query Profile Use Explain plan Call snowflake support

Use Query Profile

You need to handle a huge number of concurrent users for your snowflake data warehouse. All of them are from the same team and needs same type of access. Which is the most appropriate warehouse strategy will you recommend? Create one warehouse for each user Create one warehouse for each group of user Use multi-cluster warehouse

Use multi-cluster warehouse

How did you validate the data after unloading it using COPY INTO command? Load the data in a relational table and validate the rows Load the data in a CSV and validate the rows Use validation_mode='RETURN_ROWS'

Use validation_mode='RETURN_ROWS' Note: Execute COPY in validation mode to return the result of a query and view the data that will be unloaded from the orderstiny table if COPY is executed in normal mode: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html#validating-data-to-be-unloaded-from-a-query

Semi structured data strings in snowflake are stored in a column with the below datatype VARIANT OBJECT VARCHAR NONE OF THE ABOVE

VARIANT

Your organization requires a snowflake instance that is a completely separate Snowflake environment, isolated from all other Snowflake accounts. Which edition will you suggest? Standard Premier Virtual Private Snowflake (VPS)

Virtual Private Snowflake (VPS)

In order to load data into snowflake, which of the below are required? Virtual Warehouse Predefined target table Staging location with data staged File format

Virtual Warehouse Predefined target table Staging location with data staged File format Note: Virtual warehouse is required to provide compute resources Target table must be pre-defined load does not have feature to auto create tables Data must be staged through the PUT command File format must be defined to indicate whether CSV, JSON, XML etc is being loaded

You ran a query in snowflake and went to query history tab. The query history shows you the below columns 1. QueryID 2. SQL TEXT 3. WAREHOUSE NAME 4. WAREHOUSE SIZE 5. SESSION ID 6. START TIME 7. END TIME Which of the above column will indicate if an compute cost was incurred to run the query? WAREHOUSE NAME CREDIT WAREHOUSE SIZE SESSION ID

WAREHOUSE SIZE Note: Anytime a query incurs compute cost, you will see the warehouse size mentioned as shown below Just fo an experiment as below 1. Run the query SHOW TABLES 2. Go to query history Do you see the ware house size? No, because SHOW TABLES is a metadata query and does not incur any compute cost

If you attempt to PUT a file but cannot because a file with the same name already exists in the stage, what can you do to load the file? Wait until the existing file's data is loaded, then retry PUT Rename the file that you want to PUT Set OVERWRITE = TRUE Set FORCE COPY = TRUE

Wait until the existing file's data is loaded, then retry PUT Rename the file that you want to PUT Set OVERWRITE = TRUE

Which cache type gets purged in a predictable way so that it ends up empty of all cached infromation? Warehouse Cache Results Cache Metadata Cache

Warehouse Cache Not: Physical storage that gets purged everytime WH is suspended

When creating a warehouse, what are the two most critical factors to consider from cost and performance persepective Warehouse size (i.e. number of servers per cluster) Manual vs automated management (for starting/resuming and suspending warehouses) Cloud provider selection

Warehouse size (i.e. number of servers per cluster) Manual vs automated management (for starting/resuming and suspending warehouses)

Resource monitor notifications can be received by account administrators through the following means Web interface Email Pager Mobile

Web interface Email

Cloud services also use compute. Does snowflake charge you for cloud services. Yes, if it exceeds 10% of the daily usage of the compute resources Yes, irrespective of how much compute resources are used Snowflake never charges for cloud services

Yes, if it exceeds 10% of the daily usage of the compute resources Note: Similar to virtual warehouse usage, Snowflake credits are used to pay for the usage of the cloud services that exceeds 10% of the daily usage of the compute resources. Usage for cloud-services is charged only if the daily consumption of cloud services exceeds 10% of the daily usage of the compute resources. The charge is calculated daily (in the UTC time zone). This ensures that the 10% adjustment is accurately applied each day, at the credit price for that day https://docs.snowflake.com/en/user-guide/credits.html#cloud-services-credit-usage

In which of the below scenarios, you will use an external table. You have data on the cloud providers's object store(AWS/GCS/AZURE Blob) but the data cannot be copied or moved to any other location due to compliance regulations You have high volume of data on the cloud providers's object store(AWS/GCS/AZURE Blob) but only a part of the data is needed in snowflake You have data on the cloud providers's object store(AWS/GCS/AZURE Blob) which needs to be updated by snowflake after applying required transformations You have XML data on cloud provider's object store

You have data on the cloud providers's object store(AWS/GCS/AZURE Blob) but the data cannot be copied or moved to any other location due to compliance regulations You have high volume of data on the cloud providers's object store(AWS/GCS/AZURE Blob) but only a part of the data is needed in snowflake Note: Please use elimination method to answer this questions 1. External tables are read-only, hence #3 is not correct 2. External tables do not support XML and hence #4 is not correct

What command will you run to pause a pipe? alter pipe <pipe name> set pipe_execution_paused = true; alter pipe <pipe name> set pipe_execution_paused = stop; alter pipe <pipe name> set pipe_execution_paused = halt;

alter pipe <pipe name> set pipe_execution_paused = true;

You have created a secure view view1. Now you want to convert it back to a regular view. Which of the below can be used for the conversion? alter view view1 set type = unsecure; alter view view1 unset secure; alter view view1 set secure; None of the above

alter view view1 unset secure; Note: https://docs.snowflake.com/en/sql-reference/sql/alter-view.html#examples

You created an warehouse(ETL_WH) which is sized at XSMALL, you want to resize the Warehouse to SMALL. How will you do that? alter warehouse etl_wh set warehouse_size=SMALL; You will need to recreate the warehouse with the new size Once created an warehouse can never be re-sized

alter warehouse etl_wh set warehouse_size=SMALL;

This snowpipe rest API Fetches a report about ingested files whose contents have been added to table loadHistoryScan and insertReport insertPipeReport insertFiles

loadHistoryScan and insertReport

How are virtual warehouse credits charged? per minute per second per-second, with a 60-second (i.e. 1-minute) minimum: per hour

per-second, with a 60-second (i.e. 1-minute) minimum:

You want to get the clustering information for a table T1 that is clustered on column C1. Which of the below queries will you run to get the details? select system$clustering_information('T1') select clustering('T1') from information_schema.clusteringinfo; Show clustering info for T1;

select system$clustering_information('T1')

Let's say that you have two JSONs as below 1. {"stuId":2000, "stuName":"Amy"} 2.{"stuId":2000,"stuCourse":"Snowflake"} How will you write a query that will check if stuId in JSON in #1 is also there in JSON in#2 with stu_demography as (select parse_json(column1) as src, src:stuId as ID from values('{"stuId":2000, "stuName":"Amy"}')), stu_course as (select parse_json(column1) as src, src:stuId as ID from values('{"stuId":2000,"stuCourse":"Snowflake"}')) select case when stdemo.ID in(select ID from stu_course) then 'True' else 'False' end as result from stu_demography stdemo; with stu_demography as (select parse_json(column1) as src, src['stuId'] as ID from values('{"stuId":2000, "stuName":"Amy"}')), stu_course as (select parse_json(column1) as src, src['stuId'] as ID from values('{"stuId":2000,"stuCourse":"Snowflake"}')) select case when stdemo.ID in(select ID from stu_course) then 'True' else 'False' end as result from stu_demography stdemo; SELECT CONTAINS('{"stuId":2000, "stuName":"Amy"}','{"stuId":2000,"stuCourse":"Snowflake"}'); with stu_demography as (select parse_json(column1) as src, src['STUID'] as ID from values('{"stuId":2000, "stuName":"Amy"}')), stu_course as (select parse_json(column1) as src, src['stuId'] as ID from values('{"stuId":2000,"stuCourse":"Snowflake"}')) select case when stdemo.ID in(select ID from stu_course) then 'True' else 'False' end as result from stu_demography stdemo; See all questions BackSkip question

with stu_demography as (select parse_json(column1) as src, src:stuId as ID from values('{"stuId":2000, "stuName":"Amy"}')), stu_course as (select parse_json(column1) as src, src:stuId as ID from values('{"stuId":2000,"stuCourse":"Snowflake"}')) select case when stdemo.ID in(select ID from stu_course) then 'True' else 'False' end as result from stu_demography stdemo; with stu_demography as (select parse_json(column1) as src, src['stuId'] as ID from values('{"stuId":2000, "stuName":"Amy"}')), stu_course as (select parse_json(column1) as src, src['stuId'] as ID from values('{"stuId":2000,"stuCourse":"Snowflake"}')) select case when stdemo.ID in(select ID from stu_course) then 'True' else 'False' end as result from stu_demography stdemo; Note: Please note that this may not be the way the question will appear in the certification exam, but why we are still learning this? 1. When you take this course, I prepare you not just for the certification but also to be prepared to work on a project 2. With this hands-on, what will you learn a. That there are two ways to query JSON, dot notation(src:stuId) and bracket notation src['stuId'] b. The JSON Value is case sensitive, if you run the 4th query with src['STUID'], it will return false because src['STUID'] will return you a NULL since there is a mismatch in the case. In the certification you may be asked question on a), b) - but do you agree if you learn this way you will never forget the concept:)

One of the government agencies has decided to use Snowflake for their datawarehouse. They require compliance with US federal privacy and security standards, such as FIPS 140-2and FedRAMP (Moderate Impact) . Which cloud providers can you choose to setup the snowflake instance? A) AWS B) AZURE C) GOOGLE D) IBM

A) AWS B) AZURE Note: https://docs.snowflake.com/en/user-guide/intro-regions.html#government-regions

What are the default roles that snowflake automatically adds for any new account? A) AccountAdmin B) SecurityAdmin C) SysAdmin D) Public E) Security Admin

A) AccountAdmin B) SecurityAdmin C) SysAdmin D) Public

What are the two ways available in snowflake before change tracking metadata is recorded for a table? A) Change tracking is enabled on the table (using ALTER TABLE ... CHANGE_TRACKING = TRUE) B) A stream is created for the table (using CREATE STREAM) C) Enable CDC on the table

A) Change tracking is enabled on the table (using ALTER TABLE ... CHANGE_TRACKING = TRUE) B) A stream is created for the table (using CREATE STREAM)

What would you check to see if a large table will benefit from explicitly defining a clustering key A) Clustering depth B) Clustering percentage C) Clustering ratio

A) Clustering depth

Which are the required parameters while creating a storage integration? A) Name B) Type C) Enabled D) Storage_Allowed_Locations E) Storage_blocked_locations

A) Name B) Type C) Enabled D) Storage_Allowed_Locations

Which factors affect your snowflake data loading rate? A) Physical location of the stage B) RAM on the virtual warehouse C) GZIP compression efficiency D) Thread size

A) Physical location of the stage C) GZIP compression efficiency Note: https://www.snowflake.com/blog/how-to-load-terabytes-into-snowflake-speeds-feeds-and-techniques/ Load rates for your own data files may differ based on a number of factors: Location of your S3 buckets - For our test, both our Snowflake deployment and S3 buckets were located in us-west-2 Number and types of columns - A larger number of columns may require more time relative to number of bytes in the files. Gzip Compression efficiency - More data read from S3 per uncompressed byte may lead to longer load times.

Snowflake supports below type of streams. Please select three A) Standard B) Update-only C) Append-only D) Insert-only

A) Standard C) Append-only D) Insert-only

Which Azure regions are supported by Snowflake in US and Canada A) west-us-2 B) east-us-2 C) canada-central D) west-us-1

A) west-us-2 B) east-us-2 C) canada-central

Which are true with respect to SMT(simple message transformation) when neither key.converter or value.converter is set All SMTs are supported Most SMTs are supported regex.router is not supported

Most SMTs are supported regex.router is not supported

The RECORD_METADATA contains which information Topic Partition Key CreateTime / LogAppendTime Value

Topic Partition Key CreateTime / LogAppendTime

SQL functionality in snowflake can be extended by USER DEFINED FUNCTIONS USING SQL USER DEFINED FUNCTIONS USING JAVASCRIPT SESSION VARIABLES NONE OF THE ABOVE

USER DEFINED FUNCTIONS USING SQL USER DEFINED FUNCTIONS USING JAVASCRIPT SESSION VARIABLES

How will you store JSON data in snowflake A) Using a column with datatype as JSON B) Using a column with datatype as VARCHAR C) Using a column with datatype as VARIANT

SnowPro Certification

Related study sets

Public Speaking Midterm

Marketing Strategy Components

Chapter 1 Review Questions

Ch. 50 Prep U Patients With Hearing and Balance Disorders

ANTH 202 - Final

Practice Questions

Chp 12 - Employee Benefits: Group Life and Health Insurance

Chapter 21: PrepU - Complications Occurring Before Labor and Delivery

Peds ACLS

PSYC-3113 Exam 1 Practice

CIS-345 chapter4

Examen

CISSP Glossary

Marketing & Professional Sales Unit 3 Test Review

Introduction to project management week1

Systems Thinking 14

Income

ISP Exam 1

Social Media Marketing

Microbiology assignment 4