Azure Data Flows md5 function does not recognize decimal values as unique

user_8159777 0
Viewed 436

We use Azure data flows to generate a history of our data tables in an Azure SQL Data Warehouse. In the dataflows we use md5 or sha1 functions on all columns to generate unique row fingerprints to detect changes in records, or identify deleted/new records (pretty standard history technology).

For some data tables, we have columns that contain decimal values (datatype DECIMAL(18,1) for instance). If I look at the md5 hashes generate on one integer, one text and one decimal column, I would expect that these three rows have different hashes generated in Azure Data Flows: enter image description here

However, these three rows get the exact same hash, which means that we are not able to detect a change in the field [value] for the records with [id] = 1. If the decimal values are stored as text in the database (or converted to string in the md5 function), the hashes are different:

enter image description here

This has resulted in some of our history tables not keeping an accurate record of data where only the value in a decimal column has changed.

My question: does anybody know if this is 'by design' for Azure Data Flows, or that this is a bug that need to be fixed by Microsoft?

azure-data-flow azure-data-factory-2 checksum azure md5
1 Answers

Yes this is by design, and at same time it's a bug.

I also faced it, me and the team here came up with same workaround as you but doubled the execution time.

After a around 1 hour of discussion of Microsoft what we found out was that:

  • floats are not affected
  • datatypes that specify a precision and scale are affected
  • the behavior isof md5 and last one is, rounding it up Integer and apply md5 on it.

MD5 will return the same for these values:

  • 0
  • 0.1
  • 0.4

The same behavior for values below:

  • 0.5
  • 0.6
  • 1

Different hashes will be returned for:

  • 0.4
  • 0.9
  • 1.5
  • 2.5

I was told the fix is predicted for February.

If don't use column functions like me (byNames), you can multiply your value by a power of base 10 with scale as exponent.

That will faster than the toString.

user_47820090
Related