Dimension Table

The first analytical table a team builds almost always tries to hold everything. Payment date and amount go in, and then someone adds a column for account name, another for plan, another for signup source, another for the sales rep who owns the account. It is the fastest thing to build, and for a while it works fine.

The trouble surfaces the first time one of those descriptive values changes. Suppose the plan formerly called Pro is renamed Team. Every one of the 40,000 payment rows carrying that plan now has to be found and edited, and a single missed row means the same plan gets counted under two names for the rest of the year. Rename an account or redraw the industry categories and the same scramble repeats.

The second problem is the column that isn't there. At Pagedeck, Grace asks whether this quarter's revenue can be broken out by customer industry. Theo opens the payment table, finds no industry column, and says it can't be pulled today. Adding the column and backfilling 40,000 historical rows takes days — and the following week someone asks for the same view by region.

A dimension table solves both at once. Events go in one table, the descriptions of the things those events involve go in another, and the description side keeps exactly one row per account. Renaming a plan becomes a one-row edit. Adding an industry column means filling in 1,200 accounts rather than 40,000 payments, and the history that already exists picks up the new column for free.

A row is built from four kinds of columns: a key, attributes, hierarchy levels, and history columns.

1. Key — the value that identifies one row uniquely. It is the table's primary key, and it is usually a meaningless counter: 1, 2, 3. The fact table that records events stores only that number rather than the account name, and looks the name up when it needs one. A column that points at another table's row like this is called a foreign key.
2. Attributes — the values you want to group by. An account table carries plan, industry, signup source, region, and company size band, one column each. Every column added is one more angle the data can be cut from, which is why these tables tend to get wide rather than tall.
3. Hierarchy levels — when attributes nest inside one another, every level gets its own column. A date table carries date, week, month, quarter, and year side by side; a region table carries city, state, and country. Storing the month explicitly means grouping by month is a column choice rather than a calculation someone has to write correctly each time.
4. History columns — attributes that change over time get a valid-from date, a valid-to date, and a current flag. If an account moved from Free to Pro in May, the row is not overwritten; a second row is added. That way April revenue stays attributed to a free account and June revenue to a paid one.

The date table is the example almost every team builds. Generate one row per day from 2020 through 2030, then fill in day of week, week number, month, quarter, holiday flag, and business-day flag for each. Once it exists, a question like "weekday revenue last quarter" becomes a matter of picking two columns instead of writing date arithmetic.

Engineers who move and clean the data build these tables. Data analysts, business intelligence analysts, and everyone who opens a dashboard read them.

Building is not a one-time job. Whenever a source system starts producing a value it didn't have before, or a classification gets redrawn, the table has to be revised to match. These tables normally sit in the mart layer, the last layer of the store, where analysts pull from directly.

Which columns exist is not the builder's decision alone. The request comes first and the column follows. When Mia says at the start of a quarter which angles the team plans to look at, Theo fills those columns in first. Reverse the order — add columns nobody asked for — and the table collects twenty fields no query ever touches, each one still needing maintenance whenever the source changes.

From the reading side the table is mostly invisible. The plan names in a dashboard's dropdown, the list of regions down the left edge of a report: those come from here. That is why the stored values have to be words the reader already knows. Put Pro in the column, not P2. Leave raw codes in place and every person reading the dashboard needs a lookup sheet, and in practice they each interpret the codes slightly differently.

"That shouldn't be a new column on the fact table — put it on the account dimension."

The value describes the account rather than changing with each event, so it belongs on the descriptive side.

"To break it out by industry we'd need an industry column on the dimension, and right now it's empty."

The table exists but lacks the column that would support that particular cut.

"This account changed plans last month, and since the dimension only keeps the current value, last year's revenue is showing up as Pro."

Rows were overwritten instead of versioned, so historical numbers are now wrong.

At Pagedeck this table gets revised roughly once a quarter. When Sofia asks to see enterprise customers split by industry, Theo adds an industry column to the account table and fills in values for the existing 1,200 accounts. The part that has to be settled first is who owns the list of industries. Leave it open and the same company arrives as "IT," "Software," and "Tech" in three different rows, which splits its revenue three ways.

Once the values are filled in, nothing visibly changes except that the dashboard has one more filter. Mia and Sofia click the industry filter without ever knowing the table is there.

The most common misread is treating it as a small helper table that doesn't need much care. With 40,000 payment rows next to 1,200 account rows, that impression is easy to form. But nearly everything that appears as text on a dashboard comes from this side. One missing column blocks an entire category of question, and inconsistent values fracture the totals. Left alone, each analyst invents a private mapping from the raw data, and the same month's revenue arrives at the meeting as three different numbers.

The other misread is that the repetition should be removed. Seeing the same industry value on hundreds of rows looks like a design mistake — the instinct from transactional database work is to split it into its own table and store a reference. Dimension tables deliberately keep the values flattened. Splitting them saves storage but forces every reader to join tables correctly, and someone who doesn't know how to write that join simply never asks the question. Readability outranks storage here.