Freeform text - order notes, service call summaries, complaint descriptions - arrives attached to individual transactions. It is tempting to store it directly in the fact table, but two properties usually argue against it:
Sparsity. Most transactions have no comment. A text column that is null for 95% of rows still occupies space in every row.
Repetition. Comments are rarely unique. Standard phrases like "Customer requested expedited shipping" recur across thousands of transactions.
Storing the text once in a comments dimension and referencing it by foreign key avoids both.
Fact Table:
transaction_id
date_key
customer_key
comment_key
amount
TXN-123
20260101
C-100
COM-5
$100
TXN-124
20260101
C-212
COM-0
$250
Comments Dimension:
comment_key
comment_text
COM-0
No Comment
COM-5
Customer requested expedited shipping due to holiday.
Transactions without a comment reference the No Comment member rather than storing a NULL foreign key.
Because the dimension holds one row per distinct comment, attributes derived from the text - sentiment score, category, language, whether it mentions a competitor - can be stored alongside it and used for grouping and filtering without reprocessing the text at query time:
comment_key
comment_text
sentiment
category
COM-5
…
Neutral
Shipping
COM-9
…
Negative
Billing
This is often the stronger argument for the separate dimension: derived attributes computed once per distinct comment rather than once per transaction.
NOTE
The sparsity and repetition arguments are weaker on columnar platforms, where nulls cost little and repeated values compress well. If comments are near-unique and your platform handles wide sparse columns efficiently, keeping the text in the fact table is a reasonable choice. Decide from your storage engine and cardinality, not from the rule alone.
Text Comments Dimension
Freeform text - order notes, service call summaries, complaint descriptions - arrives attached to individual transactions. It is tempting to store it directly in the fact table, but two properties usually argue against it:
Storing the text once in a comments dimension and referencing it by foreign key avoids both.
Fact Table:
Comments Dimension:
Transactions without a comment reference the
No Commentmember rather than storing aNULLforeign key.Derived Attributes on the Comment
Because the dimension holds one row per distinct comment, attributes derived from the text - sentiment score, category, language, whether it mentions a competitor - can be stored alongside it and used for grouping and filtering without reprocessing the text at query time:
This is often the stronger argument for the separate dimension: derived attributes computed once per distinct comment rather than once per transaction.
NOTE
The sparsity and repetition arguments are weaker on columnar platforms, where nulls cost little and repeated values compress well. If comments are near-unique and your platform handles wide sparse columns efficiently, keeping the text in the fact table is a reasonable choice. Decide from your storage engine and cardinality, not from the rule alone.