Data Vault Link SQL Server Table

Purpose

This code-generation template creates the physical SQL Server table for a Data Vault Link — the persisted store of unique relationships between two or more Hubs.

It is the table-based companion to the Link view template, and is appropriate for a physical data warehouse implementation.

Motivation

A Link table records the existence of a business relationship — for example, that a particular customer placed a particular order. The Link’s hash key acts as the surrogate primary key; the business-key references back to each participating Hub act as the natural unique identifier. Materialising this as a real table gives you a stable, indexed structure that downstream Satellites and queries can join against.

Applicability

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

Design Pattern

• Data Vault Link

Schema Type

• Data Object

Output Type

• Data Definition Language (DDL)

Implementation guidelines

This template is intended to be assigned to Link Data Objects in the Integration Layer. It emits:

• One column per Data Item with the declared SQL data type, character length, and numeric precision/scale as needed.
• A non-clustered primary key on columns flagged isPrimaryKey — typically the Link hash key.
• A unique clustered index on the reference key columns (the Hub hash keys that the Link points to). The index excludes the audit-trail ID and inscription timestamp columns, which are not part of the relationship’s natural key.

The clustering strategy (NONCLUSTERED PK + CLUSTERED reference-key index) optimises for the relationship-lookup queries that dominate Link access in Data Vault workloads.

Considerations and consequences

• The clustered-index column list is built by walking all dataItems and excluding the audit-trail ID, the inscription timestamp, and any column already flagged isPrimaryKey. The remaining columns are assumed to be reference keys.
• If your Link metadata includes additional columns beyond the reference keys (for example, dependent-child keys or degenerate dimensions), they will appear in the clustered index unless explicitly excluded. Adjust the metadata or the template if that’s a problem.
• All columns are emitted as NOT NULL.

Extensions

The template reads datastore and location extensions on the Data Object’s Data Connection to build the fully qualified table name. Both must be set on the connection.

Code

--
-- Table creation statement for [{{lookupExtension dataConnection.extensions "datastore"}}].[{{lookupExtension dataConnection.extensions "location"}}].[{{name}}].
--
-- Generated from template '{{templateMetadata.name}}'.
--

DROP TABLE IF EXISTS [{{lookupExtension dataConnection.extensions "datastore"}}].[{{lookupExtension dataConnection.extensions "location"}}].[{{name}}];

CREATE TABLE [{{lookupExtension dataConnection.extensions "datastore"}}].[{{lookupExtension dataConnection.extensions "location"}}].[{{name}}]
(
{{#each dataItems}}
  [{{name}}] {{stringUpper dataType}}{{#if characterLength}}({{characterLength}}){{/if}}{{#if numericPrecision}}({{numericPrecision}},{{numericScale}}){{/if}} NOT NULL,
{{/each}}
  CONSTRAINT [PK_{{name}}] PRIMARY KEY NONCLUSTERED
    (
{{#each dataItems}}
{{#if isPrimaryKey}}      {{#if @index}},{{/if}}[{{stringUpper name}}]
{{/if}}
{{/each}}
    )
);

-- Create a clustered index on the reference key column(s).
CREATE UNIQUE CLUSTERED INDEX IX_{{name}} ON [{{lookupExtension dataConnection.extensions "datastore"}}].[{{lookupExtension dataConnection.extensions "location"}}].[{{name}}]
(
    {{#each dataItems}}{{~#stringCompare name ../conventions.auditTrailIdColumn}}{{~else~}}{{~#stringCompare name ../../conventions.inscriptionTimeStampColumn}}{{else}}{{~#if isPrimaryKey}}{{else}}{{~name}}{{#unless @last}}, {{/unless}}{{/if}}{{/stringCompare}}{{/stringCompare}}{{/each}}
);