A materialized view is a database object that stores the result of a query for later access. Because the result is persisted, querying it can be faster than rerunning the defining query, especially against external tables. section 3.4.2 section 3.5
In Snowflake, an external table can expose files in cloud storage, but querying it is generally slower than querying regular Snowflake tables. A materialized view stores the external-table data in Snowflake while preserving the queryable result. section 3.4.1 section 3.4.2
Use the pattern with these operational considerations:
- Define the view over an external table, then query the materialized view instead of repeatedly querying the external data. section 3.4.2
- Rely on Snowflake to maintain the view after the external table refreshes, whether that refresh occurs automatically through cloud notifications or manually. section 3.4.1 section 3.4.2
- Account for storage and compute costs, and verify that the Snowflake edition supports materialized views; they require Enterprise Edition or higher. section 3.4.2
Materializing external orders
Create ORDERS_BISTRO_MV from ORDERS_BISTRO_EXT with the selected order columns, then run SELECT * FROM ORDERS_BISTRO_MV to retrieve the same data through the faster persisted result. section 3.4.2
Listing 3.4 Creating an external table from an external stage
create external table ORDERS_BISTRO_EXT ( customer varchar as (VALUE:c1::varchar), order_date date as (VALUE:c2::date), delivery_date date as (VALUE:c3::date), baked_good_type varchar as (VALUE:c4::varchar), quantity number as (VALUE:c5::number), source_file_name varchar as metadata$filename ) location = @BISTRO_STAGE auto_refresh = FALSE file_format = ORDERS_CSV_FORMAT;
Use the external-table definition to see the stage location, metadata filename, typed columns, file format, and refresh configuration that precede materialization. section 3.4.1
