Event Data Modelling: Events, Sessions, Late Arrivals
Two late clicks made an append-only job report 7 sessions; rebuilt from event time there were 4.
Video #266 ยท Data modelling
Steps in the video
- 0:00 Two late events: 4 real sessions, 7 if you only append.
- 0:31 The idea
- 1:07 Setup
- 1:30 The events
- 2:01 One row per event
- 2:18 Loading a batch
- 2:33 Sessions from event time
- 3:04 Run the first batch
- 3:22 Spotting late events
- 3:33 Two ways to handle batch two
- 3:57 Run the late batch
- 4:27 What this does not show
- 4:59 Mistakes to avoid
- 5:32 My take and next steps
- 5:56 Recap
Files
- events.py (44 lines)
- run.py (37 lines)
- sessions.py (27 lines)
Tested with
Python 3.12.15
What this does not show
Eleven made-up events in SQLite, rebuilt in full each time, with no lateness cut off, one time zone and no speed measured.
Sources
- https://en.wikipedia.org/wiki/Session_(web_analytics)
- https://nightlies.apache.org/flink/flink-docs-stable/docs/concepts/time/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-fact/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/transaction-fact-table/
- https://www.sqlite.org/lang_conflict.html
- https://www.sqlite.org/lang_datefunc.html
- https://www.sqlite.org/windowfunctions.html
Learning IT by Example.