HOW TO: Use Snowflake CAST for Data Type Conversion (2024)

So you want to make sure your data is in the right format. That’s the key to processing and analysis. Think about it: you’re doing numerical computations, date manipulations, or string operations—you need the right format. Data type conversion is the answer. Say you have a dataset where dates are stored as strings, but you need to do date-based calculations. Or perhaps you have numeric data stored as strings, and you need to do math. These are the scenarios where data type conversion is essential. And if you work with Snowflake then it has a function for this: the CAST function.

In this article, we will cover the ins and outs of the Snowflake CAST function, provide some examples, and offer expert advice so you can start using it like a pro.

What is the CAST Function in Snowflake?

Snowflake CAST function is used to convert data from one type to another. This function is essential for ensuring that data is in the correct format for various operations and analyses. It exactly works like a data transformer, taking your input and reshaping it into the format you need.

CAST function is not unique to Snowflake—it's a standard SQL function. However, Snowflake's implementation comes with its own variation and capabilities that make it particularly versatile.

Next, we'll cover the syntax and basic usage of Snowflake CAST. This will give you the foundation to start using this function well in your Snowflake queries.

Syntax and Basic Usage of Snowflake CAST

Now that we understand what Snowflake CAST does, let's look at how to use it in Snowflake. The syntax is straightforward.

Basic Syntax:

The basic syntax for the Snowflake CAST function is:

CAST(expression AS target_data_type)

Alternatively,

You can use the :: operator for the same functionality:

expression::target_data_type

Parameters:

1) <expression>

This is the value or column you want to convert. It could be a literal value, a column name or even a more complex expression.

2) <target_data_type>

This is the target Snowflake data type you want to convert the expression to. Snowflake supports many data types, including NUMBER, VARCHAR, DATE, TIMESTAMP and more. You can also specify precision and scale for numeric types, or length for string types, like this: NUMBER(10,2) or VARCHAR(50).

Basic Snowflake Cast Example:

Let's consider a simple example where we convert a string to a number:

SELECT CAST('123' AS NUMBER);

To check the data type of the result of a query in Snowflake, you can use the SYSTEM$TYPEOF function.

SELECT SYSTEM$TYPEOF(CAST('123' AS NUMBER));

As you can see, Snowflake CAST converts the string "123" to the number 123.

As we progress through this article, we'll explore more complex scenarios and best practices to help you leverage the Snowflake CAST function to its full potential.

What is TRY_CAST?

Snowflake TRY CAST is a safer alternative to the CAST function. While the CAST function raises an error if the conversion fails, Snowflake TRY_CAST returns NULL, making it more robust for handling conversion errors.

Snowflake TRY_CAST function is useful when you anticipate potential conversion errors and want to handle them gracefully without interrupting your data processing pipeline.

Syntax and Basic Usage of Snowflake TRY_CAST

Basic Syntax:

The basic syntax for the TRY_CAST function is as follows:

TRY_CAST(expression AS target_data_type)

Parameters:

The parameters for Snowflake TRY_CAST are essentially the same as for Snowflake CAST:

1) expression

This can be any valid SQL expression that results in a value. Examples include:

• A column name: TRY_CAST(user_id AS VARCHAR)
• A literal value: TRY_CAST('2023-07-23' AS DATE)
• A more complex expression: TRY_CAST(TRIM(user_input) AS NUMBER)

2) <target_data_type>

This specifies the target Snowflake data type. As with CAST, you can specify precision and scale for numeric types or length for string types.

Error Handling Using Snowflake TRY_CAST

Let's look at a practical example to see how Snowflake TRY_CAST handles errors:

SELECT TRY_CAST('123' AS NUMBER) AS converted_number;

In this scenario:

• If the expression contains “123”, the converted_number will be 123.

• If the expression contains “abc”, the converted_number will be NULL.

• If the expression contains “12.3aaabc”, the converted_number will be NULL.

Snowflake TRY_CAST is particularly useful when:

• You're working with data from external sources where quality isn't guaranteed.
• You want to identify rows with problematic data for further investigation.
• You need to ensure your ETL processes are robust and can handle unexpected input.

Limitations of Snowflake TRY_CAST

Snowflake TRY_CAST is awesome for handling conversion errors, but it's not a magic solution. There are some things it can't do, and you should know what they are.

1) Limited Input Types

The biggest limitation of TRY_CAST is that it only takes string inputs. So you can only cast from string to another Snowflake data type, not between other types.

For example, this will work:

SELECT TRY_CAST('123' AS NUMBER);

But this will not:

SELECT TRY_CAST(123 AS VARCHAR);

If you need to convert between non-string types, you'll need to use the regular Snowflake CAST function or convert to a string first.

2) Limited Target Data Types

Snowflake TRY_CAST supports conversion to a subset of Snowflake data types. The supported target Snowflake data types are:

• VARCHAR
• NUMBER
• DOUBLE
• BOOLEAN
• DATE
• TIME
• TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ

You can't use Snowflake TRY_CAST to convert to more specialized types like ARRAY or OBJECT directly. For these types of conversions, you might need to use a combination of Snowflake TRY_CAST and other Snowflake functions.

3) Behavior with NULL Values

So, Snowflake TRY_CAST returns NULL if you give it NULL inputs, just like Snowflake CAST does. The problem is, that you cannot distinguish between a failed conversion and a NULL input without additional logic.

4) No Detailed Error Info

Unlike Snowflake CAST, TRY_CAST doesn't give you a helpful error message when things go wrong. You just get NULL, and you're left wondering why it didn't work.

5) Potential for Silent Errors

Because TRY_CAST doesn't error out, you might not even notice when conversions fail. This can be good (your query keeps running) or bad (you miss data quality issues).

6) Performance Considerations

Using Snowflake TRY_CAST can be slower than CAST, especially with huge datasets where most conversions work. That's because Snowflake TRY_CAST has to check for failures every time.

Despite all these limitations, Snowflake TRY_CAST is still super useful in many situations. You just need to know when to use it and when to use something else.

Next, we'll compare Snowflake CAST and Snowflake TRY_CAST head-to-head to help you decide.

What Is the Difference Between CAST and TRY_CAST in Snowflake?

Let's compare Snowflake CAST vs Snowflake TRY_CAST across several key dimensions:

The choice between Snowflake CAST and Snowflake TRY_CAST depends on your use case. It depends on your data quality and error-handling needs.

Practical Examples of Snowflake CAST Usage

Now that we've covered the theory, let's dive into some practical examples of how to use Snowflake CAST.

Note: In the following examples, we will use the SYSTEM$TYPEOF function to display the data types of both the original values and the converted values.

Example 1—Converting a String to an Integer Using Snowflake CAST

Here is how you can convert a string to an integer in Snowflake:

SELECT 
    '12345' AS original_value, 
    SYSTEM$TYPEOF('12345') AS original_value_data_type,
    CAST('12345' AS INTEGER) AS converted_value,
    SYSTEM$TYPEOF(CAST('12345' AS INTEGER)) AS converted_value_data_type;

As you can see, in this query, the string "12345" is converted to the integer 12345. The Snowflake CAST function ensures that the conversion handles only valid numerical string values, raising an error if the conversion is not possible​.

Example 2—Converting a String to a Date Using Snowflake CAST

To convert a string formatted as a date to an actual DATE type:

SELECT 
    '2024-07-23' AS original_value, 
    SYSTEM$TYPEOF('2024-07-23') AS original_value_data_type,
    CAST('2024-07-23' AS DATE) AS converted_value,
    SYSTEM$TYPEOF(CAST('2024-07-23' AS DATE)) AS converted_value_data_type;

Here, the string “2024-07-23” is transformed into the date 2024-07-23. The format of the string must match the expected date format, otherwise, an error will be thrown​​.

Example 3—Converting a Number to a Decimal with Specified Precision and Scale Using Snowflake CAST

When converting a number to a decimal with specific precision and scale:

SELECT 
    123.4567 AS original_value, 
    SYSTEM$TYPEOF(123.4567) AS original_value_data_type,
    CAST(123.4567 AS DECIMAL(5,2)) AS converted_value,
    SYSTEM$TYPEOF(CAST(123.4567 AS DECIMAL(5,2))) AS converted_value_data_type;

In this example, the number 123.4567 is converted to a decimal with a precision of 5 and a scale of 2, resulting in 123.46. Snowflake rounds the number to fit the specified scale.

Example 4—Converting a Float to an Integer Using Snowflake CAST

To convert a float to an integer:

SELECT 
    45.67 AS original_value, 
    SYSTEM$TYPEOF(45.67) AS original_value_data_type,
    CAST(45.67 AS INTEGER) AS converted_value,
    SYSTEM$TYPEOF(CAST(45.67 AS INTEGER)) AS converted_value_data_type;

In this case, the float 45.67 is converted to the integer 46, with Snowflake rounding the value to the nearest integer.

Example 5—Converting a String to a Decimal Using Snowflake CAST

For converting a string containing a decimal number to a DECIMAL type:

SELECT 
    '789.1234' AS original_value, 
    SYSTEM$TYPEOF('789.1234') AS original_value_data_type,
    CAST('789.1234' AS DECIMAL(7,3)) AS converted_value,
    SYSTEM$TYPEOF(CAST('789.1234' AS DECIMAL(7,3))) AS converted_value_data_type;

As you can see, this will convert the string "789.1234" to the decimal 789.123, where the specified precision is 7 and the scale is 3​.

Example 6—Converting a Timestamp to a Date Using Snowflake CAST

To strip the time part from a timestamp and convert it to a date:

SELECT 
    '2024-07-23 14:32:29' AS original_value, 
    SYSTEM$TYPEOF('2024-07-23 14:32:29') AS original_value_data_type,
    CAST('2024-07-23 14:32:29' AS DATE) AS converted_value,
    SYSTEM$TYPEOF(CAST('2024-07-23 14:32:29' AS DATE)) AS converted_value_data_type;

In this query, the timestamp “2024-07-23 14:32:29” is converted to the date 2024-07-23, effectively removing the time component.

Snowflake CAST can do a lot more than what you see here. This is just the tip of the iceberg. You can use it to convert Snowflake data types in a flash, or make super-precise adjustments. Whatever Snowflake data type you're dealing with, Snowflake CAST can tackle it with ease.

Limitations of Snowflake CAST

Here are the limitations of the Snowflake CAST function:

1) Type Conversion Issues

If you try to convert a data type and it doesn't work, the Snowflake CAST function will throw an error. Like, if you try to convert a string that doesn't fit the target format, you'll get an error.

2) Precision and Scale Constraints

If the precision or scale for numeric conversions is insufficient to hold the input value, Snowflake CAST will round the value or raise an error. For instance, if you're casting a number with way more decimal places than allowed, CAST will round it. And if it's still too big—you'll get an error.

3) Unsupported Conversions

Certain Snowflake data type conversions are not supported directly by Snowflake CAST. For e.g. converting objects or arrays may need extra handling or different Snowflake functions.

4) Format String Limitations

For date, time, and timestamp Snowflake data types, it must match the source format exactly. Any mismatch will cause the conversion to fail​.

5) NULL Handling

Snowflake CAST function does not handle conversion failures well—it will throw an error instead of returning a NULL. This can be mitigated by using TRY_CAST, which returns NULL for failed conversions.

6) Implicit Type Limitations

Whenever you are using the :: syntax as a shorthand for Snowflake CAST, you cannot specify additional properties like RENAME FIELDS or ADD FIELDS for structured objects​.

Want to Learn More?

Want to learn more about Snowflake CAST and TRY_CAST functions? We've got you covered. Here are some helpful resources.

• Snowflake CAST, ::
• Snowflake TRY_CAST
• Snowflake Data Type 101

Conclusion

So, that's a wrap! Snowflake CAST function is super powerful when it comes to switching/converting data types. It makes sure your data is compatible and accurate while processing. Once you get the hang of its syntax, usage, and what it can't do, you'll be able to use CAST to handle all kinds of data conversions in Snowflake.

In this article, we have covered:

• What is Snowflake CAST Function?
• What is TRY_CAST?
• Limitations of Snowflake TRY_CAST
• What Is the Difference Between CAST and TRY_CAST in Snowflake?
• Practical Examples of Snowflake CAST Usage
• Limitations of Snowflake CAST

… and so much more!

FAQs

What is the CAST function in Snowflake?

Snowflake CAST function converts a value of one Snowflake data type into another data type using the syntax: CAST(expression AS target_data_type).

How does the :: operator relate to Snowflake CAST?

The :: operator is an alternative syntax for CAST. For example, expression::target_data_type.

What is the difference between CAST and TRY_CAST?

Snowflake CAST throws an error if the conversion fails, while Snowflake TRY_CAST returns NULL in such cases.

How to handle errors during type conversion in Snowflake?

Use Snowflake TRY_CAST to handle errors gracefully by returning NULL instead of failing​​

What types can be converted using Snowflake CAST?

Most Snowflake data types can be converted, including VARCHAR, NUMBER, DATE, TIMESTAMP, and BOOLEAN​​.

Can Snowflake CAST handle precision and scale for numeric types?

Yes, you can specify precision and scale.

What happens if CAST conversion fails?

Snowflake CAST will raise an error if the conversion isn't possible.

What is the syntax for Snowflake TRY_CAST?

TRY_CAST(expression AS target_data_type)​​.

What happens when TRY_CAST fails?

It returns NULL instead of raising an error​​.

How to cast a variant to a specific type in Snowflake?

Use Snowflake CAST with the appropriate type, e.g., CAST(variant_value AS NUMBER)​

How to ensure safe conversion with TRY_CAST?

You can use Snowflake TRY_CAST to avoid errors during conversion and receive NULL if the conversion fails​

What types of expressions can TRY_CAST handle?

Snowflake TRY_CAST handles string expressions converting to types like VARCHAR, NUMBER, DOUBLE, BOOLEAN, DATE, TIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ.