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!
Table of Contents
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.
Hevo Data, a No-code Data Pipeline helps to Load Data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and loads the data onto the desired Data Warehouse, enriches the data, and transforms it into an analysis-ready form without writing a single line of code.
Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different Business Intelligence (BI) tools as well.
Get Started with Hevo for Free
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
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:
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.
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.
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.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.
Have any further queries? Let us know in the comments section below.
With an analytical perspective and a problem-solving approach, Shruti has extensive experience in solving business problems of data teams by delivering thoroughly researched content pivotal for data practitioners.