Problem
When you try to run a job with string data and the target schema is decimal data type, it fails with the following error message.
[CAST_OVERFLOW_IN_TABLE_INSERT] Fail to assign a value of "STRING" type to the "DECIMAL(p,s)" type column or variable `dataColumn` due to an overflow.
Cause
The string data is longer than the target column’s size.
Apache Spark performs implicit crosscasting (because ANSI mode is disabled by default for non-serverless computes) based on the target data type. When the string value exceeds the limit of the decimal data type, an overflow exception is thrown.
Solution
Important
You may want to explicitly cast string to decimal(p,s) before inserting, but this action will insert null values and still result in overflow in the defined decimal(p,s) data type.
Instead, there are two options available.
The first option is to increase the limit of decimal(p,s). The maximum for the decimal data type is 38. The following image shows an example notebook creating a table with decimal(19,9). Then a decimal formatted as a string is inserted. It is successfully inserted and the output is modified to fit the target schema.
The second option is to change the source data type to an equivalent type to the target, instead of string. This will ensure that the data types match and prevent any overflow exceptions.
For more information on the decimal data type, refer to the DECIMAL TYPE (AWS | Azure | GCP) documentation.