Iceberg table data types

This topic provides information about how data types work for Iceberg tables in Snowflake.

How data types work for Iceberg tables

Snowflake supports most of the data types defined by the Apache Iceberg specification, and writes Iceberg data types to table files so that your Iceberg tables remain interoperable across different compute engines when you use Snowflake as the catalog.

For an overview of the Iceberg data types that Snowflake supports, see Supported data types for Iceberg tables.

Use Iceberg types to define columns

When you create an Iceberg table that uses Snowflake as the catalog, you can use Iceberg data types to define columns.

For example:

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table (
    boolean_col boolean,
    int_col int,
    long_col long,
    float_col float,
    double_col double,
    decimal_col decimal(10,5),
    string_col string,
    fixed_col fixed(10),
    binary_col binary,
    date_col date,
    time_col time,
    timestamp_ntz_col timestamp_ntz(6),
    timestamp_ltz_col timestamp_ltz(6)
  )
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_ext_vol'
  BASE_LOCATION = 'my/relative/path/from/extvol';
Copy

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 for Iceberg tables table.

Supported data types for Iceberg tables

The following table shows the relationship between Iceberg data types and Snowflake data types. It uses 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.

Iceberg data type

Snowflake data type

Notes

boolean

BOOLEAN

int (32-bit signed integer)

NUMBER(10,0)

Inserting a 10-digit number smaller than the minimum or larger than the maximum 32-bit signed integer value results in an out-of-range error.

long (64-bit signed integer)

NUMBER(19,0)

Inserting a 19-digit number smaller than the minimum or larger than the maximum 64-bit signed integer value results in an out-of-range error.

float (single-precision 32-bit IEEE 754 floating point)

FLOAT

Synonymous with the Snowflake DOUBLE data type. Snowflake treats all floating-point numbers as double-precision 64-bit floating-point numbers, but writes Iceberg floats as 32-bit floating-point numbers in table data files.

Narrowing conversions from 64 bits to 32 bits results in precision loss.

double (double-precision 64-bit IEEE 754 floating point)

FLOAT

Synonymous with the Snowflake DOUBLE data type. Snowflake treats all floating-point numbers as double-precision 64-bit floating-point numbers.

decimal(P,S)

NUMBER(P,S)

date

DATE

time

TIME(6)

Microsecond precision. Iceberg tables don’t support using another precision like millisecond or nanosecond.

timestamp

TIMESTAMP_NTZ(6)

Microsecond precision. Iceberg tables don’t support using another precision like millisecond or nanosecond.

Snowflake doesn’t support using the deprecated Parquet physical type int96 for timestamps.

timestamptz

TIMESTAMP_LTZ(6)

Microsecond precision. Iceberg tables don’t support using another precision like millisecond or nanosecond.

string

VARCHAR(16777216)

uuid

BINARY(16)

The uuid data type isn’t supported for tables that use Snowflake as the catalog, or for converted tables.

When you use an external catalog or create a table from files in object storage, Snowflake maps the uuid Iceberg type to the BINARY(16) Snowflake type.

fixed(L)

BINARY(L)

You can create an Iceberg table using this type, but you can’t convert a table that has a column of type fixed(L).

struct

Structured OBJECT

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

list

Structured ARRAY

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

map

MAP

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

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 all Iceberg table types:

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

    • Using the deprecated Parquet physical type int96 for timestamps is not supported.

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