Pass the Snowflake SnowPro Advanced: Architect ARA-C01 Questions and answers with ExamsMirror

The Account Per Tenant strategy involves creating separate Snowflake accounts for each tenant within the multi-tenant application. This approach offers a number of advantages.

Option B:With separate accounts, each tenant's environment is isolated, making security and RBAC policies simpler to configure and maintain. This is because each account can have its own set of roles and privileges without the risk of cross-tenant access or the complexity of maintaining a highly granular permission model within a shared environment.

Option D:This approach also allows for each tenant to have a unique data shape, meaning that the database schema can be tailored to the specific needs of each tenant without affecting others. This can be essential when tenants have different data models, usage patterns, or application customizations.

 The SIMULATED_DATA_SHARING_CONSUMER session parameter allows a data provider to simulate the data access of a consumer account without creating a reader account or logging in with the consumer credentials. This parameter can be used to validate the data accessibility by the consumers in a data share, especially when using secure views or secure UDFs that filter data based on the current account or role. By setting this parameter to the name of a consumer account, the data provider can see the same data as the consumer would see when querying the shared database. This is a convenient and efficient way to test the data sharing functionality and ensure that only the intended data is visible to the consumers.

According to the Snowflake documentation, tasks are objects that enable scheduling and execution of SQL statements or JavaScript user-defined functions (UDFs) in Snowflake. Tasks can be used to automate data loading, transformation, and maintenance operations. Snowflake scripting is a feature that allows writing procedural logic using SQL statements and JavaScript UDFs. Snowflake scripting can be used to create complex workflows and orchestrate tasks. Therefore, the best option to automate the daily import of two files from an external stage into Snowflake, join and aggregate the data, and produce a final result set is to create a task using Snowflake scripting that will import the files using the COPY INTO command, and then call a UDF to perform the join and aggregation logic. The UDF can return a table or a variant value as the final result set. References:

Tasks

Snowflake Scripting

User-Defined Functions

In Snowflake, when a user activates a secondary role during a session, certain privileges associated with DDL (Data Definition Language) operations are restricted. TheCREATEstatement, 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.

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.

The Time Travel retention period for T1 will be 30 days, which is the retention period set at the table level. The Time Travel retention period determines how long the historical data is preserved and accessible for an object after it is modified or dropped. The Time Travel retention period can be set at the account level, the database level, the schema level, or the table level. The retention period set at the lowest level of the hierarchy takes precedence over the higher levels. Therefore, the retention period set at the table level overrides the retention periods set at the schema level, the database level, or the account level. When the user drops the database DB1, the table T1 is also dropped, but the historical data is still preserved for 30 days, which is the retention period set at the table level. The user can use the UNDROP command to restore the table T1 within the 30-day period. The other options are incorrect because:

10 days is the retention period set at the database level, which is overridden by the table level.

20 days is the retention period set at the schema level, which is also overridden by the table level.

37 days is not a valid option, as it is not the retention period set at any level.

Understanding & Using Time Travel

AT | BEFORE

Snowflake Time Travel & Fail-safe

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. References:

Data Loading Considerations

Preparing Your Data Files

Planning a Data Load

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.

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

The best way to integrate an enterprise identity provider with federated authentication and enable automatic user creation and role assignment in Snowflake is to use SCIM (System for Cross-domain Identity Management). SCIM allows Snowflake to synchronize with the identity provider and create users and groups based on the information provided by the identity provider. The groups are mapped to roles in Snowflake, and the users are assigned the roles based on their group membership. This way, the identity provider remains the source of truth for user and group management, and Snowflake automatically reflects the changes without manual intervention. The other options are either incorrect or incomplete, as they involve using OAuth, which is a protocol for authorization, not authentication or user provisioning, and require additional configuration of authorization and resource servers.

The Data Vault modeling approach separates data into hubs (business keys), links, and satellites, often requiring simultaneous insertion of related records derived from the same source dataset. Snowflake’s conditional multi-table INSERT enables a single source query to populate multiple target tables in parallel based on conditional logic. This capability aligns well with Data Vault patterns, where hubs and satellites are typically loaded together from the same staging data.

By inserting into hubs and satellites in parallel using surrogate keys (Answer A), architects ensure consistency, atomicity, and efficient processing without re-scanning source data multiple times. This approach reduces compute usage and simplifies ETL logic, which is particularly valuable in large-scale, near–real-time ingestion pipelines.

Options involving dimensions and facts relate more closely to star schema modeling, not Data Vault. Sequential insertion is also less efficient and not the defining advantage of conditional multi-table inserts. For SnowPro Architect candidates, this question emphasizes understanding how Snowflake SQL features support modern data modeling techniques such as Data Vault.

=========

QUESTION NO: 42 [Architecting Snowflake Solutions]

An Architect wants to alter a virtual warehouse for horizontal scaling but cannot find minimum and maximum cluster settings in the interface, even with the ACCOUNTADMIN role.

What is the MOST likely issue?

A. Missing CREATE WAREHOUSE privilege.

B. Incorrect SQL command used.

C. Using Standard edition where multi-cluster is not available.

D. A Snowflake Support ticket is required.

Answer: C

Multi-cluster virtual warehouses are only available in Snowflake Enterprise edition and higher. If the account is running on Standard edition, the options to configure minimum and maximum clusters for horizontal scaling will not appear in the UI or be available via SQL (Answer C).

This limitation is independent of role privileges; even ACCOUNTADMIN cannot enable multi-cluster functionality on unsupported editions. Incorrect SQL or missing privileges would result in errors rather than missing configuration options.

For SnowPro Architect candidates, this question tests awareness of edition-based feature availability, which is critical when designing scalable architectures and recommending Snowflake editions to meet workload requirements.

=========

QUESTION NO: 43 [Performance Optimization and Monitoring]

A 2 TB table with 400 columns is queried frequently to calculate average employee tenure by country and career level. The query exhibits poor partition pruning and runs on an X-Small warehouse.

What improvement meets the requirements with the LEAST operational overhead?

A. Add clustering keys on COUNTRY and EMPLOYMENT_STATUS.

B. Enable Query Acceleration Service (QAS).

C. Enable search optimization on equality predicates for COUNTRY and EMPLOYMENT_STATUS.

D. Build a materialized view for latest active employee records per country.

Answer: C

The query filters on equality predicates for COUNTRY, EMPLOYMENT_STATUS, and a specific EFFECTIVE_DATE value representing the latest record. Search Optimization Service (SOS) is specifically designed to accelerate highly selective equality predicates without requiring data reorganization (Answer C).

Clustering would introduce ongoing maintenance overhead and is less effective when cardinality is low or when multiple filter combinations exist. Query Acceleration Service helps with overall query execution time but does not directly improve partition pruning. Materialized views would require ongoing maintenance and additional storage, increasing operational complexity.

For SnowPro Architect candidates, this question highlights choosing the simplest effective optimization tool—SOS—when dealing with selective filters and large tables.

=========

QUESTION NO: 44 [Snowflake Data Engineering]

A team needs to recover data after pipeline failures or business rule violations.

Requirements:

• Database recoverable for 48 hours

• Analytics schema recoverable for 5 days

What is the correct approach?

A. Use custom stored procedure backups.

B. Use tasks with database and schema clones.

C. Use Time Travel with MIN_DATA_RETENTION_TIME_IN_DAYS = 2 and DATA_RETENTION_TIME_IN_DAYS = 5 on the ANALYTICS schema.

D. Use Time Travel with DATA_RETENTION_TIME_IN_DAYS = 2 and MIN_DATA_RETENTION_TIME_IN_DAYS = 5 on the ANALYTICS schema.

Answer: C

Snowflake Time Travel enables point-in-time recovery without manual backups. Setting MIN_DATA_RETENTION_TIME_IN_DAYS = 2 ensures that all database objects retain at least 48 hours of recoverable history. Extending DATA_RETENTION_TIME_IN_DAYS = 5 at the schema level allows longer recovery specifically for the analytics schema (Answer C).

This approach meets both recovery requirements with minimal operational overhead and aligns with Snowflake best practices. Cloning via tasks introduces unnecessary complexity and storage management. Custom backups are not required due to built-in Time Travel capabilities.

SnowPro Architect exams emphasize using native Snowflake features such as Time Travel for data recovery scenarios.

=========

QUESTION NO: 45 [Security and Access Management]

A financial services company needs to isolate sensitive production data from development data within the same region and support secure data transfer between environments.

What is the best solution?

A. Create two accounts with network policies and use data sharing.

B. Create two accounts with federated authentication and use cloning.

C. Create two databases in one account and use replication.

D. Create two databases in one account and use user-level network policies and shares.

Answer: A

Strong isolation of sensitive production data from development environments is best achieved using separate Snowflake accounts. Account-level isolation ensures independent security boundaries, network policies, and governance controls. Secure Data Sharing allows controlled, read-only access to production data without copying it, supporting safe data transfer between environments (Answer A).

Using cloning across accounts is not supported; cloning works only within the same account. Database-level separation does not provide the same security guarantees as account-level isolation, and user-level network policies are not supported in Snowflake.

This design aligns with SnowPro Architect best practices for regulated industries, emphasizing strong isolation, least privilege, and secure data access mechanisms.