Hashing, deterministic sampling and A/B buckets in BigQuery

FARM_FINGERPRINT gives a fast, stable INT64 hash for bucketing users, splitting A/B tests and finding duplicate rows. MD5 and SHA256 return BYTES. Take MOD before ABS, because ABS of the smallest INT64 overflows.

A hash turns a value into a number or a fixed-size string that looks random but is always the same for the same input. In BigQuery that is useful for three things: putting users into stable buckets (for a sample or an A/B test), fingerprinting whole rows to find duplicates, and pseudonymizing identifiers. FARM_FINGERPRINT is the one to use for the first two; SHA256 for the third.

The hash functions

SELECT
  FARM_FINGERPRINT('user-42') AS farm,
  TO_HEX(MD5('user-42')) AS md5_hex,
  TO_HEX(SHA256('user-42')) AS sha256_hex,
  LENGTH(SHA256('user-42')) AS sha256_bytes

The BYTES results show as base64 in query results; wrap them in TO_HEX for the usual hex form. A plain hash of an email or user ID isn’t anonymization: anyone with a list of candidate values can hash them and match. Add a secret salt, or use the AEAD and deterministic encryption functions in Encryption.

Stable buckets

MOD(ABS(FARM_FINGERPRINT(id)), 10) puts each ID into one of ten buckets, and the same ID lands in the same bucket in every query, today and next month:

SELECT
  user_id,
  MOD(ABS(FARM_FINGERPRINT(user_id)), 10) AS bucket
FROM UNNEST(['user-1', 'user-2', 'user-3', 'user-4', 'user-5']) AS user_id
ORDER BY user_id

That makes a reproducible sample: WHERE MOD(ABS(FARM_FINGERPRINT(user_id)), 10) = 0 keeps about 10% of users, and always the same 10%, with all their rows, which RAND() < 0.1 doesn’t do:

SELECT
  COUNT(*) AS users,
  COUNTIF(MOD(ABS(FARM_FINGERPRINT(CAST(id AS STRING))), 10) = 0) AS in_10_percent_sample
FROM UNNEST(GENERATE_ARRAY(1, 100000)) AS id

9,946 of 100,000, close to 10%. The sample is chosen by the hash, so it is representative as long as the ID itself carries no meaning. It doesn’t reduce the bytes read, though: BigQuery still reads every row to compute the hash. To read less, use TABLESAMPLE, which samples storage blocks. In Querylab.io, a -- querylab:tablesample=N comment on the first line of a tab applies it to every table the query reads without changing the query text; the status bar shows the sampled estimate next to the full one. See TABLESAMPLE.

Take MOD before ABS

FARM_FINGERPRINT returns negative numbers too. The usual fix, ABS(FARM_FINGERPRINT(x)), has a rare trap: the absolute value of the smallest INT64 doesn’t fit in an INT64.

SELECT ABS(-9223372036854775808) AS abs_min

A hash hits that value with a probability of 1 in 2^64, so you will probably never see it, but ABS(MOD(FARM_FINGERPRINT(x), 10)) can’t fail at all and gives buckets of the same size.

A/B test assignment

Prefix the ID with the experiment’s name before hashing, so different experiments split users independently:

SELECT
  IF(MOD(ABS(FARM_FINGERPRINT(CONCAT('checkout-test-', CAST(id AS STRING)))), 2) = 0, 'A', 'B') AS variant,
  COUNT(*) AS users
FROM UNNEST(GENERATE_ARRAY(1, 10000)) AS id
GROUP BY variant
ORDER BY variant

Without the prefix, every experiment would put the same users in group A.

Finding duplicate rows

TO_JSON_STRING(t) serializes a whole row, and FARM_FINGERPRINT of that gives one number per distinct row, which is handy for counting exact duplicates across many columns:

WITH orders AS (
  SELECT 'o1' AS order_id, 30 AS amount UNION ALL
  SELECT 'o2', 45 UNION ALL
  SELECT 'o1', 30
)
SELECT FARM_FINGERPRINT(TO_JSON_STRING(o)) AS row_hash, COUNT(*) AS copies
FROM orders AS o
GROUP BY row_hash
ORDER BY copies DESC

The same works for change detection between two versions of a table: a row whose fingerprint changed has changed. Two different rows can in theory share a fingerprint, but with 64 bits that is negligible unless you compare billions of rows, in which case check the matches.

Random values

RAND() returns a different value in every row and every run, and GENERATE_UUID() a new random UUID. Both make a query non-deterministic, so its results aren’t cached and a rerun gives different rows. Use a hash when you need the same answer twice. See Query cache.