Data and Object Management · Topic 4 of 11
External Tables and Iceberg Tables
Create and manage external tables and Apache Iceberg tables: read-only files in a stage, Snowflake-managed versus externally managed catalogs and billing.
- 4 min
- External tables are read-only and expose
VALUE,METADATA$FILENAMEandMETADATA$FILE_ROW_NUMBER. - Refresh is event driven or manual; user-specified partitions do not auto-refresh.
- Iceberg: Snowflake as catalog gives full read and write; an external catalog needs a catalog integration and gives more limited support.
What it is
Both table types let Snowflake query data that lives in your cloud storage. They differ in how much Snowflake manages.
An external table is a read-only table over files in an external stage. Snowflake does not store or manage the files. An Apache Iceberg table uses the open Iceberg format, with Parquet data files in storage you own, and a catalog that tracks table metadata.
How it works
External tables
An external table reads files through a stage. Any format that COPY INTO <table> supports can be used, except XML.
Every external table has these columns:
VALUE: aVARIANTwith one row per record.METADATA$FILENAMEandMETADATA$FILE_ROW_NUMBER: pseudo columns about the source file.
You can add virtual columns as expressions over VALUE. They are computed when you query.
CREATE EXTERNAL TABLE ext_sales (
sale_date DATE AS (VALUE:c1::DATE),
amount NUMBER AS (VALUE:c2::NUMBER)
)
LOCATION = @lake_stage/sales/
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = PARQUET);
Key facts to remember:
| Topic | Fact |
|---|---|
| DML | None. External tables are read-only, but you can query, join and build views on them |
| Metadata refresh | Automatic through cloud event notifications, or manual with ALTER EXTERNAL TABLE ... REFRESH |
| Partitioning | Strongly recommended. Automatic partitions are expressions over METADATA$FILENAME; with PARTITION_TYPE = USER_SPECIFIED you add partitions yourself and auto-refresh is not supported |
| Speed | Generally slower than native tables |
| Speeding up | A materialized view on an external table (Enterprise Edition or higher) |
| Security | Masking and row access policies can protect them |
| Cost | Auto-refresh uses Snowpipe style charges; manual refresh uses cloud services |
You cannot drop VALUE or the metadata pseudo columns, and the partition type cannot be changed after creation. A file that fails during a scan is skipped and the query continues.
When a scenario needs fast queries on a data lake in Parquet, the docs lean toward Iceberg tables rather than external tables.
Iceberg tables
Iceberg tables store data as Parquet files with Iceberg metadata. They need an external volume (the storage link, covered in a later page) unless Snowflake-managed storage is used. The key decision is who owns the catalog.
| Snowflake as catalog | External catalog | |
|---|---|---|
| Who tracks metadata | Snowflake | AWS Glue, Snowflake Open Catalog, an Iceberg REST catalog or object storage metadata, through a catalog integration |
| Platform support | Full, read and write | More limited |
| Lifecycle | Snowflake handles it, including compaction (which can be disabled) | Not managed by Snowflake |
| Typical use | Snowflake is the main engine and other engines read | Another engine is the writer of record and Snowflake queries it |
-- Snowflake as the catalog
CREATE ICEBERG TABLE orders_ice (id INT, total NUMBER(10,2))
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'lake_vol'
BASE_LOCATION = 'orders/';
What an administrator should know:
- Snowflake-managed Iceberg tables support read and write, including
DELETE. - Externally managed tables can be written through DDL and DML for Iceberg v2 and v3, with limits: only autocommit transactions, and equality deletes are not supported.
- External catalog tables stay in sync through automated refresh. Schema changes made by DDL alone need a manual refresh.
- The storage location of data files cannot be changed with
ALTER ICEBERG TABLE; you recreate the table. - Billing: Snowflake bills compute and cloud services. With an external volume, your cloud provider bills the storage.
- Fail-safe is not supported for customer-managed external volumes. Cloning is not supported for externally managed tables.
- Some tables from an external catalog can be converted to Snowflake-managed tables for full platform support; check the conversion limits for your catalog.
Choosing between them
| Need | Choose |
|---|---|
| Quick read access to files, no write | External table |
| Open format shared with Spark or Trino, with Snowflake performance | Iceberg table |
| Snowflake must write and manage the data | Iceberg with Snowflake as catalog |
| Another engine owns the table | Iceberg with an external catalog |
Common mistakes
Trying to insert into an external table. It is read-only; load data into a native or Iceberg table.
Forgetting to refresh. Without event notifications or a manual REFRESH, new files do not appear.
Skipping partitioning on a large external table. Every query then scans all files.
Assuming Fail-safe covers Iceberg data in your bucket. With a customer-managed external volume it does not.
Mixing up who pays for storage. With an external volume your cloud account pays, not Snowflake.
What to remember for the exam
- External tables are read-only and expose
VALUE,METADATA$FILENAMEandMETADATA$FILE_ROW_NUMBER. - Refresh is event driven or manual; user-specified partitions do not auto-refresh.
- Iceberg: Snowflake as catalog gives full read and write; an external catalog needs a catalog integration and gives more limited support.
- Iceberg storage in your bucket is billed by your cloud provider and has no Fail-safe.
Check yourself
Why can a user not run
INSERTon an external table?External tables are read-only; DML is not supported.
Which Iceberg setup gives full Snowflake platform support?
Snowflake as the catalog, which gives read and write access and lifecycle maintenance.
Who bills the storage of an Iceberg table that uses an external volume?
Your cloud provider, because the files sit in your own storage.
Topic 4 of 11 in Data and Object Management
Test yourself on Data and Object Management
The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.