How to CAST in Snowflake

What does the CAST function do in Snowflake?

CAST converts a value from its current data type to a target data type. Use it to change strings to dates, numbers to strings, or timestamps to dates, ensuring your data fits the schema you need.

How do you write CAST syntax?

Write CAST(expression AS data_type) or use the shorthand expression::data_type. Both forms behave the same; the full keyword form is clearer in shared code.

CAST vs :: shorthand—when should you use each?

Use the full CAST() form in production code for readability. The :: shorthand is handy for quick ad-hoc queries in Galaxy’s editor.

How do you cast VARCHAR order dates to DATE?

When Orders.order_date is stored as VARCHAR, convert it to DATE so Snowflake can sort and filter correctly:

SELECT id,
CAST(order_date AS DATE) AS order_date
FROM Orders
ORDER BY order_date DESC;

How do you convert numeric strings to NUMBER?

If Products.price is VARCHAR, CAST it to NUMBER before arithmetic:

SELECT id,
name,
CAST(price AS NUMBER(10,2)) AS price_num,
stock * CAST(price AS NUMBER(10,2)) AS inventory_value
FROM Products;

How do you safely CAST with possible bad data?

Use TRY_CAST to avoid runtime errors when data might not convert:

SELECT id,
TRY_CAST(email AS VARCHAR) AS safe_email
FROM Customers;

Best practices for CAST in production

1) Pick explicit precision and scale for NUMBER to prevent silent rounding. 2) Always document casts in pull requests. 3) Prefer TRY_CAST during data loading, then fix rows that return NULL.

Common mistakes and fixes

Implicit rely: Assuming Snowflake will auto-cast can break joins. Always CAST explicitly.
Wrong precision: Casting to NUMBER without scale can truncate decimals. Declare scale, e.g., NUMBER(10,2).

FAQ

Is CAST slower than ::?

No. Both compile to the same plan; performance is identical.

Does CAST change stored data?

CAST only affects query output. Use ALTER TABLE to change a column’s type permanently.

When should I use CONVERT instead of CAST?

CONVERT is an alias retained for compatibility. Prefer CAST for clarity.