What are Snowflake data types?

Snowflake data types are the specific type of data that can be stored in Snowflake columns. They include numeric data types, string data types, logical data types, date and time data types, semi-structured data types, and geospatial data types.

What is the difference between SQL and Snowflake data types?

While Snowflake supports a wide range of SQL data types, there are some differences. For example, the INTEGER data type in SQL corresponds to the NUMBER data type in Snowflake. Similarly, the VARCHAR, BOOLEAN, DATE, and TIMESTAMP data types in SQL correspond to the same data types in Snowflake.

Snowflake Data Types: The Ultimate Guide for Effective Data Modeling

Name: Sebastian Brandt

Updated on 7/24/2023

Snowflake, a leading player in the realm of cloud data warehousing, has revolutionized the way businesses handle their data. With its robust storage capabilities and flexible scalability, Snowflake caters to the ever-evolving market needs. One of the key aspects that sets Snowflake apart is its support for a wide range of data types. Understanding these data types is crucial for effective data modeling and manipulation in Snowflake.

In this article, we will delve into the world of Snowflake data types. We will explore what they are, how they work, and the differences between SQL and Snowflake data types. We will also provide examples of each data type and explain how to check the data type of a column in Snowflake. Whether you're a beginner or an experienced data professional, this guide will help you navigate the landscape of Snowflake data types.

Want to quickly visualize your snowflake data? Use RATH (opens in a new tab) to easily turn your Snowflake database into interactive visualizations! RATH is an AI-powered, automated data analysis and data visualization tool that is supported by a passionate Open Source community. check out RATH GitHub (opens in a new tab) for more. Here is how you can visualize Snowflake data in RATH:

Learn more about how to visualize Snowflake Data in RATH Docs.

(opens in a new tab)

Understanding Snowflake Data Types

Snowflake supports standard SQL data types with a few restrictions. Each column in a table has a name and a data type, which informs Snowflake about the amount of physical storage to allocate to the column and the form in which the data should be stored. Let's dive into the different types of Snowflake data types.

Numeric Data Types in Snowflake

Snowflake supports a variety of numeric data types, including NUMBER, DECIMAL, NUMERIC, INT, INTEGER, BIGINT, SMALLINT, FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, and REAL. Each of these data types has specific precision and scale parameters that determine the total number of digits allowed in a number and the number of digits that can appear after the decimal point, respectively.

For instance, the NUMBER data type is used to store whole numbers and has default precision and scale of 38 and 0 respectively. On the other hand, FLOAT supports double-precision IEEE 754 floating-point numbers and also special values like NaN (Not a Number), inf (infinity), and -inf(negative infinity).

String and Binary Data Types in Snowflake

String and binary data types in Snowflake include VARCHAR, CHAR, CHARACTER, STRING, TEXT, BINARY, and VARBINARY. The VARCHAR data type, for example, holds Unicode characters and has a maximum length of 16 MB. The BINARY data type, on the other hand, does not have the concept of Unicode characters, so its length is always measured in bytes, with a maximum length of 8 MB.

Logical Data Types in Snowflake

The logical data type in Snowflake is BOOLEAN, which has two values: TRUE or FALSE. It may also have an "unknown" value, which is displayed by NULL. The BOOLEAN data type provides the required support for Ternary Logic.

Date and Time Data Types in Snowflake

Snowflake provides support for managing dates, times, and timestamps through various data types like DATE, DATETIME, TIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, and TIMESTAMP_TZ. The DATE data type, for example, allows dates in the most common forms (YYYY-MM-DD, DD-MON-YYYY, etc.), while the TIMESTAMP_LTZ keeps track of the UTC time with the defined precision.

Semi-structured Data Types in Snowflake

Semi-structured data

types in Snowflake represent arbitrary data structures that can be used to load and operate on data like JSON, Avro, ORC, Parquet, or XML. Snowflake internally records these in an efficient compressed columnar binary representation to improve performance and efficiency.

The semi-structured data types include VARIANT, OBJECT, and ARRAY. The VARIANT data type is a universal data type, which can be used to store values of any other type, including OBJECT and ARRAY. It can store data up to a maximum size of 16 MB. The OBJECT data type is used to store collections of key-value pairs, where the key is a non-empty string and the value is of VARIANT type. The ARRAY data type is used to display dense and sparse arrays of arbitrary size.

Geospatial Data Types in Snowflake

Snowflake introduces the GEOGRAPHY data type, which models Earth as if it were a perfect sphere, following the WGS 84 standard. Points on the earth’s surface are represented as degrees of longitude (from -180 degrees to +180 degrees) and latitude (-90 to +90). Altitude is currently not supported. Line segments are interpreted as geodesic arcs on the Earth’s surface. Moreover, Snowflake delivers geospatial functions that operate on the GEOGRAPHY data type.

The GEOGRAPHY data type supports various geospatial objects such as Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, GeometryCollection, Feature, and FeatureCollection.

Snowflake Data Type Conversion

Data type conversion in Snowflake is a crucial aspect to understand. It allows you to convert data from one type to another, which can be particularly useful when you're dealing with data from different sources that might not use the same data types. For instance, you might need to convert a string to a number or a date to a string. Snowflake provides several functions to perform these conversions, including CAST and TRY_CAST.

Checking Data Types in Snowflake

In Snowflake, you can check the data type of a column using the INFORMATION_SCHEMA.COLUMNS view. This view contains a row for each column in a table, and it includes a column named DATA_TYPE that tells you the data type of each column. Here's an example of how you can use this view to check the data types of the columns in a table named my_table:

SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'my_table';

This query will return a result set that includes the name and data type of each column in my_table.

Snowflake Data Types for Beginners

Understanding Snowflake data types is crucial for anyone working with Snowflake, whether you're a beginner or an experienced data professional. Here are some simplified explanations of some common Snowflake data types:

NUMBER: This data type is used to store whole numbers. For example, the number of employees in a company or the number of units sold of a product.
VARCHAR: This data type is used to store text. For example, the names of employees or the names of products.
BOOLEAN: This data type is used to store true or false values. For example, whether an employee is full-time or not.
DATE: This data type is used to store dates. For example, the hire date of an employee or the sale date of a product.
TIMESTAMP: This data type is used to store dates and times. For example, the exact time a sale was made.

Snowflake SQL Data Types

Snowflake supports a wide range of SQL data types, making it easy for those familiar with SQL to work with Snowflake. Here are some examples of how Snowflake's data types correspond to SQL data types:

INTEGER in SQL is NUMBER in Snowflake: Both are used to store whole numbers.
VARCHAR in SQL is VARCHAR in Snowflake: Both are used to store text.
BOOLEAN in SQL is BOOLEAN in Snowflake: Both are used to store true or false values.
DATE in SQL is DATE in Snowflake: Both are used to store dates.
TIMESTAMP in SQL is TIMESTAMP in Snowflake: Both are used to store dates and times.

Snowflake String Data Types

Snowflake provides several data types for working with strings, including VARCHAR, CHAR, CHARACTER, STRING, and TEXT. Here are some examples of how you might use these data types:

VARCHAR: This data type is used to store text of variable length. For example, you might use it to store the names of employees in a company.

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR(100)
);

CHAR or CHARACTER: These data types are used to store text of a fixed length. For example, you might use it to store the two-letter state codes in an address.

CREATE TABLE addresses (
  id NUMBER,
  state CHAR(2)
);

STRING or TEXT: These data types are synonymous with VARCHAR in Snowflake. You can use them in the same way you would use VARCHAR.

Snowflake Numeric Data Types

Snowflake offers a variety of numeric data types, including NUMBER, DECIMAL, NUMERIC, INT, INTEGER, BIGINT, SMALLINT, FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, and REAL. Here are some examples of how you might use these data types:

NUMBER: This data type is used to store whole numbers. For example, you might use it to store the number of units sold of a product.

CREATE TABLE sales (
  id NUMBER,
  units_sold NUMBER
);

DECIMAL or NUMERIC: These data types are used to store numbers with decimal points. For example, you might use it to store the price of a product.

CREATE TABLE products (
  id NUMBER,
  price DECIMAL(10, 2)
);

FLOAT or DOUBLE: These data types are used to store floating-point numbers. For example, you might use it to store the weight of a product.

CREATE TABLE products (
  id NUMBER,
  weight FLOAT
);

Conclusion

Understanding Snowflake data types is crucial for anyone working with Snowflake. Whether you're storing whole numbers, text, true or false values, dates, or more complex data structures, Snowflake has a data type that can handle it. By understanding these data types, you can ensure that your data is stored efficiently and accurately, making your data operations smoother and more effective.

FAQs

What are Snowflake data types? Snowflake data types are the specific type of data that can be stored in Snowflake columns. They include numeric data types, string data types, logical data types, date and time data types, semi-structured data types, and geospatial data types.
How do you check the data type in Snowflake? You can check the data type of a column in Snowflake using the INFORMATION_SCHEMA.COLUMNS view. This view contains a row for each column in a table, and it includes a column named DATA_TYPE that tells you the data type of each column.
What is the difference between SQL and Snowflake data types? While Snowflake supports a wide range of SQL data types, there are some differences. For example, the INTEGER data type in SQL corresponds to the NUMBER data type in Snowflake. Similarly, the VARCHAR, BOOLEAN, DATE, and TIMESTAMP data types in SQL correspond to the same data types in Snowflake.