Skip to main content

🧮 Data Types


❄️ Snowflake Data Types — Complete Guide

Data types determine what kind of values can be stored in each column of a table.
Snowflake provides a rich and flexible set of data types designed for:

  • 💼 Analytical workloads
  • 📦 Semi-structured data (JSON, Avro, Parquet, XML)
  • ☁️ Cloud-native platforms and microservices
  • 🗃️ Data lakes, ELT pipelines & enterprise data warehousing

Choosing the right data type ensures performance, storage efficiency, accuracy, and cost optimization.


📚 Common Snowflake Data Types — Categorized & Explained

CategoryTypes
Exact NumericNUMBER, DECIMAL, NUMERIC
Integer TypesINTEGER, INT, BIGINT, SMALLINT, TINYINT
Approx NumericFLOAT, FLOAT4, FLOAT8, REAL, DOUBLE
StringVARCHAR, STRING, TEXT, CHAR, CHARACTER, NCHAR
BinaryBINARY
DateDATE
TimeTIME
TimestampTIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ
BooleanBOOLEAN
Semi-StructuredVARIANT, OBJECT, ARRAY

🔢 Numeric Data Types in Snowflake

Numeric types fall into three categories:

1️⃣ Exact Numeric Types
2️⃣ Integer Types
3️⃣ Approximate Numeric Types


1️⃣ Exact Numeric Types

NUMBER / DECIMAL / NUMERIC​

Used when precision must be 100% accurate.

  • 💰 Finance / money
  • 🔢 Counters
  • 🆔 Primary keys
  • 📏 Measurements
  • 📊 Analytics

NUMBER(precision, scale)​

TermMeaning
precisionTotal digits (max 38)
scaleDigits after decimal point

Example:


NUMBER(10,2) = 12345678.90

⚠️ Limit​

You cannot store numbers with 39+ digits.
Snowflake throws precision overflow error.


2️⃣ Integer Types (Aliases of NUMBER(38,0))

Snowflake supports:

  • INTEGER
  • INT
  • BIGINT
  • SMALLINT
  • TINYINT

All map internally to:


NUMBER(38,0)

✔ Same storage
✔ Same speed
✔ Same behavior
✔ Same numeric limits

Only the names differ.


🧾 Practical Summary of Integer Types

TypeStored AsUsage
INTEGERNUMBER(38,0)Standard IDs
INTNUMBER(38,0)Alias of INTEGER
BIGINTNUMBER(38,0)Large counters
SMALLINTNUMBER(38,0)Small values, flags
TINYINTNUMBER(38,0)Tiny counters
NUMBERNUMBER(p,s)Custom numeric precision

3️⃣ Approximate Numeric Types

FLOAT / DOUBLE / REAL​

Used when exact precision is not required:

  • 🔬 Scientific values
  • 🌡️ Sensor readings
  • 📈 Statistics
  • 🤖 ML algorithms

Characteristics:​

✔ Handles huge values
✔ Fast calculations
❌ Not accurate after 15–17 digits

Example:

SELECT 9999999999999999::DOUBLE;

Output:

1e+16

Precision loss = expected.


🚌 Integer Types — Layman Explanation

Snowflake has:

  • TINYINT
  • SMALLINT
  • INT
  • BIGINT
  • INTEGER

All internally use:

NUMBER(38,0)

🚍 Bus Analogy

Imagine a main road where all buses travel on the same road, using the same engine.

But each bus has a different label:

  • 🟡 School Bus
  • 🚌 City Bus
  • 🛫 Tourist Bus
  • 🚐 Mini Bus

All run on the same road — the names describe the purpose, not the engine.


🚦 Mapping This to Snowflake Integer Types

Bus TypeMeaning on RoadSnowflake TypeMeaning in Data
🟡 School BusFor kidsSMALLINTSmall values (flags, statuses)
🚌 City BusGeneral transportINT / INTEGERStandard IDs
🛫 Tourist BusLong routesBIGINTLarge counters / sequences
🚐 Mini BusShort tripsTINYINTTiny counters

🎯 Key Takeaway​

All integer types → same internal capacity Different names → express purpose & meaning


💡 Why This Matters

Even though storage is identical:

  • SMALLINT → small values
  • INT → regular IDs
  • BIGINT → large counters

This improves:

  • 📘 Readability
  • 🧩 Data modeling clarity
  • 🔄 Migration compatibility
  • 🧭 Governance
  • 🧱 ERD documentation consistency

⭐ Why Snowflake Keeps Multiple Integer Names

1️⃣ Cross-Database Compatibility​

DatabaseInteger Types
MySQLTINYINT, SMALLINT, INT, BIGINT
SQL Serversmallint, int, bigint
PostgreSQLsmallint, integer, bigint
OracleNUMBER

Helps migrations.


2️⃣ Readability & Modeling​

  • SMALLINT → flags
  • INT → identifiers
  • BIGINT → high-range counters

3️⃣ Tool/ORM Compatibility​

Required by:

  • dbt
  • Informatica
  • Matillion
  • Kafka Connect
  • SQLAlchemy
  • Hibernate

4️⃣ Future-Proofing​

Snowflake may introduce real integer sizes later.


5️⃣ Governance​

Cleaner documentation & ERDs.


2️⃣ String Data Types

TypeDescriptionExample
VARCHAR / STRING / TEXTVariable-length text'Hello World'
CHAR / NCHARFixed-length'A'
CHARACTERAlias of CHAR'X'

Max size = 16 MB.


3️⃣ Date & Time Types

DATE​

2024-02-01

TIME​

14:44:15

TIMESTAMP Types​

TypeMeaning
TIMESTAMP_NTZWithout timezone
TIMESTAMP_LTZUses session timezone
TIMESTAMP_TZStores timezone metadata

4️⃣ Boolean Type

Values:

  • TRUE
  • FALSE
  • NULL

5️⃣ Semi-Structured Data Types

Supports:

  • JSON
  • Avro
  • Parquet
  • ORC
  • XML

VARIANT​

Holds flexible JSON-like structures.

OBJECT​

Key–value map.

ARRAY​

Ordered list.

Example

SELECT preferences['theme'] 
FROM users;

6️⃣ Choosing the Right Data Type — Best Practices

Use CaseRecommended Type
MoneyNUMBER(10,2)
FlagsSMALLINT
Standard IDsINT
High countersBIGINT
JSON/XMLVARIANT
ArraysARRAY
Key-value pairsOBJECT
IDs > 38 digitsVARCHAR
Scientific dataFLOAT

7️⃣ Summary Table

Data TypeDescriptionExample
NUMBERExact numericNUMBER(10,2)
INTEGERWhole numbers10
FLOAT / DOUBLEApprox numeric10.5
VARCHARText'Hello'
DATEDate only'2024-01-01'
TIMESTAMP_LTZTZ-aware timestamp'2024-06-01 12:30:00'
BOOLEANTRUE/FALSETRUE
VARIANTJSONPARSE_JSON('{\"a\":1}')
ARRAYListARRAY_CONSTRUCT(1,2,3)
OBJECTKey-value mapOBJECT_CONSTRUCT('x','y')

8️⃣ Hands-On Demo

Standard Table​

CREATE OR REPLACE TABLE demo_standard_types (
emp_id INT,
emp_name STRING,
salary NUMBER(10,2),
is_active BOOLEAN,
joining_date DATE,
current_ts TIMESTAMP_LTZ,
utc_ts TIMESTAMP_NTZ
);

Insert​

INSERT INTO demo_standard_types VALUES (
1,
'Alice Johnson',
75000.50,
TRUE,
'2023-01-15',
CURRENT_TIMESTAMP(),
CONVERT_TIMEZONE('America/Los_Angeles','UTC',CURRENT_TIMESTAMP())
);

Semi-Structured Table​

CREATE OR REPLACE TABLE demo_semi_structured (
emp_id INT,
emp_name STRING,
salary NUMBER(10,2),
is_active BOOLEAN,
joining_date DATE,
current_ts TIMESTAMP,
profile_pic BINARY,
preferences VARIANT,
tags ARRAY,
metadata OBJECT
);

Insert JSON/Binary (MDX-safe)​

INSERT INTO demo_semi_structured
SELECT
1,
'Alice Johnson',
75000.50,
TRUE,
'2023-01-15',
CURRENT_TIMESTAMP(),
TO_BINARY('89504E470D0A1A0A','HEX'),
PARSE_JSON('{\"theme\":\"dark\",\"notifications\":true}'),
ARRAY_CONSTRUCT('tag1','tag2','tag3'),
OBJECT_CONSTRUCT('department','HR','location','NY');

Query JSON​

SELECT 
emp_name,
preferences['theme'] AS theme,
tags[0] AS first_tag,
metadata['location'] AS location
FROM demo_semi_structured;

9️⃣ Common Mistakes & Fixes

MistakeFix
Using FLOAT for moneyNUMBER(10,2)
Storing 40+ digit IDsVARCHAR
Overuse of VARIANTPrefer structured types
Wrong timestamp typeChoose NTZ/LTZ/TZ
JSON in VARCHARUse VARIANT

🔟 Exercises

  1. Create DB + Schema
  2. Create tables with all data types
  3. Insert JSON/ARRAY/OBJECT
  4. Query nested data
  5. Retrieve retention parameters
  6. Use Time Travel (AT OFFSET)
  7. Insert 50-digit values using VARCHAR
  8. Compare FLOAT vs NUMBER
  9. Insert BINARY data
  10. Compare timestamp types