A surrogate key is a system-generated numeric identifier (typically an auto-incrementing integer or a hash value) assigned to each row in a data warehouse dimension table — used as the primary key and as the foreign key reference in fact tables, in place of or in addition to the natural business key from the source system. Where the natural business key is the identifier used by the business (an account code like “4001-001”, an entity identifier like “SAU-KSA-001”, a vendor number from Oracle EBS), the surrogate key is a meaningless technical identifier generated by the data warehouse loading process (AccountKey = 14782, EntityKey = 3201, VendorKey = 90134). Fact table rows carry the surrogate key, not the natural business key — the dimension table maintains the mapping between the surrogate key and the natural business key, along with all descriptive attributes and historical versions of those attributes.
Why Surrogate Keys Are Required
| Reason | Without Surrogate Key | With Surrogate Key |
|---|---|---|
| SCD Type 2 historisation | A natural key cannot uniquely identify an old vs new version of the same entity | New surrogate key assigned for each new version; fact rows point to specific version by surrogate key |
| Multi-source integration | Same account may have code “4001” in Oracle EBS and “ACCT-4001” in SAP — natural keys conflict | Single surrogate AccountKey assigned to the conformed account; both source codes map to the same surrogate |
| Natural key changes | If a vendor’s code changes in the ERP, all fact table rows must be updated to reflect the new code | Surrogate key remains stable; only the dimension table mapping is updated |
| Performance | String natural keys (account codes, entity identifiers) produce larger, slower joins | Integer surrogate keys produce smaller, faster joins in large fact tables |
Surrogate Keys and Oracle EBS-to-BI Integration
In GCC enterprise finance data warehouses built on Oracle EBS source data, surrogate keys resolve a specific practical problem: Oracle EBS uses complex, segment-based natural keys for its chart of accounts (e.g., “01.4001.1001.0000” representing Company.Account.CostCentre.Product segments). These multi-segment natural keys are long strings that are expensive to join between fact and dimension tables in a high-volume data warehouse. The surrogate key generation process — assigning a single integer AccountKey to each unique segment combination — produces a join key that is both simpler to manage and faster to execute in large BI queries against millions of GL fact rows.
Surrogate Keys in Slowly Changing Dimensions (SCD)
Surrogate keys are the technical prerequisite for Slowly Changing Dimension Type 2 historisation. When an entity’s name changes (a Saudi subsidiary is renamed after a group restructuring), the existing dimension row is end-dated and a new dimension row is inserted — with a new surrogate key pointing to the new name, while the old surrogate key points to the old name. Fact table rows created before the name change carry the old surrogate key (preserving historical accuracy — the transaction was recorded when the entity had its old name); fact table rows after the name change carry the new surrogate key. Reports that filter to the entity can choose to show all history (joining to both surrogate keys) or only current-name history (joining only to the current surrogate key), depending on whether the report is historical or current-state oriented.
What Goes Wrong in Practice
The most common surrogate key implementation failure is generating surrogate keys without maintaining a mapping table that links each surrogate key back to the source system’s natural key and the load batch that created it. When an analyst investigates a specific entity’s data in the data warehouse and needs to cross-reference it to the Oracle EBS record, they need the natural key. A surrogate key-only fact table without a maintained mapping table requires the analyst to navigate through multiple tables to find the source system identifier, creating unnecessary investigation overhead in close cycle reconciliation and audit scenarios.
How Loop Wise Solutions Implements Surrogate Keys
We generate surrogate keys using a hash-based approach — computing a deterministic hash of the natural business key — rather than auto-increment sequences, because hash-based surrogate keys can be generated consistently across multiple ETL runs without requiring a centralised sequence generator. Every dimension table includes both the surrogate key and the full natural business key, and the ETL process maintains a surrogate key mapping table that records the natural key, the surrogate key, the source system, and the first load date for each key assignment.
