Snowflake

Drex for Snowflake is five SQL functions that call Drex, Nace's decision models, for each row and return calibrated probabilities as Snowflake values. See Questions for how to write them.

Optional arguments can be left off from the right, or every argument named: drex_noul(input => body, instructions => 'Is this a complaint?', model => 'drex-max'). Snowflake doesn't mix named and positional arguments in one call.

InstallCopy Anchor Link

The functions are vectorized Python UDFs built on the Python SDK. They reach Drex through an external access integration, and the API key is a Snowflake secret that the functions read, so it never appears in a query. Their source and setup script are nace-ai/drex-snowflake on GitHub, under the MIT license.

You need:

• A paid Snowflake account. Trial accounts can't create an external access integration.
• ACCOUNTADMIN, or a role that can create integrations, to run the setup script.
• A role to call the functions, which already exists and can use a warehouse. The script grants it the functions.
• An API key for these functions alone, so you can rotate it without touching anything else. Create one as shown in Authentication.

Then:

1. Clone the repository and copy its setup script into the ignored dist/ directory, so the key never lands in a tracked file:

   Copy Text

   git clone https://github.com/nace-ai/drex-snowflake.git
   cd drex-snowflake
   mkdir -p dist
   (umask 077 && cp sql/setup.sql dist/setup.local.sql)

2. In dist/setup.local.sql, replace <your nace_sk_ key> with the key and <role> with the role that will call the functions. Use an editor, not a shell command that contains the key.

3. From the repository's root, run it with the Snowflake CLI, then delete the copy:

   Copy Text

   snow sql -f dist/setup.local.sql
   rm dist/setup.local.sql

   It creates the database DREX with a network rule for console.nace.ai:443, the secret holding the key, the integration, a stage holding the functions' Python module, and the functions, then grants them to your role.

4. Check it:

   Copy Text

   SELECT drex.public.drex_noul('I was charged twice. Please refund one.', 'Is the customer asking for a refund?');

The functions install the SDK from PyPI through Snowflake's Artifact Repository, so the script also grants your role the database role SNOWFLAKE.PYPI_REPOSITORY_USER. For an account that can't use the Artifact Repository, the repository's docs/INSTALL.md shows how to install the SDK from a stage instead.

Running the script again upgrades the functions and keeps the key. To rotate the key:

ALTER SECRET drex.public.drex_api_key SET SECRET_STRING = 'nace_sk_...';

UsageCopy Anchor Link

The functions live in DREX.PUBLIC. The examples below call them unqualified, which works from another schema once the session's search path includes theirs:

ALTER SESSION SET SEARCH_PATH = '$current, $public, drex.public';

Otherwise call them as drex.public.drex_choice(...).

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. An empty string is sent and billed like any other input, so turn empty values into NULL first, for example NULLIF(TRIM(body), '').

SELECT
  ticket_id,
  drex_choice(body, 'Which team should handle this support ticket?', ['billing', 'technical', 'sales']) AS team
FROM tickets;

team:choice::STRING is the chosen label, team:probabilities holds every label with its probability, sorted by label, and team:confidence is Drex's confidence. Reading answers explains each field.

The input is text, or any VARIANT, OBJECT or ARRAY, which is sent as structured state, so a whole row works too: drex_noul(OBJECT_CONSTRUCT(*), ...). OBJECT_CONSTRUCT leaves out NULL values, so a row whose fields are all NULL is {}, which is sent too.

Labels, levels and questions are VARIANT arguments, and Snowflake doesn't convert text to them: write them as ARRAYs and OBJECTs, or wrap JSON text in PARSE_JSON.

Labels can carry descriptions, as an ARRAY of objects or an OBJECT of label to description:

drex_choice(body, 'Which team should handle this support ticket?', OBJECT_CONSTRUCT(
  'billing', 'Payments, invoices, refunds and charges',
  'technical', 'Bugs, crashes and errors in the product',
  'sales', NULL
))

Levels for drex_score are an ARRAY, since their order is the scale, and take no descriptions. drex_noul's criteria, if any, describe true and false: OBJECT_CONSTRUCT('true', 'The customer wants money back', 'false', NULL).

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. The result is Drex's whole response:

SELECT
  ticket_id,
  answers:answers:refund:noul::FLOAT AS refund,
  answers:answers:team:choice::STRING AS team
FROM (
  SELECT ticket_id, drex_decide(
    OBJECT_CONSTRUCT('subject', subject, 'body', body),
    PARSE_JSON('{
      "refund": {"type": "noul", "instructions": "Is the customer asking for a refund?"},
      "team": {"type": "choice", "instructions": "Which team should handle this ticket?",
               "criteria": {"billing": "Payments, invoices and refunds", "technical": "Bugs and errors", "sales": null}}
    }')
  ) AS answers
  FROM tickets
);

Use thresholdsCopy Anchor Link

Every answer carries its probabilities, so a query decides what is confident enough and leaves the rest for a person or another model:

CREATE TABLE ticket_teams AS
SELECT ticket_id, drex_choice(body, 'Which team should handle this support ticket?', ['billing', 'technical', 'sales']) AS team
FROM tickets;

SELECT ticket_id, team:choice::STRING AS team
FROM ticket_teams
WHERE team:confidence >= 0.8;

Choose the threshold from a labelled sample; Reading answers covers what each probability means.

Read imagesCopy Anchor Link

Drex Max reads images. drex_decide_image takes a scoped file URL of a staged PNG, JPEG or WebP file, or an ARRAY of 1 to 8 of them; prompts refer to them as "Image 1", "Image 2" and so on. It uses drex-max unless you name another model that reads images.

SELECT
  relative_path,
  drex_decide_image(
    '',
    BUILD_SCOPED_FILE_URL(@scans, relative_path),
    PARSE_JSON('{"kind": {"type": "choice", "instructions": "What kind of document is Image 1?",
                          "criteria": {"invoice": "A bill or invoice", "receipt": "A proof of payment", "other": null}}}')
  ):answers:kind:choice::STRING AS kind
FROM DIRECTORY(@scans);

The function reads the files with the calling role's privileges, so that role needs READ on the stage. A stage file URL (BUILD_STAGE_FILE_URL) is refused. A row whose request would exceed the 4 MiB body limit returns NULL without being sent, 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. Pass 'drex-max' as model for Drex Max. drex-v2.0 costs $0.06 and Drex Max $0.03 per million input tokens; see Pricing.

The functions check each batch of rows before sending 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 labels and questions must follow the rules in Questions and the model's limits. A query that fails a check returns an error.

Concurrency and rate limitsCopy Anchor Link

Snowflake calls each function on batches of up to 64 rows (16 for drex_decide_image), and each batch sends up to 8 requests at once. A warehouse runs several batches at once: an X-Small ran 8 in Nace's tests, so a query had about 64 requests in flight, and a larger warehouse runs more. That is more than an account's in-flight limit:

An account over its limit gets a 429 and the wait, and the functions retry after it, but a row whose retries all run out returns NULL. To stay within the limit, lower the number in each function's handler in sql/setup.sql, concurrency=8, and run the script again. On an X-Small, concurrency=2 keeps a query within 16 requests in flight and concurrency=1 within 8. Every API key on an account shares its limits (see Scope), so leave room for other clients using the same account.

ErrorsCopy Anchor Link

• 401, 402 and 422 fail the query. A 422 message lists each issue's path and message; a 402 links to Billing.
• 408, 429, 500, 502, 503, 504 and 529 are retried after retry-after-ms, at most 5 attempts within 2 minutes. A row whose retries run out returns NULL.
• A row Drex refuses on its own, such as one over the token limit, and an image that isn't PNG, JPEG or WebP, return NULL with a warning. The warning never quotes the row. Snowflake records warnings in your account's event table only once logging is on, which it isn't by default: run ALTER DATABASE drex SET LOG_LEVEL = WARN.
• Snowflake stops a batch after 180 seconds, so a row a batch hasn't sent by then returns NULL.

Treat NULL as no answer, not as a negative one: WHERE answer IS NULL finds the rows to look at again. Only a 200 is billed, so a retried or failed row costs nothing. See Errors and retries.

What gets sent, and billedCopy Anchor Link

Every row Snowflake evaluates a function on is a request, and every request Drex answers is billed, whether or not the query returns that row. Your warehouse's running time is billed by Snowflake as usual.

• A WHERE on an answer, or ORDER BY ... LIMIT, sends every row it reads. Filter and limit the rows in a subquery, then call the function on its result.
• Running a query again, or reading a view again, sends its rows again, and Snowflake may retry part of a query on its own. Keep the answers with CREATE TABLE ... AS SELECT or INSERT INTO ... SELECT and read the table.

CREATE TABLE ticket_teams AS
SELECT ticket_id, drex_choice(body, 'Which team should handle this support ticket?', ['billing', 'technical', 'sales']) AS team
FROM (SELECT * FROM tickets ORDER BY ticket_id DESC LIMIT 100);

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.

Alongside Cortex AI FunctionsCopy Anchor Link

Snowflake's own Cortex AI Functions, such as AI_COMPLETE, AI_CLASSIFY and AI_FILTER, run in the same queries as Drex's functions. Use Drex where you need a probability for every option, for example to route only confident answers automatically and send the rest for review, and Cortex for generating or extracting text.