Question 35

Explanation

✅ Convert the External Tables into Internal Snowflake Tables and materialize the complex join result into a new table.

Explanation:

Snowflake does not support joins inside Materialized Views, so the best way to optimize a frequently executed, complex join between large External Tables is to ingest the external data into internal tables (using COPY INTO or Snowpipe), and then materialize the join result into a new internal table. This approach allows Snowflake to:

• Leverage automatic pruning, caching, and clustering

• Optimize join performance using native storage

• Support complex join logic that Materialized Views cannot handle

You can schedule this join as part of a regular ETL/ELT pipeline to keep the materialized result up to date.

❌ Why the other options are incorrect?

Create a Standard View over the join to simplify the query syntax →
Incorrect, because Standard Views do not store precomputed results. Every query still performs the full join operation, leading to poor performance.

Create a Materialized View (MV) on top of the External Tables, pre-joining and optimizing the data →
Incorrect, because Snowflake does not allow joins in Materialized View definitions. MVs must be defined over a single base table.

Convert the External Tables into Temporary Tables and then join them →
Incorrect, because Temporary Tables are session-scoped and do not persist across sessions. This would require repeated ingestion and processing, which is inefficient for dashboards.

For more details, see:

• Snowflake Docs: Introduction to External Tables

• Snowflake Docs: Materialized Views – Limitations