Querying data with SQL
You often want to query the event data that Orb is storing, to better understand attribution to specific dimensions of your event – even if these dimensions aren’t directly relevant to how you bill customers.
Since Orb’s architecture is designed to run aggregation queries over large sets of event data at query time rather than at ingestion time, the product also exposes this capability directly for ad-hoc querying.
For example, you might want to:
- View usage for your compute metric broken down by
cluster_name
, where a user may have hundreds of clusters active in a given time period. - Understand how much a specific transaction contributed to a processing charge by point-filtering to a known
transaction_id
. - Provide more detailed analytics, such as breaking down usage and cost data by
bucket_name
anddata_tier
which is included in events you send to Orb. - Build an interactive explorer in your application to view hourly data over a day time range, rather than the default daily views available through Orb’s subscription usage and cost APIs.
- Provide a view into end customer usage by
api_key
prefix, allowing users to understand the source of API requests.
Evaluate Prices API
The evaluate prices API is Orb’s most powerful querying tool, enabling you to slice, dice, and rate event data with SQL-like flexibility. This endpoint serves two primary functions:
- Rate preview events: Apply pricing functions to hypothetical events for building price calculators and “what-if” scenarios
- Query and rate ingested events: Run metrics over your existing event data with advanced filtering and grouping
This dual capability makes the API essential for customer-facing analytics, billing transparency, price modeling, and detailed usage exploration.
Core Capabilities
The endpoint can evaluate:
- Up to 100 price evaluations in a single request (existing or inline-defined prices)
- Up to 500 preview events for rating scenarios
- Historical data up to 100 days in the past (for ingested events)
- Complex filtering and grouping using computed properties with SQL-like expressions
- Results limited to 1000 rows to ensure performance
Request Structure:
timeframe_start
(required): Inclusive lower bound for event timestampstimeframe_end
(required): Exclusive upper bound for event timestampscustomer_id
(optional): Required when querying ingested events, omit for preview eventsexternal_customer_id
(optional): Alternative to customer_idevents
(optional): Up to 500 preview events to rate instead of using ingested dataprice_evaluations
(required): Array of prices to evaluate with optional filtering and grouping
Key Use Cases
1. Customer-Facing Usage Analytics
Scenario: Provide your customers with detailed breakdowns of their usage and costs, going beyond standard invoice line items.
Example: A cloud storage provider wants to show customers their costs broken down by storage tier, region, and time period.
This query provides daily usage and cost data segmented by storage tier and region, enabling rich customer dashboards.
2. Price Calculator and Modeling
Scenario: Build an interactive price calculator that shows potential costs before customers commit to a plan.
Example: A compute platform wants to let prospects estimate costs based on expected usage patterns.
This enables real-time cost estimation as prospects adjust their expected usage parameters.
3. Invoice Line Item Auditing
Scenario: Provide detailed breakdowns of invoice charges to help customers understand exactly what they’re being billed for.
Example: Breaking down a monthly API usage charge by endpoint and customer tier.
4. High-Dimensional Usage Analysis
Scenario: Analyze usage patterns across multiple dimensions that aren’t part of your standard billing structure.
Example: A data processing platform analyzing usage by job type, data source, and processing complexity.
5. Transaction-Level Cost Attribution
Scenario: Understand the cost impact of specific transactions or operations.
Example: Analyzing the cost contribution of individual database queries or API calls.
Working with Preview Events vs. Ingested Events
Preview Events (Rating Mode)
Use preview events when you want to:
- Build price calculators
- Model “what-if” scenarios
- Test new pricing structures
- Provide cost estimates before usage occurs
Key characteristics:
- Events are provided directly in the request
- No historical data required
- Perfect for forward-looking analysis
- Limited to 500 events per request
- No customer_id required - the API rates the provided events directly
Ingested Events (Query Mode)
Use ingested events when you want to:
- Analyze historical usage patterns
- Provide detailed billing breakdowns
- Audit past charges
- Build usage analytics dashboards
Key characteristics:
- Uses events already stored in Orb
- Requires customer_id to specify which customer’s events to query
- Can query up to 100 days of historical data
- Supports complex filtering and aggregation
- Ideal for retrospective analysis
Advanced Filtering and Grouping
Computed Properties
The API supports computed properties for sophisticated data manipulation using Orb’s supported functions:
Time-based Functions
Aggregation Functions
Filtering Examples
Basic Property Filtering
Advanced Filtering
Response Structure and Interpretation
The API returns results grouped according to your specified grouping_keys
. The response contains a data
array where each element represents the results for a specific price, with the following structure:
- price_groups: Array of grouped results for this price
- grouping_values: The specific values for each grouping dimension
- quantity: The aggregated metric value for the group
- amount: The calculated cost for the group
- currency: The currency of the price
- price_id: The ID of the price (for existing prices)
- inline_price_index: The index of the inline price (for inline-defined prices)
Example Response
Multiple Prices Response
When evaluating multiple prices, you’ll get separate entries in the data
array:
Best Practices
Performance Optimization
- Limit result sets: Use filters to reduce the number of rows returned
- Choose appropriate time ranges: Shorter ranges perform better
- Optimize grouping: Avoid excessive grouping dimensions
- Use specific filters: More specific filters improve query performance
Data Accuracy
- Validate preview events: Ensure preview events match your actual event schema
- Test with small datasets: Start with limited time ranges when exploring
- Verify metric definitions: Ensure your billable metrics align with expectations
- Handle edge cases: Consider how your filters handle null or missing properties
Integration Patterns
- Caching: Cache results for frequently accessed data
- Pagination: Use time-based pagination for large datasets
- Error handling: Implement robust error handling for API limits
- Rate limiting: Respect API rate limits in high-frequency scenarios
Common Patterns and Examples
Building a Usage Dashboard
Combine multiple API calls to build comprehensive usage dashboards:
Price Comparison Tool
Compare costs across different pricing models:
The evaluate prices API transforms how you can interact with your billing data, enabling everything from simple usage queries to sophisticated pricing analysis. By leveraging its filtering and grouping capabilities, you can build rich, customer-facing analytics and powerful internal tools for pricing optimization.