How would you model user events to analyze cancellation paths?

WHAT THIS TESTS: Whether you understand that path analysis requires immutability, precise ordering, and identity resolution at the storage layer, not just in the BI tool. Interviewers want to see you separate behavioral events from entity state, handle late-arriving data, and optimize the physical layout for time-series scans.

A GOOD ANSWER COVERS: Four things in order. First, an append-only event stream schema where every row is an immutable interaction with a millisecond-precision timestamp, a stable user identifier that can reconcile anonymous and authenticated states, a session or device identifier, and a structured event type such as page_view, support_call, or cancellation_intent. Second, a separation between the event ledger and entity data; user attributes like subscription tier or churn risk should live in a profile or dimension table that is versioned or snapshotted rather than rewritten, so historical events can be joined to historical state. Third, physical storage optimization such as time-range partitioning and sorting by user_id and timestamp so that queries pulling a seven-day window before cancellation read contiguous blocks instead of full table scans. Fourth, a standardized envelope like the Experience Data Model that enforces common fields across channels while allowing custom properties, enabling the combination of web, mobile, and offline touchpoints into a single sequential view.

COMMON WRONG ANSWERS: Proposing a single wide user table with mutable columns like last_action or churn_status that gets overwritten nightly; this destroys the sequence and makes path reconstruction impossible. Suggesting a schema without session identifiers, which forces analysts to guess session boundaries with arbitrary timeouts. Ignoring late-arriving events or out-of-order timestamps, which breaks ordering guarantees in distributed systems. Proposing deep nested JSON blobs without a top-level canonical schema, making the data impossible to index or query performantly.

LIKELY FOLLOW-UPS: How would you handle identity stitching when a user browses anonymously before logging in? How do you balance schema flexibility with query performance as event types proliferate? What is your retention and compaction strategy when the event volume reaches billions of rows per day? How would you model A/B test assignments so they do not confound the path analysis?

ONE CONCRETE EXAMPLE: A subscription service stores events in a columnar format partitioned by event_date and sorted by user_id and event_timestamp. Each row contains user_id, session_id, event_type, timestamp, device_family, and a JSON context column for flexible properties. A separate daily snapshot table captures subscription_status and plan_tier per user_id. To analyze cancellation paths, an analyst filters the event table to the thirty days before cancellation, joins the snapshot table on user_id and snapshot_date, and runs a sessionized funnel because session boundaries are explicit in the schema.

Source: experienceleague.adobe.com