Which columns can be included in an external table schema? (Select THREE).
Correct Answer: A,E,F
Question 137
A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below: Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER BY clauses. Whereas columns C1, C2 and C3 are heavily used in filter and join conditions of SELECT queries. The Architect must design a clustering key for this table to improve the query performance. Based on Snowflake recommendations, how should the clustering key columns be ordered while defining the multi-column clustering key?
Correct Answer: C
According to the Snowflake documentation, the following are some considerations for choosing clustering for a table1: Clustering is optimal when either: You require the fastest possible response times, regardless of cost. Your improved query performance offsets the credits required to cluster and maintain the table. Clustering is most effective when the clustering key is used in the following types of query predicates: Filter predicates (e.g. WHERE clauses) Join predicates (e.g. ON clauses) Grouping predicates (e.g. GROUP BY clauses) Sorting predicates (e.g. ORDER BY clauses) Clustering is less effective when the clustering key is not used in any of the above query predicates, or when the clustering key is used in a predicate that requires a function or expression to be applied to the key (e.g. DATE_TRUNC, TO_CHAR, etc.). For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key. Adding more than 3-4 columns tends to increase costs more than benefits. Based on these considerations, the best option for the clustering key columns is C. C1, C3, C2, because: These columns are heavily used in filter and join conditions of SELECT queries, which are the most effective types of predicates for clustering. These columns have high cardinality, which means they have many distinct values and can help reduce the clustering skew and improve the compression ratio. These columns are likely to be correlated with each other, which means they can help co-locate similar rows in the same micro-partitions and improve the scan efficiency. These columns do not require any functions or expressions to be applied to them, which means they can be directly used in the predicates without affecting the clustering.
Question 138
You will be using a multi cluster warehouse. You will statically control the available resources (i.e. servers) and you have large numbers of concurrent user sessions and/or queries and the numbers do not fluctuate significantly. Which mode will you use for the warehouse?
Correct Answer: B
Question 139
How can the Snowpipe REST API be used to keep a log of data load history?
Correct Answer: D
The Snowpipe REST API provides two endpoints for retrieving the data load history: insertReport and loadHistoryScan. The insertReport endpoint returns the status of the files that were submitted to the insertFiles endpoint, while the loadHistoryScan endpoint returns the history of the files that were actually loaded into the table by Snowpipe. To keep a log of data load history, it is recommended to use the loadHistoryScan endpoint, which provides more accurate and complete information about the data ingestion process. The loadHistoryScan endpoint accepts a start time and an end time as parameters, and returns the files that were loaded within that time range. The maximum time range that can be specified is 15 minutes, and the maximum number of files that can be returned is 10,000. Therefore, to keep a log of data load history, the best option is to call the loadHistoryScan endpoint every 10 minutes for a 15-minute time range, and store the results in a log file or a table. This way, the log will capture all the files that were loaded by Snowpipe, and avoid any gaps or overlaps in the time range. The other options are incorrect because: * Calling insertReport every 20 minutes, fetching the last 10,000 entries, will not provide a complete log of data load history, as some files may be missed or duplicated due to the asynchronous nature of Snowpipe. Moreover, insertReport only returns the status of the files that were submitted, not the files that were loaded. * Calling loadHistoryScan every minute for the maximum time range will result in too many API calls and unnecessary overhead, as the same files will be returned multiple times. Moreover, the maximum time range is 15 minutes, not 1 minute. * Calling insertReport every 8 minutes for a 10-minute time range will suffer from the same problems as option A, and also create gaps or overlaps in the time range. References: * Snowpipe REST API * Option 1: Loading Data Using the Snowpipe REST API * PIPE_USAGE_HISTORY
Question 140
How does a standard virtual warehouse policy work in Snowflake?
Correct Answer: D
A standard virtual warehouse policy is one of the two scaling policies available for multi-cluster warehouses in Snowflake. The other policy is economic. A standard policy aims to prevent or minimize queuing by starting additional clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This policy can improve query performance and concurrency, but it may also consume more credits than an economic policy, which tries to conserve credits by keeping the running clusters fully loaded before starting additional clusters. The scaling policy can be set when creating or modifying a warehouse, and it can be changed at any time. References: * Snowflake Documentation: Multi-cluster Warehouses * Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses
