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, create Nodes, and deploy.

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 intentionally separated 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 Git 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.

  1. Define - Write your .sql transformation file
  2. Preview - Dry-run to see generated SQL before executing
  3. Create - Run DDL to build the table or view
  4. Run - Run DML to populate it with data
  5. Verify - Check results in your SQL client
  6. Iterate - Edit and repeat
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​

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. Create the workspaces.yml File​

You need to create workspaces.yml at the project root, at the same level as data.yml. This file maps your project's storage locations to physical database/schema pairs in your warehouse for local development. It 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​

coa doctor

This checks your project files, credentials, and warehouse connectivity. 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​

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.

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

##{ Node Name }

This prints the DDL without executing it. Check that:

  • The table name resolves correctly and is not blank.
  • Column types are not UNKNOWN. If they are, check your ref() targets and make sure you are using single quotes.
  • 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().

nodes/TARGET-STG_ORDERS.sql:

@id("8195ddbe-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') }}

nodes/TARGET-DIM_CUSTOMER.sql joins 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​

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

# Populate all tables
coa run --all

coa executes nodes in dependency order: sources first, then staging, then downstream nodes.

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.