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. You can also use the Parquet physical type `int96` for timestamps. Snowflake translates `timestamp` to microseconds (per the Apache Iceberg table specification).
`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.
- 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.