1. Snowflake SQL data types
Welcome back! Now, we will learn about some common and unique data types between Snowflake SQL and PostgreSQL, and how to convert data between types.
2. Common data types
Many data types are common between the two SQL flavors, let's look at some of these.
String data types include `VARCHAR`, `CHAR`, and `TEXT`.
3. Common data types
Whole numbers can be stored as `INTEGER`s.
4. Common data types
`BOOLEAN` exists across both flavors as true or false values, commonly used in filtering;
5. Common data types
and datetime data also has several common types.
6. Snowflake SQL data types - NUMBER
What about unique Snowflake data types?
First, we have `NUMBER`, similar to Postgres' `NUMERIC` data type.
We can define precision, or the number of digits, and scale, the number of decimals.
Both have a maximum of 38 - exceeding either of these will cause rounding, resulting in a potential loss of accuracy.
7. Snowflake SQL data types - TIMESTAMP_LTZ
TIMESTAMP_LTZ is a unique Snowflake data type storing date, time, and local time zone information.
We use the TIMESTAMP_LTZ keyword to define it, and the data gets stored in the displayed format.
8. Data type conversion - What?
Sometimes, we might need to convert data between types, such as string to integer.
9. Data type conversion - Why?
There are several reasons to do this.
It optimizes performance, such as converting decimals to integers if we are only interested in whole numbers.
Storing data in its correct format ensures accuracy and reliability when analyzing;
a consistent and accurate dataset is always of higher quality.
10. Data type conversion - How?
Snowflake provides various ways for data type conversion.
First is the `CAST()` function.
We use the 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 use the double colon operator positioned directly after the source data or column. It would look like this: "80'::INT`.
11. CAST
Here, we're converting the `order_timestamp` column into a `DATE` format.
Once converted, we will only see the order date, losing detail about the hour and minute of the order.
12. CAST results
This table shows the original column's data, and the second table shows the new format.
13. Conversion functions
Alternatively, Snowflake provides a range of conversion functions like `TO_VARCHAR` and `TO_DATE`. Let's examine `TO_VARCHAR`.
It converts expressions, like a number or a timestamp, into a string format. This is useful, for example, to combine numeric and string data into a unique ID for reporting purposes.
We write `TO_VARCHAR` and provide data to convert inside parentheses, and it returns the data in `VARCHAR` format.
14. Checking data types
We can use the `DESC TABLE` keywords, followed by the table name, to get information about all columns in a table, including their respective data types.
15. Let's practice!
Wasn't diving into data types and learning about their conversion refreshing? Now, it's your turn to apply this knowledge!