# OData feed reference

> The sanda OData feed in full: addresses, tables, data types, query options, the filter grammar, paging, limits and every error it returns.

sanda serves your semantic fluid as an OData v4 service. This page is the exact contract: what to send, what comes back, and what sanda refuses. For a walk through, see [Connect Excel](https://docs.sanda-os.com.au/odata/excel) or [Connect Power BI](https://docs.sanda-os.com.au/odata/power-bi).

The feed is read only and is on every edition.

## Addresses

The service root is:

```text
https://console.sanda-os.com.au/api/odata/
```

Keep the trailing slash. Some clients resolve their own links against this string, and without it the service document is the last path segment rather than the root.

| Address | Returns |
|---|---|
| `/api/odata/` | The **service document**: every table this workspace offers |
| `/api/odata/$metadata` | The **CSDL document**: every table's columns and their types, as XML |
| `/api/odata/<table>` | The rows of one table |

`$metadata` is also accepted percent-encoded as `%24metadata`.

Only `GET` and `HEAD` are accepted. Anything else is refused with 405 and `Allow: GET, HEAD`. Every response carries `OData-Version: 4.0` and `Cache-Control: no-store`. Rows come back as `application/json;odata.metadata=minimal;charset=utf-8`, and `$metadata` as `application/xml;charset=utf-8`.

A browser request that carries an `Origin` other than sanda's own is refused with 403, so a web page on another site cannot read the feed.

## Authentication

A feed takes a credential that was typed in. It never uses a signed-in browser session.

| Credential | How to send it | Use it for |
|---|---|---|
| Integration token as a Basic credential | `Authorization: Basic <base64 of user:password>`, with the token as the password | Excel, Power BI, and anything that cannot set a bearer header |
| Integration token as a bearer | `Authorization: Bearer bat_...` | Scripts and clients that can set a header |
| OAuth access token as a bearer | `Authorization: Bearer <access token>` | A client that already signed a person in with OAuth |

Notes:

- **Either half of a Basic credential is read.** A token begins `bat_`, and sanda picks it out of the pair, so a token pasted into the user name box works too.
- **An OAuth access token is refused as a Basic credential.** It expires in an hour and is refreshed by a client that knows how, which a workbook does not.
- **The token needs `query:run`.** Every integration token has it. Nothing else is asked for, and the feed cannot write, define or run SQL of its own.
- **A missing or wrong credential is a 401** with `WWW-Authenticate: Basic realm="sanda", charset="UTF-8", Bearer realm="sanda"`. That header tells a client to ask for a credential rather than treat the response as a failure.

Get a token under **Settings · Integrations**. See [agent tokens](https://docs.sanda-os.com.au/mcp/agent-tokens).

## Tables

For each table on the semantic map, the feed offers a **rows** table named for the table, and, when the table has at least one metric, a **summary** table.

| Table | Name | Properties |
|---|---|---|
| Rows | `<table>` | Every column of the table the fluid can see, up to 200. A column that has a dimension takes the dimension's name |
| Summary | `<table>_summary` | The table's dimensions, then its metrics. One row for each combination of dimension values |

Names in the fluid are lowercase prose. The feed folds each into an identifier: lowercase, every run of characters other than letters and digits becomes one underscore, underscores at either end are dropped, and a name that starts with a digit gets an `n` in front. `order details` is `order_details`, and `on-time delivery %` is `on_time_delivery`. If two names fold to the same identifier, the second gets `_2`, then `_3`, so both stay reachable.

Every table also has a `_row` property, typed `Edm.Int64`. It is the row's position in that read (a `$skip` of 1,000 makes the first row of the next page `1000`). OData needs every row to have a key, and sanda has no honest business key to offer, because a declared primary key is missing on plenty of tables and a key that is not unique makes a client silently drop rows. `_row` is not a business key and it does not survive a refresh. You cannot filter or sort by it, and it is ignored in `$select`.

Summary tables are grouped by every dimension they carry, so `$select` on a summary is also a coarser grouping. Select only `region` and `net_revenue`, and you get one row for each region.

A table is only in the feed when it is on the map. If the map is empty, the service document lists no tables and reading any table returns 404.

### Service document

```json
{
  "@odata.context": "https://console.sanda-os.com.au/api/odata/$metadata",
  "value": [
    { "name": "invoices", "kind": "EntitySet", "url": "invoices" },
    { "name": "invoices_summary", "kind": "EntitySet", "url": "invoices_summary" }
  ]
}
```

### Metadata

`$metadata` is a CSDL 4.0 document in the namespace `sanda`, with the container `fluid`. Each table is an entity set, and its entity type is named `<table>_row`.

```text title="Abridged"
<EntityType Name="invoices_summary_row">
  <Key><PropertyRef Name="_row"/></Key>
  <Property Name="_row" Type="Edm.Int64" Nullable="false"/>
  <Property Name="issue_date" Type="Edm.DateTimeOffset" Nullable="true"/>
  <Property Name="region" Type="Edm.String" Nullable="true"/>
  <Property Name="net_revenue" Type="Edm.Decimal" Scale="variable" Nullable="true"/>
</EntityType>
```

- **Every property is nullable** except `_row`, because a column you left out with `$select`, a join that matched nothing and an empty cell look the same to a client.
- **Decimals carry `Scale="variable"`.** CSDL defaults an unstated scale to zero, which would have a client round every price and total.
- **A table's description** rides as the annotation `Org.OData.Core.V1.Description`, for clients that show a description beside a table. A summary table's description is generated: "invoices: its metrics, grouped by its dimensions."

## Data types

Types are declared in `$metadata` and values are typed to match, so a number arrives as a number and Excel can add it up.

| Warehouse type | OData type | In JSON |
|---|---|---|
| `boolean` | `Edm.Boolean` | `true` or `false` |
| `smallint` | `Edm.Int16` | A number |
| `integer` | `Edm.Int32` | A number |
| `bigint` | `Edm.Int64` | A number, or a string when it has more than 15 significant digits |
| `numeric`, `decimal` | `Edm.Decimal` | A number, or a string when it has more than 15 significant digits |
| `money` | `Edm.String` | The amount as the warehouse formats it, such as `$1,234.50`. Cast it to `numeric` in the model for a number |
| `real`, `double precision` | `Edm.Double` | A number |
| `date` | `Edm.Date` | `2026-06-30` |
| `timestamp`, `timestamp with time zone` | `Edm.DateTimeOffset` | `2026-06-30T04:05:06.000Z`, always UTC |
| `time`, `time with time zone` | `Edm.TimeOfDay` | `14:30:00`. A time zone offset is dropped, because OData has no time of day with an offset |
| `interval` | `Edm.String` | An ISO 8601 duration, such as `P0Y1M3DT4H0M0S`. OData's duration type cannot hold months or years, so an interval is sent as text |
| `uuid` | `Edm.Guid` | A string |
| `text`, `character varying`, `jsonb`, arrays, ranges and enums | `Edm.String` | A string. JSON and arrays are sent as JSON text. Nothing is truncated |
| A metric in a summary | `Edm.Decimal` | A number |

Two things follow. A value too large to be exact as a JSON number arrives as a string rather than as a number that is close. And a client that would rather have every 64-bit integer and decimal as a string can say so by sending `IEEE754Compatible=true` in its `Accept` header:

```text
Accept: application/json;IEEE754Compatible=true
```

## Query options

Options are applied in the warehouse and not after the rows arrive, so a `$filter` makes a refresh cheaper as well as smaller.

| Option | Supported | Behaviour |
|---|---|---|
| `$select` | Yes | A comma-separated list of property names, case insensitive. Fewer columns is a cheaper query. `_row` and `*` are ignored, and a `$select` that names nothing else is refused |
| `$filter` | Yes | See [the filter grammar](https://docs.sanda-os.com.au/odata/reference#the-filter-grammar) |
| `$orderby` | Yes | Comma-separated `name`, `name asc` or `name desc`. You can order only by properties you also select. `_row` is refused |
| `$top` | Yes | A whole number of rows, 0 or more. It bounds the whole read, not one page. `$top=0` returns an empty page |
| `$skip` | Yes | A whole number of rows to skip, from 0 to 1,000,000 |
| `$format` | `json` only | `json` or `application/json`. Any other value is refused |
| `$count` | `$count=false` only | `$count=false` is accepted, and any other value is refused |
| `$expand`, `$apply`, `$search` | No | Refused by name, with a message saying what to do instead |

sanda does not implement any other system query option and does not read one. The four it refuses by name are refused because ignoring them would give you a bigger answer than you asked for, and nothing is dropped quietly:

- `$count` would need a second scan of the table, billed to the workspace, to fill in a number nothing on the sheet needs. Follow the pages instead.
- `$expand`: the tables in the feed have no links between them. sanda joins on the map instead, so ask for a summary, or model a view under **Data · Modelling**.
- `$apply`: sanda does its grouping in the fluid. Every table with metrics already has a summary that is grouped.
- `$search`: use `$filter` with `contains`, or sanda search in the console.

### The filter grammar

A `$filter` is one or more conditions joined by `and`. Parentheses are accepted and change nothing, because every condition is combined with `and`.

```text
region eq 'AU' and total ge 100 and contains(notes,'urgent')
```

**Comparisons** have the form `property operator value`:

| Operator | Means | Applies to |
|---|---|---|
| `eq` | Equals | Text, number, boolean, date |
| `ne` | Does not equal | Text, number, boolean, date |
| `gt` | Greater than | Number |
| `ge` | Greater than or equal, or on or after | Number, date |
| `lt` | Less than, or before | Number, date |
| `le` | Less than or equal | Number |

**Text functions** take a text property and a quoted value:

| Function | Example |
|---|---|
| `contains(property,'value')` | `contains(notes,'urgent')` |
| `startswith(property,'value')` | `startswith(region,'New')` |
| `endswith(property,'value')` | `endswith(region,'Wales')` |

**Null tests** are `property eq null` (the value is empty) and `property ne null` (it has a value). Only `eq` and `ne` may be used with `null`.

**Values:**

- Text is in single quotes, and a quote inside text is doubled: `'O''Brien'`.
- Numbers are bare: `100`, `-2.5`.
- Dates and instants are bare, in ISO 8601: `2026-01-01` or `2026-01-01T00:00:00Z`.
- Booleans are bare: `true` or `false`.

Property names are case insensitive and are the names in `$metadata`. What kind of property it is decides which operators it accepts: a **date** is a property whose dimension is a time dimension or whose column is a date or timestamp, a **number** is a numeric column, a **boolean** is a boolean column, and everything else is **text**.

What sanda refuses, always with a sentence that says what to write instead:

- **`or` and `not`.** A query carries conditions that are all combined with `and`, so a disjunction has nowhere to go, and turning one into an `and` would answer a narrower question than the one asked. Ask two questions, or use `ne`.
- **`gt` and `le` on a date.** sanda compares dates as a window that includes its first day and excludes its end, which is why a month filter never loses its last day. Use `ge` for on or after and `lt` for before.
- **A metric.** A metric in a summary is worked out from the rows a query reads, so it cannot decide which rows are read. Filter on a dimension.
- **A text function on a property that is not text.**
- **More than 20 conditions.**
- **A property that is not in the table.** The message suggests the nearest names.

A month, filtered correctly:

```text
$filter=issue_date ge 2026-06-01 and issue_date lt 2026-07-01
```

## Paging

A page is at most **1,000 rows**. When more rows remain, the response carries `@odata.nextLink`, an address that keeps every option you sent and moves `$skip` on. Its query string is percent-encoded (`%24skip`), which every client decodes. Excel and Power BI follow it without being asked, so a large table just takes a little longer.

```json
{
  "@odata.context": "https://console.sanda-os.com.au/api/odata/$metadata#invoices_summary(region,net_revenue)",
  "value": [
    { "_row": 0, "region": "AU", "net_revenue": 128450.5 },
    { "_row": 1, "region": "NZ", "net_revenue": 41210 }
  ],
  "@odata.nextLink": "https://console.sanda-os.com.au/api/odata/invoices_summary?%24select=region%2Cnet_revenue&%24skip=1000"
}
```

The rows and numbers above are an illustration. `@odata.context` names the columns you selected in brackets, and leaves them out when you selected all of them.

- **Every page is ordered.** An `OFFSET` over an unordered result is a different arbitrary slice on every page, and the symptom is a workbook missing rows. If you send no `$orderby`, sanda sorts ascending by the first four dimension or column properties it returns. Two rows that tie on every one of them may swap places, which a client cannot tell apart anyway.
- **`$top` bounds the whole read.** A `$top` of 50 returns 50 rows and no `nextLink`, however large the table is. A `$top` of 2,500 returns three pages, and the `nextLink` carries the rows still to come.
- **`$skip` goes up to 1,000,000.** Past that, every page costs the warehouse the whole scan again, so sanda refuses with `becca.too_deep`. Narrow the feed with `$filter`, read a summary instead, or build a view under **Data · Modelling**.

## Limits

| Limit | Value |
|---|---|
| Rows in one page | 1,000 |
| Deepest `$skip` | 1,000,000 |
| Time one read may take | 30 seconds |
| Columns in a rows table | 200 |
| Conditions in one `$filter` | 20 |

A read runs through the warehouse's read-only role and is billed to the workspace like any other query. See [usage](https://docs.sanda-os.com.au/billing/usage).

The feed composes no SQL of its own. Every read goes through the composer that answers questions in sanda's workbench, so anything that surface refuses, the feed refuses for the same reason: a column whose name looks like it holds a secret, two tables that would fan out if joined, a table a join repeats that has no key columns. Reading a table that has a column named like a secret is refused whole. Leave the column out with `$select`, rename it if the name is wrong, or build a view without it.

## Errors

A read sanda will not compose is a 4xx that carries sanda's own sentence. A 200 with no rows would look to Excel like an empty table, and nothing would say why, so a refusal is never a 200.

```json
{
  "error": {
    "code": "becca.refused",
    "message": "sanda can only combine filters with “and”. Ask the two questions separately, or narrow with eq on a list."
  }
}
```

`code` is a stable word a script can branch on, and `message` is the sentence a person sees in Excel's error pane.

| Status | `code` | When |
|---|---|---|
| 400 | `becca.refused` | A `$select`, `$filter`, `$orderby`, `$top` or `$skip` sanda cannot use, or a question the composer will not answer |
| 400 | `becca.unsupported` | `$count`, `$expand`, `$apply` or `$search`, or a `$format` that is not JSON |
| 400 | `becca.too_deep` | A `$skip` beyond 1,000,000 |
| 401 | `becca.unauthorized` | No credential, or one sanda does not recognise. Carries `WWW-Authenticate` |
| 402 | `becca.suspended` | The workspace is suspended |
| 402 | `becca.budget_suspended` | The warehouse is suspended for its budget |
| 403 | `becca.scope` | The token cannot ask questions |
| 403 | `becca.origin` | A browser sent an `Origin` sanda does not answer |
| 404 | `becca.no_such_set` | No table by that name. The message lists the tables there are |
| 405 | `becca.method` | Not a `GET` or `HEAD` |
| 502 | `becca.warehouse` | The read timed out, or the warehouse failed. A timeout says to narrow it with `$filter` or `$select` |
| 503 | `becca.no_warehouse` | The workspace has no warehouse to read yet |

## Try it from a terminal

`-u ":$TOKEN"` is the Basic form with an empty user name.

```bash
TOKEN=bat_your_token_here

# every table this workspace offers
curl -s https://console.sanda-os.com.au/api/odata/ -u ":$TOKEN"

# the columns and their types
curl -s 'https://console.sanda-os.com.au/api/odata/$metadata' -u ":$TOKEN"

# ten rows
curl -s 'https://console.sanda-os.com.au/api/odata/invoices?$top=10' -u ":$TOKEN"

# this year, by region
curl -s 'https://console.sanda-os.com.au/api/odata/invoices_summary?$select=region,net_revenue&$filter=issue_date%20ge%202026-01-01' \
  -u ":$TOKEN"

# the same with a bearer header
curl -s https://console.sanda-os.com.au/api/odata/ -H "Authorization: Bearer $TOKEN"
```

Replace `invoices`, `invoices_summary` and the column names with your own, which the service document and `$metadata` list. Put a space in a URL as `%20`.

## Next steps

:::links
- [Connect Excel](https://docs.sanda-os.com.au/odata/excel): Data, Get Data, From OData Feed.
- [Connect Power BI](https://docs.sanda-os.com.au/odata/power-bi): Desktop, Power Query and scheduled refresh.
- [Filters](https://docs.sanda-os.com.au/reference/filters): The operators and named periods sanda uses in its own filters.
:::