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 and data_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:

1. Rate preview events: Apply pricing functions to hypothetical events for building price calculators and “what-if” scenarios
2. 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 timestamps
• timeframe_end (required): Exclusive upper bound for event timestamps
• customer_id (optional): Required when querying ingested events, omit for preview events
• external_customer_id (optional): Alternative to customer_id
• events (optional): Up to 500 preview events to rate instead of using ingested data
• price_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.

{
  "customer_id": "customer_123",
  "timeframe_start": "2024-01-01T00:00:00Z",
  "timeframe_end": "2024-01-31T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "storage_price_id",
      "grouping_keys": [
        "storage_tier",
        "region", 
        "day_floor_timestamp_millis(timestamp_millis)"
      ],
      "filter": "storage_tier IN ('standard', 'premium')"
    }
  ]
}

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.

{
  "timeframe_start": "2024-01-15T00:00:00Z",
  "timeframe_end": "2024-01-15T23:59:59Z",
  "events": [
    {
      "event_name": "compute_usage",
      "timestamp": "2024-01-15T10:00:00Z",
      "properties": {
        "compute_hours": 100,
        "instance_type": "large",
        "region": "us-east-1"
      }
    },
    {
      "event_name": "compute_usage", 
      "timestamp": "2024-01-15T14:00:00Z",
      "properties": {
        "compute_hours": 50,
        "instance_type": "medium",
        "region": "us-west-2"
      }
    }
  ],
  "price_evaluations": [
    {
      "price": {
        "name": "Compute Hours",
        "item_id": "compute_hours_price",
        "cadence": "monthly",
        "model_type": "unit",
        "unit_config": {
          "unit_amount": "0.10"
        },
        "currency": "USD",
        "billable_metric_id": "compute_hours_metric"
      },
      "grouping_keys": [
        "instance_type",
        "region"
      ]
    }
  ]
}

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.

{
  "customer_id": "customer_456",
  "timeframe_start": "2024-01-01T00:00:00Z",
  "timeframe_end": "2024-01-31T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "api_usage_price",
      "grouping_keys": [
        "endpoint",
        "customer_tier"
      ],
      "filter": "response_code = '200'"
    }
  ]
}

​

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.

{
  "customer_id": "customer_789",
  "timeframe_start": "2024-01-01T00:00:00Z",
  "timeframe_end": "2024-01-07T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "data_processing_price",
      "grouping_keys": [
        "job_type",
        "data_source",
        "complexity_tier",
        "hour_floor_timestamp_millis(timestamp_millis)"
      ],
      "filter": "job_status = 'completed' AND data_size_gb > 1"
    }
  ]
}

​

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.

{
  "customer_id": "customer_101",
  "timeframe_start": "2024-01-15T00:00:00Z",
  "timeframe_end": "2024-01-15T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "database_operations_price",
      "filter": "transaction_id = 'txn_abc123' OR query_type = 'complex_join'"
    }
  ]
}

​

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

-- Group by hour
hour_floor_timestamp_millis(timestamp_millis)

-- Group by day (with timezone support)
day_floor_timestamp_millis(timestamp_millis)

​

Aggregation Functions

-- Count events
COUNT(*)

-- Count distinct values
COUNT(DISTINCT region)

-- Get earliest value by timestamp
EARLIEST_BY(region, timestamp_millis)

-- Get latest value by timestamp
LATEST_BY(instance_type, timestamp_millis)

-- Mathematical operations
LEAST(cpu_hours, max_cpu_hours)
GREATEST(cpu_hours, min_cpu_hours)

-- Rounding
ROUND(data_gb, 2)

​

Filtering Examples

​

Basic Property Filtering

-- Exact matches
region = 'us-east-1'
instance_type = 'large'

-- Numeric comparisons
cpu_hours > 10
data_gb >= 100

-- Multiple conditions with AND/OR
region = 'us-east-1' AND cpu_hours > 10
tier = 'premium' OR tier = 'enterprise'

-- IN clauses
region IN ('us-east-1', 'us-west-2')
tier IN ('premium', 'enterprise')

-- Null checks
customer_segment IS NOT NULL
api_key IS NULL

​

Advanced Filtering

-- Combining multiple conditions
environment = 'production' AND region = 'us-east-1' AND cpu_hours > 50

-- Complex boolean logic
(tier = 'premium' AND region = 'us-east-1') OR (tier = 'enterprise' AND cpu_hours > 100)

-- Timestamp filtering
timestamp_millis >= 1704067200000

​

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

{
  "data": [
    {
      "price_groups": [
        {
          "grouping_values": [
            "us-east-1",
            "large"
          ],
          "quantity": 150.0,
          "amount": "15.00"
        },
        {
          "grouping_values": [
            "us-west-2", 
            "medium"
          ],
          "quantity": 75.0,
          "amount": "7.50"
        }
      ],
      "currency": "USD",
      "price_id": "price_123"
    }
  ]
}

​

Multiple Prices Response

When evaluating multiple prices, you’ll get separate entries in the data array:

{
  "data": [
    {
      "price_groups": [
        {
          "grouping_values": ["compute"],
          "quantity": 100.0,
          "amount": "50.00"
        }
      ],
      "currency": "USD",
      "price_id": "compute_price_id"
    },
    {
      "price_groups": [
        {
          "grouping_values": ["storage"],
          "quantity": 500.0,
          "amount": "25.00"
        }
      ],
      "currency": "USD",
      "inline_price_index": 0
    }
  ]
}

​

Best Practices

​

Performance Optimization

1. Limit result sets: Use filters to reduce the number of rows returned
2. Choose appropriate time ranges: Shorter ranges perform better
3. Optimize grouping: Avoid excessive grouping dimensions
4. Use specific filters: More specific filters improve query performance

​

Data Accuracy

1. Validate preview events: Ensure preview events match your actual event schema
2. Test with small datasets: Start with limited time ranges when exploring
3. Verify metric definitions: Ensure your billable metrics align with expectations
4. Handle edge cases: Consider how your filters handle null or missing properties

​

Integration Patterns

1. Caching: Cache results for frequently accessed data
2. Pagination: Use time-based pagination for large datasets
3. Error handling: Implement robust error handling for API limits
4. 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:

// Get daily usage trends
const dailyUsage = await evaluatePrices({
  customer_id: customerId,
  timeframe_start: startDate,
  timeframe_end: endDate,
  price_evaluations: [
    {
      price_id: priceId,
      grouping_keys: ["day_floor_timestamp_millis(timestamp_millis)"]
    }
  ]
});

// Get usage by service
const serviceBreakdown = await evaluatePrices({
  customer_id: customerId,
  timeframe_start: startDate,
  timeframe_end: endDate,
  price_evaluations: [
    {
      price_id: priceId,
      grouping_keys: ["service_name"]
    }
  ]
});

// Get top consumers
const topConsumers = await evaluatePrices({
  customer_id: customerId,
  timeframe_start: startDate,
  timeframe_end: endDate,
  price_evaluations: [
    {
      price_id: priceId,
      grouping_keys: ["user_id"],
      filter: "user_id IS NOT NULL"
    }
  ]
});

​

Price Comparison Tool

Compare costs across different pricing models:

const priceComparison = await evaluatePrices({
  timeframe_start: startDate,
  timeframe_end: endDate,
  events: previewEvents,
  price_evaluations: [
    {
      // Current pricing
      price_id: "current_price_id"
    },
    {
      // Alternative unit pricing
      price: {
        name: "Alternative Unit Pricing",
        item_id: "alt_unit_price",
        cadence: "monthly",
        model_type: "unit",
        unit_config: { 
          unit_amount: "0.05" 
        },
        currency: "USD",
        billable_metric_id: "api_calls_metric"
      }
    }
  ]
});

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.