Snowflake ARA-C01 - SnowPro Advanced: Architect Certification Exam

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 accommodate the diverse needs of different teams and use cases within a company, a flexible and multi-faceted approach to data modeling is required.

Option B:By creating a raw database for landing and persisting raw data, you ensure that the Data Science team has access to unprocessed data for machine learning model development. This aligns with the best practices of having a staging area or raw data zone in a modern data architecture where raw data is ingested before being transformed or processed for different use cases.

Option C:Having profile-specific databases means creating targeted databases that are designed to meet the specific requirements of each user profile or team within the company. For the Finance and Vendor Management teams, the data can be structured and optimized for reporting and visualization. For the Sales team, the database can include engineered and protected data that is suitable for data monetization efforts. This strategy not only aligns data with usage patterns but also helps in managing data access and security policies effectively.

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.

According to the Snowflake documentation, object parameters are parameters that can be set on individual objects such as databases, schemas, tables, and stages. Object parameters can be set by users with the appropriate privileges on the objects. For example, to set the object parameter AUTO_REFRESH on a table, the user must have the MODIFY privilege on the table. The ACCOUNTADMIN role has the highest level of privileges on all objects in the account, so it can set any object parameter on any object. However, other roles, such as SECURITYADMIN or SYSADMIN, do not have the same level of privileges on all objects, so they cannot set object parameters on objects they do not own or have the required privileges on. Therefore, the correct answer is C. ACCOUNTADMIN, USER with PRIVILEGE.

Parameters | Snowflake Documentation

Object Parameters | Snowflake Documentation

Object Privileges | Snowflake Documentation

A transient table is a type of table in Snowflake that does not have a Fail-safe period and can have a Time Travel retention period of either 0 or 1 day. Transient tables are suitable for temporary or intermediate data that can be easily reproduced or replicated1.

A temporary table is a type of table in Snowflake that is automatically dropped when the session ends or the current user logs out. Temporary tables do not incur any storage costs, but they are not visible to other users or sessions2.

A permanent table is a type of table in Snowflake that has a Fail-safe period and a Time Travel retention period of up to 90 days. Permanent tables are suitable for persistent and durable data that needs to be protected from accidental or malicious deletion3.

In this case, the use case requires loading some data that can be visualized through Tableau. The data is updated every day and the old data is no longer required. Therefore, the best type of table to use in this case to optimize cost is a transient table, because it does not incur any Fail-safe costs and it can have a short Time Travel retention period of 0 or 1 day. This way, the data can be loaded and queried by Tableau, and then deleted or overwritten without incurring any unnecessary storage costs.

 Transient Tables : Temporary Tables : Understanding & Using Time Travel

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.

References =

•Snowflake’s documentation on multi-table inserts1

•Blog post on optimizing Data Vault architecture on Snowflake2

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

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

According to the Snowflake documentation, the following are some considerations for choosing clustering for a table1:

Clustering is optimal when either:

You require the fastest possible response times, regardless of cost.

Your improved query performance offsets the credits required to cluster and maintain the table.

Clustering is most effective when the clustering key is used in the following types of query predicates:

Filter predicates (e.g. WHERE clauses)

Join predicates (e.g. ON clauses)

Grouping predicates (e.g. GROUP BY clauses)

Sorting predicates (e.g. ORDER BY clauses)

Clustering is less effective when the clustering key is not used in any of the above query predicates, or when the clustering key is used in a predicate that requires a function or expression to be applied to the key (e.g. DATE_TRUNC, TO_CHAR, etc.).

For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key. Adding more than 3-4 columns tends to increase costs more than benefits.

Based on these considerations, the best option for the clustering key columns is C. C1, C3, C2, because:

These columns are heavily used in filter and join conditions of SELECT queries, which are the most effective types of predicates for clustering.

These columns have high cardinality, which means they have many distinct values and can help reduce the clustering skew and improve the compression ratio.

These columns are likely to be correlated with each other, which means they can help co-locate similar rows in the same micro-partitions and improve the scan efficiency.

These columns do not require any functions or expressions to be applied to them, which means they can be directly used in the predicates without affecting the clustering.

 1: Considerations for Choosing Clustering for a Table | Snowflake Documentation

 Partitioning an external table is a technique that improves the performance of queries executed against the table by reducing the amount of data scanned. Partitioning an external table involves creating one or more partition columns that define how the table is logically divided into subsets of data based on the values in those columns. The partition columns can be derived from the file metadata (such as file name, path, size, or modification time) or from the file content (such as a column value or a JSON attribute). Partitioning an external table allows the query optimizer to prune the files that do not match the query predicates, thus avoiding unnecessary data scanning and processing2

The other options are not effective steps for improving the performance of queries executed against an external table:

Shorten the names of the source files. This option does not have any impact on the query performance, as the file names are not used for query processing. The file names are only used for creating the external table and displaying the query results3

Convert the source files’ character encoding to UTF-8. This option does not affect the query performance, as Snowflake supports various character encodings for external table files, such as UTF-8, UTF-16, UTF-32, ISO-8859-1, and Windows-1252. Snowflake automatically detects the character encoding of the files and converts them to UTF-8 internally for query processing4

Use an internal stage instead of an external stage to store the source files. This option is not applicable, as external tables can only reference files stored in external stages, such as Amazon S3, Google Cloud Storage, or Azure Blob Storage. Internal stages are used for loading data into internal tables, not external tables5 References:

1: SnowPro Advanced: Architect | Study Guide

2: Snowflake Documentation | Partitioning External Tables

3: Snowflake Documentation | Creating External Tables

4: Snowflake Documentation | Supported File Formats and Compression for Staged Data Files

5: Snowflake Documentation | Overview of Stages

 SnowPro Advanced: Architect | Study Guide

 Partitioning External Tables

 Creating External Tables

 Supported File Formats and Compression for Staged Data Files

 Overview of Stages