Simulation of event flows in an evolving environment - Nikolai Golov - SmartData 2023 (Category Architecture)

Interesting. report Nikolai Golov on the topic of clickstream modeling. Kolya is a cool expert who first worked with data vault based on teradata, anchor modeling based on vertica. (avito) and anchor modeling+ based on snowflake (on Manychat). Interestingly, the volume of clickstream often exceeds the volume of other data, so it is important to learn how to work with it correctly:) Clickstream itself is characterized by what we have.

• Hundreds. (thousand) type The pattern of each type of event changes
• Analysts want one table, quick and simple.

It can be stored as partially structured data. (ala json) normal-base (conditional) And it works to a certain extent. Conventionally, if we need to analyze up to a million events, we can work directly with these json, but if there is more data to analyze, then the problem with json begins to play a role. Performance: Column databases do not speed up json in sql Scheme: what attributes to use in our json Security: How to store data in json securely (parlance) As a result, you can store clickstream in the form of json in the data lake, but not for long - otherwise we get data swamp. But what do we do about it?

Next, Kolya talks about anchor modeling, of which he is a fan. Anchor modeling is the development of data vault and 6 normal shape. We have an achor entity table, an attribute attribute attribute table, and a tie table. This is a very complex approach, but Avito managed to decompose the clickstream into hundreds of normalized pages. But now Kolya does not recommend doing so because of problems.

• Hundreds of giant pages. To download we need joins 10 9 lines For analysis we need joins 10 9 lines.
• It's like the same thing twice.

And then Kolya offers his approach, which he came to after Avito. The suggestion is to stop somewhere between 6NF and jsons. Kolya begins by recalling how star schema works:

• We have a fact sheet in hundreds of tables.
• Most of the table is sparse. For analysis you need joins on dimensions tables Each event with a pair of attributes adds columns for all tables In general, this approach doesn't work for clickstream - we won't stuff it into conditional ClickHouse and dictionaries too big for joins.

But instead of star schema, you can use it. activity schema. The idea is to do analytics on a single table, where we try to present all events as a list of activities that occur with a particular entity. In the end, the tablet sees something like this.

• stream_id
• user_id
• event_id
• action_datetime

And there are advantages to this solution.

• Fast and cheap download.
• Column. Compression. ClickHouse Convenient basic analytics: DAU/WAU/MAU, Funnels, Conversions Next, Kohl shows how this analysis can be done directly on examples of SQL queries. What do you do if you have additional data? If the attributes of events happen often, then they can be put into the main scheme. But if attributes are rare, they can be carried into small normalized tables with details. (data vault mix) - these are satellite tables. Interestingly, it looks like a star schema at first glance, but differs dramatically: In star schema references to dimensions come from the fact table. In this scheme, we have links from satellite tables to an activity table that knows nothing about additional attributes. This solves the problem with discharged tables + allows you to implement security features, since sensitive attributes can be stored in satellite tables.

And then Kolya talks about the fact that in this scheme you can still classify events through tags that can be hung on events and it works well. The second classification option is the creation of a metadata registry, which is much more difficult to implement in the company’s processes.

#Data #DWH #Processes #Management #Architecture #Software #SoftwareArchitecture