ARA-C01 Practice Exam 3 — 20 Questions

Question 1

Which Snowflake feature helps minimize query latency by avoiding re-computation for identical queries?

Accepted Answer

Result Caching.. The Result Cache is an automatic, managed feature that stores the output of identical queries. When a user runs the exact same query again, Snowflake retrieves the result directly from the cache rather than re-computing it. This provides near-instantaneous performance for repeated workloads and is a key component of Snowflake's performance optimization strategy, reducing both latency and unnecessary compute costs for end users.

Answer

Query Acceleration Service.

Answer

Materialized Views.

Answer

Automatic Clustering.

Question 2

Refer to the exhibit. An architect needs to optimize this warehouse to handle a batch process that performs a massive table scan followed by a complex join on 10 billion rows. What is the most effective architectural change?

Accepted Answer

Change the size of the warehouse from X-SMALL to LARGE.. For a single, complex query involving a massive data set, the bottleneck is typically the compute power and memory available to the nodes. Increasing the size (vertical scaling) from X-Small to a larger size (e.g., Large or X-Large) provides more CPUs and memory per node. This allows the warehouse to process more data in parallel and reduces the need for spilling to local or remote disk.

Answer

Increase the max_cluster_count to 5 to enable multi-cluster scaling.

Answer

Set the auto_resume property to TRUE to ensure the warehouse starts automatically.

Answer

Alter the warehouse type from STANDARD to SNOWPARK-OPTIMIZED.

Question 3

An organization has a Network Policy applied at the Account level to restrict IP ranges. A specific user requires access from a home office IP not in the account range. How should the architect configure this while maintaining the strictest security posture?

Accepted Answer

Create a user-level network policy and assign it directly to that specific user account.. Snowflake evaluates network policies at the most granular level first, starting with the user, then the account. Applying a specific policy to the individual user overrides the account-level restrictions without opening access for the entire organization. This hierarchy allows for precise control over entry points while maintaining a broad security baseline for all other platform participants.

Answer

Modify the existing account-level network policy to include the user's home IP address.

Answer

Create a new role for the user and attach the network policy to that specific role.

Answer

Disable the account-level network policy and rely solely on multi-factor authentication for security.

Question 4

Why does Snowflake's architecture separate compute from storage?

Accepted Answer

To enable independent scaling and improve resource efficiency.. Separating compute from storage allows both layers to scale independently based on demand. You can scale storage without adding compute power and vice versa, which is highly cost-effective and prevents resource contention. This decoupling enables multiple workloads to access the same underlying data without impacting each other's performance, facilitating a multi-tenant environment where various business units can query the same data source simultaneously without the typical limitations found in monolithic database systems.

Answer

To ensure that all queries are executed on the same machine.

Answer

To force data to be moved into the compute node before processing.

Answer

To eliminate the need for any storage management.

Question 5

A financial organization needs to ensure that only connections originating from their corporate VPN IP range can access their Snowflake account. Which feature should the architect implement?

Accepted Answer

Apply a Network Policy at the account level.. Network Policies provide the primary mechanism for controlling access based on IP addresses. By defining an allowed list of CIDR blocks, the architect creates a perimeter defense that prevents unauthorized access from public networks. This is crucial for compliance in highly regulated industries, ensuring that data exposure risks are mitigated by restricting the attack surface to trusted network locations only, effectively blocking any connection attempts outside the defined enterprise perimeter.

Answer

Enable MFA for all users.

Answer

Configure SCIM provisioning.

Answer

Implement OAuth security integration.

Question 6

When would using a Search Optimization Service be inappropriate for a table?

Accepted Answer

When the table experiences extremely high DML volume.. The Search Optimization Service is not designed for every workload. It is specifically aimed at point-lookups on large tables. It is inappropriate for tables that are very small, where standard scanning is already extremely fast, or for tables that undergo extremely high-frequency DML updates, as the maintenance cost of the search index would become a performance bottleneck.

Answer

When the table is frequently queried by ID.

Answer

When the table is extremely large.

Answer

When the query filter uses equality predicates.

Question 7

When analyzing a query profile, you observe a high 'Remote Disk Spilling' metric. What is the most likely cause, and how can it be resolved?

Accepted Answer

Insufficient memory for operations; increase warehouse size.. Remote disk spilling happens when intermediate result sets exceed the local disk space of the compute nodes, forcing data to be written to remote storage (S3/Azure Blob). This significantly impacts latency. The resolution is to either increase the warehouse size to provide more local memory/disk space or optimize the query logic—specifically joining, sorting, or grouping operations—to reduce the amount of data being processed in memory.

Answer

The query is not using the Result Cache; enable the Result Cache.

Answer

Data is not clustered properly; re-cluster the table.

Answer

Network latency is high; change the cloud provider region.

Question 8

Refer to the exhibit. An architect is configuring a Snowflake Storage Integration to access an external S3 bucket. Based on the IAM policy shown, what will happen if a Snowflake user attempts to create an External Stage pointing to 's3://production-data/raw/'?

Accepted Answer

The operation will fail because the ListBucket permission is constrained to the 'logs/' prefix.. Snowflake requires both s3:ListBucket and s3:GetObject permissions to access data in an external stage. While the policy allows GetObject for the entire bucket, the ListBucket permission is restricted by a condition to only allow the 'logs/' prefix. Therefore, any attempt to list or access files in the 'raw/' prefix will result in an Access Denied error from AWS.

Answer

The stage will be created successfully and data can be loaded because GetObject is allowed.

Answer

The stage creation will fail immediately because Snowflake cannot validate the bucket's existence.

Answer

Data loading will work, but only if the user specifies the exact filenames in the COPY command.

Question 9

An architect is designing a table to support analytical queries. Which data type choice would most likely improve performance for filtering operations?

Accepted Answer

Using the smallest appropriate data type.. Using specific, numeric, or date/time types is significantly more efficient than storing data as strings (VARCHAR). Snowflake can perform range pruning and min/max tracking much better on structured types. Converting to the most restrictive data type possible reduces storage size and improves the speed at which the query engine can filter and scan data during execution.

Answer

Storing all numeric values as VARCHAR.

Answer

Storing dates as integers in a single column.

Answer

Using VARIANT for all columns.

Question 10

A company is implementing a Data Lakehouse architecture and wants to store their data in the Apache Iceberg format on S3 while still using Snowflake for high-performance analytics. What is the most important architectural consideration when using Snowflake-managed Iceberg tables?

Accepted Answer

Snowflake will handle all data maintenance and metadata updates for the table.. Iceberg tables in Snowflake can be either Snowflake-managed or Externally-managed. When Snowflake-managed, Snowflake handles the metadata and file orchestration, providing performance similar to native tables while storing data in an open format. This allows other tools to read the Parquet files while Snowflake remains the primary engine.

Answer

Snowflake-managed Iceberg tables cannot be queried by external engines like Spark.

Answer

The data must be stored in Snowflake's internal proprietary format to use Iceberg.

Answer

Iceberg tables do not support Time Travel or Fail-safe features.

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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