Activity schema and event modeling

Raw product events should be modeled as an append-only event log with standardized core identifiers and semi-structured payloads. In high-volume event architectures, store this as an activity schema or unified event table with proper date partitioning and clustering, downstream of which you build sessionized and user-level summary models.

Event table layout and partitioning

The foundational event table captures every user interaction at the atomic level:

event_id | event_timestamp     | user_id | event_name     | properties
uuid_1   | 2026-03-01 10:14:00 | usr_42  | product_viewed | {"sku":"shoe-1"}
uuid_2   | 2026-03-01 10:15:30 | usr_42  | added_to_cart  | {"sku":"shoe-1"}

Physical optimization is mandatory for query performance:

• Partition by day on event_timestamp to prevent queries from scanning multiple months of clickstream data.
• Cluster by event_name and user_id so filtering on specific funnels or customer timelines reads minimal data blocks.

The activity schema concept

The activity schema formalizes this concept by storing all customer interactions in a single normalized table with columns: activity_id, timestamp, customer, activity, and feature_json. Instead of writing complex joins between disparate fact tables for signups, page views, and purchases, analysts run temporal window functions on one table to measure conversion sequences.

Downstream aggregates and schema governance

Never let business intelligence tools query raw event tables directly. Build scheduled dbt or Spark models that sessionize clickstreams into fact_sessions and aggregate metrics into daily user summaries. Establish strict schema governance using JSON Schema or Protobuf contracts at the ingestion boundary to prevent frontend code releases from sending malformed event properties.