ARA-C01 Data Engineering Practice Test 2 — 15 Questions

Question 1

A data engineer wants to create a stream on a view that joins three tables to track changes across the entire logical entity. What requirement must be met for Snowflake to support a stream on this multi-table view?

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

Accepted Answer

Change tracking must be explicitly enabled on all base tables used by the view.. Streams on views are supported but have specific requirements. The most critical is that change tracking must be enabled on all underlying base tables. This allows Snowflake to reconstruct the delta of the view by analyzing the changes in each contributing table, ensuring transactional consistency for the downstream consumer.

Answer

The view must be a Materialized View for a stream to be created on it.

Answer

The view must only contain INNER JOINs and no aggregations or subqueries.

Answer

All base tables must be in the same database and schema as the view.

Question 2

An architect is considering the Search Optimization Service (SOS) for a large table. In which THREE scenarios would SOS provide the most benefit? (Select THREE)

Accepted Answer

Queries on VARIANT columns using equality predicates on specific keys.. Search Optimization Service is a background service that speeds up point lookups by creating a persistent search access structure. It is highly effective for specific query patterns on large tables, but architects must be selective due to the costs associated with building and maintaining the search service.

Answer

Queries that use 'LIKE' with a wildcard at the beginning of the string (e.g., '%value').

Answer

Small tables (under 100MB) where clustering is not performing well.

Question 3

During a performance review, an architect notices a query on a large table is 'spilling to remote disk'. What does this indicate about the current warehouse configuration and the query's execution?

Accepted Answer

The local SSD of the warehouse is full, and data is being moved to S3/Azure Blob.. Spilling occurs when a warehouse's local memory and SSD are insufficient to hold the intermediate data required for a query (e.g., during large joins or sorts). Spilling to 'remote disk' (cloud storage) is the slowest form of spilling and indicates a severe memory bottleneck that usually requires a larger warehouse.

Answer

The query is scanning too many micro-partitions and needs a clustering key.

Answer

The warehouse has reached its maximum concurrency and is queuing new queries.

Answer

The query is using an External Function that has exceeded its timeout limit.

Question 4

A data engineer needs to optimize a large-scale transformation task using Dynamic Tables. Which TWO factors should be considered when configuring the target refresh rate to balance data freshness and warehouse costs?

Accepted Answer

The total volume of data processed in the source tables during the refresh interval.. Managing Dynamic Tables requires balancing the target lag against the compute cost. Since Dynamic Tables rely on incremental refreshes, frequent refreshes increase compute consumption. Engineers must analyze the dependency graph and the complexity of the SQL transformation to ensure the warehouse can complete the incremental materialization within the defined lag window, preventing performance degradation and excessive costs during high-load periods.

Answer

The number of users currently querying the dynamic table.

Answer

The geographical region of the S3 storage bucket.

Answer

The number of columns defined as primary keys in the source table.

Question 5

Which THREE actions are best practices when managing large-scale data loading using COPY INTO commands?

Accepted Answer

Split large source files into approximately 100MB to 250MB chunks.. Efficient data loading is key to maintaining a performant Snowflake environment. By breaking large files into smaller chunks, Snowflake can leverage its parallel processing engine more effectively across multiple nodes. Using file formats with metadata allows Snowflake to skip unnecessary processing, while maintaining clear directory structures helps in troubleshooting and incremental loading strategies, ensuring that the ingestion pipeline remains resilient and highly scalable under heavy load.

Answer

Always include the entire raw dataset in a single massive file.

Answer

Disable data validation to maximize ingestion speed.

Question 6

A developer wants to ensure that a specific table is not dropped accidentally. Which object feature should be implemented to prevent this?

Accepted Answer

Grant the OWNERSHIP privilege to a highly restricted role.. Accidental deletion of critical data assets can be catastrophic. Snowflake provides the 'DROP' protection via the 'TAG' feature or simply by relying on time travel and fail-safe, but the specific administrative control to prevent dropping a table is typically managed via Access Control. However, for a declarative object-level lock, implementing strict Role-Based Access Control (RBAC) preventing the DROP privilege is the standard engineering practice to protect production tables.

Answer

Enable Time Travel on the table.

Answer

Use the 'PROTECT' command on the table.

Answer

Enable Fail-safe for the database.

Question 7

When dealing with semi-structured data (JSON/Avro) stored in a VARIANT column, how can a data engineer improve query performance for frequently accessed fields?

Accepted Answer

Create a materialized view that extracts the specific fields from the VARIANT column.. Querying fields inside a VARIANT column can be slow if the engine must parse the JSON for every row. Search Optimization Service (SOS) or explicitly creating materialized views or physical columns (using computed columns) significantly improves performance. Specifically, flattening the most frequently used paths into dedicated relational columns allows for standard pruning and indexing, which reduces compute consumption and increases speed compared to runtime parsing of the variant blob.

Answer

Convert the VARIANT column into a VARCHAR column.

Answer

Enable the 'JSON_PARSE' flag on the table.

Answer

Increase the warehouse size to the next level.

Question 8

Which Snowflake feature allows for the automatic detection and handling of schema changes in semi-structured data files during ingestion?

Accepted Answer

INFER_SCHEMA. Schema detection, specifically through 'INFER_SCHEMA', is a powerful feature for modern data engineers. It allows Snowflake to automatically define the structure of JSON, Parquet, or Avro files upon ingestion. This reduces the manual effort required to manage schema definitions as upstream data sources evolve, ensuring that pipelines remain robust and do not break when new fields are added or existing fields change types.

Answer

Data Dictionary

Answer

Schema Drift Management

Answer

Dynamic Schema Evolution

Question 9

A data engineer notices that a specific task is consistently failing due to 'Warehouse not found' errors, even though the warehouse exists. What is the most likely cause?

Accepted Answer

The task owner lacks USAGE privilege on the warehouse.. This scenario points to a common issue with cross-database/cross-schema references and role permissions. If the TASK owner does not have sufficient privileges to 'USE' the warehouse or access the database context, the task will fail even if the object is present. Proper architectural design requires ensuring the task owner role has the necessary USAGE privileges on all required objects to prevent runtime failures.

Answer

The warehouse is in a suspended state.

Answer

The warehouse is too small to execute the task.

Answer

Tasks can only run on the system-default warehouse.

Question 10

A company is migrating its data lake to Snowflake and wants to use Iceberg Tables to maintain open standards while leveraging Snowflake's performance. Which TWO considerations are critical when choosing between Snowflake-managed and externally-managed Iceberg Tables? (Choose two)

Accepted Answer

Snowflake-managed Iceberg Tables allow for full DML operations and automatic metadata maintenance.. Choosing the catalog provider is the primary architectural decision for Iceberg Tables. Snowflake-managed tables provide full DML support and automatic metadata management, whereas externally-managed tables allow interoperability with other engines like Spark. Understanding the trade-offs between write capabilities and multi-engine access is essential for a robust and flexible data lake architecture strategy.

Answer

Externally-managed Iceberg Tables require an External Volume but do not support the Search Optimization Service.

Answer

Only Snowflake-managed Iceberg Tables support the use of Snowflake's proprietary encryption keys for data at rest.

Answer

Snowflake-managed Iceberg Tables cannot be shared via Snowflake Marketplace or Private Exchanges.

Question 11

Refer to the exhibit. A data engineer is using the provided script to ingest semi-structured JSON data into a structured table. What is a primary architectural benefit of using this specific approach during the ingestion process?

Accepted Answer

It enables the extraction of metadata such as filename and row numbers for audit and lineage purposes.. The EXHIBIT demonstrates the use of a SELECT statement within a COPY INTO command, which allows for 'transformation on load'. This technique enables the engineer to flatten nested JSON, cast data types, and include file metadata without first landing the data in a raw VARIANT column. This optimizes storage and improves downstream query performance by structuring data immediately.

Answer

It allows the engineer to perform complex aggregations and joins before the data hits the target table.

Answer

It automatically creates the target table schema based on the structure of the JSON file in the stage.

Answer

The ON_ERROR = 'CONTINUE' clause ensures that no data is lost even if the JSON is malformed.

Question 12

A company implements a Stream on a Secure View that joins a large fact table with a slowly changing dimension (SCD) table. The goal is to capture changes for a downstream process. What is a critical limitation the architect must consider when using a Stream on a View?

Accepted Answer

The underlying base tables must have CHANGE_TRACKING enabled for the stream to function correctly.. Streams on views are restricted by the underlying change tracking capabilities of the source tables. If the view includes joins, the stream can only track changes if certain conditions are met, and the underlying tables must have change tracking enabled. This complexity often leads architects to land data into base tables before applying stream logic for reliability.

Answer

Streams on views can only be created if the view is materialized and clustered on the join key.

Answer

The stream will only capture changes from the first table listed in the view's FROM clause.

Answer

Streams on views are only supported if the view does not contain any window functions or subqueries.

Question 13

Which THREE features are specific benefits of using Serverless Tasks over user-managed virtual warehouse tasks in Snowflake? (Choose three)

Accepted Answer

Snowflake automatically scales the compute resources based on the workload's dynamic requirements.. Serverless tasks offload the burden of warehouse management and right-sizing to Snowflake. This model is particularly beneficial for workloads with unpredictable runtimes or high-frequency executions where manual warehouse scaling would be inefficient. Understanding these benefits helps architects choose the most cost-effective and scalable compute model for their data pipelines.

Answer

Serverless tasks support the execution of external functions and complex Python-based stored procedures.

Answer

Serverless tasks allow for the use of Query Acceleration Service to speed up large scans.

Question 14

A retail architect is designing an ingestion pipeline that lands Parquet files in an internal named stage and then loads a large fact table with COPY INTO. Data volumes arrive in bursts of thousands of small files, and the team wants to minimize load time and credit consumption while preserving query performance. Which TWO actions should the architect take? (Choose two.)

Accepted Answer

Consolidate the small files into larger files before loading, using a file size in the 100-250 MB compressed range where practical.. Bursty ingestion of thousands of small files is dominated by per-file overhead and produces fragmented storage. Consolidating files into larger compressed objects before COPY INTO reduces load cost and yields healthier micro-partitions, while clustering the fact table on frequently filtered columns preserves pruning and query performance as the table grows. Together these address both load efficiency and sustained read performance.

Answer

Set the COPY INTO option PATTERN to match only files newer than the last load and rely on the load metadata to skip previously loaded files.

Answer

Load each small file with its own COPY INTO statement executed in a tight loop so that failures can be isolated to a single file.

Answer

Use a larger warehouse for the load and enable multi-cluster scaling to increase parallelism across the incoming files.

Question 15

A healthcare analytics team maintains a Snowflake table PATIENT_ENCOUNTERS with a clustered column ENCOUNTER_DATE. They frequently run queries that filter on this column and also join to a smaller PROVIDERS table. They want to improve performance without changing the table definition or reloading data. Which action should the architect recommend?

Accepted Answer

Enable automatic clustering on the table to maintain clustering as new data arrives.. Automatic clustering is the correct action because it maintains the clustering of the table as data changes, without requiring table recreation or data reload. The table already has a clustering key on ENCOUNTER_DATE, so enabling automatic clustering ensures ongoing performance for filters on that column. Other options either do not address clustering maintenance or require schema changes.

Answer

Add a search optimization service to the ENCOUNTER_DATE column.

Answer

Recreate the table with a new clustering key on (ENCOUNTER_DATE, PROVIDER_ID).

Answer

Create a materialized view on the join between PATIENT_ENCOUNTERS and PROVIDERS.