#266 Data modelling 6.4 min
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.
Predict the sessions
Copy the code folder first. Read the events in events.py and predict how many sessions each user has after batch 1, using a gap of more than 30 minutes. Run python run.py first to check, then predict the result of python run.py late and run it.
A hint
User 1 has a break between 09:20 and 10:05.
Change the session gap
In sessions.py, change GAP_MINUTES from 30 to 45. Predict how the batch 1 session count changes, then run python run.py first and python run.py late. When I ran it, batch 1 gave 2 sessions, appending gave 6 and rebuilding from event time still gave 4.
A hint
User 1's break between 09:20 and 10:05 is exactly 45 minutes.
Make one late event on time
In events.py, change the received time of e08 in BATCH_2 from 10:58 to 09:36. Predict how many late events are reported and how the append count changes, then run python run.py late. When I ran it, only e09 was reported late, appending said 6 and rebuilding still said 4.
A hint
The append job only looks at rows received after 10:30.
Add a new user event
In events.py, add the event ("e12", "u3", "10:55", "10:55", "item") to BATCH_2, after the e03 line. Predict whether user 3 gets a second session, then run python run.py late. When I ran it, 6 were delivered and 5 were new, and user 3 had one session from 10:50 to 10:55 with 2 events.
A hint
A session starts a new one only when the gap is more than 30 minutes.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
Related videos
- #111 Incremental Loads: Watermarks and the Late Update Trap Data engineering and pipelines
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2 Data modelling
- #250 Data Modelling Basics: Entities, Keys, Relationships Data modelling
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet Data modelling
