Skip to main content

Build Pipelines With the Coalesce CLI

In this guide, you'll clone your repo, use the Coalesce CLI with the coa command, and create and run Nodes locally.

Before You Begin​

Make sure to complete CLI setup so you have a valid ~/.coa/config file, credentials, and project structure.

The Core Development Loop​

Local development with COA is separate from Coalesce Cloud. Rather than going through a full plan and deploy cycle every time you change a query, you work directly against your warehouse using the files in your version control checkout. This lets you move fast: edit a SQL file, see the generated DDL, create the table, check the data, and adjust, all without touching your Coalesce Environment.

This loop is for local iteration. Once your pipeline is working the way you want, you push to your repository and move to coa plan, coa deploy, and coa refresh to promote it to a Coalesce Environment and run it under cloud operations.

StepWhat it doesCommand
DefineWrite a .sql file in nodes/ with your transformation SQLAuthor the file as described in the next section
PreviewSee the generated SQL without executing itcoa create --dry-run --verbose
CreateRun DDL to create the table or view in your warehousecoa create --include "{ NODE }"
RunRun DML to populate the table with datacoa run --include "{ NODE }"
VerifyCheck the results in your warehouseUse your SQL client, for example a Snowflake worksheet or another client
IterateEdit, re-create, re-runRepeat

There is no coa fetch command to query results from the CLI. Use your preferred SQL client to verify data after running.

1. Clone Your Coalesce Repo​

Clone the repository that contains your Coalesce Project metadata.

git clone <your-coalesce-project-repo-url>
cd my-project

Your project should have this structure:

my-project/
├── data.yml              # Root metadata (fileVersion, platformKind)
├── locations.yml         # Storage location manifest
├── nodes/                # Pipeline nodes (.yml for V1, .sql for V2)
├── nodeTypes/            # Node type definitions with templates
├── environments/         # Environment configs with storage mappings
├── macros/               # Reusable SQL macros
├── jobs/                 # Job definitions
└── subgraphs/            # Subgraph definitions

2. Set Up Project Files​

You need a workspaces.yml file at the project root so local commands know which database and schema each storage location uses. For a new Snowflake project, use coa init. For an existing repository, create the file yourself or use coa doctor --fix.

New Projects: Run coa init​

From an empty project directory, run:

coa init

The interactive setup asks for your project name, Coalesce domain, Snowflake credentials with basic auth or key pair authentication, and SRC and TARGET storage mappings. It writes data.yml, workspaces.yml, and updates ~/.coa/config, installs selected Node types, and runs coa doctor when it finishes.

Existing configuration files

If workspaces.yml, data.yml, or related files already exist, coa init prompts before overwriting them. Back up your project first when you are not starting from scratch.

Existing Projects: Create workspaces.yml Manually​

Create workspaces.yml at the project root, at the same level as data.yml. This file is not usually committed to version control, so add it to .gitignore.

Align the location keys, for example SRC and TARGET, with the names in your project's locations.yml.

workspaces:
  default:
    SRC:
      database: SNOWFLAKE_SAMPLE_DATA
      schema: TPCH_SF1
    TARGET:
      database: MY_DATABASE
      schema: MY_SCHEMA

3. Verify Your Setup​

Run coa doctor to confirm project files, credentials, and warehouse connectivity:

coa doctor

A healthy setup looks like:

Project:
  ✓ data.yml
  ✓ locations.yml
  ✓ workspaces.yml
      SRC     ->  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1
      TARGET  ->  MY_DATABASE.MY_SCHEMA
  ✓ connection profile  "default" (from ~/.coa/config)

Connection: Basic
  ✓ account  your-account
  ✓ user  your-user
  ✓ warehouse  connected (snowflake)

Fix any issues before continuing. Run coa describe config if you need help with credential format.

coa doctor validates file structure and connectivity but does not verify that the databases and schemas in your workspaces.yml actually exist in your warehouse. You will discover those issues on your first coa create.

4. Create a Node Type for SQL Transformations​

With COA, you can create Nodes without needing the Coalesce App. Before writing .sql transformation Nodes, you need at least one Node type configured for Node Type V2. This defines the DDL and DML templates that coa uses to create and populate tables.

Node type directories follow the pattern <Name>-<UUID>/. The name can be any name you want. The UUID should match what the Coalesce App generates. For quick UUID values, use UUID Generator.

mkdir -p nodeTypes/Stage-a1b2c3d4-e5f6-7890-abcd-ef1234567890

Create the Node Template​

Add definition.yml, create.sql.j2, and run.sql.j2 under your node type directory.

Set fileVersion: 2 in definition.yml. This tells coa that this node type supports .sql files. See Node Type V2 for how SQL-first Nodes work in Coalesce.

fileVersion: 2
id: a1b2c3d4-e5f6-7890-abcd-ef1234567890
isDisabled: false
metadata:
defaultStorageLocation: null
error: null
nodeMetadataSpec: |-
    capitalized: SQL Stage
    short: SQL
    plural: SQLStages
    tagColor: '#2EB67D'

    config:
    - groupName: Options
    items:
    - type: materializationSelector
        default: table
        options:
        - table
        - view
        isRequired: true

    - type: multisourceToggle
        enableIf: "{% if node.materializationType == 'table' %} true {% else %} false {% endif %}"

    - type: overrideSQLToggle
        enableIf: "{% if node.materializationType == 'view' %} true {% else %} false {% endif %}"

    - displayName: Multi Source Strategy
        attributeName: insertStrategy
        type: dropdownSelector
        default: INSERT
        options:
        - "INSERT"
        - "UNION"
        - "UNION ALL"
        isRequired: true
        enableIf: "{% if node.isMultisource %} true {% else %} false {% endif %}"

    - displayName: Truncate Before
        attributeName: truncateBefore
        type: toggleButton
        default: true

    - displayName: Enable Tests
        attributeName: testsEnabled
        type: toggleButton
        default: true

    - displayName: Pre-SQL
        attributeName: preSQL
        type: textBox
        syntax: sql
        isRequired: false

    - displayName: Post-SQL
        attributeName: postSQL
        type: textBox
        syntax: sql
        isRequired: false
name: Stage V2
type: NodeType

Run coa describe node-types for the full reference on template variables, config options, and advanced patterns like MERGE.

5. Write and Run Your First Transformation Node​

Now that you have a Node Type, you can create a Node. Create a .sql file in nodes/. The filename pattern is <STORAGE_LOCATION>-<NodeName>.sql.

Create nodes/TARGET-STG_CUSTOMER.sql:

@id("8195ddbe-6034-4bc3-821b-8f479c5b1cdf")
@nodeType("a1b2c3d4-e5f6-7890-abcd-ef1234567890")
SELECT
  C_CUSTKEY AS CUSTOMER_KEY,
  C_NAME AS CUSTOMER_NAME,
  C_ADDRESS AS ADDRESS,
  C_NATIONKEY AS NATION_KEY,
  C_PHONE AS PHONE,
  C_ACCTBAL AS ACCOUNT_BALANCE
FROM {{ ref('SRC', 'CUSTOMER') }}

The @id annotation is auto-generated and should not be changed once set. The @nodeType annotation must map to a valid node type ID in your nodeTypes/ directory.

Always use single quotes inside ref(), for example ref('SRC', 'CUSTOMER'). Double quotes silently break lineage resolution and produce UNKNOWN column types with no error message.

Preview First With a Dry Run​

Always preview before executing:

coa create --include "{ STG_CUSTOMER }" --dry-run --verbose

The command prints generated DDL without executing it. Sample output includes a header like this:

##{ Node Name }

Check that:

  • The table name resolves correctly and is not blank.
  • Column types are not UNKNOWN. If they are, recheck your ref() targets.
  • The SQL looks correct overall.

If dry-run shows CREATE TABLE () with no columns, your SQL likely has a parse error: a typo or missing keyword. The SQL parser for Node Type V2 silently produces zero columns when it cannot parse the SELECT. Check your SQL syntax carefully; dry-run will not warn you about this directly.

Create and Run​

# Create the table structure (DDL)
coa create --include "{ STG_CUSTOMER }"

# Populate with data (DML)
coa run --include "{ STG_CUSTOMER }"

Verify the results in your SQL client.

Example: Build a Multi-Layer Pipeline​

Add downstream nodes that reference upstream nodes using ref().

Create nodes/TARGET-STG_ORDERS.sql:

@id("a295ddbe-6034-4bc3-821b-8f479c5b1cdf")
@nodeType("a1b2c3d4-e5f6-7890-abcd-ef1234567890")
SELECT
  O_ORDERKEY AS ORDER_KEY,
  O_CUSTKEY AS CUSTOMER_KEY,
  O_ORDERSTATUS AS ORDER_STATUS,
  O_TOTALPRICE AS TOTAL_PRICE,
  O_ORDERDATE AS ORDER_DATE
FROM {{ ref('SRC', 'ORDERS') }}

Create nodes/TARGET-DIM_CUSTOMER.sql to join two staging tables:

@id("8432ddbe-6034-4bc3-821b-8f479c5b1cdg")
@nodeType("a1b2c3d4-e5f6-7890-abcd-ef1234567890")
SELECT
  c.CUSTOMER_KEY,
  c.CUSTOMER_NAME,
  c.PHONE,
  c.ACCOUNT_BALANCE,
  COUNT(o.ORDER_KEY) AS TOTAL_ORDERS
FROM {{ ref('TARGET', 'STG_CUSTOMER') }} c
LEFT JOIN {{ ref('TARGET', 'STG_ORDERS') }} o
  ON c.CUSTOMER_KEY = o.CUSTOMER_KEY
GROUP BY c.CUSTOMER_KEY, c.CUSTOMER_NAME, c.PHONE, c.ACCOUNT_BALANCE

Run the Full Pipeline​

coa executes nodes in dependency order when you use --all: sources first, then staging, then downstream nodes.

# Create all tables (respects dependency order)
coa create --all

# Populate all tables
coa run --all

Node Selectors​

Most commands accept --include and --exclude flags with a selector syntax for targeting specific nodes.

# Select a node by name (shorthand)
--include "{ STG_ORDERS }"

# Select by exact name (value must be quoted)
--include '{ name: "STG_ORDERS" }'

# Select by storage location
--include '{ location: "SRC" }'

# Select by node type
--include '{ nodeType: "Stage" }'

# Combine multiple nodes with OR
--include '{ STG_ORDERS } || { STG_CUSTOMER }'

# Select everything
--all

# List all available nodes
coa create --list-nodes

Common Selector Mistakes​

These patterns often return no matched nodes without a clear error:

  • Separate braces for OR - Use { A } || { B }, not { A || B }. Putting both selectors inside one pair of braces returns a "no nodes matched" result and no error message.
  • Quoted selector values - { location: "SRC" } works; { location: SRC } returns no match.
  • coa refresh and the word OR - Cloud refresh commands use the word OR, not ||. For example, --include '{ name: "STG_CUSTOMER" } OR { name: "STG_ORDERS" }'. Run coa describe selectors to confirm syntax for your installed version.

For the full selector reference, run coa describe selectors.

Validation​

Run coa validate to check your workspace for structural issues before running:

coa validate
coa validate --verbose    # Details on each scanner
coa validate --json       # Structured output

This checks YAML schemas, storage locations, column references, data types, and more.

Known Column Reference Scanner Issue

The Column References scanner produces false positives on SQL Nodes when you use Node Type V2. Aliased columns may be flagged as "references missing source columns." These warnings do not block coa create or coa run, but they produce a non-zero exit code and may block coa plan in some environments. Run coa validate --verbose to distinguish real errors from these false positives before troubleshooting further.

What's Next?​

Continue with the rest of the CLI documentation when you need the full setup walkthrough, deploy and refresh workflows, policy detail, or troubleshooting help.