Refactor fact_visits.charger_ids / charge_attempt_ids arrays into a bridge table
- Dominant language
- No language data
- Stars
- 14
- Forks
- 3
- PR merge metrics
- No merged PRs in 30d
Description
fact_visits currently stores charger_ids and charge_attempt_ids as arrays directly on the fact row (one row per visit, with all member chargers/charge attempts collapsed into an array column). See models/marts/marts.yml docs on fact_visits.charger_ids / charge_attempt_ids and PR #158.
Problem
Arrays on a fact row are non-standard for dimensional modeling and create real friction:
- Querying/filtering by a member charger or charge attempt requires array functions (array_contains, unnesting, etc.) instead of a plain join or where.
- They can't be joined normally from BI tools or the semantic layer - today they're explicitly excluded from models/semantic/semantic_models.yml (not declared as a dimension or measure) specifically because of this.
- Counting or aggregating by member (e.g. "how many visits touched charger X") means unnesting the array first.
TODO
Replace the two array columns with a bridge table at visit_id x charger_id / visit_id x charge_attempt_id grain - one row per member instead of one array-valued column per visit. This would let the bridge join into the semantic layer like any other relationship, and let BI tools filter/aggregate by member without array handling.
first_charger_id/last_charger_id and first_charge_attempt_id/last_charge_attempt_id stay on fact_visits as-is either way - they're scalar and already semantic-layer-safe; this is only about the multi-valued arrays.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.