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

The data type Time does not support the clause with or without out Time Zone in Snowflake

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

Related EWIs

1. MSCEWI1002: Removed next statement, not applicable in Snowflake.
2. MSCEWI1096: TIME ZONE not supported for time data type.