A Detailed Guide to Key Generation in BigQuery

Migrating an enterprise data warehouse from traditional SMP or MPP databases (Oracle, Teradata, SQL Server, Netezza) to Google BigQuery inevitably surfaces one foundational data-modeling question: how should we generate surrogate keys at petabyte scale?

In legacy OLTP and relational warehouse engines, stateful auto-increment sequences (SEQUENCE.NEXTVAL or IDENTITY(1,1)) were cheap because a centralized coordinator maintained an in-memory counter. In a distributed, serverless columnar engine like BigQuery, forcing every row through a single global counter can turn a massively parallel load into a single-node bottleneck—if you choose the wrong pattern.

Why Traditional ROW_NUMBER() Sequences Break at Scale

A common anti-pattern during lift-and-shift migrations is emulating sequential integers with an unpartitioned window function:

-- ANTI-PATTERN on multi-hundred-million-row tables:
-- Forces all rows into a single slot/worker to compute global row ordering!
SELECT
  (SELECT COALESCE(MAX(customer_sk), 0) FROM `analytics.dim_customer`)
    + ROW_NUMBER() OVER () AS customer_sk,
  customer_natural_id,
  customer_name,
  updated_at
FROM `staging.stg_customer_delta`;

Because ROW_NUMBER() OVER () contains no PARTITION BY clause, BigQuery must funnel the entire dataset into a single execution worker—often triggering Resources exceeded during query execution errors on large fact or dimension loads.

Four Production-Grade Key Generation Patterns in BigQuery

1. Deterministic Hash Keys with FARM_FINGERPRINT() (INT64)

For Kimball star schemas, Data Vault hubs/links/satellites, and idempotent ELT pipelines (such as Dataform or dbt), deterministic hash keys eliminate stateful sequence lookups altogether. Given the same business key components, FARM_FINGERPRINT() produces an identical 64-bit signed integer (INT64) across independent tables and parallel jobs—meaning facts and dimensions can compute keys independently without staging joins!

SELECT
  FARM_FINGERPRINT(
    ARRAY_TO_STRING(
      [
        COALESCE(TRIM(UPPER(source_system)), '_NULL_'),
        COALESCE(TRIM(customer_id), '_NULL_')
      ],
      '||'
    )
  ) AS customer_sk,
  source_system,
  customer_id,
  customer_name
FROM `staging.stg_customers`;

• Always normalize & delimit inputs: Concatenate trimmed strings with a delimiter (or TO_JSON_STRING(STRUCT(...))) so ('AB', 'C') never collides with ('A', 'BC'), and map NULLs explicitly so a single missing attribute does not null out the entire key.
• Collision math (Birthday Bound): With 264 possible outputs, a 64-bit hash has ~0.00003% collision probability at 100 million distinct keys, ~0.03% at 3 billion keys, and reaches ~1% around 600 million–1.9 billion keys depending on distribution. For dimensions up to tens of millions of members, INT64 joins are compact (8 bytes) and blazingly fast.

2. Cryptographic Hash Keys with SHA256() (BYTES)

When fact tables or global entity graphs exceed hundreds of millions of distinct keys—or when regulatory/financial auditability demands zero practical collision risk—upgrade from 64-bit FARM_FINGERPRINT to 256-bit SHA256() stored as raw BYTES (32 bytes, half the storage of a 64-character hex STRING):

SELECT
  SHA256(TO_JSON_STRING(STRUCT(source_system, account_id, effective_date))) AS account_hist_sk,
  source_system,
  account_id,
  effective_date
FROM `staging.stg_account_scd2`;

3. Distributed Unique Keys with GENERATE_UUID() (STRING)

When an event stream or append-only audit log lacks a clean natural composite key—or when you simply need guaranteed uniqueness per row with zero coordination overhead—GENERATE_UUID() generates a lowercase RFC 4122 Version 4 UUID (36-byte STRING) completely in parallel across thousands of BigQuery slots:

CREATE TABLE IF NOT EXISTS `analytics.fct_clickstream_events` (
  event_sk STRING DEFAULT GENERATE_UUID(),
  event_ts TIMESTAMP NOT NULL,
  user_id STRING,
  session_id STRING,
  payload JSON
)
PARTITION BY DATE(event_ts)
CLUSTER BY user_id;

Caution: Because GENERATE_UUID() is non-deterministic, re-running a pipeline regenerates new keys unless you preserve existing keys via MERGE. Moreover, 36-byte random strings consume 4.5x more join memory than 8-byte INT64 keys.

4. Native Surrogate Integers & Unenforced PK/FK Optimizer Hints

When downstream BI semantic layers strictly require positive sequential INT64 keys on moderate-sized dimensions, pair deterministic bucketing or BigQuery’s native PRIMARY KEY (...) NOT ENFORCED metadata constraints (covered in Implementing Your Logical Data Model in BigQuery) so the BigQuery query optimizer can eliminate redundant dimension joins automatically.

BigQuery Key Generation Decision Matrix

Originally published on Medium (@datasherlock) — A Detailed Guide to Key Generation in BigQuery.