dbt Shepherd

GitLab Duo agents turn a one-line data request into a tested, governed dbt model.

Comment

Inspiration

Every analytics team has the same backlog: "Can I get daily revenue by region?" Each request means a model, tests, docs, a PII check, a review, and a deploy. Meanwhile source data goes stale without anyone noticing, and the first alert is a wrong dashboard in a leadership meeting. We wanted the routine parts of analytics engineering handled by agents, with a human signing off on what reaches production.

What it does

  1. A stakeholder opens an issue and assigns or mentions the issue-to-dbt-model Duo flow.
  2. The flow's planner reads the repo and posts an implementation plan on the issue. In our run it also spotted that a similar model already existed and said so. Its builder then writes the SQL, schema.yml, and tests, runs dbt build and sqlfluff itself, and opens a merge request linked to the issue.
  3. The MR pipeline lints YAML and SQL, builds and tests the dbt project on DuckDB, and runs a PII governance gate that fails if any column marked PII lacks a policy tag.
  4. When the MR merges into main, GitLab SAST and Secret Detection run, a container image is pushed to the GitLab registry, and staging docs deploy automatically to GitLab Pages.
  5. Production is a manual job. A human reviews the outcome and clicks deploy. That is the Supervised level.
  6. A vX.Y.Z tag builds a versioned image, generates a changelog from git history, and creates a GitLab Release.
  7. A daily scheduled pipeline runs dbt source freshness and row-count checks. If either fails, it opens an issue that @-mentions the freshness-fixer flow, which is designed to diagnose the problem and propose a fix MR. The fixer flow is registered but was not triggered during the event.

Track: Path A (Start Fresh). Autonomy level: Supervised.

How we built it

  • dbt + DuckDB with a synthetic, seeded warehouse (fake @example.com customers), so CI needs no external database.
  • GitLab CI/CD in 7 stages: lint, build, secure, package, release, deploy, monitor. Workflow rules give MR, branch, tag, and schedule pipelines different shapes.
  • GitLab Duo Agent Platform custom flows (.gitlab/duo/flows/*.yml, flow registry v1) with two components each (planner → builder). They were registered and enabled from code with the AI Catalog GraphQL API (aiCatalogFlowCreate, aiCatalogItemConsumerCreate); see scripts/duo_register.py. AGENTS.md gives the agents the project's SQL and testing conventions.
  • A PII governance agent prompt (.gitlab/duo/agents/), plus a deterministic pii_check.py gate in CI.
  • GitLab Pages for production docs, and a Pages parallel deployment (path_prefix) for staging.

Lifecycle stages covered

Plan, Create, Verify, Secure, Package, Release, Configure, Monitor, Govern:

  • Plan: Duo flow planner posts the plan on the issue
  • Create: Duo flow builder writes the model and tests and opens the MR
  • Verify: yamllint, sqlfluff, dbt build with data tests, on every MR
  • Secure: GitLab SAST, Secret Detection, PII policy-tag gate
  • Package: Docker image pushed to the GitLab container registry
  • Release: Tag pipeline: changelog and GitLab Release
  • Configure: dev/staging/prod dbt targets selected by CI variables; flow runner config
  • Monitor: Daily scheduled freshness and row-count checks open an issue that @-mentions the fixer flow (registered, not triggered during the event)
  • Govern: Manual production deploy a human must start, CODEOWNERS documenting owners (enforcement needs Maintainer settings), PII policy tags, agent audit notes on issues

Challenges we ran into

  • The flow validator rejected our first YAML: every input passed to a component has to appear in its prompt template. The error message told us exactly which variable was unused, and the fix took one line.
  • Service accounts for flows are named ai-<flow>-<group>, so the monitor job's mention handle could only be set after the flow was enabled.
  • Producing a data-freshness failure on demand: the synthetic data has a fixed end date, so freshness drifts into an error two days later and the monitor has something real to catch.

Accomplishments that we're proud of

In our recorded run, the flow went from issue to a green MR in about 3 minutes. It ran 26 of 26 dbt nodes and tests, and every pipeline type (MR, main, manual production, tag, schedule) passed on GitLab.com shared runners.

What we learned

Agents are most useful on analytics work when the repo has strong guardrails they can run themselves: tests, linters, and a PII gate. The agent iterates until the gates pass, and the human only judges the result.

What's next

  • Wire the built-in Fix CI/CD Pipeline flow into failed MR pipelines automatically.
  • Column-level lineage in the MR description, and impact analysis on downstream dashboards.
  • Deploy to a real warehouse target (BigQuery or Snowflake) with the same supervised gate.

Built With

Share this project:

Updates

Submission history