Numeric Data Types

This topic describes the numeric data types supported in Snowflake, along with the supported formats for numeric constants/literals.

In this Topic:

Data Types for Fixed-point Numbers

Snowflake supports the following data types for fixed-point numbers.

NUMBER

Numbers up to 38 digits, with a specified 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).

DECIMAL , NUMERIC

Synonymous with NUMBER.

INT , INTEGER , BIGINT , SMALLINT , TINYINT , BYTEINT

All integer data types are synonymous with NUMBER, except that precision and scale cannot be specified, i.e. always defaults to NUMBER(38, 0).

Impact of Precision and Scale on Storage Size

Precision (total number of digits) does not impact storage, i.e. the storage requirements for the same values in columns with different precisions, such as NUMBER(2,0) and NUMBER(38,0), are the same. For each micro-partition, Snowflake determines the minimum and maximum values for a given column and uses that range to store all values for that column in the partition. For example:

  • If a column contains 5 values (e.g, 0,1,2,3,4), each of the 5 values consumes 1 byte (uncompressed; actual storage size is reduced due to compression).
  • If a column contains longer values (e.g., 0,1,2,3,4,10000000), 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.

Fixed-point Examples in Table Columns

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 Floating Point Numbers

Snowflake supports the following data types for floating point numbers.

FLOAT , FLOAT4 , FLOAT8

Snowflake uses double-precision (64 bit) IEEE 754 floating point numbers.

DOUBLE , DOUBLE PRECISION , REAL

Synonymous with FLOAT.

Floating Point Examples in Table Columns

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

DOUBLE, FLOAT, DOUBLE PRECISION, and REAL columns are displayed as FLOAT, but stored as DOUBLE. This is a known issue in Snowflake.

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 (or E) indicates an exponent in scientific notation. At least one digit must follow the exponent marker if present.

Following are all examples of supported numeric constants:

15
+1.34
0.2
15e-03
1.234E2
1.234E+2
-1