Skip to main content

Ingesting data

Bring external data into your organization's private schema so you can query it alongside the public oso.* tables and model on top of it. OSO supports three ingestion mechanisms — a REST API pipeline, a file upload, and a live connector — all driven by MCP mutations from your agent. Whichever you use, the cadence is the same: create a config, request a run, then query the materialized table.

Watch: connect a source in the app​

The fastest way to see the whole flow is in the OSO app — no code. This walkthrough connects a Google Drive spreadsheet as a source and queries it end to end:

  • 0:00 — start a new integration and pick Google Drive.
  • 0:15 — import the spreadsheet, selecting all tabs.
  • 0:28 — preview the source data while it processes.
  • 0:49 — confirm the data landed.
  • 1:03 — add a SQL cell and query it.

The rest of this page covers the same mechanisms in more depth, including the MCP tools for driving them from an agent.

First: does this data already exist?​

Ingesting is the last resort, not the first move. Before you pull anything in, check whether the data is already queryable — it's cheaper and stays fresh automatically.

  • Public data. Entities, events, and metrics for open source projects already live in oso.*. Browse the core models reference before assuming you need to ingest.
  • Your org's datasets. From an agent over MCP, run ListDatasets to see what's already in your namespace and ListTablesForDataset to list a dataset's tables. Something an earlier run ingested may already be there.
  • The marketplace. MarketplaceDatasets shows datasets published by others that you can subscribe to. subscribeToDataset grants access in place — no copy, no new ingestion, nothing to keep in sync.

If none of those cover the need, pick a mechanism below.

Choosing a mechanism​

SourceMechanismDataset type
A REST API (endpoints returning JSON)Data ingestion pipelineDATA_INGESTION
A file you upload — CSV, JSON, JSONL, Parquet, XLSX, or DOCXStatic modelSTATIC_MODEL
A live system — Google Sheets, BigQuery, PostgresConnectorDATA_CONNECTION

Rule of thumb: use a connector when the source is a live system you want to keep syncing, a REST pipeline when the source is an API, and a static model for a file, or a series of files, you already have on disk, in whichever of the six supported formats it comes in.

For BigQuery and Google Drive, the connect step is a point-and-click flow in the OSO web app — see Connect BigQuery and Connect Google Drive, both with screenshots. The rest of this page covers the agent-driven path over MCP.

REST API → data ingestion​

Each API endpoint becomes a table. Nested JSON is flattened into child tables automatically.

First create a DATA_INGESTION dataset (or reuse one with GetDataset), then configure the ingestion with createDataIngestionConfig. The config names the base URL and one resource per endpoint:

createDataIngestionConfig(input: {
rest: {
datasetId: "<dataset_id>",
factoryType: "REST",
config: {
client: { base_url: "https://api.llama.fi" },
resources: [
{ name: "chains", endpoint: { config_type: "simple", path: "/v2/chains" } }
]
}
}
})

For authenticated APIs, set client.auth and pass tokens as a secret marker — {"$type": "secret", "value": "<raw_value>"} — so the key is stored separately from the config. Paginated APIs take a client.paginator; most simple public APIs return everything in one response and need none.

Then request a run:

createRunGroup(orgId: "<org_id>", selection: { datasetIds: ["<dataset_id>"] }, includeUpstream: false)

The mutation returns a run group — one run per node it dispatched. Poll the group's runs with runs(where: {"runGroupId": {"eq": "<run_group_id>"}}) until every one reaches a terminal status (REST ingestions usually finish in 1–3 minutes), then list the resulting tables with ListTablesForDataset and verify with SqlQuery/GetAsyncQueryResult.

A success: false reply is not an error: the dataset resolved but held nothing runnable, and message says why.

File upload → static model​

Use this for a file you already have as a URL or on disk, in CSV, JSON, JSONL, Parquet, XLSX, or DOCX. A static model always materializes into exactly one table, and any number of uploaded files can feed it: each upload is its own file, and a run either replaces the table with it or appends its rows.

Create the model, get a pre-signed URL for an upload, put the file to it, then request a run that names the upload:

createStaticModel({ orgId, datasetId: "<dataset_id>", name: "gitcoin_grants" })
→ static_model_id
createStaticModelUploadUrl(staticModelId: "<static_model_id>") { uploadId url }
→ upload_id, upload_url (the `url` field)

fileFormat defaults to CSV when omitted from createStaticModel; pass JSON, JSONL, PARQUET, XLSX, or DOCX for the others. The format is fixed the moment the model is created and cannot be changed — to switch format (or, for XLSX, worksheet), create a new static model. Every createStaticModelUploadUrl call mints a new upload with its own uploadId and its own file, so uploads of one model never overwrite one another and can be made in parallel. Uploading loads nothing until a run names the upload.

The upload URL is a short-lived pre-signed slot — upload immediately with a plain HTTP PUT:

curl -X PUT \
-H "Content-Type: text/csv" \
--data-binary @gitcoin_grants.csv \
"<upload_url>"

Then materialize:

createStaticModelRunRequest({
datasetId: "<dataset_id>",
staticModelId: "<static_model_id>",
uploadId: "<upload_id>",
writeDisposition: REPLACE
})

A run names the upload it loads with uploadId. staticModelId must be the static model UUID, not its name — passing the name is refused. Poll the run, then query the table at {org}.{dataset}.{model} to confirm the row count matches your file.

Append or replace​

writeDisposition says how the run writes its upload into the table:

writeDispositionEffect
REPLACEOverwrites the table with the upload's rows. The default.
APPENDAdds the upload's rows and keeps the rows already there.

Use REPLACE when the file is the full current state, and APPEND when each file is a new batch of rows, such as dated snapshots or incremental exports. Nothing is deduplicated, and a run that leaves writeDisposition out replaces the table, so name APPEND explicitly. To add several files, upload each one and request one APPEND run per upload:

createStaticModelRunRequest({
datasetId: "<dataset_id>",
staticModelId: "<static_model_id>",
uploadId: "<upload_id>",
writeDisposition: APPEND
})

A few rules apply to appending:

  • An upload is appended once. While its APPEND run is in any state but failed or canceled, or is failed or canceled after its rows loaded, a second request for it (or a re-run of its run group) is refused with CONFLICT ("has already been appended by another run"). Don't upload the file again to get around it: that adds its rows twice.
  • Retry a failed or canceled APPEND with retryFailedRunGroup. Call it with the runGroup { id } of the run instead of requesting a new run. It resets the same run and is safe whether or not the rows had loaded: if they had, the retry skips the load and only records them; if not, it loads the upload. Don't retry while a REPLACE of the same model is queued or running, or after a REPLACE that overwrote the table and then failed: the retry would skip loading rows the REPLACE removed. Once a REPLACE has completed, the retry loads the file's rows on top of what it wrote, so leave the failed run alone if the REPLACE already contains them. In the rare case that a retried run succeeds but the table holds the file's rows twice, a failure landed between the load and its record: remove the duplicate rows (for example by REPLACE-ing with a corrected file) or contact support.
  • Runs of one model go one at a time. A run behind another waits as CONCURRENCY_BLOCKED and starts when the runs ahead finish. At most RUN_CONCURRENCY_QUEUE_LIMIT runs (10 by default) may wait per model; a request beyond that is refused with CONFLICT and the reason CONCURRENCY_QUEUE_FULL, and nothing is created. Request runs in batches below the limit, sending each request after the previous one returns so they queue in the order you want. If you hit it, wait for queued runs to finish and retry the request with the same uploadId; there is no need to upload again.
  • Columns may drift between files. New columns are added to the table, and a column a file lacks loads as NULL.
  • A run records its upload and mode in its metadata, as staticModelUploadId and writeDisposition. Once an APPEND's rows are committed, it also records staticModelUploadLoaded: true, which is what keeps a failed APPEND from loading them again on retry.
  • A run from before uploads had ids cannot be re-run. Upload the file again and request a new run.

XLSX: name the worksheet at creation time​

An XLSX static model always materializes exactly one worksheet, chosen when the model is created — a second worksheet in the same workbook is a second static model, not an additional table:

createStaticModel({
orgId,
datasetId: "<dataset_id>",
name: "q1_revenue",
fileFormat: XLSX,
worksheetName: "Q1 Revenue"
}) → static_model_id

worksheetName is required and must be non-blank (after trimming) when fileFormat is XLSX, and must be omitted for every other format. Upload and run it exactly as above — the model always reads that one named sheet, never "whichever sheet happens to be the only one with data."

DOCX: a row inside the model's own table, not a table of its own​

A DOCX static model does not get a table named after the document — the parsed content lands as a single row (tab_id, tab_title, index, content) inside the model's own one table, named from the model like every other static format (model_id with its hyphens turned into underscores). tab_title is set to the static model's own name.

This is the opposite of a DOCX picked through Google Drive, which lands in a shared document_tabs table titled from the Drive file's name instead — see Google Sheets → data connection below for that path. Same parsed shape, different table.

Nested fields, and size limits​

Nested objects or arrays in a JSON, JSONL, or Parquet upload are stored as a single JSON-text column rather than fanned out into child tables — a static model never produces more than the one table it's meant to.

An upload is capped at 100 MiB of source file bytes. XLSX and DOCX are additionally capped at 512 MiB of content once their ZIP container is expanded, so a small file that decompresses enormously is rejected rather than exhausted into memory.

Need a file attached directly to an agent dispatch instead of ingested as a table? That's a different, not-yet-built mechanism — see OSO-4695.

Google Sheets → data connection​

A connector keeps a live source in sync rather than snapshotting it. Create the connection, then trigger a sync:

createDataConnection(...) → connection_id
syncDataConnection(connectionId: "<connection_id>")

syncDataConnection pulls the current contents of the connected sheet into your schema; re-running it refreshes the data. Because the Google account link and file picker are OAuth flows, the practical way to set a Google Sheets connection up is the web app — follow Connect Google Drive, which walks through authorizing the account and selecting sheets and tabs. The same connector model backs BigQuery and Postgres.

The same Drive picker also accepts an uploaded CSV, JSON, JSONL, Parquet, XLSX, or DOCX file, not only native Google Sheets and Docs — pick one and it lands as its own dataset the same way native files do. Picking a native Sheet or Doc is unaffected: they still ingest exactly as they always have. For an uploaded file:

  • CSV, JSON, JSONL, Parquet land in one table, named from the file.
  • XLSX lands in one table per nonempty worksheet in the workbook.
  • DOCX lands in a single document_tabs table, titled from the Drive file's name — the same table a native Google Doc lands in, so an uploaded and a native document read identically once ingested. Contrast this with a DOCX uploaded to a static model above, which lands as a row inside the model's own table instead of its own document_tabs table.

Nested JSON/Parquet fields land in a JSON-text column rather than fanning out, and an uploaded file is capped at 100 MiB of source bytes (512 MiB of expanded content for the ZIP-based XLSX and DOCX formats) — the same limits a static upload has.

The common cadence​

Every mechanism follows the same three beats, only the tool names differ:

StepRESTFile uploadConnector
CreatecreateDataIngestionConfigcreateStaticModel + uploadcreateDataConnection
RuncreateRunGroupcreateStaticModelRunRequestsyncDataConnection
Materializepoll GetRun, then SqlQuery/GetAsyncQueryResultpoll the run, then SqlQuery/GetAsyncQueryResultquery once the sync completes

You can also trigger REST and connector runs from the Data Catalog in the app — open the dataset and start a run there instead of the MCP run request. Static-model runs are requested over the API or MCP only, because each one names an upload.

Once the table is queryable in your org's namespace, treat it exactly like any public table: query it, model on top of it, or pull it into a notebook. See Connect over MCP to point your agent at the server so it can run these mutations for you.