Free SnowPro Advanced Architect Sample Questions — SnowPro Advanced Architect

An Architect is investigating a performance issue with an existing architecture and found a query where the Query Profile indicates a high number of bytes spilled to remote storage. The query includes a where clause that filters out a high percentage of records. What actions can be taken to reduce the spillage to remote storage and improve the query performance? (Choose two.)

• A. Increase the size of the virtual warehouse
• B. Increase the maximum number of clusters for the virtual warehouse
• C. Define a clustering key on columns used for selective filtering with a high clustering depth
• D. Ensure primary keys and foreign keys are defined for large tables referenced by the query
• E. Decrease the data retention period for tables referenced by the query

A company needs to share its product catalog data with one of its partners. The product catalog data is stored in two database tables: PRODUCT_CATEGORY, and PRODUCT_DETAILS. Both tables can be joined by the PRODUCT_ID column. Data access should be governed, and only the partner should have access to the records. The partner is not a Snowflake customer. The partner uses Amazon S3 for cloud storage. Which design will be the MOST cost-effective and secure, while using the required Snowflake features?

• A. Use Secure Data Sharing with an S3 bucket as a destination
• B. Publish PRODUCT_CATEGORY and PRODUCT_DETAILS data sets on the Snowflake Marketplace
• C. Create a database user for the partner and give them access to the required data sets
• D. Create a reader account for the partner and share the data sets as secure views

What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)

• A. The MERGE command
• B. The UPSERT command
• C. The CHANGES clause
• D. STREAM object
• E. Thee CHANGE_DATA_CAPTURE command

A healthcare company wants to share data with a medical institute. The institute is running a Standard edition of Snowflake; the healthcare company is running a Business Critical edition. How can this data be shared?

• A. The healthcare company will need to change the institute’s Snowflake edition in the accounts panel
• B. By default, sharing is supported from a Business Critical Snowflake edition to a Standard edition
• C. Contact Snowflake and they will execute the share request for the healthcare company
• D. Set the share_restriction parameter on the shared object to false

A Snowflake Architect created a new data share and would like to verify that only specific records in secure views are visible within the data share by the consumers. What is the recommended way to validate data accessibility by the consumers?

• A. Create reader accounts as shown below and impersonate the consumers by logging in with their credentials. create managed account reader_acct1 admin_name = user1 , admin_password = 'Sdfed43da!44' , type = reader;
• B. Create a row access policy as shown below and assign it to the data share. create or replace row access policy rap_acct as (acct_id varchar) returns boolean -> case when 'acct1_role' = current_role() then true else false end;
• C. Set the session parameter called SIMULATED_DATA_SHARING_CONSUMER as shown below in order to impersonate the consumer accounts. alter session set simulated_data_sharing_consumer = 'Consumer Acct1'
• D. Alter the share settings as shown below, in order to impersonate a specific consumer account. alter share sales_share set accounts = 'Consumer1' share_restrictions = true

An Architect is designing a pipeline to stream event data into Snowflake using the Snowflake Kafka connector. The Architect’s highest priority is to configure the connector to stream data in the MOST cost-effective manner. Which of the following is recommended for optimizing the cost associated with the Snowflake Kafka connector?

• A. Utilize a higher Buffer.flush.time in the connector configuration
• B. Utilize a higher Buffer.size.bytes in the connector configuration
• C. Utilize a lower Buffer.size.bytes in the connector configuration
• D. Utilize a lower Buffer.count.records in the connector configuration

An Architect clones a database and all of its objects, including tasks. After the cloning, the tasks stop running. Why is this occurring?

• A. Tasks cannot be cloned
• B. The objects that the tasks reference are not fully qualified
• C. Cloned tasks are suspended by default and must be manually resumed
• D. The Architect has insufficient privileges to alter tasks on the cloned database

An Architect is troubleshooting a query with poor performance using the QUERY_HISTORY function. The Architect observes that the COMPILATION_TIME is greater than the EXECUTION_TIME. What is the reason for this?

• A. The query is processing a very large dataset
• B. The query has overly complex logic
• C. The query is queued for execution
• D. The query is reading from remote storage

A company’s daily Snowflake workload consists of a huge number of concurrent queries triggered between 9pm and 11pm. At the individual level, these queries are smaller statements that get completed within a short time period. What configuration can the company’s Architect implement to enhance the performance of this workload? (Choose two.)

• A. Enable a multi-clustered virtual warehouse in maximized mode during the workload duration
• B. Set the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level
• C. Increase the size of the virtual warehouse to size X-Large
• D. Reduce the amount of data that is being processed through this workload
• E. Set the connection timeout to a higher value than its default

There are two databases in an account, named fin_db and hr_db which contain payroll and employee data, respectively. Accountants and Analysts in the company require different permissions on the objects in these databases to perform their jobs. Accountants need read-write access to fin_db but only require read-only access to hr_db because the database is maintained by human resources personnel. An Architect needs to create a read-only role for certain employees working in the human resources department. Which permission sets must be granted to this role?

• A. USAGE on database hr_db, USAGE on all schemas in database hr_db, SELECT on all tables in database hr_db
• B. USAGE on database hr_db, SELECT on all schemas in database hr_db, SELECT on all tables in database hr_db
• C. MODIFY on database hr_db, USAGE on all schemas in database hr_db, USAGE on all tables in database hr_db
• D. USAGE on database hr_db, USAGE on all schemas in database hr_db, REFERENCES on all tables in database hr_db

A company needs to have the following features available in its Snowflake account: 1. Support for Multi-Factor Authentication (MFA) 2. A minimum of 2 months of Time Travel availability 3. Database replication in between different regions 4. Native support for JDBC and ODBC 5. Customer-managed encryption keys using Tri-Secret Secure 6. Support for Payment Card Industry Data Security Standards (PCI DSS) In order to provide all the listed services, what is the MINIMUM Snowflake edition that should be selected during account creation?

• A. Standard
• B. Enterprise
• C. Business Critical
• D. Virtual Private Snowflake (VPS)

What transformations are supported in the below SQL statement? (Choose three.) CREATE PIPE ... AS COPY ... FROM (...)

• A. Data can be filtered by an optional WHERE clause
• B. Columns can be reordered
• C. Columns can be omitted
• D. Type casts are supported
• E. Incoming data can be joined with other tables
• F. The ON_ERROR - ABORT_STATEMENT command can be used

An Architect on a new project has been asked to design an architecture that meets Snowflake security, compliance, and governance requirements as follows: 1. Use Tri-Secret Secure in Snowflake 2. Share some information stored in a view with another Snowflake customer 3. Hide portions of sensitive information from some columns 4. Use zero-copy cloning to refresh the non-production environment from the production environment To meet these requirements, which design elements must be implemented? (Choose three.)

• A. Define row access policies
• B. Use the Business Critical edition of Snowflake
• C. Create a secure view
• D. Use the Enterprise edition of Snowflake
• E. Use Dynamic Data Masking
• F. Create a materialized view

Which of the following are characteristics of how row access policies can be applied to external tables? (Choose three.)

• A. An external table can be created with a row access policy, and the policy can be applied to the VALUE column
• B. row access policy can be applied to the VALUE column of an existing external table
• C. row access policy cannot be directly added to a virtual column of an external table
• D. External tables are supported as mapping tables in a row access policy
• E. While cloning a database, both the row access policy and the external table will be cloned
• F. row access policy cannot be applied to a view created on top of an external table

What is a characteristic of Role-Based Access Control (RBAC) as used in Snowflake?

• A. Privileges can be granted at the database level and can be inherited by all underlying objects
• B. user can use a "super-user" access along with SECURITYADMIN to bypass authorization checks and access all databases, schemas, and underlying objects
• C. user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles
• D. user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles

A company has an inbound share set up with eight tables and five secure views. The company plans to make the share part of its production data pipelines. Which actions can the company take with the inbound share? (Choose two.)

• A. Clone a table from a share
• B. Grant modify permissions on the share
• C. Create a table from the shared database
• D. Create additional views inside the shared database
• E. Create a table stream on the shared table

Which system functions does Snowflake provide to monitor clustering information within a table (Choose two.)

• A. SYSTEM$CLUSTERING_INFORMATION
• B. SYSTEM$CLUSTERING_USAGE
• C. SYSTEM$CLUSTERING_DEPTH
• D. SYSTEM$CLUSTERING_KEYS
• E. SYSTEM$CLUSTERING_PERCENT

An Architect is integrating an application that needs to read and write data to Snowflake without installing any additional software on the application server. How can this requirement be met?

• A. Use SnowSQL
• B. Use the Snowpipe REST API
• C. Use the Snowflake SQL REST API
• D. Use the Snowflake ODBC driver

An Architect executes the following statements in order: create table emp(id integer); insert into emp values (1),(2); create temporary table emp(id integer); insert into emp values (1) ; The Architect then executes the following statements: select count(*) from emp; drop table emp; select count(*) from emp; What will be the result?

• A. COUNT (*) 2 COUNT (*) 1
• B. COUNT (*) 1 COUNT (*) 2
• C. COUNT (*) 2 COUNT (*) 2
• D. The final query will result in an error

Based on the Snowflake object hierarchy, what securable objects belong directly to a Snowflake account? (Choose three.)

• A. Database
• B. Schema
• C. Table
• D. Stage
• E. Role
• F. Warehouse