ARA-C01 Data Engineering Practice Test 4 — 15 Questions

Question 1

A logistics company ingests real-time GPS pings from delivery vehicles into a Snowflake table using Snowpipe Streaming. The table is append-only and receives about 500,000 rows per minute. The architect needs to optimize query performance for analysts who filter on vehicle_id and event_time, and also reduce storage costs. Which two actions should the architect take? (Choose two.)

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

Accepted Answer

Apply a data retention policy of 1 day to automatically purge old data and reduce storage.. Defining a clustering key on the filter columns improves query pruning and performance for the high-volume append-only table. Setting a short data retention period reduces storage costs by purging old data and fail-safe sooner. Together, these actions address both the performance and cost requirements without introducing unnecessary complexity or risk.

Answer

Convert the table to a transient table to avoid fail-safe storage costs.

Answer

Enable automatic clustering and set a target depth to control reclustering cost.

Answer

Use a materialized view to pre-aggregate GPS pings by vehicle and hour.

Question 2

A financial services firm needs to process sensitive data in Snowflake. They must ensure that data is encrypted at rest and in transit, and that encryption keys are managed by the customer. Which Snowflake feature should the architect implement?

Accepted Answer

Customer-managed keys using Snowflake's Tri-Secret Secure with AWS KMS or Azure Key Vault.. Tri-Secret Secure with customer-managed keys from a cloud provider's key management service (AWS KMS or Azure Key Vault) allows the customer to manage encryption keys while Snowflake manages a composite key. This satisfies the requirement for customer-managed keys and encryption at rest and in transit. Other options either do not provide customer-managed keys or are not designed for this purpose.

Answer

Customer-managed keys using Snowflake's External Tokenization.

Answer

Tri-Secret Secure with customer-managed keys in Snowflake.

Answer

Snowflake-managed encryption with periodic key rotation.

Question 3

A data architect is designing a pipeline that uses Snowpipe to ingest data from an external stage. The architect wants to ensure that Snowpipe is notified immediately when new files arrive. The cloud provider is AWS. Which TWO actions should the architect take to configure event notifications? (Choose two.)

Accepted Answer

Create an S3 event notification on the bucket that sends events to an Amazon SQS queue.. To enable immediate Snowpipe ingestion from S3, you must set up S3 event notifications to an SQS queue and grant Snowflake IAM permissions to read from that queue and the S3 bucket. These two actions ensure that Snowpipe receives events and can access the data, enabling near real-time ingestion.

Answer

Create an S3 event notification on the bucket that sends events directly to Snowflake's REST endpoint.

Answer

Enable the AUTO_INGEST parameter when creating the pipe and specify the SQS queue ARN.

Answer

Configure Snowpipe to use the SQS queue by associating the queue ARN with the pipe using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = FALSE.

Question 4

A retail company uses Snowflake to analyze point-of-sale data. They need to implement a change data capture (CDC) process on a source table ORDERS that receives frequent updates and deletes. The goal is to capture all changes and apply them to a target table in near real-time. Which two Snowflake features should be used together to achieve this? (Choose two.)

Accepted Answer

Tasks to schedule and execute the CDC process at regular intervals.. Streams capture all DML changes on the source table, including deletes, and tasks schedule the processing of those changes into the target. Together, they provide a native CDC solution within Snowflake. Other options either do not track changes (Snowpipe, external functions) or are not designed for capturing deletes (Dynamic Tables).

Answer

Snowpipe to continuously load data from an external stage.

Answer

External functions to call an external API for change processing.

Answer

Dynamic Tables to automatically refresh based on source changes.

Question 5

A media streaming company uses Snowflake to store user interaction events. The events table is 50 TB and partitioned by event_date. Analysts frequently run queries that join the events table with a user_dimension table on user_id and filter on event_date. The architect notices that queries are slow and wants to improve join performance without changing the query logic. Which action should the architect take?

Accepted Answer

Cluster the events table by (event_date, user_id) and the user_dimension table by (user_id).. Clustering both tables on the join and filter columns improves partition pruning and co-locates matching rows, reducing I/O and shuffle during joins. This directly addresses the performance issue without altering queries. Materialized views, search optimization, or larger warehouses do not provide the same structural optimization for this large-scale analytical join.

Answer

Create a materialized view that pre-joins the events and user_dimension tables.

Answer

Add a search optimization service on the user_id column of the events table.

Answer

Use a larger warehouse size to increase compute power for the joins.

Question 6

A data architect is implementing a Change Data Capture (CDC) pipeline using Snowflake Streams on a table that receives frequent updates and deletes. The architect needs to ensure that the stream captures all changes and that downstream tasks can process them efficiently. Which TWO actions should be taken? (Choose two.)

Accepted Answer

Create a task that consumes the stream and merges changes into a target table.. To capture all changes (inserts, updates, deletes) for CDC, a standard stream must be used because it records all DML types. Additionally, a task must consume the stream to process and apply those changes to a target, otherwise the stream will grow and become stale. These two actions ensure a complete and efficient CDC pipeline.

Answer

Use a stream on a view that joins multiple tables to capture changes across all tables.

Answer

Set the stream to include metadata columns like METADATA$ACTION and METADATA$ISUPDATE.

Answer

Set the stream to APPEND_ONLY = TRUE to capture only inserts.

Question 7

A data engineer is designing an automated ingestion pipeline using Snowpipe Streaming to ingest high-frequency clickstream data from Kafka into Snowflake tables. The architecture requires low latency and cost-effective continuous loading. Which underlying Snowflake architectural feature makes Snowpipe Streaming uniquely capable of bypassing the traditional internal staging phase?

Accepted Answer

It writes data directly to internal table micro-partitions via client-side API calls, avoiding file staging.. Snowpipe Streaming writes directly to Snowflake micro-partitions using native Java APIs without requiring files to be staged first in internal stages. This architectural shortcut significantly reduces latency and compute costs for real-time streaming pipelines compared to standard file-based Snowpipe loading.

Answer

It leverages serverless tasks to automatically stage and bulk load streaming batches every minute.

Answer

It requires continuous execution of a dedicated virtual warehouse to transform and commit micro-batches.

Answer

It automatically converts incoming streaming payloads into Parquet files before final table insertion.

Question 8

Refer to the exhibit. Task T2 is a child task of T1. If T1 completes successfully but the stream 'S1' is empty, what will be the status and behavior of Task T2?

Accepted Answer

T2 will enter a 'SKIPPED' state and will not consume any warehouse credits.. In a Snowflake Task DAG, the WHEN clause is evaluated after the predecessor task finishes. If the condition in the WHEN clause is false, the task is skipped. This skip behavior is considered a successful state in the context of the DAG's flow, and it prevents the consumption of warehouse credits for an empty operation.

Answer

T2 will fail with an error indicating that the stream contains no records for processing.

Answer

T2 will run and perform a full table scan of the stream, resulting in zero rows inserted.

Answer

T2 will wait until S1 has data before executing, potentially delaying the rest of the DAG.

Question 9

A data engineer is loading a large CSV file into Snowflake using the COPY INTO command. They want the load to continue even if some rows have errors, and they need to review the errors later. Which parameter should be added to the COPY command?

Accepted Answer

ON_ERROR = 'CONTINUE'. The ON_ERROR parameter controls how the COPY command handles data validation errors. By setting it to CONTINUE, Snowflake skips any rows that fail to load due to formatting issues and proceeds with the rest of the file. The errors can then be queried using the VALIDATE function or the LOAD_HISTORY view.

Answer

ON_ERROR = 'ABORT_STATEMENT'

Answer

ON_ERROR = 'SKIP_FILE'

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Question 10

Refer to the exhibit. An architect reviews the status of a Snowpipe and notices a high 'pendingFileCount'. The warehouse is not under heavy load. What is the most effective way to improve the ingestion throughput for this pipe?

Accepted Answer

Reduce the number of files by aggregating data into larger files (100-250 MB) before they reach the S3 bucket.. A high pending file count in Snowpipe often indicates that the ingestion rate is slower than the file arrival rate. Since Snowpipe is serverless, users cannot scale the compute manually. The best approach is to optimize the file size and count, or ensure that the files are correctly formatted to be processed as quickly as possible.

Answer

Scale up the virtual warehouse assigned to the pipe to a larger size.

Answer

Increase the MAX_CONCURRENCY_LEVEL parameter in the Pipe definition to allow more parallel loads.

Answer

Use the ALTER PIPE ... REFRESH command to force the pipe to process the pending files faster.

Question 11

An architect is designing a staging area for a daily ETL process where data is loaded, transformed, and then moved to a permanent production table. The staging data is only needed for 24 hours and does not require long-term Fail-safe protection. Which table type is most cost-effective?

Accepted Answer

Transient Tables. Transient tables are ideal for staging environments because they persist across sessions but do not incur the costs associated with Fail-safe storage. This makes them significantly cheaper for high-churn data that can be easily recreated if a system failure occurs, while still allowing for multi-day processing if needed.

Answer

Permanent Tables

Answer

Temporary Tables

Answer

External Tables

Question 12

A data engineer is concerned about the performance of a large-scale batch transformation that runs every night. Which TWO techniques can be used to improve the performance of a complex join between two very large tables (billions of rows)?

Accepted Answer

Define a Clustering Key on the join columns for both large tables.. Optimizing large joins in Snowflake involves ensuring that the data is physically organized to minimize data movement and that the compute resources are sufficient. Clustering allows for partition pruning, while larger warehouses provide the memory necessary to perform hash joins without spilling data to local or remote storage.

Answer

Use the SEARCH_OPTIMIZATION_SERVICE on the join columns of the smaller table.

Answer

Convert the tables to Iceberg format to utilize external metadata indexing.

Answer

Enable Query Acceleration Service (QAS) for the warehouse running the ETL.

Question 13

A company is moving towards an Open Data Lakehouse architecture using Snowflake Iceberg Tables. They want to ensure that the data is stored in Parquet format in their own S3 bucket but still benefit from Snowflake's performance. Which configuration should the architect recommend for the Iceberg Table's catalog?

Accepted Answer

Set the CATALOG to 'SNOWFLAKE' and specify an EXTERNAL_VOLUME.. Snowflake supports two catalog options for Iceberg tables: Snowflake and External. Using Snowflake as the catalog allows Snowflake to manage the metadata and perform full DML operations, which provides the best performance and integration while still keeping the data in an open format in the customer's cloud storage.

Answer

Set the CATALOG to 'AWS_GLUE' to ensure that other AWS services can manage the metadata.

Answer

Use the 'EXTERNAL' catalog type and point to a Snowflake Managed Iceberg Catalog.

Answer

Create a standard Snowflake table and use a periodic Task to export the data to S3 in Iceberg format.

Question 14

An enterprise data engineering team is designing a real-time ingestion pipeline into Snowflake using Snowpipe streaming. The target is a transactional table that requires low latency and high frequency inserts from Java applications. Which architectural consideration is critical for optimizing performance and maintaining transactional integrity when using Snowpipe streaming?

Accepted Answer

Channels write directly to micro-partitions without intermediate staging, requiring careful sizing of the client's memory buffers.. Snowpipe streaming utilizes client-side buffering to write rows directly to micro-partitions without staging files first, achieving sub-second latency. Understanding this architecture is critical for data architects designing high-throughput real-time pipelines to balance memory allocation on ingestion clients with Snowflake's micro-partition generation limits.

Answer

Files must be staged in an external cloud storage bucket prior to ingestion via the streaming Java SDK.

Answer

The client application must explicitly execute a SQL ALTER TABLE command to flush data buffers every five minutes.

Answer

Data loaded via Snowpipe streaming must undergo a mandatory virus scan within an internal stage before table insertion.

Question 15

An architect is designing a pipeline to transform data from a raw landing zone to a gold-tier reporting layer. The pipeline requires complex multi-table joins and aggregations that must stay updated within a five-minute latency window. Which Snowflake feature provides the most simplified declarative approach for this requirement?

Accepted Answer

Dynamic Tables with a TARGET_LAG of '5 minutes'. Dynamic Tables simplify the declarative pipeline process by automatically managing refreshes based on a specified target lag. Unlike Streams and Tasks, which require imperative logic and manual scheduling, Dynamic Tables optimize for the desired state of data, making them ideal for complex transformations where managing manual dependencies becomes a significant operational burden for architects.

Answer

Streams and Tasks with manual MERGE statements

Answer

Materialized Views on top of the raw tables

Answer

External Tables with auto-refresh enabled