ARA-C01 Data Engineering Practice Test 5 — 15 Questions

Question 1

A data engineer is observing high costs associated with Snowpipe for a high-volume ingestion pipeline where many small files arrive every second. What is the most effective architectural change to reduce Snowpipe costs while maintaining near real-time ingestion?

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

Accepted Answer

Implement a file grouping strategy in the cloud storage layer. Snowpipe costs are influenced by the number of files processed due to per-file overhead. By aggregating small files into larger batches at the source or using a more efficient staging strategy, architects can reduce the overhead. Monitoring the pipe's utilization and file sizing is a core responsibility for optimizing serverless compute usage in Snowflake.

Answer

Increase the size of the virtual warehouse used by Snowpipe

Answer

Switch from Snowpipe to a scheduled COPY INTO command

Answer

Use the PURGE = TRUE option in the Snowpipe definition

Question 2

An architect needs to implement a Change Data Capture (CDC) process for data stored in an external S3 bucket without moving all data into Snowflake first. Which TWO features must be combined to track new and modified files efficiently? (Select TWO)

Accepted Answer

Directory Tables with AUTO_REFRESH = TRUE. Capturing changes from external tables requires a combination of directory tables and streams. Directory tables provide a catalog of files in the stage, while streams track the metadata changes. This architecture allows Snowflake to react to new files landing in cloud storage without needing to constantly poll the entire bucket.

Answer

Snowpipe with a custom REST API integration

Answer

Materialized Views on the External Table

Answer

External Functions to trigger AWS Lambda

Question 3

Refer to the exhibit. What is the primary purpose of the WHEN clause in this Task definition?

Accepted Answer

To skip the task execution and avoid warehouse costs if no changes are present in the stream.. The WHEN clause in a Task allows for conditional execution based on a boolean expression. In this case, the SYSTEM$STREAM_HAS_DATA function checks if the associated stream contains any change tracking data. This prevents the Task from starting a warehouse and consuming credits when there is no work to perform.

Answer

To ensure the Task only runs if the sales_summary table is empty.

Answer

To check for the existence of the 'sales_stream' object before starting the warehouse.

Answer

To validate that the data in the stream matches the schema of the sales_summary table.

Question 4

A Snowpipe is configured to load data from an S3 bucket. The architect notices that some files are failing to load due to a schema mismatch, but no alerts are being generated. What is the most robust way to implement automated error notification for Snowpipe?

Accepted Answer

Configure the ERROR_INTEGRATION parameter on the PIPE object. Snowpipe error notifications allow architects to push alerts to a cloud messaging service when errors occur. By configuring the ERROR_INTEGRATION parameter in the Pipe definition, Snowflake can send detailed JSON error reports to services like Amazon SNS, which can then trigger emails or Slack alerts for the engineering team.

Answer

Schedule a Task to query the VALIDATE_PIPE_LOAD function every hour

Answer

Use a Stream on the target table to track failed insert attempts

Answer

Set the Snowpipe parameter ON_ERROR = 'NOTIFY'

Question 5

A company is implementing a Data Lakehouse architecture and wants to store their data in the Apache Iceberg format on S3 while still using Snowflake for high-performance analytics. What is the most important architectural consideration when using Snowflake-managed Iceberg tables?

Accepted Answer

Snowflake will handle all data maintenance and metadata updates for the table.. Iceberg tables in Snowflake can be either Snowflake-managed or Externally-managed. When Snowflake-managed, Snowflake handles the metadata and file orchestration, providing performance similar to native tables while storing data in an open format. This allows other tools to read the Parquet files while Snowflake remains the primary engine.

Answer

Snowflake-managed Iceberg tables cannot be queried by external engines like Spark.

Answer

The data must be stored in Snowflake's internal proprietary format to use Iceberg.

Answer

Iceberg tables do not support Time Travel or Fail-safe features.

Question 6

An organization requires that all data ingested into Snowflake must be encrypted at rest with customer-managed keys. Which feature enables this security requirement for data stored in Snowflake?

Accepted Answer

Tri-Secret Secure. Tri-Secret Secure uses a combination of a Snowflake-managed key and a customer-managed key stored in a cloud provider's Key Management Service (KMS). This provides an additional layer of security, ensuring that data is encrypted using a key outside of Snowflake's direct control. This is vital for highly regulated industries like finance or healthcare that must maintain strict data governance and compliance over their encryption keys.

Answer

Data Masking

Answer

Row-Level Security

Answer

Access Control Lists (ACLs)

Question 7

When designing an ELT pipeline using Snowflake, why is it recommended to perform transformations inside Snowflake rather than using an external ETL tool?

Accepted Answer

It keeps data within the Snowflake ecosystem, reducing latency and egress costs.. Snowflake's architecture separates storage and compute, allowing it to act as a highly scalable transformation engine. By performing ELT (Extract, Load, Transform), engineers ingest raw data first, then use SQL to transform it. This approach leverages Snowflake's massively parallel processing (MPP) power, avoids unnecessary data egress costs, and keeps transformation logic centralized within the database, which simplifies auditing and improves overall data lineage and pipeline performance.

Answer

External ETL tools cannot connect to Snowflake via standard interfaces.

Answer

External ETL tools are unable to handle complex SQL logic.

Answer

Snowflake does not support programmatic data manipulation.

Question 8

A data engineer needs to share data with an external organization without moving or copying the data. Which feature is the most efficient and secure way to implement this?

Accepted Answer

Implement Snowflake Data Sharing.. Snowflake Data Sharing allows secure access to live data without replication. This eliminates the overhead of managing ETL jobs for data distribution, ensures the data consumer always sees the most current information, and provides granular control over what is shared. This is the cornerstone of the Snowflake Data Cloud, enabling organizations to build data-driven partnerships while maintaining full governance and security compliance over their shared assets.

Answer

Use a scheduled task to export data to an S3 bucket.

Answer

Create a read-only user for the other organization.

Answer

Use the Snowflake Data Replication feature.

Question 9

Which object acts as the logical container for storing data in Snowflake, and how is it related to the underlying physical storage?

Accepted Answer

A Database, which organizes schemas, tables, and views.. A database is the logical container in Snowflake. Snowflake's architecture separates the logical database objects (tables/views) from the physical storage layer (micro-partitions in cloud storage). This abstraction is central to Snowflake's design, allowing users to interact with relational constructs while the engine handles the complexities of storage management, encryption, and partitioning without requiring manual maintenance from the end-user.

Answer

A Schema, which manages physical disk allocation.

Answer

A Table, which maps directly to a specific physical file on disk.

Answer

A Warehouse, which handles the persistent storage of data.

Question 10

When designing a multi-layered architecture (Raw -> Silver -> Gold), which Snowflake object type is best suited for building the 'Silver' layer for incremental transformations?

Accepted Answer

Dynamic Tables. Dynamic Tables are designed for declarative data pipelines. Instead of managing complex TASK-based workflows with intermediate tables, an engineer defines the transformation logic and the target lag. Snowflake handles the materialization, incremental updates, and orchestration. This significantly reduces the boilerplate code and management overhead, allowing engineers to focus on business logic rather than pipeline orchestration, which is the primary goal of modern data architecture.

Answer

Standard Views

Answer

Materialized Views

Answer

Stored Procedures

Question 11

A data architect is designing a pipeline that requires data freshness within 5 minutes across a series of five interdependent tables. The architect wants to minimize the operational overhead of managing task schedules and manual dependency logic. Which Snowflake feature should be prioritized to meet these requirements?

Accepted Answer

Dynamic Tables using the TARGET_LAG parameter set to 5 minutes.. Dynamic tables simplify data engineering by automating the refresh process based on a defined target lag rather than manual task orchestration. Snowflake's scheduler determines the optimal execution order to meet the freshness requirements across the entire graph. This shift from imperative to declarative pipelines reduces the risk of scheduling gaps and simplifies the management of complex dependencies.

Answer

Standard Streams and Tasks with explicit AFTER dependencies.

Answer

Materialized Views on each of the five tables with automatic clustering.

Answer

Snowpipe with a custom Lambda function to trigger downstream updates.

Question 12

A data engineer is setting up Snowpipe to ingest data from an S3 bucket. The engineer wants to ensure that Snowpipe is notified immediately when a new file arrives without polling the stage. What is the standard Snowflake recommendation for this configuration?

Accepted Answer

Set up an S3 Event Notification to send messages to a Snowflake-managed SQS queue.. Snowpipe auto-ingest is designed for low-latency ingestion by integrating with cloud provider notification services. This event-driven architecture is superior to manual polling as it reduces latency and compute costs associated with checking for new files. Configuring this correctly involves setting up an SQS queue or Event Grid to trigger the pipe object.

Answer

Configure a Snowflake Task to run every minute and execute the ALTER PIPE...REFRESH command.

Answer

Use a Python script on an EC2 instance to call the Snowpipe REST API every time a file is uploaded.

Answer

Increase the 'RECURSIVE' parameter in the Stage definition to allow Snowpipe to see nested folders.

Question 13

Refer to the exhibit. An architect observes these statistics in the Query Profile for a nightly batch job. What is the most effective architectural change to address the performance bottleneck shown?

Accepted Answer

Increase the virtual warehouse size (e.g., from Medium to Large or X-Large).. The exhibit shows significant 'spilling to remote storage', which occurs when the local SSD of the virtual warehouse is exhausted during a memory-intensive operation like a large join or sort. Remote spilling is much slower than local spilling. The primary solution is to increase the warehouse size to provide more memory and local disk.

Answer

Implement a Search Optimization Service on the join columns to reduce the number of partitions scanned.

Answer

Enable the Query Acceleration Service (QAS) for the warehouse to handle the excess data volume.

Answer

Rewrite the query to use a LATERAL FLATTEN on the largest tables to optimize memory usage.

Question 14

A Snowflake architect is implementing a Type-2 slowly changing dimension (SCD2) on the CUSTOMER_DIM table using a Stream on the source table CUSTOMER_RAW and a task that runs every 5 minutes. The task currently reads the stream and applies a MERGE that only handles inserts and updates. Historical versions are lost. The architect must preserve prior attribute values for changed customers and mark each row with effective and end timestamps. Which approach should the architect use to meet this requirement?

Accepted Answer

Use a MERGE that, for matched rows whose tracked attributes changed, sets the end timestamp and an is_current flag to false on the existing row, and inserts a new row with a new effective timestamp and is_current true.. SCD2 requires that a changed business key produce a new row while the previous row is expired rather than overwritten. A MERGE driven by the stream can detect changed attributes, close the existing current row with an end timestamp and a false current flag, and insert a new current row with a new effective timestamp. This preserves complete history and keeps exactly one active version per key.

Answer

Add a row-version column to CUSTOMER_DIM and use a MERGE with a WHEN MATCHED THEN UPDATE clause that increments the version and overwrites the attribute values in place.

Answer

Configure the CUSTOMER_DIM table with CHANGE_TRACKING = TRUE and query the table's change tracking metadata to reconstruct prior versions on demand.

Answer

Replace the task with a Materialized View over CUSTOMER_RAW that automatically retains a copy of each historical version whenever the base table changes.

Question 15

A Snowflake architect is designing a pipeline that ingests semi-structured JSON events from an internal stage and needs to write them into a VARIANT column. The events contain nested keys that vary in depth and casing across sources, and the team wants to flatten only a fixed set of known top-level keys while preserving the remaining structure for later analysis. Which approach best satisfies these requirements?

Accepted Answer

Create a view that exposes the fixed top-level keys with colon path notation (for example, src:payload:eventType) and leave the raw VARIANT column available for ad hoc queries.. Projecting known fields with colon path notation keeps the raw VARIANT column intact, so unknown keys and deeper nesting remain available. This satisfies both requirements: a stable interface for the fixed top-level keys and full retention of the original semi-structured payload for later analysis. Recursive flattening, inferred relational schemas, and key-name extraction all either destroy structure or fail to surface values.

Answer

Use LATERAL FLATTEN with the RECURSIVE argument set to TRUE on the raw VARIANT column to expand every nested key into separate rows, then filter the resulting KEY column to the known top-level keys.

Answer

Load the JSON into a relational table with one column per possible nested key, using the INFER_SCHEMA option of COPY INTO to auto-detect every attribute at load time.

Answer

Store each event as a separate row using the PARSE_JSON function and then apply OBJECT_KEYS to return only the known top-level keys as an array column.