BigQuery

The Drex integration for BigQuery deploys Drex, Nace's decision models, as BigQuery remote functions in your own Google Cloud project. Each function answers a typed question about each row and returns calibrated probabilities. See Questions for how to write them.

DeployCopy Anchor Link

The function and its Terraform module are nace-ai/drex-bigquery on GitHub, under the MIT license.

You need a Google Cloud project with billing, Terraform 1.5 or later and the gcloud CLI, signed in with gcloud auth login and gcloud auth application-default login (Terraform uses the second). Create a key as shown in Authentication.

Store the key in Secret Manager. The module only reads the secret, so the key never enters Terraform state:

gcloud services enable secretmanager.googleapis.com --project=my-project
gcloud secrets create nace-api-key --project=my-project --replication-policy=automatic
gcloud secrets versions add nace-api-key --project=my-project --data-file=-   # paste the key, then Ctrl-D

Then add the module to a main.tf in a new directory, and run terraform init and terraform apply:

module "drex" {
  source = "git::https://github.com/nace-ai/drex-bigquery.git//terraform?ref=v0.1.0"

  project_id     = "my-project"
  region         = "us-central1" # a region in the dataset's location
  location       = "US"          # where your tables are
  api_key_secret = "nace-api-key"
}

The module deploys a Cloud Run function and creates a BigQuery connection, the dataset drex and its five functions. Only the connection's service account can call the function. People who run queries need the BigQuery Connection User role on the connection, and read access to the drex dataset.

The first apply takes about ten minutes, including waits for new IAM grants to take effect. If it still fails with "Access to bucket gcf-v2-sources-... denied", a grant took longer than usual: run terraform apply again. For a few minutes after the first apply, queries can fail with a permission error until Cloud Run accepts the connection's grant; run them again.

To upgrade, change ref= in source to the new release's tag and run terraform init -upgrade and terraform apply. Releases lists each one with its changes.

Rotate the keyCopy Anchor Link

Add a new version to the secret. The function reads the key when an instance starts, so running instances keep the old one, and terraform apply changes nothing when only the secret changed: keep the old key valid until the instances have been replaced, or deploy a new revision of the function's Cloud Run service.

UsageCopy Anchor Link

BigQuery remote functions take no optional arguments, so every argument is required. model is always last, and NULL means the default. Labels and questions are JSON.

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.choice(
    body,
    'Which team should handle this support ticket?',
    JSON '{"billing": "Payments, invoices, refunds and charges", "technical": "Bugs, crashes and errors", "sales": null}',
    NULL
  ) AS team,
  drex.noul(body, 'Is the customer asking for a refund?', NULL, NULL) AS refund
FROM support.tickets;

labels takes a JSON array of labels, an array of {"label", "description"} objects, or an object of label to description. levels takes an array of plain labels, lowest first. labels and levels take 2 to 255 unique labels. criteria is NULL, or JSON describing true and false; a noul needs instructions or a described true or false. probabilities always holds every option: a choice's are sorted by label, a score's by index. Reading answers explains each field.

Read a field of a JSON result with BigQuery's JSON functions:

SELECT ticket_id, JSON_VALUE(team.choice) AS team, LAX_FLOAT64(team.confidence) AS confidence
FROM support.ticket_teams
WHERE LAX_FLOAT64(team.confidence) >= 0.8;

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 JSON state, so a whole row works too, and it returns Drex's response, with the answers under answers:

SELECT
  ticket_id,
  drex.decide(
    TO_JSON(t),
    JSON '''{
      "urgency": {"type": "score", "instructions": "How urgent is this ticket?", "criteria": ["can wait", "this week", "today"]},
      "refund": {"type": "noul", "instructions": "Is the customer asking for a refund?"}
    }''',
    NULL
  ) AS answers
FROM support.tickets AS t;

Read imagesCopy Anchor Link

Drex Max reads images. drex.decide_image reads them from a Cloud Storage object table through signed URLs, and sends their bytes to Drex. Create the object table with the module's connection, and grant the connection's service account (the module's connection_service_account output) roles/storage.objectViewer on the bucket.

SELECT
  uri,
  drex.decide_image(
    JSON '""',
    TO_JSON(signed_url),
    JSON '{"kind": {"type": "choice", "instructions": "What kind of document is Image 1?", "criteria": ["invoice", "receipt", "other"]}}',
    NULL
  ) AS answer
FROM EXTERNAL_OBJECT_TRANSFORM(TABLE scans.objects, ['SIGNED_URL']);

Pass up to 8 images as a JSON array, such as TO_JSON(ARRAY_AGG(signed_url ORDER BY uri)); prompts refer to them as "Image 1", "Image 2" and so on. More than 8, or a URL that isn't a signed Cloud Storage URL, fails the query. PNG, JPEG and WebP are accepted; an image that can't be read or is another format returns NULL. A row whose request would exceed the 4 MiB body limit returns NULL, so resize large images first. Drex Max's image limits list the rest.

ModelsCopy Anchor Link

The default model is drex-v2.0, which reads text and JSON, except for drex.decide_image, whose default is drex-max. Pass 'drex-max' as the last argument for Drex Max. drex-v2.0 costs $0.03 and Drex Max $0.03 per million input tokens; see Pricing.

The function checks a batch before it sends any of it: the model must be one that GET /v1/models lists for your key, images need a model that reads them, and the questions must follow Drex's request limits: 1 to 512 questions with unique ids, and 2 to 255 unique labels or levels. A query that fails a check returns an error. A row over the model's token limit isn't checked in advance: Drex refuses it, and it returns NULL.

Concurrency and rate limitsCopy Anchor Link

BigQuery sends the function batches of rows, at most 50 at a time (max_batching_rows; image_max_batching_rows, 1, for drex.decide_image). Each batch has up to 8 requests in flight, and the module runs one instance that answers one batch at a time, so a deployment sends at most 8 requests at once by default. That fits the in-flight limit of a free account:

On a paid account, raise it with the module's variables. The requests in flight are at most max_instances × container_concurrency × inflight_per_request:

module "drex" {
  # ...
  max_instances        = 2
  inflight_per_request = 8
}

Every API key on an account shares its limits (see Scope), so leave room when other clients use the same account. A request over the limit gets a 429 with the wait, and the function retries after it. BigQuery runs at most 10 queries with remote functions at once in a project.

ErrorsCopy Anchor Link

• 401, 402, a 422 about the questions or model, an unknown model, a TLS failure, and GET /v1/models unanswered after its retries fail the query. A 422 message lists each issue's path and message; a 402 links to Billing.
• A 422 about the row alone, such as a row over the model's limits, returns NULL for that row, as does a row whose request would be over 4 MiB, and any other status. The query goes on, with a warning in the function's Cloud Run logs.
• No response, 408, 429, 500, 502, 503, 504 and 529 are retried within the batch, after retry-after-ms (or retry-after, at most 30 seconds), at most 5 attempts within 2 minutes. A row whose retries run out returns NULL, with a warning in the logs.
• Each request times out after 60 seconds, and a batch answers within the function's timeout less 60 seconds (240 by default, timeout_seconds): rows it hasn't answered by then return NULL.

The function never answers BigQuery with an error BigQuery retries, so a batch with answered rows is never sent again because of one failed row. See Errors and retries.

What gets sent, and billedCopy Anchor Link

Every row BigQuery evaluates a function on is a request, and every request Drex answers with a 200 is billed.

• BigQuery can send a batch again even after the function answered it, and those rows are billed again.
• Running a query again sends its rows again. BigQuery never caches the result of a query that calls a remote function.
• Keep the answers with CREATE TABLE ... AS SELECT and read the table:

CREATE TABLE support.ticket_teams AS
SELECT ticket_id, drex.choice(body, 'Which team should handle this support ticket?', JSON '["billing", "technical", "sales"]', NULL) AS team
FROM support.tickets
WHERE created_at >= '2026-10-01';

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.