Data Vault Satellite SQL Server Table

Purpose

This code-generation template creates the physical SQL Server table for a Data Vault Satellite — the persisted, time-variant store of descriptive attributes attached to a Hub or Link.

It is the table-based companion to the Satellite view template, and is the right choice when you want Satellite data materialised rather than virtualised over a PSA.

Motivation

Satellites hold the data that changes over time in a Data Vault — the prices, statuses, names, and other descriptive attributes attached to a business entity. Materialising the Satellite as a table gives you fast access to history, supports indexing on the parent hash key, and is the standard physical implementation pattern.

Applicability

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

Design Pattern

• Data Vault Satellite

Schema Type

• Data Object

Output Type

• Data Definition Language (DDL)

Implementation guidelines

This template is intended to be assigned to Satellite 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 clustered primary key on columns flagged isPrimaryKey — typically the composite of the parent hash key and the load-date timestamp.

Unlike the Hub and Link templates, Satellite columns are emitted without the NOT NULL qualifier. Descriptive attributes in a Satellite frequently allow nulls (a customer’s middle name, an optional address line), and forcing NOT NULL would prevent legitimate sparse data from loading.

The clustered primary key on (parent_hash_key, load_date) matches the standard Data Vault access pattern: queries fetching the history of a particular parent are served by an in-order index scan.

Considerations and consequences

• Nullability on individual columns is not set per Data Item — every non-key column allows nulls. If you need stricter constraints, adjust the metadata or extend the template.
• The clustered PK includes every column flagged isPrimaryKey. For a standard Satellite this is parent_hash_key + load_date_timestamp; if your metadata flags additional columns, they will be part of the clustering key.
• The script is destructive — DROP TABLE IF EXISTS precedes the CREATE TABLE. Manage data preservation through your deployment process.

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}},
{{/each}}
  CONSTRAINT [PK_{{name}}] PRIMARY KEY CLUSTERED
    (
{{#each dataItems}}
{{#if isPrimaryKey}}      {{#if @index}},{{/if}}[{{stringUpper name}}]
{{/if}}
{{/each}}
    )
);