Skip to main content

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)
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

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
{% 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 %}
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 %}
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 %}
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 %}
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