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
In a nutshell
  • 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 WITH publishes a rebuilt table in one step. TRUNCATE clears load metadata, DELETE does not.
  • Schema evolution needs the table property, MATCH_BY_COLUMN_NAME and EVOLVE SCHEMA or ownership. It adds columns and drops NOT 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.

New filesextra columnsCOPY with MATCH BY COLUMN NAMETableENABLE SCHEMA EVOLUTIONadds columnsWider tableTableCOPY INTO locationStagefilesGET or cloud toolsDownstream user

How it works

Table types and data operations

TypeLivesTime TravelFail-safe
PermanentUntil droppedUp to the account and edition limitYes, 7 days
TransientUntil dropped0 or 1 dayNo
TemporaryThe session only0 or 1 dayNo
External, Iceberg, hybrid, dynamicSee earlier pagesDepends on typeDepends 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 ... LIKE copies structure. CREATE TABLE ... AS SELECT (CTAS) creates and fills. CREATE TABLE ... CLONE makes a zero copy duplicate.
  • CREATE OR REPLACE TABLE drops and recreates, losing the old table's load history.
  • ALTER TABLE ... SWAP WITH exchanges 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, and SET DATA_RETENTION_TIME_IN_DAYS change shape and retention.
  • UNDROP TABLE restores a dropped table within its Time Travel period.
  • TRUNCATE TABLE removes all rows and also clears load metadata. DELETE removes rows and keeps load metadata, so reloading the same files after DELETE needs FORCE = TRUE.
  • MERGE upserts. INSERT OVERWRITE replaces the table contents. Multi-table INSERT ALL or INSERT FIRST routes rows to several targets.
  • ALTER TABLE ... CLUSTER BY sets a clustering key for large tables.
Exam tip

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:

  1. The table has ENABLE_SCHEMA_EVOLUTION = TRUE.
  2. The load uses MATCH_BY_COLUMN_NAME.
  3. The loading role has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

What it does and where it works:

  • It adds new columns and drops NOT NULL from 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, set ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE.
  • It works with COPY INTO and Snowpipe. INSERT cannot 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 SchemaEvolutionRecord field, visible in INFORMATION_SCHEMA.COLUMNS, ACCOUNT_USAGE.COLUMNS, DESCRIBE TABLE and SHOW 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;
Table or queryCOPY INTO locationStageinternal or externalGET or cloud downloadFilesCSV, JSON, Parquet
OptionEffect
MAX_FILE_SIZETarget size per output file. Default 16 MB (16777216 bytes). The maximum supported is 5 GB.
SINGLE = TRUEOne output file. May slow the unload.
PARTITION BYWrites files into folders by an expression, such as a date
HEADER = TRUEIncludes column names in the file
OVERWRITE = TRUEReplaces existing files with the same name; other files stay; not supported with PARTITION BY
INCLUDE_QUERY_ID = TRUEAdds 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_FORMATCSV, 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_IF defaults to \N and FIELD_OPTIONALLY_ENCLOSED_BY defaults to NONE. Enclose strings with FIELD_OPTIONALLY_ENCLOSED_BY to keep empty strings distinct from NULLs; without it, and with EMPTY_FIELD_AS_NULL = TRUE (default), they look the same.
  • Give compressed files the extension that matches the compression, such as .gz for GZIP.
Watch out

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 INSERT to evolve the schema.
  • Using CREATE OR REPLACE on a table and losing its load history or grants, instead of SWAP or ALTER.
  • Using DELETE and then wondering why the same files will not reload. Load metadata is kept, so use FORCE or TRUNCATE.
  • Setting SINGLE = TRUE on a large unload and slowing it down.
  • Unloading without a unique path or INCLUDE_QUERY_ID and 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 WITH publishes a rebuilt table in one step. TRUNCATE clears load metadata, DELETE does not.
  • Schema evolution needs the table property, MATCH_BY_COLUMN_NAME and EVOLVE SCHEMA or ownership. It adds columns and drops NOT 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_ID and HEADER shape 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 uses MATCH_BY_COLUMN_NAME, and the role has EVOLVE SCHEMA or OWNERSHIP.

  • 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.