Fact Table

Answering "how many paid conversions did we get last month?" used to mean stitching several tables together. Payment records sat in whatever table the billing system wrote to, signup dates sat in the accounts table, and which plan an account was on sat somewhere else again. None of those tables were built for analysis — they were built so the product would run — so only the people who knew which column matched which other column could get an answer out.

The result showed up every week in the same way: three different revenue numbers for the same month. One person had subtracted refunds, one had cut the month by invoice date instead of payment date, and one had left trial accounts in. Each number was correct by its own method, and put side by side none of them could be used. Meetings ended with "let's go sort out the numbers first" instead of a decision.

The fix is structural — it lives in the shape of the table. Dimensional modeling splits analytical tables into a measurement side and a descriptive side, and the measurement side is the fact table. Instead of the shape that is convenient for the product to write, the data gets stacked again in the shape that is convenient for a person to question. When everyone reads that one table, revenue stops arriving in three versions.

One row stands for one event, and the columns come in three kinds.

1. Key columns — they say whose event this is, when it happened, and which product it concerned. Account id, plan id, date id, that sort of thing. The value itself is just a short number; the description behind that number lives separately in a dimension table.
2. Measure columns — the numbers actually recorded for the event. A payment of $22, twelve seats purchased — values you can add up.
3. Timestamp columns — the exact moment the event occurred. It looks redundant next to the date key, but the key exists for grouping and the timestamp preserves the hour and minute, so both stay.

The first decision is the grain: what exactly one row means in this table. If one payment is a row, the table grows one way; if one account's daily payment total is a row, the same events pile up in a completely different count. Write the grain down, or someone will later push daily totals into the same table and revenue summed from it will come out twice the real figure.

Only addable values belong in measure columns. A conversion rate, where the division already happened, does not. Averaging a 4 percent conversion rate in March with 2 percent in April gives 3 percent, but if the two months had different visitor counts the combined rate is not 3 percent. Store the conversion count as one column and the visit count as another and divide at read time, and the number holds no matter which period you group by.

A data engineer builds it, and the data analyst who will use it decides what goes in. Pulling values from the source systems and making them land at the same time every day is engineering work. What one row should count, and which columns are needed at all, is only known by the person who has to answer questions with the table.

Where the table sits is also fixed. It does not live in the raw layer, which holds source data untouched, but in the mart layer, cleaned up for direct use. The raw copy stays untouched because it is where you return when a transformation rule turns out to be wrong, and the fact table is recalculated from there.

The readership is much wider than the authorship. Only a handful of analysts open the table directly; everyone else meets it through a dashboard or a weekly report. The paid-conversion count Mia reads out in a meeting is just a number on a screen to the room, but which columns were summed to produce it is settled in advance.

That is why the table comes with a document beside it. It records what one row is, what rule computes each column, and how refunds and trial accounts were handled. Where a metric definition pins down what a number means, this document connects that definition to the column where it is actually implemented.

"That number isn't in the fact table as a column, so we can't pull it today. Even if we add it, data starts accumulating next week."

New measures only fill from the moment the column is created, so past periods stay empty.

"The grain of this table is one payment, so you'll have to group it before looking at accounts."

One account holds several rows, so counting accounts means removing duplicates first.

"That revenue figure came straight out of the fact table. Nobody calculated it on the side."

The source is one agreed table, so it won't contradict the number quoted in another meeting.

At Pagedeck the table started with a request to see revenue by plan. Mia asked whether team-plan revenue had grown since the pricing change, and Theo opened the billing system's table — which had no plan names in it, only product codes. The table mapping codes to names was maintained by another team and some names had been changed along the way, so the plan attached to a payment from six months ago looked different depending on when you queried.

So they built a new table with one row per payment, keeping only the account id, the plan id, and the amount, and moved plan names into a dimension table. Mia now reads revenue grouped by plan straight off a dashboard, and Theo never edits a row of the fact table when a plan gets renamed.

The name leads people to read a fact table as the raw record itself. "Fact" sounds like something untouched from the source. But this table is a built artifact: a person chose its grain and its columns. Source columns that analysis doesn't use are dropped, and rules like refund handling and trial-account exclusion are already applied. Treat it as raw and you get an endless stream of "why isn't this billing column in here?" requests — and bolting on a column each time you're asked produces a table nobody can explain end to end.

The other common belief is that more columns make a better table, because having everything in one place looks convenient. But putting descriptive values like plan names directly into the fact table breaks two things. On a table holding tens of millions of payments, long text columns make queries noticeably slower, and every rename means finding and editing every past row — miss one and the same plan splits into two names in the totals. Keep the description as a single row in a dimension table and there is exactly one place to fix the name.