Skip to content
Store data and insights

Query the lake

Runs ONE read-only SQL SELECT over this property's own lake tables (the tenant scope is applied for you; no comments, UNION or writes) and returns at most max_rows rows (1–500, default 100) with the data age per source.

POST /properties/{propertyId}/query

Scope read · Access Read only · Operation id data.query

Try it

productmetrics runs after productmetrics login; curl needs PM_TOKEN (see Authentication). Set $PROPERTY_ID first.

productmetrics data query \
  --property-id $PROPERTY_ID \
  --sql 'SELECT event_date, COUNT(*) AS products FROM daily_product_journey GROUP BY event_date ORDER BY event_date DESC LIMIT 2' \
  --json

Response

API response (200), shortened:

{
  "data": {
    "columns": [
      "event_date"
      // … 1 more
    ],
    "rows": [
      [
        "2026-11-12"
        // … 1 more
      ]
      // … 1 more
    ],
    "truncated": false
    // … 1 more field
  },
  "request_id": "req_01JD85CWTYRWYFND5RHXQW3TSZ"
}

CLI output: productmetrics data query --json prints { "ok": true, "data": …, "request_id": "…" } with the same data.

Before you call

It is slow and rate-limited (30 per person and 120 per property per hour, shared with the Product Metrics assistant), and needs Pro. Prefer the specific read operations (orders.list, store.get_health_*, inventory.get_state) whenever one answers the question; use this only for what they cannot. A refused or failing query answers 400 with an instruction to fix the SQL; 429 carries Retry-After.

Path parameters

NameTypeDescription
propertyIdstring (uuid)The property id, from productmetrics whoami or properties.list (property_id).

Body

NameTypeRequiredDescription
sqlstringYesOne SELECT statement; the tenant scope is applied for you. 10 to 4000 characters.
max_rowsintegerNoRuns ONE read-only SQL SELECT over this property’s own lake tables (the tenant scope is applied for you; no comments, UNION or writes) and returns at most max_rows rows (1–500, default 100) with the data age per source. From 1 to 500. Default 100.

Errors

A failure answers with its HTTP status and { error: { code, message, details? }, request_id }. Branch on code (stable, lower_snake); show message to the person. The CLI prints the same error with "ok": false.

{
  "error": {
    "code": "insufficient_scope",
    "message": "This operation needs the read scope; connect again and allow it.",
    "details": {
      "required_scope": "read"
    }
  },
  "request_id": "req_01JW4KGF3M3Z6BGNC6DCV0VW91"
}
StatusCodeWhen
400invalid_requestA path, query or body value does not match this page. details.issues lists each location, path and message.
400invalid_requestA refused or failing query answers 400 with an instruction to fix the SQL; 429 carries Retry-After.
401invalid_tokenThe token is missing, expired or revoked: sign in again (productmetrics login). The WWW-Authenticate header says why.
402subscription_requiredThe workspace has no Pro plan.
403insufficient_scopeThe token lacks the read scope (details.required_scope): sign in again and allow it.
404not_foundAn id in the path does not exist, or the signed-in person cannot open it.
429rate_limitedA refused or failing query answers 400 with an instruction to fix the SQL; 429 carries Retry-After.
429rate_limitedMore than 600 requests a minute for this person: wait the Retry-After seconds (60) and retry.

All Store data and insights operations