ARA-C01 Data Engineering Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Load raw JSON into a staging table using Snowpipe, then use a Stream and a Task to transform and merge into the reporting table.

  Why this is correct

  This approach decouples ingestion from transformation, allowing Snowpipe to load raw data quickly while a stream captures new rows. A task can then process only the new data, flatten the JSON, apply business rules, and merge into the reporting table. It minimizes latency and cost by processing incrementally and leveraging Snowflake's native scheduling.

• ✗

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

  Why it's wrong here

  Materialized views cannot contain complex transformations like flattening nested arrays or business logic that requires joins or procedural code. They are limited to simple aggregations and filters on a single table. They also do not support semi-structured data flattening directly. This option is not feasible for the required transformation.

• ✗

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

  Why it's wrong here

  COPY INTO transformations are limited to simple column expressions and do not support complex flattening of nested arrays or multi-step business rules. While COPY INTO can parse JSON, it cannot perform the required flattening and rule application efficiently. This would force the team to load raw JSON and then run separate transformations, increasing latency and complexity.

• ✗

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

  Why it's wrong here

  External functions add network latency and cost, and they require managing external compute. They are not ideal for high-volume, low-latency transformations that can be done within Snowflake. The overhead of invoking a Lambda for each batch or row would likely increase both latency and cost, contradicting the team's goals.