Free SnowPro Advanced: Architect Recertification Exam ARA-R01 Exam Practice Test

A Developer is having a performance issue with a Snowflake query. The query receives up to 10 different values for one parameter and then performs an aggregation over the majority of a fact table. It thenjoins against a smaller dimension table. This parameter value is selected by the different query users when they execute it during business hours. Both the fact and dimension tables are loaded with new data in an overnight import process.On a Small or Medium-sized virtual warehouse, the query performs slowly. Performance is acceptable on a size Large or bigger warehouse. However, there is no budget to increase costs. The Developerneeds a recommendation that does not increase compute costs to run this query. What should the Architect recommend?

• Create a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The query results will then be cached and ready to respond quickly when the users re-issue the query.
• Create a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The task will be scheduled to align with the users' working hours in order to allow the warehouse cache to be used.
• Enable the search optimization service on the table. When the users execute the query, the search optimization service will automatically adjust the query execution plan based on the frequently-used parameters.
• Create a dedicated size Large warehouse for this particular set of queries. Create a new role that has USAGE permission on this warehouse and has the appropriate read permissions over the fact and dimension tables. Have users switch to this role and use this warehouse when they want to access this data.

A large manufacturing company runs a dozen individual Snowflake accounts across its business divisions. The company wants to increase the level of data sharing to support supply chain optimizations and increase its purchasing leverage with multiple vendors.The company’s Snowflake Architects need to design a solution that would allow the business divisions to decide what to share, while minimizing the level of effort spent on configuration and management. Most of the company divisions use Snowflake accounts in the same cloud deployments with a few exceptions for European-based divisions.According to Snowflake recommended best practice, how should these requirements be met?

• Migrate the European accounts in the global region and manage shares in a connected graph architecture. Deploy a Data Exchange.
• Deploy a Private Data Exchange in combination with data shares for the European accounts.
• Deploy to the Snowflake Marketplace making sure that invoker_share() is used in all secure views.
• Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.

An Architect is designing a solution that will be used to process changed records in an orders table. Newly-inserted orders must be loaded into the f_orders fact table, which will aggregate all the orders by multiple dimensions (time, region, channel, etc.). Existing orders can be updated by the sales department within 30 days after the order creation. In case of an order update, the solution must perform two actions:

• Update the order in the f_0RDERS fact table.
• Load the changed order data into the special table ORDER _REPAIRS.
• This table is used by the Accounting department once a month. If the order has been changed, the Accounting team needs to know the latest details and perform the necessary actions based on the data in the order_repairs table.
• What data processing logic design will be the MOST performant?
• Use one stream and one task.
• Use one stream and two tasks.
• Use two streams and one task.
• Use two streams and two tasks.

Why might a Snowflake Architect use a star schema model rather than a 3NF model when designing a data architecture to run in Snowflake? (Select TWO).

• Snowflake cannot handle the joins implied in a 3NF data model.
• The Architect wants to remove data duplication from the data stored in Snowflake.
• The Architect is designing a landing zone to receive raw data into Snowflake.
• The Bl tool needs a data model that allows users to summarize facts across different dimensions, or to drill down from the summaries.
• The Architect wants to present a simple flattened single view of the data to a particular group of end users.

A company is storing large numbers of small JSON files (ranging from 1-4 bytes) that are received from IoT devices and sent to a cloud provider. In any given hour, 100,000 files are added to the cloud provider.What is the MOST cost-effective way to bring this data into a Snowflake table?

• An external table
• A pipe
• A stream
• A copy command at regular intervals

What are characteristics of the use of transactions in Snowflake? (Select TWO).

• Explicit transactions can contain DDL, DML, and query statements.
• The autocommit setting can be changed inside a stored procedure.
• A transaction can be started explicitly by executing a begin work statement and end explicitly by executing a commit work statement.
• A transaction can be started explicitly by executing a begin transaction statement and end explicitly by executing an end transaction statement.
• Explicit transactions should contain only DML statements and query statements. All DDL statements implicitly commit active transactions.

Company A has recently acquired company B. The Snowflake deployment for company B is located in the Azure West Europe region.As part of the integration process, an Architect has been asked to consolidate company B's sales data into company A's Snowflake account which is located in the AWS us-east-1 region.How can this requirement be met?

• Replicate the sales data from company B's Snowflake account into company A's Snowflake account using cross-region data replication within Snowflake. Configure a direct share from company B's account to company A's account.
• Export the sales data from company B's Snowflake account as CSV files, and transfer the files to company A's Snowflake account. Import the data using Snowflake's data loading capabilities.
• Migrate company B's Snowflake deployment to the same region as company A's Snowflake deployment, ensuring data locality. Then perform a direct database-to-database merge of the sales data.
• Build a custom data pipeline using Azure Data Factory or a similar tool to extract the sales data from company B's Snowflake account. Transform the data, then load it into company A's Snowflake account.

An Architect needs to design a solution for building environments for development, test, and pre- production, all located in a single Snowflake account. The environments should be based on production data.Which solution would be MOST cost-effective and performant?

• Use zero-copy cloning into transient tables.
• Use zero-copy cloning into permanent tables.
• Use CREATE TABLE ... AS SELECT (CTAS) statements.
• Use a Snowflake task to trigger a stored procedure to copy data.

Which Snowflake objects can be used in a data share? (Select TWO).

• Standard view
• Secure view
• Stored procedure
• External table
• Stream

An Architect on a new project has been asked to design an architecture that meets Snowflake security, compliance, and governance requirements as follows:

• Use Tri-Secret Secure in Snowflake
• Share some information stored in a view with another Snowflake customer
• Hide portions of sensitive information from some columns
• Use zero-copy cloning to refresh the non-production environment from the production environmentTo meet these requirements, which design elements must be implemented? (Choose three.)
• Define row access policies.
• Use the Business-Critical edition of Snowflake.
• Create a secure view.
• Use the Enterprise edition of Snowflake.
• Use Dynamic Data Masking.
• Create a materialized view.