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
In a nutshell
  • External tables are read-only and expose VALUE, METADATA$FILENAME and METADATA$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.

Files in cloud storageExternal stageExternal tableread only, schema on readParquet and metadata in your storageExternal volumeIceberg tablecatalog tracks 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: a VARIANT with one row per record.
  • METADATA$FILENAME and METADATA$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:

TopicFact
DMLNone. External tables are read-only, but you can query, join and build views on them
Metadata refreshAutomatic through cloud event notifications, or manual with ALTER EXTERNAL TABLE ... REFRESH
PartitioningStrongly recommended. Automatic partitions are expressions over METADATA$FILENAME; with PARTITION_TYPE = USER_SPECIFIED you add partitions yourself and auto-refresh is not supported
SpeedGenerally slower than native tables
Speeding upA materialized view on an external table (Enterprise Edition or higher)
SecurityMasking and row access policies can protect them
CostAuto-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.

Exam tip

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 catalogExternal catalog
Who tracks metadataSnowflakeAWS Glue, Snowflake Open Catalog, an Iceberg REST catalog or object storage metadata, through a catalog integration
Platform supportFull, read and writeMore limited
LifecycleSnowflake handles it, including compaction (which can be disabled)Not managed by Snowflake
Typical useSnowflake is the main engine and other engines readAnother 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

NeedChoose
Quick read access to files, no writeExternal table
Open format shared with Spark or Trino, with Snowflake performanceIceberg table
Snowflake must write and manage the dataIceberg with Snowflake as catalog
Another engine owns the tableIceberg 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$FILENAME and METADATA$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 INSERT on 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.