TODO

Persistent Staging Area Generate Table From User Managed

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. However, for User Managed staging tables there is ‘source’ as such because the landing area tables are maintained in the staging layer directly. Because of this, a separate template is required because the landing data object structure already contains the required data solution standard columns.

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 assign to ‘source’ objects. It will add the defined data solution standard columns and streamline the data types.

Considerations and consequences

N/A.

Extensions

N/A.

Code

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

DROP TABLE IF EXISTS [{{./conventions.persistentStagingAreaObjectDatastore}}].[{{./conventions.persistentStagingAreaObjectLocation}}].[{{./conventions.persistentStagingAreaObjectPrefix}}{{stringreplace name "STG" ""}}];

CREATE TABLE [{{./conventions.persistentStagingAreaObjectDatastore}}].[{{./conventions.persistentStagingAreaObjectLocation}}].[{{./conventions.persistentStagingAreaObjectPrefix}}{{stringreplace name "STG" ""}}]
(
    [{{./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~}}
     {{stringcompare name "AUDIT_TRAIL_ID"}}{{stringcompare name "CHANGE_DATA_INDICATOR"}}{{stringcompare name "CHECKSUM"}}{{stringcompare name "INSCRIPTION_RECORD_ID"}}{{stringcompare name "INSCRIPTION_TIMESTAMP"}}{{stringcompare name "SOURCE_TIMESTAMP"}}
   ,[{{stringupper name}}]
   {{~#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(){{else}}
   {{~#stringcompare dataType "nvarchar"}} NVARCHAR({{#hasStringValue ../../../../../../../../../../../../../../../dataItemsMetadata.keyDataItems name}}400{{else}}MAX{{/hasStringValue}}){{else}}
   {{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}
   {{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}{{~/stringcompare}}
   {{~/each}}

   ,CONSTRAINT [PK_{{./conventions.persistentStagingAreaObjectPrefix}}{{stringreplace name "STG" ""}}] 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)
);