Hashing is a pretty common technique for transforming some data value into another, generally fixed-length, value. The good thing about hashing is hashing algorithms are deterministic, meaning, given the same input, you will always get the same output.

Hashing is used across a variety of applications, from database indexing, password storage, compression, cryptography, file contents comparison, and much much more.

However, I'm here to suggest there's tremendous value to leveraging hashes in data pipelines. Particularly when it comes to joining and comparing data. If you aren't using hashing in your data pipelines by default, you should be.

Examples of why you should be hashing

With most (if not all) data pipelines, the goal is to get data from a raw source into a destination. You could always truncate and reload every time, pulling and loading the entire dataset. However, this is not efficient. So, generally, some form of change data capture process is employed to ensure we don't have to pull and reload all the data each time.

You might do a comparison of a unique record number, a primary key, columns and their contents, or some timestamp. All of these work, however, what if the use case becomes more complex, or doesn't support these kinds of methodologies? That's where hashing comes in.

Hashing allows you to store the contents of your data in a single, much shorter, deterministic field. Comparing data based on a single field is way simpler than using multiple fields. As well, it keeps code and processes clean and simple. For example, you could collapse 200 columns quickly and easily into a single sha256 hexidecimal that represents the unique contents of that data row. Wouldn't that make things much easier?

Below are some examples of where hashing values comes in handy.

Change Data Capture

Say you want to capture changes to records, and replace or update them? The more columns you have the more comparisons you have to do. Say you have 200 columns you have to compare. You would have to do 200 "column <> column". While it can be done, I'm sure it's just as annoying for you as it would be for me to type all those comparisons out.

As well, say a new column gets added? You'd have to change your code to add this additional column. However, if you're hashing all columns into a single value by default, code updates should just flow through without any (or minimal) changes. As well, you'll detect a change in existing records with the addition of this new field, because the hash value is deterministic, and now there's an additional value.

This would result in your comparison being as simple as:

create temp table records_to_change as
    select *
    from source s
        inner join destination d on s.key = d.key
            and s._rowhash <> d._rowhash
;
delete from destination
    where key in (select key from records_to_change)
;
insert into destination
    select *
    from records_to_change
;

This is incredibly simplified, and you'd likely want to check for new records as well. However, you can see where I'm headed, this would be way simpler than comparing each column.

Composite/Complex Key

Say you have data where no single column uniquely identifies that record, but instead it's a combination of columns (i.e., a composite/complex key). When checking for new records, you'd have to check to see if that set of unique columns exists in your destination.

With hashing, you could hash those unique columns into a single column for comparison. Simplifying your code, and depending on the number of columns that have to be compared, saves you a tremendous amount of processing power. Once creating a unique hash to use as a key for your data, finding new records could be as simple as:

insert into destination
    select *
    from source s
    where _keyhash not in (select _keyhash from destination)
;

Creating Keys Between Tables

Say similar to the above example, you have a composite/complex key for one table. You have a related table that you would like to join to your first table. Good thing is, this related table has all the same columns that make up the composite/complex key of the first table.

You could use all those columns to join these two tables. However, the more columns, the more complex and long the code becomes. So, you could hash these values in both these tables to create a key relationship between these two tables. Your analytics team will love you for this. Joining becomes super simple:

select a.*, b.*
from table_a a
    inner join table_b b on a._keyhash = b._keyhash

No Unique Key

Say your source data has 200 columns, and the entire row is unique. Meaning there is no real unique key. You would have to do a comparison of each column or a composite of all values. If you concatenate these values, comparing the contents of all these columns could be incredibly cumbersome.

However, if you hash all columns, you have one, deterministic, shorter value that represents the contents of row of data. This could make identifying new records as simple as:

insert into destination
    select *
    from source
    where _rowhash not in (select _rowhash from destination)
;

You could see how much simpler this is than if you were to compare all columns between source and destination.

Other use cases

While these additional use cases are nowhere near comprehensive, they are some other examples where hashing can be incredibly valuable.

Slowly Changing Dimensions

One of the biggest use cases I can think of where hashing has made my life easier is slowly changing dimensional modeling, or more generally, historical data capture. This is already a complex process to build, but the more data you have to compare, the more complex it can become to code. However, employing hashing will allow the evaluation of row contents quickly and efficiently.

Deidentifying data

Another use case is deidentifying data. If you work in a situation where there is PII, you know the importance of protecting personal data. Hashing can provide a method for deidentifying data by hashing personal information, and allows you to split that data off separately in a more secure location. The hash still allows you to tie data back to its user, but keeping it separated allows for better security and protection of PII.

Some code

So how do you hash columns? Well, it varies. Database providers generally have an available method for hashing values. Some examples include:

• Microsoft SQL Server - hashbytes()

• PostgreSQL - sha256()

• Vertica - sha256()

Using these functions, you'll concatenate your column values together into a new value, and pass it to one of these functions. It will then create your hash value.

Now, what if your pipelines are built in other languages? Namely Python or PySpark? Well, there are options. Python, for example, you can utilize hashlib in combination with a Pandas dataframe to generate a hash quickly:

import pandas as pd
import hashlib

#list of columns to hash
hash_cols = ["col1","col2","col3"]
#create a new dataframe
dataframe["_hash_column"] = (
    #use hash columns from the dataframe    
    dataframe[hash_cols]
        #concatenate them together with a separator of "^"
        .apply(lambda x: 
            "^".join(x.astype(str)),axis=1
        )
        #hash that concatenated value
        .apply(lambda value:
            hashlib.sha256(str(value).encode('utf-8')
        )
        #process the hash into a value
        .hexdigest()
)

In PySpark you can use sha2() and concat_ws():

from pyspark.sql.functions import *
#list of columns to hash
hash_cols = ["col1","col2","col3"]
#create a new dataframe
dataframe = (     
    #add column to new dataframe with name of _hash_column    
    dataframe.withColumn(
        "_hash_column"
        #use sha2 to hash the concatenated columns
        ,sha2(
            #this concatenates all column values in our 
            #list of columns, separated by "^"
            concat_ws("^",hash_cols)
            ,256
            )
        )
)

What are some downsides?

No solution applies in all situations, hashing is no different. Here are some things to consider depending on your situation:

---

Calculating hashes can be inefficient and intensive in certain situations

For example, MSSQL hashbytes() has the potential to slow down your pipelines with more rows and more columns. With more data, performance is always at risk. In these situations consider if hashing is required or if there is another more optimal solution.

---

Joining on hashes alone may be inefficient

Particularly for hashes used as surrogates for composite/complex keys, the more data you have, the potentially more inefficient it can become. So performance tuning is key. In situations where you have a lot of data, I recommend ensuring you appropriately index or partition your data to ensure optimal performance.

---

Change of columns could result in code changes and different hash values

If you are using a specific list of columns, and you need a new column in this list, adding a new column would require a code change. Adding a new column would also change hash values. This becomes problematic in situations where the hash represents a composite key. That being said, situations like this are rare, and I've personally never experienced a situation like this.

---

Planning is required in more complex situations or changes

In situations where you are building keys to join across tables, you will likely need to know what relationships exist before building the pipeline. Always do your research.

As well, adding a new hash column requires some careful planning. There would need to be consideration for both back population and changes to the pipeline code. That being said, any DDL or pipeline change always requires planning. However, if I know data pipelines, once they hit production, changes generally are few and far between.

Conclusion

All the cons aside, hashing provides some real value in certain situations. I think one of the biggest benefits is the simplicity of having a single column that represents the content of that data row. I generally always add a column representing a hash of that entire row of data. It helps tremendously when incrementing data.

That being said, evaluate whether or not it's right for your situation. Otherwise, just consider hashing another tool in your data engineering tool belt for when the need arises!

Thanks for reading, and I hope you found this valuable!