Snowflake Table Types: External, Iceberg, Hybrid Tables, Schema Evolution and Unloading

1.External tables: query files in place, read-only

An external table lets you query files in an external stage as if they were a Snowflake table. Snowflake stores only file-level metadata, such as filenames and version identifiers. The data stays in your cloud storage. External tables can read any format that COPY INTO <table> supports except XML. They are strictly read-only: you can query them, join them and build views on them, but you cannot run DML against them. Every external table has a VARIANT column called VALUE, which holds one row of the file, plus the pseudocolumns METADATA$FILENAME and METADATA$FILE_ROW_NUMBER. You don't need to know the schema up front. If you do know it, you can define typed virtual columns as expressions over VALUE.

Performance is the main thing to manage. Queries can be slower than on native tables, so Snowflake recommends materialized views over external tables. For Parquet, it suggests Iceberg tables instead. The recommended file sizes are 256–512 MB for Parquet files and 16–256 MB for other formats.

Partitioning is the other lever. Partition columns are expressions that parse METADATA$FILENAME, so the storage paths must encode something like a date or a country. You choose one of two methods when you create the table, and you can't change it later. With automatic partitions (the default), Snowflake computes partitions whenever the metadata refreshes. The refresh happens at creation, then either automatically on new-file events or manually with ALTER EXTERNAL TABLE … REFRESH. With user-specified partitions (PARTITION_TYPE = USER_SPECIFIED), you add partitions yourself. This is typically how you keep the table in sync with a metastore such as AWS Glue or Apache Hive.

ALTER EXTERNAL TABLE <name> ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

With TABLE_FORMAT = DELTA, a refresh parses the _delta_log transaction files to work out which Parquet files are current. Two limits apply: Delta external tables don't support deletion vectors, and they can't auto-refresh. The feature also carries a deprecation notice. The documented path forward is to migrate to an Iceberg table, which connects to the Delta files through an external volume and a catalog integration.

Sources1

2.Iceberg tables: open format, two kinds of catalog

Iceberg tables follow the Apache Iceberg open table format and use Parquet data files. The format adds ACID transactions, schema evolution, hidden partitioning and snapshots on top of the files. You choose one of two storage options. With Snowflake storage (EXTERNAL_VOLUME = SNOWFLAKE_MANAGED), Snowflake stores and manages the files and bills you for the storage, and permanent tables are protected by Fail-safe. With an external volume, the files sit in your own S3, GCS or Azure storage, which Snowflake reaches through the volume. An external volume is an account-level object that holds an IAM entity for the storage location. One volume can serve many tables. With an external volume, you are responsible for data protection: Snowflake doesn't provide Fail-safe, and your cloud provider bills you for the storage.

The second decision is the catalog. With Snowflake as the catalog (a Snowflake-managed table), you get full platform support. That includes full DML (INSERT, MERGE, UPDATE, DELETE, TRUNCATE TABLE) and bulk loading with COPY INTO and Snowpipe. Snowflake also handles lifecycle maintenance such as compaction, though you can disable compaction for a table. With an external catalog such as AWS Glue or Open Catalog, Snowflake needs a catalog integration to read the metadata, and platform support is limited. Snowflake can still write to externally managed tables that use a remote Iceberg REST catalog. You can also convert an externally managed table to use Snowflake as the catalog.

INSERT INTO store_sales VALUES (-99);

UPDATE store_sales
  SET cola = 1
  WHERE cola = -99;

For Snowflake-managed tables, Snowflake writes the Iceberg metadata to a metadata folder on the external volume. You can also call SYSTEM$GET_ICEBERG_TABLE_INFORMATION to generate metadata for recent changes. If a DML statement fails and rolls back, it can leave orphan Parquet files in your storage. If storage usage in your cloud doesn't match TABLE_STORAGE_METRICS, that is the symptom to look for.

UPDATE, DELETE and MERGE raise one more management question: should Snowflake rewrite whole data files (copy-on-write) or write small delete files that readers merge at query time (merge-on-read)? The ICEBERG_MERGE_ON_READ_BEHAVIOR parameter controls this choice. You can set it at the account, database, schema or table level, and the most specific setting wins.

'ENABLED' forces merge-on-read regardless of version or management mode, and 'DISABLED' forces copy-on-write. Use 'DISABLED' when other engines can't read v3 deletion vectors. Even when merge-on-read applies, Snowflake decides file by file: it rewrites a file when about 5% or more of its rows are deleted. The parameter only governs DML that Snowflake itself issues. External engines follow their own write.delete.mode, write.update.mode and write.merge.mode table properties.

ALTER ICEBERG TABLE my_lake.public.events
  SET ICEBERG_MERGE_ON_READ_BEHAVIOR = ' ? ';

Sources23

3.Federating external catalogs: catalog-linked databases and Horizon Catalog

Creating one externally managed Iceberg table per remote table doesn't scale when a Glue, Unity or Open Catalog deployment holds hundreds of tables. A catalog-linked database solves this. It is a Snowflake database connected to a remote Iceberg REST catalog. Snowflake polls the catalog (every 30 seconds by default, set with SYNC_INTERVAL_SECONDS), discovers namespaces and tables, and registers them automatically. Allowed namespaces appear as schemas, and their Iceberg tables appear inside them. You can query those tables, and you can also create schemas and Iceberg tables that are written back to the remote catalog.

You set up access first. If the remote catalog supports credential vending, a catalog integration with vended credentials is enough. If it doesn't, you need both an external volume and an Iceberg REST catalog integration.

CREATE DATABASE my_linked_db
  LINKED_CATALOG = (
    CATALOG = 'my_catalog_int'
  );

After you create the database, check its configuration with SYSTEM$GET_CATALOG_LINKED_DATABASE_CONFIG and its sync health with SYSTEM$CATALOG_LINK_STATUS. The status function also lists remote tables that failed to sync. A table can sync but still fail to refresh, for example because of a corrupted data file. In that case SHOW ICEBERG TABLES reports ICEBERG_TABLE_NOT_INITIALIZED in auto_refresh_status, and queries against the table return an error until you fix the file and turn automated refresh back on. Discovery and schema operations are billed as cloud services. Table creation is billed through auto refresh.

Where Horizon Catalog fits. Snowflake Horizon Catalog is the governance and interoperability layer for data inside and outside Snowflake. Catalog-linked databases are how it connects to your external Iceberg ecosystem, including AWS Glue and Azure OneLake. So the federation recipe is a catalog integration (with vended credentials, or with an external volume) plus a catalog-linked database.

Horizon also works in the other direction. It exposes the Horizon Iceberg REST Catalog API, so external engines such as Spark, Trino, DuckDB and PyIceberg, and catalogs such as AWS Glue and Apache Polaris, can read and write Snowflake-managed Iceberg tables. The two meet at one point: an external engine can reach externally managed tables in a catalog-linked database through the same Horizon endpoint, by passing the catalog-linked database name as the warehouse property. Snowflake RBAC policies apply either way. Externally managed Iceberg tables outside a catalog-linked database aren't accessible through the Horizon Iceberg REST Catalog API.

Sources4567

4.Hybrid tables: row store with enforced constraints

Hybrid tables solve a different problem from the other table types. They target transactional (Unistore) workloads: high-concurrency random writes and index-based point reads, such as an ingestion workflow's state table that thousands of parallel workers update. Writes go directly into a row store with row-level locking. Snowflake then copies the data asynchronously into object storage, so large analytical scans don't interfere with operational traffic. The optimizer decides which copy each query reads. Hybrid tables run in the same engine and warehouses as standard tables, so you can join the two types and run a single transaction across both without a two-phase commit. Expect a larger storage footprint, because row data compresses less well than columnar micro-partitions.

Two management points follow from the table. First, indexes are the performance tool for hybrid tables, and they are updated synchronously on every write, unlike the asynchronously maintained search optimization service on standard tables. Second, CHECK constraints can be defined only at table creation, so plan them up front. Snowflake also advises that you review the documented unsupported features and limitations before creating hybrid tables.

Sources8

5.General table management: columns, retention and swapping

Some management tasks apply to any table. You change structure with ALTER TABLE, for example adding columns. You can add several kinds of column in one go: with a NOT NULL constraint, a DEFAULT value or a collation. ADD COLUMN IF NOT EXISTS leaves an existing column untouched instead of failing.

ALTER TABLE t1 ADD COLUMN a2 NUMBER;

The retention period controls recovery. DATA_RETENTION_TIME_IN_DAYS sets how long Time Travel actions (SELECT, CLONE and UNDROP) can reach historical data in a table. The default is 1. Standard Edition allows 0 or 1. Enterprise Edition allows 0 to 90 for permanent tables but only 0 or 1 for temporary and transient tables. A value of 0 effectively disables Time Travel. You can set a default at the schema level, and tables created there inherit it. UNDROP restores a dropped table. Temporary tables persist only for the duration of the session and are not visible to other users.

One common pattern is to rebuild a table under a staging name, validate it, and then exchange it with the live table using ALTER TABLE … SWAP WITH. In the documented example, DESC TABLE t1 after the swap shows t2's former column (B1), and t2 now holds t1's columns. The two tables exchange contents and definitions, so the name that consumers query never stops resolving. You also keep the old version under the staging name, which lets you swap back.

ALTER TABLE t1 SWAP WITH t2;

Sources91011

6.Schema evolution: letting tables follow their sources

Upstream systems add fields over time. For loads into Snowflake tables, Snowflake can evolve the table automatically in two ways: it adds new columns, and it drops NOT NULL from columns that are missing in new files. You switch this on with ENABLE_SCHEMA_EVOLUTION = TRUE, either in CREATE TABLE or with ALTER TABLE on an existing table. The parameter alone isn't enough, though. A load evolves the table only when all three of these conditions hold:

1. The table has ENABLE_SCHEMA_EVOLUTION set to TRUE. 2. The COPY INTO <table> statement uses MATCH_BY_COLUMN_NAME. 3. The loading role has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

For CSV files loaded with MATCH_BY_COLUMN_NAME and PARSE_HEADER, you must also set ERROR_ON_COLUMN_COUNT_MISMATCH to false. Schema evolution is a separate feature from schema detection, but the two combine well: a pipeline can create a table from a set of staged files and then keep it in step as the files change.

Know the limits. Automatic evolution is limited to COPY INTO <table> statements and Snowpipe loads. Snowpipe Streaming with the high-performance architecture also supports it, as does the Kafka connector with Snowpipe Streaming Classic. INSERT operations cannot evolve the target table schema, and tasks don't support schema evolution. By default one COPY operation can add at most 100 columns or evolve no more than 1 schema. Contact Snowflake Support to raise either limit. There is no limit on dropping NOT NULL constraints.

For Iceberg tables, the same usage notes apply to Snowflake-managed tables, and you can set ENABLE_SCHEMA_EVOLUTION with ALTER ICEBERG TABLE. Schema evolution is not supported for structured type fields in Iceberg tables. You can also change an Iceberg table's columns by hand: ALTER ICEBERG TABLE supports ADD COLUMN (several columns in one command, with IF NOT EXISTS) and RENAME COLUMN. For an externally managed Iceberg table, Snowflake uses the catalog integration to retrieve the table's metadata and schema from the external catalog, and refreshing the table metadata is part of maintaining such tables.

Sources12132

7.Unloading data with COPY INTO <location>

Unloading reverses the load: COPY INTO <location> writes the result of a table or query to a stage or a cloud URL. The source can be a SELECT, including joins across tables. The target can be an internal stage (user, table or named), where you download the files afterwards with GET, or an external stage or cloud URL. You can authenticate with a storage integration or with supplied credentials, and a file format controls how the output is written: TYPE = CSV, JSON or PARQUET, or a named file format. JSON can only be used to unload VARIANT columns.

COPY INTO 'gcs://mybucket/unload/' FROM mytable STORAGE_INTEGRATION = myint FILE_FORMAT = (FORMAT_NAME = my_csv_format);

A few copy options shape the output:

- MAX_FILE_SIZE caps each file. The default is 16 MB (16777216 bytes), and the maximum is 5 GB on S3, GCS and Azure stages. - SINGLE = TRUE writes one file instead of many, at a possible cost in performance. The default is FALSE. - HEADER = TRUE includes column headings. The default is FALSE, and with multiple files every file gets the headings. - VALIDATION_MODE = RETURN_ROWS returns the query results instead of unloading them, so you can check the query first. It is the only supported validation option.

Account parameters can restrict unloading: PREVENT_UNLOAD_TO_INLINE_URL blocks ad hoc unloads to cloud storage URLs, and PREVENT_UNLOAD_TO_INTERNAL_STAGES blocks unloads to any internal stage.

To let downstream engines read only part of an export, add PARTITION BY. It takes any SQL expression that evaluates to a string and splits the unloaded rows into separate files by that value. The warehouse's parallelism and the data volume decide how many files Snowflake writes. Filenames start with data_ and include the partition values, and a UUID (the COPY statement's query ID) identifies each file. A NULL partition value produces a _NULL_ path. PARTITION BY can't be combined with OVERWRITE = TRUE, SINGLE = TRUE or INCLUDE_QUERY_ID = FALSE. The documentation's Parquet example partitions by a date column and an hour, with a 32 MB upper size limit per file.

Because partition values end up in file names, and file URLs appear in internal logs that might be processed outside your region, Snowflake recommends partitioning only on dates, timestamps and Booleans. Avoid sensitive strings or integers.

Sources1415

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.An external table can take INSERT or UPDATE statements like a native table, as long as you have write access to the stage.Why is that wrong?

   External tables are read-only. They support queries, joins and views, but no DML. To modify lake data from Snowflake, use a Snowflake-managed Iceberg table.

   Covered in External tables: query files in place, read-only

2. 2.Every Iceberg table gets Fail-safe because Snowflake manages its metadata.Why is that wrong?

   When the files are on an external volume that you manage, you are responsible for protection and recovery, and Snowflake doesn't provide Fail-safe. Fail-safe applies to permanent tables stored in Snowflake storage.

   Covered in Iceberg tables: open format, two kinds of catalog

3. 3.Primary keys on hybrid tables are informational, as they are on standard tables.Why is that wrong?

   On hybrid tables, PRIMARY KEY is required and enforced, and FOREIGN KEY and UNIQUE constraints are also enforced. On standard tables, these constraints are not enforced.

   Covered in Hybrid tables: row store with enforced constraints

4. 4.Setting ENABLE_SCHEMA_EVOLUTION = TRUE on the table is enough for new columns to appear during loads.Why is that wrong?

   The load must also use MATCH_BY_COLUMN_NAME, and the loading role needs the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

   Covered in Schema evolution: letting tables follow their sources

5. 5.An INSERT ... SELECT into a table with ENABLE_SCHEMA_EVOLUTION = TRUE adds the new columns automatically.Why is that wrong?

   Automatic evolution is limited to COPY INTO <table> and Snowpipe loads (plus streaming ingestion paths). INSERT cannot evolve the schema.

   Covered in Schema evolution: letting tables follow their sources

6. 6.You can combine PARTITION BY with SINGLE = TRUE to get one file per partition folder.Why is that wrong?

   PARTITION BY is not supported together with OVERWRITE = TRUE, SINGLE = TRUE or INCLUDE_QUERY_ID = FALSE.

   Covered in Unloading data with COPY INTO <location>