TRY_CAST and implicit casts

Concepts covered: Safe Casting

Mixing 2 types in 1 expression forces the engine to pick a common type, and for numbers it does so without being asked. An INTEGER added to a DOUBLE becomes a DOUBLE first. An INTEGER multiplied by a DECIMAL is treated as a DECIMAL with scale 0, so the CAST(quantity AS DECIMAL(5,0)) in the tax example could have been left out and the engine would have promoted the INTEGER the same way. These silent promotions are implicit casts. They run toward the type that can hold the wider range, and range is a different property from exactness: a BIGINT, the 64-bit whole-number type, loses its last digits above 2^53 when promoted to DOUBLE, and a DECIMAL mixed with a DOUBLE becomes a DOUBLE, so DECIMAL 0.1 plus DOUBLE 0.2 gives 0.30000000000000004. A single DOUBLE column in a money calculation turns the whole result into an approximation. The promotion that bites people most often is the one that never happens: 2 INTEGER values divided give an INTEGER, and the fractional part is discarded.

SELECT
  campaign,
  clicks,
  views,
  clicks / views AS integer_ratio,
  CAST(clicks AS DOUBLE) / views AS double_ratio,
  CAST(clicks AS DECIMAL(10,4)) / views AS decimal_ratio
FROM metrics
ORDER BY campaign

Campaign spring_sale has 37 clicks and 120 views. The integer ratio is 0, because 37 divided by 120 is 0 with 37 left over, and INTEGER keeps the whole part. Once either side is DOUBLE the whole expression is DOUBLE and the result is 0.308333 with a tail of digits. The DECIMAL version rounds to the 4 places of its scale, 0.3083, so the type you cast to decides the digits a reader sees. Campaign retarget with 600 clicks over 400 views comes out 1, 1.5 and 1.5000: the integer version keeps the 1 and drops the half.

Where implicit casts stop

Text never promotes. '5' + 1 fails with a type error, because the engine will not guess whether the text holds a number, and the same error appears when a VARCHAR column meets an INTEGER in a comparison. That refusal protects you from a column of ids where most rows look numeric and 1 row holds N/A. You already know CAST. The other explicit tool, TRY_CAST, has the same syntax and 1 different behavior: when a value cannot become the target type, TRY_CAST returns NULL for that row and the query keeps running.

TRY_CAST on a messy feed

raw_signups is a feed where every field arrived as text and some rows were typed by hand. Each column below is converted to the type it should have had. Rows 2 and 3 carry the damage; predict which of their cells come back NULL and why. The last column applies IS NULL to the converted age to flag a value that failed.

SELECT
  row_id,
  TRY_CAST(age_text AS INTEGER) AS age,
  TRY_CAST(signup_text AS TIMESTAMP) AS signed_up,
  TRY_CAST(paid_text AS BOOLEAN) AS paid,
  TRY_CAST(amount_text AS DECIMAL(5,2)) AS amount,
  TRY_CAST(age_text AS INTEGER) IS NULL AS age_rejected
FROM raw_signups
ORDER BY row_id

Row 2 loses every conversion. thirty holds no digits, February 30 is a date that does not exist, maybe is not on the short list BOOLEAN accepts (the words true and false or their first letters in any case, plus 1 and 0), and 1200.00 needs 6 digits where DECIMAL(5,2) allows 5. Row 3 is subtler. 27.5 fails as an INTEGER because the conversion demands a whole number and refuses to round, and 03/15/2024 fails because the TIMESTAMP parser expects the date written year first with dashes between the parts. Its 1 does convert, to TRUE. Row 4 shows that an empty string becomes NULL as an INTEGER, while the same empty string as a VARCHAR is a real value that holds no characters.

What does TRY_CAST('12.0' AS INTEGER) return?

• 12, because the fractional part is zero and drops away
• NULL, because '12.0' does not parse as a whole number and TRY_CAST returns NULL on failure
• 12.0, because the engine widens INTEGER to DOUBLE to keep the value
• An error, because text can never be converted to INTEGER

The cost of not crashing

TRY_CAST trades a loud failure for a quiet NULL, and a quiet NULL can hide a broken feed for weeks. After the conversion, the NULLs from bad input are identical to the NULLs from values that were missing to begin with: row 2's age and a row whose age was never filled in look the same. Keep the original text column next to the converted one, so a NULL can be traced to the value that produced it. Then compute a rejected flag, as the last column does, so the share of failures can be watched over time and a jump in it sends someone to look at the source.

CAST

• Fails the whole query on 1 bad value
• Right when the data is already clean
• Surfaces a broken upstream feed at once
• Syntax: CAST(x AS type)

TRY_CAST

• Returns NULL for the bad value and keeps going
• Right for raw feeds and hand-typed input
• Needs a rejected flag so failures stay visible
• Syntax: TRY_CAST(x AS type)

About this lesson

This section is part of the Data Types: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.