Querying warehouse data in Management API

Querying warehouse data in Management API#

This guide explains how to run read-only SQL queries against a workspace’s warehouse and inspect its schema, using the Management API.

Introduction#

What is warehouse querying?: Warehouse querying lets you run a bounded, read-only SQL query directly against the data Adverity has loaded into a workspace’s warehouse, and read the results back in the response — without connecting to the warehouse yourself.
What is the warehouse schema endpoint?: The warehouse schema endpoint lists the tables and columns Adverity has actually loaded data for on the workspace’s warehouse, so you can build a valid query before running it.

Important

This feature may not be enabled for your workspace. If a request to either endpoint in this guide returns HTTP 404, warehouse querying is not yet enabled — contact your Adverity representative.

Prerequisites#

Before you complete the procedures in this guide, perform the following action:

Generate an API key with the warehouse:read scope. Warehouse querying is read-only — there is no write scope for this feature. For more information, see Authorizing to Management API.

Limitations#

Querying warehouse data comes with the following limitations:

Only SELECT and WITH statements are accepted. Any other statement type is rejected.
Results are capped at 100 rows, regardless of the row_limit value you request.
A query times out after 45 seconds, regardless of how many rows it would otherwise return. Narrow the query rather than retrying an identical request.
Available only for workspaces backed by a Snowflake or BigQuery warehouse.
A query that reads facts with SELECT * (or alias.*) is rejected with 400.
A query that reads facts without filtering a date column is rejected with 400 — a date referenced only inside a window function (OVER (...)) does not count as a filter.

Querying the warehouse#

To run a query against a workspace’s warehouse, follow these steps:

Create a POST request to the following endpoint:

https://{{INSTANCE}}/api/v1/workspaces/{{WORKSPACE_UUID}}/warehouse/query/

In the HTTP request header, include the parameter Authorization with value Bearer {{KEY}}.
In the HTTP request header, include the parameter Content-Type with value application/json.

In the HTTP request body, include the following parameters:

Parameter	Required	Description
`sql`	Yes	A single `SELECT` or `WITH` statement to run against the workspace’s warehouse.
`row_limit`	No	The maximum number of rows to return. Defaults to 100. This is a soft request — the server never returns more than 100 rows regardless of the value you send.

Send the request.

As a result, the query runs against the workspace’s warehouse and the matching rows are returned in the response.

Note

For a Snowflake-backed workspace, unquoted lowercase column identifiers (for example day) are automatically quoted before the query runs, so you do not need to quote them yourself. If the query references an unknown column or exceeds the timeout, the response includes a specific error message identifying the issue.

Import the request example as raw text to your HTTP client (such as Postman). The cURL request example is the following:

curl --location --request POST 'https://{{INSTANCE}}/api/v1/workspaces/{{WORKSPACE_UUID}}/warehouse/query/' \
--header 'Authorization: Bearer {{KEY}}' \
--header 'Content-Type: application/json' \
--data-raw '{"sql": "SELECT campaign_id, clicks FROM ad_performance LIMIT 10", "row_limit": 10}'

The example response is the following:

{
  "columns": ["campaign_id", "clicks"],
  "rows": [
    {"campaign_id": "123", "clicks": 42}
  ],
  "row_count": 1,
  "warehouse_type": "snowflake",
  "query_time_ms": 214,
  "is_truncated": false
}

The response contains the following fields:

Field	Description
`columns`	The names of the columns in the result, in order.
`rows`	The matching rows, each as an object keyed by column name.
`row_count`	The number of rows returned.
`warehouse_type`	The type of warehouse the workspace is backed by, for example `snowflake` or `bigquery`.
`query_time_ms`	How long the query took to run, in milliseconds.
`is_truncated`	Whether the 100-row cap was reached. When `true`, the result is partial — narrow the query instead of assuming you have seen every matching row.

When a query on facts fails one of the limitations listed above, Adverity rejects it before it reaches the warehouse and responds with HTTP 400 instead:

{
  "detail": "Query rejected before it ran. Fix each of these and retry: 1. select_star_on_facts: SELECT * on facts reads every column, one per mapped field across every datastream on the warehouse, and returns mostly empty values. Fix: Name only the columns you need, exactly as the schema endpoint lists them (for example SELECT \"day\", <metric> FROM facts).",
  "violations": [
    {
      "rule": "select_star_on_facts",
      "reason": "SELECT * on facts reads every column, one per mapped field across every datastream on the warehouse, and returns mostly empty values.",
      "fix": "Name only the columns you need, exactly as the schema endpoint lists them (for example SELECT \"day\", <metric> FROM facts)."
    }
  ]
}

The rejected-query response contains the following fields:

Field	Description
`detail`	A single message covering every rule the query failed, each with its fix.
`violations`	One entry per failed rule, so you can fix all of them before retrying. Empty for a `400` that is not a rejected query shape.
`violations[].rule`	The rule the query failed: `select_star_on_facts` or `no_time_window`.
`violations[].reason`	Why the query was rejected.
`violations[].fix`	How to change the query to pass the rule.

Retrieving the warehouse schema#

Note

The schema endpoint reflects only the tables and columns Adverity has actually loaded data for on this workspace’s warehouse. It is not a raw introspection of the underlying Snowflake or BigQuery connection, which can be shared across other workspaces — columns Adverity has not touched do not appear.

To retrieve the warehouse schema for a workspace, follow these steps:

Create a GET request to the following endpoint:

https://{{INSTANCE}}/api/v1/workspaces/{{WORKSPACE_UUID}}/warehouse/schema/

In the HTTP request header, include the parameter Authorization with value Bearer {{KEY}}.
Send the request.

As a result, you obtain the list of tables and columns available for this workspace’s warehouse.

Import the request example as raw text to your HTTP client (such as Postman). The cURL request example is the following:

curl --location --request GET 'https://{{INSTANCE}}/api/v1/workspaces/{{WORKSPACE_UUID}}/warehouse/schema/' \
--header 'Authorization: Bearer {{KEY}}'

The example response is the following:

{
  "hint": "Snowflake column names are case-sensitive when quoted. Unquoted identifiers are folded to uppercase; Adverity automatically quotes lowercase column names for you.",
  "tables": [
    {
      "name": "ad_performance",
      "columns": [
        {"name": "campaign_id", "type": "string"},
        {"name": "clicks", "type": "integer"}
      ]
    }
  ]
}

The tables field lists each available table, with its name and its columns — each column’s name and data type. The hint field contains guidance on column-quoting rules for the workspace’s warehouse type and on the query shapes rejected under Limitations; it is an empty string when there is no applicable hint.

What’s next?#

Configuring Data Warehouse and Dashboards binding settings in Management API