ARA-C01 Practice Test 12 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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.

Question 6

A Snowflake architect is designing a solution where multiple independent compute clusters (virtual warehouses) must access the same underlying data without needing to copy it, and each workload must be isolated to avoid resource contention. Which Snowflake architecture feature directly enables this?

Accepted Answer

Multi-cluster shared data architecture. Snowflake's architecture separates storage and compute, allowing multiple virtual warehouses to access the same central data repository without duplication. This multi-cluster shared data design provides compute isolation and elastic scalability while ensuring all warehouses see consistent data. Replication and cloning create copies, and auto-scaling manages resources within a single warehouse, so they do not meet the requirement of shared data across independent compute clusters.

Answer

Database replication

Answer

Zero-copy cloning

Answer

Virtual warehouse auto-scaling

Question 7

An architect is configuring a Snowflake account to allow users to authenticate via an external OAuth 2.0 identity provider. The security team requires that the OAuth token includes specific custom claims that map to Snowflake roles. Which Snowflake feature should the architect use to enforce role mapping based on these claims?

Accepted Answer

External OAuth integration with a security integration object. External OAuth integration in Snowflake is designed to accept tokens from an external OAuth 2.0 authorization server. By configuring a security integration with the appropriate issuer, audience, and claim mappings, Snowflake can extract custom claims from the token and map them to roles. This allows the identity provider to control role assignments dynamically. Other authentication methods like SAML, key pair, or managed identity do not support OAuth token claims for role mapping.

Answer

Key pair authentication with RSA public/private keys

Answer

SAML 2.0 federation with a security integration

Answer

Snowflake managed identity with Azure AD

Question 8

A SnowPro Advanced Architect is analyzing a query that performs a large aggregation over a fact table. The query profile shows that the Aggregation operator is spilling to local disk. The architect wants to reduce spilling and improve performance. Which action is most likely to help?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Spilling to local disk during aggregation indicates that the aggregation state exceeds available memory. The most direct remedy is to increase memory per node by scaling up the warehouse. This allows the aggregation to be processed in memory, reducing or eliminating spilling. Other options either do not target memory usage or may worsen it. While scaling up increases cost, it is often the simplest and most effective fix for memory-bound aggregations.

Answer

Add a clustering key on the group-by columns to reduce the number of groups.

Answer

Enable the Query Acceleration Service to offload the aggregation to shared compute.

Answer

Rewrite the query to use a window function instead of a GROUP BY.

Question 9

A Snowflake architect is optimizing a virtual warehouse that serves a mix of short ad-hoc queries and long-running ETL jobs. Users report that ad-hoc queries sometimes wait in the queue for several minutes during ETL execution. The architect wants to reduce queuing without increasing costs unnecessarily. Which two actions should the architect take? (Choose two.)

Accepted Answer

Enable multi-cluster warehouse with a minimum of 2 clusters.. To reduce queuing for ad-hoc queries during ETL execution, the architect should either enable multi-cluster warehouse to add capacity dynamically or separate the workloads onto different warehouses. Multi-cluster warehouse with a minimum of 2 clusters ensures additional compute is available for queued queries. Separating ETL and ad-hoc workloads eliminates contention entirely. Both actions directly address the root cause of queuing without unnecessarily increasing costs, as they can be scaled independently.

Answer

Increase the size of the existing warehouse to X-Large.

Answer

Set the warehouse to auto-suspend after 60 seconds of inactivity.

Answer

Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

Question 10

A Snowflake architect is reviewing a query that performs a large aggregation over a fact table. The Query Profile shows that the aggregation is spilling to local disk. The warehouse is a Medium size. The architect wants to reduce or eliminate spilling to improve performance. Which action is most likely to achieve this?

Accepted Answer

Increase the warehouse size to Large.. Spilling to local disk occurs when an operation requires more memory than the warehouse can provide. Increasing the warehouse size adds more memory per node, allowing the aggregation to complete in-memory and eliminating spilling. While other options might offer some benefits, they do not directly provide additional memory to the operation. Scaling up the warehouse is the most direct and effective solution for memory-intensive operations that are spilling.

Answer

Enable the Query Acceleration Service.

Answer

Add a clustering key on the group-by columns.

Answer

Create a materialized view for the aggregation.

Question 11

A healthcare analytics team stores patient encounter records in a Snowflake table that is updated continuously by an external ETL tool using MERGE statements. The team needs to build a downstream transformation that incrementally processes only the rows that were inserted or changed since the last run. They want to avoid reprocessing the entire table and do not want to add triggers or modify the ETL tool. Which Snowflake feature should the architect use to capture these changes?

Accepted Answer

Create a standard Stream on the encounter table to capture inserts, updates, and deletes.. A standard stream on the encounter table captures inserts, updates, and deletes by reading the table's change tracking metadata. This is exactly what the team needs because the ETL tool uses MERGE, which produces updates as well as inserts. Append-only streams would miss updates, the CHANGES clause lacks persistent offset tracking, and materialized views do not expose change data.

Answer

Use a materialized view that refreshes automatically to expose only changed rows.

Answer

Create a Stream on the encounter table with an append-only stream type.

Answer

Enable change tracking on the encounter table and query the CHANGES clause directly.

Question 12

A data architect is analyzing a query that performs poorly due to excessive data shuffling during a large join operation. The query joins two large tables on a non-clustered key. Which Snowflake feature can help reduce data shuffling by co-locating related data?

Accepted Answer

Clustering the tables on the join key. Clustering the tables on the join key helps co-locate matching rows, reducing the need to shuffle data across nodes during a join. This can significantly improve performance for large joins. The other options address different performance aspects: Search Optimization for point lookups, Query Acceleration for scan-heavy queries, and multi-cluster warehouses for concurrency.

Answer

Multi-cluster warehouse

Answer

Search Optimization Service

Answer

Query Acceleration Service

Question 13

A data engineering team needs to transform raw JSON events into a curated table that refreshes automatically as new data arrives, without writing or scheduling any orchestration code. The target must reflect changes within a defined lag and be queryable like a regular table. Which Snowflake feature should the architect recommend?

Accepted Answer

A Dynamic Table defined with a target lag, which Snowflake refreshes automatically based on the query definition and the specified lag.. Dynamic Tables let the architect declare the transformation and a target lag, and Snowflake handles scheduling, dependency tracking, and incremental refresh automatically. The result is a queryable table that stays current without external orchestration. Tasks require manual logic, streams and views do not persist a curated result, and standard views have no refresh mechanism.

Answer

A task with a schedule that runs a CREATE OR REPLACE TABLE AS SELECT statement every minute to rebuild the curated table from scratch.

Answer

A view with a scheduled refresh policy and a query acceleration service enabled to keep the result current.

Answer

A stream on the raw table plus a materialized view over the stream to expose the transformed rows as they arrive.

Question 14

A financial services company uses Snowflake to store sensitive customer data. They need to ensure that data is encrypted at rest and in transit, and that encryption keys are managed by the cloud provider's hardware security modules (HSMs). The company also requires that Snowflake support periodic key rotation. Which Snowflake feature should the architect recommend to meet these requirements?

Accepted Answer

Tri-Secret Secure with customer-managed keys in a cloud provider HSM.. Tri-Secret Secure is a Snowflake feature that combines a Snowflake-managed key with a customer-managed key stored in the cloud provider's HSM. This provides dual control over encryption keys, meets HSM requirements, and supports key rotation. It encrypts data at rest and in transit, and is available in Business Critical edition and higher.

Answer

Snowflake External Functions with AWS KMS encryption.

Answer

Snowflake-managed encryption with annual key rotation.

Answer

Client-side encryption using Snowflake's ENCRYPT function.

Question 15

A retail company ingests JSON clickstream events into a Snowflake table using Snowpipe streaming. The events contain a nested field 'user' with subfields 'id' and 'name'. The architect needs to query only the 'id' subfield without scanning the entire JSON. Which approach is most efficient?

Accepted Answer

Load the JSON into a table with a separate column for user_id, extracted during ingestion.. Extracting the required subfield into a dedicated column during ingestion is the most efficient method because it eliminates the need to parse the entire JSON document at query time. This approach leverages Snowflake's columnar storage and allows for better compression and pruning, resulting in faster queries and lower compute costs. It is ideal when a specific subfield is frequently queried.

Answer

Use a VARIANT column and query with a dot notation, e.g., SELECT user:id FROM events.

Answer

Use a VARIANT column and create a materialized view that selects user:id.

Answer

Use a VARIANT column and query with a lateral flatten, e.g., SELECT value:id FROM events, LATERAL FLATTEN(input => user).