Submit a cross-tenant query

POST

/query-all

• Production
• Development

Runs the same SQL query across all warehouse tenant databases (or a specified subset) and combines results with a _bucket column. Returns a job ID for async polling.

Supports two modes:

• SQL mode: provide sql with a read-only query
• NL mode: provide question — SQL is generated once via MotherDuck’s prompt_sql() against the first database, then executed across all databases

The query is wrapped in SELECT * FROM (...) LIMIT {limit} per database. Results are combined and stored in DynamoDB. Poll via GET /query-all/jobs/{jobId}.

Cost note: Each invocation runs ~1 USE + 1 query per database (~260 MotherDuck operations for all tenants).

Authorizations

• ApiKeyAuth

Request Body ^required

Provide either sql or question, not both. Optional buckets array limits which databases are queried.

object

sql

Read-only SQL to execute against each database.

string

Example

SELECT count(*) as total FROM person

question

Natural language question (generates SQL via prompt_sql).

string

<= 1000 characters

Example

total payments by currency for january

buckets

Specific bucket IDs to query. If omitted, queries all warehouse databases. Bucket names with hyphens are converted to underscores for database matching.

Array<string>

Example

[
  "example",
  "jec",
  "rrec"
]

limit

Maximum rows returned per database.

integer

default: 1000 >= 1 <= 10000

table

Skip databases missing this table. Useful when querying tables that may not exist in all tenant databases.

string

Example

payment

Examples

Count people across all tenants

{
  "sql": "SELECT count(*) as total FROM person"
}

Query specific buckets

{
  "sql": "SELECT count(*) as total FROM person WHERE lower(first_name) = 'bob'",
  "buckets": [
    "example",
    "jec",
    "rrec",
    "oxpip"
  ]
}

Natural language query

{
  "question": "total payments by currency for january"
}

Skip databases missing a table

{
  "sql": "SELECT count(*) FROM payment",
  "table": "payment"
}

Responses

202

Job submitted successfully

object

data

required

object

job_id

required

string format: uuid

Example

503a73be-9119-43ba-8e30-4f278f978d49

status

required

string

Allowed values: processing

Example

processing

400

Invalid request

object

error

required

object

code

required

Machine-readable error code.

string

Example

unauthorized

message

required

Human-readable error message.

string

Example

Authentication required

Example

{
  "error": {
    "code": "bad_request",
    "message": "Missing 'sql' field"
  }
}

401

Authentication required

object

error

required

object

code

required

Machine-readable error code.

string

Example

unauthorized

message

required

Human-readable error message.

string

Example

Authentication required

Example

{
  "error": {
    "code": "unauthorized",
    "message": "Authentication required"
  }
}

403

Access denied to bucket

object

error

required

object

code

required

Machine-readable error code.

string

Example

unauthorized

message

required

Human-readable error message.

string

Example

Authentication required

Example

{
  "error": {
    "code": "forbidden",
    "message": "Access denied to bucket 'example'"
  }
}