Different Roles in the Same Analytical Question
A fact table represents something that happened or a state observed at a defined moment. Examples include a fulfilled order item, an account snapshot, a machine reading, or a support interaction. Its grain states what one row means. Measurements, event indicators, and references to descriptive entities are recorded at that exact level. Fact tables usually grow as new activity arrives, so their design emphasizes consistent loading and efficient scanning.
A dimension table describes the entities used to interpret those facts. A customer, product, facility, date, employee role, or service category can supply names, groupings, and other attributes for filtering and labeling. Dimension rows change when descriptive context changes, not whenever a related event occurs. Their structure favors readable attributes and controlled history.
The roles meet in a business question. To examine shipped quantity by product family and destination region, the quantity belongs with the shipment event, while family and region belong with descriptive entities. The query joins those pieces and aggregates the measurement. Keeping event detail separate from reusable context makes that question easier to express and lets the same dimensions support other processes.
Grain, Keys, and Column Behavior
The most important fact-table property is grain. Every measure and foreign key must describe the same row-level event or snapshot. A header-level fee placed beside item-level quantities may repeat for every item and become overstated when summed. When measures naturally exist at different levels, separate fact tables usually protect their meaning better than a mixed-grain table.
Fact tables commonly carry compact references to dimensions plus measurements and operational identifiers needed for traceability. Some facts contain no numeric measure at all; the presence of a row records that an event occurred and can be counted. Other facts store balances or durations whose valid aggregation rules must be documented. Numeric storage alone does not determine whether a column is a measure.
Dimension tables hold descriptive attributes and a warehouse-managed key for joins. They also retain the source system's durable identifier so loading logic can find the correct member. A dimension may contain several historical rows for one source entity, each with a distinct warehouse key. This allows facts from different periods to retain the appropriate descriptive context without embedding changing labels directly in the fact.
How the Tables Behave in Queries
Analytical queries typically filter dimensions, join the matching keys to a fact table, and aggregate at the requested level. The dimension provides human-readable output, while the fact provides the observation being measured. A query can group the same facts by calendar period, region, category, or any compatible combination without copying those descriptions into every event row.
Join cardinality is a critical check. A fact key should resolve to one dimension row in the historical context represented by that key. If the dimension contains duplicate keys or a query joins on a nonunique source identifier, each fact can multiply and inflate results. If an unmatched key is dropped through an inner join, totals can shrink without an obvious error. Referential tests and an explicit unresolved member make both problems visible.
Dimensions can also relate to facts in less direct ways. One event may involve several participants or categories, requiring a bridge structure with allocation rules. A single dimension can play several roles, such as order date and delivery date, through separate keys. These patterns still preserve the distinction: the fact declares the event and its grain, while dimensions provide reusable viewpoints.
A Practical Classification and Review Method
When classifying a source field, ask a sequence of business questions. What event or state does this record represent? At what detail should it be analyzed? Is the field an observation at that grain, an identifier, or a description used to group observations? Will users sum it, filter by it, display it, or use it to trace back to a source? The answers are more reliable than deciding from the source data type or table name.
Review sample queries before finalizing the schema. Trace each requested measurement to a fact and each label or filter to a dimension. Check that the joins do not cross grains and that measures have stated aggregation behavior. Run examples containing repeated source identifiers, missing references, historical dimension versions, and events with no numeric measurement. Those cases expose design ambiguity early.
Use the worksheet on this page to classify candidate fields by grain, analytical role, key behavior, and expected change pattern. Then compare the worksheet with actual report requirements and remove fields that have no defined use. A disciplined classification process produces tables that explain themselves: facts say what was observed, dimensions say how the observation can be understood, and their keys preserve that relationship.
Table roles depend on declared grain and business use, so source table names alone are not a dependable classification rule.