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)
  • Node Types (folder)
  • Nodes (folder), as .yml for Nodes on V1 node types and .sql for Nodes on V2 node types
  • 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 is stored as its own file under the Nodes folder. Nodes on V1 node types use .yml. Nodes on Node Type V2 use .sql (SQL-first authoring with annotations). V1 and V2 Nodes can live in the same repository.

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
V2 Node Types
@id("ea59b792-01e9-42a2-98c8-f760ce8caa76")
@nodeType("d75202b8-eefb-4aa5-a200-3e42276bb5e4")
@materializationType("view")
SELECT
    "O_ORDERKEY" AS "O_ORDERKEY",
    "O_CUSTKEY" AS "O_CUSTKEY",
    "O_ORDERSTATUS" AS "O_ORDERSTATUS",
    "O_TOTALPRICE" AS "O_TOTALPRICE",
    "O_ORDERDATE" AS "O_ORDERDATE",
    "O_ORDERPRIORITY" AS "O_ORDERPRIORITY",
    "O_CLERK" AS "O_CLERK",
    "O_SHIPPRIORITY" AS "O_SHIPPRIORITY",
    "O_COMMENT" AS "O_COMMENT"
FROM {{ ref('SOURCE', 'ORDERS') }} "ORDERS"

Node Types​

Node type definitions are stored under the Node Types folder in the repository for both V1 and V2 node types. The expandable examples in this section show V1 structure: definition YAML that includes a Config section, plus Jinja templates (Create, Run, Join, and related files). V2 node types use the same kinds of template files, but the type definition sets version: 2 and follows the V2 spec (no Config items in the definition YAML). For how V2 authoring and Node files differ from V1, see Node Type V2.

Coalesce-managed built-in templates cannot be changed arbitrarily. If a disallowed change is detected, Coalesce automatically attempts to revert it on the next commit. You can duplicate managed types and use them as the basis for 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