How to Materialize a Query with the Peaka API

Materializing a query stores its result as a physical table that is refreshed on demand or on a schedule. Reads against the materialized table are fast and predictable, because they no longer hit the underlying connectors — which makes materialization the right tool for dashboards, reports, and pipelines built on heavy federated queries. This guide walks through the full workflow: materializing an existing query, scheduling refreshes, querying the result, keeping it in sync with its source, and monitoring refresh runs.

Prerequisites

An API key — either a Partner API Key or a Project API Key created for the project. See Authentication for the difference.
Your Project ID.

The examples below use the US zone base URL https://partner.peaka.studio/api/v1. If your organization is in the EU zone, use https://eu.partner.peaka.studio/api/v1 instead. See Servers for details.

How materialization works

Before calling the API, it helps to know five things:

A materialized query is a separate query object with queryType: "MATERIALIZED". When you materialize an existing query, the original query is left untouched — you get a new query alongside it.
The first materialization runs immediately. Creating a materialized query executes it right away and stores the result; you do not need to trigger the first run yourself.
The result lives in the mtquery schema. A materialized query named samplequery is queryable as "peaka"."mtquery"."samplequery".
A schedule keeps it fresh. Attach a schedule object to refresh at regular intervals or on a cron expression. Without a schedule, the data stays as of the last (manual) refresh.
Materializing by reference takes a snapshot. When you pass inputQueryRefId, Peaka resolves the referenced query’s SQL at that moment and stores it as the new query’s own inputQuery. Later edits to the source query do not propagate automatically — see Keep it in sync with the source.

Materialize an existing query

This is the API equivalent of the Advanced → Materialize flow in Peaka Studio. Call Create Query with queryType: "MATERIALIZED" and the id of the source query in inputQueryRefId. You do not need to send inputQuery — the SQL is taken from the source query. The example below materializes an existing query and refreshes it twice a day:

curl --request POST \
    --url https://partner.peaka.studio/api/v1/data/projects/{projectId}/queries \
    --header 'Authorization: Bearer <token>' \
    --header 'Content-Type: application/json' \
    --data '{
        "displayName": "dailyRevenueMaterialized",
        "queryType": "MATERIALIZED",
        "inputQueryRefId": "709922802836177297",
        "schedule": {
            "type": "interval",
            "repeatDuration": "PT12H"
        }
    }'

const options = {
  method: "POST",
  headers: {
    Authorization: "Bearer <token>",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    displayName: "dailyRevenueMaterialized",
    queryType: "MATERIALIZED",
    inputQueryRefId: "709922802836177297",
    schedule: { type: "interval", repeatDuration: "PT12H" },
  }),
};

fetch(
  "https://partner.peaka.studio/api/v1/data/projects/{projectId}/queries",
  options,
)
  .then((response) => response.json())
  .then((response) => console.log(response))
  .catch((err) => console.error(err));

import requests

url = "https://partner.peaka.studio/api/v1/data/projects/{projectId}/queries"
headers = {
    "Authorization": "Bearer <token>",
    "Content-Type": "application/json",
}
payload = {
    "displayName": "dailyRevenueMaterialized",
    "queryType": "MATERIALIZED",
    "inputQueryRefId": "709922802836177297",
    "schedule": {"type": "interval", "repeatDuration": "PT12H"},
}

response = requests.post(url, headers=headers, json=payload)
print(response.text)

The response contains the new query. Note that inputQuery holds the snapshot of the source query’s SQL that was resolved at create time, and that inputQueryRefId is not part of the response — the reference is consumed, not stored:

{
  "id": "709922802836177298",
  "displayName": "dailyRevenueMaterialized",
  "name": "dailyrevenuematerialized",
  "inputQuery": "SELECT * FROM \"mycatalog\".\"payment\".\"charges\"",
  "queryType": "MATERIALIZED",
  "schedule": {
    "type": "interval",
    "repeatDuration": "PT12H",
    "expression": "PT12H"
  }
}

The first materialization has already run by the time this response returns, so the data is immediately queryable.

You can also create a materialized query from scratch — send inputQuery with the SQL instead of inputQueryRefId:

{
  "displayName": "dailyRevenueMaterialized",
  "inputQuery": "SELECT * FROM \"mycatalog\".\"payment\".\"charges\"",
  "queryType": "MATERIALIZED",
  "schedule": {
    "type": "interval",
    "repeatDuration": "PT12H"
  }
}

Convert a query in place

If you want to turn an existing PLAIN query into a materialized one — rather than creating a new query next to it — call Update Query on the query itself with queryType: "MATERIALIZED":

cURL

curl --request PUT \
    --url https://partner.peaka.studio/api/v1/data/projects/{projectId}/queries/{queryId} \
    --header 'Authorization: Bearer <token>' \
    --header 'Content-Type: application/json' \
    --data '{
        "queryType": "MATERIALIZED",
        "schedule": {
            "type": "interval",
            "repeatDuration": "P1D"
        }
    }'

Fields omitted from an update keep their current values, so this request only changes the query type and attaches a schedule. After the conversion the query is materialized in place — there is no separate PLAIN query anymore.

Schedule refreshes

The schedule object supports three types:

`type`	Fields	Behavior
`interval`	`repeatDuration` (ISO-8601 duration)	Refreshes at a fixed interval, e.g. every 6 hours.
`cron`	`cronExpression`, optional `timezone`	Refreshes on a cron schedule, evaluated in the given timezone.
`none`	—	No automatic refresh. Equivalent to omitting `schedule`.

repeatDuration is an ISO-8601 duration. Common values:

Value	Meaning
`PT1H`	Every hour
`PT6H`	Every 6 hours
`PT12H`	Twice a day
`P1D`	Once a day

cronExpression is a UNIX cron expression (minute hour day-of-month month day-of-week), and timezone is an IANA timezone name (defaults to UTC):

{
  "schedule": {
    "type": "cron",
    "cronExpression": "0 6 * * *",
    "timezone": "Europe/Istanbul"
  }
}

You can change or remove the schedule at any time with Update Query — send a new schedule object (or { "type": "none" } to stop automatic refreshes).

Query the materialized data

The materialized result is exposed in the mtquery schema of the peaka catalog, under the query’s name (the normalized form of displayName returned in the response):

SELECT * FROM "peaka"."mtquery"."dailyrevenuematerialized"

This reads the stored table directly — it is fast and does not touch the underlying connectors. You can run it anywhere you run SQL against your project, for example with the Execute Query endpoint, a JDBC driver, or from other Peaka queries.

"peaka"."query"."<name>" addresses a query’s logical definition, while "peaka"."mtquery"."<name>" addresses the stored, materialized result.

Keep it in sync with the source

Materializing by reference takes a snapshot of the source query’s SQL. Scheduled and manual refreshes re-run that snapshot against live data — so the data stays fresh — but if you later edit the SQL of the source query, the materialized query keeps computing the old SQL. To re-sync the materialized query with its source, send the reference again with Update Query:

cURL

curl --request PUT \
    --url https://partner.peaka.studio/api/v1/data/projects/{projectId}/queries/{materializedQueryId} \
    --header 'Authorization: Bearer <token>' \
    --header 'Content-Type: application/json' \
    --data '{
        "queryType": "MATERIALIZED",
        "inputQueryRefId": "709922802836177297"
    }'

This resolves the source query’s SQL again, stores the fresh snapshot, and rebuilds the materialized table.

Monitor, refresh, and cancel

Materialized queries have dedicated endpoints for operating on refreshes:

List Materialized Query Statuses — refresh status, schedule, and next execution time for all materialized queries in a project.
Read Materialized Query Status — the same information for a single query.
Refresh Materialized Query — trigger an immediate refresh, regardless of the schedule.
Cancel Materialized Query Refresh — cancel an in-progress refresh.

For example, to check when a materialized query last refreshed and when the next run is due:

cURL

curl --request GET \
    --url https://partner.peaka.studio/api/v1/data/projects/{projectId}/materialized-queries/{queryId}/status \
    --header 'Authorization: Bearer <token>'

{
  "queryId": "709922802836177298",
  "queryName": "dailyrevenuematerialized",
  "scheduleSettings": {
    "type": "interval",
    "repeatDuration": "PT12H",
    "expression": "PT12H"
  },
  "nextExecutionStartTime": "2026-07-07T06:00:00Z",
  "status": "COMPLETED",
  "lastExecutionStartTime": "2026-07-06T18:00:00Z",
  "lastUpdateTime": "2026-07-06T18:00:41Z"
}

Clean up

A materialized query is deleted like any other query, with Delete Query. Deleting it removes the materialized table and its refresh schedule.

If the materialized query was created from an existing query with inputQueryRefId, deleting it does not affect the source query.

​Prerequisites

​How materialization works

​Materialize an existing query

​Convert a query in place

​Schedule refreshes

​Query the materialized data

​Keep it in sync with the source

​Monitor, refresh, and cancel

​Clean up

Prerequisites

How materialization works

Materialize an existing query

Convert a query in place

Schedule refreshes

Query the materialized data

Keep it in sync with the source

Monitor, refresh, and cancel

Clean up