Mayadem: from game events to a ClickHouse data warehouse

7 → 1

Book dimension

Seven normalised tables, one array-based dim_book

130+

Event definitions

For TRT Çocuk Oyun Dünyası, during development

5

Live tables

4 dimensions + 1 fact, each with an ETL log

5

Report personas

Publisher, school, student, individual, parent

Problem

Mayadem’s apps produce two kinds of data. Users, books, publishers and subscriptions live in PostgreSQL — in normalised tables designed for transactions. A book’s authors, categories, illustrators, language variants and translators sit in seven separate tables; getting everything about one book takes seven joins. Behavioural data arrives from the device as events: which profile read which book on which device for how many seconds, which scene closed when. These events pile up as Parquet files in S3 — millions a day.

Reports lived in the app’s admin panel as ready-made queries: most-read books, active subscribers, MRR, churn, funnel, retention. Each worked on its own; but questions that need both sources at once — “which category held which age group this month, who reads at weekends” — were either answered slowly or never asked.

2022 — Event design and marketing dashboards

The first piece of work was for TRT Çocuk Oyun Dünyası. While the app was being developed we worked line by line with the development team on which event is sent at which moment with which fields: app launch, language selection, parent panel, screen time, bedtime, subscription, game start and close — more than 130 events, each with its key–value fields. From the same list we tied metrics such as retention, session count, sessions per user and most-played games to the events they are computed from. On top we built marketing and cross-market comparison dashboards in Power BI.

The principle of that work became the foundation of the next: data that is not collected cannot be produced later. If an event’s name, moment and fields are written down up front, there is a meaningful history to move when the warehouse is built.

2025 — A ClickHouse warehouse for MagicPages

MagicPages is a multilingual reading app for children. In August 2025 we sat down with the team and walked through the data flows end to end — what happens in the database from a user’s first app open to SSO, how a book’s many-valued attributes are stored, how events are collected on the device and how often they are written to S3. Then we deliberately set a small target.

MVP. Move a single fact table of reading durations, with user, book and time dimensions, to ClickHouse and produce three reports from it: user reading performance, book popularity and reading time, time-based reading trends. Not every dimension and every fact from the start — as much as three reports need.

Array-based dimension. The question in the meeting was clear: “Do we keep dimensions normalised and join them, as in a traditional warehouse?” On SQL Server or PostgreSQL we would, because those engines are not flexible with nested structures. In ClickHouse the opposite holds: dim_book carries a book’s authors, categories and illustrators in array columns and its language variants and translators in a Nested structure, all in one table. “Books by this author” is a single has() call instead of three joins. The table deduplicates on modified date with ReplacingMergeTree; on the PostgreSQL side data is aggregated into arrays with CTEs, JSON variants are converted to Nested and loaded in batches.

Fact table. The ReadingSceneClosed event is read straight from S3 with ClickHouse’s S3 engine and written to fact_reading_session, enriched from the book dimension: session duration, pages read, reading speed, platform, device language, age. The table is partitioned by month and ordered by (event_date, user_id, book_id); low-cardinality fields use LowCardinality, the category array has a bloom-filter index. Materialized views on top give hourly user activity and daily book performance — queries go to a pre-aggregated summary, not the raw events.

Time dimension. dim_calendar is not just year–quarter–month; Turkish public holidays, school days, terms and weekend flags are precomputed. “Evening reading on school days” is a single filter.

What went live. As of November 2025 the warehouse has dim_book, dim_calendar, dim_child, dim_parent and fact_reading_session live; each table has its own ETL log table and there is a migration_history record. Every load is traceable, every schema change is recorded.

Roadmap

The target schema is ten dimensions and nine facts: user, book, date, time, school, class, publisher, device, location, subscription; reading session, book performance, user engagement, homework, student progress, revenue, rating, school and publisher analytics. On top of it a three-level report catalogue is defined for five personas — book-level revenue and completion rates for the publisher, class comparison and homework tracking for the school, personal progress for the student, habit analysis for the individual user, daily reading time and screen time for the parent. Twenty analytical scenarios group into four platforms: churn prediction and prevention, student success analytics, content intelligence, revenue optimisation.

The work continues in 2026 as data-organisation consulting: reviewing existing structures, testing alternative technologies, planning data flows, insight and visualisation.

Why this way

• The engine changes the design. Normalised dimensions and joins are a habit born of row-based engines’ limits. In a columnar engine, arrays and nested structures are both faster and more readable; we designed the warehouse around the engine’s strengths, not around habit.
• An MVP is three reports, not ten dimensions. The first live structure answered three questions; the schema grows as those answers get used.
• Event design comes before the warehouse. If session, profile, device and duration are not in the event, no fact table can produce them later.

Related

• Upily: discovery and audit of the Wonjo Kids data infrastructure
• Derimod: from data warehouse to Optail — three layers, seven projects in three years
• Defacto DSO: four years from the founding of a data science office

All our work