ZeroDB Lakehouse

The ZeroDB Lakehouse is a query-in-place analytics layer over your project's Parquet data. Run read-only SQL directly against columnar files in object storage via DuckDB — no cluster to manage, no ETL to run, no data to move.

Like every other ZeroDB service, the Lakehouse is project-scoped: every request is confined to your own project's partition. You can only ever read your own data.

Isolation guarantee

Reads are physically scoped to tenant={project_id}/… in object storage and the caller's project ownership is verified on every request. A project can never read another project's or tenant's data. Raw filesystem/S3 paths in SQL are rejected. (Refs #5270, #5271)

Base URL & Auth

https://api.ainative.studio/api/v1/projects/{project_id}/database/lakehouse

All endpoints require authentication — either a project API key or a user JWT. The API key may be sent as X-API-Key: <key> or Authorization: Bearer <key>.

The {project_id} in the path must be a project you own; otherwise the request returns 404 Not Found.

Execute a query

POST/api/v1/projects/{project_id}/database/lakehouse/query🔒

Run a read-only SQL query against your lakehouse tables. Reference tables by their logical name (the sensor/dataset type) — the engine expands each name to your project's scoped Parquet files.

Request body

Field	Type	Default	Description
`sql`	string	—	A single `SELECT` or `WITH` statement over logical tables
`max_rows`	integer	1000	Row cap (max 10,000)

curl -X POST \
  https://api.ainative.studio/api/v1/projects/$PROJECT_ID/database/lakehouse/query \
  -H "X-API-Key: $ZERODB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT sensor_type, count(*) AS n FROM ais GROUP BY 1 ORDER BY n DESC",
    "max_rows": 500
  }'

import requests, os

resp = requests.post(
    f"https://api.ainative.studio/api/v1/projects/{os.environ['PROJECT_ID']}/database/lakehouse/query",
    headers={"X-API-Key": os.environ["ZERODB_API_KEY"]},
    json={"sql": "SELECT * FROM weather WHERE ts > '2026-07-01' LIMIT 100"},
)
data = resp.json()
print(data["columns"])
for row in data["rows"]:
    print(row)

Response

{
  "columns": ["sensor_type", "n"],
  "rows": [["ais", 1240], ["weather", 310]],
  "row_count": 2,
  "execution_time_ms": 42.7,
  "truncated": false
}

SQL rules

The query runs in a sandbox designed to keep your data isolated:

SELECT / WITH only. DDL/DML (INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, …) is rejected.
Logical tables only. Reference tables by name (ais, weather, …). Raw path access — read_parquet(...), read_csv(...), s3://…, glob(...), ATTACH, COPY, SET, PRAGMA, INSTALL, LOAD — is rejected so a query can never point at another project's files.
Single statement. Multiple ;-separated statements are rejected.
Row cap. Results are capped at max_rows (default 1000, max 10,000). When capped, truncated is true.

Violations return 400 Bad Request with a descriptive detail.

Ingest records

POST/api/v1/projects/{project_id}/database/lakehouse/ingest🔒

Write records into your project's lakehouse partition. Data is written under tenant={project_id}/{sensor_type}/… and flushed immediately, so it's queryable via /query right away — and never visible to any other project.

Request body

Field	Type	Description
`sensor_type`	string	Logical table to write into (e.g. `ais`). Must be a simple identifier.
`records`	array<object>	Rows to write (1–5000 per call)

curl -X POST \
  https://api.ainative.studio/api/v1/projects/$PROJECT_ID/database/lakehouse/ingest \
  -H "X-API-Key: $ZERODB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sensor_type": "ais",
    "records": [
      {"mmsi": "123456789", "lat": 37.8, "lon": -122.4, "sog": 12.1, "ts": "2026-07-17T10:00:00Z"},
      {"mmsi": "987654321", "lat": 37.9, "lon": -122.5, "sog": 0.2, "ts": "2026-07-17T10:00:05Z"}
    ]
  }'

Response

{ "records_written": 2, "sensor_type": "ais", "project_id": "..." }

Then read it straight back:

curl -X POST .../database/lakehouse/query \
  -H "X-API-Key: $ZERODB_API_KEY" \
  -d '{"sql": "SELECT mmsi, sog FROM ais ORDER BY sog DESC"}'

Read-after-write

Ingested rows are flushed on write, so a subsequent /query sees them immediately. A brand-new project with no data returns an empty result (row_count: 0), never an error.

Delete data

DELETE/api/v1/projects/{project_id}/database/lakehouse/data🔒

Delete data from your project's partition — a single table, or the entire project partition. Scoped to tenant={project_id}/, so you can only ever delete your own data.

Query param	Description
`sensor_type`	Table to delete (e.g. `ais`). Omit to target the whole project.
`confirm`	Required (`true`) to delete the entire project partition when `sensor_type` is omitted.

# Delete one table
curl -X DELETE \
  "https://api.ainative.studio/api/v1/projects/$PROJECT_ID/database/lakehouse/data?sensor_type=ais" \
  -H "X-API-Key: $ZERODB_API_KEY"

# Delete everything in the project's lakehouse (explicit confirm required)
curl -X DELETE \
  "https://api.ainative.studio/api/v1/projects/$PROJECT_ID/database/lakehouse/data?confirm=true" \
  -H "X-API-Key: $ZERODB_API_KEY"

{ "objects_deleted": 3, "sensor_type": "ais", "project_id": "..." }

caution

Deletes are permanent — the underlying Parquet objects are removed. Omitting sensor_type without confirm=true is rejected with 400 as a safety guard.

List tables

GET/api/v1/projects/{project_id}/database/lakehouse/tables🔒

List the logical tables (sensor/dataset types) present in your project's partition.

curl https://api.ainative.studio/api/v1/projects/$PROJECT_ID/database/lakehouse/tables \
  -H "X-API-Key: $ZERODB_API_KEY"

[
  {"name": "ais", "format": "parquet", "file_count": 12, "total_bytes": 4194304, "last_modified": "2026-07-17T09:12:00Z"},
  {"name": "weather", "format": "parquet", "file_count": 3, "total_bytes": 262144, "last_modified": "2026-07-17T08:00:00Z"}
]

Stats

GET/api/v1/projects/{project_id}/database/lakehouse/stats🔒

Aggregate storage statistics for your project's lakehouse partition.

{
  "total_files": 15,
  "total_bytes": 4456448,
  "sensor_types": 2,
  "tables": [ /* same shape as /tables */ ]
}

Connectors

GET/api/v1/projects/{project_id}/database/lakehouse/connectors🔒

Status of the data connectors writing into your project's partition — online / offline, last-data timestamp, and approximate ingest volume.

[
  {"source_id": "ais", "sensor_type": "ais", "status": "online",
   "last_data": "2026-07-17T09:12:00Z", "records_per_hour": 12, "category": "maritime"}
]

Errors

Status	Meaning
`400`	Invalid SQL (non-SELECT, raw path access, multiple statements, bad table name)
`401`	Missing/invalid credentials
`404`	Project not found or you don't own it
`429`	Query rate limit exceeded
`503`	Query engine or object storage not configured

How it compares

	ZeroDB Lakehouse	Dedicated Postgres
Best for	Large columnar scans, historical/append-only analytics	Transactional + rich extensions (TimescaleDB, PostGIS, pgvector)
Storage	Parquet in object storage (cheap, elastic)	Provisioned instance
Query	Read-only SQL via DuckDB	Full SQL/DDL
Isolation	Project-scoped partition	Dedicated instance

Use the Lakehouse for cheap, elastic scans over big append-only datasets; use a dedicated Postgres instance when you need writes, indexes, or Postgres extensions.

Base URL & Auth​

Execute a query​

SQL rules​

Ingest records​

Delete data​

List tables​

Stats​

Connectors​

Errors​

How it compares​