使用BigQuery中FARM_FINGERPRINT生成INT64代理键的碰撞概率咨询

Great question—let’s break this down into your two core concerns: collision risk with FARM_FINGERPRINT(GENERATE_UUID()) as an INT64 surrogate key, and join performance differences between INT64 and STRING keys in BigQuery.

Collision Probability of FARM_FINGERPRINT(GENERATE_UUID()) #

First, a quick primer: FARM_FINGERPRINT uses Google’s FarmHash algorithm, which produces a 64-bit INT64 value. Since you’re hashing a fully random 128-bit UUID (from GENERATE_UUID()), the input has extremely high entropy—this is ideal for minimizing collision risk.

The key math here comes from the birthday paradox, which tells us the probability of collision in a set of random 64-bit values follows this approximate formula:
p ≈ n² / (2 * 2⁶⁴)
Where n is the number of rows in your table.

Let’s put this into real-world terms:

• For 100 million rows: Collision probability is ~0.00027% (negligible for almost all use cases)
• For 1 billion rows: ~0.027%
• For 4.2 billion rows (2³²): ~50% (this is the "break-even" point where collision odds hit 50/50)

As you noted, FarmHash has passed all SMHasher tests with zero collisions reported, which adds confidence in its collision resistance for high-entropy inputs like UUIDs. Unless you’re working with tables that will grow into the tens of billions of rows, the risk of collision is effectively zero.

Join Performance: INT64 vs STRING Surrogate Keys #

When it comes to join performance, INT64 keys have a clear advantage over STRING UUIDs in BigQuery, and here’s why:

• Storage size: An INT64 is 8 bytes fixed-length, while a standard STRING UUID (36 characters like xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) takes up 36 bytes—4.5x more storage. Smaller keys mean less data to read from disk, less memory usage during queries, and faster data transfer.
• Comparison efficiency: BigQuery can compare fixed-length numeric values like INT64 much faster than variable-length strings. String comparisons require checking each character sequentially, whereas numeric comparisons are a single operation. This adds up significantly during joins, especially when working with large tables where hash joins or sort-merge joins are used.
• Indexing & sorting: INT64 keys sort more efficiently than strings, which improves the performance of indexed lookups and ordered operations (like window functions) that rely on the surrogate key.

While BigQuery doesn’t have a public side-by-side benchmark document for this specific scenario, this aligns with general database engine behavior—numeric keys are almost always more performant for joins and key-based operations than string keys.

Final Takeaway #

If your tables aren’t projected to grow beyond 1-2 billion rows, FARM_FINGERPRINT(GENERATE_UUID()) is a safe, space-efficient alternative to STRING UUIDs. You’ll get the storage savings you want, minimal collision risk, and better join performance compared to using raw UUID strings as surrogate keys.

内容的提问来源于stack exchange，提问作者Twist