Windsurf Data Pipeline Cascade

I have a rule I tell every junior who joins my team: a pipeline does not fail loudly, it fails three weeks later in a dashboard nobody trusts anymore. That fear is exactly why I was slow to let an AI agent anywhere near my dbt project. A model that compiles is not a model that is correct, and most coding assistants happily hand you SQL that compiles against a schema they invented in their head.

So this is not a story about going fast. It is about the setup I landed on after a few frustrating weekends, where Windsurf's Cascade agent can actually add a staging model or a mart to my warehouse and I trust the output enough to merge it. The short version: give the agent eyes into the real database, make it work one DAG stage at a time, and gate every commit behind dbt tests. The rest of this is the boring detail that makes those three things true.

Why Cascade fits ETL specifically

I tried a couple of the terminal agents first and bounced off them for this work. The thing about a data pipeline is that it is already a sequence: source, staging, intermediate, mart. You do not build a mart and then go back and decide what staging looks like. Cascade's flow is step driven, it proposes a change, you look, it continues, and that maps onto the DAG almost too neatly. I want the agent thinking the way the data flows, not dumping eight files at once.

The other half is that Cascade reads rules from plain Markdown in the repo, the same way the rest of my team reads the README. Windsurf calls them workspace rules and they live under .windsurf/rules. I keep mine version controlled so a teammate cloning the repo gets the same guardrails I do. No hidden settings, no per-machine config drift, which for a data team is the whole point.

---
trigger: always_on
---

# Pipeline: analytics-warehouse (dbt + Postgres)

## What this repo is
A dbt project that models raw fintech events into a star schema on
Postgres. Layers, in order: staging -> intermediate -> marts.
Orchestrated by Dagster. Nothing here touches production data directly;
Cascade works against a seeded dev warehouse.

## Commands (use these, do not invent them)
- Build everything: `dbt build`
- One model + its tests: `dbt build --select stg_payments+`
- Tests only: `dbt test --select <model>`
- Compile (no run): `dbt compile`
- Lint SQL: `sqlfluff lint models/`
- Seed dev warehouse: `dbt seed`

## DAG rules (non-negotiable)
- staging models read ONLY from sources, one source table each, no joins.
- Joins and business logic live in intermediate (int_*) models.
- marts (fct_*, dim_*) read from intermediate, never from staging.
- Every model declares a schema.yml with at least one not_null and one
  unique or relationship test on its grain key.

## Transform rules
- Transforms must be idempotent. `dbt build` twice = same result. No
  inserts that depend on run time, no `current_timestamp` in a key.
- Incremental models need a deterministic unique_key and an is_incremental
  block. Full-refresh must still produce the identical table.
- Never `SELECT *` past staging. Name your columns.

## Conventions
- One model per file, file name = model name.
- snake_case everywhere. Money is stored in minor units (integer cents).
- Document every model and every column in schema.yml. A model with no
  description is not done.

The Postgres MCP server is the whole trick

Here is where most of the value lives. Without MCP, Cascade is guessing at my schema from whatever it can grep out of the repo. With the postgres MCP server pointed at my dev warehouse, it can run a read-only query and actually see that stg_payments has an amount_cents column and not an amount column. That one capability kills the entire category of bugs where a model references a column that does not exist.

I connect it with a read-only role. cascade_ro can SELECT and read information_schema, nothing else. The agent gets to inspect, never to mutate. dbt is the only thing allowed to write tables, and dbt runs under my hands, not the agent's.

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://cascade_ro@localhost:5432/warehouse_dev"
      ]
    },
    "filesystem": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-filesystem",
        "."
      ]
    },
    "github": {
      "serverUrl": "https://api.githubcopilot.com/mcp/"
    }
  }
}

23:41

A validator subagent that refuses to trust compiled SQL

Cascade writes the model. A separate validator checks it against the real warehouse before I look. I keep that as its own rule scoped to fire after any model edit, and it has a narrow job: do not write SQL, just confirm the model is true. It reads the file, queries information_schema for every column the model assumes, and checks that the grain key is actually unique by comparing count to count distinct on the compiled output.

That last check has saved me more than once. A model can pass dbt compile and still have a fan-out join that doubles your rows. The validator catches it because it runs the query, not because it read the SQL and felt good about it.

---
trigger: model_decision
description: >
  Validate a dbt model against the warehouse before it is considered done.
  Invoke after Cascade writes or edits any model in models/.
---

You are the schema-validator for the analytics-warehouse project. You do
not write SQL. Your job is to check that a model is correct against the
real warehouse using the postgres MCP server.

For the model you are handed, do this in order:
1. Read its .sql file and its schema.yml entry.
2. Query information_schema for every source/ref it touches. Confirm each
   referenced column actually exists with the type the model assumes.
3. Confirm the declared grain key is unique: run a count vs count(distinct)
   on the compiled output (use dbt compile, then run the SQL read-only).
4. Confirm a full-refresh and an incremental run would land the same rows.
5. Report findings as: file, severity (block/warn/nit), and the exact fix.

If the model has no description or is missing the not_null/unique tests the
rules require, that is a block. Do not approve a model you could not query.

dbt test on pre-commit, so red never gets committed

Rules are advice the agent usually follows. A git hook is a wall it cannot walk through. I wire dbt test into the pre-commit hook, scoped to only the models that changed so it stays fast, and if a test fails the commit is blocked. Cascade can be as confident as it likes; if the unique test on the grain key fails, nothing lands.

#!/usr/bin/env bash
# .git/hooks/pre-commit -> wired so Cascade's commits run dbt tests.
# Only tests the models that actually changed, so it stays fast.
set -euo pipefail

CHANGED=$(git diff --cached --name-only --diff-filter=ACM | grep '^models/.*\.sql$' || true)
if [ -z "$CHANGED" ]; then
  exit 0
fi

# models/staging/stg_payments.sql -> stg_payments
SELECTORS=$(printf '%s\n' "$CHANGED" | sed -E 's#.*/##; s#\.sql$##' | paste -sd' ' -)

echo "pre-commit: dbt test on changed models -> $SELECTORS"
if ! dbt test --select $SELECTORS; then
  echo "pre-commit: dbt tests failed, commit blocked" >&2
  exit 1
fi

What it actually costs and catches

I am not going to pretend this turned me into a ten times engineer. What it did was take the tedious part of warehouse work, the boilerplate staging models and the schema.yml docs nobody wants to write, and make it reliable enough that I stopped re-reading every line. Here is roughly where it sits for me on a normal model.

• Fan-out joins are the bug it catches most. Compiled SQL hides them; a count vs count distinct does not.
• Phantom columns basically disappeared once the agent could query information_schema instead of guessing.
• Undocumented models stopped sneaking in, because the rules treat a missing description as not-done and the validator blocks on it.

The agent did not make me faster at writing SQL. It made the SQL trustworthy enough that I stopped manually diffing row counts after every change, and that was the actual time sink.

26:54

Steal the setup

If you run dbt, the parts worth copying in order are: a read-only MCP role pointed at a dev warehouse, your DAG layering written into the Windsurf rules, and a pre-commit hook that runs dbt test on the changed models. Those three turn Cascade from a fast way to write wrong SQL into something that has to prove itself against your real schema before you ever read it. Start with the rules file, it is the cheapest win.