Data Engineering · Topic 4 of 16

External Tables, Reload, Incremental Updates and Iceberg Tables

Query data in place with external and Iceberg tables, understand load history and reloads, and choose incremental or full updates for a Snowflake pipeline.

  • 5 min
In a nutshell
  • External tables are read only, store metadata, expose VALUE as VARIANT, and use AUTO_REFRESH through event notifications.
  • Iceberg: Snowflake managed reads and writes; externally managed reads through a catalog integration (writes only with a remote Iceberg REST catalog). Both use an external volume.
  • COPY load metadata is 64 days; Snowpipe history is 14 days; FORCE duplicates; LOAD_UNCERTAIN_FILES covers expired metadata.

What it is

Not every dataset needs to be loaded. External tables and Iceberg tables let Snowflake query files that stay in cloud storage. When data is loaded, the load history decides what a re-run does, and the pipeline either processes everything again (full) or only what changed (incremental).

These four ideas answer one question: how do you keep a table current without loading or processing more than you must?

Files in cloud storagequery in placeExternal or Iceberg tableno loador loadCOPY or pipeload historythenFull or incrementalrefresh the target

How it works

External tables

An external table is a read only table whose data lives in files on an external stage. Snowflake stores only metadata about the files. Each row exposes a VALUE column of type VARIANT plus pseudo columns such as METADATA$FILENAME, and you can add virtual columns that cast fields out of VALUE.

Queries read the files on every run, so they are slower than native tables. Three tools help: partition the data by a path value and define partition columns so queries prune files, add a materialized view over the external table, or load the data when speed matters.

The file list must be kept current. AUTO_REFRESH = TRUE uses cloud event notifications to refresh metadata when files are added or removed; ALTER EXTERNAL TABLE ... REFRESH does it manually.

Iceberg tables

An Iceberg table stores data as Parquet files with Apache Iceberg metadata in cloud storage that you own, reached through an external volume. There are two kinds.

AspectSnowflake managed Iceberg tableExternally managed Iceberg table
CatalogSnowflake is the Iceberg catalogAn external catalog through a catalog integration (for example AWS Glue or an Iceberg REST catalog)
Read and write from SnowflakeBothRead, with the metadata kept in step by refresh; writes are supported when the catalog is a remote Iceberg REST catalog
Who maintains metadata and filesSnowflakeThe external engine that owns the catalog
Best forFull Snowflake features with an open format that other engines can readReading tables another system already writes

An externally managed table must be refreshed to see new snapshots, either manually or with auto refresh through the catalog integration. Managed tables give Snowflake performance and features such as clustering and Time Travel over open files, but Snowflake manages the table, so other engines read it through the catalog rather than own it.

Exam tip

"Another engine writes, Snowflake reads" means externally managed. "Snowflake writes and other engines read the open format" means Snowflake managed.

Reload process and load history

Both COPY and Snowpipe track loaded files so a re-run does not duplicate rows.

AspectCOPY INTO tableSnowpipe
History kept64 days, per table14 days, per pipe
Duplicate checkFile name and file checksum (ETag)File name
View itCOPY_HISTORY, LOAD_HISTORYCOPY_HISTORY and SYSTEM$PIPE_STATUS

To reload files on purpose, COPY gives three choices.

  • FORCE = TRUE reloads every matching file, even ones already loaded. It creates duplicates unless you truncate first.
  • TRUNCATE TABLE clears the target and reloads cleanly, since truncating also resets the load metadata for the table.
  • LOAD_UNCERTAIN_FILES = TRUE loads files whose load status cannot be known because the file is older than 64 days and its metadata expired.

Account Usage views such as SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY keep 365 days of history, while the Information Schema table function reaches back 14 days.

Rerun COPYsame filescheckLoad metadatawithin 64 days?yesSkippedno duplicatesRerun COPYcheckLoad metadataFORCE or truncateReloadedduplicates or clean

Incremental updates compared to full updates

A full update rebuilds the target from all source data: CREATE OR REPLACE TABLE ... AS SELECT, INSERT OVERWRITE, or truncate and load. It is simple and self correcting, but cost grows with total data size.

An incremental update processes only new or changed rows, using streams, MERGE, a high water mark such as a timestamp, or a dynamic table in incremental refresh mode. It costs less on large tables but needs care for deletes and late data.

FactorFullIncremental
CostGrows with all dataGrows with changes
Handles deletesAutomaticallyNeeds CDC, soft deletes or a stream
ComplexityLowHigher
FitsSmall tables, rare rebuilds, dimension reloadsLarge facts, frequent refresh

A common pattern uses an incremental load daily and a periodic full rebuild to correct drift. For safe full rebuilds, build a new table and swap it in with ALTER TABLE ... SWAP WITH, so readers never see a half loaded table.

Common mistakes

Expecting external table speed to match native tables. Files are read at query time. Partition, add materialized views or load the data.

Using FORCE to retry a failed load. FORCE reloads everything and duplicates rows. Fix the cause and re-run so only unloaded files load.

Choosing incremental for a source that deletes rows. A high water mark cannot see deletes. Use CDC or a full refresh.

Confusing which Iceberg kind writes. Snowflake fully controls managed tables; an externally managed table is controlled by its external catalog, and Snowflake can write to it only through a remote Iceberg REST catalog.

What to remember for the exam

  • External tables are read only, store metadata, expose VALUE as VARIANT, and use AUTO_REFRESH through event notifications.
  • Iceberg: Snowflake managed reads and writes; externally managed reads through a catalog integration (writes only with a remote Iceberg REST catalog). Both use an external volume.
  • COPY load metadata is 64 days; Snowpipe history is 14 days; FORCE duplicates; LOAD_UNCERTAIN_FILES covers expired metadata.
  • Incremental is cheaper at scale but needs a plan for deletes; full is simple and self correcting.
  • Swap a freshly built table in with ALTER TABLE ... SWAP WITH for a safe full reload.

Check yourself

  • Which Iceberg table type does Snowflake fully manage and write to?

    A Snowflake managed Iceberg table, where Snowflake is the catalog. Externally managed tables are read through a catalog integration, with writes only through a remote Iceberg REST catalog.

  • A COPY re-run loads nothing even though you fixed the source file contents. Why?

    Load metadata still shows the file as loaded. Use a new file name, truncate the table or use FORCE, knowing FORCE can duplicate.

  • Why can an incremental load based on a timestamp be wrong?

    It never sees deleted rows, and misses rows whose timestamp was not updated.

Topic 4 of 16 in Data Engineering

Test yourself on Data Engineering

The full Snowflake ARA-C01 question bank has 687 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.