Numeric data types¶

Precision¶

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

Precision is approximately 15 digits. For example, for integers, the range is from -9007199254740991 to +9007199254740991 (-2⁵³ + 1 to +2⁵³ - 1). Floating-point values can range from approximately 10^-308 to 10⁺³⁰⁸. (Snowflake can represent more extreme values between approximately 10^-324 and 10^-308 with less precision.) For more details, see the Wikipedia article on double-precision numbers.

Snowflake supports the fixed-point 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 case-insensitive.

Comparison semantics for 'NaN' differ from the IEEE 754 standard in the following ways:

Rounding errors¶

Floating point operations can have small rounding errors in the least significant digits. Rounding errors can occur in any type of floating-point processing, including trigonometric, statistical, and geospatial functions.

The following are considerations for rounding errors:

• 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 (for example, SUM or AVG) process large numbers of rows. Casting to a fixed-point 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 (for example, Java, JavaScript, or Python) that runs inside Snowflake (for example, in UDFs and stored procedures).

• When comparing two floating-point numbers, Snowflake recommends comparing for approximate equality rather than exact equality.

It might be possible to avoid these types of approximation errors by using the exact DECFLOAT data type.

Use cases for the DECFLOAT data type¶

Use the DECFLOAT data type when you need exact decimal results and a wide, variable scale in the same column.

The DECFLOAT data type is appropriate for the following general use cases:

• You are ingesting data, and the scale of incoming numeric values is unknown or highly variable.

• You require exact numeric values; for example, ledgers, taxes, or compliance.

• You are migrating from systems that rely on the IEEE 754-decimal representation or 128-bit decimals. These migrations might be blocked by the precision or range limitations of other Snowflake data types.

• You want to avoid Number out of representable range errors when you sum, multiply, or divide high-precision numeric values.

For example, you can use the DECFLOAT data type for the following specific use cases:

• You are ingesting heterogeneously-scaled data from Oracle DECIMAL or DB2 DECFLOAT columns.

• You are performing financial modeling involving computations with scales of results that are hard to predict.

• You are running scientific measurements that swing from nano units to astronomical units.

You can continue to use the NUMBER data type for fixed-scale numeric columns or the FLOAT data type for high-throughput analytics where imprecise results are acceptable.

Usage notes for the DECFLOAT data type¶

• If an operation produces a result with more than 38 digits, the DECFLOAT value is rounded to 38-digit precision, with the least-significant digits rounded off according to the current rounding mode. Snowflake uses the half up rounding mode for DECFLOAT values.

• When specifying a DECFLOAT value or casting to a DECFLOAT value, avoid using numeric literals in SQL. If you do this, the values are interpreted as NUMBER or FLOAT values before being cast to a DECFLOAT value, which can result in range errors or loss of exactness. Use either string literals — such as SELECT '<value>'::DECFLOAT — or the DECFLOAT literal — such as SELECT DECFLOAT '<value>'.

• When operations mix DECFLOAT values and values of other numeric types, coercion prefers the DECFLOAT values. For example, when adding a value of NUMBER type and DECFLOAT type, the result is a DECFLOAT value.

• Use of the DECFLOAT type might cause storage consumption to increase.

Drivers and driver versions that support the DECFLOAT data type¶

The following Snowflake drivers and driver versions support the DECFLOAT data type. You might need to update your drivers to the versions that support DECFLOAT:

Unsupported drivers treat DECFLOAT values as TEXT values. For some drivers, a driver parameter must be set to map the DECFLOAT type to a language-native type. For more information, see Drivers.

Limitations for the DECFLOAT data type¶

The following limitations apply to the DECFLOAT type:

• DECFLOAT values can’t be stored in VARIANT, OBJECT, or ARRAY values. To cast a DECFLOAT value to a VARIANT value, you can first cast it to a VARCHAR value and then to a VARIANT value.

• DECFLOAT values aren’t supported in the following types of tables:

  • Tables in external formats, such as Iceberg

  • Hybrid tables

• The DECFLOAT data type isn’t supported in Snowflake Scripting stored procedures. However, it is supported in Snowflake Scripting user-defined functions (UDFs).

• The DECFLOAT data type isn’t supported in stored procedures or UDFs written in a language other than SQL (such as Python or Java).

• The DECFLOAT data type isn’t supported in Snowpark.

• Snowsight has limited support for the DECFLOAT data type.

• The following features don’t support the DECFLOAT data type:

  • Clustering keys

  • Differential privacy

  • Sensitive data classification

  • Search optimization service

• The NUMBER and FLOAT types might provide better performance than the DECFLOAT type.

Examples for the DECFLOAT data type¶

The following examples use the DECFLOAT data type:

• Show the differences between DECFLOAT and FLOAT

• Use DECFLOAT values with aggregate functions

CREATE OR REPLACE TABLE decfloat_sample (
  id INT,
  decfloat_val DECFLOAT,
  float_val FLOAT);

INSERT INTO decfloat_sample VALUES
  (
    1,
    DECFLOAT '123e7000',
    FLOAT '123e7000'
  ),
  (
    2,
    12345678901234567890123456789::DECFLOAT,
    12345678901234567890123456789::FLOAT
  ),
  (
    3,
    '-4.2e-5432'::DECFLOAT,
    '-4.2e-5432'::FLOAT
  ),
  (
    4,
    '1.00000000000000000000000000000000000014'::DECFLOAT,
    '1.00000000000000000000000000000000000014'::FLOAT
  ),
  (
    5,
    '1.00000000000000000000000000000000000015'::DECFLOAT,
    '1.00000000000000000000000000000000000015'::FLOAT
  );

DESC TABLE decfloat_sample;

+--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name         | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| ID           | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DECFLOAT_VAL | DECFLOAT(38) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FLOAT_VAL    | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

SELECT * FROM decfloat_sample;

+----+-----------------------------------------+------------------------+
| ID | DECFLOAT_VAL                            |              FLOAT_VAL |
|----+-----------------------------------------+------------------------|
|  1 | 1.23e7002                               | inf                    |
|  2 | 12345678901234567890123456789           |   1.23456789012346e+28 |
|  3 | -4.2e-5432                              |  -0                    |
|  4 | 1.0000000000000000000000000000000000001 |   1                    |
|  5 | 1.0000000000000000000000000000000000002 |   1                    |
+----+-----------------------------------------+------------------------+

CREATE OR REPLACE TABLE decfloat_agg_sample (decfloat_val DECFLOAT);

INSERT INTO decfloat_agg_sample VALUES
  (DECFLOAT '1e1000'),
  (DECFLOAT '-2.47e999'),
  (DECFLOAT '22e-75');

SELECT SUM(decfloat_val),
       AVG(decfloat_val),
       MAX(decfloat_val),
       MIN(decfloat_val)
  FROM decfloat_agg_sample;

+-------------------+-------------------+-------------------+-------------------+
| SUM(DECFLOAT_VAL) | AVG(DECFLOAT_VAL) | MAX(DECFLOAT_VAL) | MIN(DECFLOAT_VAL) |
|-------------------+-------------------+-------------------+-------------------|
| 7.53e999          | 2.51e999          | 1e1000            | -2.47e999         |
+-------------------+-------------------+-------------------+-------------------+