What the Star Actually Measures: Fact Tables

Opening Scene

An astronomer studying a particular star doesn’t record everything imaginable about it in one place. The star’s measurable properties — its brightness on a given night, its distance, its recorded temperature — go into the observation log. Its name, its constellation, and the telescope used to observe it are looked up separately, referenced rather than repeated. The observation log stays lean and genuinely measurement-focused, precisely because that discipline is what makes it useful for calculating real trends over time.

A fact table is held to this exact same disciplined standard.

In Plain English

A fact table holds the measurable events at the center of a star schema — the actual numbers a business cares about summing, averaging, or counting, like a sale amount, a quantity shipped, or a page view — along with foreign keys pointing out to the dimension tables that describe each event’s context. What deliberately does not belong in a fact table is descriptive text or attributes; those belong in dimensions, kept genuinely separate to avoid bloating and repeating information across millions of rows.

The Old Way

Designing a genuinely well-formed fact table has always required this same discipline, even before dimensional modeling became a widely taught practice:

• Measures are the actual numeric values worth aggregating — revenue, quantity, duration — and a fact table’s real reason for existing is to hold these efficiently at scale, often across millions or billions of rows.
• Foreign keys to dimension tables provide the context for each measurement, without repeating that context’s full descriptive detail inside the fact table itself, keeping each row lean.
• Keeping descriptive attributes out of the fact table — a customer’s name, a product’s category — is a deliberate discipline, not an oversight; that detail belongs in a dimension table, referenced by key, so it’s stored once and stays consistent everywhere it’s used.

Getting this right has always meant resisting the temptation to add “just one more descriptive column” directly to the fact table for convenience, since that convenience compounds into real bloat and inconsistency at genuine scale.

What’s Changing (and Why AI Is the Reason)

1. AI-assisted measure discovery can identify which columns in raw source data are genuinely additive, measurable facts versus which are actually descriptive attributes in disguise. Rather than a modeler manually inspecting every column, AI-assisted analysis of a column’s actual value distribution and usage pattern can flag whether it behaves like a genuine measure or like a dimension attribute that’s been miscategorized.
2. AI-assisted grain detection can identify what a single row of a proposed fact table actually represents, catching a genuinely mixed or unclear grain before it causes real downstream confusion. A fact table with an inconsistent grain — some rows representing one event, others representing a summary of several — produces subtly wrong aggregations; AI-assisted analysis of the actual data can catch this mismatch early.
3. AI agents summarizing or querying fact tables directly benefit enormously from a table that’s genuinely well-formed, since ambiguous or bloated fact tables produce ambiguous or wrong AI-generated answers. A lean, correctly-scoped fact table gives an AI agent a much more reliable foundation for aggregation than one cluttered with descriptive attributes that don’t belong there.

The Metaphor, Fully Extended

Observatory Element	Fact Table Concept
The observation log recording brightness, distance, and temperature on a given night	The fact table, holding the genuine numeric measures of an event
Looking up the star’s name and constellation separately rather than rewriting them every night	Foreign keys to dimension tables, avoiding repeated descriptive detail
An observation log that mixes single readings with multi-night averages, confusing later analysis	A fact table with an inconsistent grain, mixing different levels of detail in one place
An astronomer double-checking that a logged number is actually a genuine measurement, not a description in disguise	AI-assisted measure discovery distinguishing genuine measures from miscategorized attributes
A senior astronomer reviewing whether each log entry represents one clear observation, not several blended together	AI-assisted grain detection catching an inconsistent or unclear fact table grain

Quick Recap

• A fact table holds the measurable events at the center of a star schema, along with foreign keys to the dimensions that describe each event’s context.
• Genuine measures belong in the fact table; descriptive attributes belong in dimension tables, kept deliberately separate to avoid bloat and inconsistency.
• AI-assisted measure discovery can distinguish genuine measures from miscategorized attributes, and AI-assisted grain detection can catch an inconsistent fact table grain early.
• A lean, correctly-scoped fact table gives both human analysts and AI agents a far more reliable foundation for trustworthy aggregation.

Where This Fits in the Series

Article 1 introduced the star schema’s basic shape. This article covered what genuinely belongs at its center. Article 3 looks at the planets that describe it — dimension tables.