ARA-C01 Practice Test 3 — 10 Questions

Question 1

A Snowflake architect is investigating a query that performs poorly due to a Cartesian join between two large tables. The query is intended to join on a specific key, but the join condition is missing in the SQL. After correcting the query to include the join condition, the architect wants to ensure optimal performance. Which of the following is the most effective next step?

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

Accepted Answer

Verify that the join key columns in both tables have the same data type and are not implicitly cast.. After correcting the missing join condition, the most effective next step is to ensure that the join keys have matching data types and are not subject to implicit casting. Implicit casts can prevent hash joins and cause full scans, severely impacting performance. Addressing this ensures the optimizer can choose an efficient join method before considering other optimizations like clustering.

Answer

Add a clustering key on the join key in both tables.

Answer

Increase the warehouse size to handle the larger intermediate result set.

Answer

Rewrite the query to use a CROSS JOIN with a WHERE clause.

Question 2

A data architect is analyzing a slow-performing query that joins a large fact table with a small dimension table. The Query Profile shows that the join is executed as a broadcast join, and the dimension table is small enough to fit in memory. However, the query still takes a long time because the fact table is not pruned effectively. The fact table is clustered by date, and the query filters on a specific date range. Which action should the architect take to improve pruning and overall performance?

Accepted Answer

Ensure that the date filter is applied as a predicate pushdown and that the clustering key is active.. The fact table is already clustered by date, and the query filters on a date range, so pruning should be effective if the predicate is pushed down and the clustering key is active. The architect should verify that the date filter is applied at the scan level and that the clustering key is not stale. This directly addresses the excessive data scanning shown in the Query Profile, whereas other options do not target the pruning problem.

Answer

Convert the broadcast join to a hash join by increasing the warehouse size.

Answer

Create a materialized view that pre-joins the fact and dimension tables.

Answer

Add a clustering key on the join key of the fact table.

Question 3

A Snowflake architect is designing a pipeline that ingests semi-structured JSON events from an internal stage and needs to write them into a VARIANT column. The events contain nested keys that vary in depth and casing across sources, and the team wants to flatten only a fixed set of known top-level keys while preserving the remaining structure for later analysis. Which approach best satisfies these requirements?

Accepted Answer

Create a view that exposes the fixed top-level keys with colon path notation (for example, src:payload:eventType) and leave the raw VARIANT column available for ad hoc queries.. Projecting known fields with colon path notation keeps the raw VARIANT column intact, so unknown keys and deeper nesting remain available. This satisfies both requirements: a stable interface for the fixed top-level keys and full retention of the original semi-structured payload for later analysis. Recursive flattening, inferred relational schemas, and key-name extraction all either destroy structure or fail to surface values.

Answer

Use LATERAL FLATTEN with the RECURSIVE argument set to TRUE on the raw VARIANT column to expand every nested key into separate rows, then filter the resulting KEY column to the known top-level keys.

Answer

Load the JSON into a relational table with one column per possible nested key, using the INFER_SCHEMA option of COPY INTO to auto-detect every attribute at load time.

Answer

Store each event as a separate row using the PARSE_JSON function and then apply OBJECT_KEYS to return only the known top-level keys as an array column.

Question 4

A data architect is designing a performance monitoring strategy for a Snowflake account. They need to identify queries that are consuming the most resources over time to prioritize optimization efforts. Which Snowflake feature should they use to analyze historical query performance and resource consumption?

Accepted Answer

ACCOUNT_USAGE.QUERY_HISTORY view. To analyze historical query performance and resource consumption across the account, the architect should use the ACCOUNT_USAGE.QUERY_HISTORY view. It retains up to 365 days of data and includes metrics like execution time, bytes scanned, and credits used, enabling identification of high-resource queries. Other options either have limited retention, scope, or granularity, making them less suitable for long-term performance monitoring.

Answer

INFORMATION_SCHEMA.QUERY_HISTORY function

Answer

WAREHOUSE_LOAD_HISTORY view

Answer

Query Profile in the Snowflake web interface

Question 5

A data engineering team needs to merge late-arriving dimension updates into a large target table. The source is a staging table containing both inserts and updates, and the target has a natural business key. The team wants a single statement that applies all changes atomically and avoids duplicate rows when the source contains multiple records for the same key. Which approach should the architect recommend?

Accepted Answer

Use a MERGE statement whose source is a subquery that deduplicates the staging table by business key, keeping the latest record per key.. A MERGE with a deduplicated source applies all changes in one atomic statement and guarantees each target row is touched once. Ranking staging rows per business key and keeping the latest avoids the duplicate-match error and yields deterministic upserts. Separate insert and update statements, full-table overwrite, and per-row task updates either risk nondeterminism, incur excessive cost, or fail to collapse duplicates.

Answer

Run an INSERT for new keys and a separate UPDATE for existing keys as two statements inside an explicit transaction.

Answer

Use INSERT OVERWRITE to replace the entire target table with a fresh join of the staging table and the existing target.

Answer

Create a Stream on the staging table and consume it with a task that issues individual UPDATE statements per changed row.

Question 6

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?

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 7

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 8

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 9

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 10

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.