Data Movement · Topic 16 of 17
External, Iceberg and Hybrid Tables with Horizon Federation
Compare external, Iceberg and hybrid tables, pick catalogs and external volumes, and federate remote Iceberg catalogs with catalog-linked databases.
- 5 min
- External tables: read only,
VALUEvariant column,METADATA$FILENAME,AUTO_REFRESH, partition at creation, slower than native. - Iceberg: Snowflake-managed catalog (read and write, Snowflake maintains) or external catalog (needs a catalog integration). External volume means you own protection and recovery.
- Horizon Iceberg REST Catalog lets other engines read and write Snowflake-managed Iceberg tables.
What it is
Three table types keep data in a different place or shape than a standard table.
- External table: a read only table over files in external cloud storage.
- Iceberg table: a table in the open Apache Iceberg format, with Parquet data files, that other engines can also read.
- Hybrid table: a table with a row based store for fast single row reads and writes, used for transactional workloads.
Horizon Catalog federation brings tables from other Iceberg catalogs into Snowflake without registering each table by hand.
How it works
External tables
An external table reads files in a stage location and exposes them without loading.
- Every external table has a
VALUEcolumn (VARIANT, one row per file record) and the pseudocolumnsMETADATA$FILENAMEandMETADATA$FILE_ROW_NUMBER. You add virtual columns as expressions overVALUE. - They are read only. DML is not supported.
- Refresh:
AUTO_REFRESHuses cloud storage event notifications to update metadata, andALTER EXTERNAL TABLE ... REFRESHdoes it manually. Delta Lake external tables do not support automatic refresh. - Partitioning with
PARTITION BYis strongly recommended and cannot be changed after creation. User defined partitions do not support automatic refresh. - Queries are generally slower than on native tables. A materialized view over an external table (Enterprise Edition) speeds up frequent queries.
- Recommended file sizes are 256 to 512 MB for Parquet and 16 to 256 MB for other formats.
For Parquet data in the lake, Snowflake's documentation points to Iceberg tables for better query performance than external tables.
Iceberg tables
| Choice | Snowflake as the catalog (Snowflake-managed) | External catalog (externally managed) |
|---|---|---|
| Access from Snowflake | Read and write, full platform support | Read and write, more limited platform support |
| Table lifecycle | Snowflake manages it, including compaction (can be disabled) | The external catalog manages it |
| Created with | CATALOG = 'SNOWFLAKE' | A catalog integration and CATALOG_TABLE_NAME |
| Storage location | Who manages it |
|---|---|
| Snowflake storage | Snowflake, billed to you, Fail-safe protects permanent tables |
| External volume (your S3, GCS or Azure storage) | You, with no Fail-safe from Snowflake and storage billed by your cloud provider |
CREATE ICEBERG TABLE orders_ice (id INT, amount NUMBER(10,2))
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'lake_vol'
BASE_LOCATION = 'orders/';
- An external volume holds the access configuration to your cloud storage. A catalog integration holds the connection to an external catalog.
- For an externally managed table, refresh the table so Snowflake picks up new metadata snapshots, either automatically or with
ALTER ICEBERG TABLE ... REFRESH. - Other engines can read and write Snowflake-managed Iceberg tables through the Horizon Iceberg REST Catalog API. Support for externally managed tables through this API is limited, so check the documentation. Equality deletes are not supported for writes.
- Cross region and cross cloud use is supported, and egress costs apply.
- Iceberg v2 and v3 tables are supported for reading and writing, with Parquet data files.
Hybrid tables
A hybrid table keeps data in a row store for low latency, high throughput, index based reads and writes, and asynchronously copies it to object storage.
- A primary key is required and enforced.
- Primary key, foreign key and unique constraints are enforced. You cannot declare them
NOT ENFORCED.NOT NULLis enforced, andCHECKconstraints are enforced but can be defined only when the table is created. - Row level locking supports high concurrency, unlike partition or table level locking.
- Indexes are updated synchronously on writes.
- Hybrid and standard tables can be joined and updated in one atomic transaction.
- Storage footprint is typically larger than for standard tables.
- Generally available on AWS and Azure commercial regions, with Google Cloud commercial regions in preview.
| Workload | Table |
|---|---|
| Point lookups and many small concurrent writes | Hybrid |
| Large scans and aggregations | Standard |
| Open format shared with other engines | Iceberg |
| Query files you do not want to load, read only | External (or Iceberg) |
Federating catalogs with Horizon
A catalog-linked database connects a Snowflake database to an external Iceberg REST catalog, such as Snowflake Open Catalog, so you do not create an externally managed table for each remote table.
CREATE DATABASE lake_db
LINKED_CATALOG = (CATALOG = 'rest_catalog_int');
- Snowflake polls the remote catalog every
SYNC_INTERVAL_SECONDS(default 30 seconds). Namespaces appear as schemas. ALLOWED_NAMESPACESandBLOCKED_NAMESPACESfilter what is discovered.- Writes are supported for namespaces, externally managed Iceberg tables and database roles.
- Check sync with
SYSTEM$CATALOG_LINK_STATUS, and see the setup withSYSTEM$GET_CATALOG_LINKED_DATABASE_CONFIG. - Not synced or supported: access control,
UNDROP, replication and cloning. - A remote drop removes the table in Snowflake.
Roles and grants from the remote catalog do not come across. Apply Snowflake access control to the linked database yourself.
Common mistakes
- Trying to
INSERTinto an external table. It is read only. - Leaving out partitions on a large external table.
- Assuming Fail-safe protects Iceberg tables stored in your external volume. It does not.
- Expecting an external engine to write to an externally managed Iceberg table through the Horizon REST API.
- Declaring a hybrid table without a primary key, or setting constraints to
NOT ENFORCED. - Using a hybrid table for large analytical scans.
- Assuming a catalog-linked database copies permissions from the remote catalog.
What to remember for the exam
- External tables: read only,
VALUEvariant column,METADATA$FILENAME,AUTO_REFRESH, partition at creation, slower than native. - Iceberg: Snowflake-managed catalog (read and write, Snowflake maintains) or external catalog (needs a catalog integration). External volume means you own protection and recovery.
- Horizon Iceberg REST Catalog lets other engines read and write Snowflake-managed Iceberg tables.
- Hybrid tables: primary key required, enforced constraints, row store, row level locks, for transactional point access.
- Catalog-linked databases auto discover remote tables (default 30 second sync) but do not sync access control.
Check yourself
Which table type needs a primary key and uses row level locking?
A hybrid table.
Why is no Fail-safe available for an Iceberg table on an external volume?
Because you manage the files in your own cloud storage, so protection and recovery are your responsibility.
What does a catalog-linked database do that a single externally managed Iceberg table does not?
It auto discovers every namespace and table in the remote Iceberg REST catalog and keeps them synced.
Topic 16 of 17 in Data Movement
Test yourself on Data Movement
The full Snowflake DEA-C02 question bank has 709 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.