---
title: "How to Compare Hashed Columns Before and After a Change in Databricks"
description: "The content explains how to compare old and new MD5 hashed values in Databricks using PySpark SQL after updating the 'id' format in a product table. It details creating a sample table, updating hashes, and using Delta Time Travel to check for mismatches, concluding that mismatches are expected due to the new value format."
url: https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/
date: 2025-02-15
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/pexels-photo-30711884.jpeg?fit=1880%2C1253&ssl=1
categories: ["databricks"]
tags: ["Databrics Hash comparison"]
type: post
lang: en
---

# How to Compare Hashed Columns Before and After a Change in Databricks

I created a technique for comparing hashed columns in a table to verify accuracy before and after modifications. I developed a SQL script to automate the task and reduce the time required for manual comparisons.

1. [MD5 vs SHA-256: Top Differences](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#md5-vs-sha-256-top-differences)
   1. [Can MD5 or SHA-256 be Used for Encryption or Decryption?](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#can-md5-or-sha-256-be-used-for-encryption-or-decryption)
2. [Databricks Hash Comparison](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#databricks-hash-comparison)
   1. [Create a Table and Insert Rows](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#create-a-table-and-insert-rows)
   2. [Update the Table with New Hashes](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#update-the-table-with-new-hashes)
   3. [Compare with Delta Time Travel (Version 1 vs. Current Version)](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#compare-with-delta-time-travel-version-1-vs-current-version)
3. [Conclusion](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/#conclusion)

Related[Steps to Insert Modified Rows Keeping Orignal Data Intact : Databricks SQL Simplified](https://srinimf.com/2025/02/01/steps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified/)

## MD5 vs SHA-256: Top Differences

| Feature | **MD5** | **SHA-256** |
| --- | --- | --- |
| **Hash Type** | Cryptographic hash function | Cryptographic hash function |
| **Output Length** | 128 bits (32-character hex) | 256 bits (64-character hex) |
| **Speed** | Very fast | Slower than MD5 |
| **Security** | Weak — vulnerable to collisions & attacks | Strong — no practical collision attacks yet |
| **Collision Resistance** | Poor — collisions found | Strong |
| **Pre-image Resistance** | Weak | Strong |
| **Designed For** | Integrity checks, digital signatures (historically) | Secure cryptographic applications |
| **Year Released** | 1991 | 2001 (part of SHA-2 family) |
| **Encryption/Decryption** | ❌ **No** — Not reversible (not encryption) | ❌ **No** — Not reversible (not encryption) |
| **Salt Support** | ❌ No built-in support | ❌ No built-in support (but used with salt often) |
| **Password Hashing Use** | ❌ Not recommended | ✅ Recommended (with salt & stretching) |
| **Common Uses** | File integrity checks, duplicate detection | Digital certificates, blockchain, secure hashing |
| **Broken?** | ✅ Yes — considered broken for security | ❌ No |

Related[How to Compare All Columns of a Table as of Version 0 and as of Version 1 in Databricks SQL](https://srinimf.com/2025/03/29/how-to-compare-all-columns-of-a-table-as-of-version-0-and-as-of-version-1-in-databricks-sql/)

---

### Can MD5 or SHA-256 be Used for Encryption or Decryption?

**No.** MD5 and SHA-256 are **hash functions**, not encryption algorithms.

- 🔐 **Hashing** is **one-way**: you input data and get a fixed-size hash output. You **cannot get the original data back** from the hash.
- 🔐 **Encryption** is **two-way**: you can encrypt and later decrypt data using a key.

> 🔁 Think of hashing like blending a smoothie: [you can](https://srinimf.com/2016/09/06/jcl-syntax/)’t un-blend it to get the exact original ingredients.

## **Databricks Hash Comparison**

- Consider a table named product_table with 1 million rows.
- The hash_old is MD5 hashed and uses the ID and brand for hashing.
- The id column has been updated to a new format, generating a new MD5 hash (hash_new).
- Verify if the new hash matches the original values.

### **Create a Table and Insert Rows**

```
-- Enable Delta format in Databricks
CREATE TABLE product_table (
    id STRING,
    brand STRING,
    hash_old STRING,
    hash_new STRING
) USING DELTA;
INSERT INTO product_table VALUES
    ('12345', 'Nike', MD5(CONCAT('12345', 'Nike')), NULL),
    ('67890', 'Adidas', MD5(CONCAT('67890', 'Adidas')), NULL),
    ('54321', 'Puma', MD5(CONCAT('54321', 'Puma')), NULL);
```

Table data

![Table output](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/image-9.png?resize=442%2C81&ssl=1)

### **Update the Table with New Hashes**

Now, assume `id` has been changed to a **new format** (e.g., prefixed with `NEW_`).

```
UPDATE product_table
SET id = CONCAT('NEW_', id), 
    hash_new = MD5(CONCAT(CONCAT('NEW_', id), brand));
SELECT * FROM product_table;
```

Rows after the update

![Table data after update query](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/image-10.png?resize=608%2C89&ssl=1)

### **Compare with Delta Time Travel (Version 1 vs. Current Version)**

Compare the old and new data using the time travel table. Version 1 is the old table because version 0 is for creating the table, and version 1 is after the data has been inserted.

**View the original data (Version 1):**

```
select * from product_table version as of 1;
```

```
id	brand	hash_old	                     hash_new
12345	Nike	b543eb60934e3e5429aa97d97fcf4fe1	null
67890	Adidas	a90b98d5701948d1df23d96f6cea32e5	null
54321	Puma	fcaad176b3cc1370d25df95e84d641fa	null
```

**Compare old and new hashes side by side:**

```
SELECT 
    old.id AS old_id, 
    old.brand, 
    old.hash_old,
    new.id AS new_id, 
    new.hash_new,
    CASE 
        WHEN old.hash_old = new.hash_new THEN 'MATCH' 
        ELSE 'MISMATCH' 
    END AS hash_status
FROM product_table VERSION AS OF 1 old
JOIN product_table new 
ON old.brand = new.brand;
```

The MISMATCH in the output says that the hash is converted successfully and does not match the old value.

![The comparison of old and new hash value](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/image-12.png?resize=731%2C87&ssl=1)

## **Conclusion**

- Do a row count to check if data is lost.
- Check the hash formula consistency.
- The MISMATCHES are fine since the new hash was created based on the new ID.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2025%2F02%2F15%2Fdatabricks-sql-how-to-compare-hashes-effectively-in-pyspark%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2025%2F02%2Fpexels-photo-30711884.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=How%20to%20Compare%20Hashed%20Columns%20Before%20and%20After%20a%20Change%20in%20Databricks)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2025/02/15/databricks-sql-how-to-compare-hashes-effectively-in-pyspark/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Compare%20Hashed%20Columns%20Before%20and%20After%20a%20Change%20in%20Databricks&body=https%3A%2F%2Fsrinimf.com%2F2025%2F02%2F15%2Fdatabricks-sql-how-to-compare-hashes-effectively-in-pyspark%2F&share=email)
-

### Like this:

Like Loading…