appspace / appspace/kwwhat

Refactor fact_visits.charger_ids / charge_attempt_ids arrays into a bridge table

Open
#159 0 comments 0 reactions 1 assignee Claimed by @SYegor View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.