Free ARA-C01 sample questions

Correct answer: The MERGE statement is not wrapped in an explicit transaction block with the stream consumption.

A stream's offset advances only when the DML statement that consumes it is part of a successful transaction. If the `MERGE` statement runs as a standalone, auto-committed transaction, the stream consumption is not guaranteed to be part of that same transaction. To ensure the stream is consumed atomically with the `MERGE` operation, both the `SELECT` from the stream and the `MERGE` into the target table must be enclosed within an explicit `BEGIN...COMMIT` transaction block. This guarantees that if the `MERGE` is successful, the stream's offset is advanced, and the records are consumed.

Correct answer: Define a clustering key on the `TIMESTAMP_NTZ` column and enable auto-clustering.

The query profile indicates poor micro-partition pruning, as almost all partitions are being scanned. Since the data is naturally ordered by the timestamp column used in the `WHERE` clause, defining a clustering key on this column will formalize this organization. Snowflake can then use the micro-partition metadata to prune (skip) the vast majority of partitions that do not contain data relevant to the query's time range. This directly addresses the root cause of the excessive table scan. Increasing warehouse size would only brute-force the scan faster at a higher cost. Search Optimization is best for highly selective point lookups on high-cardinality columns, not range scans on a naturally ordered key. Creating a materialized view is a heavier solution and might not be as effective as fixing the base table's physical layout.

Correct answers: Dynamic Data Masking; Role-Based Access Control (RBAC)

Dynamic Data Masking allows defining a policy that conditionally alters the data returned from a query based on the user's role. A masking policy can be created to show the real value for the `AUDITOR` role but a masked value (e.g., 'XXX-XX-XXXX') for the `ANALYST` role.

RBAC is the foundation for this solution. The masking policy's logic will explicitly check the user's current role (`IS_ROLE_IN_SESSION('AUDITOR')`) to decide whether to show masked or unmasked data. The distinct roles (`ANALYST`, `AUDITOR`) are essential for the policy to function as required.

Correct answer: False

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.

Correct answer: FLATTEN

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.

Correct answer: The `ECONOMY` scaling policy is prioritizing cost savings by waiting for a full 6-minute run time before starting a new cluster.

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.

Correct answer: Each regional account creates a Secure Share of their sales data and adds the central NA primary and secondary accounts as consumers. The central NA account creates databases from these shares. For DR, configure a Failover Group to replicate critical objects (users, roles, warehouses) from the primary to the secondary NA account.

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.

Correct answer: The `SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES` view.

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.

Correct answer: Snowpipe with auto-ingest configured via an Azure Event Grid notification.

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.

Correct answer: The clustering key has been defined on a column that is not used in the query's `WHERE` clause.

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.