ARA-C01 Snowflake Architecture Practice Test 2 — 15 Questions

Question 1

A table with 100TB of data is frequently queried using filters on three different columns: 'region', 'status', and 'order_date'. The architect wants to optimize performance for all these queries. Why is clustering the table on all three columns potentially less effective than using the Search Optimization Service?

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

Accepted Answer

Multi-column clustering is subject to the 'curse of dimensionality' and reduces pruning effectiveness.. Clustering is most effective when queries follow the order of the clustering key (prefix). As the number of columns in a clustering key increases, the 'co-locality' of data for each individual column decreases. Search Optimization Service, however, builds independent search structures for each column, allowing for efficient point lookups regardless of which column is filtered or the order of the filters.

Answer

Search Optimization Service is free to maintain, whereas clustering has a compute cost.

Answer

Clustering keys can only be defined on a single column in Snowflake.

Answer

Search Optimization Service only works on tables smaller than 10TB.

Question 2

A company is planning a Disaster Recovery (DR) strategy for their Snowflake environment across two different regions. Which TWO statements are correct regarding Snowflake Database Replication and Failover? (Choose TWO)

Accepted Answer

Failover Groups can include databases, shares, and account-level objects like roles.. Snowflake's Business Critical and higher editions support failover and failback, allowing a secondary database to be promoted to primary during a regional outage. This is managed through Failover Groups, which synchronize not just data, but also roles, users, and grants, ensuring that the secondary site is functionally equivalent to the primary for seamless application transition.

Answer

Replication is synchronous, ensuring zero data loss (RPO=0) between regions.

Answer

The secondary database is read-write and can accept updates at any time.

Answer

Replication only works between accounts in the same Snowflake Organization.

Question 3

Which TWO of the following statements accurately describe the characteristics of Snowflake's Cloud Services layer?

Accepted Answer

It handles the maintenance of metadata and query results caching.. The Cloud Services layer is the brain of Snowflake, managing authentication, access control, metadata, query optimization, and result caching. It is essential to understand that this layer is compute-intensive for orchestration but distinct from the Virtual Warehouse compute clusters used for data processing. Its functions ensure that users interact with the system securely and efficiently, maintaining the transactional integrity of the Snowflake platform across all regional deployments.

Answer

It manages the execution of DML and DDL queries.

Answer

It is responsible for data storage and compression.

Answer

It is elastic and scales automatically based on query complexity.

Question 4

Which component is primarily responsible for performing 'query pruning' during the execution of a SQL statement?

Accepted Answer

Cloud Services Layer. Query pruning is a critical performance feature where Snowflake uses metadata about micro-partitions to skip scanning irrelevant data files. This process is orchestrated by the Cloud Services layer, which consults the metadata store to determine which files match the filter criteria. This reduces I/O and speeds up query execution, making it a fundamental concept for performance tuning and efficient storage management in Snowflake.

Answer

Virtual Warehouse

Answer

Micro-partition storage layer

Answer

Query Compiler

Question 5

Which THREE factors influence the performance of a Virtual Warehouse in Snowflake?

Accepted Answer

Warehouse Size. Virtual Warehouse performance is determined by a mix of physical compute resources (size), concurrency management (multi-cluster), and cache usage. Understanding these levers is essential for architects to balance cost and performance. By adjusting the warehouse size or enabling auto-scaling, architects can address different bottleneck scenarios, such as complex calculations requiring more CPU/RAM versus high-concurrency workloads requiring parallel execution paths for independent requests.

Answer

Database compression ratio

Answer

Query history retention policy

Question 6

An architect is designing a multi-tenant SaaS application on Snowflake where each tenant has a separate database. The architect wants to ensure strict isolation of compute resources and data access, and minimize cross-tenant data leakage. Which two Snowflake features should the architect implement? (Choose two.)

Accepted Answer

Virtual warehouses per tenant. To achieve strict compute isolation, each tenant should have its own virtual warehouse, preventing resource contention. For data isolation, row access policies can restrict each tenant to only their rows, even in shared tables. Replication, cloning, and multi-cluster warehouses do not provide the necessary isolation for multi-tenant scenarios.

Answer

Multi-cluster warehouses with auto-scaling

Answer

Database replication to a separate region

Answer

Zero-copy cloning for each tenant

Question 7

A financial services company uses Snowflake to store sensitive customer data. They need to ensure that data remains encrypted at rest and in transit, and that encryption keys are managed by the cloud provider's KMS. The company also requires that data be re-encrypted when keys are rotated. Which Snowflake feature should the architect implement?

Accepted Answer

Customer-managed keys using cloud provider's KMS.. Customer-managed keys (CMK) using the cloud provider's KMS allow the organization to maintain control over encryption keys while leveraging Snowflake's automatic encryption at rest and in transit. When keys are rotated in the KMS, Snowflake seamlessly re-encrypts data with the new key version, ensuring continuous security. This approach satisfies all requirements: encryption, KMS management, and re-encryption upon rotation.

Answer

Snowflake-managed encryption with periodic key rotation.

Answer

Client-side encryption before loading data into Snowflake.

Answer

Tri-Secret Secure with customer-managed keys.

Question 8

A Snowflake architect is designing a data pipeline that loads JSON data from an external stage into a table. The JSON records contain nested arrays and objects, and the architect needs to flatten the data into relational columns. The architect also needs to ensure that the load process is idempotent and can handle duplicate files without duplicating data. Which two Snowflake features should the architect use? (Choose two.)

Accepted Answer

Use the VARIANT data type to store the JSON and the LATERAL FLATTEN function to explode arrays.. To flatten nested JSON, the architect should use the VARIANT data type and LATERAL FLATTEN. To ensure idempotent loads and avoid duplicates, the COPY command's load metadata should be leveraged, along with the PATTERN option to select files. These two features together meet the requirements of flattening and idempotency.

Answer

Use the COPY command with the MATCH_BY_COLUMN_NAME option to map JSON fields to columns.

Answer

Enable the LOAD_UNCERTAIN_FILES option in the COPY command to avoid duplicate loads.

Answer

Create a stream on the external stage to capture new files and use a task to process them.

Question 9

A Snowflake architect is designing a data pipeline that ingests semi-structured JSON events from an external stage into a table. The events contain nested arrays and the architect needs to query individual elements efficiently using Snowflake's native functions. Which architectural feature should the architect leverage to optimize query performance on these nested arrays?

Accepted Answer

Use the GET_PATH function to extract specific elements and create a materialized view with a clustering key.. The correct approach is to extract nested elements using GET_PATH or colon notation and then materialize those extractions in a view with a clustering key. This allows Snowflake to prune micro-partitions based on the clustered extracted values, optimizing queries that filter on those elements. This leverages Snowflake's architecture for efficient semi-structured data access.

Answer

Store the JSON in a VARIANT column and use the ARRAY_CONTAINS function for filtering.

Answer

Use the FLATTEN function with a LATERAL join to explode the arrays into rows.

Answer

Leverage Snowflake's automatic partitioning and pruning on the VARIANT column using the PARSE_JSON function.

Question 10

A Snowflake architect is designing a multi-cluster warehouse to handle varying concurrency for a BI dashboard that experiences peak usage during business hours. The architect must ensure that queries are queued appropriately and that scaling policies minimize latency while controlling costs. Which two statements accurately describe the behavior of Snowflake's multi-cluster warehouse scaling policies? (Choose two.)

Accepted Answer

In Standard scaling policy, Snowflake starts additional clusters immediately when queries are queued, and shuts them down after a period of inactivity.. The Economy scaling policy delays starting additional clusters until it estimates at least 6 minutes of sustained load, conserving credits. The Standard scaling policy starts clusters immediately when queries are queued to minimize latency. These two behaviors are key to choosing the right policy for performance versus cost. The other options contain inaccuracies about shutdown timing or edition availability.

Answer

In Economy scaling policy, Snowflake shuts down idle clusters after exactly 5 minutes of inactivity to save costs.

Answer

Multi-cluster warehouses can only be used with the Enterprise Edition or higher.

Answer

The scaling policy can be changed dynamically while the warehouse is running without affecting active queries.

Question 11

A healthcare company is deploying Snowflake on AWS and needs to ensure that data stored in an external S3 bucket can be accessed securely by Snowflake without embedding AWS credentials in the Snowflake account. The architect must configure a storage integration. Which two actions are required to set up the storage integration? (Choose two.)

Accepted Answer

Create a storage integration object in Snowflake using the CREATE STORAGE INTEGRATION command, specifying the IAM role ARN.. The two required actions are creating an IAM role in AWS with a trust relationship to Snowflake's IAM user, and creating a storage integration object in Snowflake with the role ARN. These steps establish secure, credential-less access to the S3 bucket. Other options are either incorrect or not part of the standard configuration. The integration leverages temporary credentials, eliminating the need for static keys.

Answer

Attach an AWS KMS key to the S3 bucket and grant Snowflake decrypt permissions.

Answer

Configure a Snowflake network policy to allow traffic from the S3 bucket's IP range.

Answer

Generate a Snowflake-managed AWS access key and secret key for the integration.

Question 12

An architect needs to provide a data analyst with read-only access to a small subset of rows in a production table. The analyst must not be able to see any other rows, and the filtering must be enforced automatically without the analyst writing a WHERE clause. Which Snowflake feature should the architect implement?

Accepted Answer

Secure view with a WHERE clause embedded in the view definition. A secure view is the correct choice because it encapsulates the row-filtering logic and prevents unauthorized access to the underlying table. The view owner defines the WHERE clause, and the analyst queries the view without needing to add predicates. This enforces row-level security automatically and is a common Snowflake pattern for restricting data access.

Answer

Materialized view that pre-computes the filtered result set

Answer

Dynamic data masking policy applied to the columns containing sensitive data

Answer

Row access policy attached directly to the production table

Question 13

A Snowflake architect is designing a data sharing solution where a provider account shares a secure view with a consumer account. The secure view is defined on a table that includes a column with sensitive data. The architect must ensure that the consumer cannot see the underlying table definition or the sensitive column, but can still query aggregated results. Which approach should the architect take?

Accepted Answer

Create a secure view that excludes the sensitive column and grant SELECT on the view to the consumer.. A secure view is required to hide the view definition and underlying table schema from the consumer. By excluding the sensitive column, the consumer cannot access it. Granting SELECT on the secure view allows aggregated queries while protecting sensitive data. Regular views, base table grants, or materialized views do not meet the security requirements.

Answer

Grant SELECT on the base table to the consumer but create a row access policy to filter out rows with sensitive data.

Answer

Create a materialized view that aggregates the data and excludes the sensitive column, and grant SELECT on the materialized view to the consumer.

Answer

Create a regular view that excludes the sensitive column and grant SELECT on the view to the consumer.

Question 14

A healthcare company needs to share patient data with an external research partner without copying the data. The partner uses a different Snowflake account in the same region. The company wants to ensure that the partner can only read a specific subset of columns and cannot see sensitive fields like patient names. Which Snowflake feature should the architect use?

Accepted Answer

Create a secure view that selects only the non-sensitive columns and grant the partner access to that view.. A secure view allows the company to define a view that selects only the non-sensitive columns and share that view with the partner. Secure views prevent the partner from seeing the underlying table definition or accessing columns not included in the view. This meets the requirements of no data copying and column-level restriction.

Answer

Export the required columns to a CSV file, stage it in an external stage, and share the stage URL with the partner.

Answer

Create a database share and grant the partner SELECT on the entire table, then use row access policies to filter rows.

Answer

Use Snowflake Data Marketplace to publish a listing with column-level security.

Question 15

An architect is designing a data sharing solution where a provider account wants to share a secure view with a consumer account. The secure view includes a WHERE clause that filters data based on the consumer's account. The architect wants to ensure that the consumer cannot see the underlying data or the view definition. Which Snowflake feature should be used to share the secure view?

Accepted Answer

Create a share, grant SELECT on the secure view, and reference CURRENT_ACCOUNT() in the view's WHERE clause.. A secure view can encapsulate row-level filtering logic using CURRENT_ACCOUNT() to dynamically filter data based on the consumer's account. When shared, the consumer can query the view without seeing the underlying data or the view definition. This meets the requirement for secure data sharing with per-consumer filtering.

Answer

Create a share and grant SELECT on the secure view to the share.

Answer

Create a share, grant SELECT on the secure view, and use a row access policy on the underlying table.

Answer

Use a secure user-defined function (UDF) in the view definition.