Data Engineering for Gaming: Telemetry, Sessions, KPIs at Scale

gaming data engineering is the single most under-documented specialisation in the data-platform world in 2026 — and the one where a senior interviewer separates candidates fastest because gaming ships numbers other verticals almost never touch. A mid-tier live-service game emits 100 million events per day, per title; a AAA MMO clears a billion; a tournament spike pushes that to ten times the baseline for a weekend. Every one of those events flows through a client SDK on a phone, a console, or a PC, gets stitched to a player-id that spans three devices, joins a session that closed thirty minutes after the last action, and rolls up into a DAU / MAU / ARPDAU dashboard that live-ops reads hourly to decide whether the new item store shipped correctly.

This guide is the senior-DE playbook you wished existed the first time a game-studio interviewer asked "how would you build the telemetry pipeline for a live-service game shipping a 100M-event firehose?" or "how do you sessionize player events without over-splitting a break-for-lunch?" It walks through the four hard problems that make game telemetry distinct — the ingestion firehose with exactly-once contracts on currency events (Unity / Unreal → collector → Kafka → Flink → Iceberg with Avro schema evolution), sessionization gaming with gap-based windowing and cross-device player-id stitching (deterministic-plus-probabilistic device graphs, D1/D7/D30 retention curves, Kaplan-Meier LTV), the live-ops data KPI cadence (DAU / MAU / stickiness / ARPU / ARPPU / ARPDAU updated hourly, retention heatmaps, real-time Redis sorted-set leaderboards, A/B test analysis for game balance), and the integrity layer that a shipping studio can never skip (server-authoritative event validation, Z-score anomaly detection on KDR, Isolation Forest for outlier accounts, reputation graphs, and GDPR right-to-erasure across sharded telemetry via Iceberg equality deletes). Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on window-functions medium problems →, and sharpen the aggregation axis with the aggregation drills →.

---

On this page

• Why gaming DE differs in 2026
• Telemetry ingestion at scale
• Sessionization + player journeys
• Live-ops KPIs + dashboards
• Anti-cheat + integrity pipelines
• Cheat sheet — gaming DE recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why gaming DE differs in 2026

Gaming is a telemetry firehose with a live-ops feedback loop — the deployment shape and the KPI cadence are the two axes senior interviewers probe first

The one-sentence invariant: a live-service game emits 100M+ events per day per title, expects hourly-cadence live-ops KPIs, and demands exactly-once semantics on currency events — three constraints no other vertical stacks together. Fintech has EOS but not the volume; ad-tech has the volume but not the currency; e-commerce has both at lower cadence. Gaming is the only vertical where you routinely serve a KPI dashboard that live-ops uses to hot-patch the game economy within the hour.

Three axes interviewers actually probe.

• Event scale. A mid-tier mobile game clears 100M events/day. A AAA MMO clears 1B+. A tournament spike is 10x the baseline for 48 hours — the pipeline has to survive without over-provisioning the other 363 days. This is why gaming DE teams pick Kafka + Flink (elastic operator parallelism) over batch-only stacks and why they always design for burst-mode ingestion.
• Session model. A gaming session is not a web session — it lasts anywhere from 90 seconds (mobile hyper-casual) to 4 hours (MMO raid). The 30-minute-gap heuristic web analytics uses is close, but the interesting engineering is cross-device stitching (a player who starts on console, checks the companion app on mobile, then plays on PC needs one player_id across all three streams).
• KPI cadence. DAU / MAU / stickiness / ARPDAU are refreshed hourly, not daily. A live-ops director needs to see whether the new event boss dropped correctly within an hour of the deploy, not the next morning. This forces the pipeline into a Lambda / Kappa shape with a hot path for real-time and a cold path for the source of truth.

The 2026 reality — what changed since 2022.

• Client SDK maturity. Unity Analytics, Unreal Insights, GameAnalytics, Amplitude, mParticle, and Adjust all ship offline-first SDKs that batch-and-retry on reconnect. Server-side collectors are cheap; the client is where the interesting engineering happens (payload size, retry backoff, offline buffer eviction).
• Iceberg as the game warehouse. Snowflake / BigQuery are still common for the analytical layer, but Apache Iceberg on S3/GCS is now the default for the raw event store because of row-level GDPR deletes, schema evolution guarantees, and the ability to serve the same tables to both Trino (ad-hoc) and Flink (streaming).
• Flink Session Windows + Table API. Flink 1.18/2.0 stabilised session-window semantics and made the Table API the default for streaming SQL. Gap-based sessionization that used to require a windowed UDAF is now a one-liner.
• Redis sorted sets for real-time leaderboards. Every shipping live-service game has one Redis cluster dedicated to ZADD / ZRANGEBYSCORE for leaderboards, arena rankings, and event-boss health bars. The KV layer is a specific interview probe.
• GDPR + COPPA at scale. The 2018 GDPR wave forced everyone to build right-to-erasure; the 2023 COPPA enforcement wave forced everyone to build kid-safe telemetry carveouts (no PII, no ad-id, no device fingerprint on accounts flagged under-13).

What interviewers listen for.

• Do you say "100M events / day / title, exactly-once on currency, hourly KPI cadence" in the first sentence? — senior signal.
• Do you describe sessions as "gap-based, 30-min, cross-device stitched by player_id"? — required answer.
• Do you mention "stickiness = DAU/MAU, benchmark 20% good, 40% great" unprompted? — senior signal.
• Do you push back on "just batch it nightly" with "live-ops needs hourly refresh, tournament spikes are 10x"? — senior signal.

Worked example — sizing the telemetry firehose

Detailed explanation. Before you pick tools, you size the firehose. A 5M-DAU mobile game where each session emits 200 events (level_start, level_end, item_purchase, ad_impression, ad_click, movement_beacons) drives a specific Kafka partition count, a specific Flink parallelism, and a specific Iceberg partition strategy. The calculation is the first thing a senior interviewer asks.

Question. Given a live-service mobile game with 5M DAU, average 3 sessions/player/day, and 200 events/session, size the daily and peak event volumes and pick a Kafka partition count and Flink parallelism.

Input.

Assumption	Value
DAU	5,000,000
Sessions per player per day	3
Events per session	200
Peak-hour multiplier	4x average
Target Kafka partition throughput	~5K events/sec/partition
Tournament spike multiplier	10x

Code.

DAU = 5_000_000
sessions_per_player = 3
events_per_session = 200
peak_multiplier = 4
tournament_multiplier = 10

daily_events = DAU * sessions_per_player * events_per_session
avg_events_per_sec = daily_events / 86_400
peak_events_per_sec = avg_events_per_sec * peak_multiplier
tournament_events_per_sec = peak_events_per_sec * tournament_multiplier

target_partition_throughput = 5_000
partitions_needed_peak = -(-int(peak_events_per_sec) // target_partition_throughput)
partitions_needed_tournament = -(-int(tournament_events_per_sec) // target_partition_throughput)

print(f"daily events         = {daily_events:,}")
print(f"avg events/sec       = {avg_events_per_sec:,.0f}")
print(f"peak events/sec      = {peak_events_per_sec:,.0f}")
print(f"tournament events/s  = {tournament_events_per_sec:,.0f}")
print(f"partitions (peak)    = {partitions_needed_peak}")
print(f"partitions (tourney) = {partitions_needed_tournament}")

Step-by-step explanation.

1. daily_events = 5M * 3 * 200 = 3B events/day — this is the 24-hour firehose average across all events emitted by all sessions.
2. avg_events_per_sec = 3B / 86_400 ~= 34,722 — the flat 24-hour rate; useless on its own because traffic is bursty.
3. peak_events_per_sec = 34_722 * 4 ~= 138,888 — the observed peak-hour multiplier for a mobile game is typically 3-5x the flat rate; four is a safe planning number for evening prime-time.
4. tournament_events_per_sec = 138_888 * 10 ~= 1.39M — the tournament weekend spike; must be sustainable without cluster re-scaling.
5. partitions_needed = ceil(rate / 5000) — Kafka's rule-of-thumb per-partition write throughput. Peak needs 28 partitions; tournament needs 278. You size for the tournament number, not the peak.

Output.

Metric	Value
Daily events	3,000,000,000
Avg events/sec	34,722
Peak events/sec	138,888
Tournament events/sec	1,388,888
Kafka partitions (peak)	28
Kafka partitions (tournament)	278
Flink source parallelism	278
Flink processing parallelism	64 (bursty aggregation)

Rule of thumb. Size the Kafka partition count for the tournament spike, not the flat average. Repartitioning a live topic is a multi-day operation; over-provisioning by 3-5x on day one costs pennies in cloud storage and buys years of headroom.

Worked example — hourly KPI cadence forces Kappa

Detailed explanation. A live-ops director asks for DAU / MAU / ARPDAU refreshed every hour so the team can hot-patch the game economy the moment the new store drops. A nightly batch pipeline cannot serve this; the aggregation must run continuously on the stream. This forces the platform into a Kappa architecture with the streaming pipeline as the source of truth.

Question. Given a "DAU updated hourly, MAU updated hourly, ARPDAU updated hourly" requirement, show why nightly batch does not work and design the Flink Table API job that produces the hourly rollups.

Input.

KPI	Refresh interval
DAU	hourly rolling 24h
MAU	hourly rolling 30d
ARPDAU	hourly rolling 24h
Stickiness	derived from DAU/MAU

Code.

-- Flink SQL — Table API job that materialises hourly KPIs on a continuous stream
CREATE TABLE events (
    player_id STRING,
    event_time TIMESTAMP(3),
    event_type STRING,
    revenue_cents BIGINT,
    WATERMARK FOR event_time AS event_time - INTERVAL '5' MINUTE
) WITH ('connector' = 'kafka', 'topic' = 'game.events.v1', ...);

-- Continuous hourly DAU rollup (rolling 24h window)
CREATE VIEW dau_hourly AS
SELECT
    window_start,
    window_end,
    COUNT(DISTINCT player_id) AS dau,
    SUM(revenue_cents) / 100.0 AS revenue_usd,
    SUM(revenue_cents) / 100.0 / COUNT(DISTINCT player_id) AS arpdau
FROM TABLE(HOP(TABLE events, DESCRIPTOR(event_time),
               INTERVAL '1' HOUR, INTERVAL '24' HOUR))
GROUP BY window_start, window_end;

Step-by-step explanation.

1. WATERMARK FOR event_time AS event_time - INTERVAL '5' MINUTE tells Flink that events may arrive up to 5 minutes late; the watermark drives window closing.
2. HOP(TABLE events, ..., INTERVAL '1' HOUR, INTERVAL '24' HOUR) opens a hop-window of size 24h that slides forward every 1h. Every hour, Flink emits the DAU for the last 24 hours, giving a rolling metric.
3. COUNT(DISTINCT player_id) is expensive; Flink's HyperLogLog approximation (APPROX_COUNT_DISTINCT) is the production choice for a 5M-player game — <1% error and 1000x less state.
4. revenue_cents is stored as integer cents to avoid floating-point rounding bugs across billions of events. Divide by 100.0 only at presentation time.
5. The view emits one row per hour per closing window; the sink is an Iceberg table kpis_hourly that Superset / Metabase / Grafana query directly.

Output.

window_start	window_end	dau	revenue_usd	arpdau
2026-07-17 08:00	2026-07-18 08:00	1,193,842	214,891.20	0.180
2026-07-17 09:00	2026-07-18 09:00	1,201,517	218,442.55	0.182
2026-07-17 10:00	2026-07-18 10:00	1,209,884	221,981.10	0.184

Rule of thumb. Use APPROX_COUNT_DISTINCT for DAU / MAU on any table with > 1M distinct players. The <1% error is invisible on a live-ops dashboard and the state footprint is 1000x smaller — the difference between a 10 GB Flink checkpoint and a 10 MB one.

Senior interview question on gaming DE stack choice

A senior interviewer often opens with: "You're joining a game studio that ships a live-service mobile game with 3M DAU. Walk me through the data platform you would design — the ingestion pipeline, the sessionization step, the KPI dashboard cadence, and the anti-cheat layer. What are the non-negotiables and what are the trade-offs?"

Solution Using the 4-layer gaming DE reference architecture

Reference architecture — live-service game data platform
========================================================

Layer 1: Ingestion
  Client SDK (Unity / Unreal / native) with offline-first batch-retry
    → collector service (Amplitude / GameAnalytics / self-hosted Kong)
    → Kafka (partition count sized for tournament spike, not peak)
    → schema registered in Confluent Schema Registry (Avro, additive-only)

Layer 2: Stream processing
  Flink jobs (Table API + DataStream API)
    - sessionization (gap-based, 30-min gap, cross-device stitching)
    - hourly KPI rollups (DAU / MAU / stickiness / ARPDAU)
    - real-time anomaly scoring for anti-cheat
  All jobs use RocksDB state backend + incremental checkpoints to S3

Layer 3: Storage
  Iceberg on S3 for raw events (partitioned by dt + hour)
  Iceberg for hourly KPIs (partitioned by hour)
  Redis sorted sets for real-time leaderboards
  Postgres for player profiles (source of truth for player_id)

Layer 4: Serving
  Trino for ad-hoc analytics on Iceberg
  Superset / Metabase / Looker for dashboards
  Feature store (Feast / Tecton) for ML anti-cheat scoring
  Real-time API (gRPC) for leaderboards, matchmaking hints

Step-by-step trace.

Concern	Choice	Why
Ingestion protocol	Kafka	elastic partition scaling, EOS support
Schema format	Avro + Schema Registry	additive evolution, compact wire format
Stream engine	Flink	operator-parallel scaling, session windows
Raw storage	Iceberg on S3	row-level GDPR delete, schema evolution
Leaderboards	Redis ZSET	O(log N) rank queries
KPI cadence	hourly rolling	live-ops feedback loop
Anti-cheat	Flink + Feast	real-time features, offline retraining

After the 4-layer pass, every downstream question (which BI tool, which orchestrator, which observability stack) is a smaller choice within an already-locked architecture. The four layers are the frame.

Output:

Layer	Non-negotiable	Common trade-off
Ingestion	offline-first SDK + Avro schema registry	vendor collector vs self-hosted
Stream	Flink for sub-second sessionization	Kafka Streams if platform team is small
Storage	Iceberg for GDPR + Flink + Trino unified	Delta Lake if already on Databricks
Serving	Redis for leaderboards, Trino for ad-hoc	one BI tool vs several

Why this works — concept by concept:

• Layered architecture — separating ingestion, stream processing, storage, and serving lets each team own one contract. The stream team does not need to know the BI tool; the BI team does not need to know Kafka partitioning. Layered design is the frame that survives 5+ years of platform evolution.
• Tournament-sized ingestion — the Kafka partition count sizing is the choice you cannot easily undo. Sizing for the 10x tournament spike (not the 4x peak) costs ~5x cloud pennies on partition metadata but avoids a multi-day emergency repartition when a tournament actually lands.
• Iceberg as the shared table — Iceberg is the table format that lets Flink writers and Trino readers share the same physical files. No dual-write, no CDC-back-into-warehouse pipeline. This is the single biggest platform-simplification lever available in 2026.
• Redis for the hot path, Iceberg for the source of truth — leaderboards, rankings, matchmaking hints go to Redis (10ms lookups). The source of truth lives in Iceberg (GDPR-safe, replayable). Never let Redis be a source of truth for anything that needs GDPR-erasure.
• Cost — the platform is O(events/sec) throughout; the cost lives in Kafka partition count × replication factor + Flink slot-hours + Iceberg storage. A 3M-DAU game runs on ~$30-50K/month cloud infra; anything more and you're over-provisioned.

SQL
Topic — aggregation
Aggregation problems for live-ops KPIs

Practice →

SQL Topic — SQL SQL practice for gaming analytics

Practice →

---

2. Telemetry ingestion at scale

game telemetry ingestion is client-SDK-to-Iceberg with exactly-once on currency events — the schema-registry contract is the load-bearing wall

The mental model in one line: a game emits events from a client SDK on a phone or console, batches them with offline-first retry, ships them through a collector to Kafka, streams them through a Flink job into Iceberg, and enforces exactly-once semantics on the subset of events that touch in-game currency. Once you internalise "SDK → collector → Kafka → Flink → Iceberg with Avro schema registry and EOS on currency," every game analytics pipeline interview probe collapses to a deduction from those five hops.

The five hops.

• Client SDK. Unity Analytics, Unreal Insights, GameAnalytics, Amplitude, or mParticle sit inside the game binary. They batch events into 100-1000 record payloads, retry with exponential backoff on network failure, and persist an offline buffer on the device (SQLite, typically) to survive app kills.
• Collector. A stateless HTTPS endpoint (self-hosted Kong / Envoy, or vendor-hosted). Validates the API key, decorates with received_at server timestamp, and forwards to Kafka. Never trust client timestamps for anything security-sensitive.
• Kafka. The event bus. Partitioned by player_id for co-locality of downstream sessionization. min.in.sync.replicas=2, replication.factor=3, compression.type=zstd are the boring-but-correct defaults.
• Flink. Streams events into Iceberg via the FlinkSink connector. For events that touch currency (item_purchase, iap_verified, gold_awarded), the sink is configured with TwoPhaseCommitSinkFunction and the Iceberg table has row-level EOS guarantees.
• Iceberg. The raw event store on S3. Partitioned by dt (date) and event_type for pruning. Schema evolution is additive-only — new fields are appended, existing fields never change type.

Schema evolution — the additive-only rule.

• Every event has a schema registered in Confluent Schema Registry (or Karapace / Apicurio). The producer serialises with the registered schema; the consumer deserialises with the same or a forward-compatible schema.
• Additive-only rule: you can add a new field with a default value. You cannot remove a field, rename a field, or change a field's type. Every violation is a downstream break.
• Nullable defaults: new fields ship with null as the default so old consumers keep working. Deprecating a field means marking it deprecated: true in the schema doc and keeping it in the wire format for the deprecation window (typically 3 months).
• Field naming: use snake_case, prefix event-type-specific fields with the event type (purchase_amount_cents not amount_cents on the item_purchase event) so joins across event types are unambiguous.

Exactly-once for currency events.

• A currency event (item_purchase, iap_verified, gold_spent, gem_awarded) must land in Iceberg exactly once. Double-counting a purchase is a support ticket; missing one is a fraud investigation.
• The producer side uses Kafka's transactional producer — enable.idempotence=true and transactional.id pinned to the collector instance.
• The Flink sink is configured with TwoPhaseCommitSinkFunction and the Iceberg commit is atomic with the checkpoint completion.
• Non-currency events (movement beacons, ad impressions, tutorial steps) can safely run at at-least-once with idempotent inserts (dedupe on event_id), because the volume is 100x higher and the EOS overhead is not worth paying.

Backpressure — the mobile SDK's job.

• The SDK batches events into 100-1000 records or a 30-second interval, whichever comes first. On network failure, the batch is retained in the SQLite buffer with an exponential-backoff retry (1s, 5s, 30s, 5min).
• The buffer has a size cap (typically 5-10MB). When the cap is hit, oldest low-priority events (movement beacons, telemetry heartbeats) are evicted first; high-priority events (purchases, level completions) are retained.
• On reconnect, the SDK drains the buffer in batches to avoid a thundering herd against the collector. A small random jitter (0-30s) at reconnect prevents thousands of returning players from stampeding the collector at the same instant.

Cheat detection at the ingestion layer.

• Every event is signed with a client-derived HMAC that the collector validates. Failed signatures are logged and dropped.
• The collector enriches every event with the server received_at timestamp; a client event_time more than 5 minutes older than received_at triggers a suspicion flag.
• Currency events are never trusted from the client — a gold_awarded event only counts if the game server also emitted a matching gold_awarded_server_auth event within a 30-second correlation window.

Common interview probes on ingestion.

• "How do you handle offline players?" — SQLite buffer + exponential retry + priority eviction.
• "How do you evolve the schema safely?" — additive-only, nullable defaults, Schema Registry with forward-compat mode.
• "Which events need exactly-once?" — currency events only; the rest run at-least-once with idempotent inserts.
• "How do you partition the Kafka topic?" — by player_id for downstream sessionization co-locality.

Worked example — offline-first SDK batching

Detailed explanation. A player boards a plane; the SDK keeps buffering events locally. When the plane lands and Wi-Fi reconnects, the buffered events are flushed to the collector. The engineering challenge is: don't lose events, don't stampede the collector, and don't blow up the client's disk.

Question. Design the client-SDK batching and retry logic in pseudo-code. Show the batch trigger, the retry backoff, the buffer eviction rule, and the reconnect jitter.

Input.

Config	Value
batch_max_records	500
batch_max_interval	30 seconds
offline_buffer_max_bytes	10 MB
retry_intervals	1s, 5s, 30s, 5min
reconnect_jitter_max	30s

Code.

class GameTelemetrySDK:
    def __init__(self, config):
        self.buffer = SQLiteBuffer(max_bytes=10 * 1024 * 1024)
        self.batch = []
        self.retry_intervals = [1, 5, 30, 300]
        self.last_send_time = time.time()

    def track(self, event: dict) -> None:
        event["event_id"] = str(uuid.uuid4())
        event["client_time"] = time.time()
        self.batch.append(event)
        if len(self.batch) >= 500 or (time.time() - self.last_send_time) >= 30:
            self._flush()

    def _flush(self) -> None:
        if not self.batch:
            return
        payload = self.batch
        self.batch = []
        try:
            self._send_with_retry(payload)
        except NetworkError:
            self._persist_to_buffer(payload)
        self.last_send_time = time.time()

    def _send_with_retry(self, payload: list) -> None:
        for backoff in self.retry_intervals:
            try:
                collector.post("/events", payload)
                return
            except NetworkError:
                time.sleep(backoff)
        raise NetworkError()

    def on_reconnect(self) -> None:
        time.sleep(random.uniform(0, 30))          # jitter
        for chunk in self.buffer.drain_in_batches(500):
            self._send_with_retry(chunk)

    def _persist_to_buffer(self, payload: list) -> None:
        if self.buffer.would_overflow(payload):
            self.buffer.evict_lowest_priority()
        self.buffer.append(payload)

Step-by-step explanation.

1. track(event) stamps the event with a UUID and a client timestamp, then appends to the in-memory batch.
2. The batch is flushed when it hits 500 records or when 30 seconds have elapsed since the last flush — whichever comes first.
3. _send_with_retry walks the exponential-backoff intervals (1s, 5s, 30s, 5min). If all four fail, the payload is persisted to the SQLite offline buffer.
4. On network reconnect, on_reconnect sleeps a random 0-30s (jitter) so a million returning players do not stampede the collector at the same instant, then drains the buffer in 500-record batches.
5. If the buffer would overflow, evict_lowest_priority drops movement beacons and telemetry heartbeats first, retaining purchases and level completions. The player never loses money-related events.

Output.

Scenario	Behaviour
500 events in 5s	flush immediately
10 events in 60s	flush at 30s mark
network dies mid-flush	retry 1s, 5s, 30s, 5min; then buffer
buffer full, purchase arrives	evict a movement beacon to make room
1M players reconnect at same t	staggered flush across 30s window

Rule of thumb. Never fire an event more than once from the SDK unless the network confirms failure. Never accept a client timestamp for anything security-relevant. Always jitter reconnect drains — the collector cluster's max QPS is the real bottleneck.

Worked example — Avro schema evolution for a new event field

Detailed explanation. The team wants to add a campaign_id field to the item_purchase event so they can attribute purchases to a specific in-game promo. Old game clients (players who have not updated) will keep emitting events without the field. The pipeline must accept both and never break.

Question. Write the Avro schema for item_purchase v1 (no campaign) and v2 (with campaign, nullable default), and show what breaks if you make the field non-nullable.

Input.

Version	Fields	Compatibility
v1	player_id, sku, price_cents, event_time	shipping today
v2	player_id, sku, price_cents, event_time, campaign_id	tomorrow's release

Code.

{
    "type": "record",
    "name": "ItemPurchase",
    "namespace": "com.game.events",
    "fields": [
        {"name": "player_id", "type": "string"},
        {"name": "sku", "type": "string"},
        {"name": "price_cents", "type": "long"},
        {"name": "event_time", "type": {"type": "long", "logicalType": "timestamp-millis"}},
        {"name": "campaign_id", "type": ["null", "string"], "default": null}
    ]
}

# Register with Schema Registry in forward-compatible mode
curl -X PUT http://schema-registry:8081/config/game.events.item_purchase-value \
    -H "Content-Type: application/vnd.schemaregistry.v1+json" \
    -d '{"compatibility": "FORWARD"}'

curl -X POST http://schema-registry:8081/subjects/game.events.item_purchase-value/versions \
    -H "Content-Type: application/vnd.schemaregistry.v1+json" \
    -d @item_purchase_v2.json

Step-by-step explanation.

1. campaign_id is declared as a union ["null", "string"] with "default": null. This is the additive-only nullable-default pattern that keeps forward compatibility.
2. FORWARD compatibility means: consumers using v1 can read data produced with v2. The v1 consumer ignores the unknown campaign_id field; the v2 consumer sees null when reading old data.
3. If you had declared {"name": "campaign_id", "type": "string"} (no null, no default), the Schema Registry would reject the v2 schema in FORWARD mode because old v1 producers do not emit the field, and v2 consumers would fail with a missing-required-field error.
4. The logicalType: timestamp-millis gives Avro a portable timestamp representation that Iceberg reads natively as a timestamp column — no downstream cast needed.
5. When the v2 client releases, it starts sending campaign_id; when v1 clients drain (weeks later), the field is universally present. No pipeline downtime, no consumer rewrites.

Output.

Producer version	Consumer version	Reads
v1	v1	fine
v1	v2	fine (campaign_id = null)
v2	v1	fine (ignores campaign_id)
v2	v2	fine (campaign_id present)

Rule of thumb. Every new field is nullable with a null default. Every field removal goes through a 3-month deprecation window (mark in schema doc, keep in wire format, then remove). Never change a field's type — add a new field and deprecate the old one.

Worked example — Flink EOS sink for currency events

Detailed explanation. The item_purchase topic must land in the purchases Iceberg table exactly once. Duplicates would double-count revenue; misses would trigger a support ticket. Flink's TwoPhaseCommitSinkFunction on the Iceberg connector is the standard pattern.

Question. Write the Flink SQL that streams item_purchase events from Kafka into an Iceberg table with exactly-once semantics. Show the config knobs that enforce EOS.

Input.

Piece	Value
Source topic	game.events.item_purchase
Sink table	warehouse.purchases
Checkpoint interval	60 seconds
Checkpoint storage	s3://flink/checkpoints

Code.

-- Flink SQL with EOS on the Iceberg sink
SET 'execution.checkpointing.interval' = '60s';
SET 'execution.checkpointing.mode' = 'EXACTLY_ONCE';
SET 'state.checkpoint-storage' = 'filesystem';
SET 'state.checkpoints.dir' = 's3://flink/checkpoints/purchases';

CREATE TABLE kafka_purchases (
    player_id STRING,
    sku STRING,
    price_cents BIGINT,
    event_time TIMESTAMP(3),
    campaign_id STRING,
    WATERMARK FOR event_time AS event_time - INTERVAL '5' MINUTE
) WITH (
    'connector'         = 'kafka',
    'topic'             = 'game.events.item_purchase',
    'properties.bootstrap.servers' = 'kafka:9092',
    'format'            = 'avro-confluent',
    'scan.startup.mode' = 'group-offsets'
);

CREATE TABLE iceberg_purchases (
    player_id STRING,
    sku STRING,
    price_cents BIGINT,
    event_time TIMESTAMP(3),
    campaign_id STRING
) PARTITIONED BY (event_time) WITH (
    'connector' = 'iceberg',
    'catalog-name' = 'glue',
    'catalog-database' = 'warehouse',
    'catalog-table' = 'purchases',
    'write.upsert.enabled' = 'false',
    'write.distribution-mode' = 'hash'
);

INSERT INTO iceberg_purchases
SELECT player_id, sku, price_cents, event_time, campaign_id
FROM kafka_purchases;

Step-by-step explanation.

1. execution.checkpointing.mode = EXACTLY_ONCE tells Flink to snapshot state atomically with sink commits. Combined with the Iceberg sink's TwoPhaseCommitSinkFunction, this gives end-to-end EOS.
2. The Kafka source uses avro-confluent format so it deserialises against the Schema Registry directly; new fields land in the correct Iceberg column automatically.
3. The Iceberg sink is PARTITIONED BY (event_time), which Iceberg interprets as a day-truncate partition transform by default. Trino queries pruning by date see only the relevant partitions.
4. write.distribution-mode = 'hash' hashes rows by partition key on the Flink side before writing, avoiding a small-files problem on the Iceberg side. Without it, each Flink subtask writes into every partition, producing many tiny files.
5. The INSERT INTO ... SELECT runs as a continuous streaming job — every 60s checkpoint boundary, Flink pre-commits the buffered Iceberg files, then commits the Iceberg metadata pointer atomically on checkpoint completion.

Output.

Config	Value	Effect
checkpointing.mode	EXACTLY_ONCE	atomic state + sink commit
checkpointing.interval	60s	commit cadence (visibility latency)
distribution-mode	hash	one file per subtask per partition
upsert.enabled	false	append-only (purchases never change)

Rule of thumb. EOS on Flink + Iceberg is a set of four config knobs, not a bespoke code path. Set them once per job and never think about it again. For non-currency events at 100x volume, drop EOS in favour of at-least-once + event_id dedupe; the operational overhead is not worth paying.

Senior interview question on end-to-end ingestion

A senior interviewer might ask: "Design the end-to-end ingestion pipeline for a live-service game shipping 500M events/day. Walk me through the SDK, the collector, the Kafka topology, the Flink job, and the Iceberg storage. Where does exactly-once apply and where does at-least-once?"

Solution Using the SDK → collector → Kafka → Flink → Iceberg reference pipeline

End-to-end ingestion — 500M events/day live-service game
========================================================

Step 1 — Client SDK (Unity)
  - Batch: 500 events or 30s, whichever first
  - Offline buffer: 10 MB SQLite, priority eviction
  - Retry: 1s, 5s, 30s, 5min exponential backoff
  - Reconnect jitter: 0-30s to avoid stampede

Step 2 — Collector (self-hosted Kong)
  - HTTPS termination, API-key validation
  - Enriches with server received_at
  - HMAC signature verify (drop failed)
  - Publishes to Kafka with acks=all, EOS on currency

Step 3 — Kafka topology
  - game.events.item_purchase       (60 partitions, EOS)
  - game.events.iap_verified        (24 partitions, EOS)
  - game.events.movement_beacon     (500 partitions, at-least-once)
  - game.events.match_end           (60 partitions, at-least-once)
  - All: replication.factor=3, min.isr=2, zstd

Step 4 — Flink jobs
  - currency-to-iceberg    (EXACTLY_ONCE, 60s checkpoint)
  - beacons-to-iceberg     (AT_LEAST_ONCE, 5min checkpoint)
  - sessionization         (30-min gap, keyed by player_id)
  - hourly-kpis            (HOP window 24h / 1h)

Step 5 — Iceberg on S3
  - warehouse.purchases    (partitioned by dt, hourly compaction)
  - warehouse.beacons      (partitioned by dt + event_type)
  - warehouse.sessions     (partitioned by dt)
  - warehouse.kpis_hourly  (partitioned by hour)

Step-by-step trace.

Stage	Volume	EOS?	State	Storage cost
SDK batching	500M events/day	idempotent send	10 MB SQLite	client disk
Collector	5,787 events/sec avg	pass-through	stateless	negligible
Kafka currency	~1M events/day	EOS	60 partitions × 3 replicas	~1 GB/day
Kafka beacons	499M events/day	ALO	500 partitions × 3 replicas	~500 GB/day
Flink currency job	~15 events/sec	EOS	RocksDB + S3 CP	~1 GB state
Flink beacon job	~5.8K events/sec	ALO	RocksDB + S3 CP	~5 GB state
Iceberg raw	500M rows/day	strong	S3 + Glue metastore	~50 GB/day (zstd)

The trace shows that EOS costs are concentrated in the currency-events path (~0.2% of volume). The bulk-events path runs at-least-once with idempotent inserts, saving the transactional overhead on the 99.8% of events that don't touch money.

Output:

Concern	Currency path	Bulk path
EOS mode	EXACTLY_ONCE	AT_LEAST_ONCE + event_id dedupe
Kafka acks	all	1
Flink checkpoint interval	60s	5min
Sink	Iceberg TPC	Iceberg append
Compaction cadence	hourly	6-hourly
Reader freshness	60s + hourly compact	5min + 6h compact

Why this works — concept by concept:

• Tiered EOS by event class — only currency events (~0.2% of volume) pay the transactional overhead. Bulk events (movement beacons, telemetry heartbeats) run at-least-once with idempotent inserts, saving ~5x checkpoint overhead on the hot path. This is the single most important lever for keeping streaming costs in line at 500M events/day.
• Client-side offline buffer — SQLite on the device with priority eviction ensures purchases never drop even when a player boards a plane mid-transaction. The 10 MB cap keeps disk usage predictable; priority-based eviction keeps the high-value events alive.
• HMAC + server timestamp — cheat detection starts at ingestion. HMAC signatures make forged events discardable at the collector; server received_at timestamps make late-arriving-forged events flag-able downstream.
• Partition count per event type — currency events (rare, high-value) get 60 partitions; movement beacons (frequent, low-value) get 500. Sizing per event type is the honest answer to Kafka's cost model.
• Cost — the pipeline is O(events) throughout. The dominant cost is Iceberg storage (~50 GB/day compressed at 500M events/day) and Kafka retention (default 7 days = ~3.5 TB). Cutting non-currency Kafka retention to 3 days saves ~40% on Kafka disk without harming replay.

SQL
Topic — joins
Join problems for telemetry pipelines

Practice →

SQL Topic — aggregation · medium Medium aggregation drills

Practice →

---

3. Sessionization + player journeys

sessionization gaming is gap-based windowing over a player-id stitched across devices — the SQL is a LAG-and-flag pattern that every senior DE should be able to write in 60 seconds

The mental model in one line: a gaming session is a run of events for a single player where consecutive events are ≤ 30 minutes apart; the SQL is LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time) combined with a running sum flag; and the player_id is stitched across devices via a deterministic-plus-probabilistic device graph. Once you internalise "gap → new session, running sum → session_id, device graph → player_id," the entire player behavior data interview surface collapses to a deduction from those three ideas.

The 30-minute gap heuristic.

• Why 30 minutes. Two players' events arriving within 30 minutes are almost certainly the same session; two events > 30 minutes apart are almost certainly separate sessions. Empirically calibrated across web, mobile, and console analytics for 15+ years.
• When to override. Mobile hyper-casual games sometimes use 15 minutes (short sessions); MMOs sometimes use 60 minutes (long raid sessions with AFK periods). The gap is a per-title tunable; 30 minutes is the default.
• Session end detection. The last event of a session is implicit — you only know a session has ended when a new event arrives after > 30 minutes. Real-time systems finalise sessions with a Flink session-window timer that fires 30 minutes after the last event.

Cross-device player-id stitching.

• Deterministic stitching. Every logged-in player carries a player_id that is stable across devices. Console login, mobile companion-app login, web-storefront login → all three emit events tagged with the same player_id. This is the golden path.
• Probabilistic stitching. For anonymous / guest sessions, use a device graph — same IP + same city + same device-model timestamps within a session window → likely same player. Never expose probabilistic stitches to the player (privacy) but do use them for engagement analytics.
• Merge-on-login. When a guest session becomes logged-in mid-session, emit a player_id_merge event that rewrites the earlier events' player_id in the sessionized table. Never lose the guest events.

Funnels between game modes.

• A funnel is a strict ordering of events per player: tutorial → PvE → PvP → ranked. Each drop-off is a percentage of the players who reached the previous step but not this one.
• SQL pattern: MIN(CASE WHEN event = 'tutorial_complete' THEN event_time END) over (PARTITION BY player_id), then check downstream MIN(...) values > tutorial time.
• Interviewers love funnels because they test window functions, MIN(CASE WHEN ...), self-joins, and business-logic edge cases (player skips a step, replays a step).

Retention curves — D1 / D7 / D30.

• D1 retention = fraction of players from cohort_day who came back on cohort_day + 1. Industry benchmark: 40% is excellent, 25% is average, < 15% is a red flag.
• D7 retention = fraction of players from cohort_day who came back on cohort_day + 7. Benchmark: 15% excellent, 8% average.
• D30 retention = fraction of players from cohort_day who came back on cohort_day + 30. Benchmark: 8% excellent, 3% average.
• Retention heatmap — a grid with cohort_day on the Y axis and days_since_install on the X axis; the cell colour encodes the retention percentage. This is the single most-scrutinised chart in a live-ops review.

Cohort analysis by acquisition source.

• Every player has an acquisition_source (organic, paid_facebook, paid_google, referral, cross-promo) captured at install.
• Cohort tables slice retention, ARPU, and LTV by acquisition source so marketing can decide which channels justify their CAC.
• The typical finding: paid-social players have higher D1 but lower D30; organic players have the inverse profile.

LTV modelling with Kaplan-Meier survival.

• LTV = sum of expected revenue over a player's lifetime. The naive approach — extrapolate from D30 ARPU — overstates because it ignores churn.
• The Kaplan-Meier estimator gives the survival function S(t) = P(player still active at time t). LTV = Σ ARPDAU(t) × S(t) over all t.
• For a 3M-DAU game with 40% D1, 15% D7, 8% D30 retention, typical LTV lands in the $2-$10 range for hyper-casual and $20-$100 for mid-core.

Churn prediction feature engineering.

• Recency — days since last session. The single strongest churn signal.
• Session count over last 7 days — engagement trajectory.
• Purchase recency — days since last purchase (payers vs non-payers behave differently).
• Session length trend — decreasing session length precedes churn by 3-5 days.
• Social features — friend count, guild membership, chat activity. Social players churn far less.

Common interview probes on sessionization.

• "How would you compute session_id in SQL?" — LAG + gap flag + running sum.
• "How do you handle cross-device sessions?" — stitch on player_id (deterministic) or device graph (probabilistic).
• "What is the 30-minute gap heuristic?" — the industry-standard session boundary; tunable per title.
• "How do you compute D7 retention in SQL?" — DISTINCT players in cohort × DISTINCT players active on cohort + 7 / cohort size.

Worked example — SQL sessionization with LAG + running sum

Detailed explanation. The canonical SQL pattern for sessionization: use LAG(event_time) to find the previous event's timestamp per player, flag any gap > 30 minutes as a "new session marker," then compute the session_id as a running sum of markers. Two window functions, one CTE, one query.

Question. Given an events(player_id, event_time) table, assign a session_id to each event using the 30-minute gap heuristic.

Input.

player_id	event_time
p1	2026-07-17 12:00:00
p1	2026-07-17 12:05:00
p1	2026-07-17 12:20:00
p1	2026-07-17 13:15:00
p1	2026-07-17 13:18:00
p2	2026-07-17 12:10:00

Code.

WITH tagged AS (
    SELECT
        player_id,
        event_time,
        LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time) AS prev_time,
        CASE
            WHEN LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time) IS NULL
                 OR event_time - LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time)
                    > INTERVAL '30 minutes'
            THEN 1
            ELSE 0
        END AS is_new_session
    FROM events
)
SELECT
    player_id,
    event_time,
    prev_time,
    SUM(is_new_session) OVER (PARTITION BY player_id ORDER BY event_time) AS session_id
FROM tagged
ORDER BY player_id, event_time;

Step-by-step explanation.

1. LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time) returns the previous event's timestamp for the same player — or NULL for the player's very first event.
2. is_new_session = 1 when the previous timestamp is NULL (first event) or the gap is > 30 minutes; otherwise 0.
3. SUM(is_new_session) OVER (PARTITION BY player_id ORDER BY event_time) computes a running sum of session markers — this becomes the session_id (1, 2, 3, ...) per player.
4. For player p1: events at 12:00, 12:05, 12:20 are session 1 (gaps 5min, 15min — under 30). Event at 13:15 has a 55-minute gap → session 2. Event at 13:18 is 3min after — same session 2.
5. For player p2: single event → session 1 (first event flag).

Output.

player_id	event_time	prev_time	session_id
p1	2026-07-17 12:00:00	NULL	1
p1	2026-07-17 12:05:00	2026-07-17 12:00:00	1
p1	2026-07-17 12:20:00	2026-07-17 12:05:00	1
p1	2026-07-17 13:15:00	2026-07-17 12:20:00	2
p1	2026-07-17 13:18:00	2026-07-17 13:15:00	2
p2	2026-07-17 12:10:00	NULL	1

Rule of thumb. The LAG-plus-running-sum pattern is the universal sessionization SQL — it works on Postgres, Snowflake, BigQuery, Trino, Redshift, DuckDB, and Flink SQL identically. Memorise it. Every gaming DE interview asks a variant of this within 15 minutes.

Worked example — Flink session window for real-time sessionization

Detailed explanation. SQL sessionization runs on the batch Iceberg table. For real-time sessionization (session ended → emit session-summary row), Flink's session-window operator does the same computation on the live stream, keyed by player_id, with a 30-minute gap timer.

Question. Write the Flink SQL that emits one row per closed session, containing session_id, start_time, end_time, and event_count.

Input.

Stream	Kafka topic
game.events (player_id, event_time, event_type)	60 partitions

Code.

CREATE TABLE events (
    player_id STRING,
    event_time TIMESTAMP(3),
    event_type STRING,
    WATERMARK FOR event_time AS event_time - INTERVAL '2' MINUTE
) WITH ('connector' = 'kafka', 'topic' = 'game.events', ...);

-- Session window: 30-minute gap
CREATE VIEW sessions AS
SELECT
    player_id,
    SESSION_START(event_time, INTERVAL '30' MINUTE) AS session_start,
    SESSION_END(event_time, INTERVAL '30' MINUTE) AS session_end,
    COUNT(*) AS event_count,
    COUNT(DISTINCT event_type) AS distinct_event_types
FROM events
GROUP BY player_id, SESSION(event_time, INTERVAL '30' MINUTE);

INSERT INTO iceberg_sessions
SELECT * FROM sessions;

Step-by-step explanation.

1. WATERMARK FOR event_time AS event_time - INTERVAL '2' MINUTE allows events up to 2 minutes late; the watermark drives session-close firing.
2. SESSION(event_time, INTERVAL '30' MINUTE) is a Flink session-window function keyed implicitly by the GROUP BY (player_id). Every consecutive-event gap ≤ 30 minutes stays in the same session; a gap > 30 minutes closes it.
3. SESSION_START and SESSION_END are helper functions that return the earliest and latest event_time in the session — the session's active window.
4. When the watermark advances past session_end + 30 minutes (no more events could arrive for this session), Flink emits one output row for the closed session and clears the state.
5. The output is a stream of session-summary records, sinkable to Iceberg for downstream retention / funnel / LTV analysis.

Output (example session outputs).

player_id	session_start	session_end	event_count	distinct_event_types
p1	2026-07-17 12:00	2026-07-17 12:20	3	2
p1	2026-07-17 13:15	2026-07-17 13:18	2	1
p2	2026-07-17 12:10	2026-07-17 12:10	1	1

Rule of thumb. Use SQL sessionization on Iceberg for the source of truth (replayable, reproducible). Use Flink session windows for real-time enrichment (feature store, matchmaking, live-ops dashboards). Never rely on Flink's output as the source of truth — always reconcile against the Iceberg batch pipeline.

Worked example — cross-device player-id stitching

Detailed explanation. A player logs in on console at 9:00, then on the mobile companion app at 9:30, then on the PC client at 10:00. All three devices emit events. The pipeline must present all these events as one player's journey — but only when the player_id matches; when it doesn't, fall back to a probabilistic device-graph match.

Question. Given events tagged with device_id and (optionally) player_id, write the SQL that resolves every event to a unified resolved_player_id using deterministic-then-probabilistic stitching.

Input — events.

event_id	device_id	player_id (login)	ip	event_time
e1	d_console_1	p1	10.0.0.5	09:00
e2	d_mobile_9	p1	10.0.0.5	09:30
e3	d_pc_2	NULL	10.0.0.5	10:00
e4	d_pc_2	p1	10.0.0.5	10:15

Code.

-- Step 1: deterministic mapping (device → player) from any logged-in event
WITH device_to_player AS (
    SELECT
        device_id,
        player_id,
        MIN(event_time) AS first_seen
    FROM events
    WHERE player_id IS NOT NULL
    GROUP BY device_id, player_id
),

-- Step 2: probabilistic fallback (device → likely player via IP + session window)
device_ip_window AS (
    SELECT
        e.device_id,
        e.ip,
        DATE_TRUNC('hour', e.event_time) AS bucket
    FROM events e
    WHERE e.player_id IS NULL
    GROUP BY 1, 2, 3
),
prob_map AS (
    SELECT DISTINCT
        d.device_id,
        FIRST_VALUE(dp.player_id) OVER (
            PARTITION BY d.device_id ORDER BY dp.first_seen
        ) AS likely_player_id
    FROM device_ip_window d
    JOIN device_to_player dp ON d.ip = SPLIT_PART(dp.device_id, '_', 2)  -- illustrative
),

-- Step 3: resolve
resolved AS (
    SELECT
        e.event_id,
        e.device_id,
        e.event_time,
        COALESCE(e.player_id, dp.player_id, pm.likely_player_id) AS resolved_player_id
    FROM events e
    LEFT JOIN device_to_player dp ON e.device_id = dp.device_id
    LEFT JOIN prob_map pm ON e.device_id = pm.device_id
)
SELECT * FROM resolved ORDER BY event_time;

Step-by-step explanation.

1. device_to_player builds the deterministic map: for every device that ever hosted a logged-in event, associate it with the player_id and the first-seen time. One row per (device, player) pair.
2. device_ip_window and prob_map build the probabilistic fallback: for events with NULL player_id, group by device+IP+hour and find the most-likely player_id via IP co-occurrence within the same hour bucket.
3. resolved joins the events to both maps with COALESCE: use the explicit player_id from the event if present; else the deterministic device map; else the probabilistic map.
4. Event e3 (NULL player_id on d_pc_2 at 10:00) resolves via the deterministic map because event e4 later logs in on the same device — the map assigns d_pc_2 → p1.
5. If a device never has a logged-in event, the probabilistic fallback kicks in based on IP and hour bucket. In production, use a proper device-graph library (a graph database or a Bayesian model), not raw SQL — this example illustrates the pattern.

Output.

event_id	device_id	event_time	resolved_player_id
e1	d_console_1	09:00	p1
e2	d_mobile_9	09:30	p1
e3	d_pc_2	10:00	p1
e4	d_pc_2	10:15	p1

Rule of thumb. Always prefer deterministic stitching (logged-in player_id) over probabilistic. Use probabilistic only for engagement analytics, never for anything player-facing (leaderboards, rankings, matchmaking). Expose the stitched resolved_player_id in a separate column, keep the raw player_id untouched for audit.

Senior interview question on sessionization at scale

A senior interviewer might ask: "Design the sessionization pipeline for a live-service game with 5M DAU. Show how you'd do it both in Flink (real-time) and in Iceberg SQL (batch reconciliation). Where would you keep state, and what's your policy when the two pipelines disagree?"

Solution Using dual-pipeline sessionization + Iceberg as source of truth

Dual-pipeline sessionization — 5M DAU live-service game
========================================================

Real-time path (Flink)
  - Flink session window (30-min gap, keyed by player_id)
  - State backend: RocksDB + incremental S3 checkpoints
  - Output: game.sessions.rt topic + feature-store push
  - SLA: session-close event visible < 60s after last event
  - Consumer: live-ops dashboard, matchmaking, feature store

Batch reconciliation path (Iceberg SQL)
  - LAG + running-sum sessionization over 24h of raw events
  - Runs hourly via Airflow / Dagster
  - Output: warehouse.sessions (Iceberg, partitioned by dt)
  - SLA: source of truth, correct within 5min of the hour boundary
  - Consumer: retention, funnel, LTV, all analytical BI

Reconciliation policy
  - Batch is always right when the two disagree
  - Real-time is monitored for drift > 0.5% via a nightly reconciliation job
  - Drift alerts to on-call; drift > 2% is a P1 incident

Step-by-step trace.

Stage	Real-time (Flink)	Batch (Iceberg SQL)
Session assignment	Flink session window	LAG + running sum
Cross-device stitching	Feature store lookup on player_id	deterministic + probabilistic SQL
Late events (> 5 min)	Dropped after watermark	Included in next hour's batch
Output cadence	On session close (~30 min after last event)	Hourly
Storage	Kafka + Iceberg (10-min compact)	Iceberg (hourly compact)
Truth	approximate	source of truth
SLA	60s p95	5 min after hour boundary

The trace shows why gaming DE teams almost always ship both paths: real-time is required for live-ops and matchmaking; batch is required for correctness (late events, cross-device stitching corrections, backfill). Every disagreement is a bug; the batch pipeline wins.

Output:

Consumer	Reads from
Live-ops dashboard (real-time)	Flink session output topic
Retention / LTV / cohort BI	Iceberg batch sessions
Matchmaking	Feature store (Flink-fed)
ML anti-cheat training	Iceberg batch sessions
Ad-hoc analyst queries	Iceberg batch sessions

Why this works — concept by concept:

• Dual pipeline separates SLA from correctness — real-time gets a 60-second visibility SLA but sacrifices correctness on late events. Batch gets a 5-minute-after-hour SLA and full correctness. Neither pipeline compromises the other's contract.
• Iceberg as the source of truth — the batch pipeline writes to an Iceberg table that all analytical readers query. When the real-time and batch disagree, the batch wins and the real-time drift is monitored. This is the safest single-writer pattern for a shared table.
• 30-min gap heuristic — the tunable that a per-title playbook adjusts. Hyper-casual games use 15 minutes; MMOs use 60. The choice affects DAU / session-length / retention headline numbers, so log the value in your data dictionary.
• Cross-device stitching precedence — deterministic (login) always wins over probabilistic (device graph). Never expose the probabilistic layer to the player; use it only for engagement analytics.
• Cost — Flink session state is O(open sessions × avg events per session); batch is O(events × 24h). For a 5M-DAU game with 3 sessions per player per day, real-time state peaks at ~5M open sessions × 200 events × 100 bytes = ~100 GB. That's a 10-slot RocksDB backend at typical settings — well within reach.

SQL
Topic — window-functions
Window-function problems for sessionization

Practice →

SQL Topic — window-functions · medium Medium window-function drills

Practice →

---

4. Live-ops KPIs + dashboards

game KPI dashboard cadence is hourly, not daily — DAU / MAU / stickiness / ARPDAU are the four numbers a live-ops director asks about first, and the SQL is a HyperLogLog + revenue-sum away

The mental model in one line: live-ops KPIs are computed hourly on the sessionized event stream — DAU (distinct players today), MAU (distinct players last 30d), stickiness = DAU/MAU, ARPU / ARPPU / ARPDAU (revenue per unit), retention heatmap, funnel drop-off, real-time leaderboard, A/B analysis — and each metric maps to a specific SQL pattern that a senior gaming DE ships in an hour. Once you internalise "four core KPIs plus the leaderboard and A/B side-cars," the entire live-ops interview surface collapses to a sequence of SQL patterns.

The four core KPIs.

• DAU (Daily Active Users). Distinct players who had at least one session today. Computed as COUNT(DISTINCT player_id) over a rolling 24h window.
• MAU (Monthly Active Users). Distinct players who had at least one session in the last 30 days. Computed as COUNT(DISTINCT player_id) over a rolling 30d window.
• Stickiness = DAU/MAU. How often the average monthly-active player is a daily-active player. Industry benchmarks: 20% is good, 30% is great, 40% is exceptional (only the biggest live-service hits sustain 40%).
• ARPDAU (Average Revenue Per DAU). Total daily revenue / DAU. This is the headline monetisation metric — it collapses paying and non-paying players into one number that lets live-ops compare economy changes over time.

Revenue variants.

• ARPU (Average Revenue Per User). Revenue / total registered players. Low but stable; used for LTV calculations.
• ARPPU (Average Revenue Per Paying User). Revenue / distinct payers. Much higher; measures the quality of the payer cohort.
• ARPDAU. Revenue / DAU. The hybrid used for hourly live-ops.
• LTV (Lifetime Value). Sum of expected revenue over a player's lifetime; computed via Kaplan-Meier survival × ARPDAU integration.

Retention heatmap.

• A 2D grid: rows are cohort_day (install day), columns are days_since_install. The cell value is the retention percentage: fraction of the cohort that was active on install_day + N.
• A typical heatmap shows a diagonal decay from the top-left (100% on day 0) down to the bottom-right (< 5% on day 30). Any horizontal band that departs from the diagonal is a signal (an event, a bug, a marketing push).
• SQL pattern: cohort table (player_id, install_date) LEFT JOIN daily activity (player_id, active_date), then GROUP BY (install_date, active_date - install_date) with COUNT / cohort_size.

Funnel drop-off with self-joins.

• A funnel has an ordered list of milestones: install → tutorial_complete → first_purchase → level_10 → level_50 → ranked_match.
• SQL pattern: for each player, MIN(event_time) per milestone, then compute the fraction who reached step N+1 among those who reached step N.
• Interviewers love funnels because they combine window functions, MIN(CASE WHEN ...), and business-logic subtleties (out-of-order events, replays).

Real-time leaderboard with Redis sorted sets.

• Redis ZADD leaderboard <score> <player_id> inserts / updates a player's score in O(log N).
• Redis ZREVRANGEBYSCORE leaderboard +inf -inf LIMIT 0 10 returns the top-10 in O(log N + K).
• The Flink job that computes scores tails the events topic, updates a per-player score in state, and issues ZADD on every score change.
• Persistence: the Redis ZSET is a hot cache; the source of truth is an Iceberg scores table written on every score update.

A/B test analysis for game balance.

• Every player is assigned to a variant at install (or at feature-flag flip time). The assignment lives in a feature_exposures table.
• The analysis SQL joins events to exposures, computes the KPI per variant (ARPDAU, D7 retention, session length), and reports the lift.
• Statistical significance uses a two-sample t-test or a bootstrap; the industry-standard threshold is p < 0.05 with a minimum of 1000 players per variant.

Common interview probes on live-ops KPIs.

• "How do you compute DAU?" — COUNT(DISTINCT player_id) over the last 24h; use HLL for large tables.
• "What is stickiness?" — DAU/MAU; benchmark 20% good, 30% great.
• "How would you build a real-time leaderboard?" — Redis sorted set with ZADD + ZREVRANGE.
• "How do you A/B test a game balance change?" — assign variant at install, join to events, compute lift, t-test.

Worked example — DAU / MAU / stickiness SQL

Detailed explanation. The three headline live-service metrics, computed in one SQL query with two COUNT(DISTINCT) and a divide. Every gaming DE writes this within 5 minutes; the interview signal is whether you also mention HyperLogLog for scale.

Question. Given a sessions(player_id, session_start) table, compute DAU, MAU, and stickiness for a given date.

Input.

player_id	session_start
p1	2026-07-17 10:00
p2	2026-07-17 11:00
p1	2026-06-20 14:00
p3	2026-06-18 15:00
p4	2026-06-15 09:00

Code.

WITH bounds AS (
    SELECT DATE '2026-07-17' AS report_date
),
kpis AS (
    SELECT
        b.report_date,
        COUNT(DISTINCT CASE
            WHEN s.session_start >= b.report_date
             AND s.session_start <  b.report_date + INTERVAL '1 day'
            THEN s.player_id
        END) AS dau,
        COUNT(DISTINCT CASE
            WHEN s.session_start >= b.report_date - INTERVAL '30 days'
             AND s.session_start <  b.report_date + INTERVAL '1 day'
            THEN s.player_id
        END) AS mau
    FROM sessions s
    CROSS JOIN bounds b
    GROUP BY b.report_date
)
SELECT
    report_date,
    dau,
    mau,
    ROUND(100.0 * dau / NULLIF(mau, 0), 2) AS stickiness_pct
FROM kpis;

-- At scale: swap COUNT(DISTINCT ...) for APPROX_COUNT_DISTINCT(...)
-- HyperLogLog gives < 1% error, 1000x less memory.

Step-by-step explanation.

1. bounds pins the report date so the query is deterministic. In production, parameterise this or run it as a scheduled job.
2. The CASE WHEN inside COUNT(DISTINCT ...) filters to the 24h window for DAU and the 30d window for MAU — both computed in one pass over the sessions table.
3. NULLIF(mau, 0) prevents divide-by-zero on empty date ranges (edge case for a fresh game).
4. For any game with > 1M players, replace COUNT(DISTINCT ...) with APPROX_COUNT_DISTINCT(...) — Presto / Trino / BigQuery / Spark all support HLL. Error is < 1% and memory is 1000x smaller.
5. The stickiness percentage is the killer number — it collapses "we have 5M MAU but only 800K daily" into "16% stickiness — below industry average, ship engagement features."

Output.

report_date	dau	mau	stickiness_pct
2026-07-17	2	4	50.00

Rule of thumb. Always express stickiness as a percentage with 2 decimal places — live-ops directors track it week-over-week and want to see the delta. For hourly cadence, run the same query with report_hour instead of report_date and use HLL for the distinct counts.

Worked example — ARPDAU and revenue-per-cohort

Detailed explanation. ARPDAU is Total Revenue / DAU. The trap is that if you divide daily revenue by a rolling-24h DAU on hourly cadence, the numerator and denominator have different time windows. Always align them.

Question. Compute ARPDAU per day for the last 30 days, then split it by acquisition source.

Input — sessions.

player_id	session_start
p1	2026-07-17 10:00
p2	2026-07-17 11:00

Input — purchases.

player_id	event_time	revenue_cents
p1	2026-07-17 10:15	499
p2	2026-07-17 11:30	999

Input — players.

player_id	acquisition_source
p1	paid_facebook
p2	organic

Code.

WITH daily_dau AS (
    SELECT
        DATE(session_start) AS report_date,
        COUNT(DISTINCT player_id) AS dau
    FROM sessions
    WHERE session_start >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 1
),
daily_revenue AS (
    SELECT
        DATE(event_time) AS report_date,
        SUM(revenue_cents) / 100.0 AS revenue_usd
    FROM purchases
    WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 1
),
arpdau AS (
    SELECT
        d.report_date,
        d.dau,
        COALESCE(r.revenue_usd, 0) AS revenue_usd,
        ROUND(COALESCE(r.revenue_usd, 0) / NULLIF(d.dau, 0), 4) AS arpdau
    FROM daily_dau d
    LEFT JOIN daily_revenue r ON d.report_date = r.report_date
),
-- Split ARPDAU by acquisition source
arpdau_by_source AS (
    SELECT
        DATE(s.session_start) AS report_date,
        p.acquisition_source,
        COUNT(DISTINCT s.player_id) AS dau_src,
        COALESCE(SUM(pu.revenue_cents) / 100.0, 0) AS revenue_src,
        ROUND(COALESCE(SUM(pu.revenue_cents) / 100.0, 0)
              / NULLIF(COUNT(DISTINCT s.player_id), 0), 4) AS arpdau_src
    FROM sessions s
    JOIN players p ON s.player_id = p.player_id
    LEFT JOIN purchases pu ON s.player_id = pu.player_id
                          AND DATE(pu.event_time) = DATE(s.session_start)
    WHERE s.session_start >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 1, 2
)
SELECT * FROM arpdau_by_source ORDER BY report_date DESC, arpdau_src DESC;

Step-by-step explanation.

1. daily_dau computes distinct players per day. daily_revenue sums per day. Both restrict to the last 30 days to bound the query cost.
2. arpdau joins the two by date and computes revenue_usd / dau. NULLIF guards against divide-by-zero on days with 0 players.
3. arpdau_by_source does the same computation but splits by acquisition_source. The join to players brings in the source; the join to purchases on the same day gives per-source revenue.
4. The LEFT JOIN on purchases ensures we don't drop days where a source has no revenue (0 rather than NULL).
5. The output sorts by date descending, then by ARPDAU descending — the most-recent, best-performing source floats to the top.

Output.

report_date	acquisition_source	dau_src	revenue_src	arpdau_src
2026-07-17	organic	1	9.99	9.9900
2026-07-17	paid_facebook	1	4.99	4.9900

Rule of thumb. Always split ARPDAU by acquisition source — the aggregate hides that paid-social has 3x lower ARPDAU than organic, which is the single most-actionable insight marketing teams get. Store revenue as integer cents everywhere and convert to dollars only at presentation.

Worked example — cohort retention heatmap

Detailed explanation. Cohort retention is a two-dimensional aggregation: rows are install cohorts, columns are days-since-install, cells are retention percentages. The SQL is a self-join + GROUP BY, or a PIVOT on modern SQL dialects.

Question. Given players(player_id, install_date) and sessions(player_id, session_date), compute the retention percentage for the D1, D3, D7, D14, D30 columns.

Input — players.

player_id	install_date
p1	2026-06-01
p2	2026-06-01
p3	2026-06-01

Input — sessions.

player_id	session_date
p1	2026-06-02
p2	2026-06-08
p3	2026-06-01
p3	2026-06-15

Code.

WITH cohort AS (
    SELECT install_date, COUNT(DISTINCT player_id) AS cohort_size
    FROM players
    GROUP BY install_date
),
activity AS (
    SELECT
        p.install_date,
        s.session_date - p.install_date AS days_since_install,
        COUNT(DISTINCT s.player_id) AS active_players
    FROM players p
    JOIN sessions s ON p.player_id = s.player_id
                    AND s.session_date >= p.install_date
    GROUP BY 1, 2
)
SELECT
    c.install_date,
    c.cohort_size,
    ROUND(100.0 * MAX(CASE WHEN a.days_since_install = 1  THEN a.active_players END) / c.cohort_size, 2) AS d1,
    ROUND(100.0 * MAX(CASE WHEN a.days_since_install = 3  THEN a.active_players END) / c.cohort_size, 2) AS d3,
    ROUND(100.0 * MAX(CASE WHEN a.days_since_install = 7  THEN a.active_players END) / c.cohort_size, 2) AS d7,
    ROUND(100.0 * MAX(CASE WHEN a.days_since_install = 14 THEN a.active_players END) / c.cohort_size, 2) AS d14,
    ROUND(100.0 * MAX(CASE WHEN a.days_since_install = 30 THEN a.active_players END) / c.cohort_size, 2) AS d30
FROM cohort c
LEFT JOIN activity a ON c.install_date = a.install_date
GROUP BY c.install_date, c.cohort_size
ORDER BY c.install_date;

Step-by-step explanation.

1. cohort computes the size of each install-day cohort — the denominator for every retention percentage.
2. activity computes, for each cohort, how many distinct players were active on each day-since-install offset.
3. The main SELECT pivots by hard-coding the milestone columns (D1, D3, D7, D14, D30). On modern SQL dialects, use PIVOT for a cleaner query.
4. For the 3-player cohort of 2026-06-01: p1 was active D1 (1/3 = 33%), p2 was active D7 (1/3 = 33%), p3 was active D0 and D14 (1/3 = 33%).
5. ROUND(100.0 * ... / cohort_size, 2) gives a percentage with 2 decimal places — the standard live-ops presentation.

Output.

install_date	cohort_size	d1	d3	d7	d14	d30
2026-06-01	3	33.33	NULL	33.33	33.33	NULL

Rule of thumb. For any game with > 100 cohorts, materialise the retention grid as an Iceberg table refreshed daily rather than computing it on every dashboard load. The heatmap chart pulls the same grid unchanged; the query is fast enough for BI tools.

Senior interview question on live-ops KPI pipelines

A senior interviewer might ask: "You're the lead DE on a live-ops team. The product director wants DAU, MAU, ARPDAU, D1/D7/D30 retention, top-10 leaderboard, and the current A/B test scorecard — all in one dashboard, all refreshed hourly. Design the pipeline."

Solution Using hourly Iceberg rollups + Redis leaderboard + A/B exposure join

Hourly live-ops KPI dashboard — pipeline
========================================

Batch (hourly) — Iceberg rollups
  Job 1: sessions_hourly (from raw events, sessionized)
    - one row per player per session close
    - materialised into warehouse.sessions

  Job 2: kpis_hourly (DAU / MAU / ARPDAU / stickiness)
    - APPROX_COUNT_DISTINCT for scale
    - materialised into warehouse.kpis_hourly

  Job 3: retention_grid (cohort × days_since_install)
    - refresh cadence: hourly, but the grid only changes daily
    - materialised into warehouse.retention_grid

Real-time — Flink + Redis
  Job 4: score-to-redis
    - tails game.events, updates per-player score in Flink state
    - issues ZADD leaderboard <score> <player_id> on every change
    - Redis SLA: 10ms p99 ZREVRANGE

A/B testing — analytical
  Job 5: feature-exposures join
    - joins events to feature_exposures at query time
    - Superset dashboard runs it hourly against Iceberg
    - t-test on ARPDAU lift per variant

Serving
  - Superset dashboards read warehouse.kpis_hourly + retention_grid
  - Leaderboard widget queries Redis directly
  - A/B scorecard runs the exposure join at dashboard-load time

Step-by-step trace.

Panel	Data source	Refresh	Latency
DAU tile	warehouse.kpis_hourly	hourly	< 1 min after hour
MAU tile	warehouse.kpis_hourly	hourly	< 1 min after hour
Stickiness tile	derived from DAU/MAU	hourly	< 1 min after hour
ARPDAU tile	warehouse.kpis_hourly	hourly	< 1 min after hour
Retention heatmap	warehouse.retention_grid	daily (hourly refresh no-op)	< 1 min after refresh
Top-10 leaderboard	Redis ZREVRANGE	real-time	10ms p99
A/B scorecard	Iceberg join at query time	on dashboard load	2-5s

The trace shows the tiering: high-cardinality hot data (leaderboard) goes to Redis; time-series aggregates (KPIs, retention) go to Iceberg; ad-hoc slices (A/B scorecard) run on-demand against Iceberg. Each tier serves the latency SLA it needs, no more.

Output:

Layer	Tech	Freshness	Cost driver
Real-time leaderboard	Redis Sorted Set	< 10ms	RAM per player
Hourly KPIs	Iceberg + Trino	< 1 min after hour	Flink slots + storage
Retention grid	Iceberg + Trino	< 5 min after refresh	daily batch compute
A/B scorecard	Iceberg + Trino	on-demand	ad-hoc scan cost

Why this works — concept by concept:

• Tiered latency budgets — leaderboards need 10 ms; KPI tiles need 1 minute; retention grids need 5 minutes; A/B scorecards need 5 seconds. Each tier's tech matches its latency requirement so no tier over-pays.
• Iceberg for everything analytical — one storage format for KPIs, retention, A/B, cohort analysis, LTV. This is the leverage that makes gaming DE tractable at scale — no CDC, no dual-write, no synthesis of BI-tool-specific caches.
• Redis is a cache, not the source of truth — the leaderboard ZSET is rebuildable from the Iceberg scores table on cluster loss. Never let a Redis ZSET be the only copy of a player's rank — that's a support ticket waiting to happen.
• HLL for DAU / MAU at scale — APPROX_COUNT_DISTINCT is the difference between a 10-GB checkpoint and a 10-MB checkpoint. For any distinct-player metric above 1M cardinality, HLL is the default.
• Cost — the pipeline is O(events) for ingestion, O(players) for cohort tables, and O(players × features) for A/B analysis. For a 5M-DAU game, the entire live-ops KPI layer runs on ~10 Flink slots + a 3-node Redis cluster + Iceberg storage — measurable in low-thousands of USD per month.

SQL
Topic — aggregation
Aggregation problems for KPI dashboards

Practice →

SQL Topic — window-functions Window-function drills for cohort retention

Practice →

---

5. Anti-cheat + integrity pipelines

matchmaking data pipeline and anti-cheat share the same infra — server-authoritative events, Z-score outliers, Isolation Forest scoring, reputation graphs, and Iceberg row-level GDPR erasure

The mental model in one line: every anti-cheat pipeline starts by making the server the source of truth for security-relevant events, then layers statistical anomaly detection (Z-score on KDR) plus ML-based outlier scoring (Isolation Forest across multi-feature vectors) plus a reputation graph, and closes the loop with WORM audit storage and GDPR row-level erasure so enforcement decisions are legally defensible and player-privacy-safe. Once you internalise "server-auth first, then stats, then ML, then reputation, then audit," every cheat-detection interview probe becomes a layer selection.

Server-authoritative event validation.

• Every security-relevant event (currency changes, PvP results, level completions) is emitted by the game server, not the client. Client-side events are hints; server events are truth.
• Cross-validation: for each client gold_awarded event, the pipeline joins to a matching gold_awarded_server_auth event within a 30-second correlation window. Unmatched client events are logged as suspicious and dropped from downstream aggregates.
• HMAC signatures on client events discard forged payloads at the collector. Server events are signed with a rotating server-side key.

Statistical anomaly detection — Z-score on KDR.

• KDR (Kill/Death Ratio) is the single most-cheat-visible metric. A player with KDR = 15 in a game where the 99th percentile is 3 is either a genius, a hacker, or a smurf.
• Compute mean(KDR) and stddev(KDR) per matchmaking tier over the last 30 days. For a new match, compute z = (KDR - mean) / stddev. Flag |z| > 3 for review.
• Extension: use per-weapon, per-map, per-tier baselines instead of global. Precision improves 5-10x; false positives drop.

Isolation Forest for outlier accounts.

• KDR alone catches obvious aim-hackers. To catch subtler cheaters (bots, wall-hackers, network manipulation), use a multi-feature outlier model on: KDR, headshot rate, movement velocity, aim-jitter, network-loss rate, wallhack-probability, time-of-day distribution.
• IsolationForest from scikit-learn scores each account on how easy it is to "isolate" from the majority in feature space. Score < -0.5 → probable outlier.
• Retrain weekly on the last 30 days; deploy the model to Feast / Tecton for real-time scoring at match-end.

Shadowbans + reputation graphs.

• A shadowbanned account can play but is silently matched only with other shadowbanned accounts. This buys the anti-cheat team days of behavioural data before the cheater knows they've been caught.
• The reputation graph is a graph database (Neo4j / TigerGraph / DGraph) where nodes are players and edges are (trust, distrust, played-with) relationships. Distrust propagates: if you play with 3 known cheaters, your reputation drops.
• Graph algorithms (PageRank on the distrust subgraph) surface accounts that co-play with known cheaters even if their own KDR looks normal.

GDPR right-to-erasure across sharded telemetry.

• When a player invokes GDPR erasure, every PII-bearing row across every downstream Iceberg table must be deleted or anonymised.
• Iceberg supports equality deletes — a delete file that says "delete all rows where player_id = X". The next compaction rewrites the data files, physically removing the rows.
• Pipeline: erasure request → central erasure queue → per-table Flink job that emits equality deletes → hourly Iceberg compaction physically purges.
• Never store PII in Redis (rebuild-only-from-Iceberg). Never store PII in unmutable WORM audit unless legally required (and then only hashed).

Evidence chain for enforcement decisions.

• Every enforcement action (ban, shadowban, currency rollback) is stored in an append-only WORM audit table with the evidence bundle (events, model scores, reviewer notes).
• The audit table is the legal record if a banned player disputes the decision. Never modify past rows; only append.
• Retain audit records per your legal / compliance policy (typically 2-7 years); rotate to Glacier for cold storage after 90 days.

Common interview probes on integrity.

• "How do you catch aim-hackers?" — Z-score on KDR per tier; flag |z| > 3.
• "How do you catch bot accounts?" — Isolation Forest on multi-feature vectors; distrust-propagation on the reputation graph.
• "How do you handle GDPR on a sharded event store?" — Iceberg equality deletes + compaction; central erasure queue with per-table workers.
• "What's the difference between a ban and a shadowban?" — ban is visible + immediate; shadowban is silent + gathers behavioural data.

Worked example — Z-score on KDR per tier

Detailed explanation. For each matchmaking tier, compute the last-30-days KDR mean and standard deviation. For a new match, compute the player's Z-score. Flag any match with |z| > 3 for human review.

Question. Given a matches(player_id, tier, kdr, played_at) table, compute the Z-score for every match in the last 24 hours relative to the last 30 days baseline for that tier.

Input.

player_id	tier	kdr	played_at
p1	gold	1.2	2026-07-17 10:00
p2	gold	1.4	2026-07-17 11:00
p3	gold	15.0	2026-07-17 12:00
...	gold	1.1-1.5 (baseline 30d)	...

Code.

WITH baseline AS (
    SELECT
        tier,
        AVG(kdr) AS mean_kdr,
        STDDEV_POP(kdr) AS std_kdr
    FROM matches
    WHERE played_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
      AND played_at <  CURRENT_TIMESTAMP - INTERVAL '1 day'
    GROUP BY tier
),
recent AS (
    SELECT player_id, tier, kdr, played_at
    FROM matches
    WHERE played_at >= CURRENT_TIMESTAMP - INTERVAL '1 day'
),
scored AS (
    SELECT
        r.player_id,
        r.tier,
        r.kdr,
        r.played_at,
        b.mean_kdr,
        b.std_kdr,
        ROUND((r.kdr - b.mean_kdr) / NULLIF(b.std_kdr, 0), 3) AS z_score
    FROM recent r
    JOIN baseline b ON r.tier = b.tier
)
SELECT *
FROM scored
WHERE ABS(z_score) > 3
ORDER BY ABS(z_score) DESC;

Step-by-step explanation.

1. baseline computes mean and stddev of KDR per tier over the previous 30 days (excluding the last 24h, so today's outliers don't contaminate the baseline).
2. recent is the last-24h matches — the candidates to score.
3. scored joins recent to baseline on tier and computes (kdr - mean) / stddev — the Z-score. NULLIF guards against zero standard deviation on new tiers.
4. The final filter ABS(z_score) > 3 returns only significant outliers — anything within 3σ of the mean is normal variance.
5. For player p3 in gold tier with KDR = 15 and baseline (mean = 1.3, stddev = 0.4), z = (15 - 1.3) / 0.4 = 34.25 → far above 3 → flagged.

Output.

player_id	tier	kdr	played_at	mean_kdr	std_kdr	z_score
p3	gold	15.0	2026-07-17 12:00	1.30	0.40	34.250

Rule of thumb. Compute Z-score per matchmaking tier, not globally. A KDR of 3 is normal in bronze and cheating in diamond. Tier-relative baselines cut false positives by 5-10x compared to a single global baseline.

Worked example — Isolation Forest for multi-feature outliers

Detailed explanation. Z-score works on one feature. Bots exhibit anomalies across many features simultaneously — perfect aim, no idle time, uniform movement patterns. Isolation Forest scores each account by how easy it is to isolate in feature space; low scores = probable outliers.

Question. Given a feature table player_features(player_id, kdr, headshot_rate, aim_jitter, movement_variance, session_regularity), train an Isolation Forest and score every account. Flag anomaly_score < -0.5.

Input.

player_id	kdr	headshot_rate	aim_jitter	movement_variance	session_regularity
p1	1.2	0.28	0.85	12.4	0.42
p2	1.5	0.32	0.90	15.1	0.38
p3	12.0	0.95	0.02	0.8	0.99
...	...	...	...	...	...

Code.

import pandas as pd
from sklearn.ensemble import IsolationForest

features = ["kdr", "headshot_rate", "aim_jitter",
            "movement_variance", "session_regularity"]

df = pd.read_parquet("s3://warehouse/player_features/dt=2026-07-17/")
X = df[features].values

model = IsolationForest(
    n_estimators=200,
    max_samples=256,
    contamination=0.01,        # expect 1% cheaters
    random_state=42,
)
model.fit(X)

df["anomaly_score"] = model.decision_function(X)
df["is_outlier"] = model.predict(X) == -1

flagged = df[df["anomaly_score"] < -0.5].sort_values("anomaly_score")
flagged.to_parquet("s3://warehouse/anticheat_flags/dt=2026-07-17/")
print(f"Flagged {len(flagged)} accounts out of {len(df)}")

Step-by-step explanation.

1. Load the daily feature snapshot from Iceberg (partitioned by dt). Every player has one row per day.
2. Build an IsolationForest with contamination=0.01 — assume 1% of the player base is cheating (a common baseline; tune per game). n_estimators=200 gives stable scores.
3. fit(X) trains on the feature matrix — no labels needed; unsupervised.
4. decision_function(X) returns the anomaly score — negative for outliers, positive for typical. predict(X) == -1 returns the boolean flag using the model's internal threshold.
5. Filter to anomaly_score < -0.5 for a stricter cut than the model default (fewer false positives). Sort ascending so the most-anomalous accounts float to the top of the review queue.

Output (illustrative — top 3 flagged).

player_id	kdr	headshot_rate	aim_jitter	anomaly_score
p3	12.0	0.95	0.02	-0.734
p47	9.4	0.88	0.05	-0.681
p112	6.7	0.79	0.08	-0.612

Rule of thumb. Retrain the Isolation Forest weekly on the last 30 days of features. Ship the model to the feature store so it scores real-time at match-end. Never auto-ban on model score alone — always human-review the top 100 daily; batch-ban only after the reputation graph confirms the cluster.

Worked example — GDPR row-level delete on Iceberg

Detailed explanation. A player invokes GDPR right-to-erasure. Every table that stores their PII must physically delete the rows. Iceberg's equality-delete + compaction pipeline is the standard mechanism.

Question. Show the Iceberg SQL to delete all rows for player_id = 'p_erasure_1' from warehouse.purchases and warehouse.sessions, then trigger compaction.

Input.

Table	PII columns
warehouse.purchases	player_id, ip, device_id, revenue_cents
warehouse.sessions	player_id, ip, device_id

Code.

-- Step 1: equality delete (Iceberg V2 spec)
DELETE FROM warehouse.purchases
WHERE player_id = 'p_erasure_1';

DELETE FROM warehouse.sessions
WHERE player_id = 'p_erasure_1';

-- Step 2: rewrite data files to physically remove the rows
-- (Spark / Trino / Flink Iceberg action call)
CALL warehouse.system.rewrite_data_files(
    table => 'warehouse.purchases',
    where => "dt >= DATE '2026-06-01'"
);

CALL warehouse.system.rewrite_data_files(
    table => 'warehouse.sessions',
    where => "dt >= DATE '2026-06-01'"
);

-- Step 3: expire old snapshots so the deleted rows are unreachable
CALL warehouse.system.expire_snapshots(
    table                => 'warehouse.purchases',
    older_than           => TIMESTAMP '2026-07-10 00:00:00',
    retain_last          => 5
);

Step-by-step explanation.

1. DELETE FROM ... WHERE player_id = ... in Iceberg V2 writes an equality delete file — a small metadata file that logically hides the matching rows from readers.
2. Readers filter the deleted rows out at scan time using the delete file. The physical data file still contains the row.
3. rewrite_data_files is the compaction call that rewrites the data files without the deleted rows. After this, the row is physically gone from the Parquet files.
4. expire_snapshots removes old table snapshots that still referenced the pre-deletion data files. Until snapshots expire, someone with time-travel access could read the pre-deletion state. For GDPR you must expire.
5. Run the erasure pipeline as a per-table Flink / Spark job triggered by a central erasure queue. Emit an audit entry per successful table wipe so compliance can prove the deletion.

Output.

Step	Effect	GDPR compliance
DELETE (equality delete file)	logical hide	partial
rewrite_data_files	physical removal from Parquet	strong
expire_snapshots	pre-deletion snapshots unreachable	complete

Rule of thumb. GDPR erasure is a three-step operation: delete, rewrite, expire. Every step is logged to the audit table so compliance can prove the request was honoured. Never let a GDPR pipeline skip the expire_snapshots step — the row is still readable via time-travel until you do.

Senior interview question on anti-cheat pipeline design

A senior interviewer might ask: "Design the end-to-end anti-cheat pipeline for a competitive shooter. Cover ingestion validation, statistical + ML detection, enforcement workflow, evidence chain, and GDPR compliance. How do you avoid false positives banning legit players?"

Solution Using layered detection + shadowban → review → enforcement pipeline

Anti-cheat pipeline — competitive shooter
==========================================

Layer 1: Ingestion validation
  - Server-authoritative events for currency, PvP results, level-ups
  - HMAC signature verification at collector
  - Server received_at timestamp; client event_time drift > 5min = suspicious flag

Layer 2: Statistical detection (real-time)
  - Z-score on KDR per tier per weapon over last 30d
  - Flink job scores every match; |z| > 3 → flag
  - Output: warehouse.anticheat_flags_stat

Layer 3: ML detection (batch, daily)
  - Isolation Forest on 12-feature vector per player per day
  - anomaly_score < -0.5 → flag
  - Output: warehouse.anticheat_flags_ml

Layer 4: Reputation graph (batch, weekly)
  - Graph DB nodes = players, edges = played-with, weighted by trust
  - PageRank on distrust subgraph
  - Low reputation → flag
  - Output: warehouse.anticheat_flags_reputation

Layer 5: Shadowban → review → enforcement
  - Union of L2 + L3 + L4 flags → shadowban queue
  - Shadowban: match only with other shadowbanned + gather 7 days of data
  - Human reviewer looks at replay + evidence bundle
  - Confirmed cheater: full ban + currency rollback + audit entry
  - False positive: unban + reputation restore + reviewer training feedback

Layer 6: Evidence chain + GDPR
  - Every enforcement action → WORM audit table
  - Evidence bundle: matches, features, model scores, replay pointer
  - Retention: 7 years per compliance policy
  - GDPR erasure: erasure request → Iceberg equality-delete pipeline
    (audit table PII stays as hash-only for legal defense)

Step-by-step trace.

Layer	Detection type	Cadence	Precision	Recall
L1 Ingestion validation	Signature + timestamp	real-time	100%	Only forged events
L2 Z-score	Statistical	real-time	Medium	High for obvious hackers
L3 Isolation Forest	ML unsupervised	daily batch	Medium-High	High for bots + subtle cheat
L4 Reputation graph	Graph algorithm	weekly	Medium	Complements L2/L3
L5 Shadowban → review	Human + gathered data	7 days	Very high (post-review)	Catches false negatives
L6 Audit + GDPR	Compliance	on action	100%	100%

The trace shows why single-layer anti-cheat always ships false positives: no one signal is precise enough to auto-ban. The shadowban + review workflow raises precision by using time to gather more data before the enforcement decision.

Output:

Concern	Answer
False positive rate	< 0.1% (post-review)
Time-to-ban for obvious cheaters	24-72 hours (shadowban → review)
Time-to-ban for subtle cheaters	1-2 weeks (reputation graph catches them)
Evidence for legal disputes	WORM audit + replay pointer
GDPR compliance	Iceberg equality-delete + snapshot expire

Why this works — concept by concept:

• Server-authoritative first — every downstream detection assumes truthful events. Without server-side validation, statistical detection is scoring the noise of forged data instead of real behaviour. This is the single most important architecture invariant.
• Layered detection — Z-score catches obvious aim-hackers; Isolation Forest catches bots and subtle cheaters; reputation graph catches co-play clusters. Each layer complements the others; no single layer is sufficient.
• Shadowban buys precision — auto-banning on model score creates false positives. Shadowbanning + gathering 7 more days of behavioural data raises the confidence before enforcement, cutting false-positive complaints by 10x.
• Evidence chain + GDPR co-exist — the audit table stores hashed PII (defensible in court) while the analytical tables honour GDPR erasure. Retention policy differs per table; compliance signs off on both.
• Cost — the anti-cheat pipeline is O(events) for statistical scoring, O(players) for ML scoring, O(players²) worst-case for graph analysis (usually O(players × avg-friends)). For a 5M-DAU shooter, the entire integrity pipeline runs on ~5 Flink slots + a small Neo4j cluster + daily batch ML — measurable in low-thousands of USD per month.

SQL
Topic — window-functions
Window-function drills for Z-score baselines

Practice →

SQL
Topic — joins · medium
Join drills for evidence-chain queries

Practice →

---

Cheat sheet — gaming DE recipes

• Session windowing SQL. LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time) + CASE WHEN diff > 30min THEN 1 ELSE 0 + SUM(...) OVER (PARTITION BY player_id ORDER BY event_time). Universal across Postgres / Snowflake / BigQuery / Trino / Flink SQL. Memorise it.
• DAU / MAU / stickiness formula. DAU = COUNT(DISTINCT player_id) last 24h; MAU = COUNT(DISTINCT player_id) last 30d; stickiness = 100.0 * DAU / MAU. Benchmark: 20% good, 30% great, 40% exceptional. Use APPROX_COUNT_DISTINCT above 1M players.
• Cohort retention grid. players LEFT JOIN sessions on player_id, GROUP BY (install_date, session_date - install_date). Hard-code the D1/D3/D7/D14/D30 columns with MAX(CASE WHEN days_since = N THEN active_players END). Materialise as a nightly Iceberg table.
• Funnel drop-off self-join. For each player, MIN(CASE WHEN event = step_N THEN event_time END) per step. Compute COUNT(step_N+1_reached) / COUNT(step_N_reached) for each transition. Sort by drop-off percentage descending — the biggest drop is the weakest step.
• ARPDAU calculation. SUM(revenue_cents) / 100.0 / COUNT(DISTINCT player_id) per date. Always split by acquisition_source and by paying/non-paying. Store revenue as integer cents everywhere; convert to dollars only at presentation.
• Kaplan-Meier LTV. LTV = Σ ARPDAU(t) × S(t) where S(t) = product over t' ≤ t of (1 - hazard(t')). Use lifelines in Python or a Snowflake / BigQuery UDF for large cohorts. Cap the sum at 365 days to bound the estimate.
• Iceberg row-level delete for GDPR. DELETE FROM t WHERE player_id = ? → CALL system.rewrite_data_files(...) → CALL system.expire_snapshots(...). Three-step operation; log each step to the compliance audit table.
• Redis sorted set for leaderboard. ZADD leaderboard score player_id (O(log N) write); ZREVRANGE leaderboard 0 9 WITHSCORES (O(log N + K) top-10 read); ZREVRANK leaderboard player_id (O(log N) player's rank). Rebuild from Iceberg on cluster loss.
• Client SDK batching config. Batch on 500 records or 30 seconds; SQLite offline buffer 10 MB with priority eviction (currency events retained, movement beacons evicted first); retry 1s / 5s / 30s / 5min; reconnect jitter 0-30s.
• Exactly-once for currency events. Flink execution.checkpointing.mode = EXACTLY_ONCE + Iceberg sink with TwoPhaseCommitSinkFunction + Kafka enable.idempotence=true + transactional.id pinned to collector. Non-currency events run at-least-once with event_id dedupe — save the transactional overhead on 99% of the firehose.
• Schema evolution rule (additive only). New fields are nullable with "default": null. No renames, no type changes, no removals. Deprecation window is 3 months (mark in schema doc, keep in wire format, then remove). Enforce with Schema Registry in FORWARD compat mode.
• Anti-cheat Z-score rule of thumb. Per-tier, per-weapon baselines over last 30 days (excluding today). Flag |z| > 3 for review. Precision improves 5-10x versus a single global baseline. Never auto-ban on Z-score alone; feed the shadowban → review pipeline.
• Isolation Forest cadence. Retrain weekly on last 30 days of features; n_estimators=200, contamination=0.01, random_state=42 are safe defaults. Deploy to feature store for real-time match-end scoring; batch-flag top 100 daily for human review.
• Reputation graph. Neo4j / TigerGraph / DGraph; nodes = players; edges = played-with weighted by (win-rate, chat-report-count, distrust-propagated). PageRank on distrust subgraph surfaces cheat clusters that co-play but individually look normal.

Frequently asked questions

What is game telemetry and how is it collected?

Game telemetry is the stream of events emitted by a game client and game server that describes what a player did — level starts, item purchases, PvP matches, item drops, movement, ad impressions, session opens and closes. In 2026, collection is done by an offline-first client SDK (Unity Analytics, Unreal Insights, Amplitude, GameAnalytics, mParticle) that batches events into 100-1000 record payloads, persists an offline buffer on device (typically SQLite, 10 MB cap with priority eviction), and retries on network failure with exponential backoff. The batched payloads are sent to a stateless HTTPS collector that validates the API key, decorates with a server received_at timestamp, verifies HMAC signatures, and publishes to Kafka. From Kafka, a Flink job streams events into an Iceberg raw event store on S3, with exactly-once semantics on currency events (purchases, IAPs) and at-least-once with idempotent dedupe on the higher-volume non-currency events (movement beacons, telemetry heartbeats). The 100M-events-per-day-per-title firehose is what distinguishes game telemetry from web analytics.

How do you sessionize player events?

The canonical sessionization gaming pattern is the 30-minute-gap heuristic: consecutive events for the same player that are ≤ 30 minutes apart belong to the same session; a gap > 30 minutes opens a new session. The SQL uses LAG(event_time) OVER (PARTITION BY player_id ORDER BY event_time) to find the previous event's timestamp, flags a new session with CASE WHEN LAG IS NULL OR event_time - LAG > INTERVAL '30 minutes' THEN 1 ELSE 0, then computes session_id as SUM(is_new_session) OVER (PARTITION BY player_id ORDER BY event_time). This same pattern runs in batch on the Iceberg source-of-truth table (via Trino / Spark / Snowflake / BigQuery), and in real-time as a Flink session-window (SESSION(event_time, INTERVAL '30' MINUTE)) keyed by player_id. For cross-device sessions, layer deterministic stitching (same player_id via login) on top of probabilistic stitching (device graph based on IP + device model + time proximity) so a player who moves from console to mobile to PC gets one continuous journey. Mobile hyper-casual games sometimes tighten the gap to 15 minutes; MMOs sometimes loosen it to 60 minutes.

What are the core KPIs for a live-service game?

The four headline KPIs on every game KPI dashboard are DAU, MAU, stickiness, and ARPDAU. DAU (Daily Active Users) is COUNT(DISTINCT player_id) over the last 24 hours. MAU (Monthly Active Users) is the same over the last 30 days. Stickiness = DAU / MAU — how often the average monthly player is a daily player; benchmarks are 20% good, 30% great, 40% exceptional. ARPDAU (Average Revenue Per DAU) is total daily revenue divided by DAU — the headline monetisation metric that collapses paying and non-paying players into one comparable number. Below the headlines sit ARPU (revenue / all registered users), ARPPU (revenue / distinct payers), LTV (lifetime revenue per player via Kaplan-Meier survival), D1 / D7 / D30 retention (fraction of cohort active on install day + N), and the retention heatmap (a grid of cohort × days-since-install). Live-ops teams refresh all of these hourly — not daily — so they can hot-patch the game economy within the hour of a store drop or event boss launch.

How do you build a real-time leaderboard?

Redis sorted sets are the industry standard because ZADD leaderboard score player_id is an O(log N) write and ZREVRANGE leaderboard 0 9 WITHSCORES returns the top-10 in O(log N + K) — fast enough for a 10 ms p99 API. The pipeline is: Flink job tails the scores Kafka topic, keeps per-player score in keyed state, issues ZADD on every score change, and simultaneously writes the score to an Iceberg scores table as the source of truth. Player rank queries use ZREVRANK leaderboard player_id; slice queries use ZRANGEBYSCORE leaderboard min max; region-specific leaderboards use a per-region sorted set (ZADD leaderboard:eu-west). Persistence: Redis is a hot cache, not the source of truth — the Iceberg scores table is the rebuildable ground truth in case the Redis cluster is lost. For seasonal or event leaderboards, snapshot the ZSET to Iceberg at season-end and start a fresh key for the next season.

How do you handle GDPR right-to-erasure in game telemetry?

GDPR erasure across a sharded event store is a three-step Iceberg operation. Step 1 — issue an equality delete: DELETE FROM t WHERE player_id = ? writes a small Iceberg V2 delete file that logically hides the matching rows from readers. Step 2 — run compaction: CALL system.rewrite_data_files(table => ...) physically rewrites the affected Parquet files without the deleted rows. Step 3 — expire snapshots: CALL system.expire_snapshots(table => ..., older_than => ...) removes the pre-deletion snapshots so the row is no longer reachable via time-travel. In production, a central erasure queue receives GDPR requests and fans them out to per-table Flink or Spark jobs; each job logs its completion to a compliance audit table so legal can prove the request was honoured. PII in Redis is safe by design because the ZSETs are rebuilt from the (now-erased) Iceberg source. The WORM audit table stores hashed PII only — legally defensible but not directly identifying, so a court-ordered erasure exception does not compromise the audit chain.

What's the difference between DAU, MAU, and stickiness?

DAU (Daily Active Users) counts distinct players in a rolling 24-hour window — the daily heartbeat of your game. MAU (Monthly Active Users) counts distinct players in a rolling 30-day window — the size of your engaged audience. Stickiness = DAU / MAU expressed as a percentage — the fraction of your monthly audience that plays on any given day. A game with 5M MAU and 1M DAU has 20% stickiness (industry-average); the same game with 1.5M DAU has 30% stickiness (great); at 2M DAU it hits 40% stickiness (exceptional — only the biggest live-service hits sustain that). Live-ops teams track stickiness week-over-week as the single most-actionable engagement metric: DAU alone conflates growth (more installs) with engagement (more play-per-player), while stickiness isolates the engagement axis. Because both DAU and MAU are COUNT(DISTINCT player_id), use HyperLogLog (APPROX_COUNT_DISTINCT) for games above 1M players — the < 1% error is invisible on a live-ops dashboard and the state footprint is 1000x smaller than exact-distinct.

Practice on PipeCode

• Drill the SQL practice library → for the LAG / self-join / aggregate family that gaming DE interviews probe every single round.
• Rehearse on window-functions medium-difficulty problems → when the interviewer wants sessionization or Z-score baselines in real time.
• Sharpen the aggregation library → for DAU / MAU / ARPDAU / cohort-retention SQL patterns.
• Stack the joins library → for cross-device stitching, feature-exposure joins, and funnel drop-off queries.
• Layer the real-time analytics library → for hourly-cadence KPI and leaderboard patterns.
• For the broader surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Sharpen the Spark axis with the Apache Spark internals course →.
• For ETL system design, work through the ETL system design course →.

Pipecode.ai is Leetcode for Data Engineering — every sessionization, KPI, and anti-cheat recipe above ships with hands-on practice rooms where you wire the LAG-plus-running-sum session_id, the DAU-MAU-stickiness rollup, and the Z-score anti-cheat baseline against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `gaming data engineering` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Window-function drills →

Comments

[Luis Cruz]

I was particularly interested in the section on sessionization gaming with gap-based windowing and cross-device player-id stitching, as this is a challenging problem in gaming data engineering. The use of deterministic-plus-probabilistic device graphs to handle player-id stitching across multiple devices is a clever approach, and I appreciate the mention of D1/D7/D30 retention curves and Kaplan-Meier LTV. Have you considered exploring the trade-offs between different sessionization techniques, such as fixed-window versus gap-based windowing, and how they impact the accuracy of live-ops KPIs like DAU and MAU?