[TD to BQ] DVT Row Validation Fails with Decimal Primary Keys

Hello team,

We recently discovered that decimal/numeric fields between TD and BQ present and behave quite differently in their respective systems, which impacts DVT's ability to validate the data successfully.

The primary issue is in how TD and BQ store decimal values, specifically any value with a "0" in it.

Example 1: value 1.80

in TD this is stored as "1.80"
In BQ this is stored as "1.8"

When DVT performs a row hash, it converts both values to strings before generating a hash over the value. The hash for "1.8" and the hash for "1.80" are different, leading to a row hash fail 100% of the time.

Similar scenarios:

"0.5000" --> stored in TD as ".5000" and in BQ as "0.5"
"0" --> stored in TD as ".0" and in BQ as "0"

The situation becomes even more complex when these numeric fields are primary keys. In this scenario, we have identified a bug where not only does the row hash fail, but the comparison-fields validation also fails for every single column (regardless of whether it is a decimal column or not).

The only workaround we have identified so far to resolve this is quite messy - it involves manually casting every decimal value to a re-formatted string that matches exactly with how BQ handles numeric fields, and casting the BQ numeric to strings as well.

Example query in TD:

"""
SELECT
CASE
WHEN decimal_col=0 THEN '0'
WHEN decimal_col < 1 THEN '0' || trim(trailing '0' from cast(decimal_col as VARCHAR(100)))
WHEN decimal_col >= 1 and right(trim(trailing '0' from cast(decimal_col as VARCHAR(100))),1) = '.' THEN rtrim(trim(trailing '0' from cast(decimal_col as VARCHAR(100))),'.')
ELSE trim(trailing '0' from cast(decimal_col as VARCHAR(100)))
END AS decimal_col
"""

Example query in BQ:

"""
SELECT CAST(decimal_col) AS STRING AS decimal_col
"""

This case statement needs to be applied to every _primary key_ in order for comparison-fields validations to work as expected, and _every single decimal column_ in the table for row hash validations to work as expected.

Any ways to streamline this from DVT would be greatly appreciated! It's a bit of an edge case, but there are some tables that contain a large number of decimal columns. Performing this workaround for every single one can quickly become time intensive.