Data types for Apache Iceberg™ tables¶

Approximate types¶

If your table uses an Iceberg data type that Snowflake doesn’t support an exact match for, Snowflake uses an approximate Snowflake type. This type mapping affects column values for converted tables and Iceberg tables that use Snowflake as the catalog.

For example, consider a table with a column of Iceberg type int. Snowflake processes the column values using the Snowflake data type NUMBER(10,0).

NUMBER(10,0) has a range of (-9,999,999,999, +9,999,999,999), but int has a more limited range of (-2,147,483,648, +2,147,483,647). If you try to insert a value of 3,000,000,000 into that column, Snowflake returns an out-of-range error message.

For details about approximate types, see the notes in the Supported data types table.

Supported data types¶

The tables in this section show the relationship between Iceberg data types and Snowflake data types. They use the following columns:

Iceberg type:

The data type defined in the Apache Iceberg specification. When you use Snowflake as the catalog, Snowflake writes the Iceberg type to your table data files so that your tables remain interoperable across different compute engines.

Snowflake type:

The Snowflake data type that is used to process and return table data. For example, if your schema specifies the Iceberg type timestamp, Snowflake processes and returns values using the Snowflake data type TIMESTAMP_NTZ(6) with microsecond precision.

Notes:

Additional usage notes, including notes for working with approximate types.

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2026_02');

INSERT INTO geog_points
  SELECT TO_GEOGRAPHY('POINT(-122.3861109 37.61637595)');

INSERT INTO geog_points
  SELECT 'POINT(-122.3861109 37.61637595)';

CREATE ICEBERG TABLE geo_points (geom GEOMETRY)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume'
  BASE_LOCATION = 'us_states'
  ICEBERG_VERSION = 3;

CREATE ICEBERG TABLE geo_points (geom GEOMETRY(4269))
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume'
  BASE_LOCATION = 'us_states'
  ICEBERG_VERSION = 3;

INSERT INTO geo_points
  SELECT TO_GEOMETRY('POINT(-122.3861109 37.61637595)');

INSERT INTO geo_points
  SELECT 'POINT(-122.3861109 37.61637595)';

INSERT INTO geo_points
  SELECT TO_GEOMETRY('POINT(-122.3861109 37.61637595)', 4326);

CREATE ICEBERG TABLE sensor_readings (
    reading_ntz TIMESTAMP_NTZ(9),
    reading_ltz TIMESTAMP_LTZ(9))
  ICEBERG_VERSION = 3;

CREATE ICEBERG TABLE car_sales (record VARIANT)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume'
  BASE_LOCATION = 'car_sales'
  ICEBERG_VERSION = 3;

USE DATABASE my_catalog_linked_db;

USE SCHEMA my_namespace;

CREATE ICEBERG TABLE car_sales (record VARIANT)
ICEBERG_VERSION = 3;

INSERT INTO car_sales SELECT
  PARSE_JSON(
    '{
        "date" : "2017-04-28",
        "dealership" : "Valley View Auto Sales",
        "salesperson" : {
          "id": "55",
          "name": "John Salesperson"
        },
        "customer" : [
          {"name": "Alice Doe", "phone": "14151234567", "address": "San Francisco, CA"},
          {"name": "Bob Doe", "phone": "14151234567", "address": "San Francisco, CA"}
        ],
        "vehicle" : [
          {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
        ]
      }'
    );

+--------------------------------------------+
| RECORD                                     |
|--------------------------------------------|
| {                                          |
|    "customer": [                           |
|      {                                     |
|        "address": "San Francisco, CA",     |
|        "name": "Alice Doe",                |
|        "phone": "14151234567"              |
|      },                                    |
|      {                                     |
|        "address": "San Francisco, CA",     |
|        "name": "Bob Doe",                  |
|        "phone": "14151234567"              |
|      }                                     |
|    ],                                      |
|    "date": "2017-04-28",                   |
|    "dealership": "Valley View Auto Sales", |
|    "salesperson": {                        |
|      "id": "55",                           |
|      "name": "John Salesperson"            |
|    },                                      |
|    "vehicle": [                            |
|      {                                     |
|        "extras": [                         |
|          "ext warranty",                   |
|          "paint protection"                |
|        ],                                  |
|        "make": "Honda",                    |
|        "model": "Civic",                   |
|        "price": "20275",                   |
|        "year": "2017"                      |
|      }                                     |
|    ]                                       |
|  }                                         |
+--------------------------------------------+

SELECT record:salesperson.name
  FROM car_sales
  ORDER BY 1;

+-------------------------+
| RECORD:SALESPERSON.NAME |
|-------------------------|
| "John Salesperson"      |
+-------------------------+

Delta data types¶

The following table shows how Delta data types map to Snowflake data types for Iceberg tables created from Delta table files.

The following table shows how Delta nested data types map to Snowflake data types.

Considerations¶

Consider the following items when you work with data types for Iceberg tables:

• Converting a table with columns that use the following Iceberg data types is not supported:

  • uuid

  • fixed(L)

• For tables that use Snowflake as the catalog, creating a table that uses the Iceberg uuid data type is not supported.

• For tables that use an external catalog, you can’t create Iceberg v3 tables with structured type columns, which includes OBJECT, ARRAY, or MAP. For example, you can’t use CREATE ICEBERG TABLE … AS SELECT (CTAS) to create an externally managed Iceberg v3 table with structured type columns.

  You can create Snowflake-managed Iceberg v3 tables with structured type columns.

• For all Iceberg table types:

  • Structured type columns support a maximum of 1000 sub-columns.

  • Iceberg supports microsecond precision for time and timestamp types. As a result, you can’t create an Iceberg table in Snowflake that uses another precision like millisecond or nanosecond.

  • You can’t use float or double as primary keys (in accordance with the Apache Iceberg spec).

  • For Parquet files that use the LIST logical type, be aware of the following:

    • The three-level annotation structure with the element keyword is supported. For more information, see Parquet Logical Type Definitions. If your Parquet file uses an obsolete format with the array keyword, you must regenerate your data based on the supported format.

• For tables created from Delta files, be aware of the following:

  • Parquet files (data files for Delta tables) that use any of the following features or data types aren’t supported:

    • Field IDs.

    • The INTERVAL data type.

    • The DECIMAL data type with precision higher than 38.

    • LIST or MAP types with one-level or two-level representation.

    • Unsigned integer types (INT(signed = false)).

    • The FLOAT16 data type.

  • You can use the Parquet physical type int96 for TIMESTAMP, but Snowflake doesn’t support int96 for TIMESTAMP_NTZ.