Brolly Academy | Snowflake
External Tables in Snowflake
Syntax, Partitioning, Auto-Refresh and When to Use Them (2026)
A plain-English guide for October 2026: what external tables are, how to create, partition and refresh them, what they cost, and how they compare with regular tables, Iceberg tables, COPY INTO and Snowpipe.
External tables in Snowflake explained: how they read files in an external stage, CREATE EXTERNAL TABLE syntax, partitions, auto-refresh, cost, Iceberg.
By Brolly Academy Team · Brolly Academy · Updated 24 October 2026

Table of Contents
1. External tables at a glance
2. What is an external table in Snowflake?
3. How external tables work over files in a stage
4. Before you start: prerequisites and privileges
5. How to create an external table, step by step
6. Columns: VALUE, metadata columns and virtual columns
7. Partitioning external tables
8. Refreshing: auto-refresh and manual refresh
9. Materialized views, streams and security on external tables
11. External tables vs regular tables vs Iceberg tables
12. External table vs COPY INTO vs Snowpipe vs Iceberg: which to use
13. Parquet, Delta Lake and the 2026 shift towards Iceberg
14. When to use external tables
15. Common errors and how to fix them
Quick answer
An external table in Snowflake is a read-only table that lets you query files sitting in an external stage (your Amazon S3, Azure or Google Cloud Storage location) with normal SQL, without loading those files into Snowflake. Snowflake keeps only file-level metadata, such as file names and paths, while the data stays in your bucket. You create one with CREATE EXTERNAL TABLE, point it at a stage, and keep it in sync with auto-refresh (cloud event notifications) or ALTER EXTERNAL TABLE ... REFRESH. Queries can be slower than on regular tables.
- Data location: files stay in your own S3, Azure or Google Cloud storage
- Access: read-only; you can query, join and build views, but not INSERT, UPDATE or DELETE
- Key columns: VALUE (each row as VARIANT), METADATA$FILENAME, METADATA$FILE_ROW_NUMBER
- Speed tips: partition by folder path, size files well, add a materialized view (Enterprise Edition) for hot queries
- 2026 shift: for Parquet, Snowflake recommends Iceberg tables or "Parquet Direct" (preview), and Delta Lake support on external tables is slated for deprecation
Key takeaways
- An external table is a SQL window onto files in your cloud storage; Snowflake stores only the metadata.
- Every external table has a VALUE column plus two pseudocolumns, and you add typed virtual columns on top.
- Partition columns built from the folder path are the biggest performance lever.
- Auto-refresh uses cloud event notifications and shows up as Snowpipe charges; user-specified partitions cannot auto-refresh.
- Explore with external tables, load with COPY INTO or Snowpipe for speed, and choose Iceberg tables when other engines share the data.
1. External tables at a glance
Short answer: External tables give you SQL over lake files with zero loading, in exchange for read-only access, fewer features and slower queries than regular tables.
Every line comes from Snowflake's Introduction to external tables and CREATE EXTERNAL TABLE pages (Snowflake documentation, October 2026).
2. What is an external table in Snowflake?
Short answer: It is a table definition that points at files in your cloud storage, so you can query those files as if they were rows in a Snowflake table.
Normally you load data into Snowflake first. You put files in a stage (a named pointer to a file location), then run COPY INTO to copy the rows into a regular table. Snowflake then stores that data in its own compressed columnar format, split into micro-partitions (automatic storage blocks of about 50 MB to 500 MB of uncompressed data). If you are new to Snowflake itself, read our explainer on what Snowflake does first.
An external table skips the copy. When you run a SELECT, Snowflake reads the files from your bucket at query time. According to Snowflake's documentation (October 2026), Snowflake stores file-level metadata such as file names and version identifiers, but it does not store or manage the stage or the files.
Three things follow from this design:
- No data movement. You can query a data lake on day one, without a load pipeline.
- Read-only. No INSERT, UPDATE, DELETE or MERGE; data changes only when files in the bucket change.
- Fewer features. Time Travel (querying or restoring past data), cloning and clustering keys do not apply.
You will meet them most in GCC and IT services projects where a client already has a large S3 or Azure data lake.
3. How external tables work over files in a stage
Short answer: A storage integration grants access, a stage points at the folder, the external table maps files to rows, and a refresh keeps the file list current.
Here is the end-to-end flow on AWS. Azure and Google Cloud follow the same pattern.
- Files land in cloud storage, for example s3://shopkart-raw/sales/2026/10/25/.
- A storage integration grants access. It is a Snowflake object holding the cloud identity (on AWS, an IAM role) allowed to read the bucket, so no secret keys go into SQL.
- An external stage points at the location. The stage stores the bucket URL and the integration name.
- CREATE EXTERNAL TABLE maps files to rows. You give the stage path, file format, optional virtual columns and optional partition columns.
- Snowflake registers file metadata. REFRESH_ON_CREATE defaults to TRUE, so Snowflake records file names and paths at creation.
- New files trigger notifications. With AUTO_REFRESH = TRUE, cloud events tell Snowflake that files were added, changed or removed.
- Queries read the files at run time. A virtual warehouse (Snowflake's compute cluster) scans only the files whose partitions match your WHERE clause.
- Optional speed-up. A materialized view stores the results of a frequent query inside Snowflake (Enterprise Edition).
4. Before you start: prerequisites and privileges
Short answer: You need an external stage with a storage integration, the right file format, and a role with CREATE EXTERNAL TABLE on the schema.
The stage must exist first. Our guide to stages in Snowflake covers stage types, storage integrations and file formats in depth, so here is only the checklist.
- An external stage with a URL starting s3://, azure:// or gcs://. Internal stages do not work.
- A storage integration (recommended). On AWS, Snowflake's S3 storage integration guide (October 2026) says creating one needs ACCOUNTADMIN or a role with the global CREATE INTEGRATION privilege.
- A file format set on the table itself, because external tables do not inherit FILE_FORMAT options from the stage.
- Event notifications (for auto-refresh). An SNS topic on AWS, or a notification integration for Google Cloud Pub/Sub or Azure Event Grid.
- Privileges. Per the CREATE EXTERNAL TABLE page (October 2026): CREATE EXTERNAL TABLE on the schema, CREATE STAGE on the schema if you create a new stage, USAGE on an existing stage, USAGE on the file format, plus at least one privilege on the parent database and schema.
These follow RBAC (role-based access control): privileges go to roles, and roles go to users. Usually an engineering role creates the objects and analysts get only SELECT.
5. How to create an external table, step by step
Short answer: Create the integration and stage, create the external table with typed columns and a partition, refresh once, then query with a partition filter.
Let us build a small example for a fictional Hyderabad retailer, "ShopKart". Its order files arrive daily in S3 as Parquet, in folders by date: sales/2026/10/25/orders_01.parquet. Each code block is a simplified example based on syntax from Snowflake's documentation (October 2026). Replace names, ARNs and paths with your own.
Step 1: Create the storage integration and stage
Simplified example:
CREATE STORAGE INTEGRATION shopkart_s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<account_id>:role/<role_name>'
STORAGE_ALLOWED_LOCATIONS = ('s3://shopkart-raw/sales/');
DESC INTEGRATION shopkart_s3_int;
CREATE STAGE shopkart_stage
URL = 's3://shopkart-raw/'
STORAGE_INTEGRATION = shopkart_s3_int;
DESC INTEGRATION returns the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID values that your AWS admin adds to the IAM role's trust policy. Until that is done, Snowflake cannot read the bucket.
Step 2: Check the file paths first
Before you write partition expressions, look at what METADATA$FILENAME actually returns for your files. A quick way is a throwaway external table with no columns of its own. Simplified example:
CREATE OR REPLACE EXTERNAL TABLE ext_orders_peek
LOCATION = @shopkart_stage/sales/
AUTO_REFRESH = FALSE
FILE_FORMAT = (TYPE = PARQUET);
SELECT DISTINCT METADATA$FILENAME FROM ext_orders_peek LIMIT 10;
You will see paths such as sales/2026/10/25/orders_01.parquet. Count the folder positions, because SPLIT_PART needs them in the next step. Drop the peek table when you are done.
Step 3: Create the external table
Simplified example, modelled on the et1 example in the CREATE EXTERNAL TABLE docs:
CREATE OR REPLACE EXTERNAL TABLE ext_orders (
order_date DATE AS TO_DATE(
SPLIT_PART(METADATA$FILENAME, '/', 2) || '/' ||
SPLIT_PART(METADATA$FILENAME, '/', 3) || '/' ||
SPLIT_PART(METADATA$FILENAME, '/', 4), 'YYYY/MM/DD'),
order_id VARCHAR AS (VALUE:order_id::VARCHAR),
city VARCHAR AS (VALUE:city::VARCHAR),
amount_inr NUMBER(12,2) AS (VALUE:amount::NUMBER(12,2)))
PARTITION BY (order_date)
LOCATION = @shopkart_stage/sales/
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = PARQUET)
AWS_SNS_TOPIC = 'arn:aws:sns:<region>:<account_id>:<topic>';
What each part does:
- order_date is a partition column from the folder path; positions 2, 3 and 4 match sales/2026/10/25/, so adjust them to your layout.
- order_id, city, amount_inr are virtual columns that pull typed fields out of VALUE.
- AUTO_REFRESH with AWS_SNS_TOPIC keeps the file list current on S3. On Azure, use INTEGRATION instead; on Google Cloud, omit AWS_SNS_TOPIC.
Step 4: Refresh once and query
Simplified example:
ALTER EXTERNAL TABLE ext_orders REFRESH;
SELECT city, SUM(amount_inr) AS revenue_inr
FROM ext_orders
WHERE order_date = '2026-10-25'
GROUP BY city;
One manual REFRESH registers files already in the location; auto-refresh handles new ones. The order_date filter makes Snowflake read only that day's folder.
6. Columns: VALUE, metadata columns and virtual columns
Short answer: Every external table has a VARIANT column called VALUE, two metadata pseudocolumns, and any typed virtual columns you define on top.
A few practical notes from Snowflake's documentation (October 2026):
- SELECT * returns only VALUE. Query your virtual columns by name instead.
- VARIANT is Snowflake's data type for semi-structured data such as JSON. You extract fields with the colon notation, for example VALUE:city::VARCHAR. Our post on Snowflake data types explains VARIANT and casting.
- Types must match at scan time. If a virtual column says NUMBER but a file holds text there, the query can fail.
- METADATA$FILENAME is your friend for debugging. When a total looks wrong, group by METADATA$FILENAME to find the bad file.
- Constraints are not enforced, including NOT NULL.
7. Partitioning external tables
Short answer: Partition external tables by columns derived from the folder path, so Snowflake reads only the files your query needs.
Snowflake's documentation (October 2026) strongly recommends partitioning external tables. Without partitions, every query scans every file in the location. With partitions, a filter such as WHERE order_date = '2026-10-25' skips all other folders.
You choose the partitioning method when you create the table, and you cannot change it later. There are two methods.
A user-specified partition is added like this. Simplified example based on the docs et2 example:
ALTER EXTERNAL TABLE ext_orders_manual
ADD PARTITION (order_date = '2026-10-25', city = 'Hyderabad')
LOCATION '2026/10/25/hyderabad';
Here, partition columns are defined from METADATA$EXTERNAL_TABLE_PARTITION, as in the docs example, and adding a partition registers its files.
Good partitioning habits:
- Design the folder layout first. A path like sales/year/month/day/ makes date partitions simple to define.
- Partition on what people filter by. Date is the most common. Add a second column, such as region or source system, only if queries filter on it often.
- Mind the case. Per the docs, partition column names in expressions are case-sensitive; unquoted names must be uppercase in those expressions.
8. Refreshing: auto-refresh and manual refresh
Short answer: Auto-refresh uses cloud event notifications to keep the file list current; manual refresh runs ALTER EXTERNAL TABLE ... REFRESH when you choose.
An external table only sees files registered in its metadata. If a new file lands and nothing refreshes the table, queries will not see it.
Automatic refresh
- On by default. AUTO_REFRESH defaults to TRUE, but it only works once event notifications are set up for your storage.
- By cloud: Amazon S3 uses SNS (topic ARN in AWS_SNS_TOPIC); Google Cloud uses Pub/Sub and Azure uses Event Grid, each through a notification integration named in INTEGRATION.
- What it handles: new files are added, changed files are updated and deleted files are removed from the metadata.
- Where it does not work: with PARTITION_TYPE = USER_SPECIFIED, with S3-compatible storage, or with Delta Lake tables (TABLE_FORMAT = DELTA), per the docs (October 2026).
Manual refresh
- Command: ALTER EXTERNAL TABLE ext_orders REFRESH; registers changes in the whole location. You can also refresh a sub-path.
- When to use it: once after creating the table, on a schedule when notifications are not possible, and always for Delta Lake external tables.
- Very large locations: for locations with roughly 1 million or more files, the docs recommend REFRESH_ON_CREATE = FALSE and incremental refreshes by sub-path.
- Scheduling: you can run a manual refresh from a task (Snowflake's built-in scheduler for SQL and stored procedures). Our streams and tasks guide shows how tasks work.
Removing old files from metadata
Snowflake's intro page (October 2026) shows a stored procedure that runs ALTER EXTERNAL TABLE ... REMOVE FILES for files older than a set number of days, scheduled with a task, to keep metadata lean.
Checking refresh history
Use the Information Schema table functions EXTERNAL_TABLE_FILES, EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY and AUTO_REFRESH_REGISTRATION_HISTORY (registration history and credits) when a file "should be there" but is not.
9. Materialized views, streams and security on external tables
Short answer: You can speed up hot queries with materialized views (Enterprise Edition), track new rows with insert-only streams, and protect data with masking and row access policies.
Materialized views
A materialized view stores a query result inside Snowflake and keeps it updated, turning slow file scans into fast reads for your most frequent queries. Per Snowflake's documentation (October 2026), materialized views on external tables need Enterprise Edition, and you should keep the external table metadata refreshed so the view reflects the current files. The docs example is as short as CREATE MATERIALIZED VIEW et1_mv AS SELECT col2 FROM et1;.
Streams on external tables
A stream records changes to a table so a downstream job can process only new data. Snowflake's CREATE STREAM page (October 2026) says INSERT_ONLY = TRUE is required for streams on external tables. These insert-only streams track new rows from newly registered files, not deletes. Simplified example from that page:
CREATE STREAM ext_orders_stream
ON EXTERNAL TABLE ext_orders
INSERT_ONLY = TRUE;
A common pattern: a task checks the stream and merges new rows into a regular table. One caution: Snowflake's streams overview page says streams are not supported on partitioned external tables, while the CREATE STREAM example uses a partitioned table. Test on your own account before you design around it.
Security
- Masking policies hide column values, such as phone numbers, based on the querying role.
- Row access policies filter rows by role, for example showing a Pune analyst only Pune orders (WITH ROW ACCESS POLICY ... ON (VALUE)).
- Bucket permissions still matter. Anyone with direct bucket access can read the raw files, so lock the bucket down with your cloud provider's controls too.
10. Performance and cost
Short answer: External tables cost little to store in Snowflake but can cost more compute per query; partitions, good file sizes and materialized views keep both under control.
Performance
Snowflake's documentation (October 2026) says queries on external tables can be slower than on native tables, because they read raw files from your bucket instead of Snowflake's optimised micro-partitions.
How to make them faster:
- Filter on partition columns in every frequent query.
- Size files sensibly. The docs recommend Parquet files of 256 MB to 512 MB with row groups of 16 MB to 256 MB, and 16 MB to 256 MB files for other formats. Thousands of tiny files slow everything down.
- Use simple filters on Parquet. The docs note that Parquet filter pruning works with conditions such as =, >, < and STARTSWITH, and not with VARIANT columns. Queries in the form value:<path>::<type> use the vectorized scanner.
- Remember the result cache. Query results persist for 24 hours, but file changes or DDL changes invalidate them. If auto-refresh is broken, results can also be stale.
Cost
External tables have three cost parts. Prices vary by cloud, region and edition, so treat the figures below as context, not a quote.
A credit is Snowflake's unit of compute. An X-Small warehouse uses 1 credit per hour, and each size up doubles that, billed per second with a 60-second minimum each time the warehouse starts (Snowflake documentation, October 2026). The Service Consumption Table effective 15 October 2026 lists on-demand prices in AWS US East of $2, $3, $4 and $6 per credit for Standard, Enterprise, Business Critical and Virtual Private Snowflake. Snowpipe is Snowflake's serverless service for loading files as they arrive, which is why notification costs appear under its name.
Cost tips:
- Avoid auto-refresh over huge numbers of tiny files, since notification overhead grows with file count.
- Use REMOVE FILES or narrower LOCATION paths so metadata covers only what you query.
- If the same data is queried all day, loading it into a regular table is often cheaper than scanning files every time.
11. External tables vs regular tables vs Iceberg tables
Short answer: Regular tables give full features in Snowflake storage, external tables give read-only SQL over your files, and Iceberg tables give open-format data in your storage with much fuller Snowflake support.
Apache Iceberg is an open table format that stores data as Parquet files plus metadata, so several engines (Snowflake, Spark, Trino and others) can work on the same tables. Snowflake's Iceberg tables documentation (October 2026) describes two catalog options: Snowflake as the catalog (Snowflake-managed, read and write, with Snowflake doing maintenance such as compaction) or an external catalog through a catalog integration (limited support).
Regular tables also get Time Travel (up to 1 day on Standard Edition, up to 90 days on Enterprise and above), zero-copy cloning and clustering keys, none of which external tables support. Fail-safe is a 7-day period after Time Travel ends during which only Snowflake Support can attempt recovery. See our Snowflake Time Travel guide for more.
The practical point: Snowflake's docs suggest Iceberg for data lakes kept outside Snowflake storage or shared with other engines. External tables remain useful for quick, read-only access to CSV, JSON and other formats, while Iceberg tables store their data as Parquet.
12. External table vs COPY INTO vs Snowpipe vs Iceberg: which to use
Short answer: Use an external table to query files in place, COPY INTO for batch loading, Snowpipe for continuous loading, and Iceberg tables for open-format data that several engines share.
Bookmark this table. Our posts on stages, COPY INTO and Snowpipe all point back to it.
Quick rules of thumb:
- Exploring a new data source? Start with an external table; there is nothing to load.
- Same data queried all day by dashboards? Load it with COPY INTO or Snowpipe into regular tables.
- Parquet lake that Databricks or Spark also uses? Look at Iceberg tables first. Our Snowflake vs Databricks comparison explains how the two platforms share open formats.
- Need both? Keep raw history in an external table and load recent data into a regular table with a stream and task.
13. Parquet, Delta Lake and the 2026 shift towards Iceberg
Short answer: In 2026, Snowflake recommends Iceberg tables or Parquet Direct (preview) for Parquet data, and plans to deprecate Delta Lake support on external tables.
Older tutorials miss this. Snowflake's external tables introduction (October 2026) says:
- For Parquet: it points to Apache Iceberg tables for performance and recommends Parquet Direct, a preview feature that creates a read-only Iceberg table over your Parquet files in place, with schema inference and event-driven refresh.
- For Delta Lake: external tables can read Delta Lake transaction logs with TABLE_FORMAT = DELTA (a preview feature), but the page says this "will be deprecated in a future release". Deletion vectors and auto-refresh are not supported, so you must refresh periodically.
- Delta migration: create an external volume and catalog integration, run CREATE ICEBERG TABLE with a BASE_LOCATION, then drop the external table.
Parquet Direct is in preview, so do not treat it as generally available. As a learner, still know external tables well, because many existing projects use them, but be ready to explain why Iceberg may suit new Parquet or Delta designs in 2026.
14. When to use external tables
Short answer: Use external tables for data lake exploration, cold or archived data, light reporting on raw files, and pipelines that must not copy data.
Typical scenarios in Indian IT services and GCC projects:
- Data lake discovery. A Bengaluru GCC team profiles years of S3 transaction files with plain SQL before planning a migration.
- Cold history. A Hyderabad retail client keeps recent months in regular tables and older years as Parquet in S3, queried occasionally through an external table.
- Landing-zone checks. A Pune team validates row counts and file names in the raw folder before COPY INTO runs.
- Data that must stay put. When raw data must stay in the client's own cloud account, external tables query it without copying it.
- dbt sources. If your team uses dbt (data build tool, a framework for SQL transformations), an external table can act as a source that your dbt models read like any other table.
When not to use them:
- High-concurrency dashboards that need sub-second answers
- Data that needs updates, deletes, Time Travel or cloning
- Large Parquet or Delta lakes for new projects, where Iceberg tables are Snowflake's current recommendation
15. Common errors and how to fix them
Short answer: Most external table problems come from missing refreshes, wrong partition expressions, type mismatches or access setup.
16. Where external tables show up in jobs and interviews
Short answer: External tables are a named skill in some Snowflake developer job descriptions and a favourite interview topic, usually alongside stages, Snowpipe and streams.
Among 14 Snowflake job descriptions we read on 25 October 2026, a DTCC "Software Engineer - Snowflake Developer" listing in Hyderabad (LinkedIn, October 2026) named external tables with procedures, UDFs, streams and Snowpipe. SQL appeared in all 14.
Expect interview prompts like "Your external table is missing yesterday's files. What do you check?" (refresh, notifications, LOCATION path, PATTERN) or "External table or Iceberg table for a new Parquet lake in 2026?". For more practice, see our Snowflake scenario-based interview questions.
17. How Brolly Academy helps
Short answer: Brolly Academy's 45-day Snowflake course builds the groundwork for external tables: external stages, loading from AWS, Azure and Google Cloud, semi-structured data, scheduling and performance.
Our Snowflake Training in Hyderabad runs for 45 days and is taught by Mr. Bala Krishna, who has 13+ years in data warehousing, SQL and Snowflake. Here is how the course connects to this topic:
- 15 modules, including data loading and unloading with file formats, internal and external stages and COPY INTO; loading from AWS, Azure and Google Cloud storage; Snowpipe; JSON and semi-structured data with VARIANT; scheduling with tasks and streams; and query caching, clustering and performance
- 6 projects, including an ETL pipeline and an e-commerce analytics project, where you practise working with files in cloud storage
- Three ways to learn: Video ₹8,999, Online ₹17,999 and Classroom ₹22,999, with EMI available
- Brolly completion certificate, plus guidance for SnowPro exams (exam fees are paid separately to Snowflake)
- Free demo class before you enrol, and weekday, weekend, morning and evening batches
Want to build external tables, stages and pipelines on a real Snowflake account?
Join a free demo class of Brolly Academy's 45-day Snowflake course in Hyderabad or online. You will see how external stages, file formats, COPY INTO, Snowpipe and semi-structured data fit together, and how to explain them clearly in interviews.
Frequently asked questions
What is an external table in Snowflake?
An external table in Snowflake is a read-only table that lets you query files stored in an external stage, such as Amazon S3, Azure or Google Cloud Storage, without loading them. Snowflake keeps only file-level metadata like file names and paths, while the data stays in your bucket. You can select, join and build views on it, but you cannot insert, update or delete rows.
Where is external table data stored?
External table data stays in your own cloud storage location, such as an S3 bucket, an Azure container or a Google Cloud Storage bucket, which an external stage points to. Snowflake stores only metadata about the files, such as their names, paths and partition values. Your cloud provider bills you for storing the files, and Snowflake bills compute when you query them.
How do I create an external table in Snowflake?
First create a storage integration and an external stage that points at your bucket. Then run CREATE EXTERNAL TABLE with a LOCATION on that stage, a FILE_FORMAT, optional virtual columns and an optional PARTITION BY clause. Run ALTER EXTERNAL TABLE with REFRESH once to register existing files, and set up event notifications if you want automatic refresh after that.
What is the difference between an external table and a regular table?
A regular table stores data inside Snowflake's managed storage and supports DML, Time Travel, cloning and clustering keys. An external table reads files from your cloud bucket at query time, is read-only, and does not support Time Travel, cloning or clustering keys. Regular tables are faster for heavy analytics, while external tables avoid loading data at all.
What is the difference between an external table and an Iceberg table?
An external table is a read-only layer over raw files in formats like CSV, JSON or Parquet. A Snowflake-managed Iceberg table stores Parquet data with Iceberg metadata, supports reads and writes, and lets other engines read it through the Horizon Iceberg REST Catalog API. In 2026 Snowflake recommends Iceberg tables over external tables for Parquet data.
What is the VALUE column in an external table?
VALUE is a VARIANT column that every external table has, and it holds one full row of the source file as semi-structured data. When you run SELECT star on an external table, you get only this column. You usually define typed virtual columns that pull fields out of VALUE, for example VALUE colon city cast to VARCHAR, so queries are easier to write.
What are METADATA$FILENAME and METADATA$FILE_ROW_NUMBER?
They are pseudocolumns that every external table provides. METADATA$FILENAME gives the name and stage path of the file each row came from, and METADATA$FILE_ROW_NUMBER gives the row's position inside that file. Partition columns are usually built from METADATA$FILENAME, and both columns are very useful for debugging which file caused a wrong total.
Can I partition external tables in Snowflake?
Yes, and Snowflake strongly recommends it. With automatic partitioning, partition columns are expressions on METADATA$FILENAME, usually date folders in the path, and they are added on refresh. With PARTITION_TYPE set to USER_SPECIFIED, you add partitions yourself using ALTER EXTERNAL TABLE with ADD PARTITION. You choose the method at creation and cannot change it later.
How does auto-refresh work for external tables?
Auto-refresh uses cloud event notifications to tell Snowflake when files are added, changed or removed. On Amazon S3 it uses SNS with the topic ARN in AWS_SNS_TOPIC, on Google Cloud it uses Pub/Sub, and on Azure it uses Event Grid through a notification integration. AUTO_REFRESH defaults to TRUE, but it only works once those notifications are set up.
Why are new files not showing in my external table?
Usually the table's metadata has not been refreshed. Either auto-refresh notifications are not configured correctly, or you never ran the first manual refresh after creating the table. Run ALTER EXTERNAL TABLE with REFRESH, then check your SNS, Pub/Sub or Event Grid setup. Also confirm the LOCATION path and any PATTERN actually match the new files.
Are external tables slower than regular tables?
Yes, Snowflake's documentation says queries on external tables can be slower than on native tables. They read raw files from your bucket at query time instead of Snowflake's optimised micro-partitions. You can narrow the gap with partition filters, well-sized Parquet files, simple filter conditions and materialized views on Enterprise Edition, or by loading frequently queried data.
Can I create a materialized view on an external table?
Yes. Materialized views on external tables are supported on Enterprise Edition and above. The view stores the query result inside Snowflake, so frequent dashboards read fast Snowflake-managed data instead of scanning files. Keep the external table metadata refreshed, through auto-refresh or manual refresh, so the materialized view reflects the current set of files in the bucket.
Do external tables cost storage in Snowflake?
Snowflake does not charge storage for the files behind an external table, because they stay in your bucket and your cloud provider bills that storage. You still pay Snowflake for warehouse compute when you query, for auto-refresh notifications, which appear as Snowpipe charges and grow with file count, and a small cloud services charge for manual refreshes.
Can external tables read Parquet and Delta Lake files?
Yes. External tables read CSV, JSON, Avro, ORC and Parquet, but not XML. They can also read Delta Lake tables with TABLE_FORMAT set to DELTA, a preview feature that Snowflake says will be deprecated in a future release. For Parquet, Snowflake now recommends Iceberg tables or Parquet Direct, which is in preview, for better performance.
When should I use an external table instead of COPY INTO?
Use an external table when you want to query files in place, explore a new data source, read cold history occasionally, or keep data in the client's own bucket. Use COPY INTO when the same data is queried often and you want full speed, DML, Time Travel and cloning. Many teams explore with an external table first and load later.
What is the difference between an external table and Snowpipe?
An external table queries files where they sit and never copies the data. Snowpipe is a serverless service that automatically loads files into a regular Snowflake table as they land in cloud storage. Both can use cloud event notifications, which is why external table auto-refresh costs appear as Snowpipe charges, but only Snowpipe moves data into Snowflake storage.
What is the difference between an external stage and an external table?
An external stage is a named pointer to a cloud storage location, holding the URL and the storage integration for access. An external table is a queryable table built on top of a stage, with columns, partitions and metadata about each file. You need the stage first, because the external table's LOCATION must point at an external stage path.
Can I use an internal stage for an external table?
No. External tables in Snowflake work only with external stages on Amazon S3, Microsoft Azure or Google Cloud Storage. User stages, table stages and named internal stages are not supported. If your files are in an internal stage, load them into a regular table with COPY INTO, or move them to cloud storage and create an external stage over that location.
Do external tables support Time Travel and cloning?
No. Time Travel, cloning and clustering keys are not supported on external tables, because Snowflake does not manage the underlying files. Storage versioning on S3, Google Cloud or Azure is also not supported. If you need to recover or clone data, load it into regular tables, which support Time Travel on every edition and zero-copy cloning.
Can I create a stream on an external table?
Yes. Snowflake supports insert-only streams on external tables, created with CREATE STREAM on the external table and INSERT_ONLY set to TRUE, which the docs say is required. These streams track new rows from newly registered files but not deletes. Snowflake's pages differ on partitioned external tables, so test this on your own account before designing around it.
How do I secure an external table?
Use masking policies to hide sensitive column values and row access policies to filter rows by role, both of which external tables support. Grant SELECT only to the roles that need it, following role-based access control. Also lock down the bucket itself with your cloud provider's permissions, because anyone with direct bucket access can still read the raw files.
What file size works best for external tables?
Snowflake's documentation recommends Parquet files of 256 to 512 MB with row groups of 16 to 256 MB, and 16 to 256 MB files for other formats. Thousands of tiny files slow queries, and auto-refresh notification costs grow with the number of files. Combine good file sizes with a folder layout that supports date partitions.
Can I use external tables with dbt?
Yes. If your team uses dbt, the data build tool for SQL transformations, an external table can serve as a source that your dbt models read from like any other table. A common design keeps raw history in an external table and loads the data that dashboards query often into regular tables, where it runs faster.
Are external tables still worth learning in 2026?
Yes. Many existing Snowflake projects use external tables, and a Hyderabad Snowflake developer job description we read in October 2026 named them along with streams and Snowpipe. You should also learn why Snowflake now recommends Iceberg tables or Parquet Direct for Parquet data, so you can explain the trade-off in interviews and choose the right option on new projects.
Does Brolly Academy's Snowflake course cover the skills behind external tables?
Brolly Academy's 45-day Snowflake course covers the groundwork: internal and external stages, file formats, COPY INTO, loading from AWS, Azure and Google Cloud storage, Snowpipe, JSON with VARIANT, tasks and streams, and performance. Fees are ₹8,999 for video, ₹17,999 online and ₹22,999 classroom, and you can attend a free demo class before enrolling.
Learn with Brolly Academy
Review the syllabus, practical projects and batch timings, and book a free demo before you enrol.
Related Articles
Continue Your Learning
Snowflake Training in Hyderabad
45-day course covering Snowflake architecture, warehouses, data loading, security, performance and real-time projects, in classroom, live online and recorded plans.
View Coursedbt Course
Learn dbt, the transformation tool most Snowflake job descriptions ask for, to build tested, documented data models.
View CourseDatabricks Course
Add the lakehouse side of modern data engineering with Databricks, Spark and Delta Lake, often used alongside Snowflake.
View Course