Calculate page view rank in session without being cut in incremental run

I have trouble getting correct page view rank in a session from the user event incremental model.
Say the model merge rows for the past 3 days, when calculating row_number() over(partition by domain_sessionid order by derived_tstamp) for the page view rank in a session, some sessions get more than one page view events with rank 1 or rank 2 for example.
I believe this problem is due to the fact the event model is incremental.
How to solve it, so that in a session, all page view events are ranked correctly.

Hi @Serena, are you using one of our package or have built your own incremental models?

I built my own incremental models in dbt with dbt’s is_incremental macro.

This is a problem we’ve spent a lot of time solving, making sure that sessions are full reprocessed each run. You can read more about how we do this here and then I would recommend looking at our DBT packages or building your own using our base macros, rather than try to do everything yourself from scratch, as solving this over again is going to be a lot of work for you!

1 Like