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.
Effortlessly manage and integrate your data sources with Snowflake using Hevo Data. Our platform streamlines the process of connecting various sources to Snowflake, ensuring a smooth and efficient migration.
Why Choose Hevo Data?
Seamless Integration
Real-Time Data Processing
User-Friendly Interface
Automated Data Transformation
Reliable Performance
Optimize Your Snowflake Migration with Hevo
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.
Load your data into Snowflake, try now:
Integrate Oracle to Snowflake
Integrate PostgreSQL to Snowflake
Integrate MongoDB to Snowflake
Integrate Salesforce to Snowflake
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.
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
What is the data type in Snowflake?
Snowflake supports various data types like numeric, string, boolean, etc.
What data type is Snowflake float?
Snowflake uses double-precision (64-bit) IEEE 754 floating-point numbers.
What type of SQL is Snowflake?
Snowflake supports standard SQL, including a subset of ANSI SQL:1999 and the SQL:2003 analytic extensions.
How to check data type in Snowflake SQL?
You can check data type in snowflake SQL using TYPEOF function. It returns the type of a value stored in a VARIANT column.
Shruti brings a wealth of experience to the data industry, specializing in solving critical business challenges for data teams. With a keen analytical perspective and a strong problem-solving approach, she delivers meticulously researched content that is indispensable for data practitioners. Her work is instrumental in driving innovation and operational efficiency within the data-driven landscape, making her a valuable asset in today's competitive market.