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

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

data.yml​

defaultStorageMapping: SAMPLE
fileVersion: 3

locations.yml​

A list of storage locations and the default location.

defaultStorageMapping: SAMPLE
fileVersion: 1
locations:
- HELLO
- SAMPLE
- WORK