Numeric data types

Data Types and Functions in Snowflake

Jake Roach

Field Data Engineer

Numeric data types

NUMBER

Control the size of numeric values
DECIMAL, INTEGER are synonymous with NUMBER

126          -- Integers
7.99         -- Dollar amounts
.000191      -- Decimals
-46.88       -- Negative values
563719100    -- ID's

...

1.234E+2     -- Numeric constants

Precision and scale

NUMBER(<precision>, <scale>)

Precision is the total number of digits in the number

Scale is the number of digits after the decimal point

-- Precision: 4, Scale: 2
NUMBER(4, 2)


1.75    -- Good!
624.99  -- Won't fit

Precision and scale

-- Precision: 3, Scale: 0
NUMBER(3, 0)

42     -- Good!
1000   -- Won't fit
41.99  -- Won't fit

Default NUMBER is NUMBER(38, 0)

Defining numeric data types

CREATE TABLE sales (
    id NUMBER,               -- Traditional numeric data

    price NUMBER(5, 2),      -- Dollar values up to $999.99

    quantity NUMBER(4, 0)    -- Integer values less than 10,000

);

DESCRIBE TABLE schema.table;

                         name    |     type     |   kind   | ...  
                      ---------- | ------------ | -------- | ---  
                          id     | NUMBER(38,0) |  COLUMN  | ...  
                         price   | NUMBER(5,2)  |  COLUMN  | ...  
                       quantity  | NUMBER(4,0)  |  COLUMN  | ...

FLOAT data type

Used in Scientific and statistical calculations
"Approximate" numeric type
Possibility of rounding errors
Fast and can handle extreme scale!

0.0000000120056

CREATE TABLE metrics (
    id NUMBER,
    accuracy FLOAT
);

DESCRIBE TABLE schema.table;

     name    |       type     | ...  
  ---------- | -------------- | ---  
      id     |  NUMBER(38,0)  | ...  
   accuracy  |  FLOAT         | ...

Manipulating numeric data

Numeric data can be manipulated in a number of different ways

Comparing values using tools such as =, !=, <, or >.
Performing arithmetic operations, such as +, -, /, or *.
Aggregating data with SUM, AVG, or COUNT.
Calculating summary statistics with MEDIAN, STDDEV, and CORR.

... and tons more!

Let's practice!

Data Types and Functions in Snowflake