TODO

Persistent Staging Area Generate Table

Purpose

This code-generation template creates table creation scripts for Snowflake 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

• Snowflake

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.persistentStagingAreaObjectLocation}}.{{./conventions.persistentStagingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}.
--
-- Generated from template '{{templateMetadata.name}}'.
--

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

CREATE TABLE "{{./conventions.persistentStagingAreaObjectLocation}}.{{./conventions.persistentStagingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}"
(
    "{{./conventions.inscriptionTimeStampColumn}}" TIMESTAMP_NTZ NOT NULL
   ,"{{./conventions.inscriptionRecordIdColumn}}" NUMBER NOT NULL
   ,"{{./conventions.sourceTimestampColumn}}" TIMESTAMP_NTZ NOT NULL
   ,"{{./conventions.changeDataColumn}}" VARCHAR NOT NULL
   ,"{{./conventions.auditTrailIdColumn}}" NUMBER NOT NULL
   ,"{{./conventions.checksumColumn}}" BINARY(64) NOT NULL
   {{#each dataItems}}
   ,"{{stringupper name}}" {{dataType}}{{#stringcompare dataType "INT"}}{{else}}{{#stringcompare dataType "DATE"}}{{else}}{{#stringcompare dataType "DATETIME"}}{{else}}{{#stringcompare dataType "TIMESTAMP_NTZ"}}{{else}}{{#stringcompare dataType "TIMESTAMP"}}{{else}}{{#stringcompare dataType "DECIMAL"}}{{else}}{{#stringcompare dataType "VARCHAR"}}{{else}}({{characterLength}}{{#stringcompare dataType "NUMBER"}}38,{{/stringcompare}}37){{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}} NULL
   {{/each}}
);