Snowflake
External Tables & Data Lake Integration
Query data directly from a cloud data lake without loading it into Snowflake storage.
An external table lets you query files that stay in your own S3, Blob or GCS bucket. Nothing is loaded into Snowflake.
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
CREATE EXTERNAL TABLE ext_orders (
order_date DATE AS TO_DATE(SPLIT_PART(METADATA$FILENAME, '/', 3))
)
LOCATION = @lake_stage
FILE_FORMAT = (TYPE = PARQUET);2
Why you would want this. The files may already be the source of truth for Spark jobs and other tools,
and duplicating a data lake into Snowflake is expensive and immediately stale.
3
Or the dataset is enormous and queried rarely, so paying to store a second copy is not justified.
4
Why it is slower, specifically. A native table has micro-partition metadata — minimum and maximum of
every column in every file — so Snowflake prunes before reading anything.
5
An external table has no such metadata. Snowflake knows the file paths and little else, so unless you help
it, a query reads the files.
6
Partition columns are how you help it. Data lakes are usually laid out with the partition in the path:
7
s3://bucket/orders/year=2026/month=03/day=15/part-0.parquet8
Define a partition column derived from the filename, and a filter on it eliminates whole directories
without opening a file. Without partition columns an external table over a large lake performs badly, and
that is the most common complaint about them.
9
Keeping the file list current. Snowflake caches which files exist. New arrivals are invisible until the
metadata refreshes:
10
ALTER EXTERNAL TABLE ext_orders REFRESH; -- manual
... AUTO_REFRESH = TRUE -- via cloud event notifications11
Auto-refresh uses the same notification mechanism as Snowpipe.
12
They are read-only, by construction. Snowflake does not own the files and cannot guarantee anything
about them changing underneath a query.
13
Iceberg tables are the modern answer. Snowflake supports Apache Iceberg, an open table format that
keeps its own rich metadata alongside the data files in your bucket.
14
external table -> read-only, weak metadata, slower
Iceberg table -> near-native performance, writes supported, still open15
Because the metadata lives with the data, Snowflake gets proper pruning and other engines can still read
and write the same table. For new data lake work this is usually the better choice, and worth naming in an
interview as where external tables are heading.
16
What the interviewer is probing.1. "What is an external table?" Probing: the definition. Stalls: "A view." Moves up: table
metadata over files in cloud storage that Snowflake does not manage or ingest.
17
2. "Why are they slower?" Probing: the mechanism. Stalls: "Network." Moves up: no
micro-partitions or min/max metadata, so pruning depends on partition columns you define from the
file paths.
18
3. "How do you make them usable?" Probing: the techniques. Stalls: "Just query them." *Moves
up:* partition columns from the path, a materialised view over the external table, and columnar
formats like Parquet.
19
4. "When is loading the data in the better answer?" Probing: the judgement. Stalls: "Never."
Moves up: whenever the data is queried repeatedly — external tables fit rarely-touched or
externally-owned data.