Numeric data types¶
This topic describes the numeric data types supported in Snowflake, along with the supported formats for numeric constants/literals.
Data types for fixedpoint numbers¶
Snowflake supports the following data types for fixedpoint numbers.
NUMBER¶
Numbers up to 38 digits, with an optional precision and scale:
 Precision:
Total number of digits allowed.
 Scale:
Number of digits allowed to the right of the decimal point.
By default, precision is 38
and scale is 0
(i.e. NUMBER(38, 0)
). Note that precision limits the range of values that can be inserted into (or cast to) columns
of a given type. For example, the value 999
fits into NUMBER(38,0)
but not into NUMBER(2,0)
.
The maximum scale (number of digits to the right of the decimal point) is 37. Numbers that have fewer than 38 significant digits, but whose least significant digit is past the 37th decimal place, for example 0.0000000000000000000000000000000000000012 (1.2e39), cannot be represented without losing some digits of precision.
Note
If data is converted to another data type with lower precision, then back to the higherprecision form, the data can lose precision. For example, you lose precision if you convert a NUMBER(38,37) value to DOUBLE (which has a precision of approximately 15 decimal digits), and then back to NUMBER.
Snowflake also supports the FLOAT data type, which allows a wider range of values, although with less precision.
DECIMAL , DEC , NUMERIC¶
Synonymous with NUMBER.
INT , INTEGER , BIGINT , SMALLINT , TINYINT , BYTEINT¶
Synonymous with NUMBER, except that precision and scale cannot be specified (i.e. always defaults to NUMBER(38, 0)
).
Therefore, for all INTEGER data types, the range of values is all integer values from
99999999999999999999999999999999999999 to +99999999999999999999999999999999999999 (inclusive).
The various names (TINYINT, etc.) are to simplify porting from other systems and to suggest the expected range of values for a column of the specified type.
Impact of precision and scale on storage size¶
Precision (total number of digits) does not impact storage. In other words, the storage requirements for the same number in columns with different precisions, such as NUMBER(2,0)
and
NUMBER(38,0)
, are the same. For each micropartition, Snowflake determines the minimum and maximum values for a given column and
uses that information to determine the storage size for all values for that column in
the partition. For example:
If a column contains only values between
128
and+127
, then each of the values consumes 1 byte (uncompressed).If the largest value in the column is
10000000
, then each of the values consumes 4 bytes (uncompressed).
However, scale (number of digits following the decimal point) does have an impact on storage. For example, the same value stored in a column of type NUMBER(10,5)
consumes more space
than NUMBER(5,0)
. Also, processing values with a larger scale could be slightly slower and consume more memory.
To save space, Snowflake compresses values before writing them to storage. The amount of compression depends on the data values and other factors.
Examples of fixedpoint data types in a table¶
CREATE OR REPLACE TABLE test_fixed(num NUMBER,
num10 NUMBER(10,1),
dec DECIMAL(20,2),
numeric NUMERIC(30,3),
int INT,
integer INTEGER
);
DESC TABLE test_fixed;
+++++++++++
 name  type  kind  null?  default  primary key  unique key  check  expression  comment 
+++++++++
 NUM  NUMBER(38,0)  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 NUM10  NUMBER(10,1)  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 DEC  NUMBER(20,2)  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 NUMERIC  NUMBER(30,3)  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 INT  NUMBER(38,0)  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 INTEGER  NUMBER(38,0)  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
+++++++++++
Data types for floatingpoint numbers¶
Snowflake supports the following data types for floatingpoint numbers.
FLOAT , FLOAT4 , FLOAT8¶
The names FLOAT, FLOAT4, and FLOAT8 are for compatibility with other systems; Snowflake treats all three as 64bit floatingpoint numbers.
Precision¶
Snowflake uses doubleprecision (64 bit) IEEE 754 floatingpoint numbers.
Precision is approximately 15 digits. For example, for integers, the range is from 9007199254740991 to +9007199254740991 (2^{53} + 1 to +2^{53}  1). Floatingpoint values can range from approximately 10^{308} to 10^{+308}. (More extreme values between approximately 10^{324} and 10^{308} can be represented with less precision.) For more details, see the Wikipedia article on doubleprecision numbers.
Snowflake supports the fixedpoint data type NUMBER, which allows greater precision, although a smaller range of exponents.
Special values¶
Snowflake supports the following special values for FLOAT:
'NaN'
(Not A Number).'inf'
(infinity).'inf'
(negative infinity).
The symbols 'NaN'
, 'inf'
, and 'inf'
must be in single quotes, and are caseinsensitive.
Comparison semantics for 'NaN'
differ from the IEEE 754 standard in the following ways:
Condition 
Snowflake 
IEEE 754 
Comment 




In Snowflake, 



Snowflake treats 
Rounding errors¶
Floating point operations can have small rounding errors in the least significant digit(s). Rounding errors can occur in any type of floatingpoint processing, including trigonometric functions, statistical, and geospatial functions.
Errors can vary each time the query is executed.
Errors can be larger when operands have different precision or scale.
Errors can accumulate, especially when aggregate functions (e.g. SUM() or AVG()) process large numbers of rows. Casting to a fixedpoint data type before aggregating can reduce or eliminate these errors.
Rounding errors can occur not only when working with SQL, but also when working with other code (e.g. Java, JavaScript, or Python) that runs inside Snowflake (e.g. in UDFs and stored procedures).
When comparing two floatingpoint numbers, Snowflake recommends comparing for approximate equality rather than exact equality.
DOUBLE , DOUBLE PRECISION , REAL¶
Synonymous with FLOAT.
Examples of floatingpoint data types in a table¶
CREATE OR REPLACE TABLE test_float(d DOUBLE,
f FLOAT,
dp DOUBLE PRECISION,
r REAL
);
DESC TABLE test_float;
+++++++++++
 name  type  kind  null?  default  primary key  unique key  check  expression  comment 
+++++++++
 D  FLOAT  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 F  FLOAT  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 DP  FLOAT  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
 R  FLOAT  COLUMN  Y  NULL  N  N  NULL  NULL  NULL 
+++++++++++
Note
The DESCRIBE TABLE
command’s “type” column displays the data type “FLOAT” not only for FLOAT, but also for synonyms
of FLOAT (e.g. DOUBLE, DOUBLE PRECISION, and REAL).
Numeric constants¶
Constants (also known as literals) refers to fixed data values. The following formats are supported for numeric constants:
[+][digits][.digits][e[+]digits]
Where:
+
or
indicates a positive or negative value. The default is positive.digits
is one or more digits from 0 to 9.e
(orE
) indicates an exponent in scientific notation. At least one digit must follow the exponent marker if present.
The following numbers are all examples of supported numeric constants:
15
+1.34
0.2
15e03
1.234E2
1.234E+2
1