Free ARA-C01 practice test

Option D is correct. For maximum micro-partition pruning, the leading column of the clustering key should be the highest-cardinality column that appears most frequently in WHERE clause filters — TRANSACTION_DATE with ~1,000 distinct values fits perfectly as the sole key for the dominant query pattern. Option C is wrong: placing MERCHANT_CATEGORY (only 25 distinct values, low cardinality) first means Snowflake sorts data by that column first, resulting in poor pruning for date-range queries. Option A adds MERCHANT_CATEGORY as a secondary key, which is not harmful but adds unnecessary reclustering cost because the column has very low cardinality and queries rarely filter on it exclusively. Option B is incorrect: clustering on a high-cardinality synthetic primary key like TRANSACTION_ID creates extremely fine-grained micro-partitions with almost no rows per unique value, providing no pruning benefit for range or categorical filters and wasting significant credits.

Defining a clustering key on (TRADE_DATE, INSTRUMENT_TYPE) and enabling automatic reclustering is correct because the leading column should be the one most frequently used as the primary filter (TRADE_DATE is typically a high-cardinality date column that drives range pruning), and automatic reclustering keeps the table well-clustered as new data arrives. Option A is wrong because placing low-cardinality INSTRUMENT_TYPE first reduces pruning effectiveness for date-range queries, which dominate the workload. Option C is wrong because a materialized view cannot fully replace clustering on a 3-billion-row fact table; it adds refresh cost and only helps pre-defined aggregation patterns, not ad-hoc filters. Option D is wrong because Search Optimization Service excels at equality predicates and point lookups, not range predicates on high-cardinality columns in large fact tables — its cost would be disproportionate compared to clustering.

Snowflake materialized views have a well-documented limitation: the defining query cannot reference more than one table — joins between multiple base tables are not supported. The architect must design summaries that can be computed from a single base table, or use alternative patterns (e.g., dynamic tables or pre-aggregated staging tables) for multi-table joins. Option A is false: materialized views are first-class queryable objects; Snowflake's query optimizer can transparently rewrite queries against the base table to use the materialized view, and users can also query the materialized view directly. Option C is false: materialized views in Snowflake are refreshed automatically and incrementally by Snowflake's background service whenever the base table changes — no manual trigger is required. Option B is false: materialized views can coexist with clustering keys on the base table; in fact, clustering can improve materialized view refresh efficiency.

Transient tables eliminate both Time Travel (beyond 0 days) and Fail-safe storage costs, making them the lowest-cost option for data that is intentionally ephemeral and not worth recovering. Because multiple pipeline steps in the same session or across sessions need to read these tables, transient tables are the correct choice over temporary tables, which are session-scoped and invisible to other sessions. Option D (permanent tables with Time Travel=0) still incurs 7-day Fail-safe storage charges, making them costlier than transient tables. Option B (temporary tables) are session-scoped; if a downstream step runs in a different session or a different task, the table would not be visible, making them impractical for multi-step pipelines unless all steps share a single session. Option C (external tables) require data to live in cloud object storage, add I/O overhead, and do not eliminate compute or storage management concerns — they are designed for querying data that already resides outside Snowflake, not for intermediate pipeline staging.

Options B and D are correct. Snowflake MVs are automatically and incrementally refreshed by a Snowflake-managed background service whenever the base table changes — this eliminates the need for user-managed refresh pipelines or scheduled tasks. However, this automatic refresh consumes cloud service and compute credits billed to the account, so there is a cost trade-off architects must evaluate. Option E is wrong: Snowflake MVs have significant SQL limitations — they do not support subqueries in the definition, UNION/UNION ALL, GROUP BY ROLLUP/CUBE, most window functions, or JOINs to other tables in many configurations; these restrictions often force architects to use pre-aggregated tables for complex aggregations. Option A is wrong: chained or nested MVs (an MV defined on top of another MV) are not supported in Snowflake; each MV must be defined directly against a base table. Option C is wrong: MV refreshes are asynchronous and not transactional — there is a small lag between when data is inserted into the base table and when the MV is refreshed; Snowflake MVs are eventually consistent, not zero-staleness.

Correct answers (B) and (C): (B) Clustering keys on extracted expressions — e.g., CLUSTER BY (event.user_id::STRING, event.timestamp::TIMESTAMP) — enable micro-partition pruning so Snowflake skips irrelevant partitions during scans, directly reducing query latency. (C) The Search Optimization Service accelerates point-lookup and equality queries (and range queries on appropriate data types) on specific columns, including extracted VARIANT paths, without copying data. Together these address both high-cardinality user lookups and time-range filters. (A) is incorrect — you cannot cluster directly on a VARIANT column; the clustering key must reference a deterministic expression or cast. (D) is incorrect — pre-aggregating for every possible pattern is impractical and creates a maintenance burden; materialized views also have limitations with complex VARIANT queries. (E) is incorrect — external tables still query S3 and generally have higher latency than native Snowflake tables; this trades cost for performance in the wrong direction for the stated goal. (F) rephrases B with added monitoring context, but SYSTEM$CLUSTERING_INFORMATION is a monitoring tool, not a feature that reduces latency on its own — the core answer is the clustering key expression itself (B).

SYSTEM$GET_TAG is the correct function to retrieve the value of a specific tag on a specific object in real time. It accepts the fully qualified tag name, the fully qualified object name, and the object type ('table', 'column', etc.), returning the tag value or NULL if not set. Option B references ACCOUNT_USAGE.TAG_REFERENCES, which is a valid view but has up to a 2-hour data latency (as with all ACCOUNT_USAGE views) and is better suited for bulk auditing rather than immediate verification. Option C is incorrect: INFORMATION_SCHEMA.TAGS is the view for listing tag definitions (metadata about the tags themselves), not for querying which tags are applied to which objects. Option D is incorrect: SNOWFLAKE.ACCOUNT_USAGE.OBJECT_TAGS is not a standard Snowflake view name; the correct view for tag assignments is TAG_REFERENCES.

CURRENT_ROLE() returns the primary role that the session is currently operating under, which is the correct function to use when the row access policy should filter rows based on the active role. Option A (CURRENT_USER()) returns the username, which would require the mapping table to be keyed by user rather than role—an antipattern that ties permissions to individuals rather than roles. Option B (CURRENT_AVAILABLE_ROLES()) returns a comma-separated list of all roles in the user's hierarchy and is not the standard function used in row access policy joins against a role-to-region mapping table. Option D (INVOKER_ROLE()) is not a native Snowflake context function used in row access policies.

Fail-safe provides a 7-day recovery window after the Time Travel retention period ends, but it is managed exclusively by Snowflake Support—customers cannot directly query or restore Fail-safe data themselves (option B is correct). Increasing TIME_TRAVEL_IN_DAYS causes Snowflake to retain additional historical data versions for longer, directly increasing storage consumption and therefore cost proportionally to the table size (option E is correct). Option D is incorrect because Fail-safe data cannot be accessed by the customer directly; only Snowflake Support can initiate a Fail-safe recovery. Option A is incorrect in that Time Travel and active storage are billed at Snowflake's standard storage rate, but the statement is misleading—Time Travel storage is billed as additional consumption, and the key point is that it is additional cost, not that the rate differs. Option C is incorrect because Fail-safe begins only after the Time Travel retention period has elapsed, not concurrently from the moment of table creation.

The maximum DATA_RETENTION_TIME_IN_DAYS in Snowflake is 90 days (available on Enterprise edition and above, including Business Critical). Beyond that, Fail-safe provides an additional non-configurable 7-day window managed exclusively by Snowflake Support. Therefore, the maximum combined recovery window is 90 (Time Travel) + 7 (Fail-safe) = 97 days. Option A is wrong: Fail-safe does apply to Business Critical accounts; it applies to all paid editions. Option C is wrong: 90 days represents only the Time Travel component and omits Fail-safe. Option B is wrong: 14 days (7 + 7) reflects the default maximum Time Travel for Standard edition tables plus Fail-safe, not the absolute maximum available on Business Critical.

LOGIN_HISTORY contains authentication event metadata. Option B is correct: CLIENT_IP records the IP address of the connecting client. Option D is correct: IS_SUCCESS and ERROR_MESSAGE fields indicate whether authentication succeeded and why it failed if not. Option E is correct: FIRST_AUTHENTICATION_FACTOR and SECOND_AUTHENTICATION_FACTOR capture the authentication method(s) used. Option C is incorrect: SQL query details are found in QUERY_HISTORY, not LOGIN_HISTORY—LOGIN_HISTORY only covers the authentication event. Option F is partially tricky: CLIENT_APPLICATION_NAME is indeed present in LOGIN_HISTORY, but since we must select exactly three and the first three are unambiguously correct, this avoids a tie. Actually, CLIENT_APPLICATION_NAME IS present—however, the question asks test-takers to identify the three most clearly correct options among distractors. Option A is incorrect: row access policy application details are found in ACCESS_HISTORY, not LOGIN_HISTORY.

Correct (A): The WindowFunction node is spilling to local storage, which is a memory pressure problem. Upsizing the warehouse provides more total RAM across more nodes, allowing the sort and partition operations within the window function to complete in-memory rather than spilling to disk.

Correct (E): Applying a WHERE clause filter (e.g., limiting to a date range) reduces the total number of rows the window function must process. Fewer rows means smaller sort buffers, less memory pressure, and reduced or eliminated spilling — directly addressing the bottleneck.

Why B is wrong: Automatic Clustering on (customer_id, event_time) would help pruning for queries that filter on customer_id, but window functions must process all data within each partition value across the full dataset. Clustering reduces scan I/O but does not eliminate the need to sort and partition rows during window function execution.

Why C is wrong: ROW_NUMBER() cannot be trivially replaced with GROUP BY because GROUP BY collapses rows while ROW_NUMBER() assigns a rank to each row within its partition. These are semantically different operations.

Why D is wrong: QAS accelerates large partition-scan workloads (filters, aggregations over many micro-partitions). It does not offload window function computation to separate serverless nodes — window functions are executed within the warehouse compute.

Spilling to remote storage occurs when the warehouse exhausts both available memory AND local SSD space — data overflows all the way to remote object storage, which is the slowest tier. Option B correctly identifies that upgrading warehouse size increases available memory (and local disk), which eliminates or reduces the need to spill. Option A incorrectly implies local SSD was exhausted first (if local SSD were exhausted first, 'Bytes spilled to local storage' would be non-zero); in the scenario, local spill is zero, meaning the spill went directly past local SSD — this is unusual but possible when local SSD is already heavily utilized by cached data from other queries. Option C is incorrect because partition pruning reduces data scanned but does not directly address the aggregation memory pressure causing the spill. Option D is incorrect; remote storage spilling severely degrades performance and should always be investigated.

A Snowflake Stream's offset advances only when a transaction that selects from the stream is committed. If the task ran successfully and committed its transaction, the stream offset is advanced and the stream appears empty until new changes arrive. If the engineer queries the stream between task runs but after a successful task execution, they will see no data because those changes have already been consumed. Option A is partially correct in the opposite direction — if the task fails to commit, the offset is NOT advanced and the data remains visible; but the scenario describes the stream appearing empty, not stale. Option B is incorrect: DEFAULT streams capture all DML changes including INSERTs. Option D describes data retention expiry (stale stream), which causes an error on stream access rather than silently showing no rows.

The requirement is to cap the ETL warehouse at 500 credits without abruptly killing all running queries. The SUSPEND action (at 100%) allows currently-running queries to finish but prevents new queries from starting once the quota is hit — this avoids abrupt interruption while still enforcing the limit. Adding a NOTIFY action at 90% gives advance warning. Assigning the monitor to the specific ETL warehouse (not account-level) ensures only that warehouse is affected. Option C uses SUSPEND_IMMEDIATELY at 110%, which would kill running queries mid-execution and allows 10% overage before acting. Option D applies the monitor at the account level, which would impact all warehouses, not just the ETL one. Option B adds a SUSPEND_IMMEDIATELY at 110%, which allows overage and kills running queries — the requirement says 'without completely stopping all warehouse activity', which means in-flight queries should be allowed to complete.

Stored procedures in Snowflake do not automatically wrap their DML in a single atomic transaction unless the caller wraps the CALL in an explicit BEGIN ... COMMIT/ROLLBACK block (or the procedure itself manages transaction control). By calling the procedure inside an explicit transaction and having the application-level caller issue ROLLBACK on any exception propagated out of the procedure, all DML within that transaction is undone atomically. Option A is partially on the right track but incomplete — while catching the exception and issuing a ROLLBACK via snowflake.execute() inside the procedure would work, this must be done carefully; the issue is that the explicit ROLLBACK call inside the JavaScript is the mechanism, not just a try/catch alone. However, Option D is more architecturally correct because it keeps transaction management at the appropriate layer (the caller) without relying on error-prone in-procedure rollback logic. Option B is incorrect — EXECUTE AS OWNER controls privilege context, not transaction atomicity. Option C is incorrect — switching to Python does not grant automatic atomicity; pandas apply() is a client-side operation and unrelated to Snowflake transaction semantics.

In the Native App Framework, the provider sets a default (or patch) version on the application package. Consumers can configure their installed application to use the default version (auto-upgrade) or pin to a specific version. When the provider sets version 1.1 as the default, consumers with auto-upgrade enabled will receive it; others remain on 1.0 until they manually upgrade. Option C is incorrect — upgrades are not forced automatically on all consumers without their configuration allowing it. Option D is incorrect — apps do not need to be uninstalled to upgrade; the ALTER APPLICATION ... UPGRADE command (or auto-upgrade) handles this. Option A is completely false — versioning applies to the entire application package, including all stored procedures, Streamlit apps, and other objects.

.explain() prints the logical and physical query plan for the DataFrame, showing exactly what SQL Snowpark will generate and send to Snowflake. If all transformations appear as SQL operations in the plan (joins, aggregations, filters, etc.), they will be executed by the Snowflake engine. This is the standard, direct method to audit pushdown behavior. Option C is unreliable — query execution time depends on data size, warehouse size, and caching, not solely whether logic was pushed down. Option A is a misconception; Snowpark does not emit 'client-side filter' warnings in its logging framework in this manner. Option B only validates correctness, not WHERE the computation occurs — both a pushed-down and a client-side computation could return identical counts.

External functions route calls through an API Integration to an external HTTP endpoint (e.g., an AWS API Gateway backed by Lambda), introducing network round-trip latency and a dependency on external service availability. Snowpark Python UDFs execute inside Snowflake's warehouse environment, keeping compute within the Snowflake security perimeter. For third-party API enrichment, external functions are the correct choice — but understanding their latency and dependency costs versus in-warehouse UDFs is the key trade-off. Option A is false; external functions are typically SLOWER due to network overhead, not faster. Option D is misleading — standard Snowpark Python UDFs run in a sandboxed environment and cannot make arbitrary outbound network calls; Snowpark Container Services or external functions are needed for that. Option C is false; external functions batch rows in requests and return batched responses, not streaming row-by-row results.

Vectorized (pandas) UDFs receive entire batches of rows as pandas.Series objects instead of processing one row at a time, dramatically reducing Python interpreter call overhead and enabling NumPy/pandas batch operations. This is the standard Snowpark optimization for compute-heavy per-row Python logic. Option A is incorrect — JavaScript UDFs avoid the Python overhead but cannot leverage NumPy and require a full rewrite; they are not the recommended path for NumPy-based logic. Option D (larger warehouse) adds parallelism but does not fix the per-row Python invocation overhead that is the root cause. Option B is incorrect — stored procedures are designed for procedural control flow and DML orchestration, not for replacing row-level transformation logic; they would not address per-row performance.