What Gets Committed

📘

Git Repo Format Upgrade

Action Required

This feature is opt-in until August 1, 2024. After August 1, 2024, all Git repos will be automatically updated.


We have updated the way files are committed into Git. Previously it was split into a data.yml and the specific nodes.yml files. Now you can see the changes for:

  • Node Types
  • Environment Mappings
  • Subgraphs
  • Jobs
  • Macros
  • Packages

How to Opt-In

Organization Administrators can turn it on for the whole organization. Go to Org Settings > Preferences and Enable Git Repository Format Update. Once enabled, all Workspace users will be prompted to switch to the new format. They won't be able to make commits until they switch to the new format.

  • Users will be notified the upgrade is available, but will not be able to upgrade.
  • Previously committed metadata can still be deployed after the upgrade.

Learn more about what gets committed inWhat Gets Committed.

🚧

Git Repo Format Is Permanent

Once the Git Repo Format has been enabled, it can’t be turned off.

What Gets Committed

When you make a commit, we’ll commit the following:

  • Environment Mappings (folder)
  • Subgraphs (folder)
  • Jobs (folder)
  • Macros (folder)
  • Nodes (folder)
  • Packages (folder)
  • data.yml

Environment Mappings

Contains the Storage Mappings and environment ID. Each environment will have it's own file.

PROD-ENV.yml (sample)
fileVersion: 1
id: "3"
mappingDefinitions:
  SOURCE:
    database: SNOWFLAKE_SAMPLE_DATA
    schema: TPCH_SF10
  TARGET:
    database: COA_TESTING
    schema: QA
name: Dev Env
type: Environment

Subgraphs

Each subgraph created will have it's own file. Each node added to a subgraph is a step.

QA_Subgraph.yml (sample)
fileVersion: 1
id: "1"
name: New Subgraph
steps:
  - 5b8dea41-4e27-4064-80d3-41177e88fd78
type: Subgraph

Jobs

Each Job created will have it's own file.

QA_JOBS.yml (sample)
excludeSelector: ""
fileVersion: 1
id: "1"
includeSelector: "{ location: SAMPLE name: NATION }"
name: New Job
type: Job

Macros

Each Macro created will have it's own file.

Macros.yml (sample)
fileVersion: 1
id: "1"
macroString: |-
  {%- macro even_odd(column) -%}
      CASE WHEN MOD({{ column }}, 2) = 0 THEN 'EVEN' ELSE 'ODD' END
  {%- endmacro %}
name: macro
type: Macro

Nodes

Each Node created will have it's own file.

Nodes.yml (sample)
fileVersion: 1
id: e777e738-0b6f-420b-9def-164435d6f1ce
name: CUSTOMER
operation:
  database: ""
  dataset: ""
  deployEnabled: true
  description: Customer data as defined by TPC-H
  locationName: SOURCE
  metadata:
    columns:
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 4ed4a7b4-e55a-473c-9654-9a8944d5a26c
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: NUMBER(38,0)
        defaultValue: ""
        description: ""
        name: C_CUSTKEY
        nullable: false
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 375c2ba4-28e5-4b72-b624-158e75a365c7
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: VARCHAR(25)
        defaultValue: ""
        description: ""
        name: C_NAME
        nullable: false
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 486d2311-839b-464e-a2f1-7a6e8bbd7b39
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: VARCHAR(40)
        defaultValue: ""
        description: ""
        name: C_ADDRESS
        nullable: false
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 3c7ab0e8-40f1-4292-b2ca-7c9db94e639a
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: NUMBER(38,0)
        defaultValue: ""
        description: ""
        name: C_NATIONKEY
        nullable: false
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 5f4e8585-f5e9-4359-9336-78d443c955a1
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: VARCHAR(15)
        defaultValue: ""
        description: ""
        name: C_PHONE
        nullable: false
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 7057ba0e-4090-4a3d-8405-420459305131
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: NUMBER(12,2)
        defaultValue: ""
        description: ""
        name: C_ACCTBAL
        nullable: false
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: d036844e-c469-4034-802b-0bb9802416c8
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: VARCHAR(10)
        defaultValue: ""
        description: ""
        name: C_MKTSEGMENT
        nullable: true
        primaryKey: false
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: ed630963-7968-4501-9f12-e5d08143061e
          stepCounter: e777e738-0b6f-420b-9def-164435d6f1ce
        config: {}
        dataType: VARCHAR(117)
        defaultValue: ""
        description: ""
        name: C_COMMENT
        nullable: true
        primaryKey: false
        uniqueKey: false
    join:
      joinCondition: FROM {{ ref('SOURCE', 'CUSTOMER') }}
  name: CUSTOMER
  schema: ""
  sqlType: Source
  table: CUSTOMER
  type: sourceInput
  version: 1
type: Node
fileVersion: 1
id: 5b8dea41-4e27-4064-80d3-41177e88fd78
name: DIM_NATION
operation:
  config:
    postSQL: ""
    preSQL: ""
    testsEnabled: true
  database: ""
  deployEnabled: true
  description: Nation data as defined by TPC-H
  isMultisource: false
  locationName: TARGET
  materializationType: table
  metadata:
    appliedNodeTests: []
    columns:
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: a171c9ad-12cc-4acf-835f-149f85abfdc6
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: NUMBER
        defaultValue: ""
        description: ""
        isSurrogateKey: true
        name: DIM_NATION_KEY
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 24f94e8a-3b4e-4683-9f91-ab018276cd4f
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: N_NATIONKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 47ac02ae-5dd5-4b15-b428-66c6e19a39ce
                stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: d5705541-b146-48ab-9c6f-fa2446c4c167
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: VARCHAR(25)
        description: ""
        name: N_NAME
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 8a92fb9f-4715-4d18-91c1-842a4791cee4
                stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: f842b58f-b0b1-48e6-b45f-3db283f0e43e
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: N_REGIONKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 5582ceb0-a0a4-468c-a7be-998e1bcceecc
                stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 51ad5e20-85ba-4566-af8d-15c8d2fbb9bd
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: VARCHAR(152)
        description: ""
        name: N_COMMENT
        nullable: true
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: ef392e40-26bc-4dd1-9e25-77bb813fc46c
                stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: e6611ad6-e7f9-4747-aef4-31675d7526d7
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: NUMBER
        defaultValue: ""
        description: ""
        isSystemVersion: true
        name: SYSTEM_VERSION
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 3751c6cb-6a93-4f08-a0a7-853d13ce90aa
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: VARCHAR
        defaultValue: ""
        description: ""
        isSystemCurrentFlag: true
        name: SYSTEM_CURRENT_FLAG
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 1998845b-361f-486b-9577-863de67a2ae4
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemStartDate: true
        name: SYSTEM_START_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: ac82186a-44b5-45e4-869f-fbcc04af491e
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemEndDate: true
        name: SYSTEM_END_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST('2999-12-31 00:00:00' AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 313ca23a-caff-4dbf-b419-65af2ea09cf0
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemCreateDate: true
        name: SYSTEM_CREATE_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: b76d61ea-04b1-4e6d-ab58-7eaa333152f3
          stepCounter: 5b8dea41-4e27-4064-80d3-41177e88fd78
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemUpdateDate: true
        name: SYSTEM_UPDATE_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
    cteString: ""
    enabledColumnTestIDs: []
    sourceMapping:
      - aliases: {}
        customSQL:
          customSQL: ""
        dependencies:
          - locationName: TARGET
            nodeName: STG_NATION
        join:
          joinCondition: FROM {{ ref('TARGET', 'STG_NATION') }} "STG_NATION"
        name: DIM_NATION
        noLinkRefs: []
  name: DIM_NATION
  overrideSQL: false
  schema: ""
  sqlType: Dimension
  type: sql
  version: 1
type: Node
fileVersion: 1
id: e15f62d4-9eaa-4a15-8b29-db711dedd863
name: FACT
operation:
  config:
    postSQL: ""
    preSQL: ""
    testsEnabled: true
  database: ""
  deployEnabled: true
  description: Orders data as defined by TPC-H
  isMultisource: false
  locationName: TARGET
  materializationType: table
  metadata:
    appliedNodeTests: []
    columns:
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 20c7233c-471e-493b-ab14-d2e2f1a0165c
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: O_ORDERKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 65b93cb8-6472-4b72-800c-1404342027b8
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: f396aeed-e485-4009-a86d-52a42a1def62
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: O_CUSTKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 3d752dcc-6bb7-44b0-a869-6a4bf3818025
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 35b9f17c-0ed5-4422-94c4-1d9645b476de
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: VARCHAR(1)
        description: ""
        name: O_ORDERSTATUS
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 77cc8f1c-5787-4de9-81a6-0d0b0fab854b
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: bdb6e345-565e-48ed-b9f1-324fe0a431b3
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: NUMBER(12,2)
        description: ""
        name: O_TOTALPRICE
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 756b1696-81ee-4e8a-9cd6-f0868f89ead5
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 4fb6930e-4559-4ae9-b844-b0017f4f5879
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: DATE
        description: ""
        name: O_ORDERDATE
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 38873d7b-259d-4ecc-8f35-e6dffb6441da
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: e0aee3b5-31db-4042-8c99-7cf11f8245c3
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: VARCHAR(15)
        description: ""
        name: O_ORDERPRIORITY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: ef0e9d71-0f6d-42d7-957a-1aded0282cc9
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 3638fe4c-537c-4f71-9c75-4a5599bf77d7
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: VARCHAR(15)
        description: ""
        name: O_CLERK
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 76268ee3-e4b7-4796-af32-26a40dd1f2d4
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: ec4e4049-9572-42ab-9a72-ec3e6af5d370
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: O_SHIPPRIORITY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 2d4ccac5-5ae4-43e4-88df-d95bccd2be68
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 8198f262-b1ee-47d5-ae2b-3af01265e1c7
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: VARCHAR(79)
        description: ""
        name: O_COMMENT
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 4ce1713b-8035-4ada-bf34-bf4797198e6a
                stepCounter: 02ba3c15-17ca-4ed4-b2a7-68ceff20ef3b
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 3f7f1e62-eedc-4cb4-94cf-238b16fdd4a0
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemCreateDate: true
        name: SYSTEM_CREATE_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: e8777c9c-ae6a-4e6f-9fa7-38e778577c49
          stepCounter: e15f62d4-9eaa-4a15-8b29-db711dedd863
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemUpdateDate: true
        name: SYSTEM_UPDATE_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
    cteString: ""
    enabledColumnTestIDs: []
    sourceMapping:
      - aliases: {}
        customSQL:
          customSQL: ""
        dependencies:
          - locationName: SOURCE
            nodeName: ORDERS
        join:
          joinCondition: FROM {{ ref('SOURCE', 'ORDERS') }} "ORDERS"
        name: FACT
        noLinkRefs: []
  name: FACT
  overrideSQL: false
  schema: ""
  sqlType: Fact
  type: sql
  version: 1
type: Node
fileVersion: 1
id: c5c237f3-b824-49a4-8ae2-22271f19b60c
name: STG_NATION
operation:
  config:
    insertStrategy: INSERT
    postSQL: ""
    preSQL: ""
    testsEnabled: true
    truncateBefore: true
  database: ""
  deployEnabled: true
  description: Nation data as defined by TPC-H
  isMultisource: false
  locationName: TARGET
  materializationType: table
  metadata:
    appliedNodeTests: []
    columns:
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 47ac02ae-5dd5-4b15-b428-66c6e19a39ce
          stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: N_NATIONKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 2aab98c9-d282-424e-b5cc-b67b08c6a7dc
                stepCounter: 3d6d2914-999a-4a6f-a014-a6ad370c925f
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 8a92fb9f-4715-4d18-91c1-842a4791cee4
          stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
        config: {}
        dataType: VARCHAR(25)
        description: ""
        name: N_NAME
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: a68700ca-26bf-4443-b688-ae01754a9faf
                stepCounter: 3d6d2914-999a-4a6f-a014-a6ad370c925f
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 5582ceb0-a0a4-468c-a7be-998e1bcceecc
          stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: N_REGIONKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 04182a30-dcef-40bd-877d-e3eb25cc4364
                stepCounter: 3d6d2914-999a-4a6f-a014-a6ad370c925f
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: ef392e40-26bc-4dd1-9e25-77bb813fc46c
          stepCounter: c5c237f3-b824-49a4-8ae2-22271f19b60c
        config: {}
        dataType: VARCHAR(152)
        description: ""
        name: N_COMMENT
        nullable: true
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 1dc08f93-e0d2-4c81-a613-3bdde41ae16e
                stepCounter: 3d6d2914-999a-4a6f-a014-a6ad370c925f
            transform: ""
    cteString: ""
    enabledColumnTestIDs: []
    sourceMapping:
      - aliases: {}
        customSQL:
          customSQL: ""
        dependencies:
          - locationName: SOURCE
            nodeName: NATION
        join:
          joinCondition: FROM {{ ref('SOURCE', 'NATION') }} "NATION"
        name: STG_NATION
        noLinkRefs: []
  name: STG_NATION
  overrideSQL: false
  schema: ""
  sqlType: Stage
  type: sql
  version: 1
type: Node
fileVersion: 1
id: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
name: PERSISTANT_STAGE
operation:
  config:
    postSQL: ""
    preSQL: ""
    testsEnabled: true
  database: ""
  deployEnabled: true
  description: Supplier data as defined by TPC-H
  isMultisource: false
  locationName: TARGET
  materializationType: table
  metadata:
    appliedNodeTests: []
    columns:
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 87362678-cfef-4afa-b8ee-f116574311db
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: NUMBER
        defaultValue: ""
        description: ""
        isSurrogateKey: true
        name: PSTG_SUPPLIER_KEY
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: d0ca4bcc-a627-4bbf-b3aa-dad6d1f56c58
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: S_SUPPKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: c9b9b459-a483-44f1-af29-ec95efad78a6
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 7d6c1153-47b5-43a6-aba8-60733b8d619a
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: VARCHAR(25)
        description: ""
        name: S_NAME
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 55d77d8a-a818-4246-bb54-293b413b847d
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: dc935a1b-59a2-407f-9256-5cb16ee0a42b
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: VARCHAR(40)
        description: ""
        name: S_ADDRESS
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 960e9c87-f6d7-4b8c-83a1-7dd550ff1447
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 37617b0b-257c-4c7a-8a82-8f0f9f90f14f
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: NUMBER(38,0)
        description: ""
        name: S_NATIONKEY
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 4e809dc8-3f0b-410b-8d73-7799dca91b58
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 061c6a5f-0aa4-4178-9331-536a7b322c09
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: VARCHAR(15)
        description: ""
        name: S_PHONE
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 0c608479-3eba-45a4-977b-fba8aa1b0888
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: 5618abec-c8d1-4025-8a64-ff40ae3257f2
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: NUMBER(12,2)
        description: ""
        name: S_ACCTBAL
        nullable: false
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 5cde27bf-6a24-4065-ab1b-69bd4a39c288
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - appliedColumnTests: {}
        columnReference:
          columnCounter: c31ac4c1-32d3-41f1-a1ed-a9e5d7790207
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: VARCHAR(101)
        description: ""
        name: S_COMMENT
        nullable: true
        sourceColumnReferences:
          - columnReferences:
              - columnCounter: 05c51160-8280-4546-8578-574ef54b8a8c
                stepCounter: 5a46ca0c-23ff-4c31-bba6-76d4a2d292bf
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 4c3b222d-48f5-4f95-9e71-5cde01659c5e
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: NUMBER
        defaultValue: ""
        description: ""
        isSystemVersion: true
        name: SYSTEM_VERSION
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 778e0d8b-590f-4416-8cfe-9f300b9b9060
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: VARCHAR
        defaultValue: ""
        description: ""
        isSystemCurrentFlag: true
        name: SYSTEM_CURRENT_FLAG
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 9a6243d6-4b56-465a-bf13-d5610b2b104c
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemStartDate: true
        name: SYSTEM_START_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: a98dd27f-8af4-408b-ac57-6df2d09274b9
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemEndDate: true
        name: SYSTEM_END_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST('2999-12-31 00:00:00' AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 5e228153-bffd-4bdb-950e-5a454a2107b3
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemCreateDate: true
        name: SYSTEM_CREATE_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: f83583b8-f8d4-40e0-8cc9-66b8b9f4dbbe
          stepCounter: 24c80d47-13b3-4bfe-b84e-80cabcff7b9b
        config: {}
        dataType: TIMESTAMP
        defaultValue: ""
        description: ""
        isSystemUpdateDate: true
        name: SYSTEM_UPDATE_DATE
        nullable: true
        sourceColumnReferences:
          - columnReferences: []
            transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
    cteString: ""
    enabledColumnTestIDs: []
    sourceMapping:
      - aliases: {}
        customSQL:
          customSQL: ""
        dependencies:
          - locationName: SOURCE
            nodeName: SUPPLIER
        join:
          joinCondition: FROM {{ ref('SOURCE', 'SUPPLIER') }} "SUPPLIER"
        name: PERSISTANT_STAGE
        noLinkRefs: []
  name: PERSISTANT_STAGE
  overrideSQL: false
  schema: ""
  sqlType: persistentStage
  type: sql
  version: 1
type: Node

Node Types

These are the Coalesce managed templates. These files can't be changed. If a change is detected, we'll automatically attempt to revert the changes on the next commit.
You can make a copy and use the templates to create a custom node.

Dimension-Dimension
{% if node.materializationType == 'table' %}
	{{ stage('Create Dimension Table') }}

	CREATE OR REPLACE TABLE {{ ref_no_link(node.location.name, node.name) }}
	(
		{% for col in columns %}
			"{{ col.name }}" {{ col.dataType }}
			{% if col.isSurrogateKey %}
		        identity
			{% endif %}
			{%- if not col.nullable %} NOT NULL
				{%- if col.defaultValue | length > 0 %} DEFAULT {{ col.defaultValue }}{% endif %}
			{% endif %}
			{%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
			{%- if not loop.last -%}, {% endif %}
		{% endfor %}
	)
	{%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}


{% elif node.materializationType == 'view' %}
	{{ stage('Create Dimension View') }}

	CREATE OR REPLACE VIEW {{ ref_no_link(node.location.name, node.name) }}
	(
		{% for col in columns %}
			"{{ col.name }}"
			{%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
			{%- if not loop.last -%},{% endif %}
		{% endfor %}
	)
	{%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
	AS
	{% for source in sources %}

		{% if loop.first %}SELECT {% endif %}

		{% for col in source.columns %}
			{% if col.isSurrogateKey or col.isSystemUpdateDate or col.isSystemCreateDate %}
                NULL
			{% else %}
                {{ get_source_transform(col) }}
			{% endif %}
			AS "{{ col.name }}"
			{%- if not loop.last -%}, {% endif %}
		{% endfor %}
		{{ source.join }}

		{% if not loop.last %} UNION ALL {% endif %}
	{% endfor %}

{% endif %}
fileVersion: 1
id: Dimension
isDisabled: false
metadata:
  defaultStorageLocation: null
  error: null
  nodeMetadataSpec: |-
    capitalized: Dimension
    short: DIM
    tagColor: '#1E339A'
    plural: Dimensions

    config:
    - groupName: Options
      items:
      - type: materializationSelector
        isRequired: true
        default: table
        options:
        - table
        - view

      - type: multisourceToggle
        enableIf: "{% if node.materializationType == 'table' %} true {% else %} false {% endif %}"

      - type: businessKeyColumns
        isRequired: true

      - type: changeTrackingColumns
        isRequired: false

      - type: overrideSQLToggle
        enableIf: "{% if node.materializationType == 'view' %} true {% else %} false {% endif %}"

      - displayName: Enable Tests
        attributeName: testsEnabled
        type: toggleButton
        default: true
        
      - displayName: Pre-SQL
        attributeName: preSQL
        type: textBox
        syntax: sql
        isRequired: false

      - displayName: Post-SQL
        attributeName: postSQL
        type: textBox
        syntax: sql
        isRequired: false

    systemColumns:
    - displayName: '{{NODE_NAME}}_KEY'
      transform: ''
      dataType: NUMBER
      placement: beginning
      attributeName: isSurrogateKey

    - displayName: SYSTEM_VERSION
      transform: ''
      dataType: NUMBER
      placement: end
      attributeName: isSystemVersion

    - displayName: SYSTEM_CURRENT_FLAG
      transform: ''
      dataType: VARCHAR
      placement: end
      attributeName: isSystemCurrentFlag

    - displayName: SYSTEM_START_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemStartDate

    - displayName: SYSTEM_END_DATE
      transform: CAST('2999-12-31 00:00:00' AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemEndDate

    - displayName: SYSTEM_CREATE_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemCreateDate

    - displayName: SYSTEM_UPDATE_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemUpdateDate
name: Dimension
type: NodeType

{% set is_type_2 = columns | selectattr("isChangeTracking") | list | length > 0 %}

    {% for test in node.tests if config.testsEnabled %}
        {% if test.runOrder == 'Before' %}
            {{ test_stage(test.name, test.continueOnFailure) }}
            {{ test.templateString }}
        {% endif %}
    {% endfor %}

{% if node.materializationType == 'table' %}

	{% if config.preSQL %}			
		{{ stage('Pre-SQL') }}
		{{ config.preSQL }}
	{% endif %}
	
    {% if is_type_2 %}

        {% for source in sources %}
            {{ stage('MERGE ' + source.name | string) }}
            MERGE INTO {{ ref_no_link(node.location.name, node.name) }} "TGT"
            USING (
            /* New Rows That Don't Exist */
            SELECT
            {% for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemVersion %}
                    1
                {% elif col.isSystemCurrentFlag %}
                    'Y'
                {% else %}
                   {{ get_source_transform(col) }}
                {% endif %}
                AS "{{ col.name }}",
            {% endfor %}
                'INSERT_INITAL_VERSION_ROWS' AS "DML_OPERATION"
            {{ source.join }}
            LEFT JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                    {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                "DIM"."{{ col.name }}" IS NULL
            {% endfor %}
            UNION ALL
            /* New Row Needing To Be Inserted Due To Type-2 Column Changes */
            SELECT
            {% for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemVersion %}
                    "DIM"."{{ col.name }}" + 1
                {% elif col.isSystemCurrentFlag %}
                    'Y'
                {% else %}
                   {{ get_source_transform(col) }}
                {% endif %}
                AS "{{ col.name }}",
            {% endfor %}
                'INSERT_NEW_VERSION_ROWS' AS "DML_OPERATION"
            {{ source.join }}
            INNER JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE "DIM"."{{ get_value_by_column_attribute("isSystemCurrentFlag") }}" = 'Y'
            {% for col in source.columns if (col.isChangeTracking == true) %}
                {% if loop.first %}
                    AND (
                {% else %}
                    OR
                {% endif %}
                ( NVL( CAST({{ get_source_transform(col) }} as STRING), '**NULL**') <> NVL( CAST("DIM"."{{ col.name }}" as STRING), '**NULL**') )
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
            UNION ALL
            /* Rows Needing To Be Expired Due To Type-2 Column Changes
            In this case, only two columns are merged (version and end date) */
            SELECT
            {%- for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemEndDate %}
                    DATEADD(MILLISECONDS, -1, CAST(CURRENT_TIMESTAMP AS TIMESTAMP))
                {% elif col.isSystemCurrentFlag %}
                    'N'
                {% else %}
                    "DIM"."{{ col.name }}"
                {% endif %}
                AS "{{ col.name }}",
            {% endfor -%}
                'update_expired_version_rows' AS "DML_OPERATION"
            {{ source.join }}
            INNER JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE "DIM"."{{ get_value_by_column_attribute("isSystemCurrentFlag") }}" = 'Y'
            {% for col in source.columns if (col.isChangeTracking == true) %}
                {% if loop.first %}
                    AND (
                {% else %}
                    OR
                {% endif %}
                ( NVL( CAST({{ get_source_transform(col) }} as STRING), '**NULL**') <> NVL( CAST("DIM"."{{ col.name }}" as STRING), '**NULL**') )
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
            {# The if-block below avoids unnecessary updates when no type 2 column changes are present #}
            {% if source.columns 
                | rejectattr('isSurrogateKey')
                | rejectattr('isBusinessKey')
                | rejectattr('isChangeTracking')
                | rejectattr('isSystemVersion')
                | rejectattr('isSystemCurrentFlag')
                | rejectattr('isSystemStartDate')
                | rejectattr('isSystemEndDate')
                | rejectattr('isSystemCreateDate')
                | rejectattr('isSystemUpdateDate') 
                | list | length == 0 
            %}
                {# Skip Section #}
            {% else %}
              UNION ALL
              /* Rows Needing To Be Updated Due To Changes To Non-Type-2 columns
              This case merges only when there are changes in non-type-2 column updates, but no changes in type-2 columns*/
              SELECT
              {%- for col in source.columns if not col.isSurrogateKey %}
                  {% if col.isSystemVersion or col.isSystemCreateDate or col.isSystemStartDate or col.isSystemEndDate %}
                      "DIM"."{{ col.name }}"
                  {% elif col.isSystemCurrentFlag %}
                      'Y'
                  {% else %}
                      {{ get_source_transform(col) }}
                  {% endif %}
                  AS "{{ col.name }}",
              {% endfor -%}
                  'UPDATE_NON_TYPE2_ROWS' AS "DML_OPERATION"
              {{ source.join }}
              INNER JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
              {% for col in source.columns if col.isBusinessKey -%}
                  {% if not loop.first %}
                      AND
                  {% endif %}
                  {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
              {% endfor %}
              WHERE "DIM"."{{ get_value_by_column_attribute("isSystemCurrentFlag") }}" = 'Y'
              AND (
              {% for col in source.columns if (col.isChangeTracking) -%}
                  {% if not loop.first %}
                      AND
                  {% endif %}
                  {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
              {% endfor %} )
              {% for col in source.columns if not (   col.isBusinessKey or
                                                      col.isChangeTracking or
                                                      col.isSurrogateKey or
                                                      col.isSystemVersion or
                                                      col.isSystemCurrentFlag or
                                                      col.isSystemStartDate or
                                                      col.isSystemEndDate or
                                                      col.isSystemUpdateDate or
                                                      col.isSystemCreateDate) -%}
                  {% if loop.first %}
                      AND (
                  {% endif %}
                  {% if not loop.first %}
                      OR
                  {% endif %}
                  NVL( CAST({{ get_source_transform(col) }} as STRING), '**NULL**') <> NVL( CAST("DIM"."{{ col.name }}" as STRING), '**NULL**')
                  {% if loop.last %}
                      )
                  {% endif %}
              {% endfor %}
            {% endif %}
        ) AS "SRC"
        ON
        {% for col in source.columns if col.isBusinessKey -%}
            {% if not loop.first %}
                AND
            {% endif %}
            "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
        {% endfor %}
        AND "TGT"."{{ get_value_by_column_attribute("isSystemVersion") }}" = "SRC"."{{ get_value_by_column_attribute("isSystemVersion") }}"
        WHEN MATCHED THEN UPDATE SET
        {%- for col in source.columns if not (col.isBusinessKey or col.isSurrogateKey or col.isSystemCreateDate) %}
            "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
            {% if not loop.last %}, {% endif %}
        {% endfor -%}
        WHEN NOT MATCHED THEN INSERT (
        {%- for col in source.columns if not col.isSurrogateKey %}
            "{{ col.name }}"
            {% if not loop.last %}, {% endif %}
        {% endfor -%}
        )
        VALUES (
        {%- for col in source.columns if not col.isSurrogateKey %}
            "SRC"."{{ col.name }}"
            {% if not loop.last %}, {% endif %}
        {% endfor -%}
        )

    {% endfor %}



    {% else %}
        {% for source in sources %}
            {{ stage('MERGE ' + source.name | string ) }}
            MERGE INTO {{ ref_no_link(node.location.name, node.name) }} "TGT"
            USING (
                SELECT
                {% for col in source.columns if not col.isSurrogateKey %}
                    {% if col.isSystemVersion %}
                    	1
                    {% elif col.isSystemCurrentFlag %}
                    	'Y'
                    {% else %}
                        {{ get_source_transform(col) }}
                    {% endif %}
                    AS "{{ col.name }}"
                    {%- if not loop.last %}, {% endif %}
                {% endfor %}
                {{ source.join }})
                AS "SRC"
            ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                "SRC"."{{ col.name }}" = "TGT"."{{ col.name }}"
            {% endfor %}
            WHEN MATCHED
            {% for col in source.columns if not (   col.isBusinessKey or
                                                    col.isSurrogateKey or
                                                    col.isSystemVersion or
                                                    col.isSystemCurrentFlag or
                                                    col.isSystemStartDate or
                                                    col.isSystemEndDate or
                                                    col.isSystemUpdateDate or
                                                    col.isSystemCreateDate) %}
                {% if loop.first %}
                    AND (
                {% else %}
                    OR
                {% endif %}
                NVL( CAST("SRC"."{{ col.name }}" as STRING), '**NULL**') <> NVL( CAST("TGT"."{{ col.name }}" as STRING), '**NULL**')
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
            THEN UPDATE SET
            {%- for col in source.columns if not (  col.isBusinessKey or
                                                    col.isSurrogateKey or
                                                    col.isSystemVersion or
                                                    col.isSystemCurrentFlag or
                                                    col.isSystemStartDate or
                                                    col.isSystemEndDate or
                                                    col.isSystemCreateDate) %}
                    "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
                {% if not loop.last %}, {% endif %}
            {% endfor %}
            WHEN NOT MATCHED THEN
            INSERT (
            {%- for col in source.columns if not col.isSurrogateKey %}
                "{{ col.name }}"
                {% if not loop.last %}, {% endif %}
            {% endfor -%}
            )
            VALUES (
            {%- for col in source.columns if not col.isSurrogateKey %}
                "SRC"."{{ col.name }}"
                {% if not loop.last %}, {% endif %}
            {% endfor -%}
            )
        {% endfor %}
    {% endif %}
	
	{% if config.postSQL %}			
		{{ stage('Post-SQL') }}
		{{ config.postSQL }}
	{% endif %}
{% endif %}

{% if config.testsEnabled %}
	{% for test in node.tests %}
		{% if test.runOrder == 'After' %}
			{{ test_stage(test.name, test.continueOnFailure) }}
			{{ test.templateString }}
        {% endif %}
	{% endfor %}

	{% for column in columns %}
		{% for test in column.tests %}
			{{ test_stage(column.name + ": " + test.name) }}
			{{ test.templateString }}
		{% endfor %}
	{% endfor %}
{% endif %}
Fact-Fact

    {% if node.materializationType == 'table' %}
        {{ stage('Create Fact Table') }}
    
        CREATE OR REPLACE TABLE {{ ref_no_link(node.location.name, node.name) }}
        (
            {% for col in columns %}
                "{{ col.name }}" {{ col.dataType }}
                {%- if not col.nullable %} NOT NULL
                    {%- if col.defaultValue | length > 0 %} DEFAULT {{ col.defaultValue }}{% endif %}
                {% endif %}
                {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
                {%- if not loop.last -%}, {% endif %}
            {% endfor %}
        )
        {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
    
    
    {% elif node.materializationType == 'view' %}
        {{ stage('Create Fact View') }}
    
        CREATE OR REPLACE VIEW {{ ref_no_link(node.location.name, node.name) }}
        (
            {% for col in columns %}
                "{{ col.name }}"
                {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
                {%- if not loop.last -%},{% endif %}
            {% endfor %}
        )
        {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
        AS
        {% for source in sources %}
    
            {% if loop.first %}SELECT {% endif %}
    
            {% for col in source.columns %}
                {{ get_source_transform(col) }} AS "{{ col.name }}"
                {%- if not loop.last -%}, {% endif %}
            {% endfor %}
            {{ source.join }}
    
            {% if not loop.last %} UNION ALL {% endif %}
        {% endfor %}
    
    {% endif %}
    
fileVersion: 1
id: Fact
isDisabled: false
metadata:
  defaultStorageLocation: null
  error: null
  nodeMetadataSpec: |
    capitalized: Fact
    plural: Facts
    short: FCT
    tagColor: '#D9A438'

    config:
    - groupName: Options
      items:
      - type: materializationSelector
        isRequired: true
        options:
        - table
        - view
      
      - type: businessKeyColumns
        isRequired: false

      - displayName: Enable Tests
        attributeName: testsEnabled
        type: toggleButton
        default: true
        
      - displayName: Pre-SQL
        attributeName: preSQL
        type: textBox
        syntax: sql
        isRequired: false

      - displayName: Post-SQL
        attributeName: postSQL
        type: textBox
        syntax: sql
        isRequired: false

    systemColumns:
    - displayName: SYSTEM_CREATE_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemCreateDate

    - displayName: SYSTEM_UPDATE_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemUpdateDate
name: Fact
type: NodeType

    {% for test in node.tests if config.testsEnabled %}
        {% if test.runOrder == 'Before' %}
            {{ test_stage(test.name, test.continueOnFailure) }}
            {{ test.templateString }}
        {% endif %}
    {% endfor %}

    {% if node.materializationType == 'table' %}
        {% if config.preSQL %}
            {{ stage('Pre-SQL') }}
            {{ config.preSQL }}
        {% endif %}
        
        {% set has_business_key = columns | selectattr("isBusinessKey") | list | length > 0 %}
        
        {% for source in sources %}
        
            {% if has_business_key %}
        
                {{ stage('MERGE ' + source.name | string ) }}
                MERGE INTO {{ ref_no_link(node.location.name, node.name) }} "TGT"
                USING (
                    SELECT
                    {% for col in source.columns %}
                        {{ get_source_transform(col) }} AS "{{ col.name }}"
                        {%- if not loop.last %}, {% endif %}
                    {% endfor %}
                    {{ source.join }})
                    AS "SRC"
                ON
                {% for col in source.columns if col.isBusinessKey -%}
                    {% if not loop.first %}
                        AND
                    {% endif %}
                    "SRC"."{{ col.name }}" = "TGT"."{{ col.name }}"
                {% endfor %}
                WHEN MATCHED
                {% for col in source.columns if not (   col.isBusinessKey or
                                                        col.isSystemUpdateDate or
                                                        col.isSystemCreateDate) %}
                    {% if loop.first %}
                        AND (
                    {% else %}
                        OR
                    {% endif %}
                    NVL( CAST("SRC"."{{ col.name }}" as STRING), '**NULL**') <> NVL( CAST("TGT"."{{ col.name }}" as STRING), '**NULL**')
                    {% if loop.last %}
                        )
                    {% endif %}
                {% endfor %}
                THEN UPDATE SET
                {%- for col in source.columns if not (col.isBusinessKey or col.isSystemCreateDate) %}
                        "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
                    {% if not loop.last %}, {% endif %}
                {% endfor %}
                WHEN NOT MATCHED THEN
                INSERT (
                {%- for col in source.columns if not col.isSurrogateKey %}
                    "{{ col.name }}"
                    {% if not loop.last %}, {% endif %}
                {% endfor -%}
                )
                VALUES (
                {%- for col in source.columns if not col.isSurrogateKey %}
                    "SRC"."{{ col.name }}"
                    {% if not loop.last %}, {% endif %}
                {% endfor -%}
                )
        
            {% else %}
        
                {{ stage('Insert ' + source.name | string ) }}
        
                    INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
                    (
                        {% for col in source.columns %}
                            "{{ col.name }}"
                            {%- if not loop.last -%},{% endif %}
                        {% endfor %}
                    )
        
                    SELECT
                    {% for col in source.columns %}
                        {{ get_source_transform(col) }} AS "{{ col.name }}"
                        {%- if not loop.last -%}, {% endif %}
                    {% endfor %}
                    {{ source.join }}
            {% endif %}
        {% endfor %}
        {% if config.postSQL %}
            {{ stage('Post-SQL') }}
            {{ config.postSQL }}
        {% endif %}
    {% endif %}

    {% if config.testsEnabled %}
        {% for test in node.tests %}
            {% if test.runOrder == 'After' %}
                {{ test_stage(test.name, test.continueOnFailure) }}
                {{ test.templateString }}
            {% endif %}
        {% endfor %}

        {% for column in columns %}
            {% for test in column.tests %}
                {{ test_stage(column.name + ": " + test.name) }}
                {{ test.templateString }}
            {% endfor %}
        {% endfor %}
    {% endif %}
    
    

PersistentStage-persistentStage
{% if node.materializationType == 'table' %}
    {{ stage('Create Persistent Stage Table') }}

    CREATE OR REPLACE TABLE {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}" {{ col.dataType }}
            {% if col.isSurrogateKey %}
		        identity
            {% endif %}
            {%- if not col.nullable %} NOT NULL
                {%- if col.defaultValue | length > 0 %} DEFAULT {{ col.defaultValue }}{% endif %}
            {% endif %}
            {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
    )
    {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}


{% elif node.materializationType == 'view' %}
    {{ stage('Create Persistent Stage View') }}

    CREATE OR REPLACE VIEW {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}"
            {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
            {%- if not loop.last -%},{% endif %}
        {% endfor %}
    )
    {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
    AS
    {% for source in sources %}

		{% if not loop.last %} UNION ALL {% endif %}
	{% endfor %}

        {% for col in source.columns %}
            {% if col.isSurrogateKey or col.isSystemUpdateDate or col.isSystemCreateDate %}
                NULL
            {% else %}
                {{ get_source_transform(col) }}
            {% endif %}
            AS "{{ col.name }}"
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
        {{ source.join }}

{% endif %}
fileVersion: 1
id: persistentStage
isDisabled: false
metadata:
  defaultStorageLocation: null
  error: null
  nodeMetadataSpec: |
    capitalized: Persistent Stage
    short: PSTG
    plural: Persistent Stages
    tagColor: '#29B2DB'
        
    config:
    - groupName: Options
      items:
      - type: materializationSelector
        isRequired: true
        default: table
        options:
        - table
        - view
      
      - type: businessKeyColumns
        isRequired: false

      - type: multisourceToggle
        enableIf: "{% if node.materializationType == 'table' %} true {% else %} false {% endif %}"

      - type: overrideSQLToggle
        enableIf: "{% if node.materializationType == 'view' %} true {% else %} false {% endif %}"

      - displayName: Enable Tests
        attributeName: testsEnabled
        type: toggleButton
        default: true
        
      - displayName: Pre-SQL
        attributeName: preSQL
        type: textBox
        syntax: sql
        isRequired: false

      - displayName: Post-SQL
        attributeName: postSQL
        type: textBox
        syntax: sql
        isRequired: false

    systemColumns:

    - displayName: '{{NODE_NAME}}_KEY'
      transform: ''
      dataType: NUMBER
      placement: beginning
      attributeName: isSurrogateKey

    - displayName: SYSTEM_VERSION
      transform: ''
      dataType: NUMBER
      placement: end
      attributeName: isSystemVersion

    - displayName: SYSTEM_CURRENT_FLAG
      transform: ''
      dataType: VARCHAR
      placement: end
      attributeName: isSystemCurrentFlag

    - displayName: SYSTEM_START_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemStartDate

    - displayName: SYSTEM_END_DATE
      transform: CAST('2999-12-31 00:00:00' AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemEndDate

    - displayName: SYSTEM_CREATE_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemCreateDate

    - displayName: SYSTEM_UPDATE_DATE
      transform: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
      dataType: TIMESTAMP
      placement: end
      attributeName: isSystemUpdateDate
name: Persistent Stage
type: NodeType

{% set has_business_key = columns | selectattr("isBusinessKey") | list | length > 0 %}
{% set is_type_2 = columns | selectattr("isChangeTracking") | list | length > 0 %}

    {% for test in node.tests if config.testsEnabled %}
        {% if test.runOrder == 'Before' %}
            {{ test_stage(test.name, test.continueOnFailure) }}
            {{ test.templateString }}
        {% endif %}
    {% endfor %}

{% if node.materializationType == 'table' %}
	{% if config.preSQL %}
		{{ stage('Pre-SQL') }}
		{{ config.preSQL }}
	{% endif %}
	
    {% if has_business_key and is_type_2 %}

        {% for source in sources %}
            {{ stage('MERGE ' + source.name | string) }}
            MERGE INTO {{ ref_no_link(node.location.name, node.name) }} "TGT"
            USING (
            /* New Rows That Don't Exist */
            SELECT
            {% for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemVersion %}
                    1
                {% elif col.isSystemCurrentFlag %}
                    'Y'
                {% else %}
                   {{ get_source_transform(col) }}
                {% endif %}
                AS "{{ col.name }}",
            {% endfor %}
                'INSERT_INITAL_VERSION_ROWS' AS "DML_OPERATION"
            {{ source.join }}
            LEFT JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                    {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                "DIM"."{{ col.name }}" IS NULL
            {% endfor %}
            UNION ALL
            /* New Row Needing To Be Inserted Due To Type-2 Column Changes */
            SELECT
            {% for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemVersion %}
                    "DIM"."{{ col.name }}" + 1
                {% elif col.isSystemCurrentFlag %}
                    'Y'
                {% else %}
                   {{ get_source_transform(col) }}
                {% endif %}
                AS "{{ col.name }}",
            {% endfor %}
                'INSERT_NEW_VERSION_ROWS' AS "DML_OPERATION"
            {{ source.join }}
            INNER JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE "DIM"."{{ get_value_by_column_attribute("isSystemCurrentFlag") }}" = 'Y'
            {% for col in source.columns if (col.isChangeTracking == true) %}
                {% if loop.first %}
                    AND (
                {% else %}
                    OR
                {% endif %}
                ( NVL( CAST({{ get_source_transform(col) }} as STRING), '**NULL**') <> NVL( CAST("DIM"."{{ col.name }}" as STRING), '**NULL**') )
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
            UNION ALL
            /* Rows Needing To Be Expired Due To Type-2 Column Changes
            In this case, only two columns are merged (version and end date) */
            SELECT
            {%- for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemEndDate %}
                    DATEADD(MILLISECONDS, -1, CAST(CURRENT_TIMESTAMP AS TIMESTAMP))
                {% elif col.isSystemCurrentFlag %}
                    'N'
                {% else %}
                    "DIM"."{{ col.name }}"
                {% endif %}
                AS "{{ col.name }}",
            {% endfor -%}
                'update_expired_version_rows' AS "DML_OPERATION"
            {{ source.join }}
            INNER JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE "DIM"."{{ get_value_by_column_attribute("isSystemCurrentFlag") }}" = 'Y'
            {% for col in source.columns if (col.isChangeTracking == true) %}
                {% if loop.first %}
                    AND (
                {% else %}
                    OR
                {% endif %}
                ( NVL( CAST({{ get_source_transform(col) }} as STRING), '**NULL**') <> NVL( CAST("DIM"."{{ col.name }}" as STRING), '**NULL**') )
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
            UNION ALL
            /* Rows Needing To Be Updated Due To Changes To Non-Type-2 source.columns
            This case merges only when there are changes in non-type-2 column updates, but no changes in type-2 columns*/
            SELECT
            {%- for col in source.columns if not col.isSurrogateKey %}
                {% if col.isSystemVersion or col.isSystemCreateDate or col.isSystemStartDate or col.isSystemEndDate %}
                    "DIM"."{{ col.name }}"
                {% elif col.isSystemCurrentFlag %}
                    'Y'
                {% else %}
                    {{ get_source_transform(col) }}
                {% endif %}
                AS "{{ col.name }}",
            {% endfor -%}
                'UPDATE_NON_TYPE2_ROWS' AS "DML_OPERATION"
            {{ source.join }}
            INNER JOIN {{ ref_no_link(node.location.name, node.name) }} "DIM" ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %}
            WHERE "DIM"."{{ get_value_by_column_attribute("isSystemCurrentFlag") }}" = 'Y'
            AND (
            {% for col in source.columns if (col.isChangeTracking) -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                {{ get_source_transform(col) }} = "DIM"."{{ col.name }}"
            {% endfor %} )
            {% for col in source.columns if not (   col.isBusinessKey or
                                                    col.isChangeTracking or
                                                    col.isSurrogateKey or
                                                    col.isSystemVersion or
                                                    col.isSystemCurrentFlag or
                                                    col.isSystemStartDate or
                                                    col.isSystemEndDate or
                                                    col.isSystemUpdateDate or
                                                    col.isSystemCreateDate) -%}
                {% if loop.first %}
                    AND (
                {% endif %}
                {% if not loop.first %}
                    OR
                {% endif %}
                NVL( CAST({{ get_source_transform(col) }} as STRING), '**NULL**') <> NVL( CAST("DIM"."{{ col.name }}" as STRING), '**NULL**')
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
        ) AS "SRC"
        ON
        {% for col in source.columns if col.isBusinessKey -%}
            {% if not loop.first %}
                AND
            {% endif %}
            "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
        {% endfor %}
        AND "TGT"."{{ get_value_by_column_attribute("isSystemVersion") }}" = "SRC"."{{ get_value_by_column_attribute("isSystemVersion") }}"
        WHEN MATCHED THEN UPDATE SET
        {%- for col in source.columns if not (col.isBusinessKey or col.isSurrogateKey or col.isSystemCreateDate) %}
            "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
            {% if not loop.last %}, {% endif %}
        {% endfor -%}
        WHEN NOT MATCHED THEN INSERT (
        {%- for col in source.columns if not col.isSurrogateKey %}
            "{{ col.name }}"
            {% if not loop.last %}, {% endif %}
        {% endfor -%}
        )
        VALUES (
        {%- for col in source.columns if not col.isSurrogateKey %}
            "SRC"."{{ col.name }}"
            {% if not loop.last %}, {% endif %}
        {% endfor -%}
        )

    {% endfor %}

    {% elif has_business_key and not is_type_2 %}
        {% for source in sources %}
            {{ stage('MERGE ' + source.name | string ) }}
            MERGE INTO {{ ref_no_link(node.location.name, node.name) }} "TGT"
            USING (
                SELECT
                {% for col in source.columns if not col.isSurrogateKey %}
                    {% if col.isSystemVersion %}
                    	1
                    {% elif col.isSystemCurrentFlag %}
                    	'Y'
                    {% else %}
                        {{ get_source_transform(col) }}
                    {% endif %}
                    AS "{{ col.name }}"
                    {%- if not loop.last %}, {% endif %}
                {% endfor %}
                {{ source.join }})
                AS "SRC"
            ON
            {% for col in source.columns if col.isBusinessKey -%}
                {% if not loop.first %}
                    AND
                {% endif %}
                "SRC"."{{ col.name }}" = "TGT"."{{ col.name }}"
            {% endfor %}
            WHEN MATCHED
            {% for col in source.columns if not (   col.isBusinessKey or
                                                    col.isSurrogateKey or
                                                    col.isSystemVersion or
                                                    col.isSystemCurrentFlag or
                                                    col.isSystemStartDate or
                                                    col.isSystemEndDate or
                                                    col.isSystemUpdateDate or
                                                    col.isSystemCreateDate) %}
                {% if loop.first %}
                    AND (
                {% else %}
                    OR
                {% endif %}
                NVL( CAST("SRC"."{{ col.name }}" as STRING), '**NULL**') <> NVL( CAST("TGT"."{{ col.name }}" as STRING), '**NULL**')
                {% if loop.last %}
                    )
                {% endif %}
            {% endfor %}
            THEN UPDATE SET
            {%- for col in source.columns if not (  col.isBusinessKey or
                                                    col.isSurrogateKey or
                                                    col.isSystemVersion or
                                                    col.isSystemCurrentFlag or
                                                    col.isSystemStartDate or
                                                    col.isSystemEndDate or
                                                    col.isSystemCreateDate) %}
                    "TGT"."{{ col.name }}" = "SRC"."{{ col.name }}"
                {% if not loop.last %}, {% endif %}
            {% endfor %}
            WHEN NOT MATCHED THEN
            INSERT (
            {%- for col in source.columns if not col.isSurrogateKey %}
                "{{ col.name }}"
                {% if not loop.last %}, {% endif %}
            {% endfor -%}
            )
            VALUES (
            {%- for col in source.columns if not col.isSurrogateKey %}
                "SRC"."{{ col.name }}"
                {% if not loop.last %}, {% endif %}
            {% endfor -%}
            )
        {% endfor %}
    {% else %}
        {% for source in sources %}
            {{ stage('Insert ' + source.name | string ) }}
            INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
            (
                {% for col in source.columns if not col.isSurrogateKey %}
                    "{{ col.name }}"
                    {%- if not loop.last -%},{% endif %}
                {% endfor %}
            )

            SELECT
            {% for col in source.columns if not col.isSurrogateKey %}

                {% if col.isSystemVersion %}
                    1
                {% elif col.isSystemCurrentFlag %}
                    'Y'
                {% else %}
                    {{ get_source_transform(col) }}
                {% endif %}
                AS "{{ col.name }}"
                {%- if not loop.last -%}, {% endif %}
                
            {% endfor %}
            {{ source.join }}
        {% endfor %}            
    {% endif %}
	
	{% if config.postSQL %}
		{{ stage('Post-SQL') }}
		{{ config.postSQL }}
	{% endif %}
	
{% endif %}

{% if config.testsEnabled %}
	{% for test in node.tests %}
		{% if test.runOrder == 'After' %}
			{{ test_stage(test.name, test.continueOnFailure) }}
			{{ test.templateString }}
        {% endif %}
	{% endfor %}

	{% for column in columns %}
		{% for test in column.tests %}
			{{ test_stage(column.name + ": " + test.name) }}
			{{ test.templateString }}
		{% endfor %}
	{% endfor %}
{% endif %}
Stage-Stage
{% if node.override.create.enabled %}
	
	{{ node.override.create.script }}

{% elif node.materializationType == 'table' %}
	{{ stage('Create Stage Table') }}

	CREATE OR REPLACE TABLE {{ ref_no_link(node.location.name, node.name) }}
	(
		{% for col in columns %}
			"{{ col.name }}" {{ col.dataType }}
			{%- if not col.nullable %} NOT NULL
				{%- if col.defaultValue | length > 0 %} DEFAULT {{ col.defaultValue }}{% endif %}
			{% endif %}
			{%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
			{%- if not loop.last -%}, {% endif %}
		{% endfor %}
	)
	{%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}


{% elif node.materializationType == 'view' %}
    {{ stage('Create Stage View') }}

    CREATE OR REPLACE VIEW {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}"
            {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
    )
    {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
    AS
    {% for source in sources %}
        SELECT
        {% for col in source.columns %}
            {{ get_source_transform(col) }} AS "{{ col.name }}"
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}

        {{ source.join }}

        {% if not loop.last %}
            {% if config.insertStrategy in ['UNION', 'UNION ALL'] %}
                {{ config.insertStrategy }}
            {% else %}
                UNION
            {% endif %}
        {% endif %}
    {% endfor %}

{% endif %}
fileVersion: 1
id: Stage
isDisabled: false
metadata:
  defaultStorageLocation: null
  error: null
  nodeMetadataSpec: |
    capitalized: Stage
    short: STG
    plural: Stages
    tagColor: '#2EB67D'

    config:
    - groupName: Options
      items:
      - type: materializationSelector
        default: table
        options:
        - table
        - view
        isRequired: true

      - type: multisourceToggle
        enableIf: "{% if node.materializationType == 'table' %} true {% else %} false {% endif %}"

      - type: overrideSQLToggle
        enableIf: "{% if node.materializationType == 'view' %} true {% else %} false {% endif %}"

      - displayName: Multi Source Strategy
        attributeName: insertStrategy
        type: dropdownSelector
        default: INSERT
        options:
        - "INSERT"
        - "UNION"
        - "UNION ALL"
        isRequired: true
        enableIf: "{% if node.isMultisource %} true {% else %} false {% endif %}"

      - displayName: Truncate Before
        attributeName: truncateBefore
        type: toggleButton
        default: true

      - displayName: Enable Tests
        attributeName: testsEnabled
        type: toggleButton
        default: true

      - displayName: Pre-SQL
        attributeName: preSQL
        type: textBox
        syntax: sql
        isRequired: false

      - displayName: Post-SQL
        attributeName: postSQL
        type: textBox
        syntax: sql
        isRequired: false
name: Stage
type: NodeType

    {% for test in node.tests if config.testsEnabled %}
        {% if test.runOrder == 'Before' %}
            {{ test_stage(test.name, test.continueOnFailure) }}
            {{ test.templateString }}
        {% endif %}
    {% endfor %}

{% if node.materializationType == 'table' %}
	{% if config.preSQL %}
		{{ stage('Pre-SQL') }}
		{{ config.preSQL }}
	{% endif %}
	
	
	
		{% if config.truncateBefore %}
	
			{{ stage('Truncate Stage Table') }}
			TRUNCATE IF EXISTS {{ ref_no_link(node.location.name, node.name) }}
	
		{% endif %}
	
	
		{% if config.insertStrategy in ['UNION', 'UNION ALL'] %}
			{{ stage( config.insertStrategy + ' Sources' | string ) }}
			INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
				(
					{% for col in columns %}
						"{{ col.name }}"
						{%- if not loop.last -%},{% endif %}
					{% endfor %}
				)
		{% endif %}
	
	
		{% for source in sources %}
	
			{% if config.insertStrategy == 'INSERT' %}
				{{ stage('Insert ' + source.name | string ) }}
	
				INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
				(
					{% for col in source.columns %}
						"{{ col.name }}"
						{%- if not loop.last -%},{% endif %}
					{% endfor %}
				)
			{% endif %}
	
			SELECT
			{% for col in source.columns %}
                {{ get_source_transform(col) }} AS "{{ col.name }}"
				{%- if not loop.last -%}, {% endif %}
			{% endfor %}
	
			{{ source.join }}
	
			{% if config.insertStrategy in ['UNION', 'UNION ALL'] and not loop.last %}
				{{config.insertStrategy}}
			{% endif %}
	
		{% endfor %}
	
	{% if config.postSQL %}
		{{ stage('Post-SQL') }}
		{{ config.postSQL }}
	{% endif %}
{% endif %}

{% if config.testsEnabled %}
	{% for test in node.tests %}
		{% if test.runOrder == 'After' %}
			{{ test_stage(test.name, test.continueOnFailure) }}
			{{ test.templateString }}
        {% endif %}
	{% endfor %}

	{% for column in columns %}
		{% for test in column.tests %}
			{{ test_stage(column.name + ": " + test.name) }}
			{{ test.templateString }}
		{% endfor %}
	{% endfor %}
{% endif %}
View-View
{% if node.override.create.enabled %}
    
    {{ node.override.create.script }}

{% else %}
    {{ stage('Create View') }}
    CREATE OR REPLACE VIEW {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}"
            {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
    )
    {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
    AS
    {% for source in sources %}
        SELECT {% if config.selectDistinct %} DISTINCT {% endif %}
        {% for col in source.columns %}
            {{ get_source_transform(col) }} AS "{{ col.name }}"
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}

        {{ source.join }}

        {% if not loop.last %}
            {% if config.insertStrategy in ['UNION', 'UNION ALL'] %}
                {{ config.insertStrategy }}
            {% else %}
                UNION
            {% endif %}
        {% endif %}
    {% endfor %}
{% endif %}
fileVersion: 1
id: View
isDisabled: true
metadata:
  defaultStorageLocation: null
  error: null
  nodeMetadataSpec: |
    capitalized: View
    short: V
    tagColor: '#C4C4C4'
    isDisabled: true
    plural: Views

    config:
      - groupName: Options
        items: 
        - type: materializationSelector
          options:
            - view
          default: view
          isRequired: true

        - type: toggleButton
          attributeName: selectDistinct
          displayName: Distinct

        - type: multisourceToggle

        - type: overrideSQLToggle

        - displayName: Multi Source Strategy
          attributeName: insertStrategy
          type: dropdownSelector
          default: UNION
          options:
          - "UNION"
          - "UNION ALL" 
          isRequired: true
          enableIf: "{% if node.isMultisource %} true {% else %} false {% endif %}"
name: View
type: NodeType
empty

Packages

Package contents can vary. Learn more about Coalesce Packages.

Packages Example
config:
  entities:
    nodeTypes:
      "25":
        defaultStorageLocation: null
        isDisabled: false
      "35":
        defaultStorageLocation: null
        isDisabled: false
      "36":
        defaultStorageLocation: null
        isDisabled: false
      "37":
        defaultStorageLocation: null
        isDisabled: false
      "38":
        defaultStorageLocation: null
        isDisabled: false
  packageVariables: |-
    {%- set demopkg4coalesce = namespace(
               config = {
               "ldts_alias": "LDTS",
               "rsrc_alias": "RSRC", 
               "ledts_alias": "LEDTS",
               "stg_alias": "STG", 
               "snapshot_trigger_column": "IS_ACTIVE",
               "use_object_name_prefix": TRUE,
               "sdts_alias": "SDTS",
               "is_current_col_alias": "IS_CURRENT", 
               "hash": "MD5", 
               "hash_datatype": "STRING", 
               "hash_input_case_sensitive": "TRUE",
               "hash_passthrough_input_transformations": "TRUE",
               "beginning_of_all_times": "0001-01-01T00:00:01",
               "end_of_all_times": "8888-12-31T23:59:59",
               "timestamp_format": "YYYY-MM-DDTHH24:MI:SS", 
               "default_unknown_rsrc": "SYSTEM", 
               "default_error_rsrc": "ERROR", 
               "rsrc_default_dtype": "STRING", 
               "stg_default_dtype": "STRING", 
               "error_value__STRING": "'(error)'", 
               "error_value_alt__STRING": "'e'", 
               "unknown_value__STRING": "'(unknown)'",
               "unknown_value_alt__STRING": "'u'"
               }
          ) -%}
fileVersion: 1
id: "@coalesce/support-demo-pkg"
name: secondPackage
packageID: "@coalesce/support-demo-pkg"
releaseID: d1ea23de-1b86-478d-916a-d8bc9f06ba41
type: Package