Retrieve archived data

Read archived data by using the CREATE TABLE … FROM ARCHIVE OF command.

For example, the following statement creates a new table from archived rows where the value in the event_timestamp column is between January 15 and January 20 of 2023:

CREATE TABLE my_table
  FROM ARCHIVE OF my_source_table AS st
  WHERE st.event_timestamp BETWEEN '01/15/2023' AND '01/20/2023';

For syntax details and parameter descriptions, see CREATE TABLE … FROM ARCHIVE OF in the CREATE TABLE documentation.

Note

  • Using this command requires the OWNERSHIP privilege on the source table.

  • Specifying column definitions, policies, tags, or other constraints isn’t supported. Snowflake automatically retrieves the table schema, policies, tags, and constraints from the source table.

  • The WHERE clause is required. Reading archived data is expensive, and should be performed infrequently. Filtering results using the WHERE clause helps you minimize costs by ensuring that Snowflake reads only the data that you require from archival storage.

  • To estimate the number of files that Snowflake will retrieve from archive storage, run the EXPLAIN command before this operation. The output includes a createTableFromArchiveData operation and displays ARCHIVE OF <table> in the objects column for the TableScan operation. For more information, see Estimate retrieval costs with EXPLAIN.

  • To see a history of data retrieval from archive storage, use the ARCHIVE_STORAGE_DATA_RETRIEVAL_USAGE_HISTORY view.

  • To retrieve data from the COLD tier of archive storage, Snowflake must first restore the files from external cloud storage. This process can take up to 48 hours.

    To support this process, set the following parameters appropriately:

    COLD storage tier restore operations support a maximum of 1 million files per restore operation.

  • If you cancel a CREATE TABLE operation that retrieves data from archive storage, you might still incur retrieval costs.

View archive metadata before retrieval

Before retrieving archived data, you can inspect metadata about the archive to understand what data is available. Use the SYSTEM$GET_TABLE_ARCHIVE_METADATA function to view:

  • Total row count in the archive

  • Column data types

  • Minimum and maximum values for numeric and timestamp columns

This helps you decide which data to retrieve without incurring retrieval costs.

Note

The table owner or an account administrator (a user with the ACCOUNTADMIN role) who has access to the table can execute this function.

Estimate retrieval costs with EXPLAIN

To estimate how many files Snowflake will retrieve from archive storage, use the EXPLAIN command.

The command output includes the following data:

  • A createTableFromArchiveData operation in the operation column.

  • ARCHIVE OF <table> in the objects column for the TableScan operation.

  • The number of partitions that will be retrieved in the assignedPartitions column for the archive TableScan operation. This value indicates the number of partitions that Snowflake will restore from cold tier to retrieve the data from archive storage.

  • The number of bytes that will be retrieved in the bytesAssigned column.

For example:

EXPLAIN
CREATE TABLE my_table
  FROM ARCHIVE OF my_source_table AS st
  WHERE st.event_timestamp BETWEEN '01/15/2023' AND '01/20/2023';