dbt Test Definitions

dbt Test Definitions

Context

Writing dbt schema tests manually for every column of every model is repetitive and error-prone. This prompt generates a complete schema.yml test block from a table description and column definitions, covering the standard dbt test types (not_null, unique, accepted_values, relationships) and flagging columns where custom expression tests or dbt-utils tests would add value.

Review the generated YAML before adding it — some tests (especially relationships) require the referenced model to exist. For accepted_values, always verify the values match your actual domain data.

The Prompt

Generate dbt schema test definitions (in schema.yml format) for the following table.

Include tests for: not_null, unique, accepted_values, relationships, and any custom logic
that can be expressed as a dbt test or a simple SQL expression test.

Table name: [TABLE NAME]
Model description: [ONE SENTENCE ON WHAT THIS MODEL CONTAINS]

Columns:
[LIST COLUMNS WITH TYPE AND DESCRIPTION, E.G.:]
- customer_id (STRING): Primary identifier. Must be unique and non-null.
- country_code (STRING): ISO 3166-1 alpha-2. Must be a valid 2-letter country code.
- created_at (TIMESTAMP): Record creation timestamp. Must not be in the future.
- status (STRING): Customer status. Must be one of: ACTIVE, INACTIVE, PENDING.
- parent_customer_id (STRING): Self-referential FK to customer_id. Nullable.

Usage Instructions

1. Open build-cli in your terminal.
2. Copy the prompt above.
3. Replace [TABLE NAME] with the dbt model name.
4. List every column with its data type and a plain-language description of business rules that apply to it.
5. If you use a custom dbt package (e.g., dbt-utils, dbt-expectations), tell the AI which packages are available so it can use the appropriate test syntax.
6. Copy the generated YAML into the appropriate schema.yml file and validate with dbt test --select [model_name].

Example Output

The AI will produce a schema.yml block such as:

models:
  - name: dim_customer
    description: "Dimension table containing one row per active customer."
    columns:
      - name: customer_id
        tests:
          - not_null
          - unique
      - name: country_code
        tests:
          - not_null
          - accepted_values:
              values: ['AD', 'AE', 'AF']  # AI will list valid ISO codes
      - name: created_at
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "<= current_timestamp"
      - name: status
        tests:
          - not_null
          - accepted_values:
              values: ['ACTIVE', 'INACTIVE', 'PENDING']
      - name: parent_customer_id
        tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_id

Note: The AI will guess valid values from your description. Always verify accepted_values lists against your actual data before using in production.