Question 1

An Architect needs to improve the performance of reports that pull data from multiple Snowflake
tables, join, and then aggregate the data. Users access the reports using several dashboards. There
are performance issues on Monday mornings between 9:00am-11:00am when many users check the
sales reports.
The size of the group has increased from 4 to 8 users. Waiting times to refresh the dashboards has
increased significantly. Currently this workload is being served by a virtual warehouse with the
following parameters:
AUTO-RESUME = TRUE AUTO_SUSPEND = 60 SIZE = Medium
What is the MOST cost-effective way to increase the availability of the reports?

Accepted Answer

D

Explanation: The most cost-effective way to increase the availability and performance of the reports during peak usage times, while keeping costs under control, is to use a multi-cluster warehouse in auto-scale mode. Option D suggests using a multi-cluster warehouse with 1 size Medium cluster and allowing it to auto-scale between 1 and 4 clusters based on demand. This setup ensures that additional computing resources are available when needed (e.g., during Monday morning peaks) and are scaled down to minimize costs when the demand decreases. This approach optimizes resource utilization and cost by adjusting the compute capacity dynamically, rather than maintaining a larger fixed size or multiple clusters continuously. Reference: Snowflake's official documentation on managing warehouses and using auto-scaling features.

Question 2

What is a characteristic of event notifications in Snowpipe?

Accepted Answer

D

Explanation: Event notifications in Snowpipe are messages sent by cloud storage providers to notify Snowflake of new or modified files in a stage. Snowpipe uses these notifications to trigger data loading from the stage to the target table. When a pipe is paused, event messages received for the pipe enter a limited retention period, which varies depending on the cloud storage provider. If the pipe is not resumed within the retention period, the event messages will be discarded and the data will not be loaded automatically. To load the data, the pipe must be resumed and the COPY command must be executed manually. This is a characteristic of event notifications in Snowpipe that distinguishes them from other options. Reference: Snowflake Documentation: Using Snowpipe, Snowflake Documentation: Pausing and Resuming a Pipe

Question 3

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

Accepted Answer

A, E, F

Explanation: A securable object is an entity to which access can be granted in Snowflake. Securable objects include databases, schemas, tables, views, stages, pipes, functions, procedures, sequences, tasks, streams, roles, warehouses, and shares1. The Snowflake object hierarchy is a logical structure that organizes the securable objects in a nested manner. The top-most container is the account, which contains all the databases, roles, and warehouses for the customer organization. Each database contains schemas, which in turn contain tables, views, stages, pipes, functions, procedures, sequences, tasks, and streams. Each role can be granted privileges on other roles or securable objects. Each warehouse can be used to execute queries on securable objects2. Based on the Snowflake object hierarchy, the securable objects that belong directly to a Snowflake account are databases, roles, and warehouses. These objects are created and managed at the account level, and do not depend on any other securable object. The other options are not correct because: Schemas belong to databases, not to accounts. A schema must be created within an existing database3. Tables belong to schemas, not to accounts. A table must be created within an existing schema4. Stages belong to schemas or tables, not to accounts. A stage must be created within an existing schema or table. Reference: 1: Overview of Access Control | Snowflake Documentation 2: Securable Objects | Snowflake Documentation 3: CREATE SCHEMA | Snowflake Documentation 4: CREATE TABLE | Snowflake Documentation [5]: CREATE STAGE | Snowflake Documentation

Question 4

An Architect has designed a data pipeline that Is receiving small CSV files from multiple sources. All
of the files are landing in one location. Specific files are filtered for loading into Snowflake tables
using the copy command. The loading performance is poor.
What changes can be made to Improve the data loading performance?

Accepted Answer

B

Explanation: According to the Snowflake documentation, the data loading performance can be improved by following some best practices and guidelines for preparing and staging the data files. One of the recommendations is to aim for data files that are roughly 100-250 MB (or larger) in size compressed, as this will optimize the number of parallel operations for a load. Smaller files should be aggregated and larger files should be split to achieve this size range. Another recommendation is to use a multi- cluster warehouse for loading, as this will allow for scaling up or out the compute resources depending on the load demand. A single-cluster warehouse may not be able to handle the load concurrency and throughput efficiently. Therefore, by creating a multi-cluster warehouse and merging smaller files to create bigger files, the data loading performance can be improved. Reference: Data Loading Considerations Preparing Your Data Files Planning a Data Load

Question 5

Which data models can be used when modeling tables in a Snowflake environment? (Select THREE).

Accepted Answer

B, D, F

Explanation: Snowflake is a cloud data platform that supports various data models for modeling tables in a Snowflake environment. The data models can be classified into two categories: dimensional and normalized. Dimensional data models are designed to optimize query performance and ease of use for business intelligence and analytics. Normalized data models are designed to reduce data redundancy and ensure data integrity for transactional and operational systems. The following are some of the data models that can be used in Snowflake: Dimensional/Kimball: This is a popular dimensional data model that uses a star or snowflake schema to organize data into fact and dimension tables. Fact tables store quantitative measures and foreign keys to dimension tables. Dimension tables store descriptive attributes and hierarchies. A star schema has a single denormalized dimension table for each dimension, while a snowflake schema has multiple normalized dimension tables for each dimension. Snowflake supports both star and snowflake schemas, and allows users to create views and joins to simplify queries. Inmon/3NF: This is a common normalized data model that uses a third normal form (3NF) schema to organize data into entities and relationships. 3NF schema eliminates data duplication and ensures data consistency by applying three rules: 1) every column in a table must depend on the primary key, 2) every column in a table must depend on the whole primary key, not a part of it, and 3) every column in a table must depend only on the primary key, not on other columns. Snowflake supports 3NF schema and allows users to create referential integrity constraints and foreign key relationships to enforce data quality. Data vault: This is a hybrid data model that combines the best practices of dimensional and normalized data models to create a scalable, flexible, and resilient data warehouse. Data vault schema consists of three types of tables: hubs, links, and satellites. Hubs store business keys and metadata for each entity. Links store associations and relationships between entities. Satellites store descriptive attributes and historical changes for each entity or relationship. Snowflake supports data vault schema and allows users to leverage its features such as time travel, zero-copy cloning, and secure data sharing to implement data vault methodology. Reference: What is Data Modeling? | Snowflake, Snowflake Schema in Data Warehouse Model - GeeksforGeeks, [Data Vault 2.0 Modeling with Snowflake]

Question 6

A user has the appropriate privilege to see unmasked data in a column. If the user loads this column data into another column that does not have a masking policy, what will occur?

Accepted Answer

A

Explanation: According to the SnowPro Advanced: Architect documents and learning resources, column masking policies are applied at query time based on the privileges of the user who runs the query. Therefore, if a user has the privilege to see unmasked data in a column, they will see the original data when they query that column. If they load this column data into another column that does not have a masking policy, the unmasked data will be loaded in the new column, and any user who can query the new column will see the unmasked data as well. The masking policy does not affect the underlying data in the column, only the query results. Reference: Snowflake Documentation: Column Masking Snowflake Learning: Column Masking

Question 7

A company is using Snowflake in Azure in the Netherlands. The company analyst team also has data
in JSON format that is stored in an Amazon S3 bucket in the AWS Singapore region that the team
wants to analyze.
The Architect has been given the following requirements:
1. Provide access to frequently changing data
2. Keep egress costs to a minimum
3. Maintain low latency
How can these requirements be met with the LEAST amount of operational overhead?

Accepted Answer

B

Explanation: Option A is the best design to meet the requirements because it uses a materialized view on top of an external table against the S3 bucket in AWS Singapore. A materialized view is a database object that contains the results of a query and can be refreshed periodically to reflect changes in the underlying data1. An external table is a table that

Question 8

The Business Intelligence team reports that when some team members run queries for their dashboards in parallel with others, the query response time is getting significantly slower What can a Snowflake Architect do to identify what is occurring and troubleshoot this issue? A) https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/SNOWFLAKE_ARA-C01/page_71_img_1.jpg B) https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/SNOWFLAKE_ARA-C01/page_71_img_2.jpg C) https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/SNOWFLAKE_ARA-C01/page_71_img_3.jpg D) https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/SNOWFLAKE_ARA-C01/page_71_img_4.jpg

Accepted Answer

A

Explanation: The image shows a SQL query that can be used to identify which queries are spilled to remote storage and suggests changing the warehouse parameters to address this issue. Spilling to remote storage occurs when the memory allocated to a warehouse is insufficient to process a query, and Snowflake uses disk or cloud storage as a temporary cache. This can significantly slow down the query performance and increase the cost. To troubleshoot this issue, a Snowflake Architect can run the query shown in the image to find out which queries are spilling, how much data they are spilling, and which warehouses they are using. Then, the architect can adjust the warehouse size, type, or scaling policy to provide enough memory for the queries and avoid spilling12. Reference: Recognizing Disk Spilling Managing the Kafka Connector

Question 9

What are purposes for creating a storage integration? (Choose three.)

Accepted Answer

Explanation: The purpose of creating a storage integration in Snowflake includes: B. Store a generated identity and access management (IAM) entity for an external cloud provider - This helps in managing authentication and authorization with external cloud storage without embedding credentials in Snowflake. It supports various cloud providers like AWS, Azure, or GCP, ensuring that the identity management is streamlined across platforms. C. Support multiple external stages using one single Snowflake object - Storage integrations allow you to set up access configurations that can be reused across multiple external stages, simplifying the management of external data integrations. D. Avoid supplying credentials when creating a stage or when loading or unloading data - By using a storage integration, Snowflake can interact with external storage without the need to continuously manage or expose sensitive credentials, enhancing security and ease of operations. Reference: Snowflake documentation on storage integrations, found within the SnowPro Advanced: Architect course materials.

Question 10

An Architect needs to allow a user to create a database from an inbound share. To meet this requirement, the user’s role must have which privileges? (Choose two.)

Accepted Answer

C, E

Explanation: According to the Snowflake documentation, to create a database from an inbound share, the user’s role must have the following privileges: The CREATE DATABASE privilege on the current account. This privilege allows the user to create a new database in the account1. The IMPORT DATABASE privilege on the share. This privilege allows the user to import a database from the share into the account2. The other privileges listed are not relevant for this requirement. The IMPORT SHARE privilege is used to import a share into the account, not a database3. The IMPORT PRIVILEGES privilege is used to import the privileges granted on the shared objects, not the objects themselves2. The CREATE SHARE privilege is used to create a share to provide data to other accounts, not to consume data from other accounts4. Reference: CREATE DATABASE | Snowflake Documentation Importing Data from a Share | Snowflake Documentation Importing a Share | Snowflake Documentation CREATE SHARE | Snowflake Documentation

Question 11

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

Accepted Answer

A

Explanation: Partitioning an external table is a technique that improves the performance of queries executed against the table by reducing the amount of data scanned. Partitioning an external table involves creating one or more partition columns that define how the table is logically divided into subsets of data based on the values in those columns. The partition columns can be derived from the file metadata (such as file name, path, size, or modification time) or from the file content (such as a column value or a JSON attribute). Partitioning an external table allows the query optimizer to prune the files that do not match the query predicates, thus avoiding unnecessary data scanning and processing2 The other options are not effective steps for improving the performance of queries executed against an external table: Shorten the names of the source files. This option does not have any impact on the query performance, as the file names are not used for query processing. The file names are only used for creating the external table and displaying the query results3 Convert the source files’ character encoding to UTF-8. This option does not affect the query performance, as Snowflake supports various character encodings for external table files, such as UTF- 8, UTF-16, UTF-32, ISO-8859-1, and Windows-1252. Snowflake automatically detects the character encoding of the files and converts them to UTF-8 internally for query processing4 Use an internal stage instead of an external stage to store the source files. This option is not applicable, as external tables can only reference files stored in external stages, such as Amazon S3, Google Cloud Storage, or Azure Blob Storage. Internal stages are used for loading data into internal tables, not external tables5 Reference: 1: SnowPro Advanced: Architect | Study Guide 2: Snowflake Documentation | Partitioning External Tables 3: Snowflake Documentation | Creating External Tables 4: Snowflake Documentation | Supported File Formats and Compression for Staged Data Files 5: Snowflake Documentation | Overview of Stages : SnowPro Advanced: Architect | Study Guide : Partitioning External Tables : Creating External Tables : Supported File Formats and Compression for Staged Data Files : Overview of Stages

Question 12

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:
1. Deployment of Snowflake accounts on two different cloud providers.
2. Selection of cloud provider regions that are geographically far apart.
3. The Snowflake deployment will replicate the databases and account data between both cloud
provider accounts.
4. 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?

Accepted Answer

D

Explanation: To provide the highest uptime and least application disruption in case of a service event, the best option is to use the Business Critical Snowflake edition and connect the applications using the - URL. The Business Critical Snowflake edition offers the highest level of security, performance, and availability for Snowflake accounts. It includes features such as customer-managed encryption keys, HIPAA compliance, and 4-hour RPO and RTO SLAs. It also supports account replication and failover across regions and cloud platforms, which enables business continuity and disaster recovery. By using the - URL, the applications can leverage the Snowflake Client Redirect feature, which automatically redirects the client connections to the secondary account in case of a failover. This way, the applications can seamlessly switch to the backup account without any manual intervention or configuration changes. The other options are less cost-effective or less reliable because they either use a lower edition of Snowflake, which does not support account replication and failover, or they use the - URL, which does not support client redirect and requires manual updates to the connection string in case of a failover. Reference: [Snowflake Editions] 1 [Replication and Failover/Failback] 2 [Client Redirect] 3 [Snowflake Account Identifiers] 4

Question 13

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

Accepted Answer

C

Explanation: Role-Based Access Control (RBAC) is the Snowflake Access Control Framework that allows privileges to be granted by object owners to roles, and roles, in turn, can be assigned to users to restrict or allow actions to be performed on objects. A characteristic of RBAC as used in Snowflake is: Privileges can be granted at the database level and can be inherited by all underlying objects. This means that a role that has a certain privilege on a database, such as CREATE SCHEMA or USAGE, can also perform the same action on any schema, table, view, or other object within that database, unless explicitly revoked. This simplifies the access control management and reduces the number of grants required. A user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles. This means that a user can create a schema with the MANAGED ACCESS option, which changes the default behavior of object ownership and privilege granting within the schema. In a managed access schema, object owners lose the ability to grant privileges on their objects to other roles, and only the schema owner or a role with the MANAGE GRANTS privilege can do so. This enhances the security and governance of the schema and its objects. The other options are not characteristics of RBAC as used in Snowflake: A user can use a “super-user” access along with securityadmin to bypass authorization checks and access all databases, schemas, and underlying objects. This is not true, as there is no such thing as a “super-user” access in Snowflake. The securityadmin role is a predefined role that can manage users and roles, but it does not have any privileges on any database objects by default. To access any object, the securityadmin role must be explicitly granted the appropriate privilege by the object owner or another role with the grant option. A user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles. This is not true, as this contradicts the definition of a managed access schema. In a managed access schema, object owners cannot grant privileges on their objects to other roles, and only the schema owner or a role with the MANAGE GRANTS privilege can do so. Reference: Overview of Access Control A Functional Approach For Snowflake’s Role-Based Access Controls Snowflake Role-Based Access Control simplified Snowflake RBAC security prefers role inheritance to role composition Overview of Snowflake Role Based Access Control

Question 14

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.)

Accepted Answer

A, B

Explanation: These two configuration options can enhance the performance of the workload that consists of a huge number of concurrent queries that are smaller and faster. Enabling a multi-clustered virtual warehouse in maximized mode allows the warehouse to scale out automatically by adding more clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This can improve the concurrency and throughput of the workload by minimizing or preventing queuing. The maximized mode is suitable for workloads that require high performance and low latency, and are less sensitive to credit consumption1. Setting the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level allows the warehouse to run more queries concurrently on each cluster. This can improve the utilization and efficiency of the warehouse resources, especially for smaller and faster queries that do not require a lot of processing power. The MAX_CONCURRENCY_LEVEL parameter can be set when creating or modifying a warehouse, and it can be changed at any time2. Reference: Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses Snowflake Documentation: MAX_CONCURRENCY_LEVEL

Question 15

An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an
ORDERS table (deleting all records older than 5 years), without granting any privileges on the table.
The group’s manager (ORDER_MANAGER) has full DELETE privileges on the table.
How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the
DELETE privilege held by the ORDER_MANAGER role?

Accepted Answer

C

Explanation: This is the correct answer because it allows the ORDER_ADMIN role to perform the data cleanup without needing the DELETE privilege on the ORDERS table. A stored procedure is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A stored procedure can run with either the caller’s rights or the owner’s rights. A caller’s rights stored procedure runs with the privileges of the role that called the stored procedure, while an owner’s rights stored procedure runs with the privileges of the role that created the stored procedure. By creating a stored procedure that runs with owner’s rights, the ORDER_MANAGER role can delegate the specific task of deleting old data to the ORDER_ADMIN role, without granting the ORDER_ADMIN role more general privileges on the ORDERS table. The stored procedure must include the appropriate business logic to delete only the records older than 5 years, and the ORDER_MANAGER role must grant the USAGE privilege on the stored procedure to the ORDER_ADMIN role. The ORDER_ADMIN role can then execute the stored procedure to perform the data cleanup12. Reference: Snowflake Documentation: Stored Procedures Snowflake Documentation: Understanding Caller’s Rights and Owner’s Rights Stored Procedures

Free SNOWFLAKE ARA-C01 Actual Exam Questions s