ARA-C01 Data Engineering Practice Test 3 — 15 Questions

Question 1

A data engineering team is designing a pipeline where a Dynamic Table must refresh incrementally from a base table that has a column `last_updated` of type TIMESTAMP_NTZ, but the base table is not a stream. The team wants to minimize warehouse cost and ensure that only changed rows are processed. Which approach should the architect recommend?

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

Accepted Answer

Create a stream on the base table and use the stream as the source for the Dynamic Table.. A stream on the base table captures row-level changes, enabling the Dynamic Table to refresh incrementally. This avoids full table scans, reduces warehouse cost, and ensures only changed rows are processed. The other options either do not provide change tracking or force full refreshes, which are inefficient for large tables.

Answer

Enable change tracking on the base table and set the Dynamic Table's refresh mode to FULL.

Answer

Create a materialized view on the base table and then define the Dynamic Table on the materialized view.

Answer

Define the Dynamic Table with a WHERE clause filtering on `last_updated` greater than the maximum timestamp from the previous refresh.

Question 2

An architect is designing a pipeline that joins a 5 TB fact table with a 200 GB dimension table. The fact table is partitioned by event_date and the dimension is relatively static. Queries filter on event_date and join on customer_id. The team wants to minimize data scanned and cost while keeping the dimension current. Which design should the architect choose?

Accepted Answer

Cluster the fact table on event_date and customer_id, and cluster the dimension table on customer_id, relying on automatic reclustering to keep both well ordered.. The query filters on event_date and joins on customer_id, so ordering the fact table's micro-partitions by those columns enables pruning and efficient joins, and clustering the dimension on customer_id aligns both sides for hash joins. Automatic reclustering keeps the layout healthy as data changes. Materialized views, external partitioning, and cross-warehouse joins do not fit this workload.

Answer

Load the dimension table into a separate warehouse and use a cross-warehouse join with a shared cache to avoid scanning the dimension repeatedly.

Answer

Create a materialized view that pre-joins the fact and dimension tables and refreshes automatically whenever either base table changes.

Answer

Partition the fact table physically by event_date using a partitioned external table and query it with a stage-backed external table over Parquet files.

Question 3

An architect is designing a pipeline that ingests semi-structured JSON data into a Snowflake table. The JSON documents contain nested arrays and varying schemas. The team wants to query specific fields efficiently and minimize storage costs. Which approach should the architect recommend?

Accepted Answer

Use a VARIANT column for the raw JSON and create a separate table with extracted fields using a stream and task to load new data.. Storing raw JSON in a VARIANT column preserves flexibility, while extracting frequently queried fields into a separate table improves query performance and can reduce storage if the extracted table is smaller. Using a stream and task automates the process, ensuring new data is transformed. This design balances the need for schema flexibility with efficient querying and cost management.

Answer

Store the JSON in a VARCHAR column and use Snowflake's PARSE_JSON function at query time to access nested fields.

Answer

Store the entire JSON document in a VARIANT column and create a materialized view that extracts frequently queried fields.

Answer

Store the JSON in a VARIANT column and define a search optimization service on the extracted fields.

Question 4

A data architect is designing a solution to share a large dataset with a partner organization. The dataset is stored in a Snowflake table and contains sensitive information. The partner needs read-only access to a subset of columns and rows based on their region. The architect wants to ensure that the partner cannot see data outside their region and cannot modify the data. Which approach should the architect use?

Accepted Answer

Create a secure view that filters rows based on the partner's region and grants SELECT on the view to the partner's role.. A secure view is the best choice because it encapsulates both row-level and column-level filtering, hides the underlying table structure, and can be granted to the partner's role. It ensures the partner sees only the allowed data and cannot modify it, as views are read-only unless explicitly made updatable.

Answer

Create a separate table containing only the partner's region data and grant SELECT on that table.

Answer

Grant the partner's role SELECT on the table and use a row access policy to filter rows based on the partner's region.

Answer

Create a materialized view that pre-filters the data for the partner's region and grant SELECT on the materialized view.

Question 5

A media company stores video playback events in a Snowflake table that is partitioned by event_date. The table contains billions of rows, and queries often filter on event_date and user_id. The architect notices that queries filtering on user_id alone perform full table scans and are slow. The team wants to improve performance for these queries without changing the query patterns. Which action should the architect take?

Accepted Answer

Add a composite cluster key on (event_date, user_id).. A composite cluster key on (event_date, user_id) is the best choice because it aligns with the existing natural ordering by event_date and adds user_id as a secondary clustering dimension. This enables effective micro-partition pruning for queries filtering on event_date, user_id, or both, improving performance without requiring query changes or additional objects.

Answer

Create a materialized view that pre-aggregates playback events by user_id.

Answer

Add a cluster key on the user_id column.

Answer

Enable search optimization service on the user_id column.

Question 6

A data architect is designing a pipeline that uses Snowflake Tasks to orchestrate a series of SQL statements. The tasks must run in a specific order, and each task depends on the successful completion of the previous one. The architect wants to ensure that if any task fails, the entire pipeline stops and an alert is triggered. Which task configuration should the architect use?

Accepted Answer

Create a task graph with dependencies and configure an error notification integration on the root task to send alerts on failure.. A task graph with AFTER dependencies ensures sequential execution. Configuring an error notification integration on the root task enables Snowflake to send alerts if any task fails, and the pipeline stops because dependent tasks are not executed. This meets both the ordering and alerting requirements. Other options either lack proper dependency management or do not provide failure alerts.

Answer

Create a single task with a stored procedure that contains all SQL statements in a transaction.

Answer

Create a task graph and set the SUSPEND_TASK_AFTER_NUM_FAILURES parameter to 0 on each task to immediately suspend the pipeline on failure.

Answer

Create a task graph where each task has a predecessor defined with AFTER, and set the final task to call SYSTEM$SEND_EMAIL on failure.

Question 7

A data architect is designing a pipeline that ingests data into Snowflake from an on-premises Oracle database. The data volume is large, and the team wants to minimize the load on the source system while ensuring data consistency. They plan to use Snowflake's native connectors. Which TWO considerations are most important when configuring the ingestion? (Choose two.)

Accepted Answer

Use the Snowflake Connector for Oracle with change tracking to capture only changed rows.. The two most important considerations are using change tracking to capture only changed rows and scheduling ingestion during off-peak hours. Change tracking minimizes the volume of data extracted, reducing load on the source. Off-peak scheduling further reduces contention. Together, they address the goals of minimizing source impact and ensuring data consistency.

Answer

Use a larger warehouse size to speed up the extraction from Oracle.

Answer

Enable multi-cluster warehouses to handle the increased concurrency during ingestion.

Answer

Configure the connector to use a dedicated warehouse with auto-suspend and auto-resume enabled.

Question 8

A retail company ingests JSON clickstream data into a Snowflake table using a Snowpipe that references a pipe object with COPY INTO. The JSON records have a nested structure: each record includes an 'event_time' field and an 'items' array. The architect needs to flatten the 'items' array so that each item becomes a separate row, while preserving all other top-level fields. The ingestion must remain continuous. Which approach should the architect use?

Accepted Answer

Define the pipe with a COPY INTO statement that uses the VARIANT data type and a LATERAL FLATTEN clause to expand the 'items' array into separate rows.. Using LATERAL FLATTEN within the COPY INTO transformation of a Snowpipe allows continuous ingestion and immediate flattening of the JSON array. This leverages Snowflake's native semi-structured data capabilities without additional scheduling or external processing, ensuring each item becomes a row while other fields are preserved. It is the most efficient and integrated solution for the scenario.

Answer

Load the JSON into a table with a single VARIANT column, then create a materialized view that flattens the array.

Answer

Create a stored procedure that runs on a schedule to parse the JSON and insert flattened rows into a target table.

Answer

Use the PARSE_JSON function in a task that runs every minute to transform the data and insert into a flattened table.

Question 9

You are designing a Snowflake data pipeline that ingests JSON event data from an external stage. The events have a nested array of product IDs, and downstream queries frequently filter on individual product IDs within that array. You want to avoid full table scans and minimize storage costs. What is the most appropriate approach?

Accepted Answer

Load the JSON into a VARIANT column and create a materialized view that flattens the array and clusters by product ID.. Flattening the nested array into a materialized view and clustering by product ID enables efficient pruning on the product ID filter. Materialized views pre-compute and store the flattened data, reducing query-time computation and storage costs. This approach avoids full table scans and is a best practice for nested JSON with frequent array element filters.

Answer

Load the JSON into a VARIANT column and create a regular view that flattens the array at query time.

Answer

Load the JSON into a VARIANT column and create a separate table for each product ID using a stored procedure.

Answer

Load the JSON into a VARIANT column and create a search optimization service on the array path.

Question 10

A media company ingests JSON logs into a Snowflake table RAW_LOGS with a VARIANT column LOG_DATA. They need to extract nested fields such as user_id and event_type for downstream transformations. The JSON structure is deeply nested and sometimes contains arrays. The architect wants to minimize compute cost and ensure schema evolution. Which approach should be used?

Accepted Answer

Use the GET_PATH or colon notation to extract nested fields and store them in separate columns.. Extracting nested fields using GET_PATH or colon notation is efficient and cost-effective for VARIANT data. It avoids the performance overhead of FLATTEN for arrays and allows selective extraction. Storing extracted fields in separate columns improves downstream query performance and supports schema evolution by adding columns as needed. This approach aligns with minimizing compute cost and handling deeply nested JSON.

Answer

Define a schema detection and evolution process using the VARIANT data type and automatic schema inference.

Answer

Load the JSON data into a stage and use a stored procedure to parse and insert into a relational table.

Answer

Use the FLATTEN function with LATERAL to explode arrays and extract nested fields.

Question 11

A retail company uses Snowflake to store point-of-sale transactions. The data engineering team needs to create a new table that will hold a subset of columns from the existing TRANSACTIONS table and automatically stay synchronized with any inserts, updates, and deletes on the source table. The team wants minimal operational overhead and no manual refresh scheduling. Which Snowflake feature should the architect recommend?

Accepted Answer

A dynamic table that selects the desired columns from TRANSACTIONS. Dynamic tables are designed for declarative, automatically refreshed transformations. By defining the target table with a query that selects the required columns, Snowflake handles change propagation and refresh scheduling. This meets the need for a synchronized subset with minimal operational effort, unlike materialized views, streams with tasks, or regular views.

Answer

A regular view on TRANSACTIONS

Answer

A stream on TRANSACTIONS combined with a task that runs a MERGE statement

Answer

A materialized view on TRANSACTIONS

Question 12

A financial services firm uses Snowflake to store transactional data. They have a table 'TRANSACTIONS' with a clustering key on 'TRANSACTION_DATE'. The table is very large (hundreds of terabytes). The firm wants to ensure that the clustering is maintained efficiently and that queries filtering on TRANSACTION_DATE perform well. They notice that the clustering depth is high. Which action should the architect recommend to improve clustering?

Accepted Answer

Ensure that automatic clustering is enabled and consider increasing the clustering key granularity or adjusting the clustering key.. Automatic clustering should be enabled to maintain clustering as data changes. If clustering depth is high, the clustering key may need adjustment, such as using a coarser granularity (e.g., date truncation) to reduce the number of distinct values and improve clustering efficiency. This ensures that queries filtering on TRANSACTION_DATE continue to perform well without manual intervention.

Answer

Manually recluster the table using ALTER TABLE TRANSACTIONS RECLUSTER.

Answer

Disable automatic clustering and perform manual reclustering during off-peak hours.

Answer

Increase the size of the virtual warehouse used for DML operations on the table.

Question 13

An e-commerce company wants to ensure that their Snowflake data warehouse can handle concurrent queries from multiple business units without performance degradation. They have a single large warehouse. Which feature should the architect recommend to isolate workloads and optimize resource allocation?

Accepted Answer

Enable multi-cluster warehouses to automatically scale compute resources based on concurrency.. Multi-cluster warehouses are the best choice because they automatically scale compute resources to handle concurrent queries from multiple business units. They provide workload isolation by adding clusters as needed, ensuring consistent performance without manual intervention. Other options either do not address concurrency scaling or are focused on cost control rather than performance isolation.

Answer

Create separate virtual warehouses for each business unit and assign users accordingly.

Answer

Use query acceleration service to speed up individual queries.

Answer

Implement resource monitors to limit credit usage per business unit.

Question 14

A data architect is implementing a near real-time ingestion pipeline using Snowpipe Streaming. The source is an Apache Kafka topic with high throughput. The architect wants to ensure exactly-once semantics and minimize latency. Which configuration is most appropriate?

Accepted Answer

Use the Snowflake Kafka Connector with the Snowpipe Streaming ingest method and configure the connector to use a Snowflake-managed offset token to track Kafka offsets.. Snowpipe Streaming with Snowflake-managed offset tokens provides exactly-once semantics by tracking Kafka offsets in Snowflake. This allows the connector to resume from the last committed offset, ensuring no duplicates or data loss. It also minimizes latency by streaming data directly into Snowflake without intermediate staging.

Answer

Use the Snowflake Kafka Connector with the Snowpipe Streaming ingest method and configure the connector to use Kafka Connect's offset management with a dead-letter queue.

Answer

Use the Snowflake Kafka Connector with the Snowpipe Streaming ingest method and enable exactly-once semantics by setting the connector property 'snowflake.streaming.enable.exactly.once' to true.

Answer

Use the Snowflake Kafka Connector with the Snowpipe ingest method and configure the connector to use a Snowflake-managed offset token to track Kafka offsets.

Question 15

A data engineering team loads Parquet files into a Snowflake table using Snowpipe. They notice that the ingestion latency is higher than expected, and the number of pending files is growing. They have a dedicated virtual warehouse for Snowpipe. Which action is most likely to improve Snowpipe performance without changing the file format?

Accepted Answer

Reduce the number of files by combining smaller files into larger files.. Snowpipe automatically manages compute and scales elastically, so warehouse size and pipe count are irrelevant. The primary lever for improving Snowpipe throughput is optimizing file sizes. Many small files cause inefficiencies in queue processing and metadata handling. Consolidating files into larger, compressed files reduces overhead and allows Snowpipe to process data more efficiently, directly reducing the pending file count and latency.

Answer

Increase the number of pipes to parallelize ingestion.

Answer

Increase the size of the warehouse used by Snowpipe.

Answer

Enable auto-ingest using cloud storage event notifications.