Coalesce Transform MCP Server

coalesce-transform-mcp

MCP server for Coalesce. Built for Snowflake Cortex Code (CoCo) - with first-class support for every other MCP client (Claude Code, Claude Desktop, Cursor, VS Code, Windsurf). Manage nodes, pipelines, environments, jobs, and runs, and drive the local-first Coalesce CLI from the same server: validate a project, preview DDL/DML, plan a deployment, and apply it to a cloud environment.

---

I want to…

---

Quick Start

Each link below opens a short install guide with a click-to-install button (where supported) and the manual config.

[!TIP] ❄️ Snowflake Cortex Code + coalesce-transform-mcp. CoCo is Snowflake's AI coding CLI - it already knows your warehouse, role, and data. Drop this MCP in and an agent can plan pipelines, create nodes, run DML, and verify results in a single session, all under Snowflake's auth model. Install in Cortex Code →

Why this pairing? Cortex Code is Snowflake's AI coding CLI - it already authenticates to your warehouse, runs under your Snowflake role, and has native tools for querying live data. Add coalesce-transform-mcp and a single agent session can plan pipelines, create nodes, run DML, and verify results against real rows without leaving the terminal.

One-liner (after installing the Cortex Code CLI):

cortex mcp add coalesce-transform npx coalesce-transform-mcp

Or edit ~/.snowflake/cortex/mcp.json directly:

{
  "mcpServers": {
    "coalesce-transform": {
      "type": "stdio",
      "command": "npx",
      "args": ["coalesce-transform-mcp"],
      "env": {
        "COALESCE_ACCESS_TOKEN": "<YOUR_TOKEN>"
      }
    }
  }
}

Drop the env block if you're using ~/.coa/config - Cortex Code and Coalesce can both pick the token up from the same profile. Full walkthrough: docs/installation-guides/cortex-code.md.

Click-to-install:

Manual: paste into .cursor/mcp.json in your project root (or ~/.cursor/mcp.json for global):

{
  "mcpServers": {
    "coalesce-transform": {
      "command": "npx",
      "args": ["coalesce-transform-mcp"],
      "env": {
        "COALESCE_ACCESS_TOKEN": "<YOUR_TOKEN>"
      }
    }
  }
}

Cursor does not expand ${VAR} - paste the literal token, or drop the env block and use ~/.coa/config (see Credentials).

Click-to-install:

Manual: follow the VS Code MCP install guide and use this config:

{
  "name": "coalesce-transform",
  "command": "npx",
  "args": ["coalesce-transform-mcp"]
}

Add the COALESCE_ACCESS_TOKEN via VS Code's secret input prompt, or drop the token and use ~/.coa/config. Reload the VS Code window after install.

Click-to-install:

Manual: identical to the stable VS Code install - Insiders reads the same MCP config.

One-liner:

claude mcp add coalesce-transform -- npx coalesce-transform-mcp

Pass env vars inline if you need them:

claude mcp add coalesce-transform \
  --env COALESCE_ACCESS_TOKEN=$COALESCE_ACCESS_TOKEN \
  -- npx coalesce-transform-mcp

Manual: paste into .mcp.json in your project root (or ~/.claude.json for global):

{
  "mcpServers": {
    "coalesce-transform": {
      "command": "npx",
      "args": ["coalesce-transform-mcp"],
      "env": {
        "COALESCE_ACCESS_TOKEN": "${COALESCE_ACCESS_TOKEN}"
      }
    }
  }
}

Claude Code does expand ${VAR} from your shell env at load time - .mcp.json can stay safely committed to git with variable references. Omit the env block if using ~/.coa/config.

No deeplink yet - paste manually.

File: ~/Library/Application Support/Claude/claude_desktop_config.json (macOS) or %APPDATA%\Claude\claude_desktop_config.json (Windows).

{
  "mcpServers": {
    "coalesce-transform": {
      "command": "npx",
      "args": ["coalesce-transform-mcp"],
      "env": {
        "COALESCE_ACCESS_TOKEN": "<YOUR_TOKEN>"
      }
    }
  }
}

Claude Desktop does not expand ${VAR} - paste the literal token, or drop the env block and use ~/.coa/config. Fully quit Claude Desktop (Cmd+Q) and relaunch after editing.

No deeplink yet - paste manually.

File: ~/.codeium/windsurf/mcp_config.json.

{
  "mcpServers": {
    "coalesce-transform": {
      "command": "npx",
      "args": ["coalesce-transform-mcp"],
      "env": {
        "COALESCE_ACCESS_TOKEN": "<YOUR_TOKEN>"
      }
    }
  }
}

Windsurf does not expand ${VAR} - paste the literal token, or drop the env block and use ~/.coa/config. Restart Windsurf after editing.

[!TIP]

🚀 Just installed?

Just say "help me get set up" — or run /coalesce-setup. Your agent will check your credentials and project setup, then walk you through fixing whatever's missing.

---

Skills

Skills are editable markdown that shapes how the agent reasons about your Coalesce project. Ship your team's naming conventions, grain definitions, and layering patterns as context - every agent on the server instantly picks them up. No fine-tuning, no prompt engineering, just markdown you edit and commit.

Set COALESCE_MCP_SKILLS_DIR to make skills editable on disk. Each skill resolves to default content, user-augmented content, or a full user override - see docs/context-skills.md for the resolution order and customization walkthrough.

25 skills, grouped into 6 families:

• overview - General Coalesce concepts, response guidelines, and operational constraints
• tool-usage - Best practices for tool batching, parallelization, and SQL conversion
• id-discovery - Resolving project, workspace, environment, job, run, node, and org IDs
• storage-mappings - Storage location concepts, {{ ref() }} syntax, and reference patterns
• ecosystem-boundaries - Scope of this MCP vs adjacent data-engineering MCPs (Snowflake, Fivetran, dbt, Catalog)
• data-engineering-principles - Node type selection, layered architecture, methodology detection, materialization strategies
• sql-platform-selection - Determining the active SQL platform from project metadata
• setup-guide - First-time MCP setup flow driven by diagnose_setup (pairs with the /coalesce-setup prompt)

• sql-snowflake - Snowflake-specific SQL conventions for node SQL
• sql-databricks - Databricks-specific SQL conventions for node SQL
• sql-bigquery - BigQuery-specific SQL conventions for node SQL

• node-creation-decision-tree - Choosing between predecessor-based creation, updates, and full replacements
• node-payloads - Working with workspace node bodies, metadata, config, and array-replacement risks
• hydrated-metadata - Coalesce hydrated metadata structures for advanced node payload editing
• intelligent-node-configuration - How intelligent config completion works, schema resolution, automatic field detection
• node-operations - Editing existing nodes: joins, columns, config fields, and SQL-to-graph conversion
• aggregation-patterns - JOIN ON generation, GROUP BY detection, and join-to-aggregation conversion

• node-type-selection-guide - When to use each Coalesce node type (Stage/Work vs Dimension/Fact vs specialized)
• node-type-corpus - Node type discovery, corpus search, and metadata patterns

• pipeline-workflows - Building pipelines end-to-end: node type selection, multi-node sequences, execution
• intent-pipeline-guide - Using build_pipeline_from_intent to create pipelines from natural language
• pipeline-review-guide - Using review_pipeline for pipeline analysis and optimization
• pipeline-workshop-guide - Using pipeline workshop tools for iterative, conversational pipeline building

• run-operations - Starting, retrying, polling, diagnosing, and canceling Coalesce runs
• run-diagnostics-guide - Using diagnose_run_failure to analyze failed runs and determine fixes

---

Tools

[!NOTE]

Legend

• ⚠️ Destructive - the tool needs confirmed: true before it will run.
• 🧰 Bundled coa CLI - runs locally against a project directory. The tool needs a projectPath pointing at a folder that contains data.yml.
• Preflight validation - destructive 🧰 tools run a safety check before shelling out. See Safety model.

Environments, workspaces, projects

• list_environments - List all available environments
• get_environment - Get details of a specific environment
• list_workspaces - List all workspaces
• get_workspace - Get details of a specific workspace
• list_projects - List all projects
• get_project - Get project details

Nodes

• list_environment_nodes - List nodes in an environment
• list_workspace_nodes - List nodes in a workspace
• get_environment_node - Get a specific environment node
• get_workspace_node - Get a specific workspace node
• analyze_workspace_patterns - Detect package adoption, pipeline layers, methodology, and generate recommendations
• list_workspace_node_types - List distinct node types observed in current workspace nodes

Jobs, subgraphs, runs

• list_environment_jobs - List all jobs for an environment
• find_jobs_by_name - Fuzzy-match environment jobs by name and return each match with its recent runs
• get_job_run_details_by_name - Fuzzy-match one environment job by name and return its latest run details + results
• get_environment_job - Get details of a specific job
• list_job_nodes - Resolve a workspace job's selectors into concrete nodes, grouped by subgraph
• get_workspace_subgraph - Get details of a specific subgraph by UUID (the public API has no subgraph list endpoint — look up UUIDs via the repo's subgraphs/ folder or the local cache populated on create)
• list_runs - List runs with optional filters
• get_run - Get details of a specific run
• get_run_results - Get results of a completed run
• get_run_details - Run metadata plus results in one call

Search

• search_workspace_content - Search node SQL, column names, descriptions, and config values
• audit_documentation_coverage - Scan all workspace nodes/columns for missing descriptions

Local project & cloud CLI

• 🧰 coa_list_project_nodes - List all nodes defined in a local project (pre-deploy)

Plan & build

• plan_pipeline - Plan a pipeline from SQL or a natural-language goal without mutating the workspace; ranks best-fit node types from the local repo
• create_pipeline_from_plan - Execute an approved pipeline plan using predecessor-based creation
• create_pipeline_from_sql - Plan and create a pipeline directly from SQL
• build_pipeline_from_intent - Build a pipeline from a natural language goal with automatic entity resolution and node type selection
• review_pipeline - Analyze an existing pipeline for redundant nodes, missing joins, layer violations, naming issues, and optimization opportunities
• parse_sql_structure - Parse a SQL statement into structural components (CTEs, source tables, projected columns) without touching the workspace
• select_pipeline_node_type - Rank and select the best Coalesce node type for a pipeline step

Workshop (iterative, conversational)

• pipeline_workshop_open - Open an iterative pipeline builder session with workspace context pre-loaded
• pipeline_workshop_instruct - Send a natural language instruction to modify the current workshop plan
• get_pipeline_workshop_status - Get the current state of a workshop session
• pipeline_workshop_close - Close a workshop session and release resources

Local project validation & planning

• 🧰 coa_validate - Validate YAML schemas and scan a local project for configuration problems
• 🧰 coa_plan - Generate a deployment plan JSON by diffing a local project against a cloud environment (non-destructive)

Create

• create_workspace_node_from_scratch - Create a workspace node with no predecessors
• create_workspace_node_from_predecessor - Create a node from predecessor nodes with column coverage verification
• create_node_from_external_schema - Create a workspace node whose columns match an existing warehouse table or external schema

Update

• set_workspace_node - Replace a workspace node with a full body
• update_workspace_node - Safely update selected fields of a workspace node
• replace_workspace_node_columns - Replace metadata.columns wholesale
• delete_workspace_node - Delete a node from a workspace ⚠️

Configure

• complete_node_configuration - Intelligently complete a node's configuration by analyzing context
• apply_join_condition - Auto-generate and write a FROM/JOIN/ON clause for a multi-predecessor node
• convert_join_to_aggregation - Convert a join-style node into an aggregated fact-style node

Disk ↔ cloud round-trip

• serialize_workspace_node_to_disk_yaml - Convert a cloud workspace node to the on-disk nodes/*.yml shape coa reads (drop into a coa project for dry-run rendering)
• parse_disk_node_to_workspace_body - Convert an on-disk coa node YAML back into a cloud workspace-node body for set_workspace_node / update_workspace_node
• apply_sql_to_workspace_node - Round-trip user edits on rendered DDL/DML SQL (coa_dry_run_create / coa_dry_run_run output) back into a cloud node — column diff, source-mapping inference, GROUP BY / ORDER BY / LIMIT, INSERT-target rename

Subgraphs & jobs

• create_workspace_subgraph - Create a subgraph to group nodes visually
• update_workspace_subgraph - Update a subgraph's name and node membership
• delete_workspace_subgraph - Delete a subgraph (nodes are NOT deleted) ⚠️
• create_workspace_job - Create a job in a workspace with node include/exclude selectors
• update_workspace_job - Update a job's name and node selectors
• delete_workspace_job - Delete a job ⚠️

• start_run - Start a new run; requires Snowflake auth
• run_and_wait - Start a run and poll until completion
• run_status - Check status of a running job
• retry_run - Retry a failed run
• retry_and_wait - Retry a failed run and poll until completion
• cancel_run - Cancel a running job ⚠️
• diagnose_run_failure - Classify errors, surface root cause, suggest actionable fixes
• get_environment_overview - Environment details with full node list
• get_environment_health - Dashboard: node counts, run statuses, failed runs in last 24h, stale nodes, dependency health

Local execution (bundled CLI)

• 🧰 coa_dry_run_create - Preview DDL without executing (does not validate columns/types exist in warehouse)
• 🧰 coa_dry_run_run - Preview DML without executing (same caveat)
• 🧰 coa_create - Run DDL (CREATE/REPLACE) against the warehouse for selected nodes ⚠️
• 🧰 coa_run - Run DML (INSERT/MERGE) to populate selected nodes ⚠️
• 🧰 coa_deploy - Apply a plan JSON to a cloud environment ⚠️
• 🧰 coa_refresh - Run DML for selected nodes in an already-deployed environment (no local project required) ⚠️

Warehouse source discovery (bundled CLI, new in v0.10)

Pair these three to onboard warehouse tables as source nodes without hand-writing YAML. Typical flow: list to see per-location counts, add (scoped by location) to scaffold, optionally remove to drop nodes when a location is retired. Each add populates the source node's database/schema from the workspace config — no placeholder values to fix up afterward.

• 🧰 coa_list_warehouse_sources - List warehouse tables per storage location; summary or per-table detail (with alreadyExists flags)
• 🧰 coa_add_warehouse_sources ⚠️ - Scaffold source-node YAML files from warehouse tables; supports dryRun: true for preview
• 🧰 coa_remove_warehouse_sources ⚠️ - Delete scaffolded source-node YAML; hand-renamed files are skipped

• get_upstream_nodes - Walk the full upstream dependency graph for a node
• get_downstream_nodes - Walk the full downstream dependency graph for a node
• get_column_lineage - Trace a column through the pipeline upstream and downstream
• analyze_impact - Downstream impact of changing a node or specific column - impacted counts, grouped by depth, and critical path
• propagate_column_change - Update all downstream columns after a column rename or data type change ⚠️

• list_repo_packages - List package aliases and enabled node-type coverage from a committed Coalesce repo
• list_repo_node_types - List exact resolvable committed node-type identifiers from nodeTypes/
• get_repo_node_type_definition - Resolve one node type and return its outer definition plus parsed nodeMetadataSpec
• generate_set_workspace_node_template - Generate a YAML-friendly set_workspace_node body template
• search_node_type_variants - Search the committed node-type corpus by normalized family, package, primitive, or support status
• get_node_type_variant - Load one exact node-type corpus variant by variant key
• generate_set_workspace_node_template_from_variant - Generate a template from a committed corpus variant

• create_environment - Create a new environment within a project
• delete_environment - Delete an environment ⚠️
• create_project - Create a new project
• update_project - Update a project
• delete_project - Delete a project ⚠️
• list_git_accounts - List all git accounts
• get_git_account - Get git account details
• create_git_account - Create a new git account
• update_git_account - Update a git account
• delete_git_account - Delete a git account ⚠️

• list_org_users - List all organization users
• get_user_roles - Get roles for a specific user
• list_user_roles - List all user roles
• set_org_role - Set organization role for a user
• set_project_role - Set project role for a user
• delete_project_role - Remove project role from a user ⚠️
• set_env_role - Set environment role for a user
• delete_env_role - Remove environment role from a user ⚠️

Cache snapshots

• cache_workspace_nodes - Fetch every page of workspace nodes, write a full snapshot, and return cache metadata
• cache_environment_nodes - Fetch every page of environment nodes, write a full snapshot
• cache_runs - Fetch every page of run results, write a full snapshot
• cache_org_users - Fetch every page of organization users, write a full snapshot
• clear_data_cache - Delete all cached snapshots, auto-cached responses, and plan summaries ⚠️

Skills & setup

• personalize_skills - Export bundled skill files to a local directory for customization
• diagnose_setup - Stateless probe reporting configured setup pieces; pairs with the /coalesce-setup MCP prompt
• 🧰 coa_doctor - Check config, credentials, and warehouse connectivity end-to-end for a local project
• 🧰 coa_bootstrap_workspaces ⚠️ - Run coa doctor --fix to write a starter workspaces.yml from locations.yml (placeholder values — user must edit before running warehouse operations)
• 🧰 coa_describe - Fetch a section of COA's self-describing documentation by topic + optional subtopic (also available as coalesce://coa/describe/* resources)

---

Full Installation

Requirements:

• Node.js 22+
• A Coalesce account with a workspace
• An MCP-compatible AI client (see Quick Start)
• Snowflake credentials - only if you plan to use run tools or coa_create/coa_run (see Credentials)
• Install footprint is ~76 MB unpacked (the bundled @coalescesoftware/coa CLI ships its own runtime; the MCP tarball itself is under 1 MB)

1. Clone your project. If your team already has a Coalesce project in Git, clone it locally - the bundled coa CLI operates on a project directory, so most local create/run tools require one on disk:

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

Don't have a Git-linked project yet? In the Coalesce UI, open your workspace → Settings → Git and connect a repo (or create one via your Git provider and paste the URL). Coalesce will commit the project skeleton on first push; clone that repo locally once it's populated.

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

V1 vs V2 - the format is pinned by fileVersion in data.yml. V1 (fileVersion: 1 or 2) stores each node as a single YAML file with columns, transforms, and config inline. V2 (fileVersion: 3) is SQL-first: the node body lives in a .sql file using @id / @nodeType annotations and {{ ref() }} references, with YAML retained for config. New projects default to V2; existing V1 projects keep working unchanged.

[!NOTE] V2 status as of v0.10. V2 is supported. Earlier versions of this MCP required a v2Acknowledged: true flag on coa_create / coa_run / coa_plan because the V2 parser had silent failure modes. COA 7.35 fixed the two biggest (bidirectional config-annotation sync, UNION ALL preservation), so the flag has been removed. Two minor rough edges remain when authoring V2 — coa validate false positives on aliased columns, and parse errors rendering to zero-column CREATE TABLE ... AS SELECT WHERE 1=0. The V2_DETECTED preflight warning surfaces both; the coalesce://context/sql-node-v2-policy resource has the full playbook.

Point the MCP at this directory by setting repoPath in ~/.coa/config or COALESCE_REPO_PATH in your env block.

2. Create workspaces.yml. This file is required for coa_create / coa_run and their dry-run variants. It maps each storage location declared in locations.yml to a physical database + schema for local development. It's typically gitignored (per-developer), so cloning the project does not give it to you - you have to create it.

The /coalesce-setup prompt detects a missing workspaces.yml and walks you through it. If you'd rather do it directly, pick one of:

• Ask your agent to bootstrap it (easiest): prompt the agent to call the coa_bootstrap_workspaces tool (it needs confirmed: true, so the agent will ask before running).

  [!WARNING] The generated file contains placeholder values. The bootstrap tool seeds database/schema with defaults that won't match your real warehouse. Ask the agent to open the file with you and replace every placeholder before calling coa_create / coa_run - otherwise the generated DDL/DML will target the wrong (or non-existent) database.

• Hand-write it. Ask the agent to fetch the authoritative schema via the coa_describe tool (topic: "schema", subtopic: "workspaces") - no top-level wrapper, no fileVersion.

  # workspaces.yml - keys are workspace names; `dev` is the default if --workspace is omitted
  dev:
    connection: snowflake          # required - name of the connection block COA should use
    locations:                     # optional - one entry per storage location name from locations.yml
      SRC_INGEST_TASTY_BITES:
        database: JESSE_DEV        # required
        schema: INGEST_TASTY_BITES # required
      ETL_STAGE:
        database: JESSE_DEV
        schema: ETL_STAGE
      ANALYTICS:
        database: JESSE_DEV
        schema: ANALYTICS
  

Ask your agent to verify the setup - e.g. "Run coa_doctor on my project and summarize the results." It checks data.yml, workspaces.yml, credentials, and warehouse connectivity end to end.

COA 7.35 (the version bundled with v0.10) also accepts a workspace.yml (note: singular, not the plural workspaces.yml above). It's just a flat locations: map — no top-level workspace names, no connection: block — and is suitable when you only need local storage mappings and don't need to switch between named workspaces.

# workspace.yml (singular) - per-repo local storage mappings only
locations:
  SRC_INGEST_TASTY_BITES:
    database: JESSE_DEV
    schema: INGEST_TASTY_BITES
  ANALYTICS:
    database: JESSE_DEV
    schema: ANALYTICS

The two files are independent: workspaces.yml (plural) handles multi-workspace cloud profiles, workspace.yml (singular) handles per-repo local mappings. Pick the simpler shape if multi-workspace isn't a need. Fetch the canonical schema via coa_describe (topic: "schema", subtopic: "workspace").

3. Pick an auth path:

Simplest for first-time MCP users. Generate a COALESCE_ACCESS_TOKEN from Coalesce → Deploy → User Settings, then include it in your client config:

{
  "env": {
    "COALESCE_ACCESS_TOKEN": "<YOUR_TOKEN>"
  }
}

Best if you already use the coa CLI - the server reads the same profile file, so nothing to duplicate. Drop the env block entirely:

{
  "command": "npx",
  "args": ["coalesce-transform-mcp"]
}

See Credentials for the profile schema.

When both sources set a field, the env var wins.

4. Install the server via one of the Quick Start paths above.

5. Restart your client, then run the /coalesce-setup prompt to verify everything is wired up.

If you have more than one Coalesce environment to manage, see Multiple environments.

Credentials

The server reads credentials from two sources and merges them with env-wins precedence - a matching env var always overrides the profile value, so you can pin a single field per session without editing the config file. Call diagnose_setup to see which source supplied each value.

Source 1: ~/.coa/config (shared with the coa CLI)

COA stores credentials in a standard INI file. You create it by hand, or let coa write it as you use the CLI. The MCP reads the profile selected by COALESCE_PROFILE (default [default]) and maps the keys below onto their matching env vars.

[default]
token=<your-coalesce-refresh-token>
domain=https://your-org.app.coalescesoftware.io
snowflakeAccount=<your-snowflake-account>   # e.g., abc12345.us-east-1 - required by coa CLI
snowflakeUsername=YOUR_USER
snowflakeRole=YOUR_ROLE
snowflakeWarehouse=YOUR_WAREHOUSE
snowflakeKeyPairKey=/Users/you/.coa/rsa_key.p8
snowflakeAuthType=KeyPair
orgID=<your-org-id>              # optional; fallback for cancel-run
repoPath=/Users/you/path/to/repo # optional; for repo-backed tools
cacheDir=/Users/you/.coa/cache   # optional; per-profile cache isolation

[staging]
# …additional profiles; select with COALESCE_PROFILE

Key mapping - each profile key maps to an env var of the same concept:

Notes:

• snowflakeAuthType is read by COA itself (no env var) - include it when using key-pair auth.
• orgID, repoPath, and cacheDir are MCP-specific - the COA CLI ignores them.
• Only the fields the MCP needs are shown above. COA's config supports many more - run npx @coalescesoftware/coa describe config for the authoritative reference. Unknown keys are ignored.

If ~/.coa/config doesn't exist the server runs env-only - startup never fails on a missing or malformed profile file; it just logs a stderr warning.

Source 2: env vars in your MCP config

Documentation truncated — see the full README on GitHub.