Free Snowflake ARA-C01 Practice Exam with Questions & Answers | Set: 4

This question tests understanding of Snowflake Role-Based Access Control (RBAC) and privilege inheritance, which is a core SnowPro Architect exam topic. In Snowflake, privileges are not granted directly to users; instead, they are granted to roles, which are then assigned to users. Effective access depends on the combination of USAGE and SELECT privileges across databases, schemas, and tables.

In the provided design, the role DB_IT_RO_ROLE has SELECT privileges on all tables in the database, along with database usage. This role is granted to IT_ANALYST_ROLE, which is assigned to User1. As a result, User1 can read tables from both IT_SCHEMA and FINANCE_SCHEMA, assuming schema usage is satisfied, which is implied in the diagram.

User2, on the other hand, is granted the FIN_ANALYST_ROLE, which inherits privileges from DB_FIN_ROLE. That role only has USAGE and SELECT privileges on the FINANCE_SCHEMA. There are no grants that allow DB_FIN_ROLE (or FIN_ANALYST_ROLE) to access objects in IT_SCHEMA.

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.

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.

The correct answer is option B. This query is designed to assess the need for a multi-cluster warehouse by examining the queuing time (AVG_QUEUED_LOAD) on different days and virtual warehouses. When theAVG_QUEUED_LOADis greater than zero, it suggests that queries are waiting for resources, which can be an indicator that performance might be improved by using a multi-cluster warehouse to handle the workload more efficiently. By grouping by date and warehouse name and filtering on the sum of the average queued load being greater than zero, the query identifies specific days and warehouses where the workload exceeded the available compute resources. This information is valuable when considering scaling out warehouses to multi-cluster configurations for improved performance.

 A star schema model is a type of dimensional data model that consists of a single fact table and multiple dimension tables. A 3NF model is a type of relational data model that follows the third normal form, which eliminates data redundancy and ensures referential integrity. A Snowflake Architect might use a star schema model rather than a 3NF model when designing a data architecture to run in Snowflake for the following reasons:

A star schema model is more suitable for analytical queries that require aggregating and slicing data across different dimensions, such as those performed by a BI tool. A 3NF model is more suitable for transactional queries that require inserting, updating, and deleting individual records.

A star schema model is simpler and faster to query than a 3NF model, as it involves fewer joins and less complex SQL statements. A 3NF model is more complex and slower to query, as it involves more joins and more complex SQL statements.

A star schema model can provide a simple flattened single view of the data to a particular group of end users, such as business analysts or data scientists, who need to explore and visualize the data. A 3NF model can provide a more detailed and normalized view of the data to a different group of end users, such as application developers or data engineers, who need to maintain and update the data.

The other options are not valid reasons for choosing a star schema model over a 3NF model in Snowflake:

Snowflake can handle the joins implied in a 3NF data model, as it supports ANSI SQL and has a powerful query engine that can optimize and execute complex queries efficiently.

The Architect can use both star schema and 3NF models to remove data duplication from the data stored in Snowflake, as both models can enforce data integrity and avoid data anomalies. However, the trade-off is that a star schema model may have more data redundancy than a 3NF model, as it denormalizes the data for faster query performance, while a 3NF model may have less data redundancy than a star schema model, as it normalizes the data for easier data maintenance.

The Architect can use both star schema and 3NF models to design a landing zone to receive raw data into Snowflake, as both models can accommodate different types of data sources and formats. However, the choice of the model may depend on the purpose and scope of the landing zone, such as whether it is a temporary or permanent storage, whether it is a staging area or a data lake, and whether it is a single source or a multi-source integration.

Snowflake Architect Training

Data Modeling: Understanding the Star and Snowflake Schemas

Data Vault vs Star Schema vs Third Normal Form: Which Data Model to Use?

Star Schema vs Snowflake Schema: 5 Key Differences

Dimensional Data Modeling - Snowflake schema

Star schema vs Snowflake Schema

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.

References =

•Snowflake Documentation on creating schemas1

•Snowflake Documentation on configuring access control2

•Snowflake Documentation on understanding and viewing Fail-safe3