Querying warehouse data in 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.

Prerequisites

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

• Use a Connect API key with Warehouse: Read-only (warehouse:read). The Warehouse permission has no Write option. Without this permission, Management API returns HTTP 403. You cannot change the permissions of a key, so to add one, create a new key. For more information, see Creating a Connect API key in Adverity.

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:

1. Create a POST request to the following endpoint:

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

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

3. In the HTTP request header, include the parameter Content-Type with value application/json.

4. In the HTTP request body, include the following parameters:

   Scroll horizontally to see the full table.

   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.

5. Send the request.

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

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:

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:

Retrieving the warehouse schema

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

1. Create a GET request to the following endpoint:

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

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

3. 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