Using hash functions to simplify the creation of historical records

As a data engineer, we are not always masters of our data sources. Particularly when using open data, we are at the mercy of the changes made by data producers.

As part of the Euros For Docs project, our primary data source is the Transparence Santé database. This file published daily contains more than 8 million declarations of remuneration from healthcare professionals or health associations by pharmaceutical companies. From one day to the next, the file undergoes many changes: new declarations are added, some disappear and others are simply modified.

Files that change daily

#

To track the evolution of declarations, we want to create a historical file containing all versions of the declarations. Using a unique identifier for each declaration provided in the file, we are able each day to compare a declaration with its last historical value and identify changes in the source data.

Consider the following table as the last observed values of the declarations.

Now consider that today’s file contains the following declarations.

This new file presents 4 different situations.

• Declaration 1 appears in today’s file with exactly the same information as in the historical file. Since nothing has changed in this declaration, we don’t want to do anything about it in the historical file.
• Declaration 2 already existed in the historical file but today’s version has two changes: the amount increased from 200 to 220 and a typo was corrected on the beneficiary (added an S at the end). We want to record this change and add this version of the declaration to history, with today’s modification date.
• Declaration 3 no longer appears in today’s file but existed in the history. We want to document in the historical file the disappearance date of this declaration.
• Declaration 4 is a new declaration that never appeared in the file before this version. We want to add it to the historical file.
• Declaration 5 is not in today’s file but the historical file mentions that the declaration had already disappeared in earlier versions of the file. We therefore expect no processing on this declaration.

The simple case of additions and deletions

#

The appearance of new declarations and the disappearance of old ones are the simplest cases to handle. Since the files provide us with a unique id for declarations, we can simply compare the list of IDs from the historical file with today’s file. This procedure is very simple to implement in SQL and very fast.

For example, you can prepare the new declarations to inject into the file with this simple SQL query.

select
    declarations.*,
    current_date() as modified_date,
    cast(null as date) as deleted_date
from
    declarations
    left join historical_declarations hist using (id)
where
    hist.id is null

This gives us declaration 4 to inject.

And finally, we can identify the disappearance of declaration 3 with the following query.

select
    hist.id
from
    historical_declarations hist
    left join declarations using (id)
where
    hist.deleted_date is null
    and declarations.id is null

Heavy modification detection

#

Managing modified declarations is much more complex. The declarations file actually contains around fifty columns that companies can modify at will. To identify declarations modified since the last version of the file, we need to create a SQL condition that contains the comparison of all fifty columns one by one to find all declarations that were already present in yesterday’s file but at least one value has been changed. Continuing the example started earlier, the query takes the following form.

with
    modified as (
        select
            dec.*
        from
            declarations dec
            join historical_declarations h using (id)
        where
            h.deleted_date is null
            and not (
                coalesce(h.amount, -1) = coalesce(dec.amount, -1)
                and coalesce(h.beneficiary, '__NULL__') = coalesce(dec.beneficiary, '__NULL__')
                and coalesce(h.date, '1900-01-01') = coalesce(dec.date, '1900-01-01')
            )
    )
select
    *,
    current_date() as modified_date,
    cast(null as date) as deleted_date
from
    modified

This gives the following table to inject into our historical file.

This strategy has two limitations.

• The query itself can quickly become unreadable with 50 conditions (instead of 3) to take into account. In truth, modern tools can work around this limitation with templating. At EurosForDocs, it’s Python code that organizes successive SQL calls. We can then create the query dynamically thanks to a simple for loop over the declaration variables.

• The second problem with our strategy is performance. To verify that two versions of a declaration are identical, you must calculate 50 conditions. Some of these conditions can be particularly intensive, such as comparing two long strings. These limitations at the scale of several millions of rows and 50 columns can quickly become the main source of slowdown in a data pipeline.

Hash functions

#

Another more performant solution is based on the concept of a hash function. A hash function transforms arbitrary text into a signature (numeric or alphanumeric). This signature is characteristic of the initial text in the sense that applying the hash function multiple times to the same text will always produce the same signature. On the other hand, two texts that differ, even by a single typo, will obtain radically different signatures.

Let’s take a simple case: French social security numbers. Most numbers are a combination of digits related to our birth date and place. The last two digits are, however, much less well known: they are precisely used in the context of a basic hash function.

The Luhn algorithm is a way to combine the digits of a number to obtain an integer as a signature. The image below shows how to calculate this signature for an arbitrary combination of digits.

In general, the Luhn algorithm is not limited to calculating this signature. In the case of the social security number, the last two digits are added and chosen specifically to force the signature to be a multiple of 10. Thus, if we encounter a number with a signature that is not a multiple of 10, we can be sure that this number contains at least one error. It is possible to use this property to verify that the digit provided in a form is not invalid without having to ask the health insurance for a list of all valid numbers.

Hash function limitations

#

One obvious limitation of hash functions is that there are far fewer unique signatures than possible texts. In the case of social security numbers, only 10 signatures are possible, only one (0) of which really makes sense. Most computer systems today encode integers on 64 bits: which represents 2**64=18 446 744 073 709 551 616 unique signatures. Even though this number is enormous, it is insignificant compared to the set of all texts (and their sub-parts) that can exist. Many texts will therefore share the same signature.

The result of a hash function can seem random and two very similar texts can obtain radically different numbers. This means that the probability that two different texts share the same signature is lower the greater the number of available signatures. Thus, one can always choose a hash function with enough available signatures to consider the probability that two different texts in our dataset share the same signature as zero.

Detecting modifications through hashing

#

EurosForDocs uses a hash function to identify declarations modified between two versions of our source file. For each past or present declaration, the 50 columns of the file are concatenated to create a large text. This text can then be run through a hash function to obtain a signature of this version of the declaration. Identifying modifications is then very fast and very simple to implement: we check for each declaration whether the current signature value matches yesterday’s.

We can then directly calculate this hash on the new declarations and compare it with the historical hash to identify modified declarations.

WITH declas_with_hash AS (
    SELECT
        *,
        HASH(
            CONCAT(
                COALESCE(beneficiary, '__NULL__'),
                COALESCE(amount::VARCHAR, '__NULL__'),
                COALESCE(date::VARCHAR, '__NULL__')
            )
        ) AS declaration_hash
    FROM declarations
)

SELECT
    old.id,
    old.declaration_hash::VARCHAR AS declaration_hash,
    declas_with_hash.declaration_hash::VARCHAR AS updated_declaration_hash
FROM historical_declarations
INNER JOIN declas_with_hash ON historical_declarations.id = declas_with_hash.id

This method provides performance gains. Each day, we only need to compare two integers for each declaration instead of fifty columns: this consumes much less memory (no need to read character strings) and is much faster.

Conclusion

#

Hash functions offer an efficient alternative for detecting modifications in large, frequently updated datasets. By transforming complex sets of columns into simple numeric signatures, they make it possible to significantly simplify SQL queries and improve the performance of data pipelines.

In the case of EurosForDocs, this approach made it possible to reduce the complexity of detecting modifications from dozens of SQL conditions to a simple integer comparison. The gains in terms of execution time and memory consumption are particularly notable when processing millions of rows daily and having to work with machines with limited memory.

This technique applies to many other data engineering use cases: file version tracking, duplicate detection, data integrity validation, or deduplication. It is important to choose a hash function suited to the size of your dataset to minimize the risk of collisions, even though these remain negligible in most practical contexts.