TODO

Persistent Staging Area Generate Table

Purpose

This code-generation template creates table creation scripts for SQL Server based on Persistent Staging Area (PSA) conventions.

Motivation

PSA tables typically follow the structure of the operational data systems to which the data solution interfaces. The PSA object contains a number of standard columns, which will be added to the definition by this template.

Applicability

• SQL Server family databases

Design Pattern

• Persistent Staging Area (PSA)

Schema Type

• Data Object

Output Type

• Data Definition Language (DDL)

Implementation guidelines

This template is intended to be assigned to ‘source’ Data Objects. It will add the defined data solution standard columns and streamline the data types.

The columns that have been defined to be part of the Primary Key will be generated as NOT NULL so that constraints on the key can be enforced. The Primary Key constrains is also generated as part of this template.

Considerations and consequences

[!NOTE] Character columns that are part of the Primary Key definition will be generated with character length 100. This is because these columns are used in various window functions and indexes, which are subject to size / length limitations in SQL Server.

Extensions

N/A.

Code

--
-- Persistent Staging Area table creation statement for [{{./conventions.persistentStagingAreaObjectDatastore}}].[{{./conventions.persistentStagingAreaObjectLocation}}].[{{./conventions.persistentStagingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}].
--
-- Generated from template '{{templateMetadata.name}}'.
--

DROP TABLE IF EXISTS [{{./conventions.persistentStagingAreaObjectDatastore}}].[{{./conventions.persistentStagingAreaObjectLocation}}].[{{./conventions.persistentStagingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}];

CREATE TABLE [{{./conventions.persistentStagingAreaObjectDatastore}}].[{{./conventions.persistentStagingAreaObjectLocation}}].[{{./conventions.persistentStagingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}]
(
    [{{./conventions.inscriptionTimeStampColumn}}] [DATETIME2](7) NOT NULL
   ,[{{./conventions.inscriptionRecordIdColumn}}] INT NOT NULL
   ,[{{./conventions.sourceTimestampColumn}}] [DATETIME2](7) NOT NULL
   ,[{{./conventions.changeDataColumn}}] [CHAR](1) NOT NULL
   ,[{{./conventions.auditTrailIdColumn}}] [INT] NOT NULL
   ,[{{./conventions.checksumColumn}}] BINARY(16) NOT NULL
{{#each dataItems}}
   ,[{{~stringupper name~}}] {{!-- Add any type conversions below --}}
   {{~#stringcompare dataType "int"}}INT{{else}}
   {{~#stringcompare dataType "date"}}DATETIME2(7){{else}}
   {{~#stringcompare dataType "datetime"}}DATETIME2(7){{else}}
   {{~#stringcompare dataType "decimal"}}NUMERIC(38,20){{else}}
   {{~#stringcompare dataType "number"}}NUMERIC(38,20){{else}}
   {{~#stringcompare dataType "numeric"}}NUMERIC(38,20){{else}}
   {{~#stringcompare dataType "varchar"}}NVARCHAR({{#hasStringValue ../../../../../../../../dataItemsMetadata.keyDataItems name}}100{{else}}MAX{{/hasStringValue}}){{else}}
   {{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}} {{#hasStringValue ../dataItemsMetadata.keyDataItems name}}NOT NULL{{else}}NULL{{/hasStringValue}}
{{/each}}
   ,CONSTRAINT [PK_{{./conventions.persistentStagingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}] PRIMARY KEY NONCLUSTERED
    (
      {{#each ./dataItemsMetadata.keyDataItems}}
      [{{stringupper @value}}],
      {{/each}}
    [INSCRIPTION_TIMESTAMP] ASC,
    [INSCRIPTION_RECORD_ID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
);