Step Dimensions
Sequential processes - web sessions, application funnels, support case escalations - are recorded as one fact row per step. The fact table knows each step happened, but nothing in the row says where it sits in the overall sequence.
A step dimension supplies that context.
Fact Table:
| event_id | session_id | step_key | page_name | duration_seconds |
|---|---|---|---|---|
| E-01 | S-100 | STEP-1-3 | Home | 10 |
| E-02 | S-100 | STEP-2-3 | Pricing | 45 |
| E-03 | S-100 | STEP-3-3 | Checkout | 90 |
Step Dimension:
| step_key | step_number | total_steps | steps_remaining | is_final_step |
|---|---|---|---|---|
| STEP-1-3 | 1 | 3 | 2 | No |
| STEP-2-3 | 2 | 3 | 1 | No |
| STEP-3-3 | 3 | 3 | 0 | Yes |
| STEP-1-7 | 1 | 7 | 6 | No |
| STEP-2-7 | 2 | 7 | 5 | No |
The last two rows belong to a session of a different length. STEP-1-3 and STEP-1-7 are both "step 1", but one has two steps remaining and the other six.
A row exists for every (step_number, total_steps) pair, so one dimension serves sessions of any length. It stays small - a few hundred rows covers every realistic session - and is reused across every fact row.
Position then becomes an ordinary attribute filter: abandoned sessions are total_steps = 1, and the average duration of a final step is a filter on is_final_step. Without the dimension, each needs a window function or a self-join.
Why not just put the step columns on the fact table?
You certainly can. The separate dimension was originally designed for older row-based databases, where extra columns added storage costs to every fact row. Modern columnar databases largely eliminate this problem.
However, a step dimension still makes it much easier to add new attributes later. For example, adding a new category like funnel_phase is a simple update to a 200-row dimension table, rather than a massive rewrite of billions of fact rows.
As a middle ground, you can store just the step_number and total_steps directly on the fact table and introduce a step dimension later, joining on (step_number, total_steps). That pair is a valid natural key as long as every dimension attribute is determined by those two values.
The step key can't be assigned until the session ends
total_steps is unknown while a session is in progress, so the key for its first event depends on how many events follow. Sessions must therefore be loaded as complete units, and "ended" needs a definition - usually an inactivity timeout. For streaming loads, either stage events until the session closes or assign a placeholder member and update the keys afterwards.