dbt

drex is a dbt package whose macros call Drex, Nace's decision models, from dbt models. Each macro compiles to the Drex function your warehouse already has, so one model file runs on Snowflake, BigQuery, Databricks, Postgres, ClickHouse and DuckDB and returns calibrated probabilities in the warehouse's own types. See Questions for how to write them.

model is optional, and so are drex_noul's instructions and criteria: leave them out, or pass none.

Before you startCopy Anchor Link

The macros don't call Drex themselves: they compile to the Drex functions installed in your warehouse, which send each row with that warehouse's key. Install your warehouse's integration first:

dbt never sees the key. Plain dbt-spark isn't supported, since drex-spark's Spark SQL functions exist only in a session that ran drex_spark.register(spark). On Databricks, use dbt-databricks, or a dbt Python model calling drex_spark.functions. Any other adapter fails to compile, including those built on dbt-postgres such as Redshift and Materialize, whose databases don't have the Postgres functions.

InstallCopy Anchor Link

The package needs dbt 1.8 or later. Its source is nace-ai/dbt-drex on GitHub, under the MIT license. Add it to packages.yml:

packages:
  - git: "https://github.com/nace-ai/dbt-drex.git"
    revision: v0.1.2

Then run dbt deps. To upgrade, change revision to the new release and run dbt deps again.

If the functions aren't where the table above says, name their location in dbt_project.yml:

vars:
  drex_database: analytics   # Snowflake database, Databricks catalog, or BigQuery project
  drex_schema: ml            # schema or dataset

DuckDBCopy Anchor Link

On DuckDB, dbt-duckdb loads the extension and creates its nace secret when it connects. This needs dbt-duckdb 1.9.0 or later and DuckDB 2.0, which needs Python 3.11 or later. In profiles.yml:

my_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: my_project.duckdb
      extensions:
        - name: drex
          repo: community
      secrets:
        - type: nace
          provider: env

with NACE_API_KEY set where dbt runs. MotherDuck can't run the extension: see DuckDB.

UsageCopy Anchor Link

Ask one question per rowCopy Anchor Link

Each non-NULL row is one Drex request. A NULL input returns NULL and isn't sent or billed.

select
    ticket_id,
    {{ drex.drex_choice("body", "Which team should handle this support ticket?", {
        "billing": "Payments, invoices, refunds and charges",
        "technical": "Bugs, crashes and errors in the product",
        "sales": none,
    }) }} as team,
    {{ drex.drex_noul("body", "Is the customer asking for a refund?") }} as refund,
    {{ drex.drex_score("body", "How urgent is this ticket?", ["can wait", "this week", "today"]) }} as urgency
from {{ ref("tickets") }}

input is SQL, such as a column name. The instructions, labels, questions and model are Jinja values, since every warehouse requires them to be constants. Labels are a list, a list of {"label": ..., "description": ...}, or a dict of label to description (none for no description). A choice or score takes 2 to 255 unique labels, sent in the order written. A score's levels take no descriptions, so describe the scale in the instructions. drex_noul criteria labels are true and false. In YAML, such as vars, dbt reads unquoted yes, no, on and off as true and false, so quote those labels.

The package checks them when the model compiles, with the warehouse functions' rules: a choice with one label, a score level with a description, or a question id of __proto__ fails dbt compile, run and build, names the model file, and sends nothing. On ClickHouse, drex_choice and drex_score take plain labels; ask a choice with descriptions through drex_decide.

Ask several questions at onceCopy Anchor Link

drex_decide sends every question in one request per row, so the row is sent and billed once instead of once per question. It takes 1 to 512 questions. A dict state becomes a JSON object of the row's fields, which Drex reads as structured state.

select
    ticket_id,
    {{ drex.drex_decide({"subject": "subject", "body": "body"}, {
        "refund": {"type": "noul", "instructions": "Is a refund needed?"},
        "team": {"type": "choice", "instructions": "Which team?", "criteria": {"billing": "Payments", "technical": none}},
    }) }} as decision
from {{ ref("tickets") }}

A dict state is never NULL, so every row is sent and billed, even one whose fields are all NULL: leave those rows out with a where. The result is Drex's whole response, in the warehouse's JSON type: OBJECT on Snowflake, JSON on BigQuery and DuckDB, jsonb on Postgres, and JSON text on Databricks and ClickHouse. state can also be SQL of that type.

Use thresholdsCopy Anchor Link

A choice or score result's type differs by warehouse (OBJECT, JSON, STRUCT, jsonb or Tuple), so read it through the accessors, which compile to each warehouse's field access:

select answers.ticket_id, {{ drex.drex_choice_value("answers.team") }} as team
from {{ ref("ticket_teams") }} as answers
where {{ drex.drex_confidence("answers.team") }} >= 0.8

Qualify the column, as here: on ClickHouse an alias is visible in the whole query, so after as team an unqualified team is the label. drex_score_value reads a score. Each accessor returns NULL for a row with no answer; on Databricks and ClickHouse that row's result is a struct whose fields are NULL rather than NULL itself, so test it through the accessors. Treat NULL as no answer, not as a "no". Reading answers explains each field.

Read imagesCopy Anchor Link

Drex Max reads images. drex_decide_image sends 1 to 8 PNG, JPEG or WebP images with Drex Max unless model names another image model. images is SQL in each warehouse's form: staged files on Snowflake, signed Cloud Storage URLs on BigQuery, ARRAY<BINARY> on Databricks, bytea[] on Postgres and image bytes on ClickHouse, as each warehouse's page shows. drex-duckdb has no drex_decide_image, so ask about images with prompt_drex in a DuckDB-only model.

ModelsCopy Anchor Link

The default model is drex-v2.0, which reads text and JSON. Pass model="drex-max" for Drex Max. drex-v2.0 costs $0.06 and Drex Max $0.03 per million input tokens; see Pricing.

To choose the model for a whole project, set drex_model in dbt_project.yml. drex_decide_image keeps Drex Max unless its own model says otherwise.

vars:
  drex_model: drex-v2.0

Incremental modelsCopy Anchor Link

An incremental model can send only the rows whose input, question or model changed, by keeping the fingerprint each row was answered with:

{{ config(materialized="incremental", unique_key="ticket_id") }}

{% set team = {"instructions": "Which team should handle this ticket?", "labels": ["billing", "technical", "sales"]} %}

with tickets as (
    select ticket_id, body, {{ drex.drex_fingerprint("body", team) }} as drex_fingerprint
    from {{ ref("tickets") }}
)

select
    tickets.ticket_id,
    tickets.drex_fingerprint,
    {{ drex.drex_choice("tickets.body", team.instructions, team.labels) }} as team
from tickets
{% if is_incremental() %}
left join {{ this }} as answered on answered.ticket_id = tickets.ticket_id
where answered.drex_fingerprint is null or answered.drex_fingerprint <> tickets.drex_fingerprint
{% endif %}

Changing the instructions, labels or model sends every row again; nothing else does. Pass drex_fingerprint the same model as the Drex macro. drex_decide_image doesn't read var('drex_model'), so for it pass the same explicit model to both macros, such as model="drex-max": otherwise changing the var changes the fingerprint, and the next run sends every image again. Rows deleted from the source stay in the model.

A row whose answer is NULL (Drex refused it, or its retries ran out) keeps its fingerprint too, so it isn't asked again until its input, question or model changes. Drex bills only the requests it answers, so to ask those rows again on each run, add this to the where:

or (tickets.body is not null and {{ drex.drex_choice_value("answered.team") }} is null)

What gets sent, and billedCopy Anchor Link

Every row a Drex macro runs on is a request, and every request Drex answers is billed. Drex keeps no answer cache.

• Every read of a view or an ephemeral model sends its rows again. The package warns when a Drex macro compiles into a view, ephemeral, materialized_view or dynamic_table model; materialize it as table or incremental.
• dbt run of a table model sends all its rows again, and so does --full-refresh of an incremental one.
• dbt show --select runs the model's query rather than reading its table, so it sends rows again, at least the ones it shows. dbt compile and dbt parse send nothing.
• A warehouse that runs work again bills again, such as a retried task. Each warehouse's page says when that happens.

Requests show in your usage under the warehouse's integration, such as drex-snowflake or drex-databricks, since the warehouse function sends them.

Concurrency and rate limitsCopy Anchor Link

How many requests a model sends at once is set by each warehouse's integration, not by dbt: see its page. Every API key on an account shares its limits (see Scope), so several dbt threads and warehouses using one account share them too:

An account over its limit gets a 429 and the wait, and the warehouse functions retry after it.

ErrorsCopy Anchor Link

The package's checks fail the model when it compiles, before anything is sent. When it runs, the warehouse function decides: 401, 402, a 422 about the questions or model, and an unknown model fail the query; a row Drex refuses on its own, or whose retries run out, returns NULL with a warning in the warehouse's log. Each warehouse's page has its rules; see also Errors and retries.

CostCopy Anchor Link

Each row is billed for its input tokens at the model's price, and output tokens aren't billed. For 10,000,000 rows:

The same row counts a different number of tokens on each model, so read usage.input_tokens from drex_decide for a sample of rows on the model you plan to use before running a whole table.