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
- External tables are read only, store metadata, expose
VALUEasVARIANT, and useAUTO_REFRESHthrough 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;
FORCEduplicates;LOAD_UNCERTAIN_FILEScovers 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?
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.
| Aspect | Snowflake managed Iceberg table | Externally managed Iceberg table |
|---|---|---|
| Catalog | Snowflake is the Iceberg catalog | An external catalog through a catalog integration (for example AWS Glue or an Iceberg REST catalog) |
| Read and write from Snowflake | Both | Read, with the metadata kept in step by refresh; writes are supported when the catalog is a remote Iceberg REST catalog |
| Who maintains metadata and files | Snowflake | The external engine that owns the catalog |
| Best for | Full Snowflake features with an open format that other engines can read | Reading 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.
"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.
| Aspect | COPY INTO table | Snowpipe |
|---|---|---|
| History kept | 64 days, per table | 14 days, per pipe |
| Duplicate check | File name and file checksum (ETag) | File name |
| View it | COPY_HISTORY, LOAD_HISTORY | COPY_HISTORY and SYSTEM$PIPE_STATUS |
To reload files on purpose, COPY gives three choices.
FORCE = TRUEreloads every matching file, even ones already loaded. It creates duplicates unless you truncate first.TRUNCATE TABLEclears the target and reloads cleanly, since truncating also resets the load metadata for the table.LOAD_UNCERTAIN_FILES = TRUEloads 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.
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.
| Factor | Full | Incremental |
|---|---|---|
| Cost | Grows with all data | Grows with changes |
| Handles deletes | Automatically | Needs CDC, soft deletes or a stream |
| Complexity | Low | Higher |
| Fits | Small tables, rare rebuilds, dimension reloads | Large 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
VALUEasVARIANT, and useAUTO_REFRESHthrough 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;
FORCEduplicates;LOAD_UNCERTAIN_FILEScovers 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 WITHfor 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.