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.

• ✗

  Create a materialized view over the external stage that shreds the JSON into relational columns.

  Why it's wrong here

  Materialized views cannot be created directly over an external stage; they require a base table. Even if a base table existed, materializing shredded JSON would duplicate data and add storage and maintenance costs. This approach is not feasible as described and does not meet the minimization of duplication and storage.

• ✓

  Create an external table with a VARIANT column and use the FLATTEN function to query nested elements.

  Why this is correct

  External tables allow querying data directly from the external stage without loading it into Snowflake, avoiding duplication and storage costs. Using a VARIANT column accommodates the semi-structured and variable schema, and FLATTEN enables querying nested arrays and objects. This approach meets the requirement to analyze JSON data while minimizing data movement and storage.

• ✗

  Use a directory table and a stored procedure to parse and insert JSON into a relational table.

  Why it's wrong here

  A directory table provides file-level metadata but does not enable direct querying of JSON content. Using a stored procedure to parse and insert duplicates the data into Snowflake, incurring storage costs and adding complexity. This does not minimize duplication and is not a direct query solution.

• ✗

  Load the JSON files into a Snowflake table with a VARIANT column using COPY INTO, then query with FLATTEN.

  Why it's wrong here

  Loading the data into a Snowflake table duplicates the data and incurs storage costs, which the requirement explicitly aims to minimize. While this approach simplifies querying and may improve performance, it does not satisfy the cost and duplication constraints. The architect should prefer a solution that queries data in place.