Snowflake
External Tables & Data Lake Integration
Query data directly from a cloud data lake without loading it into Snowflake storage.
An external table lets Snowflake query data files that remain in external cloud storage (S3, Azure Blob, GCS) as if they were a native Snowflake table, without physically loading or copying the data. This is useful when files must remain in a data lake for other consumers, or when loading the full dataset into Snowflake storage isn't justified by the query patterns.
An external table is like a library catalog card that points to a book kept on a shelf in a different building — you can read the book by following the reference, but you can't check it out, write in it, or fully control how it's organized.
Key Concepts
1
External tables define a schema mapping over the raw files (commonly Parquet, ORC, Avro, CSV, or JSON) referenced via a stage, and Snowflake reads directly from the source location at query time. Because the underlying files are outside Snowflake's control, external tables are inherently read-only, and query performance is generally lower than native tables since there's no Snowflake-managed micro-partition metadata (though partitioning and manual metadata refresh can help).
read-only
2
To mitigate the performance gap, external tables can define partition columns derived from the file path structure (e.g., /year=2024/month=01/) and can be registered with automatic refresh via cloud storage event notifications, similar to Snowpipe, so newly arriving files are automatically recognized without manual ALTER EXTERNAL TABLE ... REFRESH calls.
automatic refreshpartition columns/year=2024/month=01/ALTER EXTERNAL TABLE ... REFRESH
3
A related, often preferable modern option for open formats is Apache Iceberg tables in Snowflake, which offer more native-like performance and even write support over data lake storage, versus the more limited, read-only external table model — worth mentioning as the evolving alternative in data lake integration discussions.
Apache Iceberg tables