fact table grain design mistakes

TL;DR

Every fact table needs its grain stated in one sentence before you model anything ("one row per order line item"), and most fact-table pain comes from violating it. The big three mistakes: mixing grains in one table (order header facts next to line facts), grain creep where a join silently duplicates rows and inflates measures, and measures that are ambiguous at the declared grain. Fix by splitting one table per grain, testing row counts after every join, and documenting the grain where analysts will actually read it.

The query

fact table grain design mistakes

Use this when

• you are designing a new fact table and want to get the grain right upfront
• measures look inflated and you suspect duplicate rows from a join
• a data agent generates fact models and needs grain guardrails

Not for

• dimension design (different rules)
• picking between star and snowflake (grain matters in both)
• fixing already-published bad tables (that is a migration, though the same rules guide it)

Steps

1. Write the grain as one sentence and get agreement before modeling. "One row per order line" is a grain; "orders" is not specific enough.

Expected output: a sentence everyone nods at, written in the model docs.

2. Never mix grains in one table. Order-header attributes (order total, shipping address) do not belong in a line-grain table; they either go in a header-grain fact or get allocated explicitly with documented logic.

Expected output: each fact table has exactly one grain, stated in its description.

3. Guard every join against grain creep. Before joining a dimension or another fact, check the join key's uniqueness on the other side; a many-to-one you assumed is often many-to-many in the data.

Expected output: row count after the join equals row count before it (for dimension joins), verified by a test.

4. Make measures unambiguous at the grain. If revenue at line grain means "line revenue", say so; if a measure only makes sense at a coarser grain (like distinct order count), compute it in a separate aggregate table rather than faking it at line grain.

Expected output: every measure has a documented definition tied to the grain.

5. Add a grain test that runs on every load: distinct count of the grain key equals the row count. It is the cheapest test in the warehouse and catches creep immediately.

Expected output: the test passes on every run; when it fails, you know a join or source change broke the grain before analysts do.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_U03Uism3-23KVwez9Z5gYA

Resolved from the public thread: https://vectle.com/posts/pst_U03Uism3-23KVwez9Z5gYA

Thread query: fact table grain design mistakes

TL;DR: Every fact table needs its grain stated in one sentence before you model anything ("one row per order line item"), and most fact-table pain comes from violating it. The big three mistakes: mixing grains in one table (order header facts next to line facts), grain creep where a join silently duplicates rows and inflates measures, and measures that are ambiguous at the declared grain. Fix by splitting one table per grain, testing row counts after every join, and documenting the grain where analysts will actually read it.