dbt-core-mcp: Instructions file for GitHub Copilot

dbt Unit Testing Guidelines

File Organization

Rule: Keep unit tests in separate unit_tests/ directory, mirroring model structure

models/
  ├── staging/
  │   └── stg_customers.sql
  └── marts/
      └── customers.sql

unit_tests/
  ├── staging/
  │   └── stg_customers_unit_tests.yml
  └── marts/
      └── customers_unit_tests.yml

Configuration (dbt_project.yml):

model-paths: ["models", "unit_tests"]

TDD Workflow for Unit Tests

1. Create Test First (Red Phase)

Step 1: Inspect the model to understand dependencies

get_resource_info(name="customers", include_compiled_sql=true)

Step 2: Query sample data for realistic fixtures

query_database("SELECT * FROM {{ ref('stg_customers') }} LIMIT 3")
query_database("SELECT * FROM {{ ref('stg_orders') }} LIMIT 5")

Step 3: Create test YAML with:

• Descriptive name: test_<behavior> (e.g., test_customer_with_single_order)
• Clear description
• Minimal data covering the test case
• Expected output

Step 4: Run the specific test

test_models(select="test_name:test_customer_with_single_order")

2. Implement/Fix Model (Green Phase)

Edit or create the dbt model to make the test pass.

3. Refactor

Clean up the model while keeping tests green.

Test Design Principles

Minimal Data: Only include columns/rows needed for the specific test case

Fixture Reuse with YAML Anchors: Reduce duplication by anchoring common input data

# Define reusable fixtures at the top
_base_customers: &customer_input
  input: ref('stg_customers')
  rows:
    - {customer_id: 1, first_name: 'Alice', last_name: 'Smith'}
    - {customer_id: 2, first_name: 'Bob', last_name: 'Jones'}

unit_tests:
  - name: test_basic_aggregation
    model: customers
    given:
      - *customer_input      # Reuse the fixture as-is
      - input: ref('stg_orders')
        rows: [...]
  
  - name: test_with_different_customers
    model: customers
    given:
      - input: ref('stg_customers')  # Just write it out if data is different
        rows:
          - {customer_id: 99, first_name: 'Different', last_name: 'Customer'}
      - input: ref('stg_orders')
        rows: [...]