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;