# 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.

Source: https://docs.querylab.io/bigquery/hashing-and-sampling/

Updated: 2026-10-14

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

**one ID through four hashes**

```sql
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
```

Result in BigQuery (1 row):

| farm | md5_hex | sha256_hex | sha256_bytes |
| --- | --- | --- | --- |
| 5340128202724118949 | 7631bc07a1cc8fcd56e70fc6b2fb4a43 | 6d894aa3ee802549d7f340e7c1cf0d1c1cb14cd84f768d92ffaa6785337c4997 | 32 |

| Function                            | Returns                   | Use                                                         |
| ----------------------------------- | ------------------------- | ----------------------------------------------------------- |
| `FARM_FINGERPRINT(x)`               | `INT64` (can be negative) | bucketing, sampling, deduplication; fast, not cryptographic |
| `MD5(x)`                            | 16 `BYTES`                | checksums against systems that use MD5                      |
| `SHA1(x)`, `SHA256(x)`, `SHA512(x)` | 20, 32, 64 `BYTES`        | pseudonymizing IDs, comparing with other systems            |

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](https://docs.querylab.io/bigquery/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:

**five users, ten buckets**

```sql
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
```

Result in BigQuery (5 rows):

| user_id | bucket |
| --- | --- |
| user-1 | 6 |
| user-2 | 7 |
| user-3 | 2 |
| user-4 | 1 |
| user-5 | 6 |

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:

**the 10% sample of 100,000 IDs**

```sql
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
```

Result in BigQuery (1 row):

| users | in_10_percent_sample |
| --- | --- |
| 100000 | 9946 |

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](https://docs.querylab.io/bigquery/table-sampling/), which samples storage blocks. In Querylab.io, a `-- querylab:tablesample=N` comment on the first line of a tab applies it to the tables the query reads without changing the query text; the status bar shows the sampled estimate next to the full one. See [TABLESAMPLE](https://docs.querylab.io/writing-sql/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`.

**ABS of the smallest INT64**

```sql
SELECT ABS(-9223372036854775808) AS abs_min
```

BigQuery rejects this query:

```text
Integer Overflow
```

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:

**a 50/50 split of 10,000 users**

```sql
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
```

Result in BigQuery (2 rows):

| variant | users |
| --- | --- |
| A | 4991 |
| B | 5009 |

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:

**the duplicate order shows up twice**

```sql
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
```

Result in BigQuery (2 rows):

| row_hash | copies |
| --- | --- |
| 2570005675733163337 | 2 |
| -8470292395151817152 | 1 |

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](https://docs.querylab.io/bigquery/query-cache/).

Split ids into A/B buckets with FARM_FINGERPRINT. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Hash functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/hash_functions)

- [Table sampling](https://cloud.google.com/bigquery/docs/table-sampling)