Problem

You store identifiers or timestamps as floating-point numbers (DOUBLE). When you later directly cast those identifiers to STRING, the direct cast produces unexpected text, such as a trailing zero after the decimal (for example, 8.0) or scientific notation (for example, 2.0190812042658E13). 

As a result, you deal with mismatched strings (such as 8 and 8.0) when joined or filtered drop valid rows, or generate unreadable outputs for larger integers (such as IDs, timestamps in format yyyymmddHHMMSS, or phone or order numbers). 

Cause

DOUBLE is a binary floating-point type with ~15–16 digits of precision. When you cast DOUBLE to STRING, formatting the values as text, it is expected behavior to see the .0 suffix or scientific notation. It is also possible that very large “integer-like” values may lose exactness in DOUBLE, which is further changed when casting.

For details on DOUBLE, refer to the DOUBLE type (AWS | Azure | GCP) documentation.

Solution

First cast the column to BIGINT if the value is an integer, then cast the column as a STRING. You can adapt and use following example code. If the value is fractional, cast to DECIMAL to avoid issue with decimals. 

CAST(CAST(<column-name> as BIGINT) as STRING