What Gets Committed
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
- locations.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)
- Source node example
- Dimension Node Example
- Fact Node Example
- Stage Node Example
- Persistent Node Example
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.
Fact-Fact
- create.sql.j2
- definition
- run.sql.j2
{% 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 %}
Dimension-Dimension
- create.sql.j2
- definition
- run.sql.j2
{% 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 %}
PersistentStage-persistentStage
- create.sql.j2
- definition
- run.sql.j2
{% 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
- create.sql.j2
- definition
- run.sql.j2
{% 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
- create.sql.j2
- definition
- run.sql.j2
{% 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