DBT Building Agent

The DBT Building Agent automates project scaffolding, model generation (staging/intermediate/marts), tests, documentation, and safe execution—turning specs and source metadata into reproducible dbt assets.

Posture: read-first on production; write in dev/test or a workspace schema. External sharing only with explicit approval.

Overview

Purpose

Automate dbt scaffolding; generate staging/intermediate/marts models; add tests/docs; run builds and capture artifacts for review.

Scope

Project scaffolding, source registration, models (staging/base, intermediate joins, marts), tests at scale, docs/exposures, seeds & snapshots, compile/run/test/build.

Design

Deterministic, idempotent generation; safety-first defaults; clear lineage via ref()/source(); YAML-managed tests/docs.

Typical use cases

Bootstrap a new dbt project from connected warehouse metadata
Generate staging and marts from a mapping specification
Introduce tests and docs at scale to an existing project
Migrate SQL pipelines into modular dbt models with macros and contracts

Inputs and prerequisites

Data access: read access to source schemas; dev/test write access for models
Specifications: mapping tables/specs, naming conventions, test coverage targets
Repo access: branch to write code (feature branch by default)
Environment: dbt profile/adapter (Cloud or Core)

Core workflows

Discover sources

Enumerate schemas/tables/columns; infer keys and relationships from metadata.

Scaffold project

Create model folders; write packages.yml and dbt_project.yml.

Generate models

Staging per source table, intermediate joins, marts aligned to specs.

Configure tests & docs

Produce schema.yml with tests; add descriptions and exposures.

Execute & validate

dbt compile/run/test; capture artifacts; open tasks for any issues.

Default outputs

Code tree: dbt_project.yml, packages.yml, models/, macros/, seeds/, snapshots/
Models: staging (stg_*), intermediate, marts (dims/facts)
Artifacts: manifest.json, run_results.json (optional catalog.json)
Test and build summaries suitable for review

Tools and permissions

Common: project_manager_tools, data_connector_tools, dbt (Core/Cloud), git_action, artifact_manager_tools, file_manager_tools
Optional: snowflake_tools, google_drive_tools, slack_tools, document_index_tools, delegate_work
Posture: read-first on production; writes in dev/test or workspace; external sharing only with approval

Safety and operational notes

Use YAML configs for materializations; avoid destructive changes without confirmation.
Enforce safe limits during profiling; avoid wide cross joins.
Mask sensitive data in logs/exports; record assumptions and caveats.
Confirm before creating persistent tables; prefer workspace/dev schemas.

Configuration

Naming: stg_, dim_, fct_; snake_case
Materializations: view/table/incremental via YAML
Tests: mandatory (not_null, unique); optional (relationships, accepted_values)
Environments: adapter-specific configs per target

Example workflow (Healthcare Data Pipeline)

This illustration uses the mission’s tables in hcls_demo_1_sources.main. Examples are SQLite-friendly; adapt functions for your adapter as needed.

Context: available sources

1) Register sources (models/sources/sources_main.yml)

models/sources/sources_main.yml

version: 2
sources:
  - name: main
    schema: main
    tables:
      - name: CLAIMS
      - name: CLAIM_DETAILS
      - name: PATIENTS
      - name: PROVIDERS

2) Staging models (SQLite-friendly)

models/staging/main/stg_claims.sql

with src as (
  select
    CLAIM_ID,
    PATIENT_ID,
    PROVIDER_ID,
    date(CLAIM_DATE) as claim_date,
    date(ADMISSION_DATE) as admission_date,
    date(DISCHARGE_DATE) as discharge_date,
    CLAIM_TYPE,
    CLAIM_STATUS,
    coalesce(INSURANCE_PAID, 0.0) + coalesce(PATIENT_RESPONSIBILITY, 0.0) as allowed_amount
  from {{ source('main','CLAIMS') }}
)
select * from src;

models/staging/main/stg_claim_details.sql

with src as (
  select
    CLAIM_DETAIL_ID,
    CLAIM_ID,
    date(SERVICE_DATE) as service_date,
    PROCEDURE_CODE,
    DIAGNOSIS_CODE,
    substr(DIAGNOSIS_CODE, 1, 3) as diagnosis_grp,
    CHARGE_AMOUNT,
    UNITS
  from {{ source('main','CLAIM_DETAILS') }}
)
select * from src;

models/staging/main/stg_patients.sql

with src as (
  select
    PATIENT_ID,
    FIRST_NAME,
    LAST_NAME,
    date(DATE_OF_BIRTH) as date_of_birth,
    GENDER
  from {{ source('main','PATIENTS') }}
)
select
  *,
  cast((julianday('now') - julianday(date_of_birth)) / 365.25 as integer) as age_years
from src;

models/staging/main/stg_providers.sql

select
  PROVIDER_ID,
  PROVIDER_NAME,
  SPECIALTY,
  NPI_NUMBER
from {{ source('main','PROVIDERS') }};

3) Marts aligned to mission tasks

models/marts/claims/claim_summary.sql

select
  strftime('%Y-%m-01', claim_date) as claim_month,
  CLAIM_TYPE,
  count(*) as claims,
  sum(allowed_amount) as total_allowed
from {{ ref('stg_claims') }}
where claim_date >= date('now','-12 months')
  and CLAIM_STATUS = 'PAID'
group by 1,2;

models/marts/claims/patient_summary.sql

select
  p.PATIENT_ID,
  strftime('%Y', c.claim_date) as report_year,
  strftime('%m', c.claim_date) as report_month,
  min(p.FIRST_NAME || ' ' || p.LAST_NAME) as patient_full_name,
  max(p.age_years) as patient_age,
  case
    when max(p.age_years) is null then 'UNKNOWN'
    when max(p.age_years) < 18 then 'CHILD'
    when max(p.age_years) between 18 and 64 then 'ADULT'
    else 'SENIOR'
  end as age_group,
  count(distinct c.CLAIM_ID) as total_claims,
  sum(c.allowed_amount) as total_allowed
from {{ ref('stg_patients') }} p
join {{ ref('stg_claims') }} c on c.PATIENT_ID = p.PATIENT_ID
group by 1,2,3;

models/marts/claims/provider_summary.sql

select
  pr.PROVIDER_ID,
  strftime('%Y', c.claim_date) as report_year,
  strftime('%m', c.claim_date) as report_month,
  min(pr.PROVIDER_NAME) as provider_name,
  count(distinct c.CLAIM_ID) as total_claims,
  count(distinct c.PATIENT_ID) as unique_patients,
  sum(c.allowed_amount) as total_allowed
from {{ ref('stg_providers') }} pr
join {{ ref('stg_claims') }} c on c.PROVIDER_ID = pr.PROVIDER_ID
where c.CLAIM_STATUS = 'PAID'
group by 1,2,3;

models/marts/claims/fct_readmissions.sql

with ip as (
  select
    c.PATIENT_ID,
    c.admission_date as admit_dt,
    c.discharge_date as discharge_dt,
    d.diagnosis_grp
  from {{ ref('stg_claims') }} c
  left join {{ ref('stg_claim_details') }} d
    on d.CLAIM_ID = c.CLAIM_ID
  where c.CLAIM_TYPE = 'INPATIENT'
    and c.discharge_date is not null
),
chain as (
  select
    PATIENT_ID,
    admit_dt,
    discharge_dt,
    diagnosis_grp,
    lead(admit_dt) over (partition by PATIENT_ID order by admit_dt) as next_admit_dt
  from ip
)
select
  strftime('%Y-%m-01', discharge_dt) as discharge_month,
  coalesce(diagnosis_grp, 'UNK') as diagnosis_grp,
  count(*) as discharges,
  sum(case when next_admit_dt is not null
            and (julianday(next_admit_dt) - julianday(discharge_dt)) between 0 and 30
           then 1 else 0 end) as readmits_30d,
  sum(case when next_admit_dt is not null
            and (julianday(next_admit_dt) - julianday(discharge_dt)) between 0 and 30
           then 1 else 0 end) * 1.0 / nullif(count(*), 0) as readmit_rate_30d
from chain
group by 1,2;

4) Tests and docs (YAML example)

models/staging/main/schema.yml

version: 2
models:
  - name: stg_claims
    config:
      materialized: table
    columns:
      - name: CLAIM_ID
        tests: [not_null, unique]
      - name: PATIENT_ID
        tests: [not_null]
      - name: PROVIDER_ID
        tests: [not_null]

  - name: stg_claim_details
    config:
      materialized: table
    columns:
      - name: CLAIM_DETAIL_ID
        tests: [not_null, unique]
      - name: CLAIM_ID
        tests: [not_null]

5) Execute and validate (dev-first)

Getting Started

Creating A Mission

Genesis Data Agents

Genesis Data Agent's Toolkit

Setup

Slack and Teams

Data Connections

Deployment Options

Overview

Purpose

Scope

Design

Typical use cases

Inputs and prerequisites

Core workflows

Default outputs

Tools and permissions

Safety and operational notes

Configuration

Example workflow (Healthcare Data Pipeline)

ERD (agent-driven: from specs & metadata → reproducible dbt assets)

Getting Started

Creating A Mission

Genesis Data Agents

Genesis Data Agent's Toolkit

Setup

Slack and Teams

Data Connections

Deployment Options

​Overview

Purpose

Scope

Design

​Typical use cases

​Inputs and prerequisites

​Core workflows

​Default outputs

​Tools and permissions

​Safety and operational notes

​Configuration

​Example workflow (Healthcare Data Pipeline)

​ERD (agent-driven: from specs & metadata → reproducible dbt assets)

Overview

Typical use cases

Inputs and prerequisites

Core workflows

Default outputs

Tools and permissions

Safety and operational notes

Configuration

Example workflow (Healthcare Data Pipeline)

ERD (agent-driven: from specs & metadata → reproducible dbt assets)