Get Started

Snowflake data type and data type conversion

1. Snowflake data type and data type conversion

Welcome back! In this video, we will learn Snowflake's data types and their conversion. We'll answer what it is, why we do it, and how it's done. Sounds interesting? Let's get started.

2. Data types

Here listed are the common data types. These are critical in how data is stored and manipulated in Snowflake. We know some of this from Postgres : VARCHAR, NUMERIC, INT. Next, we'll compare these familiar data types and delve deeper into understanding the others. The VARIANT data type is a powerful tool for storing semi-structured data in Snowflake. We will be covering this in more detail in a later video. Don't miss it!

3. Comparison with Postgres

These data types function similarly in both Snowflake and Postgres. However, they differ in some characteristics. For VARCHAR, Snowflake supports a maximum length of over 250 times longer than Postgres. The NUMERIC data type in Snowflake has a slightly higher default precision than in Postgres, impacting the number of digits stored before and after the decimal. Regarding INTEGER, Snowflake's range far exceeds that of Postgres.

4. DATE

Snowflake supports DATE data type for storing dates. It recognizes various formats like year-month-date or date-month-year, with the default being year-month-date. If we want to include an 'order_date' of the DATE data type in the order table, we will use CREATE TABLE command, specifying column order_date with the DATE keyword. And that's how the date data gets stored in our table.

5. TIME

In Snowflake, the TIME data type captures times in HH:MI:SS, representing hours, minutes, and seconds. We will use the TIME keyword for the column order_time to define the TIME data type. Note here the time format.

6. TIMESTAMP

TIMESTAMP combines DATE and TIME, making it ideal for capturing full date and time values in one field. We use the TIMESTAMP keyword to define it, and the data gets stored in the displayed format.

7. Data type conversion - What?

Data type conversion, as the name suggests, involves changing data from one type to another. For example, if we've stored order_quantity as text, we can transform it into the appropriate type, such as a number, with data type conversion.

8. Data type conversion - Why?

But why would we need this? A prime reason is performance. For instance, storing a number as text hampers computations and slows queries. Storing data in its correct format readies it for immediate use, ensuring accuracy. Proper data conversion not only prevents errors but also maintains the reliability of the data. A consistent and accurate dataset is always of higher quality.

9. Data type conversion - How?

Snowflake provides various methods for data type conversion One way is to use the CAST function. We use keyword CAST, then enclose the source data or column name and the desired target data type, separated by the AS keyword. For instance, to convert a VARCHAR(?) '80' to an INT, we'd use: CAST('80' AS INT). Alternatively, we can achieve the exact conversion using the double colon operator positioned directly after the source data or column. It would look like this: '80'::INT.

10. CAST COLUMN

Let's take another casting example. We're converting the order_date column into a TIMESTAMP format. Once converted, the time defaults to "00" because original data doesn't specify any time details.

11. Conversion functions

Snowflake provides a range of conversion functions like TO_VARCHAR, TO_DATE, and others for specific conversions. Among these, we will particularly focus on TO_DATE here. It converts expressions, like a string or a timestamp, into a date format by extracting the date component from the given timestamp. As shown here, TO_DATE keyword is used with timestamp; the result, extracted date.

12. Let's practice!

Wasn't diving into data types and learning about their conversion refreshing? Now, it's your turn to apply this knowledge. Go ahead, and best of luck!