ARA-C01 Data Engineering Practice Test 6 — 15 Questions

Question 1

An architect is building a near-real-time pipeline that reads JSON events from a Kafka topic and must land them into Snowflake with sub-minute latency. The team has a Snowpipe streaming setup using the Snowflake Ingest SDK and writes to a table with a VARIANT column. They observe that the ingestion service occasionally reports channel errors and some events are missing after a client restart. Which configuration change best addresses the missing events?

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

Accepted Answer

Enable offset token tracking and pass the last committed offset token when reopening the channel so the SDK resumes from the correct position.. Snowpipe streaming channels expose offset tokens that represent the client's progress in the source stream. Persisting the last committed token and supplying it when a channel is reopened lets the SDK resume from the correct position, closing the gap that would otherwise appear after a client restart. Throughput tuning and alternative ingestion methods do not solve the resume-position problem.

Answer

Reduce the client's flush interval so that events are committed more frequently, eliminating the need to track positions across restarts.

Answer

Increase the number of channels per table and distribute events round-robin so that a single channel failure cannot drop data.

Answer

Switch the pipeline to a standard Snowpipe with AUTO_INGEST so that the cloud provider's event notifications guarantee delivery of every record.

Question 2

An architect is building a Dynamic Table that reads from a base table receiving continuous inserts. The refresh is configured with TARGET_LAG = '1 minute' and the warehouse is a dedicated XSMALL. Monitoring shows refreshes frequently take longer than one minute and sometimes overlap with the next scheduled run. The team wants to reduce refresh latency without changing the query logic. Which change is most appropriate?

Accepted Answer

Increase the warehouse size used by the Dynamic Table so that each refresh completes within the target lag window.. Dynamic Table refreshes execute on the warehouse tied to the object, so when a run cannot finish within TARGET_LAG the practical remedy is more compute. Scaling the warehouse up shortens each incremental refresh and keeps runs from overlapping. Changing refresh mode to full, altering initialization behavior, or replacing the object with a task-driven view does not reduce per-run duration and in some cases increases cost or complexity.

Answer

Set INITIALIZE to ON_CREATE and recreate the Dynamic Table so that the initial population happens at creation time.

Answer

Set REFRESH_MODE to FULL on the Dynamic Table so that each run recomputes the entire result set instead of applying incremental changes.

Answer

Convert the Dynamic Table to a regular view and schedule a task to run every minute to materialize the results into a physical table.

Question 3

A retail company ingests a daily 2 TB CSV file into a Snowflake table. The file is stored in an internal stage. The COPY INTO command currently runs with a single large file, and the load takes over 4 hours. The architect wants to reduce load time by leveraging parallelism. Which action should the architect take?

Accepted Answer

Split the large CSV file into multiple smaller files (e.g., 100–250 MB each) and use a single COPY INTO command with a pattern to load all files.. The key to faster loads is parallelizing across multiple files. A single large file is loaded by one thread, so splitting into smaller files allows Snowflake to distribute the load across many threads, significantly reducing elapsed time. Increasing warehouse size or changing format does not address the single-file bottleneck.

Answer

Use the VALIDATION_MODE = RETURN_ERRORS option to check for errors before loading, then rerun the COPY INTO command.

Answer

Increase the warehouse size to 4X-Large and rerun the same COPY INTO command on the single large file.

Answer

Convert the CSV file to JSON and load it using the VARIANT data type with a single COPY INTO command.

Question 4

A data engineering team needs to merge late-arriving dimension updates into a large target table. The source is a staging table containing both inserts and updates, and the target has a natural business key. The team wants a single statement that applies all changes atomically and avoids duplicate rows when the source contains multiple records for the same key. Which approach should the architect recommend?

Accepted Answer

Use a MERGE statement whose source is a subquery that deduplicates the staging table by business key, keeping the latest record per key.. A MERGE with a deduplicated source applies all changes in one atomic statement and guarantees each target row is touched once. Ranking staging rows per business key and keeping the latest avoids the duplicate-match error and yields deterministic upserts. Separate insert and update statements, full-table overwrite, and per-row task updates either risk nondeterminism, incur excessive cost, or fail to collapse duplicates.

Answer

Run an INSERT for new keys and a separate UPDATE for existing keys as two statements inside an explicit transaction.

Answer

Use INSERT OVERWRITE to replace the entire target table with a fresh join of the staging table and the existing target.

Answer

Create a Stream on the staging table and consume it with a task that issues individual UPDATE statements per changed row.

Question 5

An architect is designing a near-real-time ingestion path using Snowpipe streaming into a target table. The team must choose TWO design decisions that reduce end-to-end latency and cost for continuously arriving events. (Choose two.)

Accepted Answer

Use the Snowpipe streaming SDK or Snowflake Ingest SDK to push rows directly rather than writing files and triggering a pipe.. Snowpipe streaming lowers latency by pushing rows directly through the SDK instead of staging files, and the client flush interval determines how quickly buffered rows become visible. Together these reduce freshness delay and avoid file-handling overhead. Clustering, scheduled file-based loads, and task-driven inserts either add delay, add cost, or replace the streaming path with a slower polling model.

Answer

Batch events into larger files and rely on a scheduled COPY INTO to load them periodically.

Answer

Configure the target table with clustering on the event timestamp to improve insert throughput.

Answer

Disable the pipe and instead use a task that runs every minute to call INSERT statements against the target table.

Question 6

A healthcare analytics team stores patient encounter records in a Snowflake table that is updated continuously by an external ETL tool using MERGE statements. The team needs to build a downstream transformation that incrementally processes only the rows that were inserted or changed since the last run. They want to avoid reprocessing the entire table and do not want to add triggers or modify the ETL tool. Which Snowflake feature should the architect use to capture these changes?

Accepted Answer

Create a standard Stream on the encounter table to capture inserts, updates, and deletes.. A standard stream on the encounter table captures inserts, updates, and deletes by reading the table's change tracking metadata. This is exactly what the team needs because the ETL tool uses MERGE, which produces updates as well as inserts. Append-only streams would miss updates, the CHANGES clause lacks persistent offset tracking, and materialized views do not expose change data.

Answer

Use a materialized view that refreshes automatically to expose only changed rows.

Answer

Create a Stream on the encounter table with an append-only stream type.

Answer

Enable change tracking on the encounter table and query the CHANGES clause directly.

Question 7

A data engineer must load a daily batch of CSV files from an internal stage into a staging table. The files use a pipe delimiter, include a header row, and contain date values in a non-default format. The team wants to validate that the load succeeded and capture any rejected rows for review. Which configuration should the architect specify?

Accepted Answer

Define a file format with TYPE = CSV, FIELD_DELIMITER = '|', SKIP_HEADER = 1, and a DATE_FORMAT matching the source, then use COPY INTO with ON_ERROR = 'CONTINUE' and a validation_mode run first.. A file format that matches the delimiter, skips the header, and specifies the source date format is the foundation. COPY INTO with ON_ERROR = CONTINUE keeps valid rows loading while rejected rows are recorded, and a VALIDATION_MODE run surfaces errors before any data is committed. The other approaches either ignore the delimited structure, avoid loading into the target, or abort on the first bad row.

Answer

Use COPY INTO with MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE and rely on the target table column order to map the fields.

Answer

Create an external table over the stage and query the CSV files directly, converting the date with TO_DATE in the SELECT statement.

Answer

Load the files with COPY INTO using ON_ERROR = 'ABORT_STATEMENT' and inspect the query history afterward to identify which rows failed.

Question 8

A data engineering team needs to transform raw JSON events into a curated table that refreshes automatically as new data arrives, without writing or scheduling any orchestration code. The target must reflect changes within a defined lag and be queryable like a regular table. Which Snowflake feature should the architect recommend?

Accepted Answer

A Dynamic Table defined with a target lag, which Snowflake refreshes automatically based on the query definition and the specified lag.. Dynamic Tables let the architect declare the transformation and a target lag, and Snowflake handles scheduling, dependency tracking, and incremental refresh automatically. The result is a queryable table that stays current without external orchestration. Tasks require manual logic, streams and views do not persist a curated result, and standard views have no refresh mechanism.

Answer

A task with a schedule that runs a CREATE OR REPLACE TABLE AS SELECT statement every minute to rebuild the curated table from scratch.

Answer

A view with a scheduled refresh policy and a query acceleration service enabled to keep the result current.

Answer

A stream on the raw table plus a materialized view over the stream to expose the transformed rows as they arrive.

Question 9

A retail company ingests point-of-sale data into a Snowflake table using Snowpipe. The data arrives as JSON files in an internal stage. The architect needs to transform the semi-structured JSON into a relational format and load it into a reporting table. The transformation involves flattening nested arrays and applying several business rules. The volume is high, and the team wants to minimize latency and cost. Which approach should the architect recommend?

Accepted Answer

Load raw JSON into a staging table using Snowpipe, then use a Stream and a Task to transform and merge into the reporting table.. The recommended approach is to load raw JSON into a staging table via Snowpipe, then use a stream and task to incrementally transform and merge into the reporting table. This leverages Snowflake's native change tracking and scheduling, processes only new data, and avoids external dependencies. It balances latency and cost effectively for high-volume semi-structured data.

Answer

Create a materialized view on the staging table that automatically flattens the JSON and applies business rules.

Answer

Use a Snowpipe with a COPY INTO statement that includes a transformation query to flatten and transform the JSON during load.

Answer

Use an external function to call an AWS Lambda that transforms the JSON before loading into Snowflake.

Question 10

A data engineer needs to load data from a local file system into a Snowflake table. The file is 10 GB in size and contains CSV data. The engineer wants to use the most efficient method for a one-time bulk load. Which Snowflake feature should the engineer use?

Accepted Answer

COPY INTO command. For a one-time bulk load from a local file, the engineer should first upload the file to an internal stage with PUT, then use COPY INTO to load it into the table. COPY INTO is optimized for bulk loading and is the most efficient method. Other options are designed for continuous ingestion or external access, not bulk loading.

Answer

Snowpipe

Answer

Snowflake Connector for Kafka

Answer

External tables

Question 11

A financial services firm uses Snowflake to store transactional data. The data engineering team needs to implement a process that automatically copies new files from an external stage into a raw table and then triggers a series of dependent transformations. The team wants to minimize manual intervention and ensure that transformations run only when new data is available. They also want to avoid running transformations on empty batches. Which combination of Snowflake features should the architect use?

Accepted Answer

Snowpipe with a stream on the raw table and a task with a WHEN clause checking SYSTEM$STREAM_HAS_DATA.. The combination of Snowpipe, a stream on the raw table, and a task with a WHEN clause using SYSTEM$STREAM_HAS_DATA ensures automatic ingestion and conditional transformation execution. Snowpipe loads new files, the stream tracks changes, and the task runs only when data is present, minimizing manual intervention and avoiding empty batches.

Answer

Snowpipe with a stream on the raw table and a task that runs on a fixed schedule without a WHEN clause.

Answer

A task that calls a stored procedure to copy files from the external stage and then run transformations sequentially.

Answer

Snowpipe with a task that runs on a fixed schedule and checks the row count of the raw table.

Question 12

A healthcare analytics team uses Snowflake to analyze patient records. They have a large fact table 'ENCOUNTERS' that is clustered by 'PATIENT_ID' and 'ENCOUNTER_DATE'. The team frequently runs queries that filter on 'FACILITY_ID' and 'DIAGNOSIS_CODE', which are not part of the clustering key. These queries perform poorly. The architect needs to improve performance without changing the existing clustering key, as it benefits other queries. What should the architect do?

Accepted Answer

Create a search optimization service on the columns FACILITY_ID and DIAGNOSIS_CODE.. The search optimization service is designed to accelerate queries with filters on columns that are not part of the clustering key. It creates a persistent data structure that enables efficient pruning for equality and IN filters. This allows the team to keep the existing clustering key for other queries while improving performance for FACILITY_ID and DIAGNOSIS_CODE filters. It is the appropriate solution without altering the clustering key.

Answer

Add a secondary clustering key on FACILITY_ID and DIAGNOSIS_CODE to the table.

Answer

Create a materialized view on ENCOUNTERS that includes FACILITY_ID and DIAGNOSIS_CODE, and rewrite queries to use the materialized view.

Answer

Recluster the table manually using ALTER TABLE ... RECLUSTER, specifying the new columns.

Question 13

A healthcare analytics company stores patient encounter data in a Snowflake table with columns: encounter_id (NUMBER), patient_id (NUMBER), encounter_date (DATE), and diagnosis_code (VARCHAR). The table is 20 TB and grows by 100 GB per day. Most queries filter on encounter_date and join to a patient dimension on patient_id. The architect must design a clustering key to optimize these queries while minimizing reclustering cost. Which clustering key should the architect choose?

Accepted Answer

CLUSTER BY (encounter_date, patient_id). The clustering key should lead with the column most frequently used in range filters, which is encounter_date, and then include the join column patient_id. This order enables partition pruning on date ranges and improves join performance by co-locating related patient rows. The reversed order or unrelated columns fail to support the dominant query patterns and would increase scan volume and reclustering overhead.

Answer

CLUSTER BY (patient_id, encounter_date)

Answer

CLUSTER BY (encounter_id)

Answer

CLUSTER BY (diagnosis_code, encounter_date)

Question 14

A data engineering team needs to load data from an on-premises Oracle database into Snowflake on a recurring basis. The data volume is large (several terabytes), and the team wants to minimize the load on the source system. They also require the ability to perform incremental loads based on a timestamp column. Which Snowflake feature should the architect recommend?

Accepted Answer

Snowflake Connector for Oracle (or a similar partner ETL tool) to perform incremental extraction and loading.. The Snowflake Connector for Oracle is specifically designed to enable incremental data ingestion from Oracle databases. It supports timestamp-based incremental loads and is optimized to reduce load on the source system. This makes it the ideal choice for large-scale, recurring data loads from on-premises Oracle into Snowflake, without the need for intermediate staging or manual extraction.

Answer

Snowpipe with auto-ingest from cloud storage.

Answer

Use the COPY INTO command with a stage that points to the Oracle database.

Answer

Create an external table that references the Oracle database.

Question 15

A data engineer is designing a batch transformation pipeline using Dynamic Tables. The source table is updated hourly, and the target Dynamic Table must reflect changes within 30 minutes. The transformation involves a complex join and aggregation. Which approach best meets the freshness requirement while minimizing cost?

Accepted Answer

Set the target lag to 30 minutes and use a dedicated virtual warehouse sized based on the complexity of the transformation, monitoring and adjusting as needed.. Dynamic Tables refresh automatically based on the target lag. The warehouse size must be adequate to complete the refresh within the lag. Starting with a size based on transformation complexity and monitoring allows tuning to meet freshness without overspending. This approach balances performance and cost effectively.

Answer

Set the target lag to 30 minutes and use a dedicated virtual warehouse sized as XSMALL.

Answer

Set the target lag to 30 minutes and use a multi-cluster warehouse with a minimum of 1 and maximum of 3 clusters.

Answer

Set the target lag to 30 minutes and use a dedicated virtual warehouse sized as LARGE.