Snowflake SQL data types

Introduction to Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Common data types

Category	Data types
Text/string	`VARCHAR`, `CHAR`, `TEXT`

Common data types

Category	Data types
Text/string	`VARCHAR`, `CHAR`, `TEXT`
Numeric	`INTEGER`

Common data types

Category	Data types
Text/string	`VARCHAR`, `CHAR`, `TEXT`
Numeric	`INTEGER`
Boolean	`BOOLEAN`

Common data types

Category	Data types
Text/string	`VARCHAR`, `CHAR`, `TEXT`
Numeric	`INTEGER`
Boolean	`BOOLEAN`
Date/time	`DATE`, `TIME`, `TIMESTAMP`

¹ https://docs.snowflake.com/en/sql-reference/intro-summary-data-types

Snowflake SQL data types - NUMBER

`NUMBER(p, s)`

NUMERIC works in Snowflake as an alias for NUMBER
p = precision; s = scale
Max p and s values: 38
- Exceeding will cause rounding!

Snowflake SQL data types - TIMESTAMP_LTZ

TIMESTAMP_LTZ
- Combines DATE and TIME with local time zone
- Format: YYYY-MM-DD HH:MI:SS

CREATE TABLE orders (
  -- Timestamp with local time zone
  order_timestamp TIMESTAMP_LTZ
  )

Timestamp local time zone data

Data type conversion - What?

Converting data from one type to another

Order quantity column data from text to number

Data type conversion - Why?

Improving performance
Data accuracy and consistency
Data quality

Data type conversion - How?

CAST

Syntax:
- CAST( <source_data/column> AS <target_data_type> )
- CAST('80' AS INT)
::

Syntax:
- <source_data/column>::<target_data_type>
- '80'::INT

CAST

SELECT CAST(order_timestamp AS DATE) 
       AS order_date 
FROM orders

CAST results

Before casting order_timestamp_ltz

After casting order date data

Conversion functions

Examples: TO_VARCHAR, TO_DATE, etc.

TO_VARCHAR

TO_VARCHAR( <expr> )
- expr - numeric, timestamp, etc.
- Result: VARCHAR

Example:

SELECT TO_VARCHAR(86)

Result:

86

Checking data types

DESC TABLE orders

name        type            kind     null?    default     primary key    
ORDER_ID    NUMBER(38,0)    COLUMN   N        null        Y    
ORDER_DATE  DATE            COLUMN   Y        null        N    
ORDER_TIME  TIME(9)         COLUMN   Y        null        N

Let's practice!

Introduction to Snowflake SQL