Survey data tangles two things that need separating: participation (who was asked and who answered) from the answers themselves. The model does that with three facts, keeps the atomic answers long rather than wide so a changing question set never changes the schema, and uses a bridge for the learner-to-aim fan-out.
Fact
Derived fact
Dimension
Bridge
PK primary key → foreign key measure
Facts
fact_invitationaccumulating snapshot · grain: one invitation (learner × survey) · from participants API
Keys
learner_key
provider_key
survey_key
contact_method_key
Milestone dates
date_issued_key
date_completed_key
date_started
date_submitted
Attribute
current_status
Derived
invited, completed (counts)
response rate, duration
fact_answergrain: learner × survey instance × question (× option) · from responses API
Keys
learner_key
survey_key
question_key
answer_option_key
date_response_key
Typed value slots
answer_numeric
answer_code
answer_text
fact_matched_pairgrain: one learner · derived from fact_answer
Keys
learner_key
provider_key
Per tracked measure
baseline_value
outcome_value
delta
e.g. wellbeing, confidence, work status
Dimensions
dim_learner
learner_key
uln (pseudonymised)
provider
ethnicity, sex
age band, LLDD
employment status
postcode district
dim_provider
provider_key
provider_name
ukprn
dim_aimgrain: learning aim; provision is a rollup attribute
aim_key
learning_aim_ref
aim_title
course_category (provision)
level
ssa_tier_2
dim_survey
survey_key
snap_survey_id
wave (baseline/outcome)
route (email/QR)
provider
dim_question
question_key
question_code
question_text
wave
answer_type
measure
dim_answer_option
answer_option_key
question_code
option_code
option_label
dim_contact_method
contact_method_key
method (email / SMS / WhatsApp / QR / phone)
dim_date
date_key
date, month
year, period
The aim fan-out, handled by a bridge
A learner is surveyed once but has several aims, so reporting "by provision" fans out. A learner-to-aim bridge models the many-to-many honestly.
bridge_learner_aimmany-to-many
learner_key
aim_key
weight (optional)
Rule
Headcounts at learner grain (learner counted once). Provision and course splits go through the bridge. Never sum the two together without knowing which you are doing, or a multi-aim learner double counts. This is the honest version of what the MATCHED pivot tried to fudge.
Different answer types, handled in dim_question
The reason survey models rot is forcing scales, categories, multi-selects and free text into one shape. Here the heterogeneity lives in dim_question and the typed value slots, not in the fact's shape.
| Answer type | Stored in | How it aggregates |
|---|---|---|
| Scale / Likert | answer_numeric | Mean and distribution |
| Categorical (single) | answer_code + dim_answer_option | Counts and proportions |
| Boolean | answer_numeric (0/1) or answer_code | Proportion true |
| Multi-select | one row per selected option | Counts per option (fits the grain naturally) |
| Free text | answer_text | Not aggregated; for qual |
Before and after
Each baseline and outcome question maps to a shared measure in
dim_question (baseline Q7 and outcome Q4 both map to "confidence"). That mapping is what lets fact_matched_pair line them up for change, even though they are different question IDs on different surveys.
Why long, not wide
- Question sets change between waves and providers. In
fact_answera new question is new rows plus a newdim_questionentry, with no schema change. A wide table would need a new column each time and break everything downstream, the same reason the ILR stays long. - The invitation fact is separate from answers, so response rates have a clean denominator (the invitation) rather than being inferred from who happened to answer. It is an accumulating snapshot: milestone dates (issued, completed) are updated in place as each participant progresses.
- The dashboard reads the curated
fact_matched_pairfor before and after, so it never pivots the atomic fact at query time.