Questions for the ARA-R01 were updated on : Dec 01 ,2025
A user, analyst_user has been granted the analyst_role, and is deploying a SnowSQL script to run as a
background service to extract data from Snowflake.
What steps should be taken to allow the IP addresses to be accessed? (Select TWO).
BD
Explanation:
To ensure that an analyst_user can only access Snowflake from specific IP addresses, the following
steps are required:
Option B: This alters the network policy directly linked to analyst_user. Setting a network policy on
the user level is effective and ensures that the specified network restrictions apply directly and
exclusively to this user.
Option D: Before a network policy can be set or altered, the appropriate role with permission to
manage network policies must be used. SECURITYADMIN is typically the role that has privileges to
create and manage network policies in Snowflake. Creating a network policy that specifies allowed IP
addresses ensures that only requests coming from those IPs can access Snowflake under this policy.
After creation, this policy can be linked to specific users or roles as needed.
Options A and E mention altering roles or using the wrong role (USERADMIN typically does not
manage network security settings), and option C incorrectly attempts to set a network policy directly
as an IP address, which is not syntactically or functionally valid.
Reference: Snowflake's security management documentation covering network policies and role-
based access controls.
Two queries are run on the customer_address table:
create or replace TABLE CUSTOMER_ADDRESS ( CA_ADDRESS_SK NUMBER(38,0), CA_ADDRESS_ID
VARCHAR(16), CA_STREET_NUMBER VARCHAR(IO) CA_STREET_NAME VARCHAR(60),
CA_STREET_TYPE VARCHAR(15), CA_SUITE_NUMBER VARCHAR(10), CA_CITY VARCHAR(60),
CA_COUNTY
VARCHAR(30), CA_STATE VARCHAR(2), CA_ZIP VARCHAR(10), CA_COUNTRY VARCHAR(20),
CA_GMT_OFFSET NUMBER(5,2), CA_LOCATION_TYPE
VARCHAR(20) );
ALTER TABLE DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS ADD SEARCH OPTIMIZATION ON
SUBSTRING(CA_ADDRESS_ID);
Which queries will benefit from the use of the search optimization service? (Select TWO).
AB
Explanation:
The use of the search optimization service in Snowflake is particularly effective when queries involve
operations that match exact substrings or start from the beginning of a string. The ALTER TABLE
command adding search optimization specifically for substrings on the CA_ADDRESS_ID field allows
the service to create an optimized search path for queries using substring matches.
Option A benefits because it directly matches a substring from the start of the CA_ADDRESS_ID,
aligning with the optimization's capability to quickly locate records based on the beginning segments
of strings.
Option B also benefits, despite performing a full equality check, because it essentially compares the
full length of CA_ADDRESS_ID to a substring, which can leverage the substring index for efficient
retrieval.
Options C, D, and E involve patterns that do not start from the beginning of the string or use
negations, which are not optimized by the search optimization service configured for starting
substring matches.
Reference: Snowflake's documentation on the use of search optimization for substring matching in
SQL queries.
A user has activated primary and secondary roles for a session.
What operation is the user prohibited from using as part of SQL actions in Snowflake using the
secondary role?
B
Explanation:
In Snowflake, when a user activates a secondary role during a session, certain privileges associated
with DDL (Data Definition Language) operations are restricted. The CREATE statement, which falls
under DDL operations, cannot be executed using a secondary role. This limitation is designed to
enforce role-based access control and ensure that schema modifications are managed carefully,
typically reserved for primary roles that have explicit permissions to modify database structures.
Reference: Snowflake's security and access control documentation specifying the limitations and
capabilities of primary versus secondary roles in session management.
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:
1. Update the order in the f_0RDERS fact table.
2. 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?
B
Explanation:
The most performant design for processing changed records, considering the need to both update
records in the f_orders fact table and load changes into the order_repairs table, is to use one stream
and two tasks. The stream will monitor changes in the orders table, capturing both inserts and
updates. The first task would apply these changes to the f_orders fact table, ensuring all dimensions
are accurately represented. The second task would use the same stream to insert relevant changes
into the order_repairs table, which is critical for the Accounting department's monthly review. This
method ensures efficient processing by minimizing the overhead of managing multiple streams and
synchronizing between them, while also allowing specific tasks to optimize for their target
operations.
Reference: Snowflake's documentation on streams and tasks for handling data changes efficiently.
An Architect needs to improve the performance of reports that pull data from multiple Snowflake
tables, join, and then aggregate the dat
a. 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?
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.
A company is following the Data Mesh principles, including domain separation, and chose one
Snowflake account for its data platform.
An Architect created two data domains to produce two data products. The Architect needs a third
data domain that will use both of the data products to create an aggregate data product. The read
access to the data products will be granted through a separate role.
Based on the Data Mesh principles, how should the third domain be configured to create the
aggregate product if it has been granted the two read roles?
D
Explanation:
In the scenario described, where a third data domain needs access to two existing data products in a
Snowflake account structured according to Data Mesh principles, the best approach is to utilize
Snowflake’s Data Exchange functionality. Option D is correct as it facilitates the sharing and
governance of data across different domains efficiently and securely. Data Exchange allows domains
to publish and subscribe to live data products, enabling real-time data collaboration and access
management in a governed manner. This approach is in line with Data Mesh principles, which
advocate for decentralized data ownership and architecture, enhancing agility and scalability across
the organization.
Reference:
Snowflake Documentation on Data Exchange
Articles on Data Mesh Principles in Data Management
The data share exists between a data provider account and a data consumer account. Five tables
from the provider account are being shared with the consumer account. The consumer role has been
granted the imported privileges privilege.
What will happen to the consumer account if a new table (table_6) is added to the provider schema?
D
Explanation:
When a new table (table_6) is added to a schema in the provider's account that is part of a data
share, the consumer will not automatically see the new table. The consumer will only be able to
access the new table once the appropriate privileges are granted by the provider. The correct
process, as outlined in option D, involves using the provider’s ACCOUNTADMIN role to grant USAGE
privileges on the database and schema, followed by SELECT privileges on the new table, specifically
to the share that includes the consumer's database. This ensures that the consumer account can
access the new table under the established data sharing setup.
Reference:
Snowflake Documentation on Managing Access Control
Snowflake Documentation on Data Sharing
A company has built a data pipeline using Snowpipe to ingest files from an Amazon S3 bucket.
Snowpipe is configured to load data into staging database tables. Then a task runs to load the data
from the staging database tables into the reporting database tables.
The company is satisfied with the availability of the data in the reporting database tables, but the
reporting tables are not pruning effectively. Currently, a size 4X-Large virtual warehouse is being used
to query all of the tables in the reporting database.
What step can be taken to improve the pruning of the reporting tables?
C
Explanation:
Effective pruning in Snowflake relies on the organization of data within micro-partitions. By using an
ORDER BY clause with clustering keys when loading data into the reporting tables, Snowflake can
better organize the data within micro-partitions. This organization allows Snowflake to skip over
irrelevant micro-partitions during a query, thus improving query performance and reducing the
amount of data scanned12.
Reference =
•
Snowflake Documentation on micro-partitions and data clustering2
•
Community article on recognizing unsatisfactory pruning and improving it1
An Architect needs to design a data unloading strategy for Snowflake, that will be used with the
COPY INTO <location> command.
Which configuration is valid?
C
Explanation:
For the configuration of data unloading in Snowflake, the valid option among the provided choices is
"C." This is because Snowflake supports unloading data into Google Cloud Storage using the COPY
INTO <location> command with specific configurations. The configurations listed in option C, such as
Parquet file format with UTF-8 encoding and gzip compression, are all supported by Snowflake.
Notably, Parquet is a columnar storage file format, which is optimal for high-performance data
processing tasks in Snowflake. The UTF-8 file encoding and gzip compression are both standard and
widely used settings that are compatible with Snowflake’s capabilities for data unloading to cloud
storage platforms.
Reference:
Snowflake Documentation on COPY INTO command
Snowflake Documentation on Supported File Formats
Snowflake Documentation on Compression and Encoding Options
Which command will create a schema without Fail-safe and will restrict object owners from passing
on access to other users?
D
Explanation:
A transient schema in Snowflake is designed without a Fail-safe period, meaning it does not incur
additional storage costs once it leaves Time Travel, and it is not protected by Fail-safe in the event of
a data loss. The WITH MANAGED ACCESS option ensures that all privilege grants, including future
grants on objects within the schema, are managed by the schema owner, thus restricting object
owners from passing on access to other users1.
Reference =
•
Snowflake Documentation on creating schemas1
•
Snowflake Documentation on configuring access control2
•
Snowflake Documentation on understanding and viewing Fail-safe3
A company is designing a process for importing a large amount of loT JSON data from cloud storage
into Snowflake. New sets of loT data get generated and uploaded approximately every 5 minutes.
Once the loT data is in Snowflake, the company needs up-to-date information from an external
vendor to join to the dat
a. This data is then presented to users through a dashboard that shows different levels of
aggregation. The external vendor is a Snowflake customer.
What solution will MINIMIZE complexity and MAXIMIZE performance?
D
Explanation:
Using Snowpipe for continuous, automated data ingestion minimizes the need for manual
intervention and ensures that data is available in Snowflake promptly after it is generated.
Leveraging Snowflake’s data sharing capabilities allows for efficient and secure access to the
vendor’s data without the need for complex API integrations. Materialized views provide pre-
aggregated data for fast access, which is ideal for dashboards that require high performance1234.
Reference =
•
Snowflake Documentation on Snowpipe4
•
Snowflake Documentation on Secure Data Sharing2
•
Best Practices for Data Ingestion with Snowflake1
A retailer's enterprise data organization is exploring the use of Data Vault 2.0 to model its data lake
solution. A Snowflake Architect has been asked to provide recommendations for using Data Vault 2.0
on Snowflake.
What should the Architect tell the data organization? (Select TWO).
A, C
Explanation:
Data Vault 2.0 on Snowflake supports the HASH_DIFF concept for change data capture, which is a
method to detect changes in the data by comparing the hash values of the records. Additionally,
Snowflake’s multi-table insert feature allows for the loading of multiple PIT tables in parallel from a
single join query, which can significantly streamline the data loading process and improve
performance1.
Reference =
•
Snowflake’s documentation on multi-table inserts1
•
Blog post on optimizing Data Vault architecture on Snowflake2
What is the MOST efficient way to design an environment where data retention is not considered
critical, and customization needs are to be kept to a minimum?
A
Explanation:
Transient databases in Snowflake are designed for situations where data retention is not critical, and
they do not have the fail-safe period that regular databases have. This means that data in a transient
database is not recoverable after the Time Travel retention period. Using a transient database is
efficient because it minimizes storage costs while still providing most functionalities of a standard
database without the overhead of data protection features that are not needed when data retention
is not a concern.
What actions are permitted when using the Snowflake SQL REST API? (Select TWO).
AD
Explanation:
A. The Snowflake SQL REST API does support the use of a GET command, which can be used to
retrieve the status of a previously submitted query or to fetch the results of a query once it has been
executed.
D. The use of a CALL command to a stored procedure is supported, which can return a result set,
including a table. This allows the invocation of stored procedures within Snowflake through the SQL
REST API.
Role A has the following permissions:
. USAGE on db1
. USAGE and CREATE VIEW on schemal in db1
. SELECT on tablel in schemal
Role B has the following permissions:
. USAGE on db2
. USAGE and CREATE VIEW on schema2 in db2
. SELECT on table2 in schema2
A user has Role A set as the primary role and Role B as a secondary role.
What command will fail for this user?
B
Explanation:
This command will fail because while the user has USAGE permission on db2 and schema2 through
Role B, and can create a view in schema2, they do not have SELECT permission on
db1.schemal.table1 with Role B. Since Role A, which has SELECT permission on db1.schemal.table1,
is not the currently active role when the view v2 is being created in db2.schema2, the user does not
have the necessary permissions to read from db1.schemal.table1 to create the view. Snowflake’s
security model requires that the active role have all necessary permissions to execute the command.