What are Snowflake External Tables and when would you use them over internal tables?

An External Table is a read-only Snowflake object that maps a schema onto files stored in an external stage (S3, Azure Blob, GCS) without loading the data into Snowflake-managed storage. The files stay in your own cloud bucket; Snowflake queries them in place by scanning the raw files through the Virtual Warehouse.

External Tables support partitioning based on file path expressions (e.g., year/month/day directory structure) which allows partition pruning similar to Hive-style partitioning. The partition metadata is maintained in a Snowflake-managed catalog and must be kept current with ALTER EXTERNAL TABLE ... REFRESH or automated via cloud event notifications.

Key limitations compared to internal tables: read-only (no DML), no Time Travel, no Fail-safe, no automatic micro-partition optimization, slower query performance since data is not stored in Snowflake's columnar compressed format, and schema must be explicitly defined via the VALUE:field::TYPE VARIANT path syntax.

When to use: querying a data lake without ingesting it (cost-sensitive archival data), sharing data already in S3/Azure Blob with Snowflake analysts, compliance scenarios where data must remain in a specific bucket, or bridging a data lake with Snowflake analytics without full migration.

-- Create an external table on an S3 stage
CREATE EXTERNAL TABLE ext_clickstream (
  event_ts   TIMESTAMP_NTZ AS (VALUE:c1::TIMESTAMP_NTZ),
  user_id    VARCHAR       AS (VALUE:c2::VARCHAR),
  event_type VARCHAR       AS (VALUE:c3::VARCHAR)
)
PARTITION BY (TO_DATE(event_ts))
LOCATION = @my_s3_stage/clickstream/
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = 'PARQUET');

-- Refresh metadata after new files arrive (if not using AUTO_REFRESH)
ALTER EXTERNAL TABLE ext_clickstream REFRESH;

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.