Skip to main content

Materialized Views over External Tables

External Tables are a fantastic way to query data stored in an AWS S3 or Azure Blob Data Lake without paying to load the data into Snowflake.

However, External Tables have a major flaw: Performance. Scanning files over the internet is slow. If you connect a BI Dashboard (like Tableau) directly to an External Table, the dashboard will take 10+ seconds to load, frustrating your business users.

To fix this, you create a Materialized View over the External Table.

Topics Covered​

  • The Performance Problem
  • How Materialization Solves It
  • Creating the Materialized View
  • Cost Implications
  • The Hybrid Architecture Pattern

1. The Performance Problem​

Assume a retail company in Pune stores 5 years of historical sales data as Parquet files in AWS S3. You created an External Table over it.

-- This query scans S3 directly. It takes 15 seconds to return.
SELECT
CITY,
SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM EXT_HISTORICAL_SALES
GROUP BY CITY;

If a business analyst refreshes their dashboard 50 times a day, Snowflake will scan S3 50 times, taking 15 seconds every single time, and burning massive compute credits.

2. The Solution: Materialized Views​

A Materialized View (MV) is a pre-computed query whose results are physically stored (materialized) on disk inside Snowflake's micro-partitions.

When you create an MV over an External Table, Snowflake pulls the data from S3, computes the aggregation, and stores the results natively inside Snowflake.

When the analyst runs the query, Snowflake completely ignores S3. It simply reads the pre-computed results from the MV instantly.

Hands-On: Creating the Materialized View​

(To create a Materialized View, you must be using Snowflake Enterprise Edition or higher, and have the correct privileges. Check your role: SELECT CURRENT_ROLE();)

USE ROLE SYSADMIN;

-- Create the Materialized View on top of the External Table
CREATE OR REPLACE MATERIALIZED VIEW MV_HISTORICAL_SALES_BY_CITY
AS
SELECT
CITY,
SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM EXT_HISTORICAL_SALES
GROUP BY CITY;

What happens now?

  1. Snowflake immediately scans S3, calculates the sums for Pune, Mumbai, etc., and stores that tiny result set inside Snowflake.
  2. When the analyst queries SELECT * FROM MV_HISTORICAL_SALES_BY_CITY, it returns in milliseconds.

3. Auto-Refresh and Consistency​

What happens when a new Parquet file lands in S3? Is the Materialized View now out of date?

No! Snowflake automatically keeps the Materialized View in sync. If you configured your External Table with AUTO_REFRESH = TRUE (using AWS SQS or Azure Event Grid), Snowflake receives a notification when a new file arrives. A background Snowflake service automatically reads the new file and updates the sums in the Materialized View.

You never have to manually run a REFRESH command.

4. Cost Implications​

Materialized Views are not free. You must understand the tradeoffs:

  1. Compute Costs for Refresh: You are billed for the serverless compute used by Snowflake to monitor S3 and update the MV when new files arrive. If files arrive every minute, this background compute cost will be high.
  2. Storage Costs: You are billed for the storage space the MV consumes inside Snowflake. (Though for aggregated data, this is usually negligible).
  3. Compute Savings for Querying: You save massive compute credits because the BI Dashboard queries now take milliseconds using a tiny warehouse, instead of 15 seconds using a large warehouse scanning S3.

Rule of Thumb: Use MVs when the data is queried frequently (e.g., Dashboards) but updated infrequently.

5. The Hybrid Architecture Pattern​

The most common Data Lakehouse architecture is the "Hybrid" approach.

  • Recent Data (Hot): The last 6 months of active data is loaded physically into standard Snowflake tables for maximum performance and DML capabilities (UPDATE/DELETE).
  • Historical Data (Cold): The older 5 years of data is offloaded to S3 and exposed via an External Table to save storage costs.
  • The Union View: You create a standard view that UNION ALLs the standard table and the external table.
  • The Materialized View: You build Materialized Views on top of the external data for specific dashboard metrics to guarantee performance.

Summary​

  • External Tables are cheap for storage but slow for querying.
  • A Materialized View over an External Table pulls the data from S3, computes it, and stores the result inside Snowflake.
  • This provides millisecond query performance for BI dashboards.
  • Snowflake automatically refreshes the MV when new files land in the Data Lake.
  • Be mindful of the serverless compute costs associated with continuous background refreshing.