dbt Schema.yml

The schema.yml file (or any .yml file in your models/ folder) is where you document and test your models. It holds model descriptions, column descriptions, generic tests, source definitions, and contract information. Every well-maintained dbt project has schema YAML files alongside its SQL models.

File Location and Naming

models/
  staging/
    stg_orders.sql
    stg_customers.sql
    schema.yml        ← documents stg_orders and stg_customers
    sources.yml       ← declares source tables (can be in same file or separate)
  marts/
    fct_orders.sql
    dim_customers.sql
    schema.yml        ← documents fct_orders and dim_customers

The file does not have to be named schema.yml. Any .yml file in the models/ folder (at any depth) is picked up by dbt. Common names are schema.yml, _models.yml, and models.yml.

Complete schema.yml Example

version: 2
models:
  - name: stg_orders
    description: >
      One row per order from the Shopify storefront.
      Cancelled orders are excluded.
      Amounts are converted from cents to USD.
    config:
      materialized: view
      tags: ['staging', 'daily']
    columns:
      - name: order_id
        description: "Primary key. Shopify order ID."
        tests:
          - not_null
          - unique
      - name: customer_id
        description: "Foreign key to stg_customers.customer_id."
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: order_date
        description: "Date the order was placed. Truncated to date precision."
        tests:
          - not_null
      - name: amount_dollars
        description: "Order total in USD. Converted from Shopify cents field."
        tests:
          - not_null
      - name: status
        description: "Current order status."
        tests:
          - not_null
          - accepted_values:
              values: ['pending', 'processing', 'completed', 'cancelled']

Model-Level Config in schema.yml

You can set model configuration inside the YAML file instead of (or in addition to) the SQL config block:

models:
  - name: fct_orders
    config:
      materialized: table
      schema: reporting
      tags: ['mart', 'finance']
      meta:
        owner: finance-team@company.com
        refresh_schedule: daily
    columns:
      - name: order_id
        data_type: integer
        tests:
          - not_null
          - unique

Documenting Multiple Models in One File

version: 2
models:
  - name: stg_orders
    description: "Staged orders from Shopify"
    columns:
      - name: order_id
        tests: [not_null, unique]
  - name: stg_customers
    description: "Staged customers from CRM"
    columns:
      - name: customer_id
        tests: [not_null, unique]
      - name: email
        tests: [not_null]
  - name: stg_products
    description: "Staged products from inventory system"
    columns:
      - name: product_id
        tests: [not_null, unique]
      - name: price_dollars
        tests: [not_null]

Test Shorthand Syntax

Simple tests that take no arguments can be written as a list of strings:

# Long form
tests:
  - not_null
  - unique

# Shorthand (equivalent)
tests: [not_null, unique]

Test Configuration Inside schema.yml

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['pending', 'completed', 'cancelled']
          config:
            severity: warn          ← warn only, don't fail run
            tags: ['data_quality']
      - not_null:
          config:
            severity: error         ← fail run if null found
            store_failures: true    ← save failing rows to warehouse

Sources in schema.yml

Source definitions can live in a separate sources.yml file or in the same schema.yml:

version: 2
sources:
  - name: ecommerce
    database: raw_data
    schema: shopify
    tables:
      - name: orders
        columns:
          - name: id
            tests: [not_null, unique]
models:
  - name: stg_orders
    description: "Staged from ecommerce.orders"
    columns:
      - name: order_id
        tests: [not_null, unique]

Using doc() for Long Descriptions

# Create a .md file alongside your models
# models/staging/staging_docs.md
{% docs stg_orders_description %}
One row per order placed on the Shopify storefront.

**Exclusions:**
- Cancelled orders (removed during staging)
- Test orders from the store admin panel

**Transformations:**
- `amount_cents` divided by 100 to produce `amount_dollars`
- `status` lowercased for consistency
{% enddocs %}

# Reference in schema.yml
- name: stg_orders
  description: "{{ doc('stg_orders_description') }}"

Best Practices

• Keep one schema.yml per folder — do not put all models in one giant file
• Add a description to every model in marts/ at minimum
• Add not_null and unique to every primary key column
• Add relationships tests to every foreign key column
• Use doc() blocks for descriptions longer than two sentences