Data Types

Snowflake supports most basic SQL data types (with some restrictions) for use in columns, local variables, expressions, parameters, and any other appropriate/suitable locations.

Exact and approximate numerics

BIGINT

BIGINT

Snowflake's BIGINT is an alias for NUMBER(38,0)

DECIMAL

DECIMAL

​Snowflake's DECIMAL is synonymous with NUMBER

DECFLOAT

VARCHAR

DECFLOAT data type is not supported in Snowflake. VARCHAR is used instead.

The default is the maximum allowed length (16,777,216).

DOUBLE PRECISION

DOUBLE PRECISION

​Snowflake's DOUBLE PRECISION is synonymous with FLOAT

FLOAT

FLOAT

​This data type behaves equally on both systems.

Precision 7-15 digits, float (1-24)

Storage 4 - 8 bytes, float (25-53)

INT

INT

Snowflake's INT is an alias for NUMBER(38,0)

INTEGER

INTEGER

Snowflake's INTEGER is synonymous with INT.

REAL​

REAL

Snowflake's REAL is synonymous with FLOAT

SMALLINT

SMALLINT​

​This data type behaves equally

NUMERIC

NUMERIC

​Snowflake's NUMERIC is synonymous with NUMBER

Character strings

ANSI SQL
Snowflake
Notes

VARCHAR

VARCHAR

Snowflake VARCHAR holds Unicode UTF-8 characters. If no length is specified, the default is the maximum allowed length (16,777,216).

CHAR

CHAR

Snowflake uses this data type as synonymous with VARCHAR, except that if the length is not specified, CHAR(1) is the default.

CHAR VARYING

CHAR VARYING

Snowflake uses this data type as synonymous with VARCHAR.

CHARACTER

CHARACTER

Snowflake uses this data type as synonymous with VARCHAR.

CHARACTER VARYING

CHARACTER VARYING

Snowflake uses this data type as synonymous with VARCHAR.

Character Large Object

ANSI SQL
Snowflake
Notes

CHAR LARGE OBJECT

VARCHAR

CHAR LARGE OBJECT data type is not supported in Snowflake. VARCHAR is used instead.

The default is the maximum allowed length (16,777,216).

CHARACTER LARGE OBJECT

VARCHAR

CHARACTER LARGE OBJECT data type is not supported in Snowflake. VARCHAR is used instead.

The default is the maximum allowed length (16,777,216).

CLOB, CLOB(SIZE)

VARCHAR

CLOB data type is not supported in Snowflake. VARCHAR is used instead.

The default is the maximum allowed length (16,777,216).

Because of platform differences in terms of encoding and how strings are stored in each database, the safest translation for a Character Large Object data type with a specific data size is an unlimited VARCHAR datatype.

National Character String

ANSI SQL
Snowflake
Notes

NATIONAL CHARACTER,

NATIONAL CHAR

NCHAR

If Characters or Octets is present in the data size, it will be commented since it is not supported in Snowflake.

NATIONAL CHARACTER VARYING,

NATIONAL CHAR VARYING,

NCHAR VARYING

NCHAR VARYING

If Characters or Octets is present in the data size, it will be commented since it is not supported in Snowflake.

National Character Large Object

ANSI SQL
Snowflake
Notes

NATIONAL CHARACTER LARGE OBJECT

VARCHAR

Binary Large Object strings

ANSI SQL
Snowflake
Notes

BLOB

BINARY

​BLOB data type is not supported in Snowflake. BINARY is used instead.

The default is the maximum allowed length (8,388,608 bytes).

BINARY LARGE OBJECT, BINARY LARGE OBJECT(SIZE)

BINARY

​BINARY LARGE OBJECT data type is not supported in Snowflake. BINARY is used instead.

The default is the maximum allowed length (8,388,608 bytes).

Because of platform differences in terms of encoding and how strings are stored in each database, the safest translation for a Binary Large Object data type with a specific data size is an unlimited BINARY datatype.

Boolean

ANSI SQL
Snowflake
Notes

BOOLEAN

BOOLEAN

BOOLEAN can have TRUE or FALSE values.

Date Time

ANSI SQL
Snowflake
Notes

DATE

DATE

DATE accepts dates in the most common forms (YYYY-MM-DD, DD-MON-YYYY, etc.)

TIME

TIME

Storing times in the form of HH:MI:SS. Time precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.

TIMESTAMP

TIMESTAMP

Timestamp precision can range from 0 (seconds) to 9 (nanoseconds).

TIMESTAMP WITH TIME ZONE

TIMESTAMP_TZ

TIMESTAMP_TZ internally stores UTC time together with an associated time zone offset.

TIMESTAMP WITHOUT TIME ZONE

TIMESTAMP_NTZ

TIMESTAMP_NTZ internally stores “wallclock” time with a specified precision.

Time with or without time zone

ANSI SQL Input Code

CREATE TABLE TABLE_TEST (
    COLNAME1 TIME (9) WITH TIME ZONE,
    COLNAME2 TIME WITHOUT TIME ZONE
);

Snowflake Output Code

CREATE TABLE PUBLIC.TABLE_TEST (
    COLNAME1 TIME (9)
-- ** MSC-ERROR - MSCEWI1096 - TIME ZONE NOT SUPPORTED FOR TIME DATA TYPE **
--                      WITH TIME ZONE
                                    ,
    COLNAME2 TIME
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. WITHOUT TIME ZONE **
--                  WITHOUT TIME ZONE
);

Interval

ANSI SQL
Snowflake
Notes

INTERVAL

VARCHAR(21)

There's not a direct equivalent in Snowflake, to learn more about this transformation, please refer to Interval section

For information on the Interval data type referred to Interval section

  1. MSCEWI1002: Removed next statement, not applicable in Snowflake.

  2. MSCEWI1096: TIME ZONE not supported for time data type.

Last updated

Was this helpful?