You are designing a data ingestion pipeline in Snowflake using Snowsight to load JSON data from an external stage (AWS S3). The JSON files contain nested arrays and objects, and you need to flatten…

Question

Options

• AUse the = TRUE' file format option and `COPY INTO' with a 'SELECT statement that uses
• BLoad the entire JSON file into a VARIANT column in a staging table and then use a separate query
• CCreate an external table pointing to the JSON files and then use a 'CREATE TABLE AS SELECT
• DUse a file format with ` TYPE = JSON' and = TRUE (if the JSON data is enclosed in an array). In
• ELoad the JSON files directly into the FLATTENED_DATX table without flattening and rely on

• A
  5% (1)
• B
  14% (3)
• C
  9% (2)
• D
  68% (15)
• E
  5% (1)

Explanation

Option D is correct because Snowflake's COPY INTO with TYPE = JSON and STRIP_OUTER_ARRAY = TRUE is purpose-built for this scenario - it strips the enclosing array from JSON files during ingestion and, when combined with a SELECT that uses FLATTEN(), allows you to transform nested structures into relational rows in a single load step, eliminating post-load transformation overhead.

Why the distractors fail:

• A is incomplete as written - it describes the right approach conceptually but is truncated and doesn't constitute a valid, complete solution.
• B loads into a VARIANT staging table first, which adds an unnecessary intermediate step and extra query complexity; the goal is to avoid post-load transformation.
• C uses an external table with CREATE TABLE AS SELECT, which works but is more complex to manage - external tables add DDL overhead and are better suited for ad-hoc querying, not streamlined ingestion pipelines.
• E is fundamentally wrong - loading without flattening defeats the entire purpose since FLATTENED_DATA requires discrete relational columns (NAME, VALUE, TIMESTAMP).

Memory tip: Think "STRIP then FLATTEN" - STRIP_OUTER_ARRAY = TRUE removes the JSON wrapper, then FLATTEN() in your SELECT explodes nested arrays into rows. Together they handle nested JSON in one COPY INTO pass. If you see "nested JSON + relational target + no post-load transform," reach for TYPE = JSON + STRIP_OUTER_ARRAY + FLATTEN.

No community discussion yet for this question.