ARA-C01 Practice Test 9 — 10 Questions

Question 1

A Snowflake architect is designing a multi-tenant environment where each tenant has its own database. The architect wants to ensure that tenant administrators can manage roles and users within their own database but cannot affect other tenants. Which Snowflake feature should be used to achieve this isolation?

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

Accepted Answer

Database roles. Database roles are scoped to a specific database and allow for granular privilege management within that database. By granting a tenant administrator a database role with the ability to create and manage other database roles, you enable them to manage access within their database without affecting other databases. Account roles are too broad and would not provide the needed isolation.

Answer

Network policies

Answer

Resource monitors

Answer

Account roles

Question 2

A financial services firm uses Snowflake with Tri-Secret Secure. They have integrated AWS KMS with Snowflake and manage their own key. During a security audit, the auditor asks how Snowflake ensures that data cannot be decrypted if the customer revokes access to their key. Which statement accurately describes the behavior?

Accepted Answer

If the customer revokes the key, Snowflake immediately loses the ability to decrypt data, and data becomes inaccessible.. Tri-Secret Secure uses a composite master key consisting of a Snowflake-managed key and a customer-managed key. If the customer revokes access to their key, Snowflake cannot reconstruct the composite key, making data undecryptable. This provides a strong security control where the customer retains ultimate control over data access. Other options incorrectly suggest Snowflake retains a copy or can bypass the customer key.

Answer

Revoking the key only prevents new data from being encrypted, but existing data remains accessible.

Answer

Snowflake stores a copy of the customer's key in an encrypted vault to ensure availability.

Answer

Snowflake can still decrypt data using its internal key, but the customer's key is required for auditing.

Question 3

A retail company ingests point-of-sale data into a Snowflake table using Snowpipe. The data arrives as JSON files in an internal stage. The architect needs to transform the semi-structured JSON into a relational format and load it into a reporting table. The transformation involves flattening nested arrays and applying several business rules. The volume is high, and the team wants to minimize latency and cost. Which approach should the architect recommend?

Accepted Answer

Load raw JSON into a staging table using Snowpipe, then use a Stream and a Task to transform and merge into the reporting table.. The recommended approach is to load raw JSON into a staging table via Snowpipe, then use a stream and task to incrementally transform and merge into the reporting table. This leverages Snowflake's native change tracking and scheduling, processes only new data, and avoids external dependencies. It balances latency and cost effectively for high-volume semi-structured data.

Answer

Create a materialized view on the staging table that automatically flattens the JSON and applies business rules.

Answer

Use a Snowpipe with a COPY INTO statement that includes a transformation query to flatten and transform the JSON during load.

Answer

Use an external function to call an AWS Lambda that transforms the JSON before loading into Snowflake.

Question 4

An architect is configuring a Snowflake account to use Federated Authentication with Okta. The requirement is that users must authenticate through Okta and receive Snowflake roles based on their Okta group membership. The architect has already configured the SAML integration. What additional step is required to map Okta groups to Snowflake roles?

Accepted Answer

Create a SCIM integration in Snowflake and configure the Okta provisioning connector.. To map Okta groups to Snowflake roles automatically, the architect must set up SCIM integration. SCIM allows Okta to provision users and groups into Snowflake, and Snowflake can then assign roles based on group membership. SAML alone handles authentication but not provisioning. The other options either misunderstand the role of SAML or propose custom solutions that are not standard.

Answer

Configure the Snowflake JDBC driver to pass Okta group information as a session parameter.

Answer

Create a SAML integration with the attribute 'Role' in the Okta SAML assertion.

Answer

Create a stored procedure that queries the Okta API and assigns roles based on group membership.

Question 5

An architect is designing a Snowflake environment for a global e-commerce platform. The platform needs to support read-heavy workloads from multiple regions with low latency. The data is stored in a single Snowflake account in the US West (Oregon) region. The architect wants to minimize data transfer costs and improve query performance for users in Europe and Asia. Which approach should the architect recommend?

Accepted Answer

Create a read-only replica of the primary database in each region using database replication and configure clients to connect to the nearest replica.. Database replication allows you to create a read-only replica of a database in another region. Clients can connect to the replica in their region, reducing latency and avoiding cross-region data transfer costs for reads. This is ideal for global read-heavy workloads.

Answer

Use Snowflake's multi-cluster warehouse feature to deploy warehouses in each region that read from the primary database.

Answer

Configure a Snowflake materialized view in each region that refreshes from the primary table.

Answer

Enable Snowflake's global data sharing by creating a share and granting access to accounts in each region.

Question 6

A security architect is configuring access control for a Snowflake environment. They need to ensure that a service account used by an ETL tool can only access specific tables in a schema and cannot create or drop any objects. The ETL tool connects using key-pair authentication. Which set of privileges should be granted to the service account's role to adhere to the principle of least privilege?

Accepted Answer

Grant USAGE on the database and schema, and SELECT on the specific tables.. The principle of least privilege requires granting only the privileges necessary to perform the required tasks. The ETL tool needs to read specific tables, so USAGE on the database and schema plus SELECT on those tables is sufficient. This avoids unnecessary privileges like object creation or data modification.

Answer

Grant USAGE on the database and schema, and CREATE TABLE on the schema.

Answer

Grant USAGE on the database and schema, and SELECT on all tables in the schema.

Answer

Grant USAGE on the database and schema, and ALL PRIVILEGES on the specific tables.

Question 7

A Snowflake architect is evaluating the use of Search Optimization Service for a large table that is frequently queried with highly selective filters on a column with high cardinality, such as a UUID. The architect wants to understand the benefits and limitations of enabling Search Optimization Service on this column. Which two statements are true regarding Search Optimization Service? (Choose two.)

Accepted Answer

Enabling Search Optimization Service on a column increases storage costs due to the additional search access path.. Search Optimization Service improves performance for highly selective queries using equality and IN predicates on optimized columns, and it incurs additional storage costs. It does not automatically optimize all columns, is ineffective for leading wildcards, and is not intended for join optimization. These characteristics make it suitable for point lookups on high-cardinality columns like UUIDs.

Answer

Search Optimization Service can be used to optimize joins between two large tables on the optimized column.

Answer

Search Optimization Service automatically optimizes all columns in the table without the need to specify which columns to optimize.

Answer

Search Optimization Service is most effective for queries that use wildcard characters at the beginning of a string pattern, such as LIKE '%abc'.

Question 8

A Snowflake architect is investigating a slow-running query that joins a large fact table with a small dimension table. The query profile shows that the join operation is using a Cartesian product, resulting in a massive number of rows processed. The architect checks the query and notices that the join condition is missing. Which action should the architect take to resolve the performance issue?

Accepted Answer

Rewrite the query to include an appropriate join condition between the fact and dimension tables.. A Cartesian product in a join is typically caused by a missing or incorrect join condition. The most effective fix is to rewrite the query to include the proper join predicate, which enables Snowflake to execute an efficient join algorithm. Other measures like adding filters or increasing warehouse size do not address the core problem.

Answer

Enable the Query Acceleration Service to automatically optimize the join.

Answer

Add a WHERE clause to filter the result set after the join.

Answer

Increase the size of the virtual warehouse to handle the larger result set.

Question 9

An e-commerce company wants to analyze JSON data stored in an external stage on Amazon S3. The JSON files contain nested arrays and objects, and the schema varies between files. The architect needs to query this data with Snowflake while minimizing data duplication and storage costs. Which approach should the architect recommend?

Accepted Answer

Create an external table with a VARIANT column and use the FLATTEN function to query nested elements.. External tables with a VARIANT column allow direct querying of JSON in the external stage without loading, avoiding duplication and storage costs. FLATTEN handles nested structures. This is the most efficient approach for variable-schema JSON when the goal is to minimize data movement and storage, unlike loading into a table or using materialized views.

Answer

Create a materialized view over the external stage that shreds the JSON into relational columns.

Answer

Use a directory table and a stored procedure to parse and insert JSON into a relational table.

Answer

Load the JSON files into a Snowflake table with a VARIANT column using COPY INTO, then query with FLATTEN.

Question 10

An architect is optimizing a Snowflake environment where a critical query joins a large fact table with a small dimension table. The Query Profile shows that the build side of the hash join is spilling to local disk. Which action is most likely to eliminate the spilling and improve performance?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Spilling to local disk in a hash join indicates that the build side does not fit in the available memory. Increasing the warehouse size provides more memory per node, allowing the build side to be held in memory. This eliminates spilling and improves join performance. Other options do not address the memory constraint.

Answer

Rewrite the query to use a nested loop join instead of a hash join.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Add a clustering key on the join column of the large fact table.