Hello, thank you for your question.

The issue you’re facing stems from a value exceeding the range allowed by Decimal(38,10) before it can be successfully cast to Double. This happens because the value is already invalid for the Decimal type, causing an overflow during Spark's processing.

Suggested Solution

1. Convert to String First: By converting the Decimal column to String before casting to Double, you can avoid the overflow issue. Once converted to a string, Spark can then interpret the value as a Double.

2. Use Conditional Replacement: Safeguard against uncastable values by using a condition to replace problematic values with null or a default fallback. This avoids runtime errors while maintaining data integrity.

3. Filter Out Extreme Values: Before performing the cast, filter rows with values that exceed the allowable range for Decimal(38,10). This ensures only valid values are processed.

4. Inspect the Data: Examine the range of values in the source table to confirm the scope of the issue. This can guide decisions on whether filtering or another approach is most appropriate.

Hope it helps!

Example:

from pyspark.sql.functions import col, when
from pyspark.sql.types import DecimalType

# Iterate through schema fields and process DecimalType columns
for field in df.schema.fields:
    if isinstance(field.dataType, DecimalType):
        # Filter out extreme values
        df = df.filter(col(field.name) <= 10**38)
        # Handle invalid values by first casting to String and then to Double
        df = df.withColumn(
            field.name,
            when(
                col(field.name).cast("string").cast("double").isNotNull(),
                col(field.name).cast("string").cast("double")
            ).otherwise(None)
        )

We can still combine the filtering and transformation into a single pass to reduce computation:

from pyspark.sql.functions import col, when
from pyspark.sql.types import DecimalType

# Process DecimalType columns in one step
for field in df.schema.fields:
    if isinstance(field.dataType, DecimalType):
        df = df.withColumn(
            field.name,
            when(
                (col(field.name) <= 10**38) & col(field.name).cast("string").cast("double").isNotNull(),
                col(field.name).cast("string").cast("double")
            ).otherwise(None)  # Replace invalid or out-of-range values with null
        )