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
ListDatasetsto see what's already in your namespace andListTablesForDatasetto list a dataset's tables. Something an earlier run ingested may already be there. - The marketplace.
MarketplaceDatasetsshows datasets published by others that you can subscribe to.subscribeToDatasetgrants 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
| Source | Mechanism | Dataset type |
|---|---|---|
| A REST API (endpoints returning JSON) | Data ingestion pipeline | DATA_INGESTION |
| A file you upload — CSV, JSON, JSONL, Parquet, XLSX, or DOCX | Static model | STATIC_MODEL |
| A live system — Google Sheets, BigQuery, Postgres | Connector | DATA_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:
writeDisposition | Effect |
|---|---|
REPLACE | Overwrites the table with the upload's rows. The default. |
APPEND | Adds 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
APPENDrun 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 withCONFLICT("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
APPENDwithretryFailedRunGroup. Call it with therunGroup { 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 aREPLACEof the same model is queued or running, or after aREPLACEthat overwrote the table and then failed: the retry would skip loading rows theREPLACEremoved. Once aREPLACEhas completed, the retry loads the file's rows on top of what it wrote, so leave the failed run alone if theREPLACEalready 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 byREPLACE-ing with a corrected file) or contact support. - Runs of one model go one at a time. A run behind another waits as
CONCURRENCY_BLOCKEDand starts when the runs ahead finish. At mostRUN_CONCURRENCY_QUEUE_LIMITruns (10 by default) may wait per model; a request beyond that is refused withCONFLICTand the reasonCONCURRENCY_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 sameuploadId; 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, asstaticModelUploadIdandwriteDisposition. Once anAPPEND's rows are committed, it also recordsstaticModelUploadLoaded: true, which is what keeps a failedAPPENDfrom 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_tabstable, 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 owndocument_tabstable.
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:
| Step | REST | File upload | Connector |
|---|---|---|---|
| Create | createDataIngestionConfig | createStaticModel + upload | createDataConnection |
| Run | createRunGroup | createStaticModelRunRequest | syncDataConnection |
| Materialize | poll GetRun, then SqlQuery/GetAsyncQueryResult | poll the run, then SqlQuery/GetAsyncQueryResult | query 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.