Skip to main content

Data Modeling

Modeling is how you turn source tables into clean, reusable tables of your own. You author a User-Defined Model (UDM) locally, materialize it server-side over MCP, poll the run, and verify the result — the same author → materialize → verify loop whether you write the model in SQL or Python.

This is the step after querying: once you know the data is there, a UDM captures the transformation so it's queryable, shareable, and cheap to reuse. It runs against the same warehouse, but unlike a query it writes a table.

What a UDM is​

A UDM lives in your organization's namespace as org.dataset.model — for example optimism.grants.projects. This is distinct from the read-only oso.* public tables (like oso.projects_v1); a UDM can SELECT from those, from marketplace datasets, or from your other UDMs. Locally, a model is one file:

  • SQL: {project}/models/{model}.sql
  • Python: {project}/models/{model}.py

The file is the source of truth; MCP is how you push it to the platform and materialize it as a real table.

The author → materialize → verify loop​

Every UDM follows the same path. Author the model file, then run the MCP mutations that turn it into a materialized table:

  1. createDataset — one-time per dataset. Reuse an existing one where you can: call ListDatasets (or GetDatasetByName) first, and only createDataset(orgId, name, displayName, type: "USER_MODEL") if none exists.
  2. createDataModel(orgId, datasetId, name) — create the model shell inside the dataset. name becomes the model part of the FQN.
  3. createDataModelRevision(dataModelId, name, language, code, cron, schema, kind) — push the source. The validator runs here, so a bad revision is rejected before anything is released.
  4. createDataModelRelease(dataModelId, revisionId) — release the revision for execution.
  5. createUserModelRunRequest(dataModelId, releaseId) — trigger materialization. This returns a run group (runGroup.id) holding one run per model, not a single run: poll runs(where: {"runGroupId": {"eq": "<run_group_id>"}}) until every run reaches a terminal status, or read the group's own status via runGroups(single: true, where: {"id": {"eq": "<run_group_id>"}}). Poll on a ~60s cadence; a UDM materialization can take several minutes. Never re-trigger a group whose runs are still RUNNING.

COMPLETED means the run finished, not that the model is correct — you still verify by querying the table (see below).

In the app, you can trigger this materialization straight from the Data Catalog — open the model and start a run there instead of the MCP call.

Author a SQL UDM​

SQL UDMs are Trino SQL with no Jinja and no macros — pure SQL only. Start the file with a comment naming the model, use CTEs for readability, and keep any sampling LIMIT/WHERE filters out of the final version.

-- acme.demo.top_languages

WITH ranked AS (
  SELECT
    language,
    repo_count,
    ROW_NUMBER() OVER (ORDER BY repo_count DESC) AS rn
  FROM acme.demo.language_counts
)
SELECT
  language,
  repo_count
FROM ranked
WHERE rn <= 10

Deploy it with language: "SQL" and a schema describing the output columns as [{name, type}, ...]. During development, test the SQL against a small sample with execute_sql before you push a revision.

Author a Python UDM​

Reach for Python when the transformation needs procedural logic, pandas/polars/ numpy, reshaping, or a table plain SQL can't express. The authoring contract is narrow and validator-enforced:

  • import oso and decorate exactly one function with @oso.model(...). The function's name is the model name (the model in org.dataset.model).
  • The signature is def my_model(context: oso.Context) -> oso.DataFrame: and it returns a DataFrame (pandas, polars, or pyarrow). The return annotation is required.
  • Declare inputs on the decorator: depends_on=[...] (fully-qualified org.dataset.table names you read via context.query) and external_origins=[...] (scheme://host[:port] origins you reach via context.fetch). Both are optional literal lists — omit them when the model builds its result from scratch and makes no HTTP calls.
  • The sandbox has no filesystem, no env, and no network except context.fetch. Only the standard library (except sys) plus oso/polars/pandas/pyarrow/numpy may be imported. No top-level executable code.
import oso
import polars as pl


@oso.model()
def top_languages(context: oso.Context) -> oso.DataFrame:
    return pl.DataFrame(
        {"language": ["go", "python", "rust"], "repo_count": [30, 10, 20]}
    )

To read an upstream table, declare it and query it with quoted identifiers:

import oso
import polars as pl


@oso.model(depends_on=["acme.demo.language_counts"])
def top_languages(context: oso.Context) -> oso.DataFrame:
    return context.query(
        'SELECT language, repo_count FROM "acme"."demo"."language_counts" '
        'ORDER BY repo_count DESC LIMIT 10'
    ).as_pl()

context.query is whitelisted to depends_on — querying a table you didn't declare fails the run.

import oso is not pyoso​

These are two different surfaces and conflating them is the most common mistake:

  • import oso / @oso.model is the authoring SDK. It only exists inside the sandbox where your model runs at materialization time. context.query and context.fetch are the only ways data enters the function.
  • pyoso (from pyoso import Client) is the read-only query client you use from notebooks and scripts to pull data out of the warehouse — see Querying OSO.

You never import oso in a notebook, and you never construct a pyoso.Client inside a UDM. Same warehouse, opposite directions.

Declare the output schema​

The revision's schema is a list of {name, type} columns that must match what the model produces — the SQL result set, or the DataFrame a Python UDM returns. For Python, map dtypes to warehouse types: integer → bigint, float → double, boolean → boolean, string → varchar, datetime → timestamp, date → date. A mismatch (wrong column name, bigint vs double) surfaces as a confusing query-time error rather than a deploy error, so keep it in sync.

Python UDMs deploy and run identically to SQL UDMs — the only differences are language: "python" and that code carries Python source instead of SQL.

A minimal end-to-end example​

This reproduces the whole loop with no upstream dependencies. Author the top_languages from-scratch Python model above, then run the MCP mutations:

  1. createDataset(orgId, "demo", "Demo", type: "USER_MODEL") (or reuse an existing demo dataset).
  2. createDataModel(orgId, datasetId, "top_languages").
  3. createDataModelRevision(dataModelId, name: "top_languages", language: "python", code: <source>, kind: "FULL", schema: [{name: "language", type: "varchar"}, {name: "repo_count", type: "bigint"}]). Omit cron: the model becomes eligible whenever its dataset's schedule fires (give the dataset one via updateDataset, since createDataset has no cron field). Set cron to "@manual" only to opt this model out of scheduled runs.
  4. createDataModelRelease(dataModelId, revisionId).
  5. createUserModelRunRequest(dataModelId, releaseId), then poll the returned run group's runs until they are terminal.

Then verify by querying the materialized table like any other — with execute_sql or pyoso:

SELECT * FROM acme.demo.top_languages

If the run FAILED, read run.errorMessage, fix the model file, and push a new revision (a new run request against the same release re-materializes it).

Order your models into layers​

Most projects are a DAG of models, not one. A model can only read tables that already exist and are released, so materialize upstream-first. A useful convention, roughly downstream-narrowing (each layer typically an order of magnitude smaller than the one above), is:

staging → entities → events → metrics → public_marts
  • staging — cleaned, normalized data, one model per source.
  • entities — the durable nouns (projects, artifacts, developers).
  • events — activity joined to entities (commits, deployments, transactions).
  • metrics — aggregations and KPIs over events.
  • public_marts — the stable, query-facing tables your notebooks read.

Build and materialize each layer before the one that depends on it. For worked query examples against the resulting tables, see the data science tutorials.

Next steps​

  • Connect an agent over MCP so it can run these mutations for you.
  • Look up the public oso.* tables your UDMs build on in the core models reference.
  • Build analysis on top of your models in a notebook.