--- title: "Lakehouse" description: "Query your project's Parquet lakehouse with SQL over DuckDB. Project-scoped, isolated analytics as part of ZeroDB — Lakehouse-as-a-Service." canonical: "https://docs.ainative.studio/docs/api/zerodb-lakehouse" last-updated: "2026-10-13T21:42:35.442Z" --- # Lakehouse Source: https://docs.ainative.studio/docs/api/zerodb-lakehouse > Query your project's Parquet lakehouse with SQL over DuckDB. Project-scoped, isolated analytics as part of ZeroDB — Lakehouse-as-a-Service. # 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. :::info 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: ` or `Authorization: Bearer `. The `{project_id}` in the path must be a project you own; otherwise the request returns `404 Not Found`. ### Getting an API key If you don't already have one: - **Instant DB (fastest, zero-auth):** `POST /api/v1/public/instant-db` with `{"agree_terms": true}` returns a ready `project_id` **and** `api_key`. See [Instant Database](./instant-db). - **From your account:** issue a key at `POST /api/v1/api-keys` (authenticated with your user JWT). Use the returned key as `$ZERODB_API_KEY` below. ```bash # One-request project + key curl -X POST https://api.ainative.studio/api/v1/public/instant-db \ -H "Content-Type: application/json" -d '{"agree_terms": true}' # → { "project_id": "…", "api_key": "…" } ``` --- ## Execute a query Run a read-only SQL query against your lakehouse tables. Reference tables by their **logical name** — the `sensor_type` you used when ingesting — and 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) | Query the columns you ingested. For example, if you ingested `ais` rows with `mmsi` and `sog` fields: ```bash 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 count(*) AS rows, avg(sog) AS avg_speed FROM ais", "max_rows": 500 }' ``` :::note Auto-added columns Every ingested row gains two columns the platform adds from the partition layout: **`date`** (the ingest date, `YYYY-MM-DD`) and **`tenant`** (your `project_id`). They show up in `SELECT *` alongside the fields you sent — handy for date filtering (`WHERE date = '2026-07-17'`), and safe to ignore otherwise. ::: ```python 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** ```json { "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 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) | ```bash 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** ```json { "records_written": 2, "sensor_type": "ais", "project_id": "..." } ``` Then read it straight back: ```bash curl -X POST .../database/lakehouse/query \ -H "X-API-Key: $ZERODB_API_KEY" \ -d '{"sql": "SELECT mmsi, sog FROM ais ORDER BY sog DESC"}' ``` :::tip 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 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. | ```bash # 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" ``` ```json { "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 List the logical tables (sensor/dataset types) present in **your** project's partition. ```bash curl https://api.ainative.studio/api/v1/projects/$PROJECT_ID/database/lakehouse/tables \ -H "X-API-Key: $ZERODB_API_KEY" ``` ```json [ {"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 Aggregate storage statistics for your project's lakehouse partition. ```json { "total_files": 15, "total_bytes": 4456448, "sensor_types": 2, "tables": [ /* same shape as /tables */ ] } ``` --- ## Connectors Status of the data connectors writing into your project's partition — online / offline, last-data timestamp, and approximate ingest volume. ```json [ {"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` | Rate limit exceeded — max **60 requests/min per project** across query + ingest. Honor the `Retry-After` header. | | `503` | Query engine or object storage not configured | --- ## Rate limits Query and ingest share a limit of **60 requests per minute, per project**. Exceeding it returns `429` with a `Retry-After` header (seconds). Batch your writes (up to 5,000 records per ingest call) and cache query results rather than polling in a tight loop. --- ## 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](./instant-db) instance when you need writes, indexes, or Postgres extensions.