ARA-C01 Practice Test 4 — 25 Questions

Question 1

An architect is designing a near-real-time ingestion path using Snowpipe streaming into a target table. The team must choose TWO design decisions that reduce end-to-end latency and cost for continuously arriving events. (Choose two.)

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

Accepted Answer

Use the Snowpipe streaming SDK or Snowflake Ingest SDK to push rows directly rather than writing files and triggering a pipe.. Snowpipe streaming lowers latency by pushing rows directly through the SDK instead of staging files, and the client flush interval determines how quickly buffered rows become visible. Together these reduce freshness delay and avoid file-handling overhead. Clustering, scheduled file-based loads, and task-driven inserts either add delay, add cost, or replace the streaming path with a slower polling model.

Answer

Batch events into larger files and rely on a scheduled COPY INTO to load them periodically.

Answer

Configure the target table with clustering on the event timestamp to improve insert throughput.

Answer

Disable the pipe and instead use a task that runs every minute to call INSERT statements against the target table.

Question 2

A data engineer is loading data into a Snowflake table using the COPY INTO command. The source files are in an external stage and have a consistent schema. The engineer wants to ensure that any errors during loading do not cause the entire load to fail, but rather log the errors for later review. Which COPY INTO option should the engineer use?

Accepted Answer

ON_ERROR = 'CONTINUE'. The ON_ERROR = 'CONTINUE' option allows COPY INTO to skip erroneous rows and continue loading the remaining rows. Errors are logged in the load metadata, which can be queried using the VALIDATE function or by checking the COPY_HISTORY. This ensures that the load does not fail entirely while still capturing error details for later review. Other options either abort the load, skip entire files, or only validate without loading.

Answer

ON_ERROR = 'SKIP_FILE'

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Question 3

A data engineer is building a dashboard that queries a large sales table with filters on region and date. The table is not clustered, and queries scan many micro-partitions. The engineer wants to improve query performance with minimal ongoing maintenance. Which Snowflake feature should the engineer use?

Accepted Answer

A clustering key on the region and date columns.. Clustering on the columns used in the dashboard filters (region and date) enables Snowflake to prune micro-partitions, reducing the data scanned. Snowflake automatically maintains clustering, so ongoing effort is minimal. This directly addresses the performance issue of scanning many micro-partitions and is the recommended approach for large tables with recurring filter patterns.

Answer

A materialized view that pre-aggregates sales by region and date.

Answer

Search Optimization Service on the region column.

Answer

Increasing the warehouse size to scan the table faster.

Question 4

A Snowflake architect is investigating a query that performs poorly due to a large hash join. The query joins a 5TB fact table with a 10GB dimension table. The fact table is not clustered. The query filters the fact table on a date range that covers 10% of the data. Which optimization is most likely to improve performance with minimal cost?

Accepted Answer

Add a clustering key on the date column of the fact table.. Clustering the fact table on the date column enables micro-partition pruning for the date range filter, reducing the data scanned by the join. This directly addresses the performance bottleneck without the overhead of larger warehouses or materialized views. It is the most cost-effective optimization for this scenario.

Answer

Increase the warehouse size to provide more memory for the hash join.

Answer

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

Answer

Enable the Search Optimization Service on the fact table's date column.

Question 5

A retail company ingests a daily 2 TB CSV file into a Snowflake table. The file is stored in an internal stage. The COPY INTO command currently runs with a single large file, and the load takes over 4 hours. The architect wants to reduce load time by leveraging parallelism. Which action should the architect take?

Accepted Answer

Split the large CSV file into multiple smaller files (e.g., 100–250 MB each) and use a single COPY INTO command with a pattern to load all files.. The key to faster loads is parallelizing across multiple files. A single large file is loaded by one thread, so splitting into smaller files allows Snowflake to distribute the load across many threads, significantly reducing elapsed time. Increasing warehouse size or changing format does not address the single-file bottleneck.

Answer

Use the VALIDATION_MODE = RETURN_ERRORS option to check for errors before loading, then rerun the COPY INTO command.

Answer

Increase the warehouse size to 4X-Large and rerun the same COPY INTO command on the single large file.

Answer

Convert the CSV file to JSON and load it using the VARIANT data type with a single COPY INTO command.

Question 6

A Snowflake architect is designing a multi-tenant environment where each tenant has its own database. The architect wants to ensure that tenant administrators can manage roles and users within their own database but cannot affect other tenants. Which Snowflake feature should be used to achieve this isolation?

Accepted Answer

Database roles. Database roles are scoped to a specific database and allow for granular privilege management within that database. By granting a tenant administrator a database role with the ability to create and manage other database roles, you enable them to manage access within their database without affecting other databases. Account roles are too broad and would not provide the needed isolation.

Answer

Network policies

Answer

Resource monitors

Answer

Account roles

Question 7

A financial services firm uses Snowflake with Tri-Secret Secure. They have integrated AWS KMS with Snowflake and manage their own key. During a security audit, the auditor asks how Snowflake ensures that data cannot be decrypted if the customer revokes access to their key. Which statement accurately describes the behavior?

Accepted Answer

If the customer revokes the key, Snowflake immediately loses the ability to decrypt data, and data becomes inaccessible.. Tri-Secret Secure uses a composite master key consisting of a Snowflake-managed key and a customer-managed key. If the customer revokes access to their key, Snowflake cannot reconstruct the composite key, making data undecryptable. This provides a strong security control where the customer retains ultimate control over data access. Other options incorrectly suggest Snowflake retains a copy or can bypass the customer key.

Answer

Revoking the key only prevents new data from being encrypted, but existing data remains accessible.

Answer

Snowflake stores a copy of the customer's key in an encrypted vault to ensure availability.

Answer

Snowflake can still decrypt data using its internal key, but the customer's key is required for auditing.

Question 8

A retail company ingests point-of-sale data into a Snowflake table using Snowpipe. The data arrives as JSON files in an internal stage. The architect needs to transform the semi-structured JSON into a relational format and load it into a reporting table. The transformation involves flattening nested arrays and applying several business rules. The volume is high, and the team wants to minimize latency and cost. Which approach should the architect recommend?

Accepted Answer

Load raw JSON into a staging table using Snowpipe, then use a Stream and a Task to transform and merge into the reporting table.. The recommended approach is to load raw JSON into a staging table via Snowpipe, then use a stream and task to incrementally transform and merge into the reporting table. This leverages Snowflake's native change tracking and scheduling, processes only new data, and avoids external dependencies. It balances latency and cost effectively for high-volume semi-structured data.

Answer

Create a materialized view on the staging table that automatically flattens the JSON and applies business rules.

Answer

Use a Snowpipe with a COPY INTO statement that includes a transformation query to flatten and transform the JSON during load.

Answer

Use an external function to call an AWS Lambda that transforms the JSON before loading into Snowflake.

Question 9

An architect is configuring a Snowflake account to use Federated Authentication with Okta. The requirement is that users must authenticate through Okta and receive Snowflake roles based on their Okta group membership. The architect has already configured the SAML integration. What additional step is required to map Okta groups to Snowflake roles?

Accepted Answer

Create a SCIM integration in Snowflake and configure the Okta provisioning connector.. To map Okta groups to Snowflake roles automatically, the architect must set up SCIM integration. SCIM allows Okta to provision users and groups into Snowflake, and Snowflake can then assign roles based on group membership. SAML alone handles authentication but not provisioning. The other options either misunderstand the role of SAML or propose custom solutions that are not standard.

Answer

Configure the Snowflake JDBC driver to pass Okta group information as a session parameter.

Answer

Create a SAML integration with the attribute 'Role' in the Okta SAML assertion.

Answer

Create a stored procedure that queries the Okta API and assigns roles based on group membership.

Question 10

An architect is designing a Snowflake environment for a global e-commerce platform. The platform needs to support read-heavy workloads from multiple regions with low latency. The data is stored in a single Snowflake account in the US West (Oregon) region. The architect wants to minimize data transfer costs and improve query performance for users in Europe and Asia. Which approach should the architect recommend?

Accepted Answer

Create a read-only replica of the primary database in each region using database replication and configure clients to connect to the nearest replica.. Database replication allows you to create a read-only replica of a database in another region. Clients can connect to the replica in their region, reducing latency and avoiding cross-region data transfer costs for reads. This is ideal for global read-heavy workloads.

Answer

Use Snowflake's multi-cluster warehouse feature to deploy warehouses in each region that read from the primary database.

Answer

Configure a Snowflake materialized view in each region that refreshes from the primary table.

Answer

Enable Snowflake's global data sharing by creating a share and granting access to accounts in each region.

Question 11

A security architect is configuring access control for a Snowflake environment. They need to ensure that a service account used by an ETL tool can only access specific tables in a schema and cannot create or drop any objects. The ETL tool connects using key-pair authentication. Which set of privileges should be granted to the service account's role to adhere to the principle of least privilege?

Accepted Answer

Grant USAGE on the database and schema, and SELECT on the specific tables.. The principle of least privilege requires granting only the privileges necessary to perform the required tasks. The ETL tool needs to read specific tables, so USAGE on the database and schema plus SELECT on those tables is sufficient. This avoids unnecessary privileges like object creation or data modification.

Answer

Grant USAGE on the database and schema, and CREATE TABLE on the schema.

Answer

Grant USAGE on the database and schema, and SELECT on all tables in the schema.

Answer

Grant USAGE on the database and schema, and ALL PRIVILEGES on the specific tables.

Question 12

A Snowflake architect is evaluating the use of Search Optimization Service for a large table that is frequently queried with highly selective filters on a column with high cardinality, such as a UUID. The architect wants to understand the benefits and limitations of enabling Search Optimization Service on this column. Which two statements are true regarding Search Optimization Service? (Choose two.)

Accepted Answer

Enabling Search Optimization Service on a column increases storage costs due to the additional search access path.. Search Optimization Service improves performance for highly selective queries using equality and IN predicates on optimized columns, and it incurs additional storage costs. It does not automatically optimize all columns, is ineffective for leading wildcards, and is not intended for join optimization. These characteristics make it suitable for point lookups on high-cardinality columns like UUIDs.

Answer

Search Optimization Service can be used to optimize joins between two large tables on the optimized column.

Answer

Search Optimization Service automatically optimizes all columns in the table without the need to specify which columns to optimize.

Answer

Search Optimization Service is most effective for queries that use wildcard characters at the beginning of a string pattern, such as LIKE '%abc'.

Question 13

A Snowflake architect is investigating a slow-running query that joins a large fact table with a small dimension table. The query profile shows that the join operation is using a Cartesian product, resulting in a massive number of rows processed. The architect checks the query and notices that the join condition is missing. Which action should the architect take to resolve the performance issue?

Accepted Answer

Rewrite the query to include an appropriate join condition between the fact and dimension tables.. A Cartesian product in a join is typically caused by a missing or incorrect join condition. The most effective fix is to rewrite the query to include the proper join predicate, which enables Snowflake to execute an efficient join algorithm. Other measures like adding filters or increasing warehouse size do not address the core problem.

Answer

Enable the Query Acceleration Service to automatically optimize the join.

Answer

Add a WHERE clause to filter the result set after the join.

Answer

Increase the size of the virtual warehouse to handle the larger result set.

Question 14

An e-commerce company wants to analyze JSON data stored in an external stage on Amazon S3. The JSON files contain nested arrays and objects, and the schema varies between files. The architect needs to query this data with Snowflake while minimizing data duplication and storage costs. Which approach should the architect recommend?

Accepted Answer

Create an external table with a VARIANT column and use the FLATTEN function to query nested elements.. External tables with a VARIANT column allow direct querying of JSON in the external stage without loading, avoiding duplication and storage costs. FLATTEN handles nested structures. This is the most efficient approach for variable-schema JSON when the goal is to minimize data movement and storage, unlike loading into a table or using materialized views.

Answer

Create a materialized view over the external stage that shreds the JSON into relational columns.

Answer

Use a directory table and a stored procedure to parse and insert JSON into a relational table.

Answer

Load the JSON files into a Snowflake table with a VARIANT column using COPY INTO, then query with FLATTEN.

Question 15

An architect is optimizing a Snowflake environment where a critical query joins a large fact table with a small dimension table. The Query Profile shows that the build side of the hash join is spilling to local disk. Which action is most likely to eliminate the spilling and improve performance?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Spilling to local disk in a hash join indicates that the build side does not fit in the available memory. Increasing the warehouse size provides more memory per node, allowing the build side to be held in memory. This eliminates spilling and improves join performance. Other options do not address the memory constraint.

Answer

Rewrite the query to use a nested loop join instead of a hash join.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

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

Question 16

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?

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 17

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 18

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 19

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 20

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