A lightweight hashing algorithm with use cases in analytics
Have you heard about CRC32? It’s a lightweight hashing algorithm originally designed to detect errors in data transmission. In SQL, we can use CRC32 to quickly generate a compact numeric “fingerprint” of a long string – without carrying around the full text.
How we use CRC32 at Bright Analytics
One of our main use cases for CRC32 is optimising SQL joins on large text fields, like long URLs. It’s useful as it allows you to create a numeric ID that represents a string value, making it perfect for use cases where you need to join large flat, raw data on long strings to a lookup table.
For example, if you want to categorise landing pages in your Google Analytics data based on the area of your site that they sit in, but don’t want to use the long url as key in your join.
Joining directly on massive string values can be extremely costly and slow, especially when dealing with millions of records as SQL engines must compare the full text, character by character. To mitigate this, we apply CRC32 encoding, which generates a compact, unique fingerprint for each URL. By joining on these smaller hashed values instead of the full strings, we dramatically reduce join time, improve query performance, and still maintain data integrity.
How we use CRC32 at Bright Analytics
At Bright Analytics we typically use CRC32 in a transform to set the numeric ID representing the page and store this in a custom column. This would then be used as the key in a join to a corresponding lookup table.
This allows for faster joins, quick comparisons, and lightweight deduplication. While it’s not cryptographically secure – so we wouldn’t use it for anything sensitive – it’s extremely fast and more than good enough for most analytics and reporting workloads.
Things to watch out for
CRC32 does have a small theoretical risk of collisions (e.g. different URLs producing the same hash output), but for most business cases, this risk is negligible. Other encoding methods like SHA-256 or UUID5 offer stronger collision resistance but produce much larger values, which would impact on join efficidncy performance.
More insights from Bright Analytics
Start solving your data challenges today
Our self service platform eats complexity for breakfast. With our team of friendly data specialists on hand to help, we will have a solution perfectly tailored to your needs up and running in under a week.
Get In Touch