Snowflake CAST & TRY_CAST Commands 101: Syntax & Usage Simplified

When working with an On-premise Data Storage solution, it is not always feasible to scale your Storage and Computation resources instantly without incurring hefty costs. An economical and faster alternative is to opt for Cloud Data Warehousing and Analytics Solutions such as Snowflake.

Built especially for the Cloud, Snowflake can scale Storage and Computational resources independently on the go. Making it super intuitive for Data Analysts, Snowflake provides complete ANSI SQL Language support for Data Blending, Analysis, and Transformations.

To analyse your data efficiently, one of the important tasks is to convert the columns/values with default data types to the correct data types. You can use the Snowflake CAST and TRY_CAST commands to perform the required data type conversions.

What is Snowflake?

Snowflake is a one-stop Cloud SaaS (Software as a Service) platform that can act as a Data Warehouse, Data Lake as well as perform activities associated with Data Science, Data Analytics, Data Sharing, Data Engineering, etc.

Owing to its architecture, you can scale the storage capacity and the Computation resources independently. Operating as a True SaaS Data Platform, you don’t need to install or configure any software and all the maintenance tasks & updates are managed completely by Snowflake.

Key Features of Snowflake

Founded in 2012, Snowflake has evolved into a Data Warehousing & Analytics giant that offers the following eye-catching features:

• Semi-Structured Data Support: Today, data is generated in several formats from sensors, mobile devices, etc. Snowflake provides data storing capabilities for files in JSON, Avro, ORC, Parquet, and XML format. Snowflake also provides support for a read-on schema data type called VARIANT (Size Limit 16 MB) that can store both Structured and Semi-Structured Data.  
• Zero-Copy Cloning: Cloning is an important task for performing testing changes, ad hoc analysis, or building dev/test environments from a Production Database.
• Data Security and Governance: Snowflake supports a variety of authentication methods such as Multi-factor authentication (MFA), Federated authentication & single sign-on (SSO), and OAuth. You can also set Column & Row-level access permissions. Your data is completely encrypted automatically via AES 256 Encryption in both transit and rest.

How to use the Snowflake CAST command?

Snowflake CAST is a data-type conversion command. Snowflake CAST works similar to the TO_ datatype conversion functions. If a particular data type conversion is not possible, it raises an error. Let’s understand the Snowflake CAST in detail via the syntax and a few examples.

A) Snowflake CAST Syntax

CAST( <source_expr> AS <target_data_type> )

As an alternative to CAST, you can use the :: operator for the same functionality.

<source_expr> :: <target_data_type>

The Snowflake CAST command has the following 2 arguments:

• Source_expr: This is the expression of the supported data type you need to convert.
• Target_data_type: This contains the desired data type to which you want to convert the expression. You can also provide the Precision/Scale properties for numbers/decimals. Here, Scale refers to the digits after the decimal point and Precision refers to the total number of digits in the number(including the digits after the decimal).

B) Snowflake CAST Examples

Let’s observe the following use cases and the outputs to understand the working of the Snowflake CAST command:

• Using the Snowflake CAST command to convert a string consisting of a number to a decimal with scale (2):

select cast('1.6845' as decimal(6,2));

Output:

1.68

Here, since the scale provided is insufficient to hold the input expression, the Snowflake CAST function rounds the value.

• Using the :: notation to convert a string to a decimal with a scale of 5,

select '1.6845'::decimal(6,5);

Output:

1.68450

• Using the Snowflake CAST command to convert a number to an integer:

select cast(4.78 as integer);

Output:

5

• Using the Snowflake CAST command to convert a string containing a Date to a Timestamp:

select cast('10-Sep-2021' as timestamp);

Output:

2021-09-10 00:00:00.000

• When the provided precision is insufficient to hold the input value, the Snowflake CAST command raises an error as follows:

Here, precision is set as 4 but the input value has a total of 5 digits, thereby raising the error.

How to use the Snowflake TRY_CAST command?

Snowflake TRY_CAST is a special case of the Snowflake CAST command. It also converts the data type into another, however, it returns a NULL value instead of an error message when the conversion is not possible.

Let’s understand the Snowflake TRY_CAST in detail via the syntax and a few examples.

A) Snowflake TRY_CAST Syntax

TRY_CAST( <source_string_expr> AS <target_data_type> )

Both the arguments are similar to the Snowflake CAST function. The TRY_CAST command only takes a string as an input value. For the Target_data_type argument, it supports VARCHAR (or any of its versions), NUMBER (or any of its versions), DOUBLE, BOOLEAN, DATE, TIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ.

B) Snowflake TRY_CAST Examples

Let’s observe the following use cases and the outputs to understand the working of the Snowflake TRY_CAST command:

• Using the Snowflake TRY_CAST command to convert the String Containing a Date to Timestamp.

select try_cast('05-Mar-2016' as timestamp);

Output: 

2016-03-05 00:00:00.000

• The Snowflake TRY_CAST command returns NULL as the input value has more characters than the provided precision in the target data type.

select try_cast('ABCD' as char(2));

Output:

NULL

Conclusion

In this article, you have learned how to effectively use the Snowflake CAST and TRY_CAST command to perform the data type conversions.

The Snowflake CAST function allows you to set precision and scale parameters for converting the Data type and the value according to your business logic.

When the Data conversion is not successful, Snowflake CAST returns an error message. To remedy that you can use the Snowflake TRY_CAST function that returns a NULL value whenever the data type conversion is not possible.