How would you structure an event schema for funnel and cohort analysis?

What's really being asked

This question evaluates your ability to design a data model optimized for analytical workloads rather than transactional processing. The interviewer cares that you understand event-driven architecture, the tradeoffs between normalization and denormalization, and how to minimize downstream transformation work for growth analysts. Specifically they want to see if you know how to preserve immutable history, attach rich context at collection time, and structure data so funnel, segmentation, and retention queries are single-pass operations.

The full answer

First, propose an immutable, append-only event log where each row represents exactly one event, following the Snowplow canonical model pattern. Second, advocate for a fat atomic table that stores common dimensions like user_id, anonymous_id, session_id, timestamps, device, OS, and campaign parameters in dedicated columns so analysts can filter and group without parsing JSON or joining lookup tables. Third, describe an extensible schema mechanism where custom unstructured events and custom contexts live in dedicated tables but remain joinable on a unique event_id, allowing product-specific instrumentation to evolve without breaking existing queries. Fourth, emphasize timestamp hygiene by distinguishing between collector timestamp, device timestamp, and derived timestamp so latency and clock skew do not corrupt funnel ordering. Fifth, mention identity stitching by capturing both anonymous and authenticated identifiers on every event, enabling accurate cohort retention and cross-device segmentation.

The mistakes people make

A normalized OLTP schema with separate tables for users, sessions, and events forces analysts to write complex joins and reconstruct state, which is exactly the anti-pattern the question warns against. Mutable state tables that update user attributes in place destroy historical context and make it impossible to answer what a user looked like at the time of the event. Storing only pre-aggregated counts removes the raw event granularity needed for ad-hoc funnel analysis. Proposing purely schemaless JSON blobs without indexed columns makes segmentation queries slow and expensive.

What usually comes next

The interviewer may ask how you would handle schema evolution without breaking downstream dashboards, how to enforce GDPR right-to-erasure in an immutable log, or what storage format you would choose for the raw layer versus the query layer. They might also probe how you would deduplicate events received multiple times, or how to model session boundaries when events arrive out of order.

A concrete example

Imagine a SaaS product onboarding flow. You instrument page_view, signup_start, and workspace_created events. Each row in the atomic events table carries collector_tstamp, user_id, anonymous_id, session_id, device_family, and utm_source. A custom context table holds trial_length_days and referrer_domain, joined by event_id. An analyst can compute a signup funnel by counting distinct session_ids where event_type equals each step in order, segment by utm_source without transformations, and measure week-one retention by checking whether users in the workspace_created cohort generated any subsequent event within seven days.