Assist dbt mode for Analytics Engineers

DataOps treats data as a product. The transformation step is crucial here: it turns raw data into structured, consumable data products. DataOps.live supports this with its MATE engine, which enables teams to define transformation models and write tests directly in code.

To make this even easier, DataOps.live Assist introduces a specialized DBT mode. It helps data and analytics engineers generate dbt models and tests using simple natural language prompts.

This guide shows how to use Assist to generate a dbt model and corresponding tests for analyzing customer order behavior.

We start by prompting:

"Create a simple dbt model that summarizes customer order behavior by joining customer and order data tables."

In response, Assist creates a new file: models/customer_order_summary.sql and generates the following SQL model.

models/customer_order_summary.sql

-- /modelling/models/analytics/customer_order_summary.sql

{{ config(
    alias = 'CUSTOMER_ORDER_SUMMARY',
    materialized = 'table'
) }}

SELECT
    c.C_CUSTKEY AS customer_id,
    c.C_NAME AS customer_name,
    c.C_MKTSEGMENT AS market_segment,
    COUNT(o.O_ORDERKEY) AS order_count,
    SUM(o.O_TOTALPRICE) AS total_spend,
    AVG(o.O_TOTALPRICE) AS avg_order_value
FROM
    {{ ref('samples_customer') }} c
LEFT JOIN
    {{ ref('samples_orders') }} o
    ON c.C_CUSTKEY = o.O_CUSTKEY
GROUP BY
    c.C_CUSTKEY,
    c.C_NAME,
    c.C_MKTSEGMENT
ORDER BY
    total_spend DESC;

The SQL model above calculates the order_count, average_spend, and total_spend for each customer in the database. Assist also follows dbt best practices by referencing existing models, sample_customer and sample_orders, using ref() rather than recreating the logic from scratch.

Once the SQL model is in place, the next step is to add tests. In dbt, you typically define tests in two ways:

• Define simple column constraints/tests directly in the YAML configuration.
• Write custom tests using SQL macros and then integrate into the YAML file.

After generating the dbt model, Assist automatically creates a corresponding YAML configuration for the model inside the analytics.yml file with column-level tests.

/modelling/models/analytics/analytics.yml

version: 2

models:
  - name: customer_order_summary
    description: "A summary of customer order behavior with key metrics"
    columns:
      - name: customer_id
        description: "Unique identifier for the customer"
        tests:
          - not_null
          - unique

      - name: customer_name
        description: "Name of the customer"
        tests:
          - not_null

      - name: market_segment
        description: "Market segment the customer belongs to"

      - name: order_count
        description: "Total number of orders placed by the customer"

      - name: total_spend
        description: "Total amount spent by the customer across all orders"
        tests:
          - not_null

      - name: avg_order_value
        description: "Average value of orders placed by the customer"
        tests:
          - not_null

It defines basic integrity checks, for example, customer_id must be unique and not null, and total_spend must not be null.

Custom SQL Test

You can then prompt Assist to create a custom SQL-based test:

"Create a simple SQL-based test for the model and add it to the YAML file."

It creates a custom test macro in tests/test_total_spend_consistency.sql:

tests/test_total_spend_consistency.sql

{% macro test_total_spend_consistency(model, column_name) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} < avg_order_value
  AND order_count > 0

{% endmacro %}

This test ensures that total_spend is never less than the computed avg_order_value (which would indicate a logic or aggregation error if order_count > 0).

Assist updates the YAML to include the custom test:

/modelling/models/analytics/analytics.yml

version: 2

models:

- name: customer_order_summary
    description: "A summary of customer order behavior with key metrics"
    columns:

    - name: customer_id
        description: "Unique identifier for the customer"
        tests:
            - not_null
            - unique

    - name: customer_name
        description: "Name of the customer"
        tests:
            - not_null

    - name: market_segment
        description: "Market segment the customer belongs to"

    - name: order_count
        description: "Total number of orders placed by the customer"

    - name: total_spend
        description: "Total amount spent by the customer across all orders"
        tests:
            - not_null
            - total_spend_consistency

    - name: avg_order_value
        description: "Average value of orders placed by the customer"

Explaining and Documenting Models

DBT mode also explains and documents the models it creates.

For example, when you prompt: “Explain the model located at /modelling/models/analytics/customer_order_summary.sql,” it explains the model's data sources, transformations applied, output columns, and business use cases, as the image below shows:

You can also prompt Assist to create documentation for dbt models. Example prompt: “Create a YAML documentation for the model located at /modelling/models/analytics/customer_order_summary.sql.”

This generates the YAML file customer_order_summary.yml in the model directory and asks for permission to save it. When you click Save, it stores the file.