ARA-C01 Practice Test 5 — 25 Questions

Question 1

An architect is designing a multi-cluster warehouse for a retail analytics workload that experiences unpredictable spikes in concurrency. The warehouse is configured with a minimum of 2 clusters and a maximum of 10 clusters, scaling policy set to Economy. During a sudden spike, queries are queuing despite the warehouse scaling out to 6 clusters. What is the most likely cause of the queuing?

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

Accepted Answer

The Economy scaling policy adds clusters more slowly than the Standard policy, causing queries to queue during rapid spikes.. The Economy scaling policy prioritizes cost by scaling out slowly and scaling in quickly. During a sudden spike, it may not add clusters fast enough to prevent queuing, even if the maximum cluster count is not reached. The Standard policy would scale out more aggressively to minimize queuing.

Answer

The queries are not suitable for multi-cluster warehouses because they are too complex.

Answer

The warehouse is configured with a maximum cluster count that is too low for the workload.

Answer

The warehouse has reached the limit of concurrent queries per cluster, so adding more clusters does not help.

Question 2

A Snowflake architect is analyzing a slow-running query that joins two large tables and aggregates results. The query profile shows a significant amount of time spent in the 'Join' operator and a large number of rows spilled to local storage. The join condition uses a non-equality predicate (e.g., range join). Which optimization technique is most likely to improve performance?

Accepted Answer

Rewrite the query to use an equality join by adding a derived join key.. The query profile indicates a costly range join with spilling. Converting to an equality join via a derived key allows Snowflake to use a hash join, which is more scalable and reduces memory pressure. Other options either do not change the join algorithm or are less targeted for this specific bottleneck.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Add a clustering key on the join columns of both tables.

Answer

Increase the warehouse size to add more memory per node.

Question 3

A security architect at a financial services company needs to ensure that all data stored in Snowflake is encrypted with keys that the company controls and can revoke at any time. They have already enabled Tri-Secret Secure. Which additional configuration is required to meet this requirement?

Accepted Answer

Configure the account to use a customer-managed key (CMK) stored in a supported cloud KMS.. Tri-Secret Secure requires a customer-managed key (CMK) in a supported cloud KMS to give the customer control over encryption keys. By configuring the CMK, the company can revoke access by disabling the key. The other options do not provide key control: rekeying is automatic and not customer-controlled, network policies are unrelated to encryption, and there is no Snowflake privilege to manage encryption keys directly.

Answer

Create a custom role with the MANAGE ENCRYPTION KEYS privilege and assign it to the security team.

Answer

Set up a network policy that restricts access to the Snowflake account to only the company's IP ranges.

Answer

Enable periodic rekeying of the Snowflake-managed key through the ACCOUNTADMIN role.

Question 4

An architect is configuring key-pair authentication for a service account used by an automated ETL process. They need to ensure the private key is stored securely and the public key is assigned to the user. Which two actions should be performed? (Choose two.)

Accepted Answer

Assign the public key to the Snowflake user using ALTER USER ... SET RSA_PUBLIC_KEY.. For key-pair authentication, the private key must be securely stored and the corresponding public key assigned to the Snowflake user. The private key is used by the client to sign authentication requests, and Snowflake verifies the signature using the public key. Storing the private key in a vault and setting the public key via ALTER USER are the essential steps to enable this authentication method securely.

Answer

Enable multi-factor authentication for the service account to add an extra layer of security.

Answer

Set the user's RSA_PUBLIC_KEY_FP parameter to the fingerprint of the private key.

Answer

Configure the ETL service to use the private key passphrase in the connection string.

Question 5

An architect is investigating why a query that joins a 10 TB fact table to a small dimension table runs slowly. The query profile shows a Broadcast Join operator and a high percentage of time spent in the Join node. The fact table is not clustered on the join key. Which action is most likely to improve performance?

Accepted Answer

Add a clustering key on the fact table's join column.. Clustering the large fact table on the join column aligns micro-partitions with the join key, allowing Snowflake to prune partitions and reduce the volume of data read and redistributed during the join. This directly addresses the high time in the Join node. Other options either target different workloads (Search Optimization), add cost without fixing I/O (larger warehouse), or rely on unsupported hints (forcing join type).

Answer

Force the optimizer to use a hash join instead of a broadcast join.

Answer

Increase the size of the virtual warehouse used to run the query.

Answer

Enable the Search Optimization Service on the fact table.

Question 6

A financial services company runs a Snowflake account in the AWS us-east-1 region. The security team mandates that all data at rest must be encrypted with customer-managed keys, and they require an audit trail of key usage. Which Snowflake feature should the architect implement to meet these requirements with minimal administrative overhead?

Accepted Answer

Configure Tri-Secret Secure with a customer-managed key in AWS KMS.. Tri-Secret Secure is the only Snowflake feature that allows customers to use their own AWS KMS key in combination with a Snowflake-managed key, providing customer-managed encryption at rest with an audit trail through AWS CloudTrail. The other options either do not provide customer-managed keys or do not meet the audit trail requirement.

Answer

Enable client-side encryption using the Snowflake JDBC driver's encryption capabilities.

Answer

Implement a Snowflake External Function that encrypts data before writing to internal stages.

Answer

Use Snowflake's default encryption with AES-256 and enable periodic key rotation.

Question 7

A data engineering team needs to load data from an on-premises Oracle database into Snowflake on a recurring basis. The data volume is large (several terabytes), and the team wants to minimize the load on the source system. They also require the ability to perform incremental loads based on a timestamp column. Which Snowflake feature should the architect recommend?

Accepted Answer

Snowflake Connector for Oracle (or a similar partner ETL tool) to perform incremental extraction and loading.. The Snowflake Connector for Oracle is specifically designed to enable incremental data ingestion from Oracle databases. It supports timestamp-based incremental loads and is optimized to reduce load on the source system. This makes it the ideal choice for large-scale, recurring data loads from on-premises Oracle into Snowflake, without the need for intermediate staging or manual extraction.

Answer

Snowpipe with auto-ingest from cloud storage.

Answer

Use the COPY INTO command with a stage that points to the Oracle database.

Answer

Create an external table that references the Oracle database.

Question 8

An architect is designing a dashboard that runs a query joining a 5 TB fact table to a small 2 GB dimension table. The fact table is not clustered, and the dimension table is updated hourly. The query filters on a high-cardinality column in the fact table and joins on a low-cardinality key. The architect wants to minimize query latency without increasing warehouse size. Which approach is most effective?

Accepted Answer

Add a clustering key on the fact table's high-cardinality filter column.. The query's main cost is scanning the large fact table. Clustering on the high-cardinality filter column enables partition pruning, reducing I/O. The small dimension table does not warrant special handling. Search Optimization is for point lookups, materializing adds overhead, and resizing does not address the root cause of excessive data scanning.

Answer

Enable the Search Optimization Service on the fact table.

Answer

Materialize the join as a new table and refresh it hourly.

Answer

Increase the warehouse size to add more compute nodes.

Question 9

An architect is designing a secure data sharing strategy using Snowflake's Data Sharing feature. The goal is to share a subset of data with a partner account while ensuring that the partner cannot access underlying micro-partitions directly and that the shared data is always up-to-date. Which two statements are true regarding Snowflake Data Sharing? (Choose two.)

Accepted Answer

Data sharing provides live, read-only access to the shared data without copying it, ensuring the partner always sees the latest changes.. Snowflake Data Sharing provides live, read-only access to data without copying, and providers use share objects to grant granular privileges. Consumers cannot modify shared data or create micro-partitions. Cross-region sharing is possible with replication, so the same-region requirement is not absolute.

Answer

The consumer can modify the shared data by inserting new rows into the shared tables.

Answer

Data sharing requires the consumer to have a Snowflake account in the same cloud region as the provider.

Answer

The consumer can create their own micro-partitions on the shared data to improve query performance.

Question 10

A Snowflake architect is reviewing a query that scans a large table and applies a highly selective filter on a column that is not the clustering key. The query profile shows a TableScan operator with a high percentage of partitions scanned but few rows returned. Which feature should the architect recommend to improve performance for this type of query?

Accepted Answer

Search Optimization Service. Search Optimization Service is purpose-built for queries that apply highly selective filters on columns that are not the clustering key. It creates a search access path that enables efficient micro-partition pruning, reducing the number of partitions scanned. While clustering can also improve pruning, it is more suitable for range filters and columns used in multiple queries. QAS and materialized views target different workloads.

Answer

Query Acceleration Service

Answer

Clustering key on the filtered column

Answer

Materialized view on the filtered column

Question 11

A SnowPro Advanced Architect is configuring a virtual warehouse for a workload that runs a mix of short ad-hoc queries and long-running ETL jobs. The architect wants to prevent long-running ETL jobs from monopolizing the warehouse and degrading ad-hoc query performance. Which approach is most appropriate?

Accepted Answer

Create a separate warehouse for ETL jobs and use resource monitors to control credit usage.. Workload isolation is best achieved by dedicating separate virtual warehouses to different workload types. ETL jobs and ad-hoc queries have different resource profiles and SLAs; putting them on the same warehouse causes contention. A separate ETL warehouse with its own resource monitor allows the architect to control costs and prevent ETL from affecting ad-hoc performance. Other options either do not isolate workloads or introduce disruptive side effects.

Answer

Configure the warehouse to use multi-cluster mode with a minimum of two clusters.

Answer

Enable the Query Acceleration Service on the warehouse to offload portions of the ETL jobs.

Answer

Set the STATEMENT_TIMEOUT_IN_SECONDS parameter to a low value to terminate long ETL queries.

Question 12

A Snowflake architect is analyzing a query that performs a large join between a fact table and a dimension table. The query profile shows that the join operation is spilling to local disk. The architect wants to reduce the spillage and improve performance. Which action should the architect take first?

Accepted Answer

Increase the size of the virtual warehouse used for the query.. Spilling to local disk during a join indicates that the operation requires more memory than available. Increasing the virtual warehouse size provides more memory and compute resources, allowing the join to process data in memory and reducing spillage. This is a direct and effective first step. Other options may help in specific cases but do not address the immediate memory constraint.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Rewrite the query to use a smaller dimension table.

Answer

Add a clustering key to the fact table on the join column.

Question 13

A Snowflake architect is analyzing a query that performs a large aggregation over a fact table with billions of rows. The query profile shows that the aggregation step is spilling to local disk, and the warehouse is a 2XL multi-cluster warehouse with 4 clusters. The architect wants to reduce the spilling and improve performance. Which action is most likely to achieve this?

Accepted Answer

Scale up the warehouse to a larger size to provide more memory per node.. Spilling to local disk during aggregation indicates that the query's working set exceeds the available memory per node. Scaling up the warehouse increases memory per node, allowing the aggregation to be processed in memory and reducing spilling. Adding clusters or enabling QAS does not address the per-query memory limitation.

Answer

Increase the number of clusters in the multi-cluster warehouse to 8.

Answer

Reduce the size of the warehouse to a smaller size to decrease spilling.

Answer

Enable the Query Acceleration Service to offload the aggregation.

Question 14

An architect is explaining Snowflake's architecture to a team new to the platform. The team wants to understand which component is responsible for storing metadata about tables, columns, and micro-partitions to enable efficient query pruning. Which Snowflake layer provides this functionality?

Accepted Answer

Cloud services layer. The cloud services layer is responsible for metadata management, including table definitions, column statistics, and micro-partition metadata used for pruning. It is a key component that enables Snowflake's performance and ease of use, distinct from storage and compute layers.

Answer

Database layer

Answer

Compute layer

Answer

Storage layer

Question 15

A Snowflake architect is reviewing a query that runs slowly. The query profile shows that the most time is spent in the TableScan operator, and the operator details indicate that a large number of micro-partitions were scanned. The architect wants to reduce the number of micro-partitions scanned. Which action is most likely to achieve this?

Accepted Answer

Add a clustering key on the column used in the WHERE clause.. Clustering on the column used in the WHERE clause improves pruning by organizing data so that similar values are stored together. This allows Snowflake to skip micro-partitions that do not contain matching values, reducing the number scanned. Increasing warehouse size does not reduce partitions scanned, and search optimization is more suited for selective lookups. Therefore, clustering is the most effective action.

Answer

Use a materialized view that pre-aggregates the data.

Answer

Increase the size of the virtual warehouse.

Answer

Enable the Search Optimization Service on the table.

Question 16

A data engineer must load a daily batch of CSV files from an internal stage into a staging table. The files use a pipe delimiter, include a header row, and contain date values in a non-default format. The team wants to validate that the load succeeded and capture any rejected rows for review. Which configuration should the architect specify?

Accepted Answer

Define a file format with TYPE = CSV, FIELD_DELIMITER = '|', SKIP_HEADER = 1, and a DATE_FORMAT matching the source, then use COPY INTO with ON_ERROR = 'CONTINUE' and a validation_mode run first.. A file format that matches the delimiter, skips the header, and specifies the source date format is the foundation. COPY INTO with ON_ERROR = CONTINUE keeps valid rows loading while rejected rows are recorded, and a VALIDATION_MODE run surfaces errors before any data is committed. The other approaches either ignore the delimited structure, avoid loading into the target, or abort on the first bad row.

Answer

Use COPY INTO with MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE and rely on the target table column order to map the fields.

Answer

Create an external table over the stage and query the CSV files directly, converting the date with TO_DATE in the SELECT statement.

Answer

Load the files with COPY INTO using ON_ERROR = 'ABORT_STATEMENT' and inspect the query history afterward to identify which rows failed.

Question 17

An architect is optimizing a Snowflake environment where several large tables are frequently queried with filters on a timestamp column that is not the natural sort order of the data. The architect wants to improve query performance by reducing the amount of data scanned. Which action should the architect take?

Accepted Answer

Define a clustering key on the timestamp column.. Clustering on the timestamp column sorts data into micro-partitions by that column, enabling partition pruning for range filters. This reduces I/O for many queries. Materialized views are query-specific, Search Optimization is for point lookups, and resizing does not reduce data scanned.

Answer

Enable the Search Optimization Service on the timestamp column.

Answer

Increase the size of the virtual warehouse used for these queries.

Answer

Create a materialized view that filters on the timestamp column.

Question 18

A data engineer needs to load data from a local file system into a Snowflake table. The file is 10 GB in size and contains CSV data. The engineer wants to use the most efficient method for a one-time bulk load. Which Snowflake feature should the engineer use?

Accepted Answer

COPY INTO command. For a one-time bulk load from a local file, the engineer should first upload the file to an internal stage with PUT, then use COPY INTO to load it into the table. COPY INTO is optimized for bulk loading and is the most efficient method. Other options are designed for continuous ingestion or external access, not bulk loading.

Answer

Snowpipe

Answer

Snowflake Connector for Kafka

Answer

External tables

Question 19

An architect is building a near-real-time pipeline that reads JSON events from a Kafka topic and must land them into Snowflake with sub-minute latency. The team has a Snowpipe streaming setup using the Snowflake Ingest SDK and writes to a table with a VARIANT column. They observe that the ingestion service occasionally reports channel errors and some events are missing after a client restart. Which configuration change best addresses the missing events?

Accepted Answer

Enable offset token tracking and pass the last committed offset token when reopening the channel so the SDK resumes from the correct position.. Snowpipe streaming channels expose offset tokens that represent the client's progress in the source stream. Persisting the last committed token and supplying it when a channel is reopened lets the SDK resume from the correct position, closing the gap that would otherwise appear after a client restart. Throughput tuning and alternative ingestion methods do not solve the resume-position problem.

Answer

Reduce the client's flush interval so that events are committed more frequently, eliminating the need to track positions across restarts.

Answer

Increase the number of channels per table and distribute events round-robin so that a single channel failure cannot drop data.

Answer

Switch the pipeline to a standard Snowpipe with AUTO_INGEST so that the cloud provider's event notifications guarantee delivery of every record.

Question 20

A healthcare organization needs to share patient data with external research partners without copying the data. The partners use their own Snowflake accounts and require read-only access to a specific subset of tables. Which Snowflake feature should the architect use to meet these requirements?

Accepted Answer

Use Snowflake Data Sharing to create a share and grant access to the partners' accounts.. Snowflake Data Sharing enables secure, governed access to data across Snowflake accounts without copying. By creating a share and granting access to the partners' accounts, the healthcare organization can provide read-only access to specific tables. This approach ensures data remains in the provider's account, reducing risk and eliminating data duplication.

Answer

Create a secure view and grant SELECT privileges to the partners' roles.

Answer

Export the data to an external stage and provide the partners with credentials.

Answer

Set up a Snowflake reader account for each partner and grant access to the tables.