Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Late arrivals and surrogate keys

How do late facts and late corrections join to the right version, and why do surrogate keys exist at all?

Where it really hurts: late arrivals

Late-arriving facts

A fact for event time T arrives today, say three days late. It must join to the version that was effective at T. So the loader cannot use "the current row". It must range-search the effective interval:

SELECT d.customer_sk
FROM dim_customer d
WHERE d.customer_id = :cid
  AND :event_time >= d.valid_from AND :event_time < d.valid_to;

If the member did not exist yet at time T, you insert an : a placeholder row keyed by the , with unknown attributes, to be filled in later when the real data arrives. The placeholder is a deliberate lie with an expiry date.

Late-arriving dimension changes

Suppose a correction says: "this customer's region actually changed three months ago." Now you must split an existing effective interval and re-key the facts that were already loaded against the wrong version. That is a rewrite of history proportional to the affected fact volume. This is the case that turns a from routine into a project.

Surrogate keys versus natural keys

The usual explanation is join performance. It is real and small: a 4-byte integer sorts and compresses better than a 40-character composite key. But that is not why exist.

They exist because Type 2 needs a key that distinguishes versions of the same entity, and a natural key, by definition, cannot. A natural key identifies the entity, which is exactly what all the versions have in common. The moment you accept semantics, the surrogate key is forced. Everything else is a side benefit.

Secondary forces, each genuine:

  • Natural keys are controlled by an outside party who can reissue, recycle or reformat them without telling you.
  • Natural keys are sometimes composite and wide, so they carry their width into every .
  • Merging two source systems produces two natural key spaces with overlapping values and different meanings (customer 1001 in system A is not customer 1001 in system B).

The cost: a lookup serialization point

Assigning a surrogate key requires consulting the existing key map. That is a in the middle of an otherwise parallel load: two parallel loaders must not both create a new key for the same new customer.

The escape is a deterministic hash of (natural key, version marker):

SELECT md5(concat_ws('|', source_system, customer_id, valid_from)) AS customer_sk

This removes the lookup entirely and makes loads order-independent and embarrassingly parallel (every piece can run at the same time with no coordination). This is the mechanism Data Vault is built on. You pay in collision probability (two different inputs giving the same hash), which is manageable. You also pay in something less obvious: the hash encodes the assumption that the natural key is stable and unique into every downstream row. When that assumption breaks, there is no indirection layer to repair it.

What this assumes and cannot verify: that the source's key means one thing. Who pays: whoever finds out, eighteen months later, that two upstream entities were merged and their key was reused.

Compression (keys): surrogate keys exist because versions need an identity that business keys cannot carry; hashing them buys parallel loads by hard-coding the assumption that the business key never lies.