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
In a nutshell
  • External tables: read only, VALUE variant 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.

Filescloud storageexternal tableRead only VALUE columnParquet filesIceberg metadataIceberg tableSnowflake and other enginesRow storeprimary keyhybrid tableFast point reads and writes

How it works

External tables

An external table reads files in a stage location and exposes them without loading.

  • Every external table has a VALUE column (VARIANT, one row per file record) and the pseudocolumns METADATA$FILENAME and METADATA$FILE_ROW_NUMBER. You add virtual columns as expressions over VALUE.
  • They are read only. DML is not supported.
  • Refresh: AUTO_REFRESH uses cloud storage event notifications to update metadata, and ALTER EXTERNAL TABLE ... REFRESH does it manually. Delta Lake external tables do not support automatic refresh.
  • Partitioning with PARTITION BY is 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.
Exam tip

For Parquet data in the lake, Snowflake's documentation points to Iceberg tables for better query performance than external tables.

Iceberg tables

ChoiceSnowflake as the catalog (Snowflake-managed)External catalog (externally managed)
Access from SnowflakeRead and write, full platform supportRead and write, more limited platform support
Table lifecycleSnowflake manages it, including compaction (can be disabled)The external catalog manages it
Created withCATALOG = 'SNOWFLAKE'A catalog integration and CATALOG_TABLE_NAME
Storage locationWho manages it
Snowflake storageSnowflake, 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 NULL is enforced, and CHECK constraints 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.
WorkloadTable
Point lookups and many small concurrent writesHybrid
Large scans and aggregationsStandard
Open format shared with other enginesIceberg
Query files you do not want to load, read onlyExternal (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');
Remote Iceberg REST catalogcatalog integrationCatalog linked databasepolls every 30 s defaultSchemas and Iceberg tablesauto discovered
  • Snowflake polls the remote catalog every SYNC_INTERVAL_SECONDS (default 30 seconds). Namespaces appear as schemas.
  • ALLOWED_NAMESPACES and BLOCKED_NAMESPACES filter 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 with SYSTEM$GET_CATALOG_LINKED_DATABASE_CONFIG.
  • Not synced or supported: access control, UNDROP, replication and cloning.
  • A remote drop removes the table in Snowflake.
Watch out

Roles and grants from the remote catalog do not come across. Apply Snowflake access control to the linked database yourself.

Common mistakes

  • Trying to INSERT into 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, VALUE variant 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.