Skip to main content

LLM Blocks Overview

Understanding LLM blocks: how CoginitiScript turns a natural-language prompt into structured, queryable data that flows through the rest of your SQL pipeline.

Background​

CoginitiScript organizes data work into composable blocks. A SQL block defines a named, reusable query that other blocks can reference as if it were a table. This model — small, typed, referenceable units of logic — is what makes CoginitiScript pipelines modular.

Large language models have become a routine part of analytics: classifying text, extracting structure from messy inputs, summarizing, generating synthetic data. But until now, using an LLM inside a data pipeline meant stepping outside SQL — calling an API from a separate script, massaging the response, and loading it back before you could query it.

The Problem​

Bringing AI output into a SQL pipeline by hand is awkward for a few reasons:

  • It breaks composition. The model's output lives outside the pipeline until you manually load it, so it can't be joined or transformed like any other relation.
  • It's unstructured. Raw model responses are free text. Turning them into typed rows you can trust takes parsing, validation, and error handling you have to write yourself.
  • It's hard to make reproducible. Without a defined contract, the same prompt can return differently shaped data on each run.

The Solution​

An LLM block is a block type — #+src llm — that sends a prompt to a language model and returns structured tabular data. You declare the columns and types you expect in a :schema, write your prompt in the block body, and the result behaves like any other recordset: SQL blocks can query it, join it, filter it, and publish it.

#+src llm sentiment(review)
#+meta {
  :schema {
    :columns [{:name "label", :type "STRING", :description "POSITIVE, NEUTRAL, or NEGATIVE"},
              {:name "confidence", :type "DECIMAL(3,2)"}]
  }
}
#+begin
Classify the sentiment of this review: {{ review }}
#+end

From SQL's point of view, the block above is simply a table of label and confidence. That is the central idea: an LLM block makes a model's output a first-class relation in your pipeline.

How It Works​

Core concepts​

  • Prompt body — the #+begin…#+end text sent to the model, with the full CoginitiScript template toolkit ({{ expressions }}, parameters, #+for, #+if, embedded SQL via print.Csv()).
  • Output schema — the :schema is more than documentation. It is sent to the model as a structured-output contract (a JSON schema for providers that support it, or an equivalent instruction otherwise), and the response is validated against it.
  • Recordset result — the validated rows become a recordset. When a SQL block references the LLM block, that recordset is materialized into a temporary table so the database can query it.

From prompt to table​

  1. The prompt body is evaluated (parameters and expressions resolved) into a single request to the model.
  2. The model returns rows that conform to the schema — or a refusal if it cannot.
  3. The response is validated and, if needed, retried: a malformed response is re-issued up to three times in total, with the error fed back so the model can self-correct.
  4. The resulting rows are exposed as a recordset and, when referenced from SQL, staged as a temporary table.

Integration with the AI Assistant and SQL​

LLM blocks don't introduce a new place to configure AI. They use whichever provider and model is set as your active AI Assistant, so the same configuration that powers chat-based assistance also powers LLM blocks. Downstream, the result is plain tabular data, so everything you already do with SQL blocks — joins, aggregation, tests, publication — applies unchanged.

Design Decisions​

Schema-first output​

Requiring a schema is what makes the feature dependable. It gives the model an explicit contract, lets Coginiti validate and type the response, and guarantees the result can be joined like any other table. The schema is the difference between "some text the model wrote" and "a typed relation."

Lenient by default, strict when it matters​

Validation has two modes. Lenient (the default) favors getting a usable result: values are converted to the declared types on a best-effort basis. Strict favors correctness over guessing: ambiguous or unrepresentable values are reported as an error instead of being coerced. The default keeps everyday use frictionless; strict mode is there for pipelines where a wrong-but-plausible value is worse than no value.

Caching for reproducibility​

Within a single execution, an LLM block is called once per unique combination of block and arguments, and the result is reused. Because model output varies between calls, this keeps a script internally consistent — two references to the same block see the same data — and avoids paying for redundant calls.

Materialize, then reference​

An LLM block's result is staged as a table so SQL can query it. This is also why an LLM block can be referenced from a SQL block but not used as a table reference inside another LLM block: chaining LLM steps is done by passing data through the prompt (print.Csv() or iterator()), not by relational reference. The same materialization is what lets results be published to a table, CSV, or Parquet.

A refusal channel instead of bad data​

Rather than forcing the model to invent a value it can't produce, the schema gives it an explicit way to refuse — returning a short explanation instead of rows. A refusal surfaces as an error and is not retried, because it reflects a genuine "this can't be done," not a transient formatting glitch.

When to Use LLM Blocks​

LLM blocks fit work that needs semantic understanding expressed as data: classification and sentiment, entity or field extraction from messy text, enrichment (for example, inferring a country from an address), summarization, and synthetic or seed data generation.

They are a weaker fit when:

  • Determinism is required. Model output varies between runs; validate it with data quality tests and prefer strict validation where exactness matters.
  • Volume is high. Each call costs money and time, and large inputs can exceed token limits — process big datasets in bounded batches (see the streaming patterns).
  • A plain SQL transformation would do. If the logic is expressible in SQL, SQL is cheaper, faster, and deterministic.

Next steps​