ARA-C01 Practice Exam 4 — 20 Questions

Question 1

An architect is configuring a Snowflake account to use Federated Authentication with Okta as the identity provider (IdP). The requirement is to allow users to authenticate to Snowflake using their Okta credentials and to automatically provision users and roles based on Okta group memberships. Which configuration steps must the architect perform to meet these requirements?

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

Accepted Answer

Configure SAML 2.0 in Okta and Snowflake, and enable SCIM provisioning to synchronize users and groups.. The correct configuration involves setting up SAML 2.0 for authentication and SCIM for automatic provisioning. SAML allows Okta to authenticate users, while SCIM synchronizes users and groups, enabling automatic creation and updates of Snowflake users and roles based on Okta group memberships. This dual approach satisfies both authentication and provisioning requirements.

Answer

Configure Snowflake to use Okta as an external OAuth server, and use Snowflake's built-in user provisioning.

Answer

Configure SAML 2.0 in Okta and Snowflake, and manually create users and roles in Snowflake to match Okta groups.

Answer

Configure OAuth 2.0 in Okta and Snowflake, and use the Snowflake Connector for Okta to sync users.

Question 2

A startup is preparing for its first SOC 2 audit. The auditor asks how the company prevents a compromised employee credential from being used from an unknown location while still allowing legitimate travel. The architect has already created a network policy listing the corporate ranges. What should the architect do to apply this control account-wide?

Accepted Answer

Set the network policy as the account-level policy using ALTER ACCOUNT SET NETWORK_POLICY so it applies to all users by default.. Applying a network policy at the account level makes it the default for all users, restricting connections to the listed corporate ranges and blocking unknown locations. User-level policies can still override it for specific cases such as traveling executives. Per-user assignment, sharing, or invented parameters do not deliver the account-wide default control the auditor is asking about.

Answer

Assign the network policy to each user individually using ALTER USER ... SET NETWORK_POLICY so that only named users are restricted.

Answer

Create a share that exposes the network policy to the account and grant it to PUBLIC so all roles inherit the restriction.

Answer

Enable the account parameter REQUIRE_NETWORK_POLICY and rely on Snowflake to block sessions that lack a matching policy.

Question 3

A Snowflake architect is designing a multi-cluster warehouse to handle varying concurrency demands for a BI dashboard that experiences peak usage during business hours. The architect wants to ensure that queries are not queued and that the warehouse scales out automatically. Which configuration should the architect use?

Accepted Answer

Set the warehouse to multi-cluster mode with scaling policy set to STANDARD.. To prevent queuing during peak concurrency, a multi-cluster warehouse with the STANDARD scaling policy is optimal. The STANDARD policy adds clusters immediately when queries are queued, ensuring low latency. ECONOMY may delay scaling, and single-cluster or fixed cluster count configurations cannot scale out dynamically.

Answer

Set the warehouse to multi-cluster mode with a minimum cluster count of 1 and maximum cluster count of 1.

Answer

Set the warehouse to single-cluster mode with a larger warehouse size.

Answer

Set the warehouse to multi-cluster mode with scaling policy set to ECONOMY.

Question 4

A Snowflake architect is optimizing a virtual warehouse for a workload that consists of many small, concurrent queries with high concurrency requirements. The queries are simple and return small result sets. The architect wants to minimize queuing and ensure fast response times while controlling costs. Which warehouse configuration should the architect choose?

Accepted Answer

A multi-cluster warehouse with Standard scaling policy and a small warehouse size.. A multi-cluster warehouse with Standard scaling policy and a small warehouse size is optimal for many small, concurrent queries. Standard policy adds clusters immediately when queries are queued, reducing latency. Small size keeps costs low for simple queries. This setup ensures high concurrency without over-provisioning compute resources.

Answer

A multi-cluster warehouse with Economy scaling policy and a medium warehouse size.

Answer

A single-cluster warehouse with a small warehouse size and increased statement timeout.

Answer

A single-cluster warehouse with a large warehouse size.

Question 5

An architect is evaluating Snowflake's Virtual Warehouse architecture for a new workload that requires continuous, high-concurrency query execution with minimal latency. The workload consists of many small, short-running queries issued by hundreds of users simultaneously. Which configuration should the architect recommend to best meet these requirements?

Accepted Answer

Deploy a single multi-cluster warehouse with a minimum of 3 clusters and a maximum of 10 clusters, using STANDARD scaling policy.. High-concurrency, short-running queries benefit from a multi-cluster warehouse with STANDARD scaling, which aggressively adds clusters to reduce queuing. A minimum of 3 clusters handles baseline concurrency, and a maximum of 10 allows scaling for peaks. ECONOMY scaling is more conservative and may introduce latency, while single-cluster or per-user warehouses do not scale as efficiently.

Answer

Deploy a single large warehouse with a maximum cluster count of 1 and enable auto-suspend after 60 seconds.

Answer

Deploy a single multi-cluster warehouse with ECONOMY scaling policy and a maximum of 2 clusters.

Answer

Deploy multiple independent warehouses, each sized X-Small, and assign each user to a dedicated warehouse.

Question 6

A security team is designing a Snowflake deployment where they need to centrally manage user access to a set of databases across multiple accounts in an organization. They want to define a set of privileges once and grant them to roles in each account without recreating the roles in every account. Which Snowflake feature should they use?

Accepted Answer

Organization roles. Organization roles allow centralized management of privileges across multiple accounts within a Snowflake organization. They are defined once and can be granted to accounts, eliminating the need to recreate roles in each account. This provides a scalable and consistent way to manage access, which is exactly what the security team needs.

Answer

Account roles

Answer

Application roles

Answer

Database roles

Question 7

A Snowflake architect needs to design a solution for a global retail company that requires near real-time analytics on streaming point-of-sale data. The data arrives continuously via Snowpipe and must be immediately available for complex joins with historical sales data. The architect must minimize data latency and ensure that the compute resources for ingestion do not compete with those for analytical queries. Which Snowflake feature should the architect implement to meet these requirements?

Accepted Answer

Snowpipe Streaming. Snowpipe Streaming is the correct choice because it provides a direct, low-latency path for streaming data into Snowflake tables, bypassing the need for file staging and reducing latency to seconds. It uses a separate compute service that does not consume virtual warehouse credits, ensuring that ingestion does not compete with analytical queries. This aligns with the need for near real-time analytics on streaming point-of-sale data.

Answer

Snowpipe with auto-ingest

Answer

Snowflake Tasks with streams

Answer

Snowflake External Tables

Question 8

A data architect needs to load data from an external stage into a Snowflake table. The data files are in Parquet format and are updated daily. The architect wants to minimize storage costs and avoid data duplication. Which command should be used to load only new or changed files?

Accepted Answer

COPY INTO with the load metadata tracking enabled to skip files already loaded.. COPY INTO leverages Snowflake's load metadata to track which files have been loaded from an external stage. It skips files that were previously loaded, ensuring only new or changed files are ingested. This automatic tracking prevents duplicates and reduces storage costs by avoiding redundant data.

Answer

COPY INTO with the VALIDATION_MODE option to validate files before loading.

Answer

COPY INTO with the PATTERN option to match file names based on date.

Answer

COPY INTO with the FILES option to specify a list of files to load.

Question 9

A healthcare analytics company stores patient encounter data in a Snowflake table with columns: encounter_id (NUMBER), patient_id (NUMBER), encounter_date (DATE), and diagnosis_code (VARCHAR). The table is 20 TB and grows by 100 GB per day. Most queries filter on encounter_date and join to a patient dimension on patient_id. The architect must design a clustering key to optimize these queries while minimizing reclustering cost. Which clustering key should the architect choose?

Accepted Answer

CLUSTER BY (encounter_date, patient_id). The clustering key should lead with the column most frequently used in range filters, which is encounter_date, and then include the join column patient_id. This order enables partition pruning on date ranges and improves join performance by co-locating related patient rows. The reversed order or unrelated columns fail to support the dominant query patterns and would increase scan volume and reclustering overhead.

Answer

CLUSTER BY (patient_id, encounter_date)

Answer

CLUSTER BY (encounter_id)

Answer

CLUSTER BY (diagnosis_code, encounter_date)

Question 10

A Snowflake architect is tasked with improving the performance of a dashboard that runs several queries against a large fact table. The queries filter on different columns and are run concurrently by many users. The architect notices that the warehouse is often queued due to high concurrency. Which configuration change is most appropriate to reduce queuing and improve concurrency?

Accepted Answer

Enable multi-cluster warehouse with a minimum and maximum cluster count greater than 1.. High concurrency causing queuing is best resolved by scaling out with a multi-cluster warehouse. By configuring minimum and maximum clusters greater than 1, Snowflake can automatically add clusters when queries are queued, allowing more concurrent queries to run. Scaling up or enabling other features does not increase concurrency capacity.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Increase the warehouse size to a larger T-shirt size.

Answer

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

Question 11

An architect is analyzing a Snowflake query that performs poorly. The Query Profile shows a high percentage of time spent in the 'TableScan' operator with a large number of partitions scanned, despite a filter on a column with high selectivity. The table is not clustered. Which action is most likely to improve performance?

Accepted Answer

Add a clustering key on the filtered column.. The query scans many partitions because the table is not clustered, even though the filter is selective. Adding a clustering key on the filtered column reorganizes data so that only relevant micro-partitions are read. This reduces I/O and TableScan time. Other options may provide some benefit but do not directly solve the partition pruning issue.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Create a materialized view on the filtered column.

Answer

Increase the warehouse size to add more compute nodes.

Question 12

A data architect is designing a multi-tenant environment where each tenant has its own database. The architect wants to ensure that users from one tenant cannot access data from another tenant, even if they have the same role name. Which Snowflake feature should the architect use to isolate access?

Accepted Answer

Separate accounts per tenant. For strict multi-tenant isolation, using separate Snowflake accounts per tenant is the most robust approach. Each account is a separate security and management domain, ensuring that users, roles, and data are completely isolated. This prevents any possibility of cross-tenant access through shared roles or objects.

Answer

Network policies

Answer

Database roles

Answer

Secure views

Question 13

An architect is tuning a query that joins a large fact table to a dimension table. The query profile shows that the join is a full outer join and that the fact table is scanned entirely, even though the query filters on a column from the dimension table. The dimension table is small. The architect wants to reduce the amount of data scanned without changing the result set. Which action is most likely to improve performance?

Accepted Answer

Rewrite the full outer join as an inner join if the business logic does not require unmatched rows.. Full outer joins prevent the optimizer from pushing dimension filters down to the fact table, forcing a full scan. When unmatched rows are not needed, rewriting as an inner join allows predicate pushdown, so the fact table reads only matching rows. This reduces I/O and speeds up the query without altering the required result set.

Answer

Increase the warehouse size to give the join more memory.

Answer

Materialize the dimension table as a temporary table before the join.

Answer

Add a clustering key on the fact table's join column to improve pruning.

Question 14

A security architect is designing a Snowflake environment for a company with strict data governance requirements. They need to implement column-level security to mask sensitive data based on the user's role and also track which columns are being accessed by which users. Which two Snowflake features should the architect use to achieve these goals? (Choose two.)

Accepted Answer

Dynamic Data Masking. The requirements are to mask sensitive data based on role and to track column access. Dynamic Data Masking provides column-level masking based on the user's role, satisfying the first requirement. Access History records which columns are accessed by which queries and users, satisfying the second requirement. Row Access Policies filter rows, Object Tagging is for classification, and Secure Views do not provide the needed masking or auditing.

Answer

Secure Views

Answer

Object Tagging

Answer

Row Access Policies

Question 15

A retail company uses Snowflake to analyze point-of-sale data. They notice that queries filtering on the 'transaction_date' column are slow because the table is not clustered on that column. The table is very large and experiences frequent inserts. Which Snowflake feature should the architect recommend to automatically maintain clustering on the 'transaction_date' column?

Accepted Answer

Define a clustering key on 'transaction_date' and enable automatic clustering.. Defining a clustering key on 'transaction_date' and enabling automatic clustering allows Snowflake to maintain the clustering as new data is inserted. Automatic clustering runs in the background, ensuring that queries filtering on that column benefit from partition pruning. This is the native, recommended solution for large, frequently updated tables. Other options either do not address clustering or are inefficient.

Answer

Use a task to periodically run ALTER TABLE ... CLUSTER BY on the table.

Answer

Create a materialized view that selects all columns and filters on 'transaction_date'.

Answer

Enable the search optimization service on the 'transaction_date' column.

Question 16

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.)

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 17

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 18

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 19

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 20

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.