Documentation
API references
Lakehouse API

HTTP API

Use the Altertable HTTP API to run SQL, upsert files, append event-style JSON records, validate SQL, and request autocomplete suggestions without opening a database protocol connection.

Connection details

Hostname
Port
api.altertable.ai
443 (HTTPS)

Authentication

Authenticate every request with HTTP Basic Auth. Base64-encode your credentials in the format lakehouse_username:lakehouse_password:

echo -n "your_lakehouse_username:your_lakehouse_password" | base64

Then send the encoded value in the Authorization header:

Authorization: Basic <ALTERTABLE_BASIC_AUTH_TOKEN>

The Python and Ruby SDKs handle this for you when you pass your lakehouse username and lakehouse password during client initialization.

POST /query

Execute SQL queries and retrieve results in streaming JSONL format.

Request:

curl https://api.altertable.ai/query \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{
    "statement": "SELECT event, COUNT(*) AS count FROM product_analytics.main.events GROUP BY event"
  }'

Request Body:

{
  "statement": "SELECT ... your SQL query"
}

Response Format:

Responses use JSONL (JSON Lines) format for efficient streaming:

  1. First line: Query metadata (execution time, etc.)
  2. Second line: Column names and types
  3. Remaining lines: Result rows, one JSON object per line

Example Response:

{"statement": "SELECT ... your SQL query", "session_id": "1234567890", ...}
[{"name": "event", "type": "VARCHAR"}, {"name": "count", "type": "BIGINT"}]
["signup", 42]
["login", 123]
...

POST /upsert

Upsert data files to create or update tables in your lakehouse. Supports CSV, JSON, and Parquet formats with multiple insertion modes.

Request:

curl "https://api.altertable.ai/upsert?catalog=my_catalog&schema=public&table=users&mode=create" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  --data-binary @data.csv

Query Parameters:

  • catalog (required): Name of the catalog to upsert data to
  • schema (required): Name of the schema within the catalog
  • table (required): Name of the table to create or insert into
  • mode (optional): Upsert mode - create, append, upsert, or overwrite. Defaults to upsert
  • primary_key (optional): Primary key column name. Required when mode=upsert or when mode is omitted

Request Body:

Binary file data. The server infers CSV, JSON, or Parquet from the Content-Type header when present, otherwise from magic bytes in the payload:

  • CSV: Comma-separated values with header row
  • JSON: JSON array of objects or JSONL (one JSON object per line)
  • Parquet: Apache Parquet columnar format (most efficient)

Upsert Modes:

  • create: Create a new table with the upserted data (fails if table already exists)
  • append: Append the upserted data to an existing table (preserves existing data)
  • upsert: Update existing rows and insert new ones based on primary key (requires primary_key parameter)
  • overwrite: Drop the existing table and recreate it with the upserted data (replaces all data)

Response:

Returns 200 OK on successful upsert. The endpoint accepts files up to 100 GB in size.

Example: Upload CSV file

curl "https://api.altertable.ai/upsert?catalog=my_catalog&schema=public&table=users&mode=create" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  --data-binary @users.csv

Example: Append JSON file

curl "https://api.altertable.ai/upsert?catalog=my_catalog&schema=public&table=events&mode=append" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  --data-binary @events.json

Example: Upsert with primary key

curl "https://api.altertable.ai/upsert?catalog=my_catalog&schema=public&table=users&primary_key=id" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  --data-binary @updates.parquet

POST /append

Append data to a table using a simple, fire-and-forget API. Designed for streaming analytics data without managing batches, offsets, or pipelines.

Request:

curl "https://api.altertable.ai/append?catalog=my_catalog&schema=public&table=users" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"name": "Alice", "age": 30, "email": "[email protected]"}'

Query Parameters:

  • catalog (required): Name of the catalog to append data to
  • schema (required): Name of the schema within the catalog
  • table (required): Name of the table to append to

Request Body:

The request body can be either a single JSON object or an array of JSON objects:

Single object:

{
  "name": "Alice",
  "age": 30,
  "email": "[email protected]"
}

Batch of objects:

[
  { "name": "Alice", "age": 30, "email": "[email protected]" },
  { "name": "Bob", "age": 25, "email": "[email protected]" },
  { "name": "Charlie", "age": 35, "email": "[email protected]" }
]

Response:

Returns 200 OK with a JSON response indicating whether the append was accepted:

{
  "ok": true,
  "task_id": "019d68d4-c4cb-7e40-ad50-fd4b68c2045e",
  "error_message": null,
  "error_code": null
}

Use task_id with GET /tasks/:task-uuid when you need to poll asynchronous processing. When sync=true, the response is delayed until the append task finishes.

Key Features:

  • Automatic schema inference: The schema is automatically inferred from your JSON data
  • Automatic table creation: Tables are created automatically if they don't exist
  • Automatic schema migration: New columns are added automatically when you send data with additional fields
  • Fire-and-forget: Data is accepted immediately and processed asynchronously
  • Queryable in seconds: Data becomes available for querying within seconds of being sent

Schema Inference:

The endpoint automatically infers types from your JSON data:

  • Numbers → BIGINT (integers) or DOUBLE (floats)
  • Strings → VARCHAR
  • Booleans → BOOLEAN
  • Arrays/Objects → JSON (stored as JSON strings)
  • RFC3339 timestamps → TIMESTAMP (with microsecond precision)

Example: Single object

curl "https://api.altertable.ai/append?catalog=my_catalog&schema=public&table=events" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"event": "signup", "user_id": 123, "timestamp": "2025-12-30T10:00:00Z"}'

Example: Batch of objects

curl "https://api.altertable.ai/append?catalog=my_catalog&schema=public&table=events" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '[
    {"event": "signup", "user_id": 123, "timestamp": "2025-12-30T10:00:00Z"},
    {"event": "login", "user_id": 456, "timestamp": "2025-12-30T10:01:00Z"}
  ]'

Example: Schema evolution

Send data with new fields, and the table schema is automatically updated:

# First append - creates table with name and age
curl "https://api.altertable.ai/append?catalog=my_catalog&schema=public&table=users" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"name": "Alice", "age": 30}'


# Later append - automatically adds "email" column
curl "https://api.altertable.ai/append?catalog=my_catalog&schema=public&table=users" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"name": "Bob", "age": 25, "email": "[email protected]"}'

The email column is automatically added to the table. Existing rows will have NULL for the new column.

Manual Schema Creation:

While automatic schema inference is convenient, you can still create tables manually using SQL if you need fine-tuned control over types, constraints, or defaults:

CREATE TABLE my_catalog.main.events (
  event VARCHAR,
  user_id VARCHAR,
  created_at TIMESTAMP
);

When you use /append with a manually created table, it respects your existing schema and only adds new columns when they don't exist. Automatic inference is a convenience, not a requirement.

POST /validate

Validate a SQL statement without executing it. Use this endpoint to catch syntax and semantic issues before running a query.

curl https://api.altertable.ai/validate \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"statement": "SELECT * FROM non_existent_table"}'

POST /autocomplete

Return SQL autocomplete suggestions for a partial statement.

curl https://api.altertable.ai/autocomplete \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{
    "statement": "SELECT ma",
    "catalog": "my_catalog",
    "schema": "public",
    "max_suggestions": 20
  }'

The response includes the original statement, connection errors by catalog when present, and an array of suggestions with their type, score, and replacement start position.

GET /query/:query-uuid

Inspect the current status of a running or completed query.

curl https://api.altertable.ai/query/<query-uuid> \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN"

DELETE /query/:query-uuid

Cancel a running query by query ID and session ID.

curl -X DELETE https://api.altertable.ai/query/<query-uuid>?session_id=<session-id> \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN"

GET /tasks/:task-uuid

Inspect an asynchronous task returned by /append.

curl https://api.altertable.ai/tasks/<task-uuid> \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN"

The response includes the task ID and current status:

{
  "task_id": "019d68d4-c4cb-7e40-ad50-fd4b68c2045e",
  "status": "completed"
}

Task status is either pending or completed.

Crafted with <3 by former Algolia × Front × Sorare builders© 2026 AltertableTermsPrivacySecurityCookies