A Snowflake Administrator needs to persist all virtual warehouse configurations for auditing and backups. Given a table already exists with the following schema:Table Name : VWH_METAColumn 1 : SNAPSHOT_TIME TIMESTAMP_NTZColumn 2 : CONFIG VARIANTWhich commands should be executed to persist the warehouse data at the time of execution in JSON format in the table VWH META?
Answer(s): C
According to the Using Persisted Query Results documentation, the RESULT_SCAN function allows you to query the result set of a previous command as if it were a table. The LAST_QUERY_ID function returns the query ID of the most recent statement executed in the current session. Therefore, the combination of these two functions can be used to access the output of the SHOW WAREHOUSES command, which returns the configurations of all the virtual warehouses in the account. However, to persist the warehouse data in JSON format in the table VWH_META, the OBJECT_CONSTRUCT function is needed to convert the output of the SHOW WAREHOUSES command into a VARIANT column. The OBJECT_CONSTRUCT function takes a list of key-value pairs and returns a single JSON object. Therefore, the correct commands to execute are:1. SHOW WAREHOUSES;2. INSERT INTO VWH_META SELECT CURRENT_TIMESTAMP (), OBJECT_CONSTRUCT (*) FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ()));The other options are incorrect because:· A. This option does not use the OBJECT_CONSTRUCT function, so it will not persist the warehouse data in JSON format. Also, it is missing the * symbol in the SELECT clause, so it will not select any columns from the result set of the SHOW WAREHOUSES command. · B. This option does not use the OBJECT_CONSTRUCT function, so it will not persist the warehouse data in JSON format. It will also try to insert multiple columns into a single VARIANT column, which will cause a type mismatch error.· D. This option does not use the OBJECT_CONSTRUCT function, so it will not persist the warehouse data in JSON format. It will also try to use the RESULT_SCAN function on a subquery, which is not supported. The RESULT_SCAN function can only be used on a query ID or a table name.
What are the requirements when creating a new account within an organization in Snowflake? (Select TWO).
Answer(s): C,E
According to the CREATE ACCOUNT documentation, the account name must be specified when the account is created, and it must be unique within an organization, regardless of which Snowflake Region the account is in. The other options are incorrect because:· The account does not require at least one ORGADMIN role within one of the organization's accounts. The account can be created by an organization administrator (i.e. a user with the ORGADMIN role) through the web interface or using SQL, but the new account does not inherit the ORGADMIN role from the existing account. The new account will have its own set of users, roles, databases, and warehouses.· The account name is not immutable and can be changed. The account name can be modified by contacting Snowflake Support and requesting a name change. However, changing the account name may affect some features that depend on the account name, such as SSO or SCIM. · The account name does not need to be unique among all Snowflake customers. The account name only needs to be unique within the organization, as the account URL also includes the region and cloud platform information. For example, two accounts with the same name can exist in different regions or cloud platforms, such as myaccount.us-east-1.snowflakecomputing.com and myaccount.eu-west-1.aws.snowflakecomputing.com.
A Snowflake customer is experiencing higher costs than anticipated while migrating their data warehouse workloads from on-premises to Snowflake. The migration workloads have been deployed on a single warehouse and are characterized by a large number of small INSERTs rather than bulk loading of large extracts. That single warehouse has been configured as a single cluster, 2XL because there are many parallel INSERTs that are scheduled during nightly loads.How can the Administrator reduce the costs, while minimizing the overall load times, for migrating data warehouse history?
According to the Snowflake Warehouse Cost Optimization blog post, one of the strategies to reduce the cost of running a warehouse is to use a multi-cluster warehouse with auto-scaling enabled. This allows the warehouse to automatically adjust the number of clusters based on the concurrency demand and the queue size. A multi-cluster warehouse can also be configured with a minimum and maximum number of clusters, as well as a scaling policy to control the scaling behavior. This way, the warehouse can handle the parallel load queries efficiently without wasting resources or credits. The blog post also suggests using a smaller warehouse size, such as SMALL or XSMALL, for loading data, as it can perform better than a larger warehouse size for small INSERTs. Therefore, the best option to reduce the costs while minimizing the overall load times for migrating data warehouse history is to keep the warehouse as a SMALL or XSMALL and configure it as a multi-cluster warehouse to handle the parallel load queries. The other options are incorrect because:· A. Deploying another 2XL warehouse to handle a portion of the load queries will not reduce the costs, but increase them. It will also introduce complexity and potential inconsistency in managing the data loading process across multiple warehouses. · B. Changing the 2XL warehouse to 4XL will not reduce the costs, but increase them. It will also provide more compute resources than needed for small INSERTs, which are not CPU-intensive but I/O-intensive.· D. Converting the INSERTs to several tables will not reduce the costs, but increase them. It will also create unnecessary data duplication and fragmentation, which will affect the query performance and data quality.
What roles or security privileges will allow a consumer account to request and get data from the Data Exchange? (Select TWO).
Answer(s): C,D
According to the Accessing a Data Exchange documentation, a consumer account can request and get data from the Data Exchange using either the ACCOUNTADMIN role or a role with the IMPORT SHARE and CREATE DATABASE privileges. The ACCOUNTADMIN role is the top-level role that has all privileges on all objects in the account, including the ability to request and get data from the Data Exchange. A role with the IMPORT SHARE and CREATE DATABASE privileges can also request and get data from the Data Exchange, as these are the minimum privileges required to create a database from a share. The other options are incorrect because:· A. The SYSADMIN role does not have the privilege to request and get data from the Data Exchange, unless it is also granted the IMPORT SHARE and CREATE DATABASE privileges. The SYSADMIN role is a pre-defined role that has all privileges on all objects in the account, except for the privileges reserved for the ACCOUNTADMIN role, such as managing users, roles, and shares. · B. The SECURITYADMIN role does not have the privilege to request and get data from the Data Exchange, unless it is also granted the IMPORT SHARE and CREATE DATABASE privileges. The SECURITYADMIN role is a pre-defined role that has the privilege to manage security objects in the account, such as network policies, encryption keys, and security integrations, but not data objects,such as databases, schemas, and tables.· E. The IMPORT PRIVILEGES and SHARED DATABASE are not valid privileges in Snowflake. The correct privilege names are IMPORT SHARE and CREATE DATABASE, as explained above.
An Administrator wants to delegate the administration of a company's data exchange to users who do not have access to the ACCOUNTADMIN role.How can this requirement be met?
Answer(s): B
According to the [GRANT MODIFY] documentation, the MODIFY privilege on a data exchange allows a role to perform administrative tasks on the data exchange, such as inviting members, approving profiles, and reviewing listings. This privilege can be granted by the ACCOUNTADMIN role or a role that already has the MODIFY privilege on the data exchange. Therefore, to delegate the administration of a company's data exchange to users who do not have access to the ACCOUNTADMIN role, the best option is to grant the MODIFY privilege on the data exchange to a role that the users can assume. The other options are incorrect because:· A. There is no such privilege as IMPORTED PRIVILEGES in Snowflake. The correct privilege name is IMPORT SHARE, which allows a role to create a database from a share. This privilege is not related to the administration of a data exchange, but to the consumption of shared data. · C. There is no such privilege as OWNERSHIP in Snowflake. The correct privilege name is OWNED BY, which indicates the role that owns an object and has full control over it. However, this privilege cannot be granted or revoked, but only transferred by the current owner to another role using the GRANT OWNERSHIP command. Therefore, this option is not feasible for delegating the administration of a data exchange.· D. The USAGE privilege on a data exchange allows a role to access the data exchange and view the available data listings. This privilege does not allow a role to perform administrative tasks on the data exchange, such as inviting members, approving profiles, and reviewing listings. Therefore, this option is not sufficient for delegating the administration of a data exchange.
Post your Comments and Discuss Snowflake SnowPro Advanced Administrator exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the SnowPro Advanced Administrator content, but please register or login to continue.