Activity Schema: modelling what happened, in order, in one table

Also called: the activity stream, event modelling, the customer timeline, the single-table event model

The newest model on this list, and the only one built around the question “what happened next?” rather than “how much, by whom?”

What it is

An Activity Schema is one table. Every row is one thing that happened to one entity at one moment: a customer signed up, placed an order, opened a ticket, cancelled. The entity is usually a customer; the moment is always required; everything else is detail hanging off those two.

What that buys is sequence. “What did people do between signing up and their first order?”, “how long until the second purchase?”, “what happened in the week before a cancellation?” — these are ordinary reads of a single ordered table here, and awkward multi-table gymnastics in a star schema.

It is a recent formalisation of something analytics teams kept building by hand, and it is deliberately narrow. It does not try to be a general warehouse model. It tries to make one family of questions cheap, and it succeeds.

How the tables are actually shaped

The stream

ActivityStream
  ActivityID           BIGINT IDENTITY
  EntityID             NVARCHAR   -- the customer, never NULL
  Activity             NVARCHAR   -- 'placed_order', 'opened_ticket'
  ActivityOccurredAt   DATETIME2  -- required; no time, no row
  ActivitySequence     INT        -- nth thing this entity did
  ActivityRepeatedAt   DATETIME2  -- next time THIS activity happened
  Feature1, Feature2, Feature3    -- meaning depends on the activity
  RevenueImpact        DECIMAL
  LinkID               NVARCHAR   -- back to the source row

The feature columns are the compromise that makes one table possible: their meaning depends on which activity the row is. Feature1 might be a payment method on an order row and a ticket category on a support row.

The two columns that are filled afterwards

ActivitySequence and ActivityRepeatedAt cannot be computed while the rows are being inserted — both are window functions over the finished stream. So the load has a second pass: insert everything, then number each entity’s activities in order and look ahead to the next occurrence of the same activity for the same entity.

That second column is what makes “how long between” questions cheap. Without it, every such question is a self-join over a large table.

What the data has to have

Two requirements, and both are absolute. There must be one entity everything relates to — usually a customer — and every activity must carry a timestamp. An activity with no time cannot be placed in a sequence, so it cannot be in the stream at all; leave it out and say so, rather than defaulting it to the load time and corrupting every ordering question.

Some activities reach the entity indirectly — an order line knows its order, and the order knows the customer. Those hop through the parent, and can inherit the parent’s timestamp if they have none of their own. Anything that can reach neither an entity nor a time is not an activity.

The dictionary

Because the feature columns mean different things per activity, the model is unreadable without a dictionary saying which is which. Keep it in the warehouse as a real table, generated from the design — what an activity means is a fact about the model, not about the source data — and it stays accurate instead of rotting in a wiki.

When it fits, and when it does not

Activity Schema is the easiest of the eight to rule in or out, because its requirements are concrete rather than a matter of judgement.

Those two lists are Netune’s own judgement of this methodology, copied from the rule file it scores a real database with.

What it costs

The mistakes that are expensive

Inventing a timestamp for an activity that has none

Defaulting a missing time to the load time puts the row in the stream at a position that is not true, and every sequence and gap question involving that entity is now quietly wrong. Leave the activity out and record why. An untimed event is not an event.

Letting the feature columns become undocumented

Feature1 means five different things across five activities. Without a maintained dictionary the table becomes unreadable within months, and the people who knew have moved on. This is the model’s main maintenance obligation and it is not optional.

Using it for state questions

“How many customers are in Bavaria” is a question about state, and a stream of events answers it badly — you have to reconstruct the current state by walking the history. If most of the reporting looks like that, the questions are dimensional and the model should be too.

How it compares

Activity Schema or a star schema?

Ask whether the questions are about state or about events. “How much did we sell, by region, last quarter” is state and aggregation, and a star answers it more naturally. “What did they do before they cancelled” is sequence, and a star makes you work hard for it. Plenty of organisations run both, because both kinds of question are real.

Activity Schema or One Big Table?

Both are one-table models. OBT is wide: one row per transaction with everything about it attached. Activity Schema is narrow and long: one row per thing that happened, ordered. Width answers “how much”; length answers “in what order”.

Activity Schema or Data Vault?

Both are append-only and keep everything. A vault is an integration and audit layer for many systems, structurally complex and not meant to be queried. An Activity Schema is a reporting model for one entity’s timeline, and is meant to be queried directly. They are not really competing for the same job.

Questions people ask