SQL and notebook patterns

Replace catalog, schema, table, endpoint, and version placeholders after discovery. Prefer task-specific functions over ai_query.

SQL

SELECT
  review_id,
  ai_summarize(review_text) AS summary,
  ai_analyze_sentiment(review_text) AS sentiment
FROM <project_catalog>.silver.reviews
LIMIT 10;

Pin structured task-specific outputs:

SELECT
  document_id,
  ai_extract(
    text,
    '{"invoice_id":{"type":"string"},"total":{"type":"number"}}',
    map('version', '2.1', 'enableConfidenceScores', 'true')
  ) AS invoice
FROM <project_catalog>.silver.documents
LIMIT 10;

In the validated workspace, ai_extract expects a top-level field map, not a complete JSON Schema document with type, properties, or additionalProperties around it. Materialize the VARIANT once, then cast it into the business schema separately:

CREATE TABLE IF NOT EXISTS <project_catalog>.silver.invoice_extract_stage_<run_suffix>
USING DELTA AS
SELECT source_path,
       ai_extract(text,
         '{"invoice_id":{"type":"string"},"total":{"type":"number"}}',
         map('version', '2.1', 'instructions', 'Extract explicit values only; use null when absent.')) AS extracted
FROM <project_catalog>.silver.invoice_text_<run_suffix>;

SELECT source_path,
       named_struct(
         'invoice_id', try_cast(extracted:response:invoice_id:value AS STRING),
         'total', try_cast(extracted:response:total:value AS DECIMAL(18,2))
       ) AS record,
       nullif(try_cast(extracted:error_message AS STRING), '') AS extraction_error
FROM <project_catalog>.silver.invoice_extract_stage_<run_suffix>;

Version 2.1 returns a VARIANT envelope whose field values live under response.<field>.value; nested arrays retain the same value envelope. Inspect the representative output before writing casts. JSON null inside VARIANT is not SQL NULL; use is_variant_null(value) when distinguishing the two. For ai_classify, pass short canonical label values. Each label must stay within the workspace limit of 50 characters. Put the policy and label descriptions in content or supported instructions, not inside long label strings:

SELECT ai_classify(
  concat('Apply this routing policy: ', policy, '\nMessage: ', message),
  array('billing', 'access', 'software', 'equipment', 'document_request')
) AS queue
FROM <project_catalog>.silver.support_messages;

Use row-level error capture and structured output for ai_query:

SELECT
  id,
  ai_query(
    'endpoint-name',
    concat('Extract the issue and urgency: ', text),
    responseFormat => 'STRUCT<issue:STRING, urgency:STRING>',
    failOnError => false
  ) AS inference
FROM <project_catalog>.silver.tickets
LIMIT 10;

SQL notebook source

-- Databricks notebook source
CREATE OR REPLACE TEMP VIEW validation_sample AS
SELECT * FROM <project_catalog>.silver.reviews LIMIT ${sample_limit};

-- COMMAND ----------
SELECT review_id, ai_summarize(review_text) AS summary
FROM validation_sample;

Python notebook source

# Databricks notebook source
sample_limit = int(dbutils.widgets.get("sample_limit") or "10")
source = spark.table("<project_catalog>.silver.reviews").limit(sample_limit)

enriched = source.selectExpr(
    "review_id",
    "ai_summarize(review_text) AS summary",
    "ai_analyze_sentiment(review_text) AS sentiment",
)
display(enriched)

For ai_query in PySpark, keep the SQL expression explicit so named arguments remain visible:

enriched = source.selectExpr(
    "id",
    "ai_query('endpoint-name', text, failOnError => false) AS inference",
)

Raw files and mixed document formats

Resolve the ThinkPlatform logical directory before generating notebook code:

tp datalake resolve-uri \
  --path project/raw/ai-functions-validation/2026-08-01/example

Pass the returned URI through an input_path notebook/Job parameter. Do not derive a /Volumes/... path from the project catalog: the Datalake container and Unity Catalog schema layout are independent. Preview a representative file and select the reader from its record semantics. JSONL is row-oriented even when stored in one file:

SELECT id, text, _metadata.file_path AS source_path
FROM read_files('<resolved-project-directory>', format => 'json', multiLine => false);

Using binaryFile for JSONL produces one binary row per file, not one row per JSON record. Use directory reads and metadata columns instead of enumerating filenames; reserve binaryFile for whole binary documents:

SELECT
  _metadata.file_path AS source_path,
  _metadata.file_name AS source_name,
  content
FROM read_files('<resolved-project-directory>', format => 'binaryFile');

Route by extension/content type before calling a document function:

ai_parse_document v2.0 was live-validated with PDF, DOCX, and PNG.
HTML was rejected with Unsupported file format: html; decode it as UTF-8 and retain DOM/heading/table boundaries in a compatible envelope or deterministic chunks.
Do not send plain text or HTML through a binary document parser merely because the directory is mixed.
A non-NULL parse result can still contain error_status; count both NULL and internal-error outputs.
ai_prep_search is beta. A successful DESCRIBE does not guarantee runtime success. If the representative run returns an internal platform error, stop retrying and deterministically chunk parsed title/section/text/table elements while retaining page/bbox/source metadata.

Fact-preserving text transforms

ai_summarize is appropriate for ordinary summaries, but a representative run can omit operational facts. For strict handover text, define the required fact classes (identifiers, numbers, units, times, states, actions, safety statements, and follow-up owners), inspect every bounded validation output, and reject summaries that add outcomes or causal claims absent from the source. If a task-specific result cannot satisfy that contract, use a constrained generation function and keep the same independent fact gate in production. Masking functions can also hide approved non-personal identifiers. When a business identifier must survive masking, replace only an explicit allowlisted value with a collision-resistant placeholder before the AI expression, restore it deterministically afterward, and verify both sensitive-value absence and allowlisted-value presence. Translation functions can be technically correct while using terminology that is too general for the operating domain. Keep the raw translation immutable, apply only an explicit reviewed terminology map in a downstream stage, and inspect every bounded validation pair for identifiers, equipment terms, negation, and temporal qualifiers. ai_analyze_sentiment can return mixed in addition to positive, negative, and neutral. When the consumer requires three labels, define the normalization policy before production (for example, map mixed to neutral) and retain or audit the native label so the policy is visible. ai_similarity returns a score, not a universal duplicate decision. Calibrate the threshold with labeled positive and negative pairs, record it as a Job parameter, require margin between classes, and periodically re-evaluate it. Live validation showed small score variation between representative and production calls, so avoid thresholds that sit close to observed examples.

Serverless materialization and retry safety

AI expressions can be reevaluated by every DataFrame action. Materialize each expensive step exactly once, then run counts, transformations, and quality checks against Delta:

CREATE TABLE IF NOT EXISTS <project_catalog>.silver.enrichment_stage_<run_suffix>
USING DELTA AS
SELECT id, ai_summarize(text) AS summary
FROM <project_catalog>.silver.source;

CREATE TABLE IF NOT EXISTS <project_catalog>.silver.enrichment_result_<run_suffix>
USING DELTA AS
SELECT id, summary, current_timestamp() AS processed_at
FROM <project_catalog>.silver.enrichment_stage_<run_suffix>;

Use a new run suffix rather than overwriting an existing table. CREATE TABLE IF NOT EXISTS makes a retry reuse an already completed stage after an unrelated notebook failure. Avoid .write.mode("errorifexists").saveAsTable(...); the live Serverless environment rejects that mode.

Quality gates

Validate from an independent SQL Statement after the Job succeeds:

raw inputs, output rows, and distinct source IDs agree;
required values, internal error fields, invalid labels, duplicates, and empty outputs are counted;
structured values cast to the intended DATE/DECIMAL/ARRAY/STRUCT types;
identifiers, dates, numbers, units, and source/page locations are retained;
fields that were not explicit in the source remain NULL; instructions alone can still permit arithmetic or date inference, so use a deterministic source-evidence gate for strict extraction;
representative original/output pairs are inspected for semantic drift.

Task-specific text transforms are model operations, not deterministic linters. For strict grammar-only requirements, ai_fix_grammar still needs a semantic-preservation gate; a successful run alone is not acceptance.

Explicit Unity Catalog SQL wrapper

Create only when the user wants a stable shared abstraction. Keep it inside the project catalog and use LANGUAGE SQL:

CREATE OR REPLACE FUNCTION <project_catalog>.ai.summarize_review(text STRING)
RETURNS STRING
LANGUAGE SQL
RETURN ai_summarize(text);

ThinkPlatform deliberately rejects Python, JAR, external, permission-changing, and DROP FUNCTION statements through the project SQL policy.

Get started

CLI

Python SDK

Agent skills

SQL

SQL notebook source

Python notebook source

Raw files and mixed document formats

Fact-preserving text transforms

Serverless materialization and retry safety

Quality gates

Explicit Unity Catalog SQL wrapper

​SQL

​SQL notebook source

​Python notebook source

​Raw files and mixed document formats

​Fact-preserving text transforms

​Serverless materialization and retry safety

​Quality gates

​Explicit Unity Catalog SQL wrapper

SQL

SQL notebook source

Python notebook source

Raw files and mixed document formats

Fact-preserving text transforms

Serverless materialization and retry safety

Quality gates

Explicit Unity Catalog SQL wrapper