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

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:

    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:

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.

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:

  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:

{
  "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.

What’s next?#

Contents