🧮 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
| Category | Types |
|---|---|
| Exact Numeric | NUMBER, DECIMAL, NUMERIC |
| Integer Types | INTEGER, INT, BIGINT, SMALLINT, TINYINT |
| Approx Numeric | FLOAT, FLOAT4, FLOAT8, REAL, DOUBLE |
| String | VARCHAR, STRING, TEXT, CHAR, CHARACTER, NCHAR |
| Binary | BINARY |
| Date | DATE |
| Time | TIME |
| Timestamp | TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ |
| Boolean | BOOLEAN |
| Semi-Structured | VARIANT, 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)
| Term | Meaning |
|---|---|
| precision | Total digits (max 38) |
| scale | Digits 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
| Type | Stored As | Usage |
|---|---|---|
| INTEGER | NUMBER(38,0) | Standard IDs |
| INT | NUMBER(38,0) | Alias of INTEGER |
| BIGINT | NUMBER(38,0) | Large counters |
| SMALLINT | NUMBER(38,0) | Small values, flags |
| TINYINT | NUMBER(38,0) | Tiny counters |
| NUMBER | NUMBER(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 Type | Meaning on Road | Snowflake Type | Meaning in Data |
|---|---|---|---|
| 🟡 School Bus | For kids | SMALLINT | Small values (flags, statuses) |
| 🚌 City Bus | General transport | INT / INTEGER | Standard IDs |
| 🛫 Tourist Bus | Long routes | BIGINT | Large counters / sequences |
| 🚐 Mini Bus | Short trips | TINYINT | Tiny 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
| Database | Integer Types |
|---|---|
| MySQL | TINYINT, SMALLINT, INT, BIGINT |
| SQL Server | smallint, int, bigint |
| PostgreSQL | smallint, integer, bigint |
| Oracle | NUMBER |
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
| Type | Description | Example |
|---|---|---|
| VARCHAR / STRING / TEXT | Variable-length text | 'Hello World' |
| CHAR / NCHAR | Fixed-length | 'A' |
| CHARACTER | Alias of CHAR | 'X' |
Max size = 16 MB.
3️⃣ Date & Time Types
DATE
2024-02-01
TIME
14:44:15
TIMESTAMP Types
| Type | Meaning |
|---|---|
| TIMESTAMP_NTZ | Without timezone |
| TIMESTAMP_LTZ | Uses session timezone |
| TIMESTAMP_TZ | Stores 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 Case | Recommended Type |
|---|---|
| Money | NUMBER(10,2) |
| Flags | SMALLINT |
| Standard IDs | INT |
| High counters | BIGINT |
| JSON/XML | VARIANT |
| Arrays | ARRAY |
| Key-value pairs | OBJECT |
| IDs > 38 digits | VARCHAR |
| Scientific data | FLOAT |
7️⃣ Summary Table
| Data Type | Description | Example |
|---|---|---|
| NUMBER | Exact numeric | NUMBER(10,2) |
| INTEGER | Whole numbers | 10 |
| FLOAT / DOUBLE | Approx numeric | 10.5 |
| VARCHAR | Text | 'Hello' |
| DATE | Date only | '2024-01-01' |
| TIMESTAMP_LTZ | TZ-aware timestamp | '2024-06-01 12:30:00' |
| BOOLEAN | TRUE/FALSE | TRUE |
| VARIANT | JSON | PARSE_JSON('{\"a\":1}') |
| ARRAY | List | ARRAY_CONSTRUCT(1,2,3) |
| OBJECT | Key-value map | OBJECT_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
| Mistake | Fix |
|---|---|
| Using FLOAT for money | NUMBER(10,2) |
| Storing 40+ digit IDs | VARCHAR |
| Overuse of VARIANT | Prefer structured types |
| Wrong timestamp type | Choose NTZ/LTZ/TZ |
| JSON in VARCHAR | Use VARIANT |
🔟 Exercises
- Create DB + Schema
- Create tables with all data types
- Insert JSON/ARRAY/OBJECT
- Query nested data
- Retrieve retention parameters
- Use Time Travel (
AT OFFSET) - Insert 50-digit values using VARCHAR
- Compare FLOAT vs NUMBER
- Insert BINARY data
- Compare timestamp types