Relationship History

The relationship between two entities can change just like the entities themselves.

Most historical models focus on entities such as customers, contracts or products. However, the relationships between these entities often change over time as well.

A customer may switch advisors. A policy may move to a different broker. An employee may change departments. Historical reporting requires not only the correct entity state, but also the correct relationship state.

A policy moves from Broker A to Broker B during the year.

Try this Relationship History case in Target Table Validation

Use these sample target tables to test whether historical attribution uses the correct relationship at the snapshot date.

1. Copy one of the target tables below.
2. Open Target Table Validation.
3. Paste the copied table as your target output.
4. Check whether August is attributed to Broker B or incorrectly to Broker A.

policy_id,broker_id,snapshot_date,valid_from,valid_to,relationship_status
P123,Broker B,2024-08-31,2024-07-01,2024-12-31,historized_relationship

policy_id,broker_id,snapshot_date,valid_from,valid_to,relationship_status
P123,Broker A,2024-08-31,2024-01-01,2024-12-31,current_relationship_used

Relationships are often treated as simple foreign keys even when they are time-dependent.

In many models, relationships are stored as current references: current broker, current advisor, current department or current sales organization. This works for current-state reporting, but it breaks historical attribution.

Once reporting asks “who owned this at the reporting date?”, the relationship itself must become historized.

Model the relationship as its own historized object.

Validate that each reporting date resolves to the intended relationship.

Historical attribution often depends more on relationship history than on entity history.

Commission reporting, portfolio reporting and organizational KPIs all depend on knowing which relationship was active at a specific point in time.

Ignoring relationship history can produce historically incorrect reports even when all dimensions are perfectly historized.