A company has many nested JSON files in cloud storage. The company would like to use these files in complex JOIN operations. Which method would provide the BEST performance?

Question

Options

• ACreate an external table on the cloud storage without ingesting files into Snowflake.
• BCreate a materialized view over an external table, flattening the JSON.
• CUse the COPY INTO command on all the JSON files into a permanent table as a VARIANT and
• DUse the command on all the JSON files into an internal stage and use the stage for

• A
  5% (1)
• B
  14% (3)
• C
  76% (16)
• D
  5% (1)

Explanation

Loading nested JSON into a permanent Snowflake table as VARIANT via COPY INTO gives the best JOIN performance because the data lives inside Snowflake's optimized, micro-partitioned columnar storage - enabling full use of caching, pruning, and the query engine's parallel processing. Once loaded, Snowflake can flatten and query VARIANT columns inline using LATERAL FLATTEN, and JOINs operate on native internal data rather than round-tripping to external storage.

Why the distractors fail:

• A - External tables query files directly from cloud storage on every execution, making them slow for complex JOINs since there's no caching or micro-partitioning benefit.
• B - Materialized views on external tables in Snowflake cannot be automatically refreshed like standard materialized views, have refresh limitations, and still inherit the performance ceiling of the underlying external table.
• D - An internal stage is a temporary landing zone for files before loading, not a queryable data layer; running JOINs against staged files bypasses Snowflake's storage optimizations entirely.

Memory tip: Think of it as the "move in, then work" rule - external = data stays outside (slow rent), COPY INTO = data moves in (fast ownership). For performance-sensitive operations like JOINs, you always want data inside Snowflake's house.

No community discussion yet for this question.