Identity Columns Arrive in BigQuery: Migrate Surrogate Keys Without Breaking MERGE, Replay, or Lineage

A warehouse migration can look trivial in a design review: add an INT64 identity column and let BigQuery assign the surrogate. The risk appears when the table is a production contract. A direct streaming writer may no longer be compatible, a replay can allocate a different key, and a dashboard that sorts by the largest ID can quietly turn a sparse, loosely ordered generator into false chronology.

Google Cloud announced the feature on September 2, 2026 in its BigQuery identity columns launch post. As rechecked on September 9, 2026, the feature-specific BigQuery identity-column documentation still marks the capability as Preview under Pre-GA terms. That status matters operationally: a production adoption decision should include an exit criterion for support maturity, not only a syntax test.

This guide treats BigQuery identity columns as a key-contract change. It separates source identity from warehouse surrogate identity, inventories producers and consumers, chooses between GENERATED ALWAYS and BY DEFAULT, proves supported DML behavior, redesigns unsupported streaming paths, and tests slowly changing dimensions, replay, lineage, parallel migration, and rollback. The goal is to decide where database-generated keys improve the contract and where they weaken determinism or recoverability.

What BigQuery Identity Columns Change and What They Do Not

An identity column is an INT64 column generated by BigQuery when a row is written. System-generated values are unique within the table, but they are loosely ordered and sparse rather than strictly monotonic or gap-free. START WITH chooses the first generated value, while INCREMENT BY is a nonzero INT64 whose multiples separate generated values. Both defaults are 1.

The GoogleSQL data definition language reference documents GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY, including START WITH and INCREMENT BY. It also states that converting an existing INT64 column affects future inserts only. Existing rows are not rewritten or backfilled automatically.

The generated number is not a business identifier merely because it is unique. It does not encode event time, load time, source provenance, or cross-table identity. BigQuery constraint declarations also do not replace data-quality controls, so referential integrity still needs explicit tests.

Documented behavior and contract implication

The BigQuery primary and foreign key documentation is a useful reminder that warehouse key declarations and generated values are not a substitute for enforced application-level integrity. Treat identity generation as one mechanism inside a broader contract.

Decide Whether an Identity Column Fits the Table’s Contract

The first decision is whether the table should own key generation at all. A database-generated integer fits best as an internal warehouse surrogate with narrow scope: it supports compact joins, stays opaque to consumers, and can be reconstructed through a retained source-to-surrogate mapping. It is weaker when external applications cache it indefinitely, producers must generate IDs outside BigQuery, or replay must deterministically reproduce the same key without a stored mapping.

A useful decision record borrows the discipline of the Open Data Contract Standard: name the semantic owner, write paths, consumers, invariants, and compatibility expectations before changing the schema. The identity column should be described as a generated warehouse surrogate, an externally durable identifier, or a physical convenience. Do not leave the role implicit.

Identity-column decision record

Compare Identity Integers, UUIDs, Hashes, and Natural Keys

No key strategy wins every dimension. Natural keys carry business meaning but can be mutable or wide. UUIDs support distributed generation but are larger and opaque. Deterministic hashes replay cleanly but depend on canonicalization and collision policy. Identity integers are compact and easy to join, but their assignment is table-local and can change across rebuilds.

Key-strategy comparison matrix

Inventory Every Producer, Consumer, and Existing Key Assumption

Identity migrations fail when teams inventory tables but not behavior. Start with every producer: SQL jobs, load jobs, Dataform or dbt models, notebooks, application services, exports that round-trip data, vendor connectors, recovery scripts, and one-off administrative loads. Then map consumers: BI semantic models, dashboards, APIs, extracts, machine-learning features, downstream warehouses, quality checks, retention jobs, pagination logic, and operational support tools.

The inventory should also identify pipeline simplifications that hide write-path assumptions. The zero-ETL architecture lessons are relevant here: removing transformation hops can reduce latency, but direct writes create tighter coupling to target-table capabilities. An identity column can reintroduce the need for a landing layer if a direct stream currently targets the production table.

Producer and consumer inventory

Acceptance gate: no table enters a pilot until every known producer has an owner and write method, every material consumer has a compatibility status, and every sequence-like assumption has either been removed or explicitly accepted. Unknown consumers are a stop condition for externally exposed keys.

Choose GENERATED ALWAYS or GENERATED BY DEFAULT

GENERATED ALWAYS is the safer default when the identity value is purely system-owned. Ordinary manual insertion is blocked, which reduces accidental key injection and makes the contract easier to explain: producers omit the column or request DEFAULT, and BigQuery generates the value. GENERATED BY DEFAULT permits a producer to supply a value. That flexibility is useful for controlled historical migrations, sentinel compatibility, or preserving an approved legacy mapping, but it expands the integrity boundary.

BY DEFAULT does not turn manual values into generator-managed values. The generator's uniqueness guarantee applies to automatically generated values. Manually supplied values can duplicate another manual value or collide with a generated value unless your process prevents it. Therefore, choosing BY DEFAULT is not a syntax preference. It is an authorization and validation design.

Mode selection rule

Model Manual Overrides and Uniqueness Controls

If BY DEFAULT is allowed, create a separate control plane for manual values. Limit the service accounts or roles that can execute override loads, require an override reason and change ticket, and write every override into a reconciliation table with source system, source key, requested warehouse key, operator identity, ingestion run, and approval evidence. Do not rely on tribal knowledge that only the migration team will use the feature.

Pre-commit validation for BY DEFAULT

-- Duplicate keys already present in the target
SELECT warehouse_key, COUNT(*) AS row_count
FROM mart.customer_dim
GROUP BY warehouse_key
HAVING COUNT(*) > 1;

-- Manual keys proposed by a migration that already exist
SELECT m.warehouse_key
FROM migration.manual_key_batch AS m
JOIN mart.customer_dim AS t
USING (warehouse_key);

Plan Existing-Table Conversion Without Assuming Backfill

BigQuery can change an existing top-level INT64 column into an identity column, but the change affects future inserts. Existing values remain as they are. Existing NULLs remain NULL unless you backfill them explicitly. That behavior is useful because it avoids an implicit rewrite, but it means the schema can enter a mixed state where historical rows and new rows follow different assignment rules.

For an existing table, choose deliberately between in-place conversion, a shadow table, retaining both legacy and new keys, or a full replacement. In-place change is simplest when the existing INT64 values remain authoritative; a shadow table offers cleaner parallel validation; keeping both keys protects durable consumers; full replacement carries the highest rollback and copy risk.

Historical-key migration patterns

A practical schema keeps the legacy key if it has any durable consumer. Rename semantics rather than forcing one field to serve both purposes: legacy_customer_key for the old contract, customer_sk for the new warehouse surrogate, source_system and source_customer_id for source identity, and migration_run_id for traceability. The crosswalk should be treated as migration evidence, not disposable staging data.

ALTER TABLE mart.customer_dim
ALTER COLUMN customer_sk
SET GENERATED ALWAYS AS IDENTITY (
  START WITH 1000000
  INCREMENT BY 1
);

The example syntax is documented in the GoogleSQL DDL reference, but the start value must be chosen from your own data profile. Do not pick a large number merely to make new rows look newer. Its only safe meaning is generator configuration. If consumers use the numeric range to distinguish historical from new data, you have created a second chronology contract by accident.

Preserve MERGE, Replay, and Idempotency Semantics

Identity generation changes the moment at which the warehouse key becomes known, but it should not change the logical match condition of an idempotent pipeline. A replay-safe MERGE matches source records to existing target rows using stable source identity and the business rules that define a row version. The generated surrogate belongs in the target row after the match decision; it should not be the field that decides whether the source entity already exists.

The BigQuery DML reference for INSERT, UPDATE, and MERGE allows DEFAULT to request a default or system-generated identity value. That means generation can participate in supported DML paths. The operational concern is not whether MERGE can insert an identity row. It is whether the MERGE condition remains stable under retries, duplicate source events, late arrivals, and backfills.

A common anti-pattern is to replay a staged batch and use a generated key in the matching logic. The replay cannot know the original generated value until it looks it up, so a missed lookup produces a second row with a second surrogate. Another failure is MERGE ON source_customer_id alone for a Type 2 dimension, which collapses historical versions. The correct match must reflect the table grain.

Replay-safe MERGE pattern

MERGE mart.customer_dim AS t
USING stage.customer_changes AS s
ON  t.source_system = s.source_system
AND t.source_customer_id = s.source_customer_id
AND t.valid_from = s.valid_from
WHEN MATCHED THEN
  UPDATE SET
    customer_name = s.customer_name,
    valid_to = s.valid_to,
    is_current = s.is_current,
    ingestion_run_id = s.ingestion_run_id
WHEN NOT MATCHED THEN
  INSERT (source_system, source_customer_id, customer_name,
          valid_from, valid_to, is_current, ingestion_run_id)
  VALUES (s.source_system, s.source_customer_id, s.customer_name,
          s.valid_from, s.valid_to, s.is_current, s.ingestion_run_id);

The broader discipline is the same one described in reliable data-pipeline foundations: retries should reproduce intended state, not merely rerun code. For identity tables, rerun stability must be a named acceptance test.

Separate Source Identity From Warehouse Surrogate Identity

Model identity explicitly enough that a reviewer can tell why a row exists. At minimum, store source_system, source_key, warehouse_key, ingestion_run_id, and ingestion timestamp. Slowly changing dimensions add valid_from, valid_to, and a current-row marker. Event-oriented tables may need source_event_id or an idempotency token. These fields serve different purposes and should not be collapsed into one generated number.

Recommended identity and lineage fields

This model lets a replay find the row through source identity, then preserve the warehouse surrogate already assigned. If a full rebuild is intentionally allowed to generate different surrogates, that must be documented as a breaking migration with downstream key remapping. Do not call that behavior idempotent simply because row counts match.

Redesign Streaming Pipelines That Cannot Write to Identity Tables

This is the most important architectural constraint for teams with real-time ingestion. BigQuery documents that tables with identity columns cannot receive streaming data through the Storage Write API (gRPC) or tabledata.insertAll. If a production service streams directly into the target table, adding an identity column makes the existing writer incompatible. That is a stop condition, not a post-deployment tuning issue.

Read the restriction together with the general BigQuery Storage Write API guidance. The API supports streaming patterns and exactly-once techniques for compatible tables, but the identity-column limitation means the target architecture must change. A practitioner design is to keep the stream landing table free of identity columns and promote data into the identity table through supported query or load paths.

This is where cloud-native data engineering principles become concrete: the managed service removes infrastructure work, but platform capabilities still shape application boundaries. Do not preserve a direct-to-target architecture merely because it was simpler before the schema changed.

Streaming alternatives for an identity target

Do not label the landing-table pattern as a vendor workaround or a guarantee. It is an architecture choice that uses documented supported boundaries. Measure added latency, DML cost, concurrency behavior, stage retention, and operational complexity before acceptance. If the business requirement is direct low-latency streaming into the final table, the correct decision may be not to adopt identity columns there.

Rework Batch Loads, Copies, CTAS, and Table Replacement

Batch pipelines usually have more compatible paths, but they often contain hidden copy semantics. BigQuery documents unsupported table-copy operations when a source or destination has an identity column: table copy with WRITE_APPEND, table copy with WRITE_TRUNCATE, and multi-source table copy. Existing deployment scripts that stage a table and then overwrite production with a copy can therefore fail even when ordinary query jobs succeed.

Review the feature limitation together with BigQuery guidance for managing table data and copy jobs. The goal is not to memorize a single forbidden command. It is to classify every job by operation type, source, destination, and write disposition so CI can stop incompatible changes before they reach production.

Load jobs can generate identity values when the identity column is omitted from the source. With GENERATED ALWAYS, the identity column must not be supplied as ordinary source data. Query append paths can also generate values when the destination identity column is omitted. Use explicit column lists so a schema reorder or newly added column does not accidentally feed a value into the generated field.

Batch and deployment preflight

Test Unsupported Copy and Write Dispositions

Turn the preflight into an automated deployment check. Query INFORMATION_SCHEMA to discover identity columns, parse job configuration for copy dispositions, and reject a release when an unsupported copy would touch an identity table. For infrastructure-as-code or orchestration repositories, lint the compiled job definitions rather than only source templates because variables can change the final destination.

Example CI discovery query

SELECT table_schema, table_name, column_name,
       identity_generation, identity_start, identity_increment
FROM project.region-us.INFORMATION_SCHEMA.COLUMNS
WHERE is_identity = 'YES';

Pair that inventory with negative tests. A good pilot deliberately runs the old WRITE_APPEND copy, old WRITE_TRUNCATE copy, and multi-source copy in a disposable dataset and records the failure. That evidence is stronger than a checklist item because it proves your preflight is protecting a real unsupported path. Stop deployment if CI cannot distinguish query append from table copy or if production automation can bypass the check.

Protect Slowly Changing Dimensions and Fact-to-Dimension Joins

Slowly changing dimensions (SCDs) are where a generated integer can be valuable and dangerous at the same time. In a Type 1 dimension, one surrogate often represents the current entity row, and attributes are updated in place. In a Type 2 dimension, each historical version typically needs its own surrogate, while source business identity remains constant across versions. An identity column can generate the version surrogate efficiently, but only if the MERGE logic defines version boundaries correctly.

For Type 2, the fact table should join to the dimension version valid at the fact's business timestamp, not the dimension row with the largest surrogate. Late-arriving facts may need to resolve to an older version. Backfills may create historical versions after newer ones already exist. Generated identity values are loosely ordered, so the largest number cannot safely identify the latest or current version.

SCD key model and controls

Unknown-member handling deserves an explicit decision. Many warehouses reserve 0 or -1 for an unknown dimension row. GENERATED ALWAYS cannot accept an ordinary manually assigned sentinel, while BY DEFAULT can. If a fixed sentinel is only a legacy convention, consider generating the unknown member normally and storing its surrogate in configuration or lookup logic. If a fixed number is contractually required, BY DEFAULT introduces the manual uniqueness controls described earlier.

For fact loads, retain the source business key long enough to re-resolve dimension mappings during reconciliation. A fact table that stores only the surrogate cannot prove whether an orphan came from a missing dimension row, an incorrect historical interval, or a changed key mapping. During migration, keep both old and new surrogate mappings until consumer parity is accepted.

Validate Ordering, Sparsity, and Gap-Tolerant Consumers

The most subtle migration defect is a consumer that continues to run successfully while interpreting identity values incorrectly. Generated values can be sparse and loosely ordered. Therefore, MAX(identity_id) is not the number of rows, a missing ID is not evidence of lost data, and a greater ID is not guaranteed to represent a later event, later load, or newer dimension version.

Inventory ORDER BY clauses, pagination tokens, watermarks, retention jobs, dashboards, and support queries that use the key. Replace surrogate-based recency with event_time, ingested_at, valid_from, or another explicit field. When timestamps tie, use a semantic tie-breaker such as source_event_id rather than assuming identity magnitude represents order.

Sequence-assumption test set

Eliminate Sequence-Order Business Logic

Build a canary dataset that intentionally violates sequence assumptions. Insert rows concurrently, use a nondefault increment in a test table, replay a batch, and load late events whose event time predates existing rows. The test passes only if reports, APIs, and orchestration still return correct business order without depending on identity magnitude.

A useful static-analysis rule flags SQL patterns such as MAX(identity_column), MIN(identity_column) used as lifecycle markers, BETWEEN ranges tied to dates, and identity predicates used as watermarks. Not every occurrence is wrong. For example, MAX can be a debugging statistic. The rule should force review, not automatically rewrite logic. The reviewer must name the semantic field that actually represents chronology or completeness.

Stop the migration if a consumer cannot be changed and its contract requires a monotonic sequence. An identity column is not a documented sequence service. If strict order is a real requirement, model order explicitly with source sequence numbers, event versions, or timestamps and define late-arrival semantics rather than asking the surrogate to carry meaning it does not guarantee.

Update dbt, Dataform, Orchestration, and Data Quality Tests

Transformation frameworks can hide generated SQL, which makes identity adoption a compile-time problem as well as a database problem. In dbt, incremental models on BigQuery commonly use MERGE when configured appropriately. The model's unique_key should represent the logical source or row-version identity used for matching, not the generated warehouse surrogate. In Dataform, incremental unique keys play a similar role. Identity generation belongs in the destination insert, not in the framework's definition of source uniqueness.

Review the dbt guidance for incremental models and dbt BigQuery configuration documentation alongside the compiled SQL your project actually emits. Refonte Learning's discussion of modern analytics with dbt and Snowflake is useful background for transformation ownership, but the identity-specific control is to inspect the target SQL, not assume the framework abstracts the feature correctly.

For Dataform, use Dataform assertions and the Dataform core reference to implement uniqueness, non-null, and incremental-key checks where applicable. The exact assertion should match the table contract: generated surrogate uniqueness, source-key uniqueness at the current-row grain, and no duplicate source-version keys.

Framework and orchestration checklist

-- Contract tests for a current-row dimension
SELECT source_system, source_customer_id, COUNT(*)
FROM mart.customer_dim
WHERE is_current
GROUP BY source_system, source_customer_id
HAVING COUNT(*) != 1;

SELECT customer_sk, COUNT(*)
FROM mart.customer_dim
GROUP BY customer_sk
HAVING COUNT(*) > 1;

Maintain Lineage, Auditability, and Key Ownership

Generated keys reduce code only if ownership becomes clearer. Data engineering owns source identity and replay semantics; platform or database teams own DDL guardrails; producer teams own idempotent inputs; BI and application owners own consumer compatibility; security owns override permissions; operations owns recovery; and a risk approver owns any time-bounded exception for using a Preview feature on critical data.

The division of responsibility resembles the boundary described in database administrator versus data engineer: schema mechanics and pipeline semantics overlap, but they are not interchangeable. An identity migration needs both database-level control and data-engineering ownership of source keys, replay, and lineage.

Use the BigQuery COLUMNS INFORMATION_SCHEMA view to discover is_identity, identity_generation, identity_start, and identity_increment. Capture those fields in schema inventory and deployment evidence. Also monitor the BigQuery release notes before broad rollout so Preview status or limitations are rechecked at each promotion gate.

Ownership model

Retain reconciliation evidence long enough to support post-cutover investigations. At minimum, keep source-to-surrogate mapping snapshots, migration run identifiers, duplicate and orphan checks, manual override records, cutover timestamps, and the exact schema generation mode. Without that history, an identity incident quickly becomes unanswerable: teams can see that a key changed but cannot prove which run assigned it or whether the change was expected.

Migrate in Parallel With Reconciliation and Rollback

A reversible migration uses a shadow table or dual model long enough to prove behavior without destroying the old key system. Use source fixtures for normal rows, duplicates, late arrivals, historical versions, unknown members, and replays. Populate the identity target through the intended production path, then build the old-to-new crosswalk from source identity rather than row position or generated-key order.

Then run consumers in canary mode. BI models can compare aggregate and record-level results. Fact loads can resolve both old and new dimension keys and compare orphan rates. Exports can carry both keys temporarily if the receiving system supports additive fields. Do not cut over a consumer that has not demonstrated it can ignore identity order and preserve its own stable identifiers.

Worked example: customer dimension migration

A customer dimension currently uses a deterministic transformed surrogate. Discovery finds three important dependencies: an event service streams customer changes directly into the production table, the nightly SCD Type 2 MERGE matches on source customer ID plus valid_from, and an operations dashboard sorts by surrogate descending to show what it calls the latest customer records. The proposed identity column initially appears to simplify the transformation model.

The first prototype fails because the event service streams through the Storage Write API directly to the identity target. Production remains unchanged. The revised design streams into a non-identity landing table and promotes rows through micro-batch MERGE, adding run-level backlog and source-key parity checks.

Replay then exposes a second defect: an early MERGE looked up the generated surrogate and inserted when the lookup missed, so the same fixture could receive a second identity. The fix removes warehouse identity from the match and uses source_system, source_customer_id, and valid_from. The repeated run updates the same row and preserves its surrogate.

During canary cutover, a late-arriving fact becomes an orphan because its historical dimension version has not yet been promoted. The orphan gate stops the switch, facts continue using the old mapping, and the team reorders dimension promotion before fact resolution. After replay, the canary resumes only when the intended orphan count returns to zero and dashboard order uses valid_from rather than surrogate magnitude.

Cutover and rollback state model

Define Key-Parity and Referential-Integrity Gates

Promotion gates should be numeric and explainable. Proposed practitioner thresholds can be stricter than the platform's documented behavior. For a surrogate-key migration, exact equality is appropriate for source-identity coverage and unintended duplicate counts. Metric differences may have a documented tolerance only when the underlying business logic already permits one. Identity sparsity is never an accepted explanation for missing source records.

Promotion gates

Rollback conditions include any unsupported production writer discovered after deployment, growth in unintended orphans, duplicate source-version keys, consumer reliance on identity order that cannot be corrected quickly, or inability to reproduce source-to-surrogate mappings. Recovery evidence is not a statement that the rollback succeeded. It is the restored row counts, stable old-key joins, disabled new write path, preserved crosswalk, and incident record showing which assignments occurred before rollback.

Build a Production Adoption Checklist for Identity Columns

A 60-day adoption plan should end with a decision, not assume production rollout is the destination. Preview is an acceptable outcome for a contained pilot if the risk is understood. The team can also decide that identity columns fit batch dimensions but not streaming facts, or that a critical table should wait for broader support. Standardize only the use cases that pass deterministic replay, supported-write, referential-integrity, and rollback gates.

Production checklist

• Table purpose and key role are documented: internal surrogate, external identifier, or physical convenience.

• Source identity remains separate from warehouse identity and is retained for reconciliation.

• All producers are classified by INSERT, MERGE, UPDATE, load, streaming, copy, and replacement behavior.

• All consumers have been checked for ordering, pagination, watermark, retention, and numeric-feature assumptions.

• Generation mode is approved, and BY DEFAULT override permissions are least-privilege and audited if used.

• Streaming writers have been redesigned or identity adoption has been rejected for that target.

• Unsupported WRITE_APPEND, WRITE_TRUNCATE, and multi-source copy paths are blocked by CI.

• Existing-row migration has an explicit plan for historical values, nulls, and key crosswalks.

• MERGE and replay tests prove the same source identity does not acquire a second surrogate unintentionally.

• SCD Type 1/2, unknown members, late facts, and backfills have been validated.

• Lineage captures source key, warehouse key, valid time, ingestion run, and override evidence.

• Parallel reconciliation, cutover, stop conditions, and rollback have named owners and rehearsed evidence.

60-day adoption plan

After cutover, monitor promotion lag, duplicate source-version count, unintended orphans, unexpected NULL warehouse keys, manual overrides, blocked writes, and consumer canary errors. Alert on contract breaches, not gaps in identity numbers: sparse keys are expected, missing source identities are not.

A production-ready decision is conditional. Adopt identity columns when the INT64 is an opaque internal surrogate, every write path is supported, MERGE matches stable source identity, replay preserves existing mappings, SCD joins use valid time, consumers tolerate gaps and loose ordering, and rollback can return to the old mapping. Defer when final-table streaming is mandatory, external systems need deterministic regeneration, or crosswalk and rollback evidence cannot be preserved.

For practitioners building the broader skills behind these controls, the Refonte Learning Data Engineering Program covers verified foundations in data storage, real-time processing, batch and streaming ingestion, data pipelines, transformation, security, data protection, and governance. Those foundations are relevant to evaluating warehouse key migrations, but the program page does not claim product-specific instruction in BigQuery identity columns, dbt, Dataform, surrogate-key migration, or slowly changing dimensions.

A generated integer must never silently become business identity or event order. The contract still has to state what the number means, who may override it, how replay recognizes the same source record, how facts retain referential integrity, and how rollback prevents a second key system. Database-generated keys help only when those responsibilities become more explicit.