Skip to main content

Documentation index: llms.txt. This page is also available as markdown: append .md to this URL or send Accept: text/markdown.

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 YAML node types and .sql for Nodes on SQL node types
  • Packages (folder)
  • data.yml
  • locations.yml

Repository Layout​

Coalesce manages a fixed set of files and folders at the root of your repository. Whether you build in the web app or in Coalesce Desktop, where your AI agent makes changes through the Coalesce CLI, make sure every change to the repository follows the rules in this section.

Reserved Files​

FileWhat it holds
data.ymlWorkspace settings, such as the file format version and the warehouse platform. Required.
locations.ymlYour Storage Locations and their default database and schema. Required.
packagesV1.ymlOlder package format. Kept for compatibility. Leave it in place if it exists.
workspace.ymlYour personal Storage Mappings and the profile that Coalesce Desktop and coa use on your machine. Local only. Keep it out of Git. See Bind a Workspace to a Profile.

Reserved Folders​

FolderWhat it holdsFile name pattern
nodes/One file per Node<LOCATION>-<NODE_NAME>.yml or .sql
nodeTypes/One folder per Node Type<NODE_TYPE_NAME>-<ID>/ containing definition.yml, create.sql.j2, and run.sql.j2
subgraphs/Subgraph definitions<NAME>-<ID>.yml
jobs/Job definitions<NAME>-<ID>.yml
macros/Macro definitions<NAME>-<ID>.yml
environments/Storage Mappings for each Environment<NAME>-<ID>.yml
packages/Installed packages<PACKAGE_NAME>.yml

Rules for Reserved Paths​

  1. Keep reserved folders at the repository root. Coalesce looks for nodes/, jobs/, and the other reserved folders only at the top level of the repository.
  2. Don't add arbitrary .yml or .sql files to reserved folders. Coalesce expects every .yml file in a reserved folder, plus .sql files in nodes/ and .sql.j2 files in nodeTypes/, to hold a definition in the format for that folder: a Node in nodes/, a Node Type in nodeTypes/, a Job in jobs/, and so on. Authoring your own Nodes, Jobs, and other objects as files is encouraged, by hand or with your AI agent. A file that isn't a valid definition, such as a jobs/notes.yml file of personal notes, is removed the next time you commit from the web app.
  3. Don't create subfolders inside reserved folders. Every file goes directly in its folder, for example nodes/STG-ORDERS.yml or jobs/Daily_Load-12.yml. When you commit from the web app, Coalesce moves files out of subfolders and back to the top of the folder. Coalesce Desktop skips files in subfolders. The one exception is nodeTypes/, which holds exactly one folder per Node Type, such as nodeTypes/Stage-4/definition.yml. Don't nest anything deeper than that. To group Nodes without subfolders, see Organize Nodes by Subject Area.
  4. Let Coalesce name files for you. File names come from the object's name, its Storage Location for Nodes, or its ID. Coalesce keeps only letters, numbers, underscores, and dashes. Renaming a Node, Job, or other object in Coalesce renames its file. Renaming the file by hand doesn't rename the object.
  5. Node names must be unique within a Storage Location. STG-CUSTOMER.yml and STG-CUSTOMER.sql can't both exist, because both define the Node CUSTOMER in the STG location.
  6. Node file names are case-sensitive, and Coalesce splits them at the first dash. Everything before the first dash is the Storage Location and everything after it is the Node name.

Organize Nodes by Subject Area​

Every Node file must sit directly inside the nodes/ folder. Subfolders such as nodes/finance/ or nodes/bronze/ aren't supported.

Subfolders in nodes/ Aren't Supported
  • The web app moves them back. When you commit from a Workspace in the web app, Coalesce rewrites every Node file to its standard path, nodes/<LOCATION>-<NODE_NAME>.yml or .sql. Files you moved into subfolders are removed from those subfolders and written back to the top of nodes/.
  • Coalesce Desktop skips them. Coalesce Desktop and your AI agent work through the coa CLI, which reads only the files directly in nodes/. A Node file in a subfolder is skipped without an error, so the Node seems to disappear from your pipeline.

To group Nodes by team, subject area, or layer, use one of these approaches instead:

  • Use Subgraphs. Group related Nodes inside Coalesce, such as one Subgraph for Finance and one for Marketing. Subgraphs are saved in the subgraphs/ folder and don't change where Node files live. See Using Subgraphs to Build Your Pipeline.
  • Name Storage Locations after subject areas. If each area has its own schema, name the locations to match, such as FINANCE and MARKETING. Every Node file name starts with its Storage Location, so files from the same area sort together in nodes/. For example, FINANCE-INVOICES.yml sits next to FINANCE-PAYMENTS.yml. See Storage Locations and Storage Mappings.

Files You Can Add​

Coalesce ignores anything outside the reserved paths and never changes it. This includes a README.md, CI settings such as .github/workflows/, scripts, and documentation folders. Coalesce also ignores files inside reserved folders that don't match its file types, such as nodes/README.md.

Local Development Files​

The .coa/ folder holds reports generated by coa. The web app ignores it and workspace.yml. Keep both out of Git so each person on your team uses their own settings.

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 YAML node types use .yml. Nodes on SQL Node Type use .sql (SQL-first authoring with annotations). V1 and SQL 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
SQL 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 YAML and SQL 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). SQL 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 SQL Node Type.

Coalesce-managed built-in templates cannot be changed arbitrarily. If a disallowed change is detected, Coalesce automatically attempts to revert it the next time you commit from the web app. 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