SnowPro Advanced Architect Practice Exam

Join Us Among the Stars

Sign Up & unlock 100% of Exam Questions

Log in / Sign up

No Strings Attached!

234Practice Questions

3Study Modes

Free

TopicSort

Question 1

Data Ingestion and Data Preparation

What does a Snowflake Architect need to consider when implementing a Snowflake Connector for Kafka?

A Every Kafka message is in JSON or Avro format.
B The default retention time for Kafka topics is 14 days.
C The Kafka connector supports key pair authentication, OAUTH, and basic authentication (for example, username and password).
D The Kafka connector will create one table and one pipe to ingest data for each topic. If the connector cannot create the table or the pipe it will result in an exception.

Question 2

Data Storage and Processing

Question 3

Data Ingestion and Data Preparation

Question 4

Performance, Cost, and Resource Optimization

Question 5

Data Ingestion and Data Preparation

Question 6

Data Ingestion and Data Preparation

Question 7

Secure Data Sharing and Consumption

Question 8

Secure Data Sharing and Consumption

Question 9

Secure Data Sharing and Consumption

Question 10

Secure Data Sharing and Consumption

Question 11

Secure Data Sharing and Consumption

Question 12

Data Storage and Processing

Question 13

Data Ingestion and Data Preparation

Question 14

Performance, Cost, and Resource Optimization

Question 15

Performance, Cost, and Resource Optimization

Question 16

Performance, Cost, and Resource Optimization

Question 17

Data Storage and Processing

Question 18

Secure Data Sharing and Consumption

Question 19

Secure Data Sharing and Consumption

Question 20

Data Storage and Processing

Question 21

Architecture Overview

Question 22

Performance, Cost, and Resource Optimization

Question 23

Secure Data Sharing and Consumption

Question 24

Secure Data Sharing and Consumption

Question 25

Data Ingestion and Data Preparation

Page 1 of 10 • Questions 1-25 of 234

1 2 3 4 5

→

Know a question that should be here? Contribute to this exam

What considerations need to be taken when using database cloning as a tool for data lifecycle management in a development environment? (Choose two.)

A Any pipes in the source are not cloned.
B Any pipes in the source referring to internal stages are not cloned.
C Any pipes in the source referring to external stages are not cloned.
D The clone inherits all granted privileges of all child objects in the source object, including the database.
E The clone inherits all granted privileges of all child objects in the source object, excluding the database.

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.

What is a key consideration when setting up search optimization service for a table?

A Search optimization service works best with a column that has a minimum of 100 K distinct values.
B Search optimization service can significantly improve query performance on partitioned external tables.
C Search optimization service can help to optimize storage usage by compressing the data into a GZIP format.
D The table must be clustered with a key having multiple columns for effective search optimization.

When using the COPY INTO [table] command with the CSV file format, how does the MATCH_BY_COLUMN_NAME parameter behave?

A It expects a header to be present in the CSV file, which is matched to a case-sensitive table column name.
B The parameter will be ignored.
C The command will return an error.
D The command will return a warning stating that the file has unmatched columns.

Question 6

Data Ingestion and Data Preparation

Question 7

Secure Data Sharing and Consumption

Question 8

Secure Data Sharing and Consumption

Question 9

Secure Data Sharing and Consumption

Question 10

Secure Data Sharing and Consumption

Question 11

Secure Data Sharing and Consumption

Question 12

Data Storage and Processing

Question 13

Data Ingestion and Data Preparation

Question 14

Performance, Cost, and Resource Optimization

Question 15

Performance, Cost, and Resource Optimization

Question 16

Performance, Cost, and Resource Optimization

Question 17

Data Storage and Processing

Question 18

Secure Data Sharing and Consumption

Question 19

Secure Data Sharing and Consumption

Question 20

Data Storage and Processing

Question 21

Architecture Overview

Question 22

Performance, Cost, and Resource Optimization

Question 23

Secure Data Sharing and Consumption

Question 24

Secure Data Sharing and Consumption

Question 25

Data Ingestion and Data Preparation

Based on the architecture in the image, how can the data from DB1 be copied into TBL2? (Choose two.)

Question Image

A
B
C
D
E

How can the Snowflake context functions be used to help determine whether a user is authorized to see data that has column-level security enforced? (Choose two.)

A Set masking policy conditions using CURRENT_ROLE targeting the role in use for the current session.
B Set masking policy conditions using IS_ROLE_IN_SESSION targeting the role in use for the current account.
C Set masking policy conditions using INVOKER_ROLE targeting the executing role in a SQL statement.
D Determine if there are OWNERSHIP privileges on the masking policy that would allow the use of any function.
E Assign the ACCOUNTADMIN role to the user who is executing the object.

A company has a Snowflake environment running in AWS us-west-2 (Oregon). The company needs to share data privately with a customer who is running their Snowflake environment in Azure East US 2 (Virginia).

What is the recommended sequence of operations that must be followed to meet this requirement?

A
1. Create a share and add the database privileges to the share2. Create a new listing on the Snowflake Marketplace3. Alter the listing and add the share4. Instruct the customer to subscribe to the listing on the Snowflake Marketplace
B
1. Ask the customer to create a new Snowflake account in Azure EAST US 2 (Virginia)2. Create a share and add the database privileges to the share3. Alter the share and add the customer's Snowflake account to the share
C
1. Create a new Snowflake account in Azure East US 2 (Virginia)2. Set up replication between AWS us-west-2 (Oregon) and Azure East US 2 (Virginia) for the database objects to be shared3. Create a share and add the database privileges to the share4. Alter the share and add the customer's Snowflake account to the share
D
1. Create a reader account in Azure East US 2 (Virginia)2. Create a share and add the database privileges to the share3. Add the reader account to the share4. Share the reader account's URL and credentials with the customer

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

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 A user can use a "super-user" access along with SECURITYADMIN to bypass authorization checks and access all databases, schemas, and underlying objects.
C A user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles.
D A user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles.

A group of Data Analysts have been granted the role ANALYST_ROLE. They need a Snowflake database where they can create and modify tables, views, and other objects to load with their own data. The Analysts should not have the ability to give other Snowflake users outside of their role access to this data.

How should these requirements be met?

A Grant ANALYST_ROLE OWNERSHIP on the database, but make sure that ANALYST_ROLE does not have the MANAGE GRANTS privilege on the account.
B Grant SYSADMIN OWNERSHIP of the database, but grant the create schema privilege on the database to the ANALYST_ROLE.
C Make every schema in the database a MANAGED ACCESS schema, owned by SYSADMIN, and grant create privileges on each schema to the ANALYST_ROLE for each type of object that needs to be created.
D Grant ANALYST_ROLE OWNERSHIP on the database, but grant the OWNERSHIP ON FUTURE [object type]s in database privilege to SYSADMIN.

Which columns can be included in an external table schema? (Choose three.)

A VALUE
B METADATA$ROW_ID
C METADATA$ISUPDATE
D METADATA$FILENAME
E METADATA$FILE_ROW_NUMBER
F METADATA$EXTERNAL_TABLE_PARTITION

How can the Snowpipe REST API be used to keep a log of data load history?

A Call insertReport every 20 minutes, fetching the last 10,000 entries.
B Call loadHistoryScan every minute for the maximum time range.
C Call insertReport every 8 minutes for a 10-minute time range.
D Call loadHistoryScan every 10 minutes for a 15-minute time range.

A company is designing high availability and disaster recovery plans and needs to maximize redundancy and minimize recovery time objectives for their critical application processes.

Cost is not a concern as long as the solution is the best available.

The plan so far consists of the following steps:

Deployment of Snowflake accounts on two different cloud providers.
Selection of cloud provider regions that are geographically far apart.
The Snowflake deployment will replicate the databases and account data between both cloud provider accounts.
Implementation of Snowflake client redirect.

What is the MOST cost-effective way to provide the HIGHEST uptime and LEAST application disruption if there is a service event?

A Connect the applications using the - URL.Use the Business Critical Snowflake edition.
B Connect the applications using the - URL.Use the Virtual Private Snowflake (VPS) edition.
C Connect the applications using the - URL.Use the Enterprise Snowflake edition.
D Connect the applications using the - URL.Use the Business Critical Snowflake edition.

Data is being imported and stored as JSON in a VARIANT column. Query performance was fine, but most recently, poor query performance has been reported.

What could be causing this?

A There were JSON nulls in the recent data imports.
B The order of the keys in the JSON was changed.
C The recent data imports contained fewer fields than usual.
D There were variations in string lengths for the JSON values in the recent data imports.

Which SQL ALTER command will MAXIMIZE memory and compute resources for a Snowpark stored procedure when executed on the snowpark_opt_wh warehouse?

A alter warehouse snowpark_opt_wh set max_concurrency_level = 1;
B alter warehouse snowpark_opt_wh set max_concurrency_level = 2;
C alter warehouse snowpark_opt_wh set max_concurrency_level = 8;
D alter warehouse snowpark_opt_wh set max_concurrency_level = 16;

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.

Is it possible for a data provider account with a Snowflake Business Critical edition to share data with an Enterprise edition data consumer account?

A A Business Critical account cannot be a data sharing provider to an Enterprise consumer. Any consumer accounts must also be Business Critical.
B If a user in the provider account with role authority to CREATE or ALTER SHARE adds an Enterprise account as a consumer, it can import the share.
C If a user in the provider account with a share owning role sets SHARE_RESTRICTIONS to False when adding an Enterprise consumer account, it can import the share.
D If a user in the provider account with a share owning role which also has OVERRIDE SHARE RESTRICTIONS privilege SHARE_RESTRICTIONS set to False when adding an Enterprise consumer account, it can import the share.

Which Snowflake objects can be used in a data share? (Choose two.)

A Standard view
B Secure view
C Stored procedure
D External table
E Stream

What are characteristics of the use of transactions in Snowflake? (Choose two.)

A Explicit transactions can contain DDL, DML, and query statements.
B The AUTOCOMMIT setting can be changed inside a stored procedure.
C A transaction can be started explicitly by executing a BEGIN WORK statement and end explicitly by executing a COMMIT WORK statement.
D A transaction can be started explicitly by executing a BEGIN TRANSACTION statement and end explicitly by executing an END TRANSACTION statement.
E Explicit transactions should contain only DML statements and query statements. All DDL statements implicitly commit active transactions.

The diagram shows the process flow for Snowpipe auto-ingest with Amazon Simple Notification Service (SNS) with the following steps:

Step 1: Data files are loaded in a stage.

Step 2: An Amazon S3 event notification, published by SNS, informs Snowpipe — by way of Amazon Simple Queue Service (SQS) - that files are ready to load. Snowpipe copies the files into a queue.

Step 3: A Snowflake-provided virtual warehouse loads data from the queued files into the target table based on parameters defined in the specified pipe.

If an AWS Administrator accidentally deletes the SQS subscription to the SNS topic in Step 2, what will happen to the pipe that references the topic to receive event messages from Amazon
S3?

Question Image

A The pipe will continue to receive the messages as Snowflake will automatically restore the subscription to the same SNS topic and will recreate the pipe by specifying the same SNS topic name in the pipe definition.
B The pipe will no longer be able to receive the messages and the user must wait for 24 hours from the time when the SNS topic subscription was deleted. Pipe recreation is not required as the pipe will reuse the same subscription to the existing SNS topic after 24 hours.
C The pipe will continue to receive the messages as Snowflake will automatically restore the subscription by creating a new SNS topic. Snowflake will then recreate the pipe by specifying the new SNS topic name in the pipe definition.
D The pipe will no longer be able to receive the messages. To restore the system immediately, the user needs to manually create a new SNS topic with a different name and then recreate the pipe by specifying the new SNS topic name in the pipe definition.

What step will improve the performance of queries executed against an external table?

A Partition the external table.
B Shorten the names of the source files.
C Convert the source files' character encoding to UTF-8.
D Use an internal stage instead of an external stage to store the source files.

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.

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

Which of the following ingestion methods can be used to load near real-time data by using the messaging services provided by a cloud provider?

A Snowflake Connector for Kafka
B Snowflake streams
C Snowpipe
D Spark

Join Us Among the Stars

SnowPro Advanced ArchitectPreview

About the SnowPro Advanced Architect Exam

Mode Selection

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25