ARA-C01 Practice Test 15 — 15 Questions

Question 1

An architect needs to audit all queries executed by users in the last 30 days. Which approach is most efficient?

Accepted Answer

Query the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view.. The SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view is the standard interface for auditing executed queries. It provides a comprehensive historical record of all SQL commands, their execution times, and the users who ran them. This is the most efficient and scalable method for auditing compared to manual logs or local metadata, as it is maintained and optimized by Snowflake, ensuring minimal impact on production query performance.

Answer

Query the INFORMATION_SCHEMA.QUERY_HISTORY view.

Answer

Enable query logging in the user profile.

Answer

Use the GET_QUERY_HISTORY() function.

Question 2

While reviewing a Query Profile, an architect notices that a 'Join' operator is consuming 90% of the total execution time, and one specific node in the warehouse is processing significantly more rows than others. What is the most likely cause and the best resolution?

Accepted Answer

The query is experiencing data skew; investigate the join key distribution.. Data skew occurs when the data is not evenly distributed across the join key. This causes one node in the warehouse to perform the majority of the work while others remain idle. To resolve this, the architect should consider using a different join key or applying a 'skew hint' if supported, or pre-aggregating the skewed data.

Answer

The warehouse size is too small; increase it to a larger T-shirt size.

Answer

The Result Cache is cold; run the query again to populate the cache.

Answer

The table is not clustered; apply a clustering key to the join column.

Question 3

A Snowpipe is configured to load data from an S3 bucket. The architect notices that some files are failing to load due to a schema mismatch, but no alerts are being generated. What is the most robust way to implement automated error notification for Snowpipe?

Accepted Answer

Configure the ERROR_INTEGRATION parameter on the PIPE object. Snowpipe error notifications allow architects to push alerts to a cloud messaging service when errors occur. By configuring the ERROR_INTEGRATION parameter in the Pipe definition, Snowflake can send detailed JSON error reports to services like Amazon SNS, which can then trigger emails or Slack alerts for the engineering team.

Answer

Schedule a Task to query the VALIDATE_PIPE_LOAD function every hour

Answer

Use a Stream on the target table to track failed insert attempts

Answer

Set the Snowpipe parameter ON_ERROR = 'NOTIFY'

Question 4

A retailer has a 50TB table containing transaction logs. Users frequently query specific transaction_id values (highly selective point lookups) but also run daily reports aggregated by transaction_date. The table is currently clustered by transaction_date. What is the most cost-effective way to improve point lookup performance without degrading report performance?

Accepted Answer

Enable the Search Optimization Service on the transaction_id column.. Point lookups on high-cardinality columns like IDs benefit significantly from the Search Optimization Service because it creates a persistent data structure to locate specific rows without scanning entire micro-partitions. Unlike re-clustering, SOS does not change the physical layout of the data, allowing the existing clustering on date to remain optimal for range-based analytical reporting while drastically reducing latency for needle-in-a-haystack queries.

Answer

Re-cluster the table by transaction_id and transaction_date.

Answer

Increase the Virtual Warehouse size to X-Large.

Answer

Create a Materialized View on transaction_id.

Question 5

A large retail organization is experiencing slow performance on a specific set of complex queries that filter by a non-clustered high-cardinality timestamp column. Which architectural feature of Snowflake should the architect prioritize to optimize these specific lookups without manually rebuilding the entire table structure?

Accepted Answer

Enabling the Search Optimization Service for the specific table and column.. Snowflake utilizes Search Optimization Service (SOS) as a background process that creates a specialized persistent data structure to accelerate point lookup queries on specific columns. This is particularly effective for high-cardinality columns where traditional micro-partition pruning is less effective. SOS operates independently of the table clustering, providing a maintenance-free way to improve performance for highly selective filters in large datasets.

Answer

Manually re-sorting the data using an ORDER BY clause during a table rewrite.

Answer

Implementing a Materialized View on the timestamp column.

Answer

Increasing the size of the Virtual Warehouse to a larger T-shirt size.

Question 6

What is the function of the 'Snowflake Data Storage' layer in the overall architecture?

Accepted Answer

It stores and organizes data in a proprietary, columnar format.. The Data Storage layer is responsible for the persistent storage of data, which is organized into micro-partitions and stored in a proprietary, compressed, and columnar format. It is completely managed by Snowflake, meaning users do not need to worry about storage management, file indexing, or configuration. This layer acts as the foundation, allowing the compute layer to access data independently while maintaining high reliability, durability, and performance for various analytical workloads.

Answer

It manages user connections and sessions.

Answer

It executes queries and processes data transformations.

Answer

It keeps track of query history and metadata.

Question 7

An architect is tasked with auditing all failed login attempts for a security investigation. Which view should be queried?

Accepted Answer

LOGIN_HISTORY. The ACCOUNT_USAGE schema provides historical metadata about account activity. Specifically, the LOGIN_HISTORY view records all login attempts, including successes and failures, the client used, and the source IP address. Querying this view is essential for security architects to identify brute-force attacks or anomalous behavior, allowing them to take proactive measures such as updating network policies or flagging compromised accounts, thereby maintaining the overall integrity and security posture of the Snowflake environment.

Answer

QUERY_HISTORY

Answer

ACCESS_HISTORY

Answer

SESSIONS

Question 8

Refer to the exhibit. Based on the security integration definition provided, what is the primary purpose of the 'token_user_field' parameter in this specific configuration?

Accepted Answer

It defines the field in the OAuth token that Snowflake uses to match against the login_name of a Snowflake user.. External OAuth allows third-party identity providers like Microsoft Entra ID to authorize access to Snowflake. The `token_user_field` is a critical configuration parameter that tells Snowflake which claim in the incoming JWT (JSON Web Token) should be used to identify the Snowflake user. In this exhibit, setting it to 'upn' ensures the user identity is mapped correctly from the Microsoft environment.

Answer

It specifies the Snowflake role that the user will be assigned when they connect via the OAuth provider.

Answer

It identifies the public key used to verify the digital signature of the incoming OAuth access token.

Answer

It determines the audience for which the token was issued to prevent token substitution attacks.

Question 9

Refer to the exhibit. A user runs a query that returns results in 0.2 seconds and shows 'Result Cache used' in the Query Profile. Where did Snowflake retrieve this data from?

Accepted Answer

The Cloud Services layer.. When Snowflake detects a query that matches a previously executed result set, it uses the Result Cache located in the Cloud Services layer. This avoids spinning up or utilizing a Virtual Warehouse, which is why the execution time is extremely low. Understanding this mechanism is crucial for architects to optimize costs by leveraging existing cached result sets instead of re-running compute-heavy analytical queries unnecessarily.

Answer

The Virtual Warehouse local SSD cache.

Answer

The centralized cloud storage bucket.

Answer

The local disk of the user's client.

Question 10

Which TWO of the following statements correctly describe the behavior of Key Pair Authentication in Snowflake?

Accepted Answer

The public key must be associated with the user profile.. Key Pair Authentication replaces traditional password authentication with a cryptographic approach using a private key stored locally and a public key registered in Snowflake. This is essential for automated services and programmatic access where hardcoded passwords pose security risks. By rotating keys and managing the public key in the user object, security architects can implement high-assurance machine-to-machine authentication protocols that eliminate the risks of credential leakage.

Answer

The private key is stored in the Snowflake user object.

Answer

Key pair authentication is only supported for the ACCOUNTADMIN role.

Answer

Snowflake manages the generation of the private key.

Question 11

An architect needs to implement a Change Data Capture (CDC) process for data stored in an external S3 bucket without moving all data into Snowflake first. Which TWO features must be combined to track new and modified files efficiently? (Select TWO)

Accepted Answer

Directory Tables with AUTO_REFRESH = TRUE. Capturing changes from external tables requires a combination of directory tables and streams. Directory tables provide a catalog of files in the stage, while streams track the metadata changes. This architecture allows Snowflake to react to new files landing in cloud storage without needing to constantly poll the entire bucket.

Answer

Snowpipe with a custom REST API integration

Answer

Materialized Views on the External Table

Answer

External Functions to trigger AWS Lambda

Question 12

A query that usually takes 10 seconds is now taking 5 minutes. The Query Profile shows that 'Local Disk Spilling' is high. What does this indicate about the warehouse and the query?

Accepted Answer

The data processed by the query exceeds the available memory on the warehouse nodes.. Disk spilling occurs when the memory (RAM) available on the Virtual Warehouse nodes is exhausted by the query's intermediate data, such as large joins or sorts. The system first spills to local SSD (Local Spilling) and, if that is also full, to remote storage. This is a clear sign that the warehouse size is too small for the query's memory requirements.

Answer

The query is waiting for metadata locks from the Cloud Services layer.

Answer

The network bandwidth between the warehouse and the storage layer is throttled.

Answer

The Result Cache has been invalidated by a concurrent DML operation.

Question 13

A company is moving towards an Open Data Lakehouse architecture using Snowflake Iceberg Tables. They want to ensure that the data is stored in Parquet format in their own S3 bucket but still benefit from Snowflake's performance. Which configuration should the architect recommend for the Iceberg Table's catalog?

Accepted Answer

Set the CATALOG to 'SNOWFLAKE' and specify an EXTERNAL_VOLUME.. Snowflake supports two catalog options for Iceberg tables: Snowflake and External. Using Snowflake as the catalog allows Snowflake to manage the metadata and perform full DML operations, which provides the best performance and integration while still keeping the data in an open format in the customer's cloud storage.

Answer

Set the CATALOG to 'AWS_GLUE' to ensure that other AWS services can manage the metadata.

Answer

Use the 'EXTERNAL' catalog type and point to a Snowflake Managed Iceberg Catalog.

Answer

Create a standard Snowflake table and use a periodic Task to export the data to S3 in Iceberg format.

Question 14

An architect is investigating query performance issues where queries are spilling to local disk. Which TWO actions would most effectively mitigate this issue?

Accepted Answer

Resize the virtual warehouse to a larger size.. Spilling to disk occurs when the data required for an operation (like a Join or Sort) exceeds the memory available in the warehouse's compute nodes. By scaling up the warehouse, you increase the memory capacity per node. Alternatively, optimizing the query logic to reduce the volume of data being shuffled or sorted ensures that intermediate result sets fit within the available memory heap of the virtual warehouse.

Answer

Increase the warehouse multi-cluster scale factor.

Answer

Enable query result cache.

Answer

Convert the table to a temporary table.

Question 15

Which Snowflake feature should be used to monitor and identify slow-running queries across the entire account for performance tuning?

Accepted Answer

Query History in the web interface.. The Query History page in the Snowflake web interface or the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view are the standard tools for monitoring. They allow administrators to inspect execution time, bytes scanned, partition pruning efficiency, and spilling events. These metrics are vital for identifying bottlenecks and determining which queries require optimization, such as adding clustering keys or adjusting warehouse sizes for better throughput.

Answer

Snowflake Data Marketplace.

Answer

Warehouse auto-suspend settings.

Answer

Snowpipe auto-ingest configuration.