Resolving Issues with Scientific Notation in QuickSight When Introducing Glue and Athena as Intermediaries

Hello AWS Community,

I'm facing a challenging issue with our AWS data pipeline that affects data visualization in Amazon QuickSight. Initially, our setup involved directly importing CSV data from S3 into QuickSight, where the data was treated as strings and then transformed into DECIMAL for analysis. This configuration handled scientific notation (e.g., 4.0954924E7) correctly, displaying the data accurately in our visualizations.

Recently, we revised our pipeline to incorporate AWS Glue and Amazon Athena between S3 and QuickSight. Here’s the current flow:

    CSV files are stored in S3.
    Data is ingested into a Glue Table with columns set as strings.
    Data is queried through an Athena view, maintaining the string data type.
    Data is then loaded into QuickSight, where it is transformed from string to DECIMAL using the same conversion method as our previous setup.

Post-modification, we are experiencing issues where QuickSight fails to convert values in scientific notation correctly from the Athena output, resulting in these values being displayed as null in the visualizations. This problem did not occur when QuickSight directly accessed data from S3.

Has anyone else encountered this issue when introducing Glue and Athena as intermediary data processing stages? I'm looking for insights or suggestions on configurations or adjustments needed in Glue, Athena, or QuickSight to handle scientific notation as smoothly as in the direct S3 data source setup.

Thank you for any advice or guidance you can provide!

Best regards,

Kasper