Data Types

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

Applies to

• Hive SQL

• Spark SQL

• Databricks SQL

Exact and approximate numerics

TINYINT, SHORT	SMALLINT	​Snowflake's SMALLINT has a larger range (-32768 to +32767) than Spark's TINYINT (-128 to +127). This should generally be a safe transformation.
SMALLINT	SMALLINT	Direct equivalent in terms of range.
INT, INTEGER	INT, INTEGER	​Direct equivalent in terms of range.
BIGINT	BIGINT​	Direct equivalent in terms of range.
DECIMAL(p, s)​	NUMBER(p, s)	Snowflake's NUMBER(p, s) is the direct equivalent for fixed-precision and scale numbers. p is the precision (total number of digits) and s is the scale (number of digits to the right of the decimal point).
NUMERIC(p, s)	NUMBER(p, s)	Synonym for DECIMAL(p, s), maps directly to Snowflake's NUMBER(p, s).
FLOAT	FLOAT	Direct equivalent in terms of range.
DOUBLE, DOUBLE PRECISION	DOUBLE	Generally a good equivalent for double-precision floating-point numbers.
REAL	REAL	If REAL in your Spark context is strictly single-precision, be mindful of potential precision differences.

Date and time

Hive-Spark-Databricks SQL	Snowflake	Notes
DATE	DATE	Direct equivalent for storing calendar dates (year, month, day).
TIMESTAMP	TIMESTAMP_NTZ	Snowflake offers several timestamp variations. TIMESTAMP_NTZ (no time zone) is often the best general equivalent if your Spark TIMESTAMP doesn't have specific time zone information tied to the data itself.

Character strings

Hive-Spark-Databricks SQL	Snowflake	Notes
STRING	VARCHAR	​Snowflake's VARCHAR is the most common and flexible string type. It can store variable-length strings.
VARCHAR(n)​	VARCHAR(n)	Direct equivalent for variable-length strings with a maximum length.
CHAR(n)	CHAR(n)	Direct equivalent for fixed-length strings.

Binary strings

Hive-Spark-Databricks SQL	Snowflake	Notes
BINARY	​BINARY	Direct equivalent for storing raw byte sequences.

Boolean type

Hive-Spark-Databricks SQL	Snowflake	Notes
BOOLEAN, BOOL	​BOOLEAN	Direct equivalent for storing boolean (TRUE/FALSE) values.

Complex type

Hive-Spark-Databricks SQL	Snowflake	Notes
ARRAY<DataType>	​ARRAY	Snowflake's ARRAY type can store ordered lists of elements of a specified data type. The dataType within the array should also be mapped accordingly.
MAP<keyType, valueType>	VARIANT
STRUCT<name: dataType, ...>	VARIANT
INTERVAL	VARCHAR(30)	INTERVAL data type is not supported in Snowflake. VARCHAR is used instead.