ARA-C01 Practice Test 2 — 25 Questions

Question 1

A financial services firm uses Snowflake to store transactional data. The data engineering team needs to implement a process that automatically copies new files from an external stage into a raw table and then triggers a series of dependent transformations. The team wants to minimize manual intervention and ensure that transformations run only when new data is available. They also want to avoid running transformations on empty batches. Which combination of Snowflake features should the architect use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Snowpipe with a stream on the raw table and a task with a WHEN clause checking SYSTEM$STREAM_HAS_DATA.. The combination of Snowpipe, a stream on the raw table, and a task with a WHEN clause using SYSTEM$STREAM_HAS_DATA ensures automatic ingestion and conditional transformation execution. Snowpipe loads new files, the stream tracks changes, and the task runs only when data is present, minimizing manual intervention and avoiding empty batches.

Answer

Snowpipe with a stream on the raw table and a task that runs on a fixed schedule without a WHEN clause.

Answer

A task that calls a stored procedure to copy files from the external stage and then run transformations sequentially.

Answer

Snowpipe with a task that runs on a fixed schedule and checks the row count of the raw table.

Question 2

A healthcare analytics team uses Snowflake to analyze patient records. They have a large fact table 'ENCOUNTERS' that is clustered by 'PATIENT_ID' and 'ENCOUNTER_DATE'. The team frequently runs queries that filter on 'FACILITY_ID' and 'DIAGNOSIS_CODE', which are not part of the clustering key. These queries perform poorly. The architect needs to improve performance without changing the existing clustering key, as it benefits other queries. What should the architect do?

Accepted Answer

Create a search optimization service on the columns FACILITY_ID and DIAGNOSIS_CODE.. The search optimization service is designed to accelerate queries with filters on columns that are not part of the clustering key. It creates a persistent data structure that enables efficient pruning for equality and IN filters. This allows the team to keep the existing clustering key for other queries while improving performance for FACILITY_ID and DIAGNOSIS_CODE filters. It is the appropriate solution without altering the clustering key.

Answer

Add a secondary clustering key on FACILITY_ID and DIAGNOSIS_CODE to the table.

Answer

Create a materialized view on ENCOUNTERS that includes FACILITY_ID and DIAGNOSIS_CODE, and rewrite queries to use the materialized view.

Answer

Recluster the table manually using ALTER TABLE ... RECLUSTER, specifying the new columns.

Question 3

A data architect is designing a multi-cluster warehouse for a retail company's peak sales periods. The warehouse must automatically add clusters when queries are queued and shut them down when demand drops. The company wants to minimize credit consumption while maintaining query performance. Which configuration should the architect implement?

Accepted Answer

Set the warehouse to Auto-scale mode with a scaling policy of Standard.. Auto-scale mode with the Standard scaling policy dynamically adjusts the number of clusters based on query load, adding clusters when queries queue and removing them after idle periods. This provides the necessary performance during peak sales while controlling costs by not over-provisioning. The Standard policy is optimized for performance, making it ideal for time-sensitive workloads that still require cost efficiency.

Answer

Set the warehouse to Auto-scale mode with a scaling policy of Economy.

Answer

Set the warehouse to Maximized mode with a scaling policy of Economy.

Answer

Set the warehouse to Maximized mode with a scaling policy of Standard.

Question 4

An e-commerce company uses Snowflake to analyze clickstream data. They want to optimize query performance for a large fact table that is frequently filtered by 'event_date' and 'user_id'. The table is growing rapidly, and queries often scan many micro-partitions. Which architectural feature should the architect recommend to improve pruning?

Accepted Answer

Define a clustering key on (event_date, user_id).. Clustering keys on frequently filtered columns like event_date and user_id allow Snowflake to prune micro-partitions effectively. When data is clustered, rows with similar values are stored together, so queries with filters on those columns scan fewer micro-partitions. This reduces I/O and improves performance for large fact tables with growing data volumes.

Answer

Create a materialized view that pre-aggregates data by event_date and user_id.

Answer

Enable search optimization service on the table.

Answer

Partition the table by event_date using Snowflake's partitioning feature.

Question 5

An architect is designing a Snowflake environment for a financial services company that requires the highest level of security. The company must ensure that data is encrypted at rest and in transit, and that encryption keys are managed by the customer, not Snowflake. Which Snowflake feature should the architect implement?

Accepted Answer

Enable Tri-Secret Secure with a customer-managed key in AWS KMS or Azure Key Vault.. Tri-Secret Secure is the Snowflake feature that allows customers to use their own encryption key, managed in a cloud KMS, in addition to Snowflake's key. This provides customer control over encryption keys and meets the highest security requirements. The other options either do not provide customer-managed keys for Snowflake's internal encryption or are not valid Snowflake features.

Answer

Enable periodic rekeying of Snowflake-managed keys and store the keys in an external HSM.

Answer

Use the ENCRYPTION parameter in the CREATE DATABASE command to specify a customer key.

Answer

Configure Snowflake to use client-side encryption with a customer-provided key before loading data.

Question 6

Refer to the exhibit. Which architectural mechanism is responsible for the significant difference between 'partitions_total' and 'partitions_scanned'?

Accepted Answer

Metadata-based partition pruning.. The difference between total partitions and scanned partitions is caused by partition pruning, a critical optimization feature of Snowflake. By consulting the metadata in the Cloud Services layer, the engine identifies that only 150 partitions contain data matching the '2023-01-01' filter. This prevents the compute engine from scanning unnecessary files, which significantly improves query performance and reduces compute costs by minimizing the amount of data processed during a table scan.

Answer

Materialized views automatically created by the optimizer.

Answer

Automatic indexing of the date column by the system.

Answer

Data clustering on the 'date' column.

Question 7

A Snowflake architect is designing a multi-cluster warehouse for a workload that experiences unpredictable spikes in concurrency. The architect wants to ensure that queries are not queued unnecessarily while minimizing credit consumption during periods of low demand. The warehouse is configured with a minimum cluster count of 1 and a maximum of 5. Which scaling policy should the architect choose to best meet these goals?

Accepted Answer

Standard scaling policy. The Standard scaling policy is designed to minimize queuing by promptly adding clusters when queries are waiting, and it scales down after inactivity to save credits. Economy scaling is more conservative and may allow queuing during rapid spikes. Auto-suspend is unrelated to cluster scaling, and Maximized is not a valid policy. Therefore, Standard best meets the requirement to handle unpredictable concurrency without excessive credit use.

Answer

Auto-suspend scaling policy

Answer

Maximized scaling policy

Answer

Economy scaling policy

Question 8

A Snowflake architect is using the Data Exchange to share data with a partner who does not have a Snowflake account. What is the most appropriate feature to use?

Accepted Answer

The creation of a Managed (Reader) Account for the partner.. Managed accounts, also known as reader accounts, allow Snowflake customers to share data with parties who do not have their own Snowflake subscription. This feature enables the provider to bear the cost of the consumer's queries, facilitating easy and secure data collaboration without requiring the partner to manage an account.

Answer

Direct Data Sharing to the partner's corporate email address.

Answer

FTP export to the partner's secure external storage bucket.

Answer

Granting the partner access via a temporary security token.

Question 9

What is the primary benefit of implementing Snowflake Tri-Secret Secure?

Accepted Answer

It allows customers to have total control over data access by managing one of the master keys.. Tri-Secret Secure is an advanced security feature that combines a customer-managed key with Snowflake-managed keys to encrypt data. This provides an additional layer of control, as it allows the customer to revoke access to their data by disabling their key in their own cloud provider's Key Management Service.

Answer

It automatically rotates data encryption keys every 24 hours.

Answer

It enables the use of three different identity providers for authentication.

Answer

It provides a three-way handshake for all data transfers via Snowpipe.

Question 10

Refer to the exhibit. An architect observes these statistics in the Query Profile for a nightly batch job. What is the most effective architectural change to address the performance bottleneck shown?

Accepted Answer

Increase the virtual warehouse size (e.g., from Medium to Large or X-Large).. The exhibit shows significant 'spilling to remote storage', which occurs when the local SSD of the virtual warehouse is exhausted during a memory-intensive operation like a large join or sort. Remote spilling is much slower than local spilling. The primary solution is to increase the warehouse size to provide more memory and local disk.

Answer

Implement a Search Optimization Service on the join columns to reduce the number of partitions scanned.

Answer

Enable the Query Acceleration Service (QAS) for the warehouse to handle the excess data volume.

Answer

Rewrite the query to use a LATERAL FLATTEN on the largest tables to optimize memory usage.

Question 11

A security architect is reviewing the network architecture. Why is the 'Private Link' (or Private Connectivity) feature considered an architectural enhancement for high-security environments?

Accepted Answer

It ensures that all traffic stays within the cloud provider's network backbone.. Private connectivity ensures that traffic between the customer's VPC and Snowflake never traverses the public internet. By using private endpoints, the data flow is kept within the cloud provider's backbone network. This architectural design reduces the attack surface, minimizes exposure to public threats, and helps organizations meet strict regulatory requirements that prohibit data transmission over public routes, while maintaining the scalability of a SaaS platform.

Answer

It provides faster data ingestion by removing encryption overhead.

Answer

It allows the customer to host the Snowflake compute nodes in their own VPC.

Answer

It replaces the need for Snowflake role-based access control.

Question 12

Which feature of Snowflake's architecture allows for the scaling of resources to handle high-concurrency BI dashboard requests without affecting the performance of heavy ETL workloads?

Accepted Answer

Independent Virtual Warehouses. Snowflake's multi-cluster warehouse architecture enables the separation of workloads. By assigning distinct Virtual Warehouses to different user groups or applications, architects ensure that one workload cannot starve another for resources. This prevents contention and allows for granular, independent scaling based on the specific needs of each workload, which is a core architectural benefit for enterprise-level data environments managing diverse user requirements.

Answer

Warehouse Resizing

Answer

Data Sharing

Answer

Automatic Query Clustering

Question 13

What is the primary benefit of the decoupled storage and compute architecture regarding elastic scaling?

Accepted Answer

It allows compute warehouses to be resized or multi-clustered without data movement.. By decoupling storage and compute, Snowflake allows users to scale compute resources up or out without having to move or replicate data. This independence means users can handle massive spikes in query volume by spinning up new warehouses, while the storage remains in a consistent, cost-effective layer. This design enables the 'pay-as-you-go' model, where compute is only active during processing, dramatically reducing costs for intermittent workloads.

Answer

It eliminates the need for data distribution keys.

Answer

It automatically compresses data to reduce cloud storage costs.

Answer

It ensures that compute nodes can store local temporary data for faster access.

Question 14

A data engineer is setting up Snowpipe to ingest data from an S3 bucket. The engineer wants to ensure that Snowpipe is notified immediately when a new file arrives without polling the stage. What is the standard Snowflake recommendation for this configuration?

Accepted Answer

Set up an S3 Event Notification to send messages to a Snowflake-managed SQS queue.. Snowpipe auto-ingest is designed for low-latency ingestion by integrating with cloud provider notification services. This event-driven architecture is superior to manual polling as it reduces latency and compute costs associated with checking for new files. Configuring this correctly involves setting up an SQS queue or Event Grid to trigger the pipe object.

Answer

Configure a Snowflake Task to run every minute and execute the ALTER PIPE...REFRESH command.

Answer

Use a Python script on an EC2 instance to call the Snowpipe REST API every time a file is uploaded.

Answer

Increase the 'RECURSIVE' parameter in the Stage definition to allow Snowpipe to see nested folders.

Question 15

Which approach is most effective for optimizing queries that frequently filter by multiple columns simultaneously?

Accepted Answer

Apply a multi-column clustering key.. When queries frequently filter by multiple columns, clustering the table by those specific columns is highly effective. Snowflake's micro-partitioning tracks the min/max values for each column. By ensuring these columns are clustered together, the engine can prune partitions much more aggressively. This minimizes the amount of data read, resulting in faster query performance for multi-dimensional filter conditions on large tables.

Answer

Use a single clustering key with high cardinality.

Answer

Create separate materialized views for each filter.

Answer

Store all data in a single JSON column.

Question 16

Which Snowflake feature helps minimize query latency by avoiding re-computation for identical queries?

Accepted Answer

Result Caching.. The Result Cache is an automatic, managed feature that stores the output of identical queries. When a user runs the exact same query again, Snowflake retrieves the result directly from the cache rather than re-computing it. This provides near-instantaneous performance for repeated workloads and is a key component of Snowflake's performance optimization strategy, reducing both latency and unnecessary compute costs for end users.

Answer

Query Acceleration Service.

Answer

Materialized Views.

Answer

Automatic Clustering.

Question 17

Refer to the exhibit. An architect needs to optimize this warehouse to handle a batch process that performs a massive table scan followed by a complex join on 10 billion rows. What is the most effective architectural change?

Accepted Answer

Change the size of the warehouse from X-SMALL to LARGE.. For a single, complex query involving a massive data set, the bottleneck is typically the compute power and memory available to the nodes. Increasing the size (vertical scaling) from X-Small to a larger size (e.g., Large or X-Large) provides more CPUs and memory per node. This allows the warehouse to process more data in parallel and reduces the need for spilling to local or remote disk.

Answer

Increase the max_cluster_count to 5 to enable multi-cluster scaling.

Answer

Set the auto_resume property to TRUE to ensure the warehouse starts automatically.

Answer

Alter the warehouse type from STANDARD to SNOWPARK-OPTIMIZED.

Question 18

An organization has a Network Policy applied at the Account level to restrict IP ranges. A specific user requires access from a home office IP not in the account range. How should the architect configure this while maintaining the strictest security posture?

Accepted Answer

Create a user-level network policy and assign it directly to that specific user account.. Snowflake evaluates network policies at the most granular level first, starting with the user, then the account. Applying a specific policy to the individual user overrides the account-level restrictions without opening access for the entire organization. This hierarchy allows for precise control over entry points while maintaining a broad security baseline for all other platform participants.

Answer

Modify the existing account-level network policy to include the user's home IP address.

Answer

Create a new role for the user and attach the network policy to that specific role.

Answer

Disable the account-level network policy and rely solely on multi-factor authentication for security.

Question 19

Why does Snowflake's architecture separate compute from storage?

Accepted Answer

To enable independent scaling and improve resource efficiency.. Separating compute from storage allows both layers to scale independently based on demand. You can scale storage without adding compute power and vice versa, which is highly cost-effective and prevents resource contention. This decoupling enables multiple workloads to access the same underlying data without impacting each other's performance, facilitating a multi-tenant environment where various business units can query the same data source simultaneously without the typical limitations found in monolithic database systems.

Answer

To ensure that all queries are executed on the same machine.

Answer

To force data to be moved into the compute node before processing.

Answer

To eliminate the need for any storage management.

Question 20

A financial organization needs to ensure that only connections originating from their corporate VPN IP range can access their Snowflake account. Which feature should the architect implement?

Accepted Answer

Apply a Network Policy at the account level.. Network Policies provide the primary mechanism for controlling access based on IP addresses. By defining an allowed list of CIDR blocks, the architect creates a perimeter defense that prevents unauthorized access from public networks. This is crucial for compliance in highly regulated industries, ensuring that data exposure risks are mitigated by restricting the attack surface to trusted network locations only, effectively blocking any connection attempts outside the defined enterprise perimeter.

Answer

Enable MFA for all users.

Answer

Configure SCIM provisioning.

Answer

Implement OAuth security integration.