How to correctly add float/double tolerance?

[xdanny]

Hi! I am currently trying out reladiff, and it looks like a really nice project! I am currently running into somewhat curious issues, my diff fails when comparing doubles, even though they are the same column the decimal point is not deterministic and it fails. I tried to introduce rounding as part of the normalize_number, however, as predicted, it fails. What is the best way to approach the problem and where can this tolerance be introduced in the code in order to make it work for this use-case?

Cheers!

[erezsh]

Hello. Please explain more. What databases are you using? What do you mean when you say "even though they are the same column the decimal point is not deterministic".

Usually such issues occur due to different precision between databases, and so the minimal precision needs to be adjusted accordingly for the diff to be accurate.

[xdanny]

Thanks for the quick reply @erezsh!

So in short, I am using only trino, but I am using reladiff to check if the output of a specific ETL pipeline is deterministic (for example in different spark version). The issue is that with doubles, the decimal precision produced by the pipeline is a bit random (as expected). I tried to use format and rounding in the normalize_number function, but there are still differences produced in the pipeline. Therefore I was thinking that maybe I could add some double tolerance in the diff, but I haven’t figured out a good solution to do that.

Cheers for the quick reply, I really appreciate it!

[erezsh]

It sounds to me like you need to reduce the precision.

The precision is normally queried from the database (it's part of the standard sql schema)

It's then processed here and saved for each column: https://github.com/erezsh/sqeleton/blob/8655be43096dd6610c4ed8b5f9713f9a97670e7e/sqeleton/databases/base.py#L263

The precision is then negotiated here: https://github.com/erezsh/reladiff/blob/master/reladiff/hashdiff_tables.py#L104

If you artificially reduce the precision of the relevant columns, at any one of those points, it should work.

If that does solve your situation, we can try to discuss adding such an override as a feature of the API. (as manual_precision or such)

[xdanny]

Thank you! I assume the same can also be achieved with the normalize_number? Anyway, I tried with a modified version of the _validate_and_adjust_columns and I have arbitrarly chose for example 3 as the precision for both columns. However, values still differ, and the scale is the error of the precision (for example with 3 precision values differ by 0.001 sometimes like 0.103 and 0.102 because of the rounding). Is there a way to skip the diff comparison for this column if max error is 0.001?

[erezsh]

Sounds like you need precision 2 ? ...

[xdanny]

It seems that the biggest level of precision is actually 1 with this approach, which brings somewhat limited value. I was hoping to be able to either do this on a row level (lowest level of precision on a row level is taken into account), or skip the comparison that have a certain tolerance like ABS(src.column - tgt.column) < 1e-6 but I assume this is not very feasible?

[erezsh]

The to compare lower tolerances is to reduce the precision. If reducing the precision of two numbers increases the difference between them, something is going wrong.