> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ticksupply.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Export Schemas
> Define custom column mappings to control the shape of your exported data
# Export Schemas
An export schema defines how raw exchange data is transformed into structured columns in your exported CSV files. Each column maps to either system metadata (like timestamps) or a value extracted from the exchange's JSON message.
## Built-in vs custom schemas
Every account has access to built-in schemas like `raw` and `normalized`. Reference them by name when creating an export:
```json theme={"system"}
{
"datastream_id": 123,
"start_time": "2024-01-15T10:00:00Z",
"end_time": "2024-01-16T10:00:00Z",
"schema": "normalized"
}
```
`normalized` is a shared name that covers most stream categories. When you reference `normalized`, the backend picks the version matching your datastream's category automatically. Orderbook-specific built-ins `book_5` and `book_20` are also available for top-N snapshots.
The [List export schemas](/api-reference/export-schemas/list-export-schemas) endpoint returns all database-backed schemas (built-in `normalized` variants, `book_5`, `book_20`, and your custom ones). The special `raw` name is always available as a schema reference but is not returned by the list endpoint.
| Built-in schema | `stream_category` targets | Notes |
| --------------- | -------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `raw` | all categories | Default. Two-column `collection_timestamp_ns` + raw JSON. Not returned by the list endpoint. |
| `normalized` | `trade`, `orderbook`, `book_update`, `quote`, `kline`, `liquidation` | Referenced by name; backend picks the version matching your datastream's category. No `ticker` version — use `raw` or a custom schema for that category. |
| `book_5` | `orderbook` | Top-5 depth snapshot. |
| `book_20` | `orderbook` | Top-20 depth snapshot. |
When built-in schemas don't fit your needs, you can create a custom schema with exactly the columns you want.
Built-in schemas are read-only. `DELETE /v1/export-schemas/{id}` on a built-in returns `404 not_found` — only custom schemas owned by your account can be deleted.
## Stream categories
Each schema is scoped to one `stream_category`. Built-in schemas have one version per category; custom schemas target a single category. Schema names are unique per `(account, stream_category)` pair — you can reuse the same name across different categories.
| Category | Describes |
| ------------- | ----------------------------------------------------- |
| `trade` | Executed trades |
| `orderbook` | Full or top-N order book snapshots |
| `book_update` | Incremental order book deltas (bid/ask level changes) |
| `quote` | Best bid/ask quotes |
| `kline` | OHLCV candles |
| `ticker` | 24h rolling ticker summaries |
| `liquidation` | Forced liquidations (derivatives) |
## Exchange codes
Every entry in a schema's `data` and `unfold` maps is keyed by an exchange code. The [List exchanges](/api-reference/catalog/list-exchanges) endpoint is the source of truth for which codes you can use.
| Code | Exchange |
| -------------- | ----------------------------------------------- |
| `binance` | Binance |
| `bybit_linear` | Bybit Linear (USDT/USDC perpetuals and futures) |
| `bybit_spot` | Bybit Spot |
| `okx_spot` | OKX Spot |
A schema only needs rules for the exchanges you plan to export. If an export pulls rows from an exchange a column doesn't cover, the export fails — so include every exchange that any datastream in the export belongs to.
## Schema structure
A schema has a `name`, a `stream_category` it applies to, a list of `columns`, and an optional `unfold` configuration:
```json theme={"system"}
{
"name": "my_trades",
"stream_category": "trade",
"unfold": {
"bybit_linear": { "path": "data" }
},
"columns": [
{ "output_column": "ts", "meta": { "value": "collection_timestamp_ns" } },
{ "output_column": "price", "data": { ... } }
]
}
```
Each column has an `output_column` (the column name in output) and exactly one of:
* **`meta`** — extract a system metadata value
* **`data`** — extract from the exchange's JSON message, with per-exchange rules
A schema must have at least one column and by default can have up to 100. Contact support if you need a higher limit for your account.
## Meta columns
Meta columns extract system-level values that are consistent across all exchanges:
| Value | Description |
| ------------------------- | -------------------------------------------------- |
| `collection_timestamp_ns` | When Ticksupply received the message (nanoseconds) |
You can control the output format with the `format` field:
| Format | Output | Example |
| -------------- | ------------------------ | ---------------------- |
| `ns` (default) | Nanoseconds since epoch | `1705312800000000000` |
| `us` | Microseconds since epoch | `1705312800000000` |
| `ms` | Milliseconds since epoch | `1705312800000` |
| `s` | Seconds since epoch | `1705312800` |
| `iso8601` | ISO 8601 string | `2024-01-15T10:00:00Z` |
```json theme={"system"}
{
"output_column": "timestamp",
"meta": {
"value": "collection_timestamp_ns",
"format": "ms"
}
}
```
## Data columns
Data columns extract values from the exchange's raw JSON message. Because different exchanges use different JSON structures, you provide extraction rules per exchange.
```json theme={"system"}
{
"output_column": "price",
"data": {
"binance": {
"json": { "path": "data.p", "type": "decimal(18)" }
},
"bybit_linear": {
"json": { "path": "data.p", "type": "decimal(18)" }
}
}
}
```
Binance messages are stored with the exchange's combined-stream envelope — `{"stream": "...", "data": {...}}` — so Binance paths start with `data.`. Other exchanges use their own envelope shape; always match the raw message structure you see in the data stream.
### JSON extraction
Each exchange extractor has a `json` field with:
* **`path`** — dot-notation path into the JSON message (e.g., `"p"`, `"data.price"`, `"info.amount"`). Positive integer segments are treated as **1-indexed** array indices, so `"data.1.price"` picks the first element's `price` field.
* **`type`** — output data type
| Type | Use for | Example |
| ------------ | --------------------------------------------------- | ------------- |
| `decimal(N)` | Prices, quantities (N decimal places, `0 ≤ N ≤ 38`) | `decimal(18)` |
| `f64` | Floating-point values like percentages | `f64` |
| `i64` | Integer values like trade IDs | `i64` |
| `string` | Text values like side ("Buy"/"Sell") | `string` |
| `bool` | Boolean flags | `bool` |
Use `decimal(18)` for financial values like price and quantity. It preserves exact precision, unlike `f64` which can introduce floating-point rounding. Values that don't fit become `NULL`.
`decimal(N)` values have 38 digits of total precision. The scale `N` (`0 ≤ N ≤ 38`) is how many of those digits sit after the decimal point — the rest are available for the integer part. `decimal(18)` leaves 20 integer digits, which fits any crypto price or quantity. At `decimal(38)` every value ≥ 1 overflows and is stored as `NULL`. Scales above 38 cause the export to fail.
### Transforms
When the raw value needs post-processing, add a `transform` — a SQL expression where `{v}` is the extracted value.
A common example: Binance represents trade side as a boolean (`m` = is the buyer the market maker?), while most downstream systems expect `"buy"` or `"sell"`:
```json theme={"system"}
{
"output_column": "side",
"data": {
"binance": {
"json": { "path": "data.m", "type": "bool" },
"transform": "CASE WHEN {v} = false THEN 'buy' ELSE 'sell' END"
},
"bybit_linear": {
"json": { "path": "data.S", "type": "string" }
}
}
}
```
Other transform examples:
| Use case | Transform |
| -------------------- | ---------------------- |
| Convert to lowercase | `lower({v})` |
| Divide by 100 | `{v} / 100` |
| Default for nulls | `coalesce({v}, 0)` |
| Extract substring | `substring({v}, 1, 3)` |
## Unfold
Some exchanges pack multiple events into a single WebSocket message as a JSON array. For example, Bybit trade messages look like:
```json theme={"system"}
{
"topic": "publicTrade.BTCUSDT",
"data": [
{ "p": "50000.00", "v": "0.001", "S": "Buy" },
{ "p": "50001.00", "v": "0.002", "S": "Sell" }
]
}
```
Without unfold, this entire message would become one row. With unfold, each element in the `data` array becomes its own row — so the example above produces two rows.
Configure unfold per exchange by specifying the path to the array:
```json theme={"system"}
{
"unfold": {
"bybit_linear": { "path": "data" },
"bybit_spot": { "path": "data" },
"okx_spot": { "path": "data" }
}
}
```
The unfold `path` accepts dot-notation for nested arrays (e.g., `"result.trades"`) — point it directly at the array regardless of how deep it sits in the message.
Column `path` values stay the same whether or not unfold is configured — always use the full JSON path from the raw message. When unfold matches, the backend strips the unfold prefix before extracting from each array element.
For example, with unfold `{"path": "data"}` on Bybit Linear and a column `"path": "data.p"`, the backend:
1. Array-joins each element of `data` into its own row.
2. Strips the `data.` prefix from `data.p`.
3. Extracts `p` from each element.
| Unfold config | Column `path` | Extracts from each row |
| ------------------ | ------------- | ------------------------------------------- |
| *(none)* | `data.p` | `p` inside the top-level `data` object |
| `{"path": "data"}` | `data.p` | `p` inside each element of the `data` array |
| `{"path": "data"}` | `data` | the whole array element |
Unfold only applies to exchanges that batch events. Exchanges like Binance that send one event per message don't need unfold rules — just omit them.
## Derive
Some exchanges split related values across multiple arrays in a single message. For example, Binance book-update messages carry separate `bids` (`b`) and `asks` (`a`) arrays:
```json theme={"system"}
{
"stream": "btcusdt@depth",
"data": {
"b": [ ["50000.00", "0.5"], ["49999.00", "1.2"] ],
"a": [ ["50001.00", "0.3"], ["50002.00", "0.8"] ]
}
}
```
If you want one row per price level — tagged with `bid`/`ask` — you need the two arrays merged before unfold splits them apart. That's what `derive` does: it computes a synthetic field on the raw message before column extraction runs, so `unfold` and column paths can reference it like any other field.
Configure derive per exchange. Each entry is a list of synthetic fields:
```json theme={"system"}
{
"derive": {
"binance": [
{
"field": "changes",
"op": "tagged_concat",
"arrays": [
{ "path": "data.b", "tag": "bid" },
{ "path": "data.a", "tag": "ask" }
],
"tag_field": "side",
"value_field": "level"
}
]
}
}
```
This builds a single `changes` array where each element looks like `{"side": "bid", "level": ["50000.00", "0.5"]}` (or `"ask"`). The only operation currently supported is `tagged_concat`, which:
* concatenates every array in `arrays` into one,
* wraps each source element under `value_field` (here `level`),
* tags it with the corresponding `tag` under `tag_field` (here `side`).
Once derived, point `unfold` at the synthetic field and let column paths extract from each element:
```json theme={"system"}
{
"unfold": {
"binance": { "path": "changes" }
},
"columns": [
{ "output_column": "side", "data": { "binance": { "json": { "path": "changes.side", "type": "string" } } } },
{ "output_column": "price", "data": { "binance": { "json": { "path": "changes.level.1", "type": "decimal(18)" } } } },
{ "output_column": "quantity", "data": { "binance": { "json": { "path": "changes.level.2", "type": "decimal(18)" } } } }
]
}
```
Column `path` values for derived fields reference the synthetic field name directly (e.g., `changes.side`), not the original array path. The built-in `normalized` / `book_update` schema uses exactly this pattern — fetch it with [Get export schema](/api-reference/export-schemas/get-export-schema) to see a full working example.
## Creating schemas
### Dashboard
The [dashboard schema editor](https://app.ticksupply.com/schemas/new) is the easiest way to create schemas. It provides a visual builder where you select exchanges, configure JSON paths and data types per column, set up unfold rules, and see a live JSON preview. You can switch between the visual editor and the raw JSON view at any time.
### API
To create a schema programmatically, use the [Create export schema](/api-reference/export-schemas/create-export-schema) endpoint. Here's a complete trade schema covering Binance and Bybit Linear with unfold:
```bash cURL theme={"system"}
curl -X POST https://api.ticksupply.com/v1/export-schemas \
-H "X-Api-Key: YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
"name": "my_trades",
"stream_category": "trade",
"unfold": {
"bybit_linear": { "path": "data" }
},
"columns": [
{
"output_column": "timestamp_ns",
"meta": { "value": "collection_timestamp_ns", "format": "ns" }
},
{
"output_column": "price",
"data": {
"binance": { "json": { "path": "data.p", "type": "decimal(18)" } },
"bybit_linear": { "json": { "path": "data.p", "type": "decimal(18)" } }
}
},
{
"output_column": "quantity",
"data": {
"binance": { "json": { "path": "data.q", "type": "decimal(18)" } },
"bybit_linear": { "json": { "path": "data.v", "type": "decimal(18)" } }
}
},
{
"output_column": "side",
"data": {
"binance": {
"json": { "path": "data.m", "type": "bool" },
"transform": "CASE WHEN {v} = false THEN '\''buy'\'' ELSE '\''sell'\'' END"
},
"bybit_linear": { "json": { "path": "data.S", "type": "string" } }
}
}
]
}'
```
```python Python theme={"system"}
from ticksupply import Client
client = Client(api_key="YOUR_API_KEY")
schema = client.export_schemas.create(
name="my_trades",
stream_category="trade",
unfold={
"bybit_linear": {"path": "data"}
},
columns=[
{
"output_column": "timestamp_ns",
"meta": {"value": "collection_timestamp_ns", "format": "ns"}
},
{
"output_column": "price",
"data": {
"binance": {"json": {"path": "data.p", "type": "decimal(18)"}},
"bybit_linear": {"json": {"path": "data.p", "type": "decimal(18)"}}
}
},
{
"output_column": "quantity",
"data": {
"binance": {"json": {"path": "data.q", "type": "decimal(18)"}},
"bybit_linear": {"json": {"path": "data.v", "type": "decimal(18)"}}
}
},
{
"output_column": "side",
"data": {
"binance": {
"json": {"path": "data.m", "type": "bool"},
"transform": "CASE WHEN {v} = false THEN 'buy' ELSE 'sell' END"
},
"bybit_linear": {"json": {"path": "data.S", "type": "string"}}
}
}
]
)
print(f"Created schema: {schema.id}")
```
```javascript JavaScript theme={"system"}
const schema = {
name: "my_trades",
stream_category: "trade",
unfold: {
bybit_linear: { path: "data" }
},
columns: [
{
output_column: "timestamp_ns",
meta: { value: "collection_timestamp_ns", format: "ns" }
},
{
output_column: "price",
data: {
binance: { json: { path: "data.p", type: "decimal(18)" } },
bybit_linear: { json: { path: "data.p", type: "decimal(18)" } }
}
},
{
output_column: "quantity",
data: {
binance: { json: { path: "data.q", type: "decimal(18)" } },
bybit_linear: { json: { path: "data.v", type: "decimal(18)" } }
}
},
{
output_column: "side",
data: {
binance: {
json: { path: "data.m", type: "bool" },
transform: "CASE WHEN {v} = false THEN 'buy' ELSE 'sell' END"
},
bybit_linear: { json: { path: "data.S", type: "string" } }
}
}
]
};
const response = await fetch("https://api.ticksupply.com/v1/export-schemas", {
method: "POST",
headers: {
"X-Api-Key": "YOUR_API_KEY",
"Content-Type": "application/json"
},
body: JSON.stringify(schema)
});
console.log(await response.json());
```
```rust Rust theme={"system"}
use ticksupply::resources::export_schemas::{
DataType, ExchangeExtractor, MetaExtraction, MetaValue, SchemaColumn,
SchemaContent, StreamCategory, TimestampFormat, UnfoldConfig,
};
use ticksupply::Client;
let client = Client::with_api_key("YOUR_API_KEY")?;
let content = SchemaContent::builder()
.column(SchemaColumn::meta(
"timestamp_ns",
MetaExtraction::new(MetaValue::CollectionTimestampNs).format(TimestampFormat::Ns),
))
.column(
SchemaColumn::data("price")
.exchange("binance", ExchangeExtractor::json("data.p", DataType::Decimal(18)))
.exchange("bybit_linear", ExchangeExtractor::json("data.p", DataType::Decimal(18))),
)
.column(
SchemaColumn::data("quantity")
.exchange("binance", ExchangeExtractor::json("data.q", DataType::Decimal(18)))
.exchange("bybit_linear", ExchangeExtractor::json("data.v", DataType::Decimal(18))),
)
.column(
SchemaColumn::data("side")
.exchange(
"binance",
ExchangeExtractor::json("data.m", DataType::Bool)
.transform("CASE WHEN {v} = false THEN 'buy' ELSE 'sell' END"),
)
.exchange("bybit_linear", ExchangeExtractor::json("data.S", DataType::Str)),
)
.unfold("bybit_linear", UnfoldConfig::new("data"))
.build();
let schema = client.export_schemas()
.create("my_trades", StreamCategory::Trade, content)
.send().await?;
println!("Created schema: {}", schema.id);
```
This produces a CSV with columns: `timestamp_ns`, `price`, `quantity`, `side` — normalized across both exchanges.
## Versioning
Custom schemas are versioned. Every schema starts at version `1` when created, and each schema response carries `version` (the latest published version number) and `has_draft` (whether an unpublished draft exists).
You can change a schema two ways:
**Atomic update** — replace the content in one call. Increments `version` by 1 and discards any draft.
```bash theme={"system"}
PUT /v1/export-schemas/{id}
```
**Draft workflow** — iterate on changes before they take effect.
```bash theme={"system"}
POST /v1/export-schemas/{id}/draft # create draft (empty body copies latest published)
GET /v1/export-schemas/{id}/draft # inspect current draft
PUT /v1/export-schemas/{id}/draft # replace draft content
DELETE /v1/export-schemas/{id}/draft # discard the draft
POST /v1/export-schemas/{id}/publish # promote draft to next version
```
While a draft exists, the published version stays untouched — the schema continues to behave exactly as before. Publishing increments `version` by 1 and clears `has_draft`.
Existing exports that referenced earlier versions are unaffected. Each export snapshots the schema content at the time it was created, so a schema change never alters output for already-created exports. Only exports created **after** publishing use the new version.
Built-in schemas are read-only — every versioning endpoint returns `404 not_found` for built-in IDs.
## Using schemas with exports
Once you have a schema (created via dashboard or API), you can reference it three ways when [creating an export](/api-reference/exports/create-export):
**By name** — for built-in or saved custom schemas:
```json theme={"system"}
{ "schema": "my_trades" }
```
**By ID** — for custom schemas:
```json theme={"system"}
{ "schema": "sch_0194a1b2c3d4e5f6a7b8c9d0e1f2a3b4" }
```
**Inline** — for ad-hoc schemas without saving:
```json theme={"system"}
{
"schema": {
"columns": [
{ "output_column": "ts", "meta": { "value": "collection_timestamp_ns" } },
{ "output_column": "price", "data": { "binance": { "json": { "path": "data.p", "type": "decimal(18)" } } } }
]
}
}
```
Inline schemas accept the same `unfold` and `derive` fields as saved schemas — useful when you need one-off batching or array merging without creating a reusable schema.
## Next steps
Save a reusable schema via the API
Use your schema to export data
View all available schemas
End-to-end guide from subscription to export