Write Predictive Queries

This tutorial turns a business question into a complete PQL query and tests the query on a small set of entities before scaling up.

Example question

How much will each customer spend during the next 30 days?

Assume the schema contains customers(customer_id, ...) and orders(order_id, customer_id, amount, order_time).

1. Choose the entity

The result should contain one prediction for each customer. Therefore the entity is identified by the customers.customer_id primary key.

FOR EACH customers.customer_id

2. Define the target and horizon

The target is the sum of future order amounts. The interval 0, 30, days starts at the prediction anchor time and ends 30 days later.

PREDICT SUM(orders.amount, 0, 30, days)

3. Start with explicit entities

Combine the two parts into a complete query for a few known entity IDs:

PREDICT SUM(orders.amount, 0, 30, days)
FOR customers.customer_id IN (101, 102, 103)

Start without filters. This approach helps isolate errors in the target, schema relationships, and entity selection.

Use backticks around identifiers that contain spaces, punctuation, a leading digit, or another character the PQL grammar cannot express as a bare name. Quote the table and column separately:

PREDICT SUM(`Order Lines`.`Line Total`, 0, 30, days)
FOR EACH `My Customers`.`Customer ID`

Backticks are identifier delimiters; string values continue to use single quotes.

4. Submit and inspect

from sdm_client import PredictiveQuery, SDMClient, KumoRelational
query = PredictiveQuery(
    """
    PREDICT SUM(orders.amount, 0, 30, days)
    FOR customers.customer_id IN (101, 102, 103)
    """,
    schema,
)
request = query.materialize(indices=[101, 102, 103])
with SDMClient(url="http://localhost:8000") as client:
    result = client.predict(request, model=KumoRelational())
print(result.to_pandas()[["row_index", "id", "prediction"]])

Confirm that every expected entity appears and that prediction contains values of the expected numeric type.

5. Add context filters carefully

Use WHERE to restrict historical context, not to choose which entities receive predictions. Current Kumo Relational static filters are limited to columns in the same table.

PREDICT SUM(orders.amount, 0, 30, days)
FOR customers.customer_id IN (101, 102, 103)
WHERE COUNT(orders.*, -90, 0, days) > 0

Compare the filtered and unfiltered results for the same entity set. If a query fails, remove the optional clauses and add them back one at a time.

6. Scale the entity list

Use FOR EACH in PQL and pass keys through indices:

query = PredictiveQuery(
    "PREDICT SUM(orders.amount, 0, 30, days) FOR EACH customers.customer_id",
    schema,
)
request = query.materialize(indices=customer_ids)
with SDMClient(url="http://localhost:8000") as client:
    result = client.predict(request, model=KumoRelational())

More entities than one call scores are gathered as several calls automatically, up to 100,000 entities or 100 calls per materialize(). See Limits for the exact caps and rows_per_call.

Review checklist

• The entity column is a validated primary key.
• The target table is connected to the entity table.
• Temporal targets use the intended future window and unit.
• The first request uses a small, known entity set.
• Results are joined to downstream data by id (or row_index when there is no key), never by row position.

See Prediction Types and Filters and Operators.