ARA-C01 Practice Test 10 — 10 Questions

Question 1

A data architect is tuning a Snowflake virtual warehouse that runs a mix of short ad-hoc queries and long-running analytical queries. The warehouse is sized as Medium, and the architect observes that short queries are frequently queued behind long queries. The architect wants to reduce queuing for short queries without increasing the warehouse size. Which configuration change should the architect make?

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

Accepted Answer

Create a separate warehouse for short ad-hoc queries and route them accordingly.. Workload isolation by creating a separate warehouse for short ad-hoc queries prevents them from queuing behind long analytical queries. This allows independent sizing and scaling, improving performance for both workloads without increasing the size of the original warehouse. It is a standard Snowflake best practice for managing mixed workloads and reducing contention.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Configure the warehouse with a multi-cluster scaling policy set to Standard.

Answer

Set the warehouse to auto-suspend after 60 seconds and auto-resume when queued.

Question 2

A financial services company runs a Snowflake workload where several long-running analytical queries compete with a high volume of short interactive dashboard queries on the same multi-cluster warehouse. Users report that dashboards sometimes wait behind heavy reports. The architect wants a cost-effective configuration that isolates the heavy reports from the interactive queries without duplicating compute unnecessarily. What should the architect do?

Accepted Answer

Create a separate warehouse for the heavy reports and route those queries to it, leaving the dashboard warehouse for interactive queries.. Separate warehouses are the standard way to isolate workloads in Snowflake. By giving heavy reports their own warehouse, the interactive dashboards no longer queue behind them, and each workload can be sized and scaled independently. This avoids over-provisioning a single warehouse and keeps credit usage aligned with each workload's needs.

Answer

Increase the warehouse size to the next larger size so that all queries get more compute resources.

Answer

Enable the Query Acceleration Service on the existing warehouse to offload portions of the heavy queries.

Answer

Set the STATEMENT_TIMEOUT_IN_SECONDS parameter to a low value on the warehouse so long queries are cancelled.

Question 3

A Snowflake architect is explaining the layers of Snowflake's architecture to a new team. Which layer is responsible for managing metadata, optimizing queries, and providing transaction management?

Accepted Answer

Cloud Services layer. Snowflake's architecture consists of three layers: storage, compute, and Cloud Services. The Cloud Services layer is responsible for metadata management, query optimization, transaction management, and access control. It coordinates all activities and ensures ACID compliance. The compute layer executes queries, and the storage layer stores data. Thus, the Cloud Services layer is the correct answer.

Answer

Compute layer

Answer

Database layer

Answer

Storage layer

Question 4

A data architect is designing a pipeline that uses Snowpipe to load data from an external stage. The architect wants to ensure that data is loaded as soon as files are available and that the load is triggered automatically. Which mechanism should be used to notify Snowpipe of new files?

Accepted Answer

Configure a cloud storage event notification that triggers Snowpipe via a notification integration.. The correct mechanism is to configure a cloud storage event notification that triggers Snowpipe via a notification integration. This enables Snowpipe to automatically load new files as soon as they are written to the stage, providing near-real-time data ingestion. It eliminates the need for polling or manual refreshes.

Answer

Configure the pipe with AUTO_INGEST = TRUE and manually call ALTER PIPE ... REFRESH.

Answer

Create a task that calls the Snowpipe REST API to ingest files on a schedule.

Answer

Use a Snowflake stream on the external stage to detect new files.

Question 5

A healthcare company stores PHI in a Snowflake database and must ensure that only authorized roles can decrypt the data at rest. They want an additional layer of protection so that even Snowflake cannot access the data without a customer-held key. Which Snowflake feature should the architect implement?

Accepted Answer

Tri-Secret Secure. Tri-Secret Secure is designed to give customers control over a key that is part of the encryption hierarchy. By combining a customer-managed key with Snowflake's key, it ensures that data cannot be decrypted without the customer's key, providing an extra layer of protection. This directly addresses the need for customer-controlled encryption at rest.

Answer

Network policies with private connectivity

Answer

Column-level security with masking policies

Answer

Client-side encryption with Snowflake's ENCRYPT function

Question 6

A Snowflake architect is reviewing a query that uses a window function partitioned by customer_id and ordered by transaction_date. The query processes a 1TB table and runs slowly. The architect notices that the table is not clustered. Which action should the architect take to improve performance?

Accepted Answer

Cluster the table on customer_id and transaction_date.. Clustering on the columns used in the window function's PARTITION BY and ORDER BY clauses co-locates related data, reducing shuffling and sorting during query execution. This directly improves the performance of window functions on large tables. Other options either do not address data organization or are not applicable.

Answer

Increase the warehouse size to provide more compute for the window function.

Answer

Use the Search Optimization Service on the customer_id column.

Answer

Create a materialized view that pre-computes the window function.

Question 7

A data architect notices that a recurring batch job that loads data into a Snowflake table and then runs a series of transformation queries is taking longer than expected. The transformation queries involve multiple joins and aggregations. The architect wants to ensure that the warehouse is adequately sized for the workload without over-provisioning. Which Snowflake feature should the architect use to analyze the performance of individual queries and identify bottlenecks?

Accepted Answer

Query Profile in the Snowflake web interface.. Query Profile is the dedicated tool for examining query execution plans and operator-level metrics. It helps identify bottlenecks like expensive joins or aggregations. Other options provide either high-level metadata or capacity information, but not the granular execution details needed for tuning individual queries.

Answer

Warehouse load monitoring in the Snowflake web interface.

Answer

Snowflake's automatic clustering recommendations.

Answer

ACCOUNT_USAGE.QUERY_HISTORY view.

Question 8

A data architect is designing a pipeline that ingests streaming data into a Snowflake table. The data must be transformed with a Python UDF that calls an external API for enrichment. The architect wants to minimize latency and ensure the UDF can scale independently. Which Snowflake feature should be used?

Accepted Answer

Create an external function that calls the API via an API integration and use it in the transformation.. External functions are specifically designed to call external services via API integrations, providing secure and scalable access to external APIs. They allow the external service to scale independently from Snowflake warehouses, and they are invoked from SQL like regular functions. This makes them ideal for enriching streaming data with external API calls while minimizing latency and ensuring scalability.

Answer

Create a JavaScript UDF and call the external API using the built-in fetch function.

Answer

Use a stored procedure with Python and call the API using the requests library.

Answer

Create a Python UDF and use the Snowpark library to make HTTP requests.

Question 9

A data engineer needs to copy data from an internal stage into a Snowflake table. The stage contains files with a mix of valid and malformed records. The engineer wants to load all valid records and capture the malformed ones for later analysis without failing the entire load. Which COPY INTO option should be used?

Accepted Answer

ON_ERROR = 'CONTINUE'. ON_ERROR = 'CONTINUE' is the correct option because it allows the COPY INTO operation to skip erroneous rows and load all valid ones. The rejected rows are recorded in the load metadata, which can be queried using the VALIDATE function or by checking the COPY_HISTORY. This provides a way to capture malformed records for later analysis while ensuring valid data is loaded.

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Answer

ON_ERROR = 'SKIP_FILE'

Question 10

A SnowPro Advanced Architect is designing a new fact table that will be loaded incrementally each day with 500 million rows. The table will be queried primarily by a small set of high-concurrency BI dashboards filtering on order_date and product_category. The architect wants to minimize micro-partition scanning for these queries. Which table design approach should the architect choose?

Accepted Answer

Define a clustering key on (order_date, product_category) to co-locate related rows in the same micro-partitions.. The scenario calls for reducing micro-partition scanning for range and equality filters on order_date and product_category. A clustering key on those columns aligns rows with similar values into the same micro-partitions, so the optimizer can prune more effectively. Automatic clustering maintains the layout as new data is inserted, which is critical for an incrementally loaded fact table. Other options either do not improve pruning or add unnecessary complexity and cost.

Answer

Create a materialized view that pre-aggregates the fact table by order_date and product_category.

Answer

Partition the table manually by order_date using separate tables per month and a UNION ALL view.

Answer

Enable the Search Optimization Service on the fact table to speed up point lookups on order_date and product_category.