Snowflake Data Types 101—Overview of 6 Essential Types (2024)

Snowflake supports a variety of data types, offering just the right fit for all kinds of data, whether it's numbers, strings—or even complex semi-structured data formats. This rich plethora of options is not merely a means to store information - by selecting the appropriate data type, you can boost your storage efficiency, streamline query performance—and even enhance the overall integrity and usability of your data.

In this article, we will cover everything you need to know about Snowflake data types—from numeric, string, binary, to logical, date & time, as well as more complex semi-structured and geospatial types. Also, we'll delve into what each type is best suited for—their unique capabilities and practical use cases.

What are Snowflake Data Types?

Snowflake supports most basic SQL data types, from numbers and text to dates, and extends its capabilities to manage more complex semi-structured and geospatial data. These types are available for use in different contexts, such as columns, local variables, expressions, parameters, and other suitable locations.

Carefully selecting appropriate data types is crucial in Snowflake to balance flexibility, storage efficiency, and streamline query performance.

Now, let's look at the 6 different categories of data types offered natively by Snowflake.

Categories of Snowflake Data Types

Snowflake classifies data types into 6 primary categories:

1. Numeric Data Types
2. String & Binary Data Types
3. Logical Data Types
4. Date & Time Data Types
5. Semi-structured Data Types
6. Geospatial Data Types

Check out this video for a quick rundown of the data types available in Snowflake.

1) Numeric—Snowflake Data Types

Numeric Snowflake data types allow storing numeric values including whole numbers, decimals, floating point numbers, doubles, etc. Choosing the right numeric type is important for accuracy and efficient storage and processing.

Here are some of the common numeric data types in Snowflake:

NUMBER()

Remember this, whenever you are working with numbers in Snowflake, two key concepts come into play—Precision and Scale.  These help control how numeric data is stored and handled.

Precision refers to the total number of digits allowed for a number. So if you have a column defined as NUMBER(5,2), the precision here is 5. This means values can range from -999 to 999 in this column. Having lower precision constrains the size of numbers that can be stored.

Scale relates to the number of digits allowed after the decimal point. In our example of NUMBER(5,2), the scale is 2. So our values could look like either -99.99 or 123.45. A higher scale allows for capturing more granularity in the fractions.

TLDR;

• Precision: Total number of digits allowed.
• Scale: Number of digits allowed to the right of the decimal point.

One interesting aspect is that precision does not impact the storage size in Snowflake. So a value takes the same space, whether the column precision is 5 or 25. It's the scale that increases storage needs—more fractional digits means more bytes are needed to store a value.

Now, one issue arises when data goes through multiple conversions. If data is converted to another data type with lower precision, and then back to the higher-precision form, the data can lose precision.

The default value for precision and scale of NUMBER are 38 and 0, respectively (i.e. NUMBER(38, 0))

Here's a simple example illustrating the use of the NUMBER Snowflake data type

-- Creating a table with NUMBER snowflake data type - data type in Snowflake
CREATE TABLE number_table (
  num1 NUMBER(5,2), 
  num2 NUMBER 
);

INSERT INTO number_table VALUES
  (-999, 999999);

DESC TABLE number_table;  

DECIMAL, DEC, NUMERIC

DECIMAL enables fixed point decimal arithmetic by explicitly defining value range via precision and scale. DECIMAL, DEC, and NUMERIC are also synonymous with NUMBER. The default value for precision and scale of DECIMAL is also same as number (i.e. DECIMAL(38, 0))

Here's a simple example illustrating the use of the DECIMAL Snowflake data type:

-- Creating a table with DECIMAL snowflake data type - data type in Snowflake
CREATE TABLE decimal_table (
  num1 DECIMAL(5,2), 
  num2 DECIMAL 
);

INSERT INTO decimal_table VALUES
  (-999, 999999);

DESC TABLE decimal_table; 

INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINT

Integer is also synonymous with NUMBER, except that precision and scale cannot be specified. Whenever you are modeling whole numbers in Snowflake, you have a number of integer types to choose from—INT, BIGINT, SMALLINT—and more. These different types actually behave very similarly, simply allowing different ranges of integers to be stored.

For example, INT allows storing fairly big 38 digit integers ranging from -999 quadrillion to plus 999 quadrillion without any decimal fraction component!

They give hints to developers on likely value ranges they can model, without having to worry about corner case overflows. You can roughly rely on INT for common use cases, BIGINT for extremely large numbers, and SMALLINT for small ranges.

Regarding storage and performance, the precision of an integer type (the total number of digits) does not affect the storage size in Snowflake. Instead, Snowflake optimizes storage by compressing values and determining storage requirements based on the range of values within each micro-partition. But, the scale (digits after the decimal point, which is zero for integer types) can impact storage, with larger scales potentially consuming more space and affecting processing speed and memory usage.

Here's a simple example illustrating the use of the integer Snowflake data type:

CREATE OR REPLACE TABLE integer_table (
    small_number SMALLINT,
    regular_int INT,
    big_number BIGINT
);

-- Inserting values
INSERT INTO integer_table (small_number, regular_int, big_number) VALUES 
(-32768, 2147483647, 9223372036854775807);

-- Query to describe the table
DESC TABLE integer_table;

In short, these integer types offer different intuitive "buckets" for whole number modeling without fractions.

FLOAT , FLOAT4 , FLOAT8

FLOAT data type, along with its variations FLOAT4 and FLOAT8, are treated as 64-bit floating-point numbers. This is in line with the double-precision IEEE 754 standard and is designed for compatibility with other systems. FLOAT in Snowflake can accurately represent approximately 15 digits.

For integers, the range spans from -9007199254740991 to +9007199254740991. For floating-point values, the range is about 10^-308 to 10^+308, with more extreme values between approximately 10^-324 and 10^-308 represented with less precision.

Snowflake also supports special values for FLOAT:

• NaN (Not A Number).
• in (infinity).
• -inf (negative infinity).

These are case-insensitive and must be enclosed in single quotes. Notably, Snowflake's treatment of 'NaN' differs from the IEEE 754 standard, as Snowflake considers all 'NaN' values equal and treats 'NaN' as greater than any other FLOAT value.

Rounding errors are a consideration with floating-point operations. These can vary with each query execution and may be more larger when operands have different precision or scale. Such errors are particularly relevant in aggregate functions like SUM() or AVG(). Snowflake recommends casting to a fixed-point data type before aggregating to reduce or eliminate these errors.

Here's a detailed example of using FLOAT Snowflake data types:

-- Creating a table with FLOAT snowflake data type - data type in Snowflake
CREATE OR REPLACE TABLE float_table (
    regular_float FLOAT,
    small_float FLOAT4,
    large_float FLOAT8
);

INSERT INTO float_table (regular_float, small_float, large_float) VALUES 
(123.456, 789.1011, 112233.445566);

-- Query to describe the table 
DESC TABLE float_table;

DOUBLE , DOUBLE PRECISION , REAL

DOUBLE, DOUBLE PRECISION, and REAL are also synonymous with FLOAT, meaning they are also treated as 64-bit floating-point numbers. These types are also designed to handle numbers that require large ranges and significant precision, especially useful in scenarios where FLOAT is used to represent real-world data with a high degree of accuracy.

Here's a detailed example of using DOUBLE Snowflake data type:

-- Creating a table with DOUBLE snowflake data type - data type in Snowflake
CREATE OR REPLACE TABLE double_table (
    column_double DOUBLE,
    column_double_precision DOUBLE PRECISION,
    column_real REAL
);

INSERT INTO double_table (column_double, column_double_precision, column_real) VALUES 
(123456.789, 987654.321, 12345.6789);

-- Query to describe the table
DESC TABLE double_table;

Numeric constants

Numeric constants, also known as literals, are fixed data values. They follow specific formats:

[+-][digits][.digits][e[+-]digits]

• The format can include an optional sign (+ or -), where the default is positive if not specified.
• It consists of digits (0 to 9), which can be placed before or after a decimal point.
• The letter e or E is used to denote scientific notation, indicating an exponent. If an exponent is present, it must be followed by at least one digit.

2) String & Binary—Snowflake Data Types

String and Binary Snowflake data types are integral for managing text and binary data. These Snowflake data types uniformly handle Unicode UTF-8 characters, ensuring consistent data handling across various string types.

Here are some of the common  String & Binary data types in Snowflake:

VARCHAR

VARCHAR is the most versatile string data type in Snowflake, accommodating Unicode UTF-8 characters. Its length can be defined up to 16,777,216 characters or 16 MB, but if unspecified, it defaults to this maximum.

Regarding performance, there is no performance difference between using the full-length VARCHAR declaration VARCHAR(16777216) and a smaller length.

This Snowflake data type is really good for storing text where the length might vary, such as names, descriptions or comments…

For Example:

-- Creating a table with VARCHAR snowflake data type - data type in Snowflake
CREATE TABLE user_reviews (
    review_id INT,
    review_text VARCHAR(500)
);

INSERT INTO user_reviews VALUES (1, Some awesome reviews......');

DESC TABLE user_reviews;

CHAR, CHARACTER, NCHAR

CHAR (or CHARACTER, NCHAR) is similar to VARCHAR but is typically used for storing fixed-length strings. If the length is not specified, it defaults to CHAR(1). Snowflake currently deviates from common CHAR semantics in that strings shorter than the maximum length are not space-padded at the end.

For Example:

-- Creating a table with CHAR snowflake data type - data type in Snowflake
CREATE TABLE user_initials (
    employee_id INT,
    initials CHAR(3)
);

INSERT INTO user_initials VALUES (123, 'Mr.');

DESC TABLE user_initials;

STRING, TEXT, NVARCHAR, NVARCHAR2, CHAR VARYING, NCHAR VARYING

STRING, TEXT, NVARCHAR, NVARCHAR2, CHAR VARYING, NCHAR VARYING Snowflake data types are all treated as VARCHAR.

For Example:

-- Creating a table with STRING snowflake data type - data type in Snowflake
CREATE TABLE product_descriptions (
    product_id INT,
    description TEXT
);

INSERT INTO product_descriptions VALUES (456, 'Somke awesome description....');

DESC TABLE product_descriptions;

What is the Difference Between String and Varchar?

STRING and VARCHAR—Snowflake data types are often used interchangeably, as both are effective in handling text data. Think of VARCHAR as your go-to for variable-length text. In Snowflake, STRING is essentially synonymous with VARCHAR. But, in some db systems, STRING is used for fixed-length character data. In the context of Snowflake, they function the same way—both are designed to handle text data efficiently, allowing your text/string data be as short or as long-winded as it needs to be.

BINARY

BINARY data type is used for fixed-length binary data, like images or files, with a maximum size of 8 MB. Unlike VARCHAR, the BINARY data type has no notion of Unicode characters, so the length is always measured in terms of bytes.

For Example:

-- Creating a table with BINARY snowflake data type - data type in Snowflake
CREATE TABLE user_avatars (
    user_id INT,
    avatar BINARY(1000)
);

DESC TABLE user_avatars;

VARBINARY

VARBINARY is for variable-length binary data, also up to 8 MB. It’s similar to BINARY but more flexible, as it adjusts its size based on the actual length of the data.
For Example:

-- Creating a table with VARBINARY snowflake data type - data type in Snowflake
CREATE TABLE file_storage (
    file_data VARBINARY
);

DESC TABLE file_storage;

String Constants

String constants (literals) are used for representing fixed text values. Snowflake supports single-quoted and dollar-quoted string constants. The former requires escaping special characters like single quotes, while the latter is useful for complex strings containing special characters or spanning multiple lines.

Single-Quoted String Constants: Enclosed between single quotes ('), using two single quotes to represent a quote within the string.

SELECT 'Snowflake''s power is insane';

Dollar-Quoted String Constants: Enclosed between pairs of dollar signs ($$), useful for strings that include quotes or special characters.

SELECT $$String with 'quotes' and multiple
lines without needing escape sequences.$$;

Check this Snowflake documentation to learn more in-depth on String & Binary Snowflake data type.

3) Logical—Snowflake Data Types

Snowflake's logical data type is BOOLEAN, which represents true or false values. It can also have an "unknown" value, represented by NULL. You can use BOOLEAN in various parts of your query, like in a SELECT list or a WHERE clause, to make decisions based on logical conditions.

Now, let's talk about converting to BOOLEAN. You've got two ways: explicit and implicit. With explicit conversion, you can turn text strings like “true”, ”yes”, or ”1” into TRUE, and ”false”, ”no”, ”0” into FALSE. This conversion doesn’t fuss about case sensitivity. Numeric values follow a simple rule: zero converts to FALSE, and any non-zero value becomes TRUE.

Implicit conversion is even simpler. It's like the system makes an educated guess and converts ”true” or “false” strings into their BOOLEAN equivalents, and does the same with numeric values following the zero and non-zero rule.

When you flip it around, converting from BOOLEAN, you can explicitly turn TRUE into “true” or 1, and FALSE into “false” or 0. Implicitly, TRUE and FALSE get converted into “true” and “false” as strings.

For Example:

Creating and Inserting Data:

CREATE TABLE test_boolean(b BOOLEAN, n NUMBER, s STRING);
INSERT INTO test_boolean VALUES (true, 1, 'yes'), (false, 0, 'no'), (null, null, null);

Using BOOLEAN in Expressions:

-- Using BOOLEAN snowflake data type in Expressions - data type in Snowflake
SELECT b, n, NOT b AND (n < 1) FROM test_boolean;

Using BOOLEAN in Predicates:

SELECT * FROM test_boolean WHERE NOT b AND (n < 1);

Text to BOOLEAN Conversion:

SELECT s, TO_BOOLEAN(s) FROM test_boolean;

Number to BOOLEAN Conversion:

SELECT n, TO_BOOLEAN(n) FROM test_boolean;

Implicit Conversion to Text:

SELECT 'Text for ' || s || ' is ' || b AS result FROM test_boolean;

4) Date & Time—Snowflake Data Types

Snowflake Date & Time Snowflake Data Types are used for managing dates, times—and timestamps.

Let's delve deeper into each Date & Time data type in Snowflake

DATE

DATE data type is exclusively for storing calendar dates. It accepts various date formats (e.g., YYYY-MM-DD, DD-MON-YYYY) and is adept at handling conversions from TIMESTAMP data, though it disregards any time component. Snowflake recommends using dates between the years 1582 and 9999 to avoid complications with historical calendar reforms.

For example:

-- Creating a table with DATE snowflake data type - data type in Snowflake
CREATE TABLE project_deadlines (project_name STRING, deadline DATE);
INSERT INTO project_deadlines VALUES ('Some  Project', '2023-07-20');
DESC TABLE project_deadlines;

DATETIME

DATETIME in Snowflake is an alias for TIMESTAMP_NTZ. It's used for dates and times without considering the time zone. This data type is useful for applications where time zone is not a critical factor.

For example:

CREATE TABLE meeting_schedule (meeting_topic STRING, meeting_datetime DATETIME);
INSERT INTO meeting_schedule VALUES ('Budget Review', '2023-07-21 10:00:00');

TIME

TIME data type stores time without a date component. It supports precision for fractional seconds, ranging from 0 (seconds) to 9 (nanoseconds). This type is beneficial when you need to store specific times of the day without date information, like schedules or routines.

For example:

-- Creating a table with TIME snowflake data type - data type in Snowflake
CREATE TABLE store_hours (day_of_week STRING, opening_time TIME, closing_time TIME);
INSERT INTO store_hours VALUES ('Monday', '08:00:00', '18:00:00');
DESC TABLE store_hours;

TIMESTAMP Variants

• TIMESTAMP_LTZ (Local Time Zone): Stores times in UTC but converts them to the local time zone for display and calculation. It's ideal for applications needing to record the exact moment of an event in a globally consistent way, regardless of where the event occurred.
• TIMESTAMP_NTZ (No Time Zone): Records time without any time zone information. This variant is best suited for applications where time zone context is either irrelevant or managed externally.
• TIMESTAMP_TZ (Time Zone): Stores both the time and the time zone, making it perfect for applications that require awareness of the time zone, like scheduling events across different regions.

For example:

-- Creating a table with TIMESTAMP snowflake data type - data type in Snowflake
CREATE TABLE meetings (meeting_name STRING, start_time TIMESTAMP_TZ);
INSERT INTO meetings VALUES ('Global Sync', '2023-07-22 15:00:00 +0100');

Check this Snowflake documentation to learn more in-depth on DATE & TIME Snowflake data type.

These Snowflake data types offer great flexibility and precision in handling date and time data.

5) Semi-structured—Snowflake Data Types

Semi-structured Snowflake data types are particularly versatile, allowing storage and manipulation of a variety of data formats. These are:

VARIANT

VARIANT is a flexible data type that can hold any other type, including OBJECT and ARRAY. It's particularly useful for storing data in formats like JSON, Avro, ORC, Parquet, or XML. VARIANT has a maximum size of 16 MB, and it stores both the value and its type, allowing for operations without explicit casting.

For example:

-- Creating a table with VARIANT snowflake data type - data type in Snowflake
CREATE TABLE user_data (user_info VARIANT);
INSERT INTO user_data SELECT PARSE_JSON('{"name": "Genius", "age": 40}');
DESC TABLE user_data;

OBJECT

OBJECT is similar to a JSON object or a dictionary in other languages. It contains key-value pairs, with each key as a VARCHAR and each value as a VARIANT. OBJECT is perfect for representing structured data in a flexible format.

-- Creating a table with OBJECT snowflake data type - data type in Snowflake
CREATE TABLE user_profiles (id INT, details OBJECT);
INSERT INTO user_profiles(details) SELECT OBJECT_CONSTRUCT('name', 'Chaos', 'age', 35, 'location', 'SF');
DESC TABLE user_profiles;

ARRAY

ARRAY Snowflake data type is similar to arrays in many programming languages, capable of holding an ordered list of elements. An ARRAY contains 0 or more pieces of data. Each element is accessed by specifying its position in the array. Each element in an ARRAY is a VARIANT, meaning it can store diverse types of data. ARRAY is useful for storing lists or sequences of values, where each element can be different but often is of the same or compatible types for uniform processing.

Note: Snowflake does not support fixed-size arrays or arrays with values of a specific non-VARIANT type.

-- Creating a table with ARRAY snowflake data type - data type in Snowflake
CREATE TABLE snowflake_size (_id INT, sizes ARRAY);
INSERT INTO snowflake_size(sizes) SELECT ARRAY_CONSTRUCT('XS','S', 'M', 'L', 'XL');
DESC TABLE snowflake_size;

Check this Snowflake documentation to learn more in-depth on Semi-structured Snowflake data type.

6) Geospatial—Snowflake Data Types

Geospatial Snowflake data types refers to information about geographic locations and shapes like points, lines and polygons on the earth's surface. Snowflake provides two data types to store and analyze this type of location data, they are:

GEOGRAPHY

Stores data using spherical latitude / longitude coordinates following the WGS 84 standard. Interprets lines as curved geodesic paths on the earth's surface allowing natural modeling of real-world geospatial shapes.

-- Creating a table with GEOGRAPHY snowflake data type
CREATE TABLE cities (
  location GEOGRAPHY 
);

INSERT INTO cities
  VALUES ('POINT(-122.4783 37.8199)');

GEOMETRY

Handles 2D planar cartesian data often from projected coordinate systems. Treats lines as straight line segments between points instead of curved paths. Supports various well-known spatial reference systems for coordinate interpretation.

-- Creating a table with GEOMETRY snowflake data type - data type in Snowflake
CREATE TABLE survey_zones (
  outline GEOMETRY
);

INSERT INTO survey_zones 
  VALUES ('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');

DESC TABLE survey_zones;

What are the supported Geospatial Object Types?

The GEOGRAPHY and GEOMETRY data types support storing a number of structured geospatial objects, they are:

WKT / WKB / EWKT / EWKB / GeoJSON geospatial objects:

• Point
• MultiPoint
• LineString
• MultiLineString
• Polygon
• MultiPolygon
• GeometryCollection

GeoJSON-specific geospatial objects:

• Feature
• FeatureCollection

TLDR; GEOGRAPHY type is designed for data based on the earth's round surface, using latitude and longitude coordinates and the GEOMETRY type handles flat plane based shapes using X and Y coordinates. Both integrate with spatial functions in Snowflake SQL for location-based processing and analysis.

Which datatype is not supported by Snowflake?

Certain data types are common in other db systems but they are not supported in Snowflake. Here are the list of all the unsupported Snowflake data types, they are:

LOB (Large Object)

• BLOB: Snowflake does not support the Binary Large Object type. As an alternative, the BINARY data type can be used, with a maximum size limit of 8,388,608 bytes.
• CLOB: Character Large Object type is also unsupported. VARCHAR is the recommended alternative in Snowflake, with a maximum capacity of 16,777,216 bytes for single-byte characters.

Other Unsupported Types

• ENUM: The ENUM type, used in other database systems for a list of predefined values, is not supported in Snowflake.
• User-defined Data Types: Snowflake does not support data types that are defined by users, which are commonly supported in some other database systems.

Conclusion

And that's a wrap! As you can see, Snowflake supports a variety of data types, catering to virtually any data requirement you might encounter —- from the precision of Numeric types to the versatility of Semi-structured formats, Snowflake's data types are integral tools for data management and analysis.

In this article, we covered:

• What are Snowflake Data Types?
• Different types of Snowflake Data Types
• Numeric—Snowflake Data Types
• String & Binary—Snowflake Data Types
• Logical—Snowflake Data Types
• Date & Time—Snowflake Data Types
• Semi-structured—Snowflake Data Types
• Geospatial—Snowflake Data Types
• Which datatype is not supported by Snowflake?

—and so much more!

Imagine Snowflake's data types like a bunch of keys on your keyring. Each key, representing a different data type, is made to open a specific door (data challenge), whether it's numeric precision, text processing, or handling complex JSON structures. Just like picking the right key for the right door, choosing the right data type in Snowflake can really unlock your data's full potential in the smoothest way possible.

FAQs

What is a data type in Snowflake?

Data types in Snowflake specify the format in which data is stored, including numeric, string, binary, date and time, and Boolean data types.

Which data types are not supported by Snowflake?

Snowflake does not support BLOB and CLOB data types.

What are the three main categories of data types in Snowflake?

Numeric, string, and date and time types.

What are the seven main types of data in Snowflake?

Numeric, character or string, binary, date and time, Boolean, geospatial, and Semi-structured data​

Can Snowflake handle geospatial data?

Yes, Snowflake supports geospatial data with GEOGRAPHY and GEOMETRY data types.

What is the difference between SQL and Snowflake data types?

Snowflake has all the usual SQL data types you're familiar with, but it goes a step further. It offers specialized types like VARIANT for semi-structured data and GEOGRAPHY for spatial data. These enhancements makes Snowflake more versatile/flexible, perfect for modern data challenges.

Does Snowflake support JSON data?

Yes, Snowflake can efficiently store and query JSON data using the VARIANT data type.

Can Snowflake handle large binary data?

While Snowflake doesn't support BLOB, it can use BINARY data types up to 8,388,608 bytes.

How does Snowflake handle large character data?

Snowflake uses VARCHAR for large character data, with a maximum of 16,777,216 bytes for single-byte characters.

Can Snowflake store and process array data?

Yes, Snowflake has the ARRAY data type for storing arrays of values.

What is BOOLEAN Snowflake data type used for?

It represents true or false values for logical operations.

Does Snowflake support user-defined data types?

No, Snowflake does not support user-defined data types.

Can Snowflake store data in a planar coordinate system?

Yes, with the GEOMETRY Snowflake data type.

Does Snowflake support the storage of images and videos?

While it doesn't support BLOB, binary Snowflake data types can be used for images and videos up to 8 MB.

How does Snowflake handle temporal data?

Snowflake uses DATE, TIME, and TIMESTAMP data types for temporal data.

Can Snowflake process semi-structured data like XML or Avro?

Yes, Snowflake's VARIANT data type can handle semi-structured data formats.

Is ENUM supported in Snowflake?

No, ENUM data type is not supported in Snowflake.

Can Snowflake integrate data from various sources?

Yes, Snowflake's diverse data types allow integrating different data sources into a unified platform.