Skip to main content

Querying OSO

Query the OSO warehouse with read-only Trino SQL. There are two ways to run a query, both against the same warehouse:

  • pyoso (Python) — for notebooks and scripts.
  • execute_sql (MCP) — so your own agent can run queries.

This is the first step of the end-to-end path: get data out, then model it, build a notebook, and share the result.

  • 0:04 — open Scratchpad, add a SQL cell, paste a table name, and run it — no install, no API key.
  • 0:17 — export the results (download or copy), or save the output as a DataFrame to keep working in code.

Install and authenticate​

Client() reads your OSO_API_KEY from the environment. See Python & pyoso to install the package and generate an API key.

Your first query with pyoso​

from pyoso import Client

client = Client()
df = client.to_pandas("SELECT * FROM oso.projects_v1 LIMIT 5")
print(df)

A fuller example, joining projects, metrics, and the time series:

df = client.to_pandas("""
    SELECT m.metric_name, tm.sample_date, tm.amount
    FROM oso.timeseries_metrics_by_project_v0 AS tm
    JOIN oso.metrics_v0 AS m ON tm.metric_id = m.metric_id
    JOIN oso.projects_v1 AS p ON tm.project_id = p.project_id
    WHERE p.project_name = 'uniswap'
    ORDER BY tm.sample_date DESC
""")

Query from your agent​

An agent connected over MCP runs the same read-only SQL with the execute_sql tool — it takes a single sql argument and returns rows. See Connect over MCP to set up the server in your client.

Discover what's available​

  • 0:00 — open the Data Catalog to see every table and asset your org can access.
  • 0:06 — drill into a dataset to review its tables and schema, and trigger a materialization from the same view.

oso.* is the default namespace for public data; your organization's private tables live under your org's own namespace. To see what you can query:

  • From an agent over MCP, use ListDatasets to enumerate datasets and ListTablesForDataset to list the tables within one.
  • For the stable public schemas — entities, events, and metrics — see the core models reference, which documents each table's fields and the v0/v1 stability convention.
  • In the app, the Data Catalog lists every table and asset your org can access. From a dataset there you can inspect its schema and trigger a materialization run on the spot — the point-and-click equivalent of the MCP run requests you use to ingest or model data.

Model layers​

Tables come in three layers. Query the one furthest downstream that answers your question; each layer is typically 1–2 orders of magnitude smaller than the one above it, so downstream tables are cheaper and faster.

  1. Staging models — cleaned, normalized data per source.
  2. Intermediate models — sources joined into a master event table and its aggregations.
  3. Mart models (e.g. projects_v1, metrics_v0) — the stable, versioned tables most queries should use.

Staging and intermediate tables are internal and change often. For worked query examples against the marts, see the data science tutorials.

Trino dialect​

OSO runs Trino SQL. A few things to know:

  • Cast with CAST(x AS VARCHAR), not SAFE_CAST.
  • DATE_TRUNC('month', dt), not DATE_TRUNC(dt, MONTH).
  • Aggregate arrays with ARRAY_AGG / ARRAY_JOIN.
  • Double-quote identifiers, single-quote strings.

Inspect data provenance​

client.query() returns a QueryResponse with the data plus a dependency tree, so you can see how a table was built and how fresh it is.

from pyoso import Client

client = Client()
response = client.query("SELECT * FROM oso.artifacts_v1 LIMIT 5")
print(response.to_pandas())
response.analytics.print_tree()

Rate limits​

Queries are rate limited. If your limit is too low for your use case, reach out on Discord.