Shaped Query Language

The Shaped Query Language is a SQL-like domain-specific language (DSL) for building recommendation and ranking queries. It provides a familiar SQL syntax while supporting advanced retrieval, filtering, scoring, and reordering operations specific to recommendation systems.

Quick Start
Overview
- SQL vs YAML Format
Basic Syntax
Entity Types
Retriever Functions
Query Encoders
Filter Functions
Reorder Functions
Parameters
SQL Clauses
Complete Examples
Query Patterns and Recipes
Unsupported Features
Limits and Constraints
Pagination
API Usage
- Endpoints
- Authentication
- Making Query Requests
- Defining Saved Queries
Best Practices
When to Use Each Retriever
Common Use Cases
Troubleshooting
Error Handling
Quick Reference
Glossary
FAQ

Quick Start

Simple recommendation query:

SELECT * 
FROM similarity(embedding_ref='als', limit=50, 
               encoder='precomputed_user', input_user_id='$user_id')
LIMIT 10

Execute via API:

curl -X POST https://api.shaped.ai/v2/engines/{engine_name}/query \
  -H "x-api-key: YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM similarity(embedding_ref=''als'', limit=50, encoder=''precomputed_user'', input_user_id=''$user_id'') LIMIT 10",
    "parameters": {
      "user_id": "user123"
    }
  }'

Response:

{
  "results": [
    {
      "id": "item123",
      "score": 0.95,
      "attributes": {
        "name": "Product Name",
        "price": 99.99
      }
    }
  ]
}

Overview

The query language transpiles SQL-like queries into query configuration objects that define the complete query pipeline. Queries can be written in two formats:

SQL Syntax: A SQL-like string that gets transpiled to a query config
YAML/JSON Query Objects: Direct configuration objects with explicit structure

This document focuses on the SQL syntax, which provides a familiar interface for building recommendation and ranking queries.

SQL vs YAML Format

Use SQL When:

You're familiar with SQL and want a quick way to write queries
You need ad-hoc queries for experimentation
You want a concise, readable syntax
You're building simple to moderately complex queries

Use YAML/JSON When:

You need fine-grained control over every query component
You're defining complex queries with many nested options
You want explicit type safety and validation
You're programmatically generating queries

SQL Example:

SELECT * FROM similarity(embedding_ref='als', limit=50) LIMIT 10

Equivalent YAML:

type: rank
from: item
retrieve:
  - type: similarity
    embedding_ref: als
    query_encoder:
      type: precomputed_user
      input_user_id: $parameter.user_id
    limit: 50
limit: 10

Both formats transpile to the same internal query configuration.

Basic Syntax

SELECT [columns | *]
FROM retriever_function(..., entity_type='items'|'users')
     [, retriever_function(...), ...]
[WHERE filter_expression]
[WHERE filter_expression [AND prebuilt(...)] [AND truncate(...)]]
[ORDER BY score_expression]
[LIMIT n]
[REORDER BY reorder_function(...), ...]

Query Execution Pipeline:

Retrieve: Execute all retriever functions in parallel and merge results
Filter: Apply WHERE clause filters
Score: Apply ORDER BY scoring
Reorder: Apply REORDER BY functions
Limit: Apply final LIMIT

Notes:

Multiple retrievers can be specified, separated by commas
If no FROM clause is specified, a default filter retriever is used
Function names are case-insensitive
SQL comments (-- comment) are supported
String literals support escaped quotes: 'O''Reilly' or "He said ""Hello"""

Entity Types

Queries operate on one of two entity types:

items: Items to be recommended (products, content, etc.)
users: Users to be recommended (for user-to-user recommendations)

The entity_type parameter determines which entity type a query operates on. All retrievers in a single query must use the same entity type. If not specified, the default is items.

Retriever Functions

Retriever functions define how candidates are initially retrieved. All retriever functions support the entity_type parameter.

Multiple Retrievers:

You can specify multiple retrievers in the FROM clause, separated by commas. They execute in parallel and their results are merged (union). All retrievers must use the same entity_type.

SELECT * 
FROM similarity(embedding_ref='als', limit=50),
     text_search(query='$query', mode='lexical', limit=50)
LIMIT 20

No FROM Clause:

If no FROM clause is specified, a default filter retriever is used with limit=100.

similarity()

Performs vector similarity search using embeddings.

similarity(
    embedding_ref='name',
    limit=100,
    encoder='precomputed_user',
    entity_type='items',
    where='optional_filter_expression',
    name='optional_step_name'
)

Parameters:

embedding_ref (default: 'default'): Name of the embedding configuration to use. Must be non-empty if specified.
limit (default: 100): Maximum number of candidates to retrieve
encoder or query_encoder (default: 'precomputed_user'): Query encoder type (see Query Encoders section)
entity_type (default: 'items'): Entity type to retrieve
where (optional): Additional filter expression
name (optional): Step name for debugging/explanation

Example:

SELECT * FROM similarity(embedding_ref='als', limit=50) LIMIT 10

text_search()

Performs full-text search using lexical or vector search.

text_search(
    query='$query',
    mode='lexical',
    limit=100,
    entity_type='items',
    fuzziness=1,
    text_embedding_ref='default',
    name='optional_step_name'
)

Parameters:

query (default: '$query'): Search query text (supports parameters)
mode (default: 'lexical'): Search mode - 'lexical' or 'vector'
limit (default: 100): Maximum number of candidates to retrieve
entity_type (default: 'items'): Entity type to retrieve
fuzziness (default: 1): Edit distance for fuzzy matching (lexical mode only)
text_embedding_ref (default: 'default'): Embedding reference for vector mode
name (optional): Step name for debugging/explanation

Examples:

-- Lexical search
SELECT name, price
FROM text_search(query='$query', mode='lexical', entity_type='items')
WHERE category = 'electronics'
LIMIT 20

-- Vector search
SELECT * FROM text_search(query='$query', mode='vector', 
                          text_embedding_ref='text_embedding', limit=50)

column_order()

Retrieves candidates ordered by column values.

column_order(
    columns='col1 DESC, col2 ASC',
    limit=100,
    entity_type='items',
    where='optional_filter_expression',
    name='optional_step_name'
)

Parameters:

columns (required): Column ordering specification (e.g., 'price DESC, rating ASC')
limit (default: 100): Maximum number of candidates to retrieve
entity_type (default: 'items'): Entity type to retrieve
where (optional): Additional filter expression
name (optional): Step name for debugging/explanation

Column Ordering Syntax:

column_name ASC - Ascending order
column_name DESC - Descending order
column_name ASC NULLS FIRST - Nulls first
column_name DESC NULLS LAST - Nulls last

Multiple columns can be specified, separated by commas.

Example:

SELECT * FROM column_order(columns='price DESC, rating ASC', limit=100)
WHERE in_stock = true
LIMIT 20

filter()

Retrieves candidates matching a filter expression.

filter(
    where='expression',
    limit=100,
    entity_type='items',
    name='optional_step_name'
)

Or using positional argument:

filter('expression', limit=100)

Parameters:

where or positional (required): Filter expression
limit (default: 100): Maximum number of candidates to retrieve
entity_type (default: 'items'): Entity type to retrieve
name (optional): Step name for debugging/explanation

Example:

SELECT * FROM filter(where='category = "electronics" AND price < 100', 
                     limit=200)
LIMIT 20

ids() / candidate_ids()

Retrieves specific candidates by their IDs. Used for reranking scenarios.

ids(['id1', 'id2', 'id3'], entity_type='items')

Or using a parameter:

ids($item_ids)

Parameters:

First argument: List of IDs or parameter reference (e.g., $item_ids)
entity_type (default: 'items'): Entity type to retrieve

Example:

SELECT * FROM ids($item_ids) ORDER BY score REORDER BY diversity(0.3)

candidate_attributes()

Retrieves candidates from attributes provided at query time. Used for reranking items not in the catalog.

candidate_attributes($item_attributes, entity_type='items')

Parameters:

First argument (required): Parameter reference to item attributes (e.g., $item_attributes)
entity_type (default: 'items'): Entity type to retrieve

Example:

SELECT * FROM candidate_attributes($item_attributes) 
ORDER BY score 
LIMIT 10

Query Encoders

Query encoders determine how the query vector is created for similarity search. They are specified via the encoder parameter in similarity() retrievers.

precomputed_user (default)

Uses a precomputed user embedding.

similarity(embedding_ref='als', encoder='precomputed_user', 
          input_user_id='$user_id')

Parameters:

input_user_id (default: '$user_id'): User ID parameter

precomputed_item

Uses a precomputed item embedding.

similarity(embedding_ref='item_embedding', encoder='precomputed_item',
          input_item_id='$item_id')

Parameters:

input_item_id (default: '$item_id'): Item ID parameter

interaction_pooling

Creates a query vector by pooling embeddings from a user's interaction history.

similarity(embedding_ref='als', encoder='interaction_pooling',
          input_user_id='$user_id',
          pooling_function='mean',
          truncate_interactions=10)

Parameters:

input_user_id (default: '$user_id'): User ID parameter
pooling_function (default: 'mean'): Pooling function - 'mean' or 'max'
truncate_interactions (default: 10): Maximum number of interactions to use

interaction_round_robin

Retrieves items using round-robin strategy from a user's interaction history.

similarity(embedding_ref='als', encoder='interaction_round_robin',
          input_user_id='$user_id',
          pooling_function='mean',
          num_clusters=5)

Parameters:

input_user_id (default: '$user_id'): User ID parameter
pooling_function (default: 'mean'): Pooling function - 'mean' or 'max'
num_clusters (default: 5): Number of interaction clusters to create

user_attribute_pooling

Creates a query vector by encoding user attributes. Requires a content or text embedding.

similarity(embedding_ref='text_embedding', encoder='user_attribute_pooling',
          input_user_id='$user_id',
          input_user_features='$user_features')

Parameters:

input_user_id (optional): User ID parameter
input_user_features (optional): User features dictionary parameter

At least one of input_user_id or input_user_features must be provided.

item_attribute_pooling

Creates a query vector by encoding item attributes. Requires a content or text embedding.

similarity(embedding_ref='text_embedding', encoder='item_attribute_pooling',
          input_item_id='$item_id',
          input_item_features='$item_features')

Parameters:

input_item_id (optional): Item ID parameter
input_item_features (optional): Item features dictionary parameter

At least one of input_item_id or input_item_features must be provided.

Filter Functions

Filter functions can be used in the WHERE clause to apply additional filtering operations.

prebuilt()

Applies a prebuilt filter (personal or global filter defined in the model config).

WHERE prebuilt('filter_name', input_user_id='$user_id')

Parameters:

First argument or filter_ref (required): Filter name (e.g., 'filter_name')
input_user_id (optional): User ID parameter for personal filters
name (optional): Step name for debugging/explanation

Filter References:

Prebuilt filters are defined in your model configuration and can be referenced by name. The filter name should match a filter defined in data.filters.

Example:

SELECT * FROM items
WHERE price > 100 AND prebuilt('exclude_purchased', input_user_id='$user_id')
LIMIT 20

This assumes you have a filter named exclude_purchased defined in your model configuration.

truncate()

Truncates the candidate set to a maximum length.

WHERE truncate(500)

Parameters:

First argument or max_length (required): Maximum number of candidates to keep
name (optional): Step name for debugging/explanation

Example:

SELECT * FROM similarity(embedding_ref='als', limit=1000)
WHERE truncate(500)
LIMIT 20

Reorder Functions

Reorder functions apply post-retrieval reordering to diversify or explore the results. They are specified in the REORDER BY clause.

diversity()

Applies diversity reordering to reduce similarity between consecutive results.

REORDER BY diversity(0.3)

Parameters:

First argument or strength (default: 0.5): Diversity strength (0.0 to 1.0)

exploration()

Applies exploration reordering to balance exploitation and exploration.

REORDER BY exploration(0.2)

Parameters:

First argument or strength (default: 0.5): Exploration strength (0.0 to 1.0)

boost()

Applies boosted reordering to emphasize certain retrievers.

REORDER BY boost(0.4)

Parameters:

First argument or strength (default: 0.5): Boost strength (0.0 to 1.0)

Multiple Reorder Functions:

You can chain multiple reorder functions:

REORDER BY diversity(0.3), exploration(0.2)

Parameters

Parameters allow runtime value substitution using $parameter_name or $parameter.name syntax.

Parameter Syntax

$param_name - Simple parameter reference
$parameter.param_name - Explicit parameter reference
$param.param_name - Alternative syntax (normalized to $parameter.name)

Supported Parameter Types:

int - Integer values
float - Floating-point numbers
str - String values
bool - Boolean values (true/false)
List[int] - Arrays of integers
List[float] - Arrays of floats
List[str] - Arrays of strings
List[bool] - Arrays of booleans

Important Notes:

Parameters are substituted at query execution time, not at transpilation time
Parameter names must be valid identifiers (letters, numbers, underscores)
limit is a reserved keyword and cannot be used as a parameter name (use max_results or similar instead)
Nested function calls are not supported in parameter values

Common Parameters

$user_id - User identifier
$item_id - Item identifier
$query - Search query text
$item_ids - List of item IDs for reranking
$item_attributes - Item attributes for reranking
$limit - Result limit (use LIMIT clause instead when possible)

Parameter Examples

-- Using user_id parameter
SELECT * FROM similarity(embedding_ref='als', 
                       encoder='precomputed_user',
                       input_user_id='$user_id')
LIMIT 10

-- Using query parameter
SELECT * FROM text_search(query='$query', mode='lexical')
LIMIT 20

-- Using item_ids parameter for reranking
SELECT * FROM ids($item_ids) ORDER BY score LIMIT 10

SELECT Clause

The SELECT clause specifies which columns to return.

SELECT * - Returns all columns (default)
SELECT column1, column2 - Returns specific columns

Example:

SELECT name, price, category
FROM similarity(embedding_ref='als', limit=50)
LIMIT 10

WHERE Clause

The WHERE clause applies filtering expressions using DuckDB SQL syntax. You can combine regular SQL expressions with filter functions.

Supported Operations:

Comparison: =, !=, >, <, >=, <=
Logical: AND, OR, NOT
String operations: LIKE, ILIKE
Null checks: IS NULL, IS NOT NULL
In operations: IN (...), NOT IN (...)
Range: BETWEEN ... AND ...
Boolean values: true, false

Note: WHERE clause expressions are evaluated using DuckDB SQL syntax, so DuckDB functions and operators are supported. Use parameters for dynamic values rather than trying to embed complex expressions.

Examples:

-- Simple filter
WHERE category = 'electronics' AND price < 100

-- With filter functions
WHERE price > 100 
  AND prebuilt('ref:data.filters:category_filter', input_user_id='$user_id')
  AND truncate(500)

-- Complex expression
WHERE (category = 'electronics' OR category = 'computers')
  AND price BETWEEN 50 AND 500
  AND in_stock = true

ORDER BY Clause

The ORDER BY clause specifies how to score and sort results.

ORDER BY score - Uses the retriever's default score (PassthroughScore)
ORDER BY expression - Uses a custom scoring expression (ScoreEnsemble)

Examples:

-- Default scoring
SELECT * FROM similarity(embedding_ref='als', limit=50)
ORDER BY score
LIMIT 10

-- Custom scoring (requires model reference)
SELECT * FROM similarity(embedding_ref='als', limit=50)
ORDER BY 0.6 * model1 + 0.4 * model2
LIMIT 10

LIMIT Clause

The LIMIT clause specifies the maximum number of results to return.

LIMIT 20

Or using a parameter:

LIMIT $limit

Note: The LIMIT applies after all retrieval, filtering, scoring, and reordering operations.

Complete Examples

Basic Similarity Search

SELECT * 
FROM similarity(embedding_ref='als', limit=50, 
               encoder='precomputed_user', input_user_id='$user_id')
LIMIT 10

Text Search with Filtering

SELECT name, price, category
FROM text_search(query='$query', mode='lexical', entity_type='items', limit=100)
WHERE category = 'electronics' AND price < 500
ORDER BY score
LIMIT 20

User Search

SELECT * 
FROM text_search(query='$query', entity_type='users', limit=50)
LIMIT 10

Reranking with Diversity

SELECT * 
FROM ids($item_ids)
ORDER BY score
REORDER BY diversity(0.3)
LIMIT 10

Complex Query with Multiple Filters

SELECT name, price, rating
FROM similarity(embedding_ref='als', limit=200, 
               encoder='interaction_pooling',
               input_user_id='$user_id',
               truncate_interactions=20)
WHERE price > 100 
  AND category IN ('electronics', 'computers')
  AND prebuilt('ref:data.filters:exclude_purchased', 
               input_user_id='$user_id')
  AND truncate(500)
ORDER BY 0.7 * similarity_score + 0.3 * rating
REORDER BY diversity(0.2), exploration(0.1)
LIMIT 20

Column Ordering

SELECT * 
FROM column_order(columns='price DESC, rating DESC NULLS LAST', limit=100)
WHERE in_stock = true
LIMIT 20

Query Patterns and Recipes

Pattern: Cold Start Recommendations

For new users without interaction history, use attribute-based similarity:

SELECT * 
FROM similarity(embedding_ref='content_embedding',
               encoder='user_attribute_pooling',
               input_user_features='$user_features',
               limit=100)
REORDER BY diversity(0.3)
LIMIT 20

Pattern: Multi-Stage Retrieval

Retrieve from multiple sources and combine:

SELECT * 
FROM similarity(embedding_ref='collaborative', limit=50),
     similarity(embedding_ref='content', limit=50),
     text_search(query='$query', mode='lexical', limit=50)
WHERE category = '$category'
ORDER BY score
REORDER BY diversity(0.2)
LIMIT 20

Pattern: Time-Based Filtering

Filter by recency using date comparisons:

SELECT * 
FROM similarity(embedding_ref='als', limit=100)
WHERE created_at > '2024-01-01'
  AND last_interaction > '2024-12-01'
ORDER BY score
LIMIT 20

For dynamic dates, use parameters:

SELECT * 
FROM similarity(embedding_ref='als', limit=100)
WHERE created_at > $start_date
  AND last_interaction > $recent_date
ORDER BY score
LIMIT 20

Pattern: A/B Testing Queries

Use parameters to switch between query variants. Note that parameters work best for values, not entire expressions:

SELECT * 
FROM similarity(embedding_ref=$embedding_name, limit=$retriever_limit)
WHERE category = $test_category
ORDER BY score
REORDER BY diversity($diversity_strength)
LIMIT $result_limit

For more complex A/B testing, define separate saved queries and switch between them at the application level.

Pattern: Category-Specific Recommendations

Personalize by category with fallback:

SELECT * 
FROM similarity(embedding_ref='als', 
               encoder='interaction_pooling',
               input_user_id='$user_id',
               where='category = $category',
               limit=50)
WHERE category = $category
ORDER BY score
LIMIT 20

If no results, fall back to general recommendations by omitting the category filter.

Unsupported Features

The following SQL features are not supported:

JOINs - Use multiple retriever functions instead
Subqueries in FROM clause - Use retriever functions instead
Nested function calls - Functions cannot be nested (e.g., similarity(embedding_ref=func('arg')) is invalid)
GROUP BY / Aggregations - This DSL is for ranking, not analytics
HAVING clause - Use WHERE instead
Window functions (OVER clause) - Use ORDER BY instead
Common Table Expressions (WITH clause) - Use retriever functions instead
Multiple statements - Only single SELECT statements are allowed
Table aliases - Not supported in FROM clause
Column aliases in SELECT - Column names are used as-is

Limits and Constraints

Result Limits

Maximum LIMIT: 1000 results per query
Default LIMIT: 15 results (if not specified)
Retriever Limits: Each retriever function can retrieve up to the limit specified in its limit parameter

String Length Limits

Maximum string length: 1000 characters for string parameters and values

Performance Considerations

Retriever Limits: Set appropriate limit values in retriever functions to balance recall and performance. Retrieving too many candidates can slow down queries.
Filter Complexity: Complex WHERE clauses with many conditions may impact performance. Consider using prebuilt filters for frequently-used filter patterns.
Multiple Retrievers: When using multiple retrievers, they execute in parallel for better performance.

Pagination

Pagination allows you to retrieve results in pages, maintaining consistency across requests.

Using Pagination

Pagination is supported through the pagination_key parameter in API requests. The same pagination key will return consistent results across multiple requests.

Example API Request:

{
  "query": "SELECT * FROM similarity(embedding_ref='als', limit=50) LIMIT 20",
  "parameters": {
    "user_id": "user123"
  },
  "pagination_key": "page_1"
}

Pagination Behavior:

Results are consistent for the same pagination_key
Changing the pagination_key returns a new page
The pagination offset is automatically handled based on the key
Results maintain the prefix property: page N results are a prefix of page N+1 results

API Usage

Endpoints

Ad-hoc Query:

POST /v2/engines/{engine_name}/query - Execute a SQL query string directly

Saved Query:

POST /v2/engines/{engine_name}/queries/{query_name} - Execute a saved query
GET /v2/engines/{engine_name}/queries - List all saved queries
GET /v2/engines/{engine_name}/queries/{query_name} - Get saved query details

Authentication

All requests require an API key in the x-api-key header:

x-api-key: YOUR_API_KEY

Making Query Requests

Queries can be executed via the API in two ways:

Ad-hoc queries: Send a SQL query string directly in the request
Saved queries: Reference a pre-saved query by name (defined in model configuration)

Key Differences:

Ad-hoc queries: Flexible, can be modified per request, but must be validated each time
Saved queries: Pre-validated, reusable, can define parameter schemas, better for production use

Defining Saved Queries

Saved queries are defined in your model configuration YAML under the queries section:

queries:
  personalized_recommendations:
    params:
      user_id:
        type: string
        required: true
      limit:
        type: number
        default: 20
    query: |
      SELECT * 
      FROM similarity(embedding_ref='als', limit=100,
                     encoder='interaction_pooling',
                     input_user_id='$user_id')
      REORDER BY diversity(0.3)
      LIMIT $limit

Benefits of Saved Queries:

Pre-validated at deployment time (SQL syntax errors caught early)
Parameter schemas with types and defaults
Reusable across multiple API calls
Better performance (no re-parsing on each request)
Version controlled with your model configuration

Query Validation:

Saved queries are automatically validated when you create or update an engine. If a SQL query has syntax errors, you'll get a clear error message during engine setup, preventing deployment of invalid queries.

Ad-hoc Query Request:

{
  "query": "SELECT * FROM similarity(embedding_ref='als', limit=50) LIMIT 10",
  "parameters": {
    "user_id": "user123"
  },
  "return_metadata": true,
  "return_explanation": false
}

Saved Query Request:

{
  "parameters": {
    "user_id": "user123",
    "query": "laptop"
  },
  "return_metadata": true
}

Response Format

Query responses include the following structure:

{
  "results": [
    {
      "id": "item123",
      "score": 0.95,
      "attributes": {
        "name": "Product Name",
        "price": 99.99,
        "category": "electronics"
      }
    }
  ],
  "explanation": {
    "retrieval": [...],
    "filtering": [...],
    "scoring": [...],
    "reordering": [...]
  }
}

Response Fields:

results: Array of result objects, each containing:
- id: Entity identifier (string)
- score: Relevance score (float, 0.0 to 1.0)
- attributes: Entity metadata object (if return_metadata=true)
- journey: Entity journey tracking object (if return_journey_explanations=true)
explanation: Detailed execution breakdown object (if return_explanation=true)
- retrieval: List of retrieval step explanations
- filtering: List of filtering step explanations
- scoring: Scoring step explanation
- reordering: List of reordering step explanations
pagination_key: Pagination key for fetching next page (string, only if more results available)

HTTP Status Codes:

200 OK - Query executed successfully
400 Bad Request - Invalid request format
401 Unauthorized - Missing or invalid API key
404 Not Found - Saved query not found (for saved query endpoint)
422 Unprocessable Entity - Query validation error (invalid SQL, missing parameters, etc.)
500 Internal Server Error - Server error during query execution

Empty Results:

If a query returns zero results, you'll still receive a 200 OK response with an empty results array. This is normal behavior - it means the query executed successfully but no entities matched your criteria. Use return_explanation=true to understand why no results were returned (e.g., filters removed all candidates).

Best Practices

Query Design

Set Appropriate Limits: Use retriever limit parameters to retrieve enough candidates (typically 50-200) while keeping final LIMIT reasonable (10-50 for most use cases).
Use Prebuilt Filters: For frequently-used filter patterns, define them as prebuilt filters in your model configuration and reference them with prebuilt().
Combine Retrievers: Use multiple retrievers in the FROM clause to combine different retrieval strategies (e.g., similarity + text_search).
Optimize WHERE Clauses: Place the most selective filters first in WHERE clauses. Use indexes on frequently-filtered columns.
Parameterize Queries: Use parameters for dynamic values (user IDs, search queries) rather than string concatenation.

Performance Tips

Retriever Limits: Start with smaller retriever limits (50-100) and increase if needed. Larger limits improve recall but increase latency.
Filter Early: Apply filters in the WHERE clause or retriever where parameter to reduce the candidate set early in the pipeline.
Use Truncate: If you retrieve many candidates but only need a subset, use truncate() to limit before expensive scoring operations.
Diversity Strength: Use moderate diversity values (0.2-0.4) to balance diversity and relevance. Higher values may reduce relevance.
Column Selection: Use SELECT column1, column2 instead of SELECT * when you only need specific columns to reduce response size.

Security Considerations

Parameter Validation: Always validate and sanitize user-provided parameters before passing them to queries.
SQL Injection Prevention: The query language automatically handles parameter substitution safely. Parameters are never concatenated into SQL strings. Never concatenate user input directly into query strings.
Access Control: Ensure proper authentication and authorization for query endpoints. Users should only be able to query data they have access to.
API Key Security: Never expose API keys in client-side code or public repositories. Use server-side proxies for client applications.

When to Use Each Retriever

Retriever	Best For	Avoid When
`similarity()`	Personalized recommendations, similar items/users	No embeddings configured
`text_search()`	Search features, keyword queries	No text fields indexed
`column_order()`	Trending, newest, most popular	Need personalization
`filter()`	Rule-based retrieval, filtering only	Need ranking/scoring
`ids()`	Reranking known items	Initial retrieval (no candidates)
`candidate_attributes()`	Reranking new/ephemeral items	Items in catalog

Common Use Cases

Personalized Recommendations

Retrieve personalized recommendations for a user based on their interaction history:

SELECT * 
FROM similarity(embedding_ref='als', limit=100,
               encoder='interaction_pooling',
               input_user_id='$user_id',
               truncate_interactions=20)
WHERE prebuilt('ref:data.filters:exclude_purchased', 
               input_user_id='$user_id')
REORDER BY diversity(0.3)
LIMIT 20

Search with Filters

Combine text search with attribute filtering:

SELECT name, price, category
FROM text_search(query='$query', mode='lexical', limit=200)
WHERE category = '$category' 
  AND price BETWEEN $min_price AND $max_price
  AND in_stock = true
ORDER BY score
LIMIT 20

Retrieve trending items ordered by popularity metrics:

SELECT * 
FROM column_order(columns='views DESC, likes DESC, created_at DESC', limit=100)
WHERE created_at > '2024-12-01'
LIMIT 20

Use a date parameter for dynamic filtering:

SELECT * 
FROM column_order(columns='views DESC, likes DESC, created_at DESC', limit=100)
WHERE created_at > $start_date
LIMIT 20

Reranking

Rerank a set of candidate items with diversity:

SELECT * 
FROM ids($item_ids)
ORDER BY score
REORDER BY diversity(0.4), exploration(0.2)
LIMIT 10

Hybrid Search

Combine similarity and text search for better recall:

SELECT * 
FROM similarity(embedding_ref='als', limit=50),
     text_search(query='$query', mode='lexical', limit=50)
WHERE category = '$category'
ORDER BY score
LIMIT 20

User-to-User Recommendations

Find similar users:

SELECT * 
FROM similarity(embedding_ref='user_embedding', 
               encoder='precomputed_user',
               input_user_id='$user_id',
               entity_type='users',
               limit=50)
LIMIT 10

Troubleshooting

Common Errors

"Unknown retriever function: 'similiarity'"

Unknown retriever function: 'similiarity'. 
Supported: candidate_attributes, column_order, filter, ids, similarity, text_search
Did you mean: similarity?

Check function name spelling (case-insensitive)
The error message includes suggestions for similar function names

"All retrievers must use the same entity_type"

All retrievers must use the same entity_type. Found: items and users

Ensure all retriever functions in your query use the same entity_type parameter
Remove conflicting entity_type specifications

"Parameter 'limit' exceeds maximum"

Parameter 'limit' exceeds maximum allowed value of 1000. Got: 5000

The LIMIT value must be ≤ 1000
Reduce the LIMIT value or use pagination for larger result sets

"'limit' is a reserved keyword"

Failed to parse SQL: 'limit' is a reserved keyword and cannot be used as a 
parameter name. Use 'max_results' instead.

Don't use limit as a parameter name
Use max_results, result_limit, or similar instead

"JOINs are not supported"

JOINs are not supported. Use retriever functions to combine data sources: 
FROM similarity(...), text_search(...), etc.

Use multiple retriever functions in the FROM clause instead of JOINs

"Subqueries in FROM clause are not supported"

Subqueries in FROM clause are not supported. Use retriever functions instead

Replace subqueries with retriever functions

"GROUP BY and aggregations are not supported"

GROUP BY and aggregations are not supported. This DSL is designed for ranking 
queries, not analytical queries.

This query language is for recommendations/ranking, not analytics
Use a data warehouse or OLAP system for analytical queries

"Nested function calls are not supported"

Nested function calls are not supported. 
Example: similarity(embedding_ref=func('arg')) is invalid.
Use simple values: similarity(embedding_ref='als')

Don't nest functions within function arguments
Use parameters for dynamic values instead

"Missing required parameter: 'input_user_id'"

Missing required field: 'parameters.user_id'. Please include this field in 
your request.

Provide all required parameters in the request
Check saved query parameter schemas for required fields

"Embedding 'als2' referenced in retriever is not defined"

Embedding 'als2' not found in model configuration

Verify the embedding name exists in your model configuration
Check for typos in the embedding_ref parameter

Query Performance Issues

Slow queries:

Reduce retriever limit values
Add filters earlier in the pipeline (in retriever where or WHERE clause)
Use truncate() to limit candidate sets before scoring
Check if multiple retrievers can be optimized or combined

Low recall:

Increase retriever limit values
Use multiple retrievers to combine different retrieval strategies
Adjust diversity/exploration strength (lower values improve relevance)

Inconsistent results:

Ensure parameters are provided consistently
Use pagination keys for consistent pagination
Check for non-deterministic operations (some reorder functions may have slight variations)

Error Handling

The query language provides helpful error messages for common issues:

Unknown retriever functions: Includes suggestions for similar function names
Invalid parameter syntax: Explains correct parameter format
Missing required parameters: Lists which parameters are required
Entity type mismatches: Explains entity type conflicts
Invalid function arguments: Describes expected argument types and values
Limit violations: Shows maximum allowed values

All errors include context about where the error occurred in your query to help with debugging.

Error Response Format

Error responses follow this structure:

{
  "status_code": 422,
  "message": "Invalid query configuration: similarity() requires a non-empty 'embedding_ref'",
  "data": null
}

Common Error Messages:

"SQL query cannot be empty" - Query string is empty or whitespace
"Unknown retriever function: 'X'" - Function name not recognized
"All retrievers must use the same entity_type" - Entity type conflict
"Parameter 'limit' exceeds maximum" - LIMIT value too large
"Failed to transpile SQL query: ..." - SQL syntax error
"Saved query 'X' not found" - Saved query doesn't exist
"Missing required field: 'X'" - Required request field missing

Quick Reference

Retriever Functions

Function	Purpose	Key Parameters
`similarity()`	Vector similarity search	`embedding_ref`, `encoder`, `limit`
`text_search()`	Full-text search	`query`, `mode`, `limit`
`column_order()`	Order by column values	`columns`, `limit`
`filter()`	Filter-based retrieval	`where`, `limit`
`ids()`	Retrieve by IDs	List of IDs or parameter
`candidate_attributes()`	Rerank from attributes	Parameter reference

Query Encoders

Encoder	Use Case	Key Parameters
`precomputed_user`	User-based recommendations	`input_user_id`
`precomputed_item`	Item-to-item similarity	`input_item_id`
`interaction_pooling`	Pool user interactions	`input_user_id`, `truncate_interactions`
`interaction_round_robin`	Round-robin from interactions	`input_user_id`, `num_clusters`
`user_attribute_pooling`	Encode user attributes	`input_user_id`, `input_user_features`
`item_attribute_pooling`	Encode item attributes	`input_item_id`, `input_item_features`

Filter Functions

Function	Purpose	Parameters
`prebuilt()`	Apply prebuilt filter	`filter_ref`, `input_user_id`
`truncate()`	Limit candidate set	`max_length`

Reorder Functions

Function	Purpose	Parameters
`diversity()`	Reduce similarity	`strength` (0.0-1.0)
`exploration()`	Balance exploit/explore	`strength` (0.0-1.0)
`boost()`	Emphasize retrievers	`strength` (0.0-1.0)

Common Parameters

$user_id - User identifier (string)
$item_id - Item identifier (string)
$query - Search query text (string)
$item_ids - List of item IDs (array of strings)
$item_attributes - Item attributes dictionary (object/array)
$limit - Result limit (integer) - Note: use LIMIT clause when possible

Array Parameters Example:

{
  "parameters": {
    "item_ids": ["item1", "item2", "item3"]
  }
}

Limits

Maximum LIMIT: 1000
Default LIMIT: 15
Maximum string length: 1000 characters

SQL Operators

Comparison: =, !=, >, <, >=, <=
Logical: AND, OR, NOT
String: LIKE, ILIKE
Null: IS NULL, IS NOT NULL
Membership: IN (...), NOT IN (...)
Range: BETWEEN ... AND ...

Glossary

Ad-hoc Query: A query sent directly in the API request
Candidate: An entity retrieved by a retriever function
Embedding: A vector representation of an entity or query
Encoder: A component that converts user/item input into a query vector
Engine: A deployed model with its configuration (also called model name)
Entity Type: The type of entity being queried (items or users)
Filter: A condition that excludes entities from results
Pagination: Mechanism for retrieving results in pages
Prebuilt Filter: A filter defined in the model configuration
Query Pipeline: The sequence of retrieve → filter → score → reorder → limit operations
Reorder: Post-processing step that modifies result order (diversity, exploration, etc.)
Retriever: A function that retrieves candidate entities from the index
Saved Query: A pre-defined query stored in the model configuration
Score: A relevance score assigned to each entity (0.0 to 1.0)
Transpilation: Converting SQL syntax to query configuration objects

FAQ

Q: Can I use SQL joins in queries?

No, joins are not supported. Use multiple retriever functions in the FROM clause to combine data from different sources. The retrievers execute in parallel and results are merged.

Q: How do I filter out items a user has already seen?

Define a prebuilt personal filter in your model configuration and use it in your query:

WHERE prebuilt('exclude_seen', input_user_id='$user_id')

Q: Can I use SQL functions in WHERE clauses?

Yes, DuckDB SQL functions are supported. For example: WHERE LENGTH(name) > 10 or WHERE price * 1.1 > 100.

Q: What's the difference between ORDER BY and REORDER BY?

ORDER BY: Scores/sorts results based on relevance
REORDER BY: Post-processes already-scored results for diversity, exploration, or boosting

Use both together: ORDER BY score REORDER BY diversity(0.3)

Q: How do I debug slow queries?

Use return_explanation=true to see:

How many candidates each retriever found
How long each step took
Which filters removed the most candidates

Q: Can I cache query results?

The platform handles caching automatically. Identical queries with identical parameters within a short time window may return cached results.

Q: What happens if a parameter is missing?

If a required parameter is missing, you'll get a 422 error with details about which parameter is missing. Optional parameters use their default values if not provided.

Q: Can I use this for analytics queries?

No, this DSL is designed for ranking and recommendation queries, not analytical queries. It doesn't support GROUP BY, aggregations, or other analytical operations.

Q: How do I create a saved query?

Add it to your model configuration YAML under queries and deploy. See Defining Saved Queries for details.

Q: What happens if my query returns zero results?

Queries that return zero results are handled gracefully. You'll receive an empty results array with a 200 OK status. The explanation field (if requested) will show how many candidates were found at each stage, helping you debug why no results were returned.

Q: Can I test queries before deploying?

Yes! You can test queries in two ways:

Ad-hoc queries: Use the ad-hoc query endpoint to test SQL syntax and logic before creating saved queries
Console UI: Use the query console in the dashboard to interactively test queries with live data

Q: Are there limits on query complexity?

There are no explicit limits on query complexity (number of retrievers, filters, etc.), but very complex queries may impact performance. Monitor query execution times using return_explanation=true and optimize as needed.

Table of Contents​

Quick Start​

Overview​

SQL vs YAML Format​

Basic Syntax​

Entity Types​

Retriever Functions​

similarity()​

text_search()​

column_order()​

filter()​

ids() / candidate_ids()​

candidate_attributes()​

Query Encoders​

precomputed_user (default)​

precomputed_item​

interaction_pooling​

interaction_round_robin​

user_attribute_pooling​

item_attribute_pooling​

Filter Functions​

prebuilt()​

truncate()​

Reorder Functions​

diversity()​

exploration()​

boost()​

Parameters​

Parameter Syntax​

Common Parameters​

Parameter Examples​

SELECT Clause​

WHERE Clause​

ORDER BY Clause​

LIMIT Clause​

Complete Examples​

Basic Similarity Search​

Text Search with Filtering​

User Search​

Reranking with Diversity​

Complex Query with Multiple Filters​

Column Ordering​

Query Patterns and Recipes​

Pattern: Cold Start Recommendations​

Pattern: Multi-Stage Retrieval​

Pattern: Time-Based Filtering​

Pattern: A/B Testing Queries​

Pattern: Category-Specific Recommendations​

Unsupported Features​

Limits and Constraints​

Result Limits​

String Length Limits​

Performance Considerations​

Pagination​

Using Pagination​

API Usage​

Endpoints​

Authentication​

Making Query Requests​

Defining Saved Queries​

Response Format​

Best Practices​

Query Design​

Performance Tips​

Security Considerations​

When to Use Each Retriever​

Common Use Cases​

Personalized Recommendations​

Search with Filters​

Trending Items​

Reranking​

Hybrid Search​

User-to-User Recommendations​

Troubleshooting​

Common Errors​

Query Performance Issues​

Error Handling​

Error Response Format​

Quick Reference​

Retriever Functions​

Table of Contents

Quick Start

Overview

SQL vs YAML Format

Basic Syntax

Entity Types

Retriever Functions

similarity()

text_search()

column_order()

filter()

ids() / candidate_ids()

candidate_attributes()

Query Encoders

precomputed_user (default)

precomputed_item

interaction_pooling

interaction_round_robin

user_attribute_pooling

item_attribute_pooling

Filter Functions

prebuilt()

truncate()

Reorder Functions

diversity()

exploration()

boost()

Parameters

Parameter Syntax

Common Parameters

Parameter Examples

SELECT Clause

WHERE Clause

ORDER BY Clause

LIMIT Clause

Complete Examples

Basic Similarity Search

Text Search with Filtering

User Search

Reranking with Diversity

Complex Query with Multiple Filters

Column Ordering

Query Patterns and Recipes

Pattern: Cold Start Recommendations

Pattern: Multi-Stage Retrieval

Pattern: Time-Based Filtering

Pattern: A/B Testing Queries

Pattern: Category-Specific Recommendations

Unsupported Features

Limits and Constraints

Result Limits

String Length Limits

Performance Considerations

Pagination

Using Pagination

API Usage

Endpoints

Authentication

Making Query Requests

Defining Saved Queries

Response Format

Best Practices

Query Design

Performance Tips

Security Considerations

When to Use Each Retriever

Common Use Cases

Personalized Recommendations

Search with Filters

Trending Items

Reranking

Hybrid Search

User-to-User Recommendations

Troubleshooting

Common Errors

Query Performance Issues

Error Handling

Error Response Format

Quick Reference

Retriever Functions