# Fact Candidate Table: [Fact Table Name]

> One sheet per fact table. Documents all candidate measures and their properties.

---

## Overview

| Field | Value |
|---|---|
| Fact table name | |
| Grain statement | |
| Fact type | Transaction / Periodic Snapshot / Accumulating Snapshot |
| Primary business process | |
| Source system(s) | |
| Estimated row volume | |
| Update frequency | |

---

## Fact Candidates

| # | Fact name | Business label | Definition / calculation | Additive? | Data type | Unit | Source system | Source field | Notes |
|---|---|---|---|---|---|---|---|---|---|
| 1 | | | | `fully / semi / non` | | | | | |
| 2 | | | | | | | | | |

**Additivity guide:**
- **Fully additive** — can be summed across ALL dimensions. Safe to aggregate anywhere. (e.g., revenue, units sold, tickets opened)
- **Semi-additive** — can be summed across some dimensions but NOT all. Typically cannot be summed across time. (e.g., inventory balance, headcount, MRR balance) → Flag the restricted dimension explicitly.
- **Non-additive** — cannot be meaningfully summed across any dimension. (e.g., ratios, rates, percentages) → Store the components instead, never the derived value.

---

## Non-additive fact handling

> For each non-additive fact, document the components to store instead.

| Non-additive fact | Why it's non-additive | Component A | Component B | Derived calculation |
|---|---|---|---|---|
| ACOS | Ratio — summing across campaigns is meaningless | ad_spend | attributed_revenue | ad_spend / attributed_revenue |
| Conversion rate | Ratio | trial_starts | paid_conversions | paid_conversions / trial_starts |
| | | | | |

---

## Semi-additive fact handling

| Semi-additive fact | Can sum across | CANNOT sum across | Recommended aggregation for time |
|---|---|---|---|
| inventory_units_on_hand | Product, Warehouse | Date (time periods) | Latest snapshot / period-end balance |
| mrr_balance | Account, Plan | Date | End-of-period balance |

---

## Degenerate dimensions

> Attributes that are used for filtering/grouping but don't warrant a separate dimension table.
> Stored directly as columns on the fact table.

| Attribute | Description | Example values |
|---|---|---|
| order_number | Transactional identifier | ORD-20240115-00123 |
| | | |

---

## Dimension foreign keys

> List all dimension tables this fact table joins to, with the role and join key.

| Dimension | FK column on fact | Role / alias | Notes |
|---|---|---|---|
| Date | order_date_key | Order date | |
| Date | ship_date_key | Ship date | Role-playing |
| | | | |

---

## Accumulating snapshot stage columns

> Only for accumulating snapshot fact tables. Document each lifecycle stage.

| Stage | Column name | Date populated | Milestone description |
|---|---|---|---|
| 1 | trial_start_date | When trial begins | Account enters trial |
| 2 | | | |
| Final | | | |

---

## Known calculation conflicts

> Different teams may calculate the same metric differently. Document and resolve.

| Metric | Team A definition | Team B definition | Agreed resolution |
|---|---|---|---|
| | | | |

---

## Open questions

1.
2.