Data Vault Hub Stored Procedure

Purpose

This code-generation template creates a SQL Server stored procedure that loads a Data Vault Hub table from a Persistent Staging Area (PSA) source — the physical, table-based counterpart to the Hub view template.

The procedure handles one source-to-target mapping per data object pair, so a Hub fed by multiple PSA tables results in multiple procedures — one per source.

Motivation

In a physical Data Vault deployment, a Hub is populated by a load process that inserts only previously unseen business keys. This template generates exactly that — an idempotent, incrementally driven stored procedure that:

• Asks a control framework for its load window (start/end timestamps).
• Reads new PSA rows whose inscription timestamp falls in that window.
• Hashes the business-key components into a surrogate key.
• Inserts the resulting rows into the Hub, suppressing keys that already exist there.

Applicability

• SQL Server family databases; the template uses Microsoft SQL (T-SQL) syntax.

Design Pattern

• Data Vault Hub

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 Hub Data Object.

The generated procedure:

1. Takes an audit-trail @ModuleInstanceId parameter (defaulted to 0 for ad-hoc runs).
2. Calls CreateLoadWindow in the configured control-framework database to evaluate the date range it should process.
3. Reads candidate rows from the PSA, filtering on the inscription timestamp falling within the load window.
4. Hashes the business-key columns with MD5 to produce the Hub surrogate key.
5. Uses a ROW_NUMBER partition on the business key to keep only the earliest arrival per key.
6. Uses NOT EXISTS against the Hub to insert only previously unseen keys.

The procedure is idempotent: repeated runs with the same load window have no effect after the first.

Considerations and consequences

• The template requires a Persistent Staging Area (PSA) as its source — the dataObjectMappings.0.sourceDataObjects.0 is expected to be a PSA-style table with a loadDateTime / inscription timestamp column.
• The template requires a control framework to be present in the configured location, exposing CreateLoadWindow (and optionally RegisterModule / RunModule).
• MD5 is used for hash-key generation. If you need a stronger hash, replace HASHBYTES('MD5', …) in both the surrogate-key construction and the NOT EXISTS lookup — they must stay aligned.

Extensions

If the mapping carries a hasControlFramework extension, the template appends a commented-out RegisterModule / RunModule block to the output. Strip the comment markers to register the procedure as a control-framework module.

The conventions controlFrameworkDatastore, controlFrameworkLocation, persistentStagingAreaObjectDatastore, persistentStagingAreaObjectLocation, integrationLayerObjectDatastore, integrationLayerObjectLocation, auditTrailIdColumn, and inscriptionTimeStampColumn must all be defined on the project.