> ## Documentation Index
> Fetch the complete documentation index at: https://docs.thinkplatform.ai/llms.txt
> Use this file to discover all available pages before exploring further.

> ## Agent Instructions
> Choose User guide for browser tasks, Developers for CLI, SDK and external agents, and Administration for organization management.
> Confirm identity and organization/project context before operations. Documentation access does not grant ThinkPlatform permissions.
> Review side effects, verify changes with a read, and do not blindly retry an operation with an uncertain outcome.
> Use documented UI steps for the web app and documented public CLI or SDK interfaces for automation. Follow each page’s prerequisites and authentication requirements.

# SQL and notebook patterns

> Apply AI functions to project data with SQL queries and notebook examples.

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

## SQL

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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`:

```sql theme={"system"}
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

```sql theme={"system"}
-- 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

```python theme={"system"}
# 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:

```python theme={"system"}
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:

```bash theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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`:

```sql theme={"system"}
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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.