Free Snowflake ARA-C01 Practice Exam with Questions & Answers

Snowflake event tables capture logs, traces, and metrics generated by Snowflake features such as stored procedures, functions, and dynamic tables. When a database does not have an explicitly associated event table, Snowflake writes telemetry data to the default account-level event table, which is exposed through SNOWFLAKE.TELEMETRY.EVENTS.

Since the problematic stored procedure resides in marketing_db, and that database is not associated with its own event table, the relevant telemetry data will not appear in sales_db’s event table. Instead, it will be recorded in the account-level telemetry view. Querying SNOWFLAKE.TELEMETRY.EVENTS allows the Architect to analyze logs and traces across databases within the same account.

Creating additional event tables is unnecessary for investigation, and querying MARKETING_DB.TELEMETRY.EVENTS is invalid because no such association exists. From a SnowPro Architect perspective, this question validates understanding of event table scope, default telemetry behavior, and effective troubleshooting of Snowflake-native observability features.

=========

QUESTION NO: 27 [Snowflake Data Engineering]

A data ingestion pipeline loads data randomly throughout the day. A requirement states that data must be processed within 10 minutes of landing in Snowflake.

What is the MOST cost-effective solution?

A. Use a third-party orchestration tool to run the pipeline every 5 minutes.

B. Create views containing transformation logic.

C. Create a task that runs every 5 minutes and processes new rows.

D. Create a stream on the table and a task that runs every 5 minutes using SYSTEM$STREAM_HAS_DATA.

Answer: D

Streams and tasks are Snowflake’s native mechanism for incremental data processing. A stream tracks row-level changes (inserts, updates, deletes) on a table, while tasks enable scheduled or conditional execution of SQL statements.

Using SYSTEM$STREAM_HAS_DATA allows the task to run only when new data is available, avoiding unnecessary compute usage (Answer D). This makes the solution both timely—meeting the 10-minute SLA—and cost-efficient, as compute resources are consumed only when there is work to do.

Views alone do not materialize transformations and still require compute at query time. Third-party orchestration introduces unnecessary operational overhead and cost. Running a task on a fixed schedule without checking stream data would waste compute cycles.

This pattern—streams + tasks with conditional execution—is a core SnowPro Architect design pattern for efficient, low-latency data pipelines.

=========

QUESTION NO: 28 [Performance Optimization and Monitoring]

A query shows a high number of bytes spilled to remote storage. The query includes a WHERE clause that filters out a large percentage of rows.

What actions can reduce spillage and improve performance? (Select TWO).

A. Increase the size of the virtual warehouse.

B. Increase the maximum number of clusters.

C. Define a clustering key on columns used for selective filtering.

D. Define primary and foreign keys.

E. Decrease the data retention period.

Answer: A, C

Bytes spilled to remote storage indicate memory pressure during query execution. Increasing the warehouse size provides more memory and compute resources, reducing the likelihood of spilling (Answer A).

Additionally, defining clustering keys on highly selective filter columns improves micro-partition pruning, which reduces the volume of data scanned and processed in memory (Answer C). Together, these actions address both the root cause (too much data scanned) and the symptom (insufficient memory).

Increasing clusters improves concurrency, not per-query memory. Primary and foreign keys are informational only in Snowflake and do not affect execution plans. Retention settings have no impact on query execution.

This question reinforces SnowPro Architect performance diagnostics: understand both physical execution (memory, spill) and logical data access (pruning).

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 -<accountLocator> 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 -<accountLocator> 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 -<connection_name> URL, which does not support client redirect and requires manual updates to the connection string in case of a failover. References:

[Snowflake Editions] 1

[Replication and Failover/Failback] 2

[Client Redirect] 3

[Snowflake Account Identifiers] 4

The Snowflake Connector for Python is designed to integrate Snowflake with Python-based analytics, ETL, and application workloads. One key capability is its support for both synchronous and asynchronous query execution, which allows architects to design scalable pipelines and applications that can submit long-running queries without blocking execution threads (Answer B). This is particularly important for large data sets and complex analytical workloads, where asynchronous execution improves throughput and application responsiveness.

Additionally, the connector automatically converts Snowflake data types into native Python data types wherever possible (Answer D). For example, VARCHAR values are returned as Python strings, numeric values as Python numeric types, and timestamps as Python datetime objects. This default behavior simplifies downstream processing and analysis, eliminating the need for manual casting or parsing in most use cases.

The connector does not convert all values to strings by default, nor does it specifically convert NUMBER to DECIMAL as a required behavior; instead, type conversion is handled intelligently to match Python equivalents. While cursors are used to execute queries, this is standard DB-API behavior and not a distinguishing feature for performance or architecture decisions. For SnowPro Architect candidates, understanding these connector capabilities is essential when designing Python-based data engineering or analytics solutions on Snowflake.

=========

QUESTION NO: 7 [Security and Access Management]

Which parameters can only be set at the account level? (Select TWO).

A. DATA_RETENTION_TIME_IN_DAYS

B. ENFORCE_SESSION_POLICY

C. MAX_CONCURRENCY_LEVEL

D. PERIODIC_DATA_REKEYING

E. TIMESTAMP_INPUT_FORMAT

Answer: B, D

Snowflake parameters exist at different levels of the hierarchy, including account, user, session, warehouse, database, schema, and object levels. However, some parameters are intentionally restricted to the account level because they enforce global security or compliance behavior across the entire Snowflake environment.

ENFORCE_SESSION_POLICY is an account-level parameter that determines whether session policies (such as authentication or session controls) are enforced across all users. Because this impacts authentication and session behavior globally, it cannot be overridden at lower scopes (Answer B).

PERIODIC_DATA_REKEYING is another account-level-only parameter. It controls automatic re-encryption (rekeying) of data to meet strict compliance and security requirements. Rekeying affects all encrypted data in the account and must therefore be centrally managed at the account level (Answer D).

By contrast, DATA_RETENTION_TIME_IN_DAYS can be set at multiple levels (account, database, schema, and table). MAX_CONCURRENCY_LEVEL is a warehouse-level parameter, and TIMESTAMP_INPUT_FORMAT can be set at account, user, or session levels. From a SnowPro Architect perspective, understanding which parameters are global versus scoped is critical for designing secure, compliant, and governable Snowflake architectures.

=========

QUESTION NO: 8 [Snowflake Data Engineering]

A MERGE statement is designed to return duplicated values of a column ID in a USING clause. The column ID is used in the merge condition. The MERGE statement contains these two clauses:

WHEN NOT MATCHED THEN INSERT

WHEN MATCHED THEN UPDATE

What will be the result when this query is run?

A. The MERGE statement will run successfully using the default parameter settings.

B. If the value of the ID is present in the target table, all occurrences will be updated.

C. If the value of the ID is present in the target table, only the first occurrence will be updated.

D. If the ERROR_ON_NONDETERMINISTIC_MERGE = FALSE parameter is set, the MERGE statement will run successfully.

Answer: D

In Snowflake, MERGE statements require deterministic behavior when matching rows between the source (USING clause) and the target table. If the USING clause contains duplicate values for the join condition (in this case, column ID), Snowflake cannot deterministically decide which source row should update or insert into the target. By default, this results in an error to prevent unintended data corruption.

Snowflake provides the parameter ERROR_ON_NONDETERMINISTIC_MERGE to control this behavior. When set to TRUE (the default), Snowflake raises an error if nondeterministic matches are detected. When this parameter is explicitly set to FALSE, Snowflake allows the MERGE statement to run successfully even when duplicate keys exist in the source, accepting the nondeterministic outcome (Answer D).

Snowflake does not guarantee updating all or only the first occurrence in such cases; instead, the behavior is undefined unless the parameter is adjusted. This question tests an architect’s understanding of data correctness, deterministic processing, and safe data engineering practices—key topics within the SnowPro Architect exam scope.

=========

QUESTION NO: 9 [Snowflake Data Engineering]

An Architect needs to define a table structure for an unfamiliar semi-structured data set. The Architect wants to identify a list of distinct key names present in the semi-structured objects.

What function should be used?

A. FLATTEN with the RECURSIVE argument

B. INFER_SCHEMA

C. PARSE_JSON

D. RESULT_SCAN

Answer: A

When working with unfamiliar semi-structured data such as JSON, a common first step is to explore its structure and identify all possible keys. Snowflake’s FLATTEN function is specifically designed to explode VARIANT, OBJECT, or ARRAY data into relational form. Using the RECURSIVE option allows FLATTEN to traverse nested objects and arrays, returning all nested keys regardless of depth (Answer A).

This approach enables architects to query and aggregate distinct key names, making it ideal for schema discovery and exploratory analysis. INFER_SCHEMA, by contrast, is used primarily with staged files to infer column definitions for external tables or COPY operations, not for exploring existing VARIANT data already stored in tables. PARSE_JSON simply converts a string into a VARIANT type and does not help identify keys. RESULT_SCAN is used to query the results of a previously executed query and is unrelated to schema discovery.

For SnowPro Architect candidates, this highlights an important semi-structured data design pattern: using FLATTEN (often with RECURSIVE) to explore, profile, and understand evolving data structures before committing to a relational schema or transformation pipeline.

=========

QUESTION NO: 10 [Security and Access Management]

A global retail company must ensure comprehensive data governance, security, and compliance with various international regulations while using Snowflake for data warehousing and analytics.

What should an Architect do to meet these requirements? (Select TWO).

A. Create a network policy at the column level to secure the data.

B. Use column-level security to restrict access to specific columns.

C. Store encryption keys on an external server to manage encryption manually.

D. Implement Role-Based Access Control (RBAC) to assign roles and permissions.

E. Enable Secure Data Sharing with external partners for collaborative purposes.

Answer: B, D

Snowflake provides built-in governance and security mechanisms that align with global regulatory requirements. Column-level security—implemented through features such as dynamic data masking and row access policies—allows architects to restrict access to sensitive data at a granular level based on roles or conditions (Answer B). This is essential for compliance with regulations such as GDPR, HIPAA, and similar frameworks that require limiting access to personally identifiable or sensitive data.

Role-Based Access Control (RBAC) is the foundation of Snowflake’s security model and is critical for governing who can access which data and perform which actions (Answer D). By assigning privileges to roles instead of users, organizations can centrally manage permissions, enforce separation of duties, and audit access more effectively.

Snowflake does not support column-level network policies, and encryption keys are managed by Snowflake (or via Tri-Secret Secure), not manually by customers. Secure Data Sharing is useful for collaboration but is not a core requirement for governance and compliance in this scenario. For the SnowPro Architect exam, mastering RBAC and column-level security is essential for designing compliant and secure Snowflake architectures.

 The warehouse MY_WH will only be active when there are results in the stream. This is because the task is created based on a stream, which means that the task will only be executed when there are new data in the stream. Additionally, the warehouse is set to auto_suspend - 60, which means that the warehouse will automatically suspend after 60 seconds of inactivity. Therefore, the warehouse will only be active when there are results in the stream. References:

[CREATE TASK | Snowflake Documentation]

[Using Streams and Tasks | Snowflake Documentation]

[CREATE WAREHOUSE | Snowflake Documentation]

According to the Snowflake documentation1, the Business Critical edition offers the following features that are relevant to the question:

Support for Multi-Factor Authentication (MFA): This is a standard feature available in all Snowflake editions1.

A minimum of 2 months of Time Travel availability: This is an enterprise feature that allows users to access historical data for up to 90 days1.

Database replication in between different regions: This is an enterprise feature that enables users to replicate databases across different regions or cloud platforms1.

Native support for JDBC and ODBC: This is a standard feature available in all Snowflake editions1.

Customer-managed encryption keys using Tri-Secret Secure: This is a business critical feature that provides enhanced security and data protection by allowing customers to manage their own encryption keys1.

Support for Payment Card Industry Data Security Standards (PCI DSS): This is a business critical feature that ensures compliance with PCI DSS regulations for handling sensitive cardholder data1.

Therefore, the minimum Snowflake edition that should be selected during account creation to provide all the listed services is the Business Critical edition.

 According to the Snowflake documentation, materialized views have some limitations on the query specification that defines them. One of these limitations is that they cannot include nested subqueries, such as subqueries in the FROM clause or scalar subqueries in the SELECT list. Another limitation is that they cannot include ORDER BY clauses, context functions (such as CURRENT_TIME()), or outer joins. However, materialized views can support MIN and MAX aggregates, as well as other aggregate functions, such as SUM, COUNT, and AVG.

Limitations on Creating Materialized Views | Snowflake Documentation

Working with Materialized Views | Snowflake Documentation

https://docs.snowflake.com/en/user-guide/data-sharing-intro

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.

Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses

Snowflake Documentation: MAX_CONCURRENCY_LEVEL

The best design to store and analyze large amounts of structured and semi-structured data for different business units and departments is to use a centralized Snowflake database for core business data, and use separate databases for departmental or project-specific data. This design allows for scalability, security, and cost efficiency by leveraging Snowflake’s features such as:

Database cloning: Cloning a database creates a zero-copy clone that shares the same data files as the original database, but can be modified independently. This reduces storage costs and enables fast and consistent data replication for different purposes.

Database sharing: Sharing a database allows granting secure and governed access to a subset of data in a database to other Snowflake accounts or consumers. This enables data collaboration and monetization across different business units or external partners.

Warehouse scaling: Scaling a warehouse allows adjusting the size and concurrency of a warehouse to match the performance and cost requirements of different workloads. This enables optimal resource utilization and flexibility for different data analysis needs. References: Snowflake Documentation: Database Cloning, Snowflake Documentation: Database Sharing, [Snowflake Documentation: Warehouse Scaling]

A healthcare company that handles PHI data must ensure compliance with relevant privacy standards, such as HIPAA, HITRUST, and GDPR. Snowflake provides several features and best practices to help customers meet their data protection and compliance requirements1.

One best practice recommendation is to use, at minimum, the Business Critical edition of Snowflake. This edition provides the highest level of data protection and security, including end-to-end encryption with customer-managed keys, enhanced object-level security, and HIPAA and HITRUST compliance2. Therefore, option A is correct.

Another best practice recommendation is to create Dynamic Data Masking policies and apply them to columns that contain PHI. Dynamic Data Masking is a feature that allows masking or redacting sensitive data based on the current user’s role. This way, only authorized users can view the unmasked data, while others will see masked values, such as NULL, asterisks, or random characters3. Therefore, option B is correct.

A third best practice recommendation is to use the External Tokenization feature to obfuscate sensitive data. External Tokenization is a feature that allows replacing sensitive data with tokens that are generated and stored by an external service, such as Protegrity. This way, the original data is never stored or processed by Snowflake, and only authorized users can access the tokenized data through the external service4. Therefore, option D is correct.

Option C is incorrect, because the Internal Tokenization feature is not available in Snowflake. Snowflake does not provide any native tokenization functionality, but only supports integration with external tokenization services4.

Option E is incorrect, because rewriting SQL queries to eliminate projections of PHI data based on current_role() is not a best practice. This approach is error-prone, inefficient, and hard to maintain. A better alternative is to use Dynamic Data Masking policies, which can automatically mask data based on the user’s role without modifying the queries3.

Option F is incorrect, because avoiding sharing data with partner organizations is not a best practice. Snowflake enables secure and governed data sharing with internal and external consumers, such as business units, customers, or partners. Data sharing does not involve copying or moving data, but only granting access privileges to the shared objects. Data sharing can also leverage Dynamic Data Masking and External Tokenization features to protect sensitive data5.

 Snowflake’s Security & Compliance Reports : Snowflake Editions : Dynamic Data Masking : External Tokenization : Secure Data Sharing