A surrogate key is a warehouse-generated identifier for a dimension row, assigned independently of any identifier in the source system. The dimensional modeling pillar covers why surrogates exist in the first place: source system independence, support for slowly changing dimension versioning, and a stable join key that survives upstream change. This article goes one level deeper into the mechanics. How to generate surrogates on the warehouse engines a 2026 reader is actually using, how the fact-loading lookup works when a single business key has several Type 2 versions, and the edge cases that quietly produce wrong joins under load.
The framing throughout is that surrogate keys are load-bearing, not cosmetic. They are the mechanism that makes the Type 2 SCD pattern work correctly, the mechanism that lets inferred members be backfilled in place when the real dimension data arrives, and the mechanism that survives source-system primary key reuse without poisoning the fact tables.
TL;DR. Integer sequences are the default for batch-loaded dimensions. Hash-based surrogates are the deterministic option that survives replay and parallel loads. UUID v7 is the streaming-friendly choice that preserves rough sort order. The lookup that matters is the date-range lookup under Type 2 SCD; getting that wrong is how dimension versions silently end up referencing the wrong fact history.
What a surrogate key is and what it is not
A surrogate key is a compact, meaningless-outside-the-warehouse identifier on a dimension row. It is the primary key of the dimension table and the foreign key in every fact table that references that dimension. Integers are the most common physical type because they are compact, comparable, and efficient as join keys. Hashes and UUIDs are also surrogates when used in the same role.
The surrogate is not the business key. The business key is the identifier the source system uses for the entity: customer ID 41827 in the CRM, SKU AX-220 in the product catalog, employee number 11042 in the HR system. The business key is stored as a regular attribute on the dimension table and is the lookup key during fact loading. The surrogate is the row identifier; the business key is the entity identifier. A single business key can correspond to several surrogate keys under Type 2 because each version of the dimension row gets its own surrogate.
The surrogate is also not the source system primary key under a different name. Reusing a source primary key as the warehouse surrogate ties the warehouse's identity model to the source's identity model. Sources change their primary keys on migration, recycle deleted IDs, and produce overlapping ranges across systems when a warehouse consolidates multiple sources. A surrogate that is independently generated by the warehouse insulates the rest of the model from all of that.
Three properties matter for whether a generation strategy is fit for purpose. Uniqueness within the dimension table, so two rows for different versions or different entities never collide. Stability across reloads, so a row that gets the surrogate value 7281 today gets the same value if the load is rerun against the same source state. And ordering, where the surrogate's natural sort order corresponds to the order in which rows were minted, which simplifies recency queries and clustering.
Different generation strategies trade these properties against each other.
Generation patterns
Three patterns are in routine production use. Integer sequences, hash-based deterministic keys, and UUIDs. Each maps onto a class of warehouse and a class of load pattern.
Integer sequences and identity columns
The textbook surrogate is a monotonically increasing integer assigned by the warehouse when a new dimension row is created. The dimension table declares an integer column as the surrogate, and the loader either reads the next value from a sequence object or relies on the engine's identity column mechanism.
Snowflake supports both. A SEQUENCE object provides nextval-style allocation that can be referenced in INSERT statements; identity columns on tables produce values automatically on insert. Both produce monotonically increasing integers, though neither guarantees contiguity. Snowflake's micro-partition architecture and concurrent-write semantics can leave gaps where transactions abort or where multiple writers allocate values in parallel. Gaps are not a correctness problem because the surrogate carries no business meaning; queries that depend on the surrogate being densely packed are misusing it.
Redshift supports identity columns through the IDENTITY clause on CREATE TABLE. The values are unique but not strictly sequential, particularly in multi-node clusters where each compute slice allocates its own range. Practitioners coming from row-oriented engines expecting contiguous sequences are sometimes surprised; the design trade-off is the same as Snowflake's.
BigQuery is the platform that breaks the pattern. There is no native sequence object and no identity column. The standard generation approach is one of: a ROW_NUMBER() OVER (ORDER BY ...) window function applied at load time and offset against the current maximum surrogate, a hash-based key (covered below), or a UUID. The ROW_NUMBER approach works for batch loads where the entire incremental batch can be materialized in a single statement; it does not scale to streaming inserts or to loads from multiple concurrent jobs without coordination. Teams building dimensional models on BigQuery converge on hash-based surrogates more often than teams on Snowflake or Redshift do, largely because the platform pushes them in that direction.
Databricks SQL on Delta tables supports GENERATED ALWAYS AS IDENTITY on Unity Catalog tables, with the same caveats about non-contiguity under concurrent writes. The mechanism is functional for batch ELT but does not extend to streaming writes from Spark Structured Streaming without explicit coordination, and the practical workaround is again hash-based or UUID-based generation for streaming-fed dimensions.
The integer sequence pattern is the right default when loads are batch, when the dimension is loaded by a single ELT job at a time, and when join performance on integer keys is worth the operational simplicity. It struggles when loads are streaming, when the same dimension is fed by parallel jobs that cannot share a sequence, or when the warehouse engine does not provide a native sequence primitive.
Hash-based deterministic keys
A hash-based surrogate is the output of a hash function applied to the business key (and, under Type 2, the change-detection columns or an effective-from timestamp). The same input always produces the same hash, which makes the key deterministic across reloads, across parallel workers, and across systems. Two workers loading the same business key independently produce the same surrogate without coordination.
The implementation is straightforward. A 64-bit or 128-bit hash function (Snowflake's HASH, BigQuery's FARM_FINGERPRINT, an MD5 or SHA-256 truncated to the desired width, or the dbt dbt_utils.generate_surrogate_key macro) takes the relevant columns and produces a fixed-width integer or binary value. The dimension row's surrogate column is declared as that type. The fact loader computes the same hash from the incoming business key and uses it directly as the foreign key, with no lookup join required.
The lookup-free property is the practical win. Under integer sequences, the fact loader has to join the incoming fact's business key against the dimension table to find the current surrogate, which adds an O(n) join step at load time. Under hash-based surrogates, the loader computes the surrogate directly from the business key and writes it into the fact row. The join step disappears for the current-state case.
Hash-based surrogates compose cleanly with data vault hash keys, which use the same principle for hub primary keys. Teams running both a vault layer and a dimensional layer often share hash conventions so that the same business key produces the same surrogate in both places.
Type 2 versioning requires the hash to include something that differentiates versions. A common pattern is hash(business_key || effective_from), which produces a different surrogate for each version of the row. The fact-loading lookup then needs the event's effective-from timestamp to compute the right hash, which means the loader still needs to resolve which version was active at the event time. The lookup-free advantage applies to current-state joins; historical joins under Type 2 still require resolving the version.
The cost is two-fold. Hash values are larger than narrow integers (8 to 16 bytes versus 4 to 8 bytes for a typical integer surrogate), and join performance on hash keys is slightly worse than on integers on most engines. Both costs are small at modern warehouse scale. The bigger consideration is that hashes are not ordered: the natural sort order of hash values is random, which removes the recency-clustering benefit that monotonic integers give. On columnar warehouses with clustering or sort keys, dimension tables clustered on a hash surrogate get no locality benefit from the clustering.
UUID v7 and streaming generation
UUIDs are the generic answer to the "no sequence and no coordination" problem. A UUID generated by the loader is statistically unique without consulting any central state, which makes them the default for streaming-ingested dimensions where each writer produces rows independently.
The traditional UUID v4 (random) has the same sort-order problem as hash-based surrogates: the values are random, so there is no clustering benefit. UUID v7, finalized in RFC 9562 in 2024, addresses this directly. The first 48 bits of a v7 UUID encode a Unix epoch millisecond timestamp, and the remaining bits are random. The values are still unique and still generated without coordination, but they sort by mint time. A dimension table clustered on a v7 surrogate clusters by mint time, which is a useful proxy for recency.
For streaming-fed dimensions on any warehouse, UUID v7 is the strongest default in 2026. It works on platforms without native sequences (BigQuery), composes with streaming writers that cannot share sequence state, preserves the recency-clustering property that v4 loses, and produces keys that are immediately stable across replay. Generation is supported in client libraries across the major languages and is available as a Spark function in current Databricks runtimes; on warehouses without native v7, a deterministic implementation in the loader is straightforward.
The cost is width. A UUID is 16 bytes, twice the width of a 64-bit hash and four times the width of a 32-bit integer. On dimension tables with hundreds of millions of rows joined to fact tables with hundreds of billions of rows, the additional width per fact row is real storage and real scan cost. Whether that cost matters depends on the table sizes; for typical mid-size warehouses the storage cost is negligible and the streaming-friendliness is worth it.
The integer-vs-hash-vs-UUID trade-off
The three patterns are ordered by a recurring set of properties.
| Property | Integer sequence | Hash-based | UUID v7 |
|---|---|---|---|
| Width | 4-8 bytes | 8-16 bytes | 16 bytes |
| Coordination required | Yes (sequence state) | No | No |
| Deterministic across reloads | No (depends on insert order) | Yes | No |
| Sort order matches mint time | Yes | No | Yes |
| Works on BigQuery natively | No | Yes | Yes |
| Streaming-friendly | No | Yes | Yes |
| Lookup-free current-state joins | No | Yes | No |
| Join performance | Best | Good | Acceptable |
The selection logic that recurs in production stacks: batch ELT on Snowflake or Redshift with a single loader per dimension defaults to integer sequences; the operational simplicity is worth the coordination cost. Loads on BigQuery, or any platform where parallel workers feed the same dimension, default to hash-based surrogates; the deterministic property eliminates the coordination problem. Streaming-ingested dimensions default to UUID v7; the combination of independent generation and time-ordered values is the property that other schemes do not provide.
Mixing strategies within a warehouse is acceptable when the choice is driven by the load pattern of each dimension. A customer dimension fed from a SaaS source through batch ELT might use an integer surrogate; an event-stream-derived "session" dimension might use UUID v7; both can reference into the same fact table through their respective foreign keys. The discipline is that each dimension's surrogate type is documented and consistent across its lifetime.
Lookup mechanics during fact loading
Surrogate keys exist so the fact table can carry a stable foreign key that survives source-system changes. The mechanic that makes this work is the fact-loading lookup. Under Type 1 SCDs the lookup is trivial: one row per business key in the dimension, one current surrogate, one join. Under Type 2 SCDs the lookup is where surrogate keys earn their cost.
The Type 2 dimension has several rows for the same business key, one per period of stable attribute values. The fact loader receives a fact record carrying a business key and an event timestamp. The correct surrogate is the one for the dimension row that was active at the event timestamp. The lookup joins on the business key and applies a date-range predicate that the event timestamp falls within the row's effective_from and effective_to range. The matched surrogate becomes the fact's foreign key.
Three failure modes are common enough to call out specifically.
The first is using is_current filtering against late-arriving facts. A loader that joins on the business key and filters to is_current = true returns the current surrogate regardless of the fact's event timestamp. A fact arriving today for an event last June joins to the row that is current today, which may not be the row that was active last June. The fact's region attribute, joined through the current surrogate, reflects the customer's current region rather than the historical region. The result is systematically wrong history that succeeds silently because the join finds a row.
The second is using the load timestamp rather than the event timestamp in the date-range predicate. The fact's event timestamp is what should match the dimension's effective range. The load timestamp is when the fact arrived at the warehouse, which can be hours, days, or weeks later. Using the load timestamp produces the same failure mode as is_current filtering with extra steps.
The third is failing to handle the case where no dimension row is active at the fact's timestamp, which happens when the fact's business key is not yet in the dimension. The two standard responses are an inferred member row that becomes the current row and gets backfilled when the real dimension data arrives, or a designated unknown-dimension row that absorbs all unresolved joins. The inferred member pattern is more accurate; the unknown row is simpler. The advanced dimensional modeling article covers the inferred member pattern in detail because the mechanism depends on the warehouse being able to mint a new surrogate at fact-load time and update it in place when the real dimension data arrives.
For hash-based surrogates under Type 2, the lookup is structurally the same. The hash includes a version differentiator (typically effective_from), so the loader still has to resolve which version's effective_from to use, which still requires the same date-range scan against the dimension. The hash advantage in lookup-free joins applies to current-state queries; historical resolution always requires the version lookup.
Edge cases and gotchas
The patterns above interact with the operational reality of real loaders in ways that the textbook examples skip.
Deletes from the source after the surrogate was minted produce orphan dimension rows. A customer record deleted in the source CRM still has a dimension row in the warehouse with a stable surrogate, and that surrogate is referenced by every historical fact loaded against the customer. The dimension row should be retained; the warehouse owns the identifier, and discarding it breaks every historical fact join. The standard pattern is to add an is_deleted flag (or a deleted_at timestamp) to the dimension and let queries that want current-state filtering exclude deleted rows while preserving the surrogate for historical joins. Hard-deleting the dimension row to mirror the source is a mistake that surfaces months later as inexplicably null fact joins.
Source key reuse is the operational counterpart. A source system that recycles primary keys, for example by deleting customer 41827 and creating a new customer with ID 41827 a year later, produces two different business entities with the same business key. The warehouse's surrogate isolates the historical record; the second entity gets its own surrogate keyed off the change-detection columns. The complication is the lookup: a fact arriving with business key 41827 needs to resolve to the right entity, which requires the business key plus the event timestamp plus enough attribute history to distinguish the two. Sources that recycle keys are a documented operational risk; the warehouse design should treat the business key as a non-unique attribute and the surrogate plus a stable durable identifier as the actual entity reference.
Multi-system identity reconciliation is the case where two source systems have their own primary keys for what the business considers a single entity. The customer numbered 41827 in the CRM and account 8842 in the ERP may be the same person, the same household, or completely unrelated entities. The warehouse cannot decide; an upstream identity-resolution process produces a master ID that the dimension uses as the business key, with the source system keys retained as attributes. The surrogate then references the master ID. Building this without the identity-resolution layer first produces a dimension with two rows per entity that the warehouse cannot collapse later without a load rewrite.
Batch versus streaming generation interact with idempotency under replay. A batch loader that uses an integer sequence and reruns against the same source state produces different surrogate values for the same rows on the second run, because the sequence has advanced. This is fine for a fresh load (truncate-and-reload), and breaks for an incremental load (where existing fact rows reference the old surrogates). The standard pattern is to keep the surrogate generation idempotent across reruns: hash-based surrogates achieve this naturally; integer-sequence loaders achieve it by detecting existing rows and reusing their surrogates rather than minting new ones. A streaming loader replaying a window of events must produce the same surrogate for the same input each time, which is the case where hash-based and UUID v7 generation outperform sequence-based generation.
The hash-input choice matters for stability. A hash that includes a load timestamp produces a different value every time the load runs and is functionally a UUID. A hash that includes only the business key plus the change-detection columns is stable across reruns and across parallel workers. The dbt generate_surrogate_key macro hashes a concatenation of normalized column values; the normalization (NULL handling, whitespace trimming, case folding where appropriate) matters because cosmetic differences in input produce different hashes and therefore different surrogates for the same logical entity. Without normalization, the same business key with a trailing space gets a different surrogate than the same key without, and the dimension grows spurious versions on every source-system formatting drift.
Related techniques
Surrogate keys are the foundation that the slowly changing dimensions article builds on, particularly for the date-range lookup mechanics under Type 2. The advanced dimensional modeling article covers the inferred member pattern, which depends on the warehouse being able to mint a surrogate at fact-load time and update the placeholder row in place when the real dimension data arrives. The dimensional modeling pillar covers the broader context, including why source-system independence makes surrogate keys load-bearing rather than convenient.
The modern warehouse platforms pillar covers the platform-specific primitives that drive the integer-vs-hash-vs-UUID choice: Snowflake and Redshift identity columns, BigQuery's lack of native sequences, Databricks identity columns on Delta tables.
For the data vault analog, the data vault modeling pillar covers hash-based hub keys that share the deterministic generation principle.
Closing
Surrogate keys are the warehouse's identity layer. They exist so the rest of the model can rely on a stable join key that survives source-system change, supports Type 2 versioning, and absorbs the messy reality of deletes, key reuse, and multi-system reconciliation without poisoning the fact tables. The choice of generation pattern follows the load pattern: integer sequences for batch loads on platforms that support them, hash-based surrogates for parallel and platform-portable loads, UUID v7 for streaming. The fact-loading lookup under Type 2 is where surrogate keys earn their cost; getting that lookup wrong, by filtering on a current-record flag instead of a date range, is how dimension history silently ends up wrong. The mechanics are not difficult, but they reward being made explicit at the start of the model rather than rebuilt after the first round of analytical complaints.
Reference
The surrogate key pattern is part of the Kimball dimensional modeling vocabulary and has been a working foundation across analytical warehouses for three decades.
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed., Wiley, 2013. The canonical reference for surrogate keys, including the SCD-versioning rationale and the load-time lookup mechanics.
- Ralph Kimball, Surrogate Keys, DBMS Magazine, 1998 (Kimball Group archive). The original treatment of the technique.
- RFC 9562: Universally Unique IDentifiers (UUIDs), IETF, 2024. The standard that defines UUID v7 and its time-ordered properties.