Mock Exam 2 Flashcards
What is the minimum Snowflake edition that supports Database replication between Snowflake accounts (within an organization)?
Standard
Database replication is supported in all Snowflake editions; thus, the minimum edition that supports it is the Standard edition.
https://docs.snowflake.com/en/user-guide/intro-editions.html
Temporary tables can be cloned to permanent tables?
Temporary tables can NOT be cloned to a permanent table.
Doing so will typically show the following error “Temp table cannot be cloned to a permanent table; clone to a transient table instead.”
However, a temporary table may be cloned to a transient table or another temporary table.
Which of the following roles can import a dataset from Snowflake Marketplace?
ACCOUNTADMIN or SECURITYADMIN?
Although any user or role can explore the Snowflake Marketplace, you need a user with the ACCOUNTADMIN privilege or the IMPORT SHARE privilege for consuming data.
For simplicity, we suggest you utilize a user with the ACCOUNTADMIN privilege.
Can you CAST or TRUNCATE during loading data using COPY command?
During the load process, the COPY command allows for modifying the order of columns, omitting one or more columns, casting data into specified data types, and truncating values
Complex transformations such as joins, filters, aggregations, and the use of FLATTEN are not supported as they are not essential data transformations. Therefore, joining, filtering, and aggregating the data are supported ONLY after the data has been loaded into a table.
https://docs.snowflake.com/en/user-guide/data-load-overview#id2
Can Snowpipe load data directly from Internal Stages and Snowflake Tables?
No.
Snowpipe can load data from an external stage as well as an internal stage.When using an external stage, you can use the cloud platform notifications to trigger your Snowpipe.
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro
True or False
Automatic Clustering Service is responsible for Managing multi-cluster virtual warehouses.
False
It is for Redistributing data in micro-partitions according to the clustering key.
Automatic Clustering only adjusts those micro-partitions which benefit from the re-clustering process.
https://docs.snowflake.com/en/user-guide/tables-auto-reclustering
True or False
The default privileges provided to the built-in roles can NOT be revoked.
True
The built-in system-defined roles cannot be dropped. The default privileges granted to those roles cannot be revoked.
https://docs.snowflake.com/en/user-guide/security-access-control-overview#roles
Is Operator tree shown in the Statistics box in the Query Profile?
No.
https://docs.snowflake.com/en/user-guide/ui-query-profile#statistics
True or False
Snowflake guarantees that files are loaded in the order they arrived.
False
Each time data is loaded, metadata is created, called load metadata.
By utilizing this load metadata, Snowflake ensures that it will not reprocess a previously loaded file. Snowflake doesn’t ensure that the files are loaded in the order they arrived.
https://docs.snowflake.com/en/user-guide/data-load-considerations-load
What is the MAXIMUM compute size that Snowflake may allocate to a serverless task?
2X-Large
This sizing is determined dynamically by analyzing recent statistics of similar task executions to optimize performance. The largest compute size available for serverless tasks is equivalent to the capacity of an XXLARGE or 2X-Large user-managed virtual warehouse.
https://docs.snowflake.com/en/user-guide/tasks-intro#serverless-tasks
True or False
When loading data through COPY command, it is required that your table and the file from where the data is being loaded should have the same number of columns.
False
a SELECT statement can be used to select only the required columns from the stage. When loading data into a table using the COPY command, Snowflake allows you to do simple transformations on the data as it is being loaded by using a SELECT statement. During the load process, the COPY command allows for modifying the order of columns, omitting one or more columns, and casting data into specified data types. It is also possible to truncate data using the COPY command if it is larger than the desired column width.
https://docs.snowflake.com/en/user-guide/data-load-overview#simple-transformations-during-a-load
True or False
External Tokenization provides Row-level security
FALSE
Snowflake supports masking policies that may be applied to columns and enforced at the column level to provide column-level security. Column-level security is achieved by dynamic data masking or external Tokenization.
https://docs.snowflake.com/en/user-guide/security-column
True or False
When defining a clustering key, you should choose columns that have very low cardinality.
FALSE
When defining clustering keys, the initial candidate clustering columns are those columns that are frequently used in the WHERE clause or other selective filters.
Additionally, columns that are used for joining can also be considered.
Furthermore, the columns’ cardinality (number of distinct values) is also important. It is crucial to choose a column with a high enough cardinality to allow effective partition pruning while having a low enough cardinality for Snowflake to group data into micro-partitions efficiently. A column with too few distinct values (e.g., gender) will result in minimal partition pruning. On the other hand, a column that has too many distinct values (e.g., customer id) will result in too much overhead when maintaining the partitions.
Also, when creating a multi-column cluster key, order the columns from the lowest cardinality to the highest cardinality; otherwise, the effectiveness of clustering will be reduced.
https://docs.snowflake.com/en/user-guide/tables-clustering-keys
True or False:
When exporting data using the COPY command, the exported file(s) are automatically compressed.
TRUE
When data is unloaded from Snowflake, it is automatically compressed using gzip compression. This is the default behavior; however, you can specify alternate compression methods or turn off compression entirely.
The default size of each output file is 16 MB but can be changed using the MAX_FILE_SIZE parameter. The maximum allowed size per file is 5GB if you export data to cloud storage.
https://docs.snowflake.com/en/user-guide/data-unload-considerations#unloading-to-a-single-file
True or False
A consumer of a shared database can add new tables or views to the shared database.
FALSE
Shared objects are read-only for the consumer and cannot be modified by the consumer.
A database created on Share contains the tables and other objects that the data provider added, but the consumer cannot add additional objects.
