The cost fact table

A fact table in the ordinary warehousing sense is one row per occurrence of the thing you measure, carrying the dimensions you slice by and the measures you add up. Kimball's dimensional modelling work in the 1990s named the two decisions that matter and they are the same two here. The grain is what one row means, and for cost attribution the grain is one model call — not one request, not one user session, not one hour. The dimensions are the columns somebody will eventually want to group by, and the rule for those is that adding one is cheap going forward and impossible going backward.

This chapter is the schema those two decisions produce, along with what it costs to store, how it is rolled up and partitioned so a dashboard does not scan it, and an honest comparison against the alternative that requires no engineering at all: give every feature its own provider API key and read the per-key breakdown out of the console.

One row per model call, written at call time#

The columns below are doing two different jobs, and the test that separates them is whether adding a column up across rows produces a quantity somebody wants. Sum cost_usd over every row a tenant caused and the result is that tenant's spend, which is a number finance will act on. Sum tenant_id and there is nothing there, because its values are labels and labels do not add. The first kind are the measures, the second the dimensions — the columns you aggregate by rather than aggregate.

The other half of the distinction is about time, and it decides the schema rather than the vocabulary. A fact is an event: it happened at an instant, it is finished, and nothing occurring afterwards changes what it was. A dimension describes an entity that persists — a tenant, a model, a plan tier — and entities change. Facts are therefore append-only and dimensions are not, which is why attaching one to the other at read time is the hazard the snapshotting section below is about.

The grain has to be settled before any column can be chosen, because a column is only meaningful relative to it. The grain is the sentence "one row means one of these", and every measure on the row has to be a quantity belonging to exactly one of them; until that sentence is written there is no way to tell whether a proposed column measures the row or something larger that the row is part of. Choosing one call rather than something coarser follows from aggregation being a function while disaggregation is not. Hourly rows can always be added into a week, and a weekly row can never be taken apart into hours, because many different sets of hours add to the same week and the total does not say which set produced it. That information was destroyed when the coarse row was written, not hidden inside it awaiting a cleverer query.

The question of what fraction of spend goes on retries, one of the queries below, is where that becomes concrete. At the grain of one call it is a filter on attempt, because an attempt is a property of a call. At the grain of one request attempt has nowhere to live: a request that retried twice is a single row whose token counts already have both attempts added into them, and no column added later can separate them, because separating them needs the per-attempt numbers and those were summed away before anything was stored. The question is not unanswered but unaskable, and it stays unaskable for as long as the table exists.

There is a floor as well, and the provider sets it. Money is measured per call because that is where the provider's arithmetic happens: one usage object comes back attached to one call, and one rate is applied to it. A row per token would carry no measurement, since there is no per-token event and no per-token price, so splitting a call's cost across its tokens would be an apportionment rule you invented rather than a figure anybody billed. The grain of a fact table is the grain of the event that produces the measure, and going finer swaps a fact for a modelling choice. What the fine grain costs is bytes, and bytes are purchasable; a coarser grain costs answers, and those are not.

CREATE TABLE llm_cost_facts (
  call_id            uuid PRIMARY KEY,
  occurred_at        timestamptz NOT NULL,
  hour_bucket        timestamptz NOT NULL,   -- date_trunc('hour', occurred_at)

  -- dimensions
  tenant_id          text        NOT NULL,
  user_id            text,                   -- null for system-initiated work
  feature            text        NOT NULL,
  correlation_id     text        NOT NULL,
  parent_call_id     uuid,
  attempt            smallint    NOT NULL DEFAULT 1,
  model              text        NOT NULL,
  effort             text,
  environment        text        NOT NULL,   -- prod | staging | eval

  -- measures
  input_tokens       integer     NOT NULL DEFAULT 0,
  output_tokens      integer     NOT NULL DEFAULT 0,
  cache_read_tokens  integer     NOT NULL DEFAULT 0,
  cache_write_tokens integer     NOT NULL DEFAULT 0,
  reasoning_tokens   integer,
  cost_usd           numeric(14,8) NOT NULL,
  rate_card_version  text        NOT NULL,
  usage_status       text        NOT NULL,   -- complete | client_timeout | ...
  latency_ms         integer
);

CREATE INDEX ON llm_cost_facts (hour_bucket, tenant_id, feature);
CREATE INDEX ON llm_cost_facts (correlation_id);

The hour_bucket column is redundant against occurred_at and worth the four bytes. Every dashboard query groups by hour or by day, and a stored bucket is an index the planner can use directly rather than a function call it has to evaluate over thirty million rows. It also fixes the timezone question once, at write time, in one place, instead of leaving each analyst to decide whether their week starts at midnight UTC or midnight in Berlin.

The claim about the planner is worth taking apart, because it is why a row that is cheap to write is what makes a query cheap to answer. An index is an ordered structure over the values of a column, so a predicate on that column is answered by seeking into the ordering and reading only the part that matches. date_trunc('hour', occurred_at) is not a column. There is nothing ordered to seek into, so the planner's only strategy is to compute the function for every row it considers and test each result — reading every row in order to find out which rows it needed. Storing the bucket converts a computation repeated once per row per query, forever, into one performed once, on the row, while the value was already in hand. The reviewer's objection that a derived column violates normalisation rests on a real rule — two copies of a value drift apart when either can be updated — and a fact row is never updated, so the failure that rule prevents cannot occur here.

environment earns its place for a duller reason: an evaluation suite running nightly against production keys is real spend that must reconcile against the invoice and must not appear in any per-tenant chart. Without the column, someone eventually filters it out with a WHERE tenant_id NOT LIKE 'eval%' clause that works until the naming convention drifts.

With the table in place, a weekly spend question is one line:

SELECT sum(cost_usd)
FROM   llm_cost_facts
WHERE  feature = 'summarize'
  AND  environment = 'prod'
  AND  hour_bucket >= date_trunc('week', now()) - interval '1 week'
  AND  hour_bucket <  date_trunc('week', now());

And so are the questions nobody has asked yet:

-- Which tenants drove last month's increase?
SELECT tenant_id,
       sum(cost_usd) FILTER (WHERE hour_bucket >= '2026-03-01') AS mar,
       sum(cost_usd) FILTER (WHERE hour_bucket <  '2026-03-01') AS feb
FROM   llm_cost_facts
WHERE  hour_bucket >= '2026-02-01' AND hour_bucket < '2026-04-01'
GROUP  BY tenant_id
ORDER  BY mar - feb DESC
LIMIT  10;

-- What fraction of spend is retries?
SELECT feature,
       sum(cost_usd) FILTER (WHERE attempt > 1) / sum(cost_usd) AS retry_share
FROM   llm_cost_facts
WHERE  hour_bucket >= now() - interval '7 days'
GROUP  BY feature;

-- Cost of the most expensive user actions this week
SELECT correlation_id, feature, count(*) AS calls, sum(cost_usd) AS usd
FROM   llm_cost_facts
WHERE  hour_bucket >= now() - interval '7 days'
GROUP  BY correlation_id, feature
ORDER  BY usd DESC
LIMIT  20;

At 4.2 million calls a day and roughly 220 bytes a row, the raw table grows about 920 MB a day, or 83 GB over a ninety-day retention window. That is small enough to keep hot and large enough that a dashboard should not scan it. The standard answer is a rollup written by a scheduled job — sums grouped by (hour_bucket, tenant_id, feature, model) — which for 300 tenants, 5 features and 4 models has a theoretical ceiling of 144,000 rows a day and lands around 38,000 in practice, because most tenants do not use most features every hour. A seven-day dashboard query then touches about 266,000 rows instead of 29 million. Keep the raw table for the questions the rollup cannot answer, which are all the ones involving correlation_id, user_id, or a dimension nobody thought to roll up.

Each of those figures is a multiplication worth doing rather than accepting. Roughly 220 bytes on each of 4.2 million rows is a little over nine hundred megabytes, which is where 920 MB a day comes from, and ninety of those days is the 83 GB. The rollup's ceiling is a product because the job emits at most one row per distinct combination of its grouping columns: 300 tenants by 5 features by 4 models is six thousand possible combinations in any one hour, and twenty-four hours of them is 144,000. A GROUP BY emits a row only for combinations that actually occurred, which is why it lands nearer 38,000. Seven days of that is around 266,000, against seven days of raw rows at 4.2 million a day, which is the 29 million a dashboard would otherwise scan.

What makes the rollup legitimate at all is a property of the specific aggregates it stores, and it does not extend to every aggregate somebody might want. Addition is associative, so the sum over a week equals the sum of the hourly sums, and a count behaves the same way. That is why sum(cost_usd) and count(*) can be computed once per hour and added up afterwards without changing the answer by a cent. A median does not compose like that: the median of a week is not the median of the hourly medians, and there is no arithmetic on the stored hourly figures that recovers it. Nor does a distinct count, since adding the distinct users in each hour double-counts everybody who appeared in two of them. So a rollup answers exactly the questions expressible as sums and counts over the grouping key it was built with, and no column added to it later changes that.

Snapshot the dimensions that change#

There is a version of the mutable-rate-card problem from chapter 1 one level up, and it is easier to walk into because the table it involves belongs to somebody else. A tenant's plan tier is a natural dimension to slice spend by — enterprise against professional against trial — and the natural way to get it is a join to the tenants table, which is exactly the query-time join that reports March's spend against April's plan assignments. When a tenant upgrades on 12 April, every March row silently moves from the professional bucket to the enterprise bucket, and the margin analysis for the professional tier improves for reasons that have nothing to do with March.

The mechanism is the one chapter 1 (pricing a call at write time) found in the rate card. A query containing a join is evaluated against the state of both tables at the moment the query runs, so its answer is a function of two things: the facts, which are history, and the current contents of the dimension table, which are not. Change the second and every answer that depended on it changes too, with no query edited and no error raised. A report built that way does not report the past. It reports the past as re-described by the present.

Anything that can change independently of the fact and that you slice by should be snapshotted onto the row: plan tier, region, account manager, contract identifier, the pricing model in force for that tenant. Anything genuinely immutable — the tenant's creation date, its industry — can stay in the dimension table and be joined. The test is simple enough to apply in review: if this value changed tomorrow, should last month's report change with it? Where the answer is no, the value belongs on the row.

ALTER TABLE llm_cost_facts
  ADD COLUMN tenant_plan   text,     -- snapshotted: 'trial' | 'pro' | 'enterprise'
  ADD COLUMN tenant_region text;     -- snapshotted: affects data-residency routing

A column added in June is populated from June, and every row before that carries a null no amount of later work can fill, because the value it needed was a property of the world at the time the call was made. Being liberal with dimensions on day one costs bytes; being conservative costs history.

Snapshotting buys stability and does not buy correctness. If a provisioning bug records a tenant on the wrong tier, that wrong tier is frozen onto every row written while the bug was live, and fixing it is a scoped UPDATE over exactly those rows — the same shape of backfill chapter 1 describes for rows written with no rate in force, and reviewable for the same reason, which is that you can state which rows it touches before running it. What is being bought is that no report changes unless somebody deliberately changes it. Whether the value was right in the first place is a different job.

Partitions, retention, and what survives the drop#

At 920 MB a day the raw table wants monthly range partitions on occurred_at, which turns retention from a DELETE that locks the table for an hour into a DROP TABLE that returns immediately, and lets the planner skip whole partitions for the date-bounded queries that make up nearly all the traffic.

CREATE TABLE llm_cost_facts (
  -- columns as above
) PARTITION BY RANGE (occurred_at);

CREATE TABLE llm_cost_facts_2026_03 PARTITION OF llm_cost_facts
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Both halves of that come from what the database has to touch. A DELETE is row-by-row work, under locks other queries wait on; dropping a partition touches no rows at all, so its cost is proportional to the number of partitions rather than to the number of rows. Skipping works the same way: from the partition bounds alone the planner can prove that no row in the April partition satisfies a March predicate, and it never opens the file. That proof exists only because the partition key is the column the predicate is on, which is why partitioning by tenant_id would make the same date-bounded query visit everything.

The ordering constraint is that the rollup must be written before the partition is dropped, and the rollup must be complete rather than provisional — so the retention job depends on reconciliation having settled for that period, not merely on ninety days having elapsed. A rollup computed from a partition still receiving late-arriving asynchronous rows is permanently short by the tail, and the tail is the expensive part.

Follow that through and it is worse than an undercount. A rollup taken at the month boundary sums whatever existed at that instant, and rows for work caused in March keep arriving into April. Once the partition underneath is dropped the rollup is the only surviving record, it is internally consistent, and there is nothing left to compare it against, so the shortfall is not merely present but undetectable — a number with no source is a number nobody can audit.

Retain the rollups indefinitely. They are 38,000 rows a day, about 14 million a year, and they are what makes a year-over-year question answerable at all. The raw rows are what you keep for ninety days, because the questions they uniquely answer — which correlation ids were most expensive, which users, which retries — are questions asked about the recent past. Nobody asks in December which user action was most expensive last February; they ask what February cost, which is a rollup.

Against one API key per feature#

The competing proposal costs no engineering time at all: give every feature its own provider API key and read the per-key breakdown out of the provider's console. It deserves a serious comparison rather than a dismissal, because the one thing it does, it does better than anything you will build.

The belief underneath the proposal is that a provider's console attributes spend to features, and it is tempting for good reasons: the console genuinely shows a breakdown, the breakdown agrees with the invoice to the cent, and obtaining it costs an afternoon. What it breaks on is that the console's dimension is the credential, and a credential is a property of the process that made the call rather than of the reason the work was done. A shared code path holds one client and therefore one key, so its spend carries whichever key that module was constructed with rather than the caller's — the row below about a call path shared by two features.

One call also carries exactly one key, which is what produces most of the noes in the table below. Feature and tenant are independent dimensions, and a label taking one value at a time can encode one or the other. Encoding both means a key per combination, and 300 tenants across 5 features is an inventory of thousands of credentials to mint, distribute, rotate and revoke. Since nobody operates that, a per-key scheme carries whichever single dimension somebody chose in advance, and anything not in the key inventory before the traffic happened is unrecoverable afterwards.

The two right-hand advantages of A are genuine and neither is about attribution. Exact reconciliation is worth having, and rate-limit isolation is worth having — a runaway batch job on one key cannot starve the interactive path on another. Neither requires per-feature keys, though; both are achieved with one key per environment, which is a configuration you probably want anyway.

Exact reconciliation is exact for a reason worth naming: the provider's own meter does the grouping, so the breakdown and the invoice are one piece of arithmetic performed once, with no second measurement available to disagree. A cost table is a second measurement, and it differs from the invoice by the residual that chapter 4 (spend reconciliation) is about. So the choice is not which design is more accurate. A measures perfectly along one axis fixed before the traffic existed; B measures approximately along any axis chosen after it.

The fact table is not a trace store#

There is no room on these rows for prompts, completions, tool arguments or retrieved documents. Those belong in a tracing or evaluation system, keyed by the same correlation_id so a spend anomaly opens cleanly into a request somebody can read. Storing prompt text on the cost row triples its size, drags personally identifying data into a table that finance and analytics query freely, and turns a ninety-day retention policy into a legal question rather than a storage one.

Nor is it the wide events table the application already writes, which is the substitution that gets furthest through a review, because the two look identical from a distance: one row per event, with fields to group by. What separates them is that a log line is written for a person to read during an incident, so nothing constrains it: a field can be renamed at one call site and not another, can be missing, can change type, and each is a perfectly valid log line. In a fact table each is a broken row and no error is raised. A renamed feature is spend that silently leaves its group, which is what happened to the filter in the scenario above, and the repair was a column with a declared name, a declared type and a NOT NULL on it.

Nor is it a metrics time series, and building it inside one fails on cardinality — the count of distinct label combinations a metrics system has to hold a separate running series for. Three hundred tenants times five features times four models times five token categories is 30,000 label combinations before users enter the picture, and a Prometheus-style system will either fall over or force you to drop the dimension that made the data worth collecting. A columnar table handles that shape without complaint, and a label-based metrics system is the right place only for the two or three low-cardinality aggregates you actually alert on.

Cardinality is a hard limit rather than a tuning problem, because of what a label-based system has to keep resident. A counter is incremented in place, so it holds one live series — its own memory, its own retention — for every distinct combination of label values ever seen, and the count is a product because every value of one label can occur alongside every value of every other. A columnar table keeps nothing per combination: one comes into existence when a query groups by it and ceases to exist when the query ends.

What the table buys, in the end, is that spend questions stop being projects. The grain decides which questions are available at all, the snapshotted dimensions decide whether last month's answer stays put, and the rollup decides whether a dashboard is fast — but the property that matters most is that everyone asking gets the same number, because they are all running the same sum() over the same rows rather than each reconstructing a filter from memory.

That still leaves the table waiting to be asked. A cost regression that lands on a Tuesday afternoon does not announce itself; it sits inside a correct table until the invoice arrives, unless a small number of derived metrics are watching on somebody's behalf.