ARA-C01 Practice Test 8 — 10 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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.