ARA-C01 Practice Questions 2 — 50 Questions

Question 1

Which property of External Tables should an architect configure to ensure that Snowflake does not scan all files in the underlying S3/Azure/GCS bucket for every query?

Accepted Answer

PARTITION BY. Partitioning is the key to performance for External Tables. By defining partitions that match the folder structure in cloud storage, Snowflake can use the metadata to skip entire directories (files) that do not match the query's filter criteria, significantly reducing the I/O required for the query.

Answer

AUTO_REFRESH = TRUE

Answer

FILE_FORMAT = (TYPE = PARQUET)

Answer

INTEGRATION = 'MY_STORAGE_INT'

Question 2

Which Snowflake architectural layer is responsible for the 'Time Travel' functionality?

Accepted Answer

The Cloud Services layer.. Time Travel works because micro-partitions are immutable. When data is modified, Snowflake preserves the original micro-partitions for a defined period based on the object's retention policy. The Cloud Services layer acts as a time-based index, allowing the system to query the state of the metadata as it existed at a specific point in time, enabling users to reconstruct previous data states without requiring manual backups.

Answer

The compute warehouse layer.

Answer

The cloud storage object repository.

Answer

The local SSD cache of the virtual warehouse.

Question 3

A healthcare organization uses Snowflake to store sensitive patient data. They need to implement column-level security that allows users with the role 'DOCTOR' to see full patient IDs, while users with the role 'RESEARCHER' should see only the last four digits. The organization wants a centralized, reusable solution that can be applied to multiple columns across different tables. Which Snowflake feature should the architect use?

Accepted Answer

Dynamic Data Masking with a masking policy that checks the current role and applies the appropriate masking.. Dynamic Data Masking with a masking policy is the correct feature because it allows column-level masking based on the current role. The policy can be written to show full data to the DOCTOR role and masked data to others. Masking policies are centralized and can be applied to multiple columns, making them reusable. Secure views, row access policies, and object tagging do not provide the required dynamic column-level masking.

Answer

Row Access Policies that limit which rows are visible based on the user's role.

Answer

Object Tagging with a tag that indicates the sensitivity level, combined with a policy that enforces access.

Answer

Secure Views that filter the data based on the current role.

Question 4

An architect is configuring a Snowflake account to support a workload that requires reading data from an external Apache Iceberg table stored in an external cloud storage location. The architect wants to ensure that queries against the Iceberg table can leverage Snowflake's compute and caching. Which Snowflake feature should the architect use?

Accepted Answer

Iceberg tables. Snowflake Iceberg tables enable native integration with Apache Iceberg, allowing Snowflake to query and write to external Iceberg data using its compute and caching. This feature supports ACID transactions and schema evolution, making it ideal for lakehouse architectures. External tables and stages do not provide the same level of integration.

Answer

Secure views

Answer

External tables

Answer

External stages

Question 5

A Snowflake architect is designing a solution where external users need to access specific data in a Snowflake database without having Snowflake accounts. They want to provide read-only access to a few tables and ensure that the external users cannot see any other data. Which Snowflake feature should they use?

Accepted Answer

Snowflake Reader Accounts. Reader Accounts are designed for sharing data with consumers who do not have Snowflake accounts. The provider creates and manages the reader account, and can grant access to specific shares, ensuring read-only access to the desired tables. This meets the requirement for external users to access data without having their own Snowflake accounts.

Answer

Secure Data Sharing

Answer

External Tables

Answer

Materialized Views

Question 6

An architect is investigating a performance regression in a query that previously ran quickly. The query involves a join between a large fact table and a small dimension table. The query profile shows a significant amount of time spent in the 'Join' operator, with many rows being processed. Which of the following is the most likely cause of the performance degradation?

Accepted Answer

The small dimension table has grown significantly and is no longer suitable for a broadcast join.. When a small dimension table grows, it may exceed the threshold for a broadcast join, causing the optimizer to switch to a hash join that shuffles the large fact table. This increases data movement and join processing time. The other options would typically manifest in different operators or with additional symptoms like spills.

Answer

The warehouse size was reduced, causing less memory for join operations.

Answer

Statistics on the fact table are stale, leading to a poor join order.

Answer

The fact table has been clustered on a different column, causing poor pruning.

Question 7

A data engineer is designing a batch transformation pipeline using Dynamic Tables. The source table is updated hourly, and the target Dynamic Table must reflect changes within 30 minutes. The transformation involves a complex join and aggregation. Which approach best meets the freshness requirement while minimizing cost?

Accepted Answer

Set the target lag to 30 minutes and use a dedicated virtual warehouse sized based on the complexity of the transformation, monitoring and adjusting as needed.. Dynamic Tables refresh automatically based on the target lag. The warehouse size must be adequate to complete the refresh within the lag. Starting with a size based on transformation complexity and monitoring allows tuning to meet freshness without overspending. This approach balances performance and cost effectively.

Answer

Set the target lag to 30 minutes and use a dedicated virtual warehouse sized as XSMALL.

Answer

Set the target lag to 30 minutes and use a multi-cluster warehouse with a minimum of 1 and maximum of 3 clusters.

Answer

Set the target lag to 30 minutes and use a dedicated virtual warehouse sized as LARGE.

Question 8

A Snowflake architect is implementing a Type-2 slowly changing dimension (SCD2) on the CUSTOMER_DIM table using a Stream on the source table CUSTOMER_RAW and a task that runs every 5 minutes. The task currently reads the stream and applies a MERGE that only handles inserts and updates. Historical versions are lost. The architect must preserve prior attribute values for changed customers and mark each row with effective and end timestamps. Which approach should the architect use to meet this requirement?

Accepted Answer

Use a MERGE that, for matched rows whose tracked attributes changed, sets the end timestamp and an is_current flag to false on the existing row, and inserts a new row with a new effective timestamp and is_current true.. SCD2 requires that a changed business key produce a new row while the previous row is expired rather than overwritten. A MERGE driven by the stream can detect changed attributes, close the existing current row with an end timestamp and a false current flag, and insert a new current row with a new effective timestamp. This preserves complete history and keeps exactly one active version per key.

Answer

Add a row-version column to CUSTOMER_DIM and use a MERGE with a WHEN MATCHED THEN UPDATE clause that increments the version and overwrites the attribute values in place.

Answer

Configure the CUSTOMER_DIM table with CHANGE_TRACKING = TRUE and query the table's change tracking metadata to reconstruct prior versions on demand.

Answer

Replace the task with a Materialized View over CUSTOMER_RAW that automatically retains a copy of each historical version whenever the base table changes.

Question 9

An architect is designing a data pipeline that ingests JSON data into a Snowflake table with a VARIANT column. The pipeline uses Snowpipe for continuous ingestion. The architect notices that some JSON records contain nested arrays and occasionally exceed 16 MB in size. Which statement accurately describes a limitation or behavior the architect must consider?

Accepted Answer

The VARIANT data type can store up to 16 MB of uncompressed data per value, and exceeding this limit causes the load to fail with an error.. VARIANT columns in Snowflake have a strict 16 MB uncompressed size limit per value. When ingesting JSON via Snowpipe, if any single record exceeds this, the load fails. Architects must preprocess data to split or truncate large records. Compression does not change the uncompressed size limit, and Snowpipe does not split records automatically.

Answer

The VARIANT data type has no size limit, but Snowpipe enforces a 16 MB limit per file.

Answer

Snowpipe compresses JSON data, so the 16 MB limit applies to the compressed size, allowing larger uncompressed records.

Answer

Snowpipe automatically splits large JSON records larger than 16 MB into multiple VARIANT rows to comply with the size limit.

Question 10

A company wants to allow their data analysts to query data in a specific database but prevent them from viewing the underlying table definitions. Which Snowflake feature should the architect recommend?

Accepted Answer

Secure views. Secure views hide both the view definition and the underlying table definitions from users who do not have the necessary privileges. When analysts query a secure view, they can retrieve data without seeing the SQL or the base tables. This makes secure views the appropriate feature to meet the requirement of querying data while preventing viewing of table definitions.

Answer

Object tagging

Answer

Column-level security

Answer

Row access policies

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.