Transaction Fact Table
One row per sale, payment, or click, at the finest grain the process records. Transaction fact tables answer “what happened” directly; state takes more work.
-
-
Surrogate Key
A warehouse gives each dimension row its own meaningless number, so one customer can have several historical versions, each with a key a fact can point to.
-
Durable Key
When an employee rejoins with a new number or two customer records turn out to be one person, a durable key keeps a single identity across every change.
-
Grain, Facts, and the Arithmetic That Quietly Goes Wrong
Wrong totals rarely raise an error. See why grain is a contract, which facts you may not sum, and the join that inflates a number without warning.
-
Semi-Additive Fact
A balance can be summed across accounts but not across days. Semi-additive facts need a point in time or an average along the dimension where sums are nonsense.
-
Periodic Snapshot Fact Table
A periodic snapshot stores the state of every account or product once per day or month, including quiet ones, so “what was it then” becomes a simple read.
-
Non-Additive Fact
Ratios and distinct counts cannot be added up, and averaging them is usually wrong too. Store the additive parts, aggregate those, and compute the ratio last.
-
Natural Key
A customer number or SKU identifies a record in its source system. It is essential for tracing data back, and unreliable as the only key a warehouse joins on.
-
Drill-Across
To compare orders with shipments, do not join the two fact tables. Query each at the report’s grain on shared attributes, then merge the results.
-
Conformed Dimension
Orders, shipments, and returns can be compared by customer or month only if those attributes mean exactly the same thing in each. That is a conformed dimension.
-
Accumulating Snapshot Fact Table
One row per order, updated as it is picked, shipped, and delivered. An accumulating snapshot makes lead times a subtraction, but keeps one date per step.
-
Operational and Analytical Models: Designed for Different Jobs
One schema cannot serve both a transaction and a decade of history. Here is what each model is designed to protect, and what breaks when the two are confused.