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