# Push Data

Source: https://www.hotdata.dev/docs/push-data
Site index: https://www.hotdata.dev/llms.txt

You can push data into Hotdata through the API. This page covers the loading model, the upload sequence, and the write modes for publishing to a table; for data Hotdata pulls from systems you connect, see [Pull Data](/docs/pull-data).

## The loading model

Teams arriving from a relational database generally expect `CREATE TABLE` followed by repeated `INSERT` statements. Hotdata does not work that way.

There is no DDL to run and no transactional insert path — SQL here is read-only. Data is loaded by _publishing_ it: a file is supplied, and it becomes the contents of a table. Tables are created on first publish, so no migration step is required. Every publish is atomic — readers see either the previous contents or the new ones, never a partially written table.

The engine is optimised for bulk operations. A single call carrying ten million rows is the intended pattern; ten million single-row calls are not. Integrations that write row-at-a-time should batch upstream and publish at whatever interval freshness allows.

Every publish, regardless of size, goes through one endpoint:

```text
POST /v1/databases/{database_id}/schemas/{schema}/tables/{table}/loads
```

The body carries a `mode` — how the incoming rows are applied to those already present, covered in the next section — and **exactly one** source. More than one source, or an unrecognised field name, is rejected with a `400` rather than silently ignored.

| Source | Description | Formats | Ceiling |
| --- | --- | --- | --- |
| `upload_id` | A file already placed in storage through an upload session. The primary path for anything of size. | Parquet, CSV, JSON (NDJSON) | terabytes |
| `data` | CSV text inline in the request body, header row included. Types are inferred unless you declare `columns`. | CSV | 2 MiB |
| `result_id` | A persisted query result, copied into a table so the data outlives the result. | — | — |

### The upload sequence

File contents do not travel through the API. Every upload follows the same three steps, with a fourth call to publish what arrived:

1. **Request an upload session.** `POST /v1/uploads` — declare the approximate size, and the response returns an upload id, one or more URLs to write to, and a finalize token.
2. **PUT the bytes to the URL.** They are written directly to object storage, so a 40 GB transfer does not hold an API connection open for its duration.
3. **Finalize.** `POST /v1/uploads/{upload_id}/finalize` commits the upload, after which the file can be loaded.
4. **Load it.** The endpoint above, with the `upload_id` as its source.

Only the second step varies with file size: a single PUT or several. The choice need not be made in advance — open a session and read `mode` from the response.

### A single file

For a file small enough to send in one request, the sequence is four calls and a single transfer.

**Finalize is the commit, and it occurs exactly once.** Before it the session is merely open; after it the file is loadable, and a second finalize returns `400`. Step 4 is deliberately a separate call: an upload can be held and loaded later, but consumed only once. Presigned URLs are valid for 30 minutes; the session itself for 24 hours.

> **Do not hardcode the size threshold** — Whether a session returns `single` or `multipart` depends on the declared size and on how the instance is configured; the default cutover is 8 MiB, but it is not a constant. Declare `declared_size_bytes`, read `mode` from the response, and branch on that. The same applies to `part_size`: use the value supplied.

### A large file

Above the threshold, the session returns as `multipart` with a list of part URLs. PUT the slices concurrently, retain every `ETag`, and submit the set at finalize. Where the total size is not known in advance — a stream, for instance — omit `declared_size_bytes` and mint part URLs as the transfer proceeds. The finalize call is unchanged, and no URL expires while waiting on a slow producer.

**Slice by `part_size`, not by part count.** Dividing the file evenly by the number of URLs supplied can produce an intermediate part below the 5 MiB minimum, and the error surfaces only when finalize rejects the set. The final part is the only one permitted to be short. A rejected set returns `4xx` rather than `5xx` and the session survives, so the parts can be corrected and finalize retried.

### Multiple files

Open all the sessions in a single call, then transfer them with whatever concurrency suits. Loading is the step that cannot be parallelised: a load takes a write lock on its target table, so a second concurrent load into the same table returns `409`.

**There is no manifest, prefix, or glob ingest.** One load consumes one upload into one table, so a directory of N files is N loads. Each is atomic and the sequence is resumable: an upload that has already been consumed replays its original result rather than loading twice. Where orchestrating the sequence is undesirable, concatenate upstream and upload a single larger file instead.

### Small payloads

Below 2 MiB, the upload sequence adds more overhead than the payload warrants. CSV text can go directly in the load call's `data` field — one call, no session to finalize. This suits a few thousand rows of reference data, a lookup table, or the remainder of a stream flushed on a timer.

**Same publish, same atomicity, one fewer round trip.** Exceeding 2 MiB returns `413` with the code `INLINE_DATA_TOO_LARGE` — branch on that code and fall back to the upload sequence rather than estimating the limit in advance. Inline data is CSV with a header row; declare `columns` to set types explicitly instead of having them inferred.

### Ingestion limits

The figures below apply across all of the paths above. Two of them — the multipart threshold and the maximum upload size — are deployment settings rather than fixed values, which is why the session response is the authority on which mode applies.

| Limit | Value | Notes |
| --- | --- | --- |
| Inline `data` | 2 MiB | Exceeding this returns `413 INLINE_DATA_TOO_LARGE` |
| Single PUT ceiling | 5 GiB | Above this, multipart is mandatory |
| Part size | 5 MiB – 5 GiB | 8 MiB by default; use the value from the session |
| Parts per upload | 10,000 | Determines the smallest viable part size |
| Part URLs per mint call | 100 | Streaming uploads only |
| Sessions per batch call | 100 | Via `POST /v1/uploads/batch` |
| Presigned URL lifetime | 30 min | Re-mint rather than retrying a stale URL |
| Upload session lifetime | 24 h | Unfinalized sessions are removed |

> **Pull-based ingestion** — Everything above is push-based: the caller holds the data and submits it. Pulling from a system you do not control — a warehouse, a SaaS API, or a bucket on a schedule — is a separate surface with its own lifecycle. Integrations shaped like a connector rather than a writer should build against [Pull Data](/docs/pull-data) instead of the load endpoint.

## Load modes

`mode` determines what happens to the rows already in the table. `replace` and `append` require nothing further; the remaining three require a `key` — the columns that identify a row — either declared on the table or supplied with the load.

| Mode | Effect on existing rows | Key required | Typical use |
| --- | --- | --- | --- |
| `replace` | All are discarded; the payload becomes the table's contents. | — | Full snapshots, and the first load into any new table. |
| `append` | Retained; the new rows are added alongside them. | — | Immutable events, or combining several files into one table. |
| `upsert` | Matching rows are replaced; unmatched payload rows are inserted. | yes | Incremental synchronisation. Supply full rows. |
| `update` | Matching rows are replaced. Payload rows with no match are ignored. | yes | Corrections that must not create rows. |
| `delete` | Rows whose keys appear in the payload are removed. | yes | Propagating deletions. Supply keys only, not full rows. |

### Constraints

**The first load into a new table must be `replace`.** Every other mode requires a table that already has a shape to modify, and requesting one against a never-loaded table returns a `400` stating as much. Whether `append` and the keyed modes are available at all also depends on how the instance is provisioned, so treat that `400` as a branch to handle rather than an assertion failure.

**A key identifies rows for matching; it is not a uniqueness constraint.** The `key` names the columns that match incoming rows against existing ones — the match `upsert`, `update`, and `delete` act on. Nothing verifies the key is unique, and duplicates are not rejected. It is resolved per load — from the request where supplied, otherwise from the table's declaration — so changing it between loads silently changes what the merge matches on. Keeping it stable for a given table is the caller's responsibility.

**Schema changes are accepted only where they are lossless.** A column the table has not seen before is added. A type that widens without loss is promoted. A narrowing or cross-family change — text to number, or a decimal whose scale has moved — is refused with a `409` before anything is written; the table remains as it was and remains queryable.

> **Idempotency** — Inline loads should carry an `idempotency_key`. Replaying it with the same payload returns the original result; replaying it with a different payload returns a `409` and writes nothing. Uploads do not require one — the `upload_id` serves as the idempotency key, as a consumed upload replays its receipt. A `409` never indicates that data was written twice.

## Error handling

Errors are returned as `{ "error": { "message", "code" } }`. Branch on the code, never the message. Every response — rejections included — carries an `X-Trace-Id`, which should be logged and quoted in support requests.

| Status | Code | Meaning | Action |
| --- | --- | --- | --- |
| 413 | `INLINE_DATA_TOO_LARGE` | Inline payload above 2 MiB. | Fall back to an upload session; this case is recoverable. |
| 501 | `PRESIGN_UNSUPPORTED` | This instance cannot issue upload URLs. | Send the data inline instead. |
| 409 | — | The table is busy, the upload is already being consumed, or an incompatible column type change was requested. | Retry after a short delay. Nothing was written. |
| 400 | `BAD_REQUEST` | Two sources supplied, a mode the table cannot accept, a keyed mode without a key, or malformed CSV. | Correct the request. Validation occurs before any job is created. |
| 404 | `NOT_FOUND` | Unknown database, or a table that has been deleted. | A deleted table is not recreated by a load; declare it again. |
| 429 | `OVERLOADED` | The instance is shedding load. | Honour `Retry-After`. |

## Querying and result formats

One endpoint runs SQL. It responds in JSON with a preview of the rows and, for larger results, a `result_id` that can be retrieved in the format required.

```text
POST /v1/query
X-Database-Id: dbid6lguax1dxn9y1xj5gxnameyywl

{ "sql": "SELECT status, count(*) FROM default.main.orders GROUP BY 1" }

# => { "query_run_id": …, "result_id": …, "columns": […], "rows": […], "truncated": false }

GET /v1/results/{result_id}?format=arrow   # or parquet, csv, md, json
```

Use Arrow or Parquet for any significant volume; JSON is provided for convenience rather than throughput. Large results paginate with `offset` and `limit` and return a `Link` header for the next page; Parquet is returned whole.

Queries are read-only. `INSERT`, `COPY`, and DDL are refused for every caller — writing is the load endpoint's job.

The dialect is HotSQL. It is standard analytical SQL with additions for search and geospatial work; the full syntax and function reference is at [/docs/sql](/docs/sql).

## Further reading

- [Core Concepts](/docs/core-concepts) — the object model and authentication.
- [Pull Data](/docs/pull-data) — ingesting from sources you connect.
- [API Reference](/docs/api-reference) — request and response shapes, field by field.
- [SQL Reference](/docs/sql) — the HotSQL syntax and function reference.
- [Python SDK](/docs/python-sdk) — the Python client.
- [Rust SDK](/docs/rust-sdk) — the Rust client.