Principle:Helicone Helicone ClickHouse Cost SQL Generation
| Knowledge Sources | |
|---|---|
| Domains | Cost Calculation, Analytics Database, SQL Generation |
| Last Updated | 2026-02-14 00:00 GMT |
Overview
Generating SQL CASE expressions that compute LLM request costs directly within the analytics database engine, avoiding row-by-row application-level cost calculation.
Description
When computing aggregate cost metrics across millions of LLM requests -- for example, total spend per day, average cost per model, or cumulative cost for a project -- it is far more efficient to perform the calculation server-side in the analytics database than to fetch every row and compute costs in the application layer. ClickHouse Cost SQL Generation addresses this by translating the entire cost registry (all providers and their model-specific per-token rates) into a SQL CASE expression that ClickHouse can evaluate natively.
The generated SQL expression encodes a two-level decision tree. The outer CASE branches on the provider column to select the correct cost table. Each provider branch contains an inner CASE that matches the model column (using SQL =, LIKE, or ILIKE operators corresponding to the equals, startsWith, and includes matching strategies) and computes cost as a linear combination of token columns: prompt_tokens, completion_tokens, prompt_audio_tokens, completion_audio_tokens, prompt_cache_write_tokens, and prompt_cache_read_tokens. When no provider-specific match is found, the expression falls through to the default provider (OpenAI) cost table.
To maintain numeric precision without floating-point errors, all per-token rates are multiplied by a precision constant of 1,000,000,000 (one billion) before being embedded in the SQL. The final result is wrapped in a sum() aggregate and optionally divided by the same precision multiplier to convert back to USD. This integer-arithmetic approach ensures exact cost computation across billions of rows.
Usage
Use this pattern when:
- Building analytical queries that aggregate costs across many requests.
- Generating dashboard metrics like total spend, cost per model, or cost per user.
- Implementing cost-based filtering or sorting in ClickHouse queries.
- Backfilling cost data for historical requests.
Theoretical Basis
This is an application of pushing computation to the data -- a fundamental principle in analytical database design where expressions are evaluated at the storage layer rather than the application layer, minimizing data transfer and leveraging the database engine's vectorized execution.
The precision multiplier technique implements fixed-point arithmetic in a system (SQL) that would otherwise use floating-point. By scaling all rates to integers, multiplying, then dividing at the end, the approach avoids accumulated floating-point rounding errors that would become significant when summing millions of sub-cent amounts.
The generated SQL embodies a code generation pattern where application-level data structures (the provider/cost registry) are compiled into database-executable code (SQL expressions) at query construction time, bridging the impedance mismatch between TypeScript data models and SQL execution.