Snowflake Data Types: A Deep Dive into 6 Essential Varieties

Snowflake provides support for the standard SQL data types (with a few restrictions) for use in columns, local variables, expressions, and parameters. Every column in a table will have a name and a data type. The data type informs Snowflake of how much physical storage to allocate to a column and also the form in which the data must be stored. 

The Snowflake driver restricts the number of columns to 16,384 because of the limit on the length of SQL commands. The driver also restricts the number of columns according to the data length that is required to read/write a row. When all the data is only numeric, it leads to a limit of about 7,480 columns. The actual limit might be lower depending on the data types used.

This article will introduce you to Snowflake and its key features. It will also explain in-depth, the various Snowflakes Data Types, Read along to understand Snowflake better!

What is Snowflake?

Snowflake is a relational, Cloud Data Warehouse that provides a Database as a Service (DBaaS) to users. This Data Warehouse can add flexibility and scalability to your business and help you in meeting the changing market needs. Its robust Cloud Storage lets you store unlimited volumes of structured and semi-structured data. Therefore you can consolidate the data collected from various data sources. Furthermore, the Snowflake Data Warehouse safeguards your business from any additional hardware purchase.

Key Features of Snowflake

The following features of Snowflake are responsible for its enormous popularity worldwide:

• Scalability: Snowflake offers storage facilities separate from its computation facilities. It uses a Database to store data while the calculation is performed in the virtual Data Warehouse. Thus Snowflake ensures high scalability at affordable costs.
• Low Maintainance: Snowflake has a design that minimizes users’ efforts. It requires minimal user interaction and maintenance effort.
• Query Optimization: Snowflake supports automated query optimization that can save your time and resources from the troubles of manual query improvement.
• Load Balancing: Snowflake lets you separate the routine workloads of your business into various virtual Data Warehouse units. This helps in managing Data Analytics, especially during peak routine loads.

To learn more about Snowflake, visit here.

What is the Snowflake Data Types?

To master the Snowflake Data Warehouse, you first need to understand its various Data Types. Snowflake supports the following 6 Data Types:

• Numeric Snowflake Data Types
• String and Binary Snowflake Data Types
• Logical Snowflake Data Types
• Date and Time Snowflake Data Types
• Semi-structured Snowflake Data Types
• Geospatial Snowflake Data Types

1) Numeric Snowflake Data Types

Before going into the different types of numeric data types it is important to understand for precision and scale are: 

Precision – The total number of digits allowed in a number.

Scale– The number of digits that can appear after the decimal point. 

Note: If a data item is converted to some data type with lower precision and then back to its higher-precision form, it may lose precision.

Precision does not impact storage, i.e., the same number placed in columns with different precisions, like NUMBER(5,0) and NUMBER(25,0) will have the same storage requirements. On the other hand, the scale does impact storage, like, the same value stored in a column of type NUMBER(20,5) uses up more space than NUMBER(20,0). Also, values with a larger scale might be slightly slower to process and require more memory.

The different numeric data types are as follows:

• NUMBER: Used to store whole numbers. Has default precision and scale of 38 and 0 respectively.
• DECIMAL: It is synonymous with NUMBER.
• NUMERIC: It is synonymous with NUMBER too.
• INT, INTEGER, BIGINT, SMALLINT: Each one of them is synonymous with NUMBER. However, the precision and scale cannot be altered, they are set to 38 and 0 respectively.
• FLOAT, FLOAT4, FLOAT8: Snowflake employs double-precision IEEE 754 floating-point numbers. It also supports special values like NaN (Not a Number), inf (infinity), and -inf(negative infinity).
• DOUBLE, DOUBLE PRECISION, REAL: Each one of them is synonymous with  FLOAT.
• Numeric Constants: Constants refer to fixed values. The following formats are supported by Snowflake:
  
  [+-][digits][.digits][e[+-]digits]
  
  Here,
  + or – identifies if the value is positive or negative.
  
  digits are one or more digits between 0 to 9.
  
  e (or E) represents an exponent in scientific notation. 

2) String and Binary Snowflake Data Types

Snowflake provides the following data types to work with characters (text).

• VARCHAR: It holds Unicode characters and has a maximum length of 16 MB. Some BI/ETL tools may initialize the maximum length of the VARCHAR data in storage or in memory. 
• CHAR, CHARACTER: It’s the same as VARCHAR except the default length is VARCHAR(1).
• STRING: It’s the same as VARCHAR.
• TEXT: It’s the same as VARCHAR.
• BINARY: The BINARY data type does not have the concept of Unicode characters, so its length is always measured in bytes. The maximum length is 8 MB.
• VARBINARY: It is the same as BINARY.
• String Constants: Constants refer to fixed values. String constants in Snowflake are always placed between delimiter characters. Snowflake allows either single quotes or dollar symbols to delimit string literals.
  • Single-Quoted String Constants: A string constant can be enclosed between single quote delimiters (e.g. ‘This is a string’). To include a single quote character within a string constant, type two adjacent single quotes
  • Dollar-Quoted String Constants: it is often convenient to enclose a string within dollar symbols when it contains many quote characters. 

Load your data into Snowflake, try now:

3) Logical Snowflake Data Types

BOOLEAN has 2 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.

4) Date and Time Snowflake Data Types

Now, we will discuss the different data types supported by Snowflake for managing dates, times, and timestamps. 

• DATE: Snowflake provides support for a DATE data type (with no time elements). It allows dates in the most common forms (YYYY-MM-DD, DD-MON-YYYY, etc.).
• DATETIME: DATETIME is an alias for TIMESTAMP_NTZ.
• TIME: Snowflake provides support for a TIME data type in the form of HH:MM:SS. It also supports an optional precision parameter for fractional seconds. By default, the precision is 9. All-TIME values should lie between 00:00:00 and 23:59:59.999999999. 
• TIMESTAMP: TIMESTAMP is a user-specified alias for one of the TIMESTAMP_* variants. In every operation where TIMESTAMP is employed, the associated TIMESTAMP_* variation is used. This data type is never stored in tables. 
• TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ: Snowflake provides support for three versions of timestamp:
  • TIMESTAMP_LTZ: It keeps a track of the UTC time with the defined precision. Every operation is performed in the current session’s time zone and is controlled by the TIMEZONE session parameter.
  • TIMESTAMP_NTZ: It keeps a track of “wallclock” time with the defined precision. Every operation is performed without any consideration of the time zone. 
  • TIMESTAMP_TZ: It internally records UTC time along with a related time zone offset. If the time zone is not given, the session time zone offset will be used.

5) Semi-structured Data Types

Semi-structured data types represent arbitrary data structures that are 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. 

• VARIANT: It 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. 
• OBJECT: It is used to store collections of key-value pairs, where the key will be a non-empty string and the value is of VARIANT type. Currently, Snowflake does not support explicitly-typed objects.
• ARRAY: It is used to display dense and sparse arrays of arbitrary size. An index is a non-negative integer (up to 2^31-1) and values are of VARIANT type. Currently, Snowflake does not provide support for fixed-size arrays or arrays with values of a specific non-VARIANT type.

6) Geospatial Snowflake Data Types

Snowflake delivers the GEOGRAPHY data type, which models Earth as if it were a perfect sphere. It follows 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.

Geospatial Object Types: 

The GEOGRAPHY data type supports the following geospatial objects:

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

Conclusion

You have now learned the different data types provided by Snowflake. However, Snowflake does not support certain data types such as ENUM or even user-defined data types. With the help of this guide you can migrate your data from any source such as MongoDB to Snowflake easily within minutes.

Curious about the Snowflake Max Date? Check out our detailed guide to see its use cases and benefits for managing dates in your data.

Visit our Website to Explore Hevo

Use a fully automated tool like Hevo to handle your data for you. You no longer have to worry about choosing the right data type, Hevo will take care of it.

FAQ on Snowflake Data Types