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.

Build Your First Pipeline With Your Agent

In this quickstart your AI agent builds a small pipeline from sample data, and you review each step in Coalesce Desktop before anything ships.

You'll build: stage Nodes for customers and orders, a customer dimension that keeps history, and an orders fact, as SQL Nodes. On Databricks, the dimension and fact use YAML Node Types for now.

You'll learn: how to brief your agent, what to check in Coalesce Desktop, and how a change gets from your machine to a shared Environment.

Time: about 30 minutes.

Before You Begin​

You set up four things yourself. Your agent does the rest.

  • Git, and a repository on your Git host for this Workspace. Check with git --version.
  • A Coalesce account and token. Sign in to Coalesce, open the Deploy tab, and select Generate Access Token. You also need permission to create a Project, since coa init creates one for this Workspace.
  • A development schema on Snowflake, Databricks, or BigQuery that you can write to. Never point this quickstart at a production schema.
  • A coding agent, like Claude Code, Cursor, or Codex.

Step 1: Install Coalesce Desktop​

Download Coalesce Desktop for macOS, open the disk image, and drag Coalesce to Applications.

Desktop includes the coa CLI and tells your agent where to find it, so there is nothing else to install.

Step 2: Create the Workspace​

  1. Clone your empty repository and move into it:

    git clone <YOUR_REPO_URL> my-first-pipeline
    cd my-first-pipeline

  2. Launch Coalesce Desktop, choose Open existing folder…, and select my-first-pipeline. If Desktop warns that the folder isn't a Workspace yet, choose Open anyway.

    When Desktop asks for a profile, choose Create a profile…, name it quickstart, and paste the access token you generated. Create a new profile even if you already use coa, because coa init saves this Workspace's settings into it. On Workspace ready, choose Continue to Transform. You give your agent its prompt in Step 3.

    Keep Desktop running with this folder open. The ~/.coalesce/desktop/coa command used below exists only while Desktop has a Workspace open.

  3. In your terminal, still in my-first-pipeline, run coa init for your platform with the profile you just created. It checks your token, then asks for the Environment to deploy to, your warehouse credentials, and two Storage Locations: SRC for the sample data and TARGET for your development schema. It tests each connection before it saves anything, installs the base Node Types package for your platform, and creates a Coalesce Project named after the folder. Your agent adds the SQL Node Types package in Step 4.

~/.coalesce/desktop/coa init --profile quickstart
LocationDatabaseSchema
SRCSNOWFLAKE_SAMPLE_DATATPCH_SF1
TARGETYour development databaseYour development schema
  • On Windows, run & "$env:USERPROFILE\.coalesce\desktop\coa.cmd" init --profile quickstart in PowerShell, or %USERPROFILE%\.coalesce\desktop\coa.cmd init --profile quickstart in Command Prompt.
  • If you sign in somewhere other than app.coalescesoftware.io, add --domain https://<your-host>.
  • Check the first two lines of output. They name your folder and your profile. If either is wrong, press Ctrl-C, cd into my-first-pipeline, and run the command again.
  • Pick an Environment you can deploy to, not one marked (dev). Step 7 deploys to it. If your organization has no Environments yet, coa init skips this question; create one before Step 7.
  • Enter each database and schema separately. For SRC on Snowflake, that's SNOWFLAKE_SAMPLE_DATA, then TPCH_SF1. The TARGET schema must already exist.
  • If coa init asks to overwrite workspace.yml, answer y. Desktop wrote a starter file when it opened the folder, and --profile keeps your profile in the new one.
  • If a connection test fails, fix the value and run coa init again. It keeps everything that already passed.
  • Choose your sign-in method with Step 7 in mind. Snowflake OAuth and BigQuery Application Default Credentials work for local runs only. To plan and deploy, use a key pair or password on Snowflake, or a service account key on BigQuery.

See coa init for every option. Back in Coalesce Desktop, the graph is empty for now. Keep Desktop open next to your agent: this is where you review its work.

Step 3: Equip Your Agent​

Open a terminal in the Workspace folder, start your agent, and paste:

Read https://docs.coalesce.io/docs/get-started/set-up-your-agent.md and follow the instructions to set yourself up for Coalesce.

Your agent installs the Coalesce platform skills, which teach it how to write Nodes and use coa correctly, then checks the Workspace and reports back. Don't continue until its report says the warehouse check passed.

Databricks

The Databricks SQL package ships a Work Node Type today, not Dimension or Fact. In Steps 5 and 6 your agent tells you which Node Types it used instead; the review checks are the same.

Step 4: Build the Stage Layer​

Paste this prompt:

Using my platform's Base Node Types - SQL package (install it if it's missing), add the CUSTOMER and ORDERS tables from SRC as sources. Then add a SQL Work Node for each in TARGET, named STG_CUSTOMER and STG_ORDERS. Give every column a clear lowercase alias, and cast prices to DECIMAL(12,2). Create and run both Nodes, then tell me the row counts.

Review in Coalesce Desktop. Four Nodes appear in the graph as your agent writes them: two sources, each feeding a stage. Open STG_ORDERS and check:

  • The SQL is a plain SELECT with a {{ ref('SRC', 'ORDERS') }} in the FROM.
  • Every column in the column list has the name and type you expect. A type of UNKNOWN means the agent should add a cast.
  • The last run succeeded, and the row count matches what your agent reported.

The file your agent wrote looks like this:

nodes/TARGET-STG_ORDERS.sql
@id("<generated by coa>")
@nodeType("<package alias>:::SQLWork")
@materializationType("table")

SELECT
    O_ORDERKEY                    AS order_key @notNull,
    O_CUSTKEY                     AS customer_key,
    O_ORDERSTATUS                 AS order_status,
    O_TOTALPRICE::DECIMAL(12,2)   AS order_total,
    O_ORDERDATE                   AS order_date
FROM {{ ref('SRC', 'ORDERS') }}

Step 5: Add a Customer Dimension​

Add a SQL Dimension Node DIM_CUSTOMER from STG_CUSTOMER. customer_key is the business key. Keep history when address or phone changes, so it's a Type 2 dimension. Create and run it, and explain which annotations you used and why.

Review in Coalesce Desktop. Open DIM_CUSTOMER and check:

  • customer_key carries @isBusinessKey.
  • address and phone carry @isChangeTracking. Marking any column this way is what makes the dimension Type 2.
  • The agent added the system columns Type 2 needs: a version, a current flag, and create, update, and end dates, plus a surrogate key marked @isSurrogateKey. Desktop lists them in the column panel.
nodes/TARGET-DIM_CUSTOMER.sql (excerpt)
@mergeStrategy("changeTracking")

SELECT
    customer_key   @isBusinessKey,
    customer_name,
    address        @isChangeTracking,
    phone          @isChangeTracking
    -- plus the system columns for version, current flag, and dates
FROM {{ ref('TARGET', 'STG_CUSTOMER') }}

Step 6: Add an Orders Fact​

Add a SQL Fact Node FCT_ORDERS from STG_ORDERS, joined to DIM_CUSTOMER on customer_key so each order carries the dimension's surrogate key. order_key is the business key. Add a not-null test on order_key. Create and run it.

Review in Coalesce Desktop. The graph now runs from sources through stages to DIM_CUSTOMER and FCT_ORDERS. Select FCT_ORDERS and check:

  • Its lineage shows both STG_ORDERS and DIM_CUSTOMER upstream.
  • The join condition is on customer_key, and only current dimension rows are joined.
  • The test ran and passed.

Ask your agent to fix anything that looks wrong. You review the next version the same way.

Step 7: Ship It​

So far everything ran in your development schema. Shipping puts the pipeline into a shared Environment.

Before you plan, your Environment needs Storage Mappings for SRC and TARGET, and the target schema must already exist. Ask your agent to set these up and to confirm the Environment ID with coa environments list. To switch Environments, or to pick one you created after you ran coa init, run ~/.coalesce/desktop/coa init --profile quickstart --environmentID <ID>. It keeps everything else that already passed.

  1. Commit. Ask your agent to commit the Workspace and push to your repository.
  2. Plan. Ask your agent to run coa plan against your Environment. The plan lists every object it will create or change. Read it before you continue.
  3. Deploy. When the plan looks right, tell your agent to run coa deploy, then coa refresh to load the data.

No Environment yet? Ask your Coalesce administrator, or create one in the web app.

What You Built​

  • A Workspace in Git that your team can clone, review, and build on.
  • Two sources and four Nodes, each one a file your agent wrote and you reviewed.
  • A deploy that went through a plan you approved.

Next Steps​