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​

For how Subgraphs behave in the Build Interface, how they map to Jobs, and what gets stored in Git, see Using Subgraphs to Build Your Pipeline.

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: fa3ae643-55c3-4cd8-a860-851c13d5b240
name: SUPPLIER
operation:
  database: ""
  dataset: ""
  deployEnabled: true
  description: Supplier data as defined by TPC-H
  locationName: SOURCE
  metadata:
    columns:
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: c2595953-a53d-43b1-bb67-f43c99b11a7e
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: NUMBER(38,0)
        defaultValue: ""
        description: ""
        name: S_SUPPKEY
        nullable: false
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 0e83d34f-616e-4ef9-a68f-27335ae9becf
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: VARCHAR(25)
        defaultValue: ""
        description: ""
        name: S_NAME
        nullable: false
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 52436bcb-fc31-4085-b45c-b46c3c477e9c
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: VARCHAR(40)
        defaultValue: ""
        description: ""
        name: S_ADDRESS
        nullable: false
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: a5a8b8c9-d145-4246-8db3-2afbb70779b7
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: NUMBER(38,0)
        defaultValue: ""
        description: ""
        name: S_NATIONKEY
        nullable: false
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 5b1f4abf-f2b0-4eac-ac28-6628b98c84ff
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: VARCHAR(15)
        defaultValue: ""
        description: ""
        name: S_PHONE
        nullable: false
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 7a62e69a-da5a-4311-b81c-202e300ad389
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: NUMBER(12,2)
        defaultValue: ""
        description: ""
        name: S_ACCTBAL
        nullable: false
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
      - acceptedValues:
          strictMatch: true
          values: []
        appliedColumnTests: {}
        columnReference:
          columnCounter: 200972c1-d133-47bc-8fac-f9ea4d07cb1a
          stepCounter: fa3ae643-55c3-4cd8-a860-851c13d5b240
        config: {}
        dataType: VARCHAR(101)
        defaultValue: ""
        description: ""
        name: S_COMMENT
        nullable: true
        primaryKey: false
        sourceColumnReferences:
          - columnReferences: []
            transform: ""
        transform: ""
        uniqueKey: false
    join:
      joinCondition: FROM {{ ref('SOURCE', 'SUPPLIER') }}
  name: SUPPLIER
  schema: ""
  sqlType: Source
  table: SUPPLIER
  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