Download Snowflake Certification Practice Questions and Answers and more Exams Computer Science in PDF only on Docsity!
Tackling Snowflake Cert With 305
Questions
When a temporary "Internal" Stage is dropped then .. - ✔ ✔ All files in the stage are removed When a temporary "External" Stage is dropped then .. - ✔ ✔ None of the files in the stage are removed In Snowflake the Boolean data type can store Unknown in addition to True / False - ✔ ✔ Correct The BINARY data type can store what amount of data? - ✔ ✔ 8MB Data in a Snowflake table is automatically encrypted by Snowflake using ...? - ✔ ✔ AES-256 strong encryption Unloading of data from Snowflake requires a running virtual warehouse. - ✔ ✔ True Snowflake supports which of the following types of stages? - ✔ ✔ External stage; Internal stage When converting a character value to Boolean, which of the following values will result in "FALSE". - ✔ ✔ 'n'; 'no'; '0' 'f'
'off' 'false' When converting a numeric value to Boolean, which of the following values will result in "TRUE". - ✔ ✔ 1; -1; 2; Anything but zero For efficient & parallelized loading of data, Snowflake recommends which of the following - ✔ ✔ Files should be 10-100 MB in size (compressed); Split large files into smaller files In Snowflake all data at rest is always encrypted. - ✔ ✔ True If data is loaded into an Internal Snowflake Stage it will be automatically encrypted. - ✔ ✔ True Select the correct statement about Snowflake external tables. - ✔ ✔ Querying data in an external table is slower than querying a native Snowflake table What are the valid properties of the VARIANT data type? - ✔ ✔ Any data type can be cast to the VARIANT data type; VARIANT data type can store up to 16MB of compressed data
Which one of the following cannot be cloned? - ✔ ✔ Internal named stage You are a solution's architect for a large retail company running a Snowflake data warehouse. Your Snowflake implementation has just gone live with a single virtual Warehouse and has been opened up to all users across the pension. One of your heaviest users ate the Finance department, who have a large number of users and execute a large number of queries. The finance department has complained that the queries take a long time to execute for them. What is the best immediate action should you take to improve their experience? - ✔ ✔ Introduce a dedicated virtual Warehouse instance for the finance department and size according to their needs. Set the new virtual Warehouse to auto suspend and auto resume. What are the resources that a virtual Warehouse provides? - ✔ ✔ Memory; Temporary Storage; CPU Snowflake uses a unique architecture in which data and compute has been decoupled and both be scaled independently of each other - ✔ ✔ Yes The "load metadata" (which maintains which files have been already been loaded) for a table expertise assure how many days? - ✔ ✔ 64 What is the correct way to find out virtual Warehouse credit usage information in Snowflake? - ✔ ✔ Query WAREHOUSE_METERING_HISTORY in the Information Schema;
Go to Account -> Billing & Usage in the WebUI; Query WAREHOUSE_METERING_HISTORY in Account Usage Schema Which minimum Snowflake license provides up to 90 days of time travel? - ✔ ✔ Enterprise You can increase or decrease the size of a virtual Warehouse as your user base & query demand increase or decrease? - ✔ ✔ True Snowflake uses a staged release process for new release in which different account types are upgraded gradually. - ✔ ✔ True Both time travel and fail safe require extra storage which has cost associated with it. - ✔ ✔ True Snowflake deploy new release at what frequency? - ✔ ✔ Every week Snowflake database is based on the massively parallel shared nothing Architecture used by databases like Teradata & Greenplum. - ✔ ✔ False What method does Snowflake use to limit the number of micro- partitions accessed during a query? - ✔ ✔ Pruning What is the lowest Snowflake edition that allows customer dedicated virtual Warehouse? - ✔ ✔ Standard A consumer of a shared database can add new tables or viewed in the shared database? - ✔ ✔ False, shared objects are read-only Which role is most powerful role in a Snowflake system? - ✔ ✔ ACCOUNTADMIN
Snowflake starts minimum required clusters in the multi-cluster virtual warehouse that can accommodate the number of users & number of queries; If the demand is decreased the number of clusters are gradually shutdown, up to the defined minimum cluster value When a table is cloned, a snapshot of the data is taken at the time of the cloning and represents the state of the source data at the time of cloning. - ✔ ✔ True Which type of short lived Snowflake tables will continue to exist even if the session is closed? - ✔ ✔ Transient Cloning a database will clone which of the following - ✔ ✔ The database itself; All the schemas in the database; All tables with in every schema in that database Which type of Snowflake table do not have a failsafe? - ✔ ✔ Transient; Temporary Which of the following is required in order for a role to be able to successfully execute a Task? - ✔ ✔ The role should have the global EXECUTE TASK privilege on the Account; The role should have the permissions required to run the SQL statement executed in the Task
Snowflake automatically determines the most efficient algorithm to compress columns in a micro partitions - ✔ ✔ True When a virtual warehouse is resumed from suspended state what is the minimum amount of Snowflake credits that it will immediately consume? - ✔ ✔ 1 minute Snowflake SQL UDFs can return either Scalar or Tabular results? - ✔ ✔ True When a data provider stops sharing an object, that object immediately becomes unavailable to all its consumers. - ✔ ✔ True Multifactor Authentication can be enabled for which of the following?
Select all that Apply? - ✔ ✔ JDBC; Snowflake WebUI; SnowSQL The default table type for new tables in Snowflake is.. - ✔ ✔ Permanent On the WebUI which button on the top bar should you select to get to tabs that provide information about storage usage? - ✔ ✔ Account Which minimum Snowflake license provides the failsafe feature? - ✔ ✔ Standard In a multi cluster virtual warehouse, if you set different values for minimum cluster count and the maximum cluster count - ✔ ✔
Cast Columns; Omit Columns Which of the following statement is True regarding how Snowflake stores its data. - ✔ ✔ Snowflake uses it own proprietary columnar format to store table data. Which minimum Snowflake license allows data sharing capability? - ✔ ✔ Standard Assume a virtual warehouse of size X-Large(128 servers) running for an hour. What is the amount of Snowflake credits that will be billed? - ✔ ✔ 128 On the Snowflake Web UI, which of the following are buttons in the top bar? Select all that apply - ✔ ✔ Shares; Databases In the Snowflake staged release process for new releases, which account types are applied updates the LAST? - ✔ ✔ Enterprise & Higher Accounts To create a new Task a user should be using a role which.. - ✔ ✔ has the CREATE TASK privilege on the schema where the Task is being created You are planning to utilize the multi-cluster virtual warehouse to provide auto-scaling and performance for your users. You want to make sure that the user queries don't wait in queue and if auto-scaling is required the cluster should auto scale immediately. What is the correct
value for Scaling Policy to achieve this? - ✔ ✔ SCALING_POLICY = STANDARD You increased the size of a virtual warehouse to improve performance of a query? What is this an example of? - ✔ ✔ Scaling Up Which of the following objects can be cloned? Select all that apply. - ✔ ✔ Schema; Database; Table To create a SHARE what is the minimum required role? - ✔ ✔ ACCOUNTADMIN Snowflake patch releases are applied to all accounts at the same time. - ✔ ✔ True Which of the following are valid Casting function available in Snowflake? - ✔ ✔ TO_NUMERIC; TO_VARIANT; TO_NUMBER; TO_TIMEATAMP If a multi cluster virtual warehouse is resized the new size applies to? - ✔ ✔ All the running clusters & any running clusters that are started after the multi cluster virtual warehouse is resized You are planning to utilize the multi-cluster virtual warehouse to provide auto-scaling and performance for your users. You want to make sure that the auto scaling doesn't start clusters immediately and is cost
What is the maximum time travel period allowed for transient & temporary tables? - ✔ ✔ 1 Day Which of the following will allow a user in consumer account to create a database from the share. Select two. - ✔ ✔ ACCOUNTADMIN role; IMPORT SHARE privileges When a database or a schema is cloned, which of the following statements are true for the stages in that database? - ✔ ✔ External named stages are cloned; named internal stages are NOT cloned; Table internal stages are cloned How are the columns stored in a Snowflake micro partition? (Also referred to as Columnar storage) - ✔ ✔ Independently - each column stored on it own What are some of the ways to improve performance in Snowflake? Select all that apply - ✔ ✔ Clustering Keys; Multi Clustering Virtual Warehouse; Dedicated Virtual Warehouse; Query Result Caching The COPY command in a Snowpipe definition supports the same transformation as provided by the usual COPY command. - ✔ ✔ True
If you used a specific virtual warehouse to load data into a table, you must use the same virtual warehouse to query that data. - ✔ ✔ False You are required to run a series of SQL statements on a scheduled interval. The SQL statements must execute in a defined order. What is the best way to achieve this in Snowflake? - ✔ ✔ 1. Create multiple Tasks each containing one SQL statement.
- Connect the Tasks in a parent-child relationship to define the execution order.
- Schedule the root Task A virtual warehouse can be set to auto-suspend or auto-resume but not both. - ✔ ✔ False What is the Snowflake recommended approach to identifying the minimum and maximum values for a multi-cluster virtual warehouse? Select all that apply - ✔ ✔ Start with a minimum of 1 & a maximum cluster value of 2 or 3; Track the virtual warehouse usage over time and adjust the minimum and maximum values that provide the best configuration for your workload; Start with the multi-cluster virtual warehouse in auto scale mode Under what circumstances results for a query will be fulfilled by the query result cache? Select all that apply - ✔ ✔ The query being run is syntactically identical to a previously executed query; The underlying data for the tables in the query has not changed;
AUTO_RESUME;
MAX_CLUSTER_COUNT;
Which of the following semi-structured file formats are supported by Snowflake? Select all that apply - ✔ ✔ PARQUET; XML; AVRO; ORC; JSON You are designing a snowflake based solution for a large retail chain. What should you consider for ensuring performance once the Snowflake based data warehouse goes live? Select all that apply - ✔ ✔ Consider using multi-clustering to auto spawn & auto shutdown virtual warehouse instances to cater for increase & decrease in user demand; Consider using dedicated virtual warehouses for different departments REST API calls can be used to trigger a Snowpipe regardless of whether it is defined over an Internal Stage or an External Stage - ✔ ✔ True Please select the 3 key services which are part of the Snowflake Architecture - ✔ ✔ Cloud Services Layer; Query Processing Layer; Database Storage Layer
Each snowflake account is limited to a maximum of 10 shares. - ✔ ✔ False If you have multiple virtual warehouses in your Snowflake system, they will each be accessing the same shared data. - ✔ ✔ True Using Snowpipe customers can load data continuously from which of the following? - ✔ ✔ External Stages; Internal Stages All queries will perform better if the virtual warehouse size is increased? - ✔ ✔ False You are the Snowflake Administrator for a large telecom company. You had a Medium sized virtual warehouse on which a complex query was executing, you change the virtual warehouse size to be Small. Will the query be impacted and perform slower? - ✔ ✔ No. The currently executing query will keep on executing on the original cluster. Only new queries will execute on the Small sized cluster Which function is used to find the query id of the last executed query? - ✔ ✔ SELECT last_query_id() Which of the following actions can not be performed by the consumer of a shared database? - ✔ ✔ Create a new table in the shared database; Insert data in a shared table Which of the following objects can be shared? Select all that apply. - ✔ ✔ Secure UDFs;
Please select the correct options that can be used to bring semi structured data into Snowflake. - ✔ ✔ Load semi structured data into VARIANT column; Transform the data while using the COPY command Snowflake COPY command provides the capability to validate the data inside a file, without actually loading it. - ✔ ✔ True The maintenance & tuning and upgrade of Snowflake is performed by the Snowflake (company) without requiring any action from the customers - ✔ ✔ True A virtual warehouse can be resized any time, regardless if it is in suspended state or active and executing queries. - ✔ ✔ True A snowflake user can see the query results of a query executed by another user. - ✔ ✔ False When a new user is created, multi factor authentication (MFA) is automatically enabled for that user by default - ✔ ✔ False A share must have at least one consumer added to it - ✔ ✔ False, a share can be defined without a consumer added to it. Can be added later. What happens when a virtual warehouse is resized to a LARGER size? - ✔ ✔ The new server(s) will execute queries that are in queue; The already executing queries are not shifted to the new servers(s)
You have shared a table with another Snowflake account. A user in the consumer account has executed a query on the shared table. Who will be charged for the query cost? - ✔ ✔ Consumer The size of your Snowflake based data warehouse is virtually unlimited because it uses cloud storage as the underlying storage mechanism. - ✔ ✔ Yes Snowflake technology has been build from scratch, specifically designed for execution on cloud platforms. - ✔ ✔ YEs Which of the following is true regarding Scaling virtual warehouses? - ✔ ✔ The performance of complex queries can be improved by increasing the size of a virtual warehouse; An increased number of queries & users can be accommodated by adding additional clusters to a multi-cluster virtual warehouse Data protected by Failsafe can be recovered by? - ✔ ✔ The Snowflake support You can not increase the size of a virtual warehouse if one or more queries are executing on that virtual warehouse. - ✔ ✔ False Snowflake maintains the partitions using the order of the data in which it is inserted - ✔ ✔ True A cloned object doesn't contribute to the over all storage unless.. - ✔ ✔ Operations that modify data are performed on the cloned e.g. update, insert, or delete data; New populated tables are created in one of the cloned schemas
