ARA-C01 Sample Questions & Answers

The statement is false. Snowflake's Secure Data Sharing is built on its unique architecture that separates storage and compute. Data is never physically copied to the consumer's account. Instead, the consumer gets secure, live, read-only access to the provider's data. Because the data remains in the provider's account, the provider is responsible for all storage costs. The consumer is only responsible for the compute costs incurred from querying the shared data using their own virtual warehouses.

The FLATTEN function is a table function specifically designed to explode semi-structured data, like JSON arrays, into a relational representation. It produces a lateral view of a VARIANT, OBJECT, or ARRAY column, effectively converting each element of the array into a separate row in the result set. PARSE_JSON is used to convert a string into a VARIANT, which is done at ingestion. JSON_EXTRACT_PATH_TEXT is used to extract scalar values from a specific path within the JSON, not to un-nest an entire array. CHECK_JSON is for validation.

The ECONOMY scaling policy is designed to conserve credits by starting new clusters only when the system estimates there's enough query load to keep the new cluster busy for at least 6 minutes. This can lead to queuing even if the current clusters are not fully utilized, as the policy waits to ensure the new cluster will be used efficiently. The low CPU utilization suggests the existing clusters are handling their current load, but the queuing indicates that the policy is too conservative for the bursty, high-concurrency workload of the holiday season. Changing the policy to STANDARD would start new clusters more aggressively, reducing queue time.

This is the optimal solution. Using Secure Data Sharing meets requirements 1, 2, and 3: it provides live, real-time access to data across regions and clouds without copying it, so the central BI team only pays for compute. For requirement 4, the regional accounts must share their data with both the primary (us-east-1) and secondary (us-west-2) NA accounts. The databases created from these shares are not replicated via Account Replication. Instead, a Failover Group should be used to replicate the central account's own objects like users, roles, and warehouses. In a failover event, the secondary account is promoted, and it already has access to the live regional data via the pre-configured shares, allowing it to resume operations quickly.

The POLICY_REFERENCES function (or the SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES view for historical lookups) is the correct tool for this task. It is specifically designed to show which security policies (like masking or row access) are set on which objects. Querying this function with the policy name will return the objects it is attached to, or querying with the object name will return the policies attached to it. The TABLES view does not contain policy attachment details. GET_DDL shows the table's DDL but not the policy attachments, which are managed separately via ALTER TABLE ... ADD ROW ACCESS POLICY. The APPLICABLE_ROLES view shows role information, not policy attachments.

Snowpipe is Snowflake's continuous data ingestion service, designed for exactly this use case: loading micro-batches of data as they become available in an external stage. It is serverless, meaning you don't manage a virtual warehouse, which minimizes credit consumption for sporadic, small loads. Configuring auto-ingest with an Azure Event Grid notification provides the lowest latency, as the load is triggered immediately upon file creation rather than on a fixed schedule. A task-based approach would consume warehouse credits for the entire time it runs, even if there are no new files, making it less cost-effective and introducing latency up to the schedule interval.

Micro-partition pruning is only effective when the query's filter predicates align with the columns in the table's clustering key. In this scenario, the table is clustered by ORDER_DATE, but the query filters by CUSTOMER_ID. Since the CUSTOMER_ID values are likely spread randomly across all the date-based micro-partitions, Snowflake cannot prune any partitions and must perform a full table scan. The problem is the mismatch between the clustering strategy and the query pattern. High cardinality or low cardinality of the key itself is not the direct cause; the mismatch is.

The Snowflake best practice for designing a role hierarchy is to grant privileges on objects to access roles, grant those access roles to functional roles, and finally grant the functional roles to users. This creates a clean, manageable, and scalable RBAC model. It decouples users and their business functions from the underlying permissions on physical data objects, making it easier to manage access as the organization changes.

Database replication is the core mechanism for asynchronously copying the data from the primary account (AWS) to the secondary account (Azure). This is what allows the data to be available in the DR site.

A Failover Group is essential for meeting the RTO. It bundles the databases for replication and allows for a single command to fail over all objects in the group, including account-level objects like users and roles, to the secondary account. This automates and speeds up the recovery process.

A separate Snowflake account must be provisioned in the target cloud and region (Azure eastus2) to act as the replication target and the failover site. Replication occurs between two distinct accounts.

Due to Snowpark's lazy execution, DataFrame operations are chained together and compiled into a single complex SQL query when an action is called. The .cache_result() method is an action that evaluates the DataFrame, executes the corresponding SQL, and stores the result in a temporary table. Subsequent operations on this cached DataFrame will then query the simpler temporary table instead of re-executing the entire complex upstream logic. This is the standard technique for breaking the query plan and materializing intermediate results to improve performance and reliability in complex Snowpark workflows. .collect() and .to_pandas() are actions, but they pull data to the client side, which is not what's needed here. persist() is not a valid Snowpark Python method.