Data Vault Link Satellite (Driving Key) Stored Procedure

Purpose

This code-generation template creates a SQL Server stored procedure that loads a Data Vault Link Satellite using Driving Key semantics. It is the physical-table counterpart to the Driving Key view template.

A Driving Key Satellite is the right pattern when a Link describes a relationship where one side is “owned” by another, and a new value for the dependent side implicitly ends the previous relationship — for example, a customer’s current assigned account, where assigning a new account closes the prior one.

Motivation

A standard Link Satellite only inserts new rows — it has no concept of the previous relationship being ended. Driving Key semantics introduce the rule that changing the dependent side of the relationship (for example, switching the assigned account for a customer) should close the previous Satellite row. This pattern is what makes “current relationship” queries against the Data Vault feasible without resorting to point-in-time tables.

Applicability

• SQL Server family databases; the template uses Microsoft SQL (T-SQL) syntax.
• The Link must have a clear driving-key column — typically marked with an isDrivingKey extension on the source data item.

Design Pattern

• Data Vault Satellite (Driving Key)

Schema Type

• Data Object Mapping

Output Type

• Stored Procedure

Implementation guidelines

This template is intended to be assigned to Data Object Mappings whose target is a Link Satellite with one designated driving-key component.

The generated procedure follows the standard Link Satellite load but adds a per-driving-key LAG-partitioned comparison: it detects whether the dependent (non-driving) business-key component has changed for the same driving key, and uses that signal to drive the “close previous relationship” logic.

Considerations and consequences

• You must mark exactly one business-key component as the driving key — the template inspects each source Data Item’s extensions for isDrivingKey to know which column to anchor the partitioning on.
• The procedure produces additional checksum/change indicators beyond a plain Link Satellite. Downstream consumers should be aware that “no change” rows are suppressed, and the latest row for a given driving-key value represents the current relationship.
• As with the other Satellite templates, MD5 is used; replace consistently if a stronger hash is needed.
• This pattern is invasive: misidentifying the driving key produces semantically wrong history. Validate the metadata flags before generating production code.

Extensions

Requires an isDrivingKey extension on the driving-key source Data Item. The standard conventions auditTrailIdColumn, inscriptionTimeStampColumn, inscriptionRecordIdColumn, changeDataColumn, checksumColumn, sourceTimestampColumn, and controlFramework* must all be defined on the project.