Snowflake AI Data Cloud Features & Architecture · Topic 13 of 17

Table Types - Apache Iceberg, External and Dynamic

Know when to use Apache Iceberg tables, external tables and dynamic tables in Snowflake: storage, catalogs, refresh, target lag and limits compared.

  • 6 min
In a nutshell
  • Iceberg tables use the open Apache Iceberg format, reach your storage through an external volume, and use Snowflake or an external system as catalog.
  • Snowflake-managed Iceberg tables support full read and write; externally managed ones have more limited support.
  • External tables are read-only, read files in an external stage, and can auto refresh metadata through event notifications.

What it is

Three table types go beyond the standard Snowflake table. Apache Iceberg tables store data in the open Iceberg format so other engines can use it too. External tables let you query files that sit in a stage without loading them. Dynamic tables hold the result of a query and keep it refreshed for you.

The exam tests whether you can tell them apart by where the data lives, whether you can change it with DML, and what keeps it current.

TypeData lives inWrite with DMLKept current by
Iceberg, Snowflake as catalogExternal volume or Snowflake storageYesSnowflake
Iceberg, external catalogYour cloud storage, tracked by another catalogLimitedMetadata refresh from the catalog
External tableFiles in an external stageNo, read-onlyMetadata refresh, manual or automatic
Dynamic tableSnowflake storageNo, defined by its queryAutomatic refreshes to meet a target lag

How it works

Apache Iceberg tables

Iceberg tables keep table data as Parquet files plus Iceberg metadata. Usually the files live in your own cloud storage (Amazon S3, Google Cloud Storage or Azure Storage), which Snowflake reaches through an external volume: an account-level object that connects Snowflake to that storage. One external volume can serve many Iceberg tables.

Every Iceberg table has a catalog that tracks its metadata:

  • Snowflake as the catalog (Snowflake-managed Iceberg table). Snowflake gives full read and write support and handles maintenance such as compaction. Other engines can read the table, but third-party clients cannot write to it directly; external writes are possible only through Snowflake's Horizon Iceberg REST Catalog API, with restrictions.
  • External catalog (externally managed Iceberg table), connected through a catalog integration. Sources include remote Iceberg REST catalogs such as AWS Glue and Snowflake Open Catalog, or Iceberg metadata files in object storage. Snowflake platform support is more limited, and writes are supported only in some setups, such as tables in a remote Iceberg REST catalog.

A catalog-linked database can connect to a remote Iceberg REST catalog and automatically discover and sync its namespaces and tables.

Spark or other enginereads and writesIceberg formatCloud storageParquet + metadataexternal volumeSnowflakeIceberg tablecatalogSnowflake catalogor external catalog

Billing and protection differ from standard tables. When the data sits on a customer-managed external volume, your cloud provider bills the storage, not Snowflake, and Fail-safe does not apply, so you own data protection. Snowflake still bills compute for queries.

External tables

An external table is a read-only table over data files in an external stage. Snowflake stores only file-level metadata, such as file names and paths, so queries read the files directly from cloud storage.

  • Each row exposes a VALUE column of type VARIANT holding the record, plus pseudocolumns such as METADATA$FILENAME.
  • You can define virtual columns as expressions on VALUE, and partition columns based on the file path to improve pruning.
  • Metadata is refreshed with ALTER EXTERNAL TABLE ... REFRESH, or automatically with AUTO_REFRESH = TRUE driven by cloud event notifications.

Because files are read from outside Snowflake, external tables are usually slower than native tables. A materialized view on an external table is a common way to speed up repeated queries.

Data lakeS3, Azure, GCSevent notificationExternal tableAUTO REFRESH metadataqueryWarehousereads filesoptionalMaterialized viewfaster repeat queries

Dynamic tables

A dynamic table is defined by a query and a target lag. Snowflake runs refreshes so the table's content is never more than the target lag behind its base tables. You declare the result; Snowflake handles the scheduling and dependency order.

CREATE DYNAMIC TABLE daily_sales
  TARGET_LAG = '15 minutes'
  WAREHOUSE = transform_wh
  AS SELECT order_date, SUM(amount) AS total
     FROM orders GROUP BY order_date;

Key settings and behavior:

  • TARGET_LAG sets freshness. The minimum is 60 seconds. Setting it to DOWNSTREAM means the table refreshes only when a dynamic table that reads from it needs fresh data.
  • WAREHOUSE names the virtual warehouse that runs the refreshes, so refreshes consume that warehouse's credits.
  • REFRESH_MODE can be INCREMENTAL (process only changes), FULL (recompute everything) or AUTO, which lets Snowflake choose at creation. Newer modes such as ADAPTIVE also exist.
  • Dynamic tables are read-only: you cannot run INSERT, UPDATE, DELETE, TRUNCATE or MERGE against them. Change the query instead.

Chaining dynamic tables builds a declarative pipeline, an alternative to streams and tasks for many transformations.

Raw tableordersrefreshDynamic tablecleaned, lag DOWNSTREAMrefreshDynamic tabledaily sales, lag 15 minutesread byDashboard
Exam tip

If a question describes "keep a transformed table up to date within N minutes without writing schedules or streams", the answer is a dynamic table with a TARGET_LAG.

How the exam asks about this

  • "Data engineers use Spark on the same data that Snowflake queries, and the company wants one open table format" -> Apache Iceberg table on an external volume.
  • "Query files in an existing data lake without loading them, and pick up new files automatically" -> external table with AUTO_REFRESH.
  • "Build a multi-step transformation pipeline declaratively with a freshness goal" -> chained dynamic tables.

Common mistakes

Treating external tables as writable. External tables are read-only. Load the data into a standard or Iceberg table if you need DML.

Assuming Snowflake bills storage for every Iceberg table. With a customer-managed external volume, the cloud provider bills storage and Fail-safe is not available.

Thinking dynamic tables refresh with serverless compute. Refreshes run on the warehouse named in the WAREHOUSE parameter.

Setting a target lag below one minute. The minimum target lag is 60 seconds.

What to remember for the exam

  • Iceberg tables use the open Apache Iceberg format, reach your storage through an external volume, and use Snowflake or an external system as catalog.
  • Snowflake-managed Iceberg tables support full read and write; externally managed ones have more limited support.
  • External tables are read-only, read files in an external stage, and can auto refresh metadata through event notifications.
  • Dynamic tables are defined by a query plus TARGET_LAG and refresh on a named warehouse; no direct DML.
  • DOWNSTREAM lag refreshes a dynamic table only when a downstream dynamic table needs it.
Watch out

An external table only holds file metadata. If files are added but the metadata is not refreshed, queries will not see the new data.

Check yourself

  • What object connects Snowflake to the cloud storage that holds Iceberg table files?

    An external volume, an account-level object that can serve many Iceberg tables.

  • Can you run UPDATE on an external table?

    No. External tables are read-only; load the data into a table if you need to change it.

  • What does TARGET_LAG control on a dynamic table?

    How far the table's content may fall behind its base tables, which Snowflake uses to schedule refreshes.

Topic 13 of 17 in Snowflake AI Data Cloud Features & Architecture

Test yourself on Snowflake AI Data Cloud Features & Architecture

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.