Data Movement · Topic 17 of 17
Table Management, Schema Evolution and Unloading
Manage table types and data operations, enable schema evolution on load, and unload data with COPY INTO location, file size, partitioning and format rules.
- 6 min
- Transient and temporary tables have no Fail-safe and at most 1 day of Time Travel. Permanent tables have 7 days of Fail-safe.
SWAP WITHpublishes a rebuilt table in one step.TRUNCATEclears load metadata,DELETEdoes not.- Schema evolution needs the table property,
MATCH_BY_COLUMN_NAMEandEVOLVE SCHEMAor ownership. It adds columns and dropsNOT NULL.
What it is
This page covers keeping tables healthy and in sync with their sources: choosing the table type, running everyday table operations, letting a table grow new columns as files change, and writing table data back out to files.
How it works
Table types and data operations
| Type | Lives | Time Travel | Fail-safe |
|---|---|---|---|
| Permanent | Until dropped | Up to the account and edition limit | Yes, 7 days |
| Transient | Until dropped | 0 or 1 day | No |
| Temporary | The session only | 0 or 1 day | No |
| External, Iceberg, hybrid, dynamic | See earlier pages | Depends on type | Depends on type |
Use transient tables for staging and intermediate data that can be rebuilt, since they avoid Fail-safe storage. Use temporary tables for session scratch work.
Everyday operations a data engineer should know:
CREATE TABLE ... LIKEcopies structure.CREATE TABLE ... AS SELECT(CTAS) creates and fills.CREATE TABLE ... CLONEmakes a zero copy duplicate.CREATE OR REPLACE TABLEdrops and recreates, losing the old table's load history.ALTER TABLE ... SWAP WITHexchanges two tables in one step, which is the usual way to publish a rebuilt table without downtime.ALTER TABLE ... RENAME TO,ADD COLUMN,DROP COLUMN, andSET DATA_RETENTION_TIME_IN_DAYSchange shape and retention.UNDROP TABLErestores a dropped table within its Time Travel period.TRUNCATE TABLEremoves all rows and also clears load metadata.DELETEremoves rows and keeps load metadata, so reloading the same files afterDELETEneedsFORCE = TRUE.MERGEupserts.INSERT OVERWRITEreplaces the table contents. Multi-tableINSERT ALLorINSERT FIRSTroutes rows to several targets.ALTER TABLE ... CLUSTER BYsets a clustering key for large tables.
Swap is the safe publish step. Build orders_new, validate it, then ALTER TABLE orders SWAP WITH orders_new.
Schema evolution
Schema evolution lets a table add columns automatically when new files contain them.
ALTER TABLE raw_events SET ENABLE_SCHEMA_EVOLUTION = TRUE;
COPY INTO raw_events
FROM @events_stage
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
All three conditions must hold:
- The table has
ENABLE_SCHEMA_EVOLUTION = TRUE. - The load uses
MATCH_BY_COLUMN_NAME. - The loading role has the
EVOLVE SCHEMAorOWNERSHIPprivilege on the table.
What it does and where it works:
- It adds new columns and drops
NOT NULLfrom columns that are missing in new files. It does not rename columns or change types. - Supported file formats: Avro, Parquet, CSV, JSON and ORC. For CSV with
PARSE_HEADER, setERROR_ON_COLUMN_COUNT_MISMATCH = FALSE. - It works with
COPY INTOand Snowpipe.INSERTcannot evolve the schema. The Snowpipe Streaming high performance architecture is also supported. - By default it adds up to 100 columns and evolves one schema per COPY operation. Higher limits need Snowflake Support.
- Changes are recorded in the
SchemaEvolutionRecordfield, visible inINFORMATION_SCHEMA.COLUMNS,ACCOUNT_USAGE.COLUMNS,DESCRIBE TABLEandSHOW COLUMNS. - It is not supported in tasks, nor for structured type fields in Iceberg tables.
Use INFER_SCHEMA with CREATE TABLE ... USING TEMPLATE to design the first table, and schema evolution to keep it current.
Unloading data
COPY INTO <location> writes a table or query result to files in an internal stage, an external stage or a cloud location.
COPY INTO @unload_stage/orders/
FROM (SELECT * FROM orders WHERE order_date >= '2026-01-01')
FILE_FORMAT = (TYPE = PARQUET)
PARTITION BY (TO_VARCHAR(order_date, 'YYYY-MM-DD'))
HEADER = TRUE
MAX_FILE_SIZE = 268435456;
| Option | Effect |
|---|---|
MAX_FILE_SIZE | Target size per output file. Default 16 MB (16777216 bytes). The maximum supported is 5 GB. |
SINGLE = TRUE | One output file. May slow the unload. |
PARTITION BY | Writes files into folders by an expression, such as a date |
HEADER = TRUE | Includes column names in the file |
OVERWRITE = TRUE | Replaces existing files with the same name; other files stay; not supported with PARTITION BY |
INCLUDE_QUERY_ID = TRUE | Adds the query ID to file names, so reruns do not collide; default FALSE, but always TRUE with PARTITION BY; not allowed with SINGLE or OVERWRITE |
FILE_FORMAT | CSV, JSON or Parquet. JSON uses OBJECT_CONSTRUCT to make one value per row. |
- To unload to an external location, use a stage built on a storage integration, and no credentials are in the statement.
- For an internal stage, download the files with
GET(not from the SQL API). - Parquet unload writes DECIMAL for fixed point and DOUBLE for floating point by default. Cast in the query to choose types such as BIGINT. VARIANT unloads as a JSON string unless cast with
TO_ARRAY, and TIMESTAMP_TZ or TIMESTAMP_LTZ columns cause an error. - CSV and JSON truncate floating point values to about 15 digits of precision, while Parquet does not.
NULL_IFdefaults to\NandFIELD_OPTIONALLY_ENCLOSED_BYdefaults toNONE. Enclose strings withFIELD_OPTIONALLY_ENCLOSED_BYto keep empty strings distinct from NULLs; without it, and withEMPTY_FIELD_AS_NULL = TRUE(default), they look the same.- Give compressed files the extension that matches the compression, such as
.gzfor GZIP.
Snowflake does not clear the target path before an unload. A rerun to the same path can leave old and new files mixed together unless you use a fresh path, OVERWRITE or INCLUDE_QUERY_ID.
Common mistakes
- Enabling schema evolution but loading without
MATCH_BY_COLUMN_NAME, so no columns are added. - Expecting
INSERTto evolve the schema. - Using
CREATE OR REPLACEon a table and losing its load history or grants, instead ofSWAPorALTER. - Using
DELETEand then wondering why the same files will not reload. Load metadata is kept, so useFORCEorTRUNCATE. - Setting
SINGLE = TRUEon a large unload and slowing it down. - Unloading without a unique path or
INCLUDE_QUERY_IDand overwriting earlier output by accident.
What to remember for the exam
- Transient and temporary tables have no Fail-safe and at most 1 day of Time Travel. Permanent tables have 7 days of Fail-safe.
SWAP WITHpublishes a rebuilt table in one step.TRUNCATEclears load metadata,DELETEdoes not.- Schema evolution needs the table property,
MATCH_BY_COLUMN_NAMEandEVOLVE SCHEMAor ownership. It adds columns and dropsNOT NULL. - Default limit is 100 columns added per COPY and one schema evolved per operation.
- Unload:
COPY INTO <location>, default 16 MB files, maximum 5 GB, formats CSV, JSON and Parquet. PARTITION BY,SINGLE,OVERWRITE,INCLUDE_QUERY_IDandHEADERshape the output.
Check yourself
Which three conditions must be true for schema evolution to add a column during COPY?
The table has
ENABLE_SCHEMA_EVOLUTION = TRUE, the COPY usesMATCH_BY_COLUMN_NAME, and the role hasEVOLVE SCHEMAorOWNERSHIP.What is the default output file size when unloading with COPY INTO a location?
16 MB, set by
MAX_FILE_SIZE, which can be raised to at most 5 GB.How can a rebuilt table replace the live one with no gap?
Run
ALTER TABLE live SWAP WITH rebuilt.
Topic 17 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.