How PlaySimple Games Built User-Level Funnel Analytics with StarRocks

Originally published at: Medium

About PlaySimple Games

PlaySimple Games develops mobile puzzle games, with modes such as Daily Challenge and Tournaments. Its Business Intelligence team builds the large-scale data systems behind analytics, personalization and real-time insights for millions of players.

The original post is credited to PlaySimple Games' Business Intelligence Team and was published on Sukhjot Singh's Medium account.

PlaySimple's analysts and data scientists study what players do, and also who did it, at what time, in which order, and what they did afterward. That means working with billions of raw gameplay events rather than pre-aggregated tables.

The challenge

Funnels on atomic, user-level data

A typical PlaySimple funnel follows a player from Level Start to Level Complete, then Daily Challenge and Tournament Join, and finally Store Purchase. Building such funnels involves:

• More than 500 distinct event types, such as start, end, hint_used and reward_claimed
• More than 50 funnel templates across game modes
• Enrichment with player profile, geography, A/B bucket and device
• Ordering each player's events to rebuild the path through the game
• Finding the steps where players fail a puzzle or stop playing
• Tracking how players move from free to power user to spender

In practice, one funnel query filters the raw events, joins player attributes, rebuilds every player's timeline, finds the transitions between actions, and returns both aggregates and the individual paths. The team was stuck between pre-computing everything, which removed flexibility, and querying everything on demand, which was slow and hard to manage.

Why the existing engines fell short

PlaySimple already ran Trino, the distributed SQL engine analysts used for ad-hoc work, and Apache Druid, which served fast aggregates. Neither handled deep user journeys well:

• Druid stores data in columns tuned for filtering and aggregation. Rebuilding ordered timelines per user and stitching events into multi-step paths on raw data was costly, and complex journeys needed repeated scans and heavy I/O.
• Trino has stateless workers, so each funnel step pulled and shuffled large amounts of event data again. Multi-step funnels meant complex joins, large intermediate results and shuffles across workers, which could cause compute spikes and out-of-memory errors on workers for long timelines or big cohorts.

The solution

The team tested whether StarRocks could serve funnels and journey analysis straight from raw events. They chose it for its tightly coupled MPP execution, columnar storage with vectorized pipelines for scans and joins, materialized view rewrite to speed up multi-step funnel logic, index pruning and caching for repeated queries, and its focus on interactive analytics with normalized schemas, complex joins and high concurrency.

Hybrid query architecture

StarRocks runs as a shared-nothing cluster, and queries are split into two kinds:

1. Filter queries back the quick dashboard interactions: dropdowns, funnel selection and date ranges. They read internal StarRocks tables, into which a selected subset of data is synced on a schedule.
2. Data (fetch) queries do the heavier reads of player-level or aggregated metrics from the Delta Lake catalog. Results stay fresh, and the growing datasets are not copied or fully synced.

Filters get speed from local tables, data queries get freshness from the lake, and compute and storage scale separately.

Setup

StarRocks is deployed on Kubernetes with Helm, so FE and BE nodes scale independently and upgrades roll out cleanly. A Delta Lake catalog backed by AWS Glue Metastore lets StarRocks read the Parquet data on S3 directly, with no ETL step or extra copy:

CREATE EXTERNAL CATALOG delta
PROPERTIES (
    "type" = "deltalake",
    "hive.metastore.type" = "glue",
    "aws.glue.region" = "us-west-2",
    "aws.s3.region" = "us-west-2"
);

Periodic LOAD LABEL jobs bring selected partitions from S3 into internal tables for the low-latency filters:

LOAD LABEL filters_table_20251105 (
  DATA INFILE ("s3://playsimple/.../data/*.parquet")
  INTO TABLE filters_table
  FORMAT AS "parquet"
  SET (__op = 'upsert')
)
WITH BROKER ("aws.s3.region" = "us-west-2");

Optimizations

1. Asynchronous materialized views refresh when base tables change, which lowered filter query latency.
2. Result cache returns repeated queries faster.
3. Parallelism tuning matched query threads to machine cores through pipeline_dop and parallel_fragment_exec_instance_num, improving concurrency and join speed.
4. A single-flight pattern in the APIs blocks identical concurrent queries, which saves compute.

The team says that together these changes made the heavy analytical endpoints much more responsive.

The results

From manual SQL to one-click funnels

Before StarRocks, a funnel analysis meant writing custom SQL, joining several tables and combining the output in Sheets. Analysts now open templated, self-serve dashboards and explore a funnel in one click. Query setup, which took about 30 to 60 minutes per funnel, is gone, and end-to-end funnel load time dropped from about an hour to about two minutes.

Benchmark against Trino

On the team's funnel and journey workloads, StarRocks was faster than Trino on 6 of 8 queries, with the largest gain on Query 8 (121.4 s to 20.19 s). The two slower queries were small scans, where Trino's lower overhead gives it an edge.

Trade-offs

The team lists three costs of adding the engine:

• Operations: running StarRocks on Kubernetes requires FE/BE tuning, Helm upgrades and orchestration of materialized view refreshes. Most of it is automated, but it is ongoing work.
• Cost: selective table duplication, refresh compute and operational effort are new costs compared with the previous stack, and the initial setup and tuning took real engineering time.
• Cold starts: after refreshes or compactions, materialized views and internal tablets stay cold for a while. Until the segment, page and MV caches fill again, the first query can be slower because of full tablet scans or shuffle-heavy joins.

The resulting split: Trino serves broad, cross-source queries, Druid serves fast aggregate charts and dashboards, and StarRocks serves interactive, player-level exploration.

What's next

PlaySimple is extending StarRocks to support experiment analysis with dynamic inclusion and exclusion logic, and to build custom user cohorts for behavioral or geographic segmentation, so product, analytics and data science teams can ask more detailed questions of the same raw events.