ARA-C01 Exam Dumps - SnowPro Advanced: Architect Certification Exam

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.

Snowflake Documentation: Stored Procedures

Snowflake Documentation: Understanding Caller’s Rights and Owner’s Rights Stored Procedures

 According to the Snowflake documentation, the best practices for choosing clustering keys are:

Choose columns that are frequently used in join predicates. This can improve the join performance by reducing the number of micro-partitions that need to be scanned and joined.

Choose columns that are most actively used in selective filters. This can improve the scan efficiency by skipping micro-partitions that do not match the filter predicates.

Avoid using low cardinality columns, such as gender or country, as clustering keys. This can result in poor clustering and high maintenance costs.

Avoid using TIMESTAMP columns with nanoseconds, as they tend to have very high cardinality and low correlation with other columns. This can also result in poor clustering and high maintenance costs.

Avoid using columns with duplicate values or NULLs, as they can cause skew in the clustering and reduce the benefits of pruning.

Cluster on multiple columns if the queries use multiple filters or join predicates. This can increase the chances of pruning more micro-partitions and improve the compression ratio.

Clustering is not always useful, especially for small or medium-sized tables, or tables that are not frequently queried or updated. Clustering can incur additional costs for initially clustering the data and maintaining the clustering over time.

Clustering Keys & Clustered Tables | Snowflake Documentation

[Considerations for Choosing Clustering for a Table | Snowflake Documentation]

Comprehensive and Detailed Explanation From Exact Extract:

Snowflake supports both implicit and explicit transactions. However, only specific statement types are allowed within transactions.

Option C:

This is correct. In Snowflake, transactions can be started with any of the following: BEGIN, BEGIN WORK, or START TRANSACTION. Transactions can be ended using COMMIT, COMMIT WORK, or ROLLBACK.

Official Extract:

"You can explicitly start a transaction using the BEGIN, BEGIN WORK, or START TRANSACTION statements and end it using the COMMIT, COMMIT WORK, or ROLLBACK statements."

Source:Snowflake SQL Transactions

Option E:

This is correct. Transactions should only include DML statements (INSERT, UPDATE, DELETE, MERGE) and queries. DDL statements (CREATE, ALTER, DROP) automatically commit and cannot be part of an explicit transaction block.

Official Extract:

"A transaction can contain only DML statements and queries. Any DDL statement implicitly commits the current transaction."

Source:Snowflake SQL Transactions

Option A:

Incorrect. DDL statements are not allowed inside explicit transactions. If used, they trigger an implicit commit.

Option B:

Incorrect. The autocommit setting cannot be modified within a stored procedure. Autocommit is session-level and not dynamically changeable within procedural logic.

Option D:

Incorrect. Snowflake does not support END TRANSACTION as a valid SQL command. The correct ending statement for a transaction is COMMIT or ROLLBACK.

 According to Snowflake recommended best practice, the requirements of the large manufacturing company should be met by deploying a Private Data Exchange in combination with data shares for the European accounts. A Private Data Exchange is a feature of the Snowflake Data Cloud platform that enables secure and governed sharing of data between organizations. It allows Snowflake customers to create their own data hub and invite other parts of their organization or external partners to access and contribute data sets. A Private Data Exchange provides centralized management, granular access control, and data usage metrics for the data shared in the exchange1. A data share is a secure and direct way of sharing data between Snowflake accounts without having to copy or move the data. A data share allows the data provider to grant privileges on selected objects in their account to one or more data consumers in other accounts2. By using a Private Data Exchange in combination with data shares, the company can achieve the following benefits:

The business divisions can decide what data to share and publish it to the Private Data Exchange, where it can be discovered and accessed by other members of the exchange. This reduces the effort and complexity of managing multiple data sharing relationships and configurations.

The company can leverage the existing Snowflake accounts in the same cloud deployments to create the Private Data Exchange and invite the members to join. This minimizes the migration and setup costs and leverages the existing Snowflake features and security.

The company can use data shares to share data with the European accounts that are in different regions or cloud platforms. This allows the company to comply with the regional and regulatory requirements for data sovereignty and privacy, while still enabling data collaboration across the organization.

The company can use the Snowflake Data Cloud platform to perform data analysis and transformation on the shared data, as well as integrate with other data sources and applications. This enables the company to optimize its supply chain and increase its purchasing leverage with multiple vendors.

For ingesting a large volume of CSV data into Snowflake using Snowpipe, especially for a substantial amount like 10 TB, theon error = SKIP_FILEoption in theCOPY INTOcommand can be highly effective. This approach allows Snowpipe to skip over files that cause errors during the ingestion process, thereby not halting or significantly slowing down the overall data load. It helps in maintaining performance and cost-effectiveness by avoiding the reprocessing of problematic files and continuing with the ingestion of other data.

Tri-Secret Secure is only available in Snowflake Business Critical edition or higher, making that edition mandatory to satisfy the first requirement (Answer B). Secure views are required when sharing data with another Snowflake customer to ensure that underlying table data, logic, and metadata are protected and governed during Secure Data Sharing (Answer C). To hide or reveal portions of sensitive data at query time based on role or policy, Dynamic Data Masking is the appropriate Snowflake feature (Answer E).

Row access policies control row-level visibility but do not mask column values. The Enterprise edition does not support Tri-Secret Secure, making it insufficient for the stated requirements. Materialized views are unrelated to security or data masking and introduce unnecessary storage overhead.

This question integrates multiple SnowPro Architect domains—security, governance, sharing, and environment management—and tests the architect’s ability to select the minimal set of Snowflake features that collectively meet complex compliance and operational requirements.

=========

QUESTION NO: 52 [Snowflake Data Engineering]

Which columns can be included in an external table schema? (Select THREE).

A. VALUE

B. METADATA$ROW_ID

C. METADATA$ISUPDATE

D. METADATA$FILENAME

E. METADATA$FILE_ROW_NUMBER

F. METADATA$EXTERNAL_TABLE_PARTITION

Answer: A, D, E

External tables in Snowflake expose a combination of user-defined columns and system-generated metadata columns. The VALUE column is commonly used to store semi-structured data (such as JSON or Avro records) read directly from external storage (Answer A).

Snowflake also provides metadata columns that describe the source file. METADATA$FILENAME identifies the name of the file from which a given row was read (Answer D), and METADATA$FILE_ROW_NUMBER indicates the row number within that file (Answer E). These columns are frequently used for auditing, debugging, and data lineage tracking.

METADATA$ROW_ID and METADATA$ISUPDATE are associated with streams and change tracking, not external tables. METADATA$EXTERNAL_TABLE_PARTITION is not a valid selectable column in the external table schema definition. This question reinforces SnowPro Architect knowledge of how Snowflake represents external data and exposes file-level metadata for data lake architectures.

=========

QUESTION NO: 53 [Security and Access Management]

An Architect needs to ensure that users can upload data from Snowsight into an existing table.

What privileges must be granted? (Select THREE).

A. Database: USAGE

B. Database: OWNERSHIP

C. Schema: CREATE TABLE

D. Schema: USAGE

E. Table: SELECT

F. Table: OWNERSHIP

Answer: A, D, E

Uploading data into an existing table via Snowsight requires sufficient privileges to access the database and schema and to interact with the target table. Database-level USAGE is required to access objects within the database (Answer A). Schema-level USAGE is required to access the schema containing the table (Answer D).

Table-level SELECT is required by Snowsight to validate and preview the data and table structure during the upload process (Answer E). OWNERSHIP privileges are not required and would grant excessive control. CREATE TABLE is unnecessary when uploading into an existing table.

This reflects Snowflake’s least-privilege security model and is a common SnowPro Architect exam topic when designing user self-service data ingestion workflows.

=========

QUESTION NO: 54 [Performance Optimization and Monitoring]

An Architect is troubleshooting a long-running statement and needs to identify blocked transactions and the queries blocking them.

Which views should be used? (Select TWO).

A. QUERY_HISTORY

B. OBJECT_DEPENDENCIES

C. DATA_TRANSFER_HISTORY

D. LOCK_WAIT_HISTORY

E. ACCESS_HISTORY

Answer: A, D

LOCK_WAIT_HISTORY provides detailed information about transactions waiting on locks, including which transactions are blocked and which ones are blocking them (Answer D). This view is essential for diagnosing contention and concurrency issues.

QUERY_HISTORY complements this by providing execution details about the blocking queries, such as duration, user, and SQL text (Answer A). Together, these views allow architects to correlate blocked transactions with the responsible workloads.

The other views are unrelated to transaction locking behavior. This question highlights SnowPro Architect troubleshooting skills related to concurrency and transaction management.

=========

QUESTION NO: 55 [Snowflake Ecosystem and Integrations]

Which functions does the Data Build Tool (dbt) facilitate? (Select TWO).

A. Data loading

B. Data testing

C. Data visualization

D. Data transformation

E. Data replication

Answer: B, D

dbt is a transformation-focused analytics engineering tool that operates inside the data warehouse. It enables SQL-based data transformations and manages dependencies between models (Answer D). dbt also provides built-in data testing capabilities, allowing teams to define and run tests for data quality, such as uniqueness, not-null constraints, and referential integrity checks (Answer B).

dbt does not handle data loading, visualization, or replication; those functions are typically handled by ingestion tools, BI platforms, or replication services. SnowPro Architect candidates are expected to understand dbt’s role in the Snowflake ecosystem and how it fits into modern ELT architectures.