TODO

Landing Area Generate Table

Purpose

This code-generation template creates table creation scripts for Snowflake based on Landing Area conventions.

Motivation

After importing data definitions from operational systems, a typical next step is generating Landing Area objects to load the data delta into. This template creates these tables.

Applicability

• Snowflake

Design Pattern

• Staging / Landing

Schema Type

• Data Object

Output Type

• Data Definition Language (DDL)

Implementation guidelines

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

Considerations and consequences

N/A.

Extensions

N/A.

Code

--
-- Landing Area table creation statement for {{./conventions.landingAreaObjectLocation}}.{{./conventions.landingAreaObjectPrefix}}_{{name}}.
--
-- Generated from template '{{templateMetadata.name}}'.
--

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

CREATE TABLE "{{./conventions.landingAreaObjectLocation}}.{{./conventions.landingAreaObjectLocation}}_{{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}}
);