Activity Schema & Event-Based Modeling: The Single-Table Analytics Pattern

The activity schema is the modeling pattern that asks a heretical question: what if your entire analytics warehouse were one table instead of forty? Instead of a star schema per business process — a fct_orders here, a fct_sessions there, a dim_customer conformed across both, plus the dozen bridge tables that accrete around them — the activity schema records every meaningful thing a customer, account, or device did as a single row in one long, append-only stream. A signup is a row. A page view is a row. A completed order is a row. Each carries who did it, when, what kind of thing it was, and a small bag of attributes — and every downstream question, from "days from signup to first purchase" to a full customer-journey funnel, is answered by joining that one table to itself along time.

This guide is the senior walkthrough you wished existed the first time an interviewer said "explain the activity schema and how it differs from a star schema," or "how would you get each user's first purchase after signup out of a single activity stream," or "when would event-based modeling actually beat dimensional modeling and when would it fall apart?" It covers why event-based modeling emerged as a dimensional modeling alternative, the anatomy of the single table at one grain, the eleven canonical relationships implemented as temporal joins (self-joins on the stream), how customer 360 views and cohort funnels compose out of the same table, and the trade-offs, clustering strategy, and tooling — including narrator and the dbt activity-schema packages — that decide the verdict. Each section pairs a teaching block with a Solution-Tail interview answer: real 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 dimensional-modeling practice library →, rehearse the joins on the self-join practice library →, and sharpen the event angle with the event-modeling practice library →.

---

On this page

• Why event-based modeling emerged
• The activity stream table: one table, one grain
• Relationships and temporal joins (the 11 relationships)
• Building datasets and customer 360 from activities
• Trade-offs, tooling, and the modern-stack verdict
• Cheat sheet — activity schema recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why event-based modeling emerged

Model sprawl was the disease; "everything is an activity" was the cure

The one-sentence invariant: event-based modeling collapses the many facts and dimensions of a star schema into a single append-only activity stream where each row is one thing an entity did at a point in time — trading the star schema's per-process modeling and re-usable conformed dimensions for one grain, one table, and a fixed vocabulary of time-based relationships that answer customer-journey questions without designing a new fact table for each one. Dimensional modeling is not wrong; it is expensive to maintain once a company has dozens of business processes, because every new process means a new fact table, a re-conformed dimension, and a fresh round of "which grain is this at, and how does it join to the others." The activity schema's bet is that most analytics questions are really questions about sequences of events per entity, and that a single-grain table plus temporal joins answers them with far fewer moving parts.

The pain the star schema accrues at scale.

• Model sprawl. Each business process (orders, sessions, tickets, emails, payments) becomes its own fact table, each with its own grain, its own late-arriving-dimension handling, and its own set of derived measures. Forty models is a small warehouse; a large one has hundreds, and the dependency graph becomes the bottleneck.
• Conformed-dimension drift. A dim_customer is supposed to mean the same thing to fct_orders and fct_support, but the two teams that own those facts inevitably diverge on what "active" or "region" means. Keeping dimensions truly conformed is continuous political work, not a one-time design.
• Join fan-out and grain confusion. Cross-process questions ("did customers who opened the pricing email convert within 7 days?") require joining fact tables at different grains, and the classic fan-out/chasm-trap bugs multiply the moment you touch two facts and a bridge.
• Onboarding cost. A new analyst must learn the whole star before they can answer a journey question. The mental model does not compress: forty tables is forty tables.

The "everything is an activity" insight.

• A row is a verb, not a noun. In a dimensional model you store entities (customers, products) and measurements (order totals). In the activity schema you store what happened: signed_up, viewed_page, completed_order, opened_email. The noun (who) and the number (how much) ride along as attributes of the verb.
• One grain for the whole business. Every row is "entity X did activity Y at time T." There is exactly one grain, so there is never a grain mismatch to reason about when you combine two activities.
• Time is the join key, not a foreign key. Because everything shares the (entity_uuid, ts) shape, any two activities can be related by time — "the first order after signup," "the last page view before churn" — without a bespoke bridge table.

What the activity schema optimizes for.

• Customer journeys. Sequence questions ("signup → activation → purchase → churn") are the pattern's home turf; they are one temporal join away, not a new fact table.
• Fewer models, faster onboarding. One stream plus a handful of relationship macros replaces dozens of hand-built facts. A new analyst learns one table and eleven relationships.
• Auditability and replay. Because the stream is append-only and immutable, it doubles as an event log: you can rebuild any derived dataset from scratch, and you can answer "what did we know as of last Tuesday?" by filtering on ts.

What senior interviewers actually probe.

• Can you state the grain in one sentence ("one row per entity per activity per timestamp")? — required answer.
• Do you name the temporal self-join as the core mechanic, not "just a big events table"? — senior signal.
• Do you flag the cost of temporal joins at scale unprompted (clustering by entity_uuid, ts, pre-computed occurrence columns)? — senior signal.
• Do you know when it breaks (heavy financial roll-ups, wide slicing across many conformed dimensions)? — senior signal.
• Do you position it as a dimensional modeling alternative for journeys, not a wholesale replacement? — required answer.

Worked example — the activity-schema-vs-star comparison table

Detailed explanation. The single most useful artifact for an event-modeling interview is a memorised comparison of the activity schema against the star schema across the axes that actually differ. The point is not that one wins; it is that you can name the trade on each axis fluently. Walk through building the comparison for a subscription business that needs to answer both "signup-to-purchase funnel" (journey) and "monthly recognised revenue by plan" (finance).

• The workload split. Journey questions (funnels, retention, time-to-event) versus slice-and-dice finance questions (revenue by plan by month by region).
• The maintenance axis. How many models change when a new business process appears.
• The query axis. What a typical question costs to write and to run.
• The grain axis. How many grains a new analyst must hold in their head.

Question. Build the activity-schema-vs-star comparison for this business and state which workload each side wins.

Input.

Axis	Star schema	Activity schema
Grain	one per fact table	one, global (entity_uuid, ts, activity)
New process cost	new fact + conform dims	one new activity value
Journey question	multi-fact join + bridges	one temporal self-join
Finance slice-and-dice	native (star was built for it)	awkward (pivot the stream)
Onboarding	learn N tables	learn 1 table + 11 relationships

Code.

-- The same question, two ways.
-- Q: "days from signup to first purchase, per customer."

-- STAR SCHEMA: join two fact tables through a conformed dim.
SELECT c.customer_id,
       DATE_DIFF('day', s.signup_date, MIN(o.order_ts)) AS days_to_first_order
FROM   dim_customer  c
JOIN   fct_signups   s ON s.customer_key = c.customer_key
JOIN   fct_orders    o ON o.customer_key = c.customer_key
                       AND o.order_ts >= s.signup_date
GROUP  BY c.customer_id, s.signup_date;

-- ACTIVITY SCHEMA: one table, joined to itself along time.
SELECT signup.entity_uuid,
       DATE_DIFF('day', signup.ts, MIN(purchase.ts)) AS days_to_first_order
FROM   activity_stream signup
JOIN   activity_stream purchase
       ON purchase.entity_uuid = signup.entity_uuid
      AND purchase.activity     = 'completed_order'
      AND purchase.ts          >= signup.ts
WHERE  signup.activity = 'signed_up'
GROUP  BY signup.entity_uuid, signup.ts;

Step-by-step explanation.

1. The star version reaches into three objects — dim_customer, fct_signups, fct_orders — and depends on the customer key being conformed across both facts. If a customer exists in one fact but not the dimension, the join silently drops them.
2. The activity version touches exactly one physical table, aliased twice. The "dimension" (who) is the entity_uuid column carried on every row; there is nothing to conform because there is only one source of truth.
3. Both compute the same thing, but the activity version generalises: swap 'completed_order' for 'opened_ticket' and you have time-to-first-support with zero new modeling. The star version needs a new fact table for support before that question is even askable.
4. The finance question ("monthly recognised revenue by plan") flips the advantage: the star's fct_revenue with a dim_plan and dim_date is purpose-built for slicing, while the activity stream must pivot revenue_impact out of a narrow table — more work, sometimes slower.
5. The honest verdict is a split: journeys and time-to-event lean activity schema; heavy financial slice-and-dice leans star. Most mature stacks run both.

Output.

Workload	Winner	Why
Signup→purchase funnel	activity schema	one temporal join, no new models
Time-to-first-support	activity schema	add an activity, not a fact table
Monthly revenue by plan/region	star schema	built for slice-and-dice
New-hire ramp on journeys	activity schema	one table + 11 relationships
Auditable event replay	activity schema	append-only, immutable stream

Rule of thumb. Do not frame the activity schema as "better than the star." Frame it as "better for journeys and cheaper to maintain; the star still wins financial slice-and-dice." Interviewers reward the split answer.

Worked example — the "when it fits / when it doesn't" rubric

Detailed explanation. The senior skill is not adopting the activity schema; it is knowing when to. The rubric is four yes/no questions, and the answer to any single one can flip the decision. Walk the rubric for three teams: a growth team obsessed with funnels, a finance team that lives in slice-and-dice, and a startup that just wants something shipped this quarter.

• Journey-heavy? Are most questions about sequences of events per entity?
• Slice-and-dice-heavy? Are most questions "measure X by many dimensions"?
• Team maturity? Do analysts write SQL comfortably enough to reason about temporal joins?
• Scale + budget? Can the warehouse cluster the stream and pay for the self-joins?

Question. Score each of the three teams on the rubric and recommend activity schema, star, or hybrid.

Input.

Team	Journey-heavy?	Slice-heavy?	SQL-mature?	Can cluster/pay?
Growth	yes	no	yes	yes
Finance	no	yes	yes	yes
Early startup	mixed	mixed	mixed	yes (small data)

Code.

# A pocket rubric that returns a recommendation.
def recommend_model(journey_heavy, slice_heavy, sql_mature, can_cluster):
    if journey_heavy and not slice_heavy and sql_mature and can_cluster:
        return "activity schema"
    if slice_heavy and not journey_heavy:
        return "star schema"
    if not sql_mature or not can_cluster:
        return "star schema (safer default)"
    return "hybrid: activity schema for journeys + star for finance marts"

print(recommend_model(True,  False, True,  True))   # growth
print(recommend_model(False, True,  True,  True))   # finance
print(recommend_model(True,  True,  False, True))   # early startup

Step-by-step explanation.

1. The growth team is the textbook fit: journey-heavy, comfortable with SQL, and willing to cluster the stream. They get the activity schema and lose almost nothing.
2. The finance team is the textbook anti-fit: their questions are "revenue by plan by region by month," which is exactly what a star was designed to serve. Forcing an activity schema here means pivoting a narrow table on every query — slower and more error-prone.
3. The early startup scores "mixed" everywhere. The safe move is the star (or even one big table) until the workload declares itself; premature adoption of temporal joins burns a small team's SQL budget on a pattern they cannot yet operate.
4. Notice that can_cluster gates the pattern: without clustering by entity_uuid, ts, temporal self-joins on a large stream degrade to full shuffles. If the warehouse cannot cluster, downgrade the recommendation.
5. The most common real answer is the last branch — hybrid — where the activity schema powers journeys and a handful of conventional marts serve finance. Adopting the pattern is rarely all-or-nothing.

Output.

Team	Recommendation
Growth	activity schema
Finance	star schema
Early startup	star schema (safer default)
Mature mixed org	hybrid

Rule of thumb. The activity schema is a fit test, not a religion. Score journey-heaviness, slice-heaviness, SQL maturity, and clustering budget before you commit; when in doubt, hybrid.

Worked example — the anatomy of an activity

Detailed explanation. Before writing any DDL it pays to name the parts of a single activity, because the column list is the whole design. An activity is "an entity did a thing at a time, with a few attributes and an optional money impact and link." Everything else in the schema is derived from that sentence. Walk through decomposing three real events into activity rows.

• The who. entity_uuid — the customer, account, or device the activity belongs to. This is the join key for every relationship.
• The when. ts — the timestamp the activity occurred; the ordering key for every temporal join.
• The what. activity — a short verb string (signed_up, completed_order) drawn from a controlled vocabulary.
• The details. feature_json — a small typed bag of activity-specific attributes (plan, page, amount) so the table stays narrow.
• The money and the link. revenue_impact (optional numeric) and link (a URL or id back to the source record) for drill-through.

Question. Convert three raw events — a signup, a page view, and a completed order — into activity rows, deciding what goes in feature_json versus a top-level column.

Input.

Raw event	entity	ts	maps to activity
user registered	u-17	09:00	signed_up
viewed /pricing	u-17	09:02	viewed_page
paid $49 order 88	u-17	09:10	completed_order

Code.

INSERT INTO activity_stream (entity_uuid, ts, activity, feature_json, revenue_impact, link)
VALUES
  ('u-17', '2026-08-09 09:00:00', 'signed_up',
     '{"plan":"free","source":"google"}',                 NULL,  '/users/u-17'),
  ('u-17', '2026-08-09 09:02:00', 'viewed_page',
     '{"path":"/pricing","referrer":"/home"}',            NULL,  NULL),
  ('u-17', '2026-08-09 09:10:00', 'completed_order',
     '{"order_id":88,"plan":"pro","items":1}',            49.00, '/orders/88');

Step-by-step explanation.

1. The three raw events share the same entity_uuid (u-17), so a temporal join can already relate them — signup → page view → order — with no extra keys.
2. activity is a controlled verb, not free text. signed_up, viewed_page, completed_order are the vocabulary; keeping it small is what makes the stream queryable.
3. Attributes that vary per activity type live in feature_json: the signup's plan and source, the page view's path, the order's order_id. This keeps the physical table narrow while allowing per-activity richness.
4. revenue_impact is a top-level column, not a JSON field, because it is aggregated across activity types constantly ("total revenue before this activity"), and hoisting it out of JSON avoids parsing on every aggregate.
5. link gives every row a drill-through back to the operational record, which is what turns the stream into an auditable customer-360 backbone rather than an opaque event dump.

Output.

entity_uuid	ts	activity	revenue_impact
u-17	09:00	signed_up	—
u-17	09:02	viewed_page	—
u-17	09:10	completed_order	49.00

Rule of thumb. Hoist to a top-level column anything you aggregate or join on constantly (entity_uuid, ts, activity, revenue_impact); push everything else into feature_json. The column list is the schema design.

Senior interview question on choosing event-based modeling

A senior interviewer often opens with: "Your growth org has 40+ hand-built fact and dimension tables, new journey questions take a week to model, and analysts keep tripping over grain mismatches. Would you migrate to an activity schema? Walk me through how you'd decide, what you'd model first, and what you'd deliberately leave in the star."

Solution Using a scoped activity-schema adoption with a hybrid finance carve-out

Adoption plan — activity schema for journeys, star for finance
==============================================================

Step 1 — score the workload (the fit rubric)
  Journey-heavy?    yes  -> favours activity schema
  Slice-heavy?      finance only -> carve out
  SQL-mature team?  yes
  Can cluster/pay?  yes (Snowflake/BigQuery cluster on entity_uuid, ts)
  Verdict: HYBRID.

Step 2 — pick the entity + define the activity vocabulary
  entity = customer (entity_uuid)
  activities (v1): signed_up, activated, viewed_page, started_cart,
                   completed_order, opened_ticket, churned

Step 3 — build ONE activity_stream, backfilled from existing facts
  INSERT ... SELECT from fct_signups, fct_orders, fct_sessions, ...
  one SELECT per source, UNION ALL into the stream.

Step 4 — model journeys as relationships (not new facts)
  time-to-activate, signup->purchase funnel, retention curves
  = temporal self-joins, shipped as dbt macros.

Step 5 — leave finance in the star
  fct_revenue + dim_plan + dim_date stays; it slices better.
  Reconcile: SUM(revenue_impact) on the stream == fct_revenue total.

Step 6 — deprecate journey facts once parity holds
  delete fct_funnel_daily etc. after the stream reproduces them.

-- Parity check that must pass before deprecating a journey fact:
-- the stream must reproduce the old funnel's numbers exactly.
WITH stream_funnel AS (
    SELECT COUNT(DISTINCT CASE WHEN activity = 'signed_up'       THEN entity_uuid END) AS signups,
           COUNT(DISTINCT CASE WHEN activity = 'completed_order' THEN entity_uuid END) AS buyers
    FROM   activity_stream
    WHERE  ts >= '2026-07-01' AND ts < '2026-08-01'
)
SELECT s.signups, s.buyers,
       f.signups AS legacy_signups, f.buyers AS legacy_buyers,
       (s.signups = f.signups AND s.buyers = f.buyers) AS parity_ok
FROM   stream_funnel s
CROSS  JOIN fct_funnel_daily_rollup f
WHERE  f.month = '2026-07-01';

Step-by-step trace.

Step	Action	Outcome
1	Score rubric	journey-heavy + mature → hybrid
2	Define vocabulary	7 activities in v1
3	Backfill stream	one UNION ALL per source fact
4	Model journeys	temporal-join macros replace journey facts
5	Keep finance	fct_revenue star retained
6	Parity + deprecate	delete journey facts once parity_ok

Walking it: the rubric says hybrid, so you do not rip out the star wholesale. You stand up one activity_stream, backfill it from the existing facts (each source fact becomes a SELECT ... UNION ALL), and re-express journey questions as temporal-join macros. Finance stays in its purpose-built star. Before deleting any legacy journey fact, the parity check must show the stream reproduces its numbers exactly — parity_ok = true — which prevents a migration from silently changing a reported metric.

Output:

Deliverable	Before	After
Journey models	~15 hand-built facts	1 stream + macros
New journey question	~1 week	1 temporal join
Finance marts	star	star (unchanged)
Grain mismatches	frequent	none (one grain)
Metric parity	n/a	enforced pre-deprecation

Why this works — concept by concept:

• One grain — collapsing every process to "entity did activity at ts" removes the grain-mismatch class of bugs entirely, because there is only one grain to reason about across the whole warehouse.
• Backfill via UNION ALL — each existing fact table maps to one SELECT that emits activity rows, so the migration is additive and reversible; the old facts keep running until parity is proven.
• Relationships instead of facts — journey questions become temporal-join macros rather than new physical tables, which is why "new question in a week" becomes "new question in a query."
• Hybrid carve-out — finance slice-and-dice stays in the star because that is what stars are for; adopting the activity schema is scoped to the workload it wins.
• Parity gate — the parity_ok check makes deprecation safe: no legacy metric changes value silently during the migration.
• Cost — the stream costs one extra append per event and O(activities) storage; each journey query costs a self-join clustered on (entity_uuid, ts) — O(events per entity) locally rather than O(all facts) globally. Net: cheaper to maintain, with self-join compute traded for model sprawl.

Dimensional modeling
Topic — dimensional-modeling
Dimensional modeling and activity-schema design problems

Practice →

Design Topic — design Data-model design trade-off problems

Practice →

---

2. The activity stream table: one table, one grain

The canonical activity stream — entity, timestamp, verb, and a small bag of features, forever appended

The mental model in one line: the activity stream is a single physical table at exactly one grain — one row per (entity_uuid, activity, ts) — that is append-only and immutable, carries a small controlled set of top-level columns (entity_uuid, ts, activity, feature_json, revenue_impact, link) plus optional pre-computed occurrence columns, and serves as the sole source of truth from which every dataset, funnel, and customer 360 view is derived. Everything that makes the pattern work — the temporal joins, the datasets, the auditability — depends on this table being narrow, immutable, and clustered on (entity_uuid, ts).

The canonical columns.

• entity_uuid (TEXT). The subject of the activity — usually the customer, but any consistent entity (account, device, org) works. It is the partition key of every relationship.
• ts (TIMESTAMP). When the activity happened, in UTC. The ordering key for all temporal joins; it must be the event time, not the load time.
• activity (TEXT). A short verb from a controlled vocabulary. The distinct set of activity values is effectively your list of business processes.
• feature_json (JSON/VARIANT). A narrow bag of activity-specific attributes. Keeps the table narrow while allowing each activity type its own fields.
• revenue_impact (NUMERIC, nullable). Money attached to the activity, hoisted out of JSON because it is aggregated constantly.
• link (TEXT, nullable). A drill-through pointer (URL or source id) back to the operational record.

Why one grain and append-only matter.

• One grain removes grain math. Because every row means the same thing, combining any two activities never raises "are these at compatible grains?" — the question that sinks half of star-schema queries.
• Append-only enables replay. New events are inserted, never updated; corrections arrive as new activities (e.g. order_refunded) rather than mutations. Any derived dataset can be rebuilt from the stream.
• Immutability enables time-travel. "What did we know as of last Tuesday?" is a WHERE ts <= '...' filter, because history is never rewritten in place.
• Pre-computed occurrence columns. Optional helper columns — activity_occurrence (the 1st, 2nd, 3rd time this entity did this activity) and activity_repeated_at (when they next did it) — are computed once at load time so hot relationships avoid a self-join at query time.

Feeding the stream from source systems.

• One transform per source. Each source table (app events, payments, email logs) maps to a SELECT that renames its columns to the canonical six and emits activity rows.
• UNION ALL, never join. Sources are stacked, not joined; the stream is the union of every source's activity rows.
• Idempotent loads. A natural key (entity_uuid, activity, ts, source_id) plus a MERGE/dedupe guard keeps re-runs from double-inserting.
• Late data is just a new row. Because ordering is by ts, a late-arriving event slots into the correct place in the stream the moment it lands.

Worked example — the activity-stream DDL

Detailed explanation. The DDL is the whole design decision made concrete: which columns are top-level, how the table clusters, and what occurrence helpers you pre-compute. Walk through a Snowflake-flavoured DDL for a customer activity stream, noting each choice.

• Top-level vs JSON. The six canonical columns are top-level; everything else is feature_json.
• Clustering. Cluster on (entity_uuid, ts) so temporal self-joins co-locate an entity's rows.
• Occurrence helpers. Add activity_occurrence and activity_repeated_at for hot relationships.
• Immutability. No updated_at; corrections are new rows.

Question. Write the activity_stream DDL with clustering and the occurrence helper columns.

Input.

Column	Type	Role
entity_uuid	TEXT	subject / join key
ts	TIMESTAMP_NTZ	event time / order key
activity	TEXT	controlled verb
feature_json	VARIANT	per-activity attributes
revenue_impact	NUMBER(18,2)	aggregated money
link	TEXT	drill-through
activity_occurrence	INT	nth time (pre-computed)
activity_repeated_at	TIMESTAMP_NTZ	next same activity

Code.

CREATE TABLE analytics.activity_stream (
    entity_uuid           TEXT             NOT NULL,
    ts                    TIMESTAMP_NTZ    NOT NULL,
    activity              TEXT             NOT NULL,
    feature_json          VARIANT,
    revenue_impact        NUMBER(18,2),
    link                  TEXT,
    -- pre-computed occurrence helpers (filled at load time):
    activity_occurrence   INTEGER,          -- 1 = first time entity did this activity
    activity_repeated_at  TIMESTAMP_NTZ,    -- when the entity next did the same activity
    _loaded_at            TIMESTAMP_NTZ     DEFAULT CURRENT_TIMESTAMP()
)
CLUSTER BY (entity_uuid, ts);

-- One controlled vocabulary check (documentation-as-constraint):
-- activity IN ('signed_up','activated','viewed_page','started_cart',
--              'completed_order','opened_ticket','churned', ...)

Step-by-step explanation.

1. The six canonical columns come first and are NOT NULL where they must be (entity_uuid, ts, activity); a stream row with no entity or no time is meaningless.
2. feature_json is VARIANT so each activity type carries its own attributes without widening the table; on BigQuery this is JSON, on Postgres JSONB.
3. revenue_impact is a real numeric column, not a JSON field, because "aggregate revenue before/after this activity" is a hot query and parsing JSON on every row would dominate its cost.
4. activity_occurrence and activity_repeated_at are the performance escape hatch: computed once with window functions at load time, they let the two most common relationships ("first ever," "next occurrence") skip a self-join entirely.
5. CLUSTER BY (entity_uuid, ts) is not optional — it is the line that makes temporal self-joins scale, because it physically co-locates each entity's activities in ts order so a join prunes to a handful of micro-partitions.

Output.

Choice	Decision	Reason
Grain	one row per activity	removes grain math
revenue_impact	top-level column	hot aggregation
feature_json	VARIANT	narrow table, rich attrs
Clustering	(entity_uuid, ts)	temporal-join pruning
Occurrence cols	pre-computed	skip self-joins on hot paths

Rule of thumb. The DDL commits you: hoist the hot-aggregated/hot-joined columns to top level, cluster on (entity_uuid, ts), and pre-compute occurrence helpers. Everything else lives in feature_json.

Worked example — inserting activities from source events

Detailed explanation. The stream is fed by one transform per source, each renaming source columns to the canonical six and stacking with UNION ALL. The occurrence columns are then filled with a window function. Walk through loading signups, orders, and email opens into the stream.

• One SELECT per source. Signups from raw.users, orders from raw.orders, opens from raw.email_events.
• Rename to canonical. Map each source's id → entity_uuid, its time → ts, a literal → activity.
• UNION ALL. Stack the three; never join them.
• Fill occurrence. A ROW_NUMBER over (entity_uuid, activity ORDER BY ts) sets activity_occurrence.

Question. Write the insert that loads three sources into the stream and computes activity_occurrence.

Input.

Source	entity col	ts col	activity literal
raw.users	user_id	created_at	signed_up
raw.orders	user_id	paid_at	completed_order
raw.email_events	user_id	opened_at	opened_email

Code.

INSERT INTO analytics.activity_stream
    (entity_uuid, ts, activity, feature_json, revenue_impact, link,
     activity_occurrence, activity_repeated_at)
WITH unioned AS (
    SELECT user_id            AS entity_uuid,
           created_at         AS ts,
           'signed_up'        AS activity,
           OBJECT_CONSTRUCT('plan', plan, 'source', utm_source) AS feature_json,
           NULL::NUMBER       AS revenue_impact,
           '/users/' || user_id AS link
    FROM   raw.users

    UNION ALL

    SELECT user_id, paid_at, 'completed_order',
           OBJECT_CONSTRUCT('order_id', order_id, 'plan', plan),
           amount, '/orders/' || order_id
    FROM   raw.orders

    UNION ALL

    SELECT user_id, opened_at, 'opened_email',
           OBJECT_CONSTRUCT('campaign', campaign_id),
           NULL, NULL
    FROM   raw.email_events
)
SELECT entity_uuid, ts, activity, feature_json, revenue_impact, link,
       ROW_NUMBER() OVER (PARTITION BY entity_uuid, activity ORDER BY ts)  AS activity_occurrence,
       LEAD(ts)     OVER (PARTITION BY entity_uuid, activity ORDER BY ts)  AS activity_repeated_at
FROM   unioned;

Step-by-step explanation.

1. Each source becomes one SELECT that renames its columns to the canonical six; the activity value is a literal string, which is how a source table "declares" which activity it produces.
2. OBJECT_CONSTRUCT builds feature_json from the source's leftover columns — plan and source for signups, order id and plan for orders — keeping those attributes without new physical columns.
3. UNION ALL (not UNION) stacks the three without deduping, because two genuinely distinct events can share values and must both survive; dedupe, if needed, happens against a natural key upstream.
4. ROW_NUMBER() OVER (PARTITION BY entity_uuid, activity ORDER BY ts) numbers each entity's occurrences of each activity, so activity_occurrence = 1 marks the first ever of that activity for that entity — a relationship precomputed for free.
5. LEAD(ts) fills activity_repeated_at with the next time the same entity did the same activity, precomputing the "next occurrence" relationship so retention/repeat queries avoid a self-join.

Output.

entity_uuid	activity	ts	activity_occurrence
u-17	signed_up	09:00	1
u-17	completed_order	09:10	1
u-17	opened_email	10:00	1
u-17	completed_order	Aug-12	2

Rule of thumb. Load with one SELECT per source, UNION ALL them, and compute activity_occurrence/activity_repeated_at with window functions in the same pass — pre-computing the two hottest relationships at write time is the cheapest performance win in the whole pattern.

Worked example — validating the one-grain invariant

Detailed explanation. A stream is only trustworthy if the grain actually holds: no duplicate (entity_uuid, activity, ts), no null keys, and activity inside the controlled vocabulary. A data-contract check enforces this at load time. Walk through the three assertions.

• Uniqueness. No two rows share (entity_uuid, activity, ts).
• Non-null keys. entity_uuid, ts, activity are always present.
• Vocabulary. Every activity is a known verb.

Question. Write a validation query that returns the count of grain violations; it must return zero for a healthy stream.

Input.

Check	Rule
dup_grain	count of duplicate (entity_uuid, activity, ts)
null_keys	rows with any null key
bad_activity	activity not in vocabulary

Code.

WITH dups AS (
    SELECT entity_uuid, activity, ts, COUNT(*) AS n
    FROM   analytics.activity_stream
    GROUP  BY entity_uuid, activity, ts
    HAVING COUNT(*) > 1
)
SELECT
    (SELECT COUNT(*) FROM dups)                                            AS dup_grain,
    (SELECT COUNT(*) FROM analytics.activity_stream
      WHERE entity_uuid IS NULL OR ts IS NULL OR activity IS NULL)         AS null_keys,
    (SELECT COUNT(*) FROM analytics.activity_stream
      WHERE activity NOT IN ('signed_up','activated','viewed_page',
                             'started_cart','completed_order',
                             'opened_email','opened_ticket','churned'))    AS bad_activity;

Step-by-step explanation.

1. The dups CTE groups by the grain key (entity_uuid, activity, ts) and keeps only groups with more than one row — any such group is a grain violation that would double-count in every downstream aggregate.
2. The null_keys subquery counts rows missing any of the three mandatory keys; such rows cannot participate in a temporal join and must be quarantined at load.
3. The bad_activity subquery enforces the controlled vocabulary; a typo like signd_up would otherwise silently create a phantom activity that no query looks for.
4. All three are returned as a single row of counts, which makes the check trivial to wire into a dbt test or a CI assertion: fail the build if any count is non-zero.
5. Because the stream is append-only, running this after each load is cheap and catches source-mapping regressions the moment they appear.

Output.

dup_grain	null_keys	bad_activity
0	0	0

Rule of thumb. Treat the grain as a contract: assert uniqueness of (entity_uuid, activity, ts), non-null keys, and vocabulary membership on every load. A stream whose grain drifts poisons every dataset built on it.

SQL interview question on the single-table activity stream

A senior interviewer often asks: "Why would you deliberately store all business events in one wide activity table instead of separate typed event tables? Defend the single-table choice, and show me the query pattern that makes it pay off — say, each customer's most recent activity of any kind."

Solution Using one clustered stream with a per-entity latest-activity query

-- Each entity's most recent activity of ANY kind, from the single stream.
-- QUALIFY avoids a self-join by ranking within the entity partition.
SELECT entity_uuid,
       ts          AS last_activity_ts,
       activity    AS last_activity,
       revenue_impact
FROM   analytics.activity_stream
QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_uuid ORDER BY ts DESC) = 1;

-- Contrast: the SAME question across N typed tables would need
-- a UNION ALL of every event table first, then the ranking:
--   SELECT ... FROM events_signup
--   UNION ALL SELECT ... FROM events_order
--   UNION ALL SELECT ... FROM events_email  ... (repeated per new event type)
-- The single table makes "any activity" a first-class, zero-maintenance query.

Step-by-step trace.

entity_uuid	ts	activity	row_number (ts DESC)
u-17	09:00	signed_up	3
u-17	09:10	completed_order	2
u-17	10:00	opened_email	1 ✓
u-31	08:00	signed_up	1 ✓

Walking it: because everything lives in one clustered table, "the latest activity of any kind" is a single ROW_NUMBER over PARTITION BY entity_uuid ORDER BY ts DESC, filtered to rank 1 via QUALIFY. For u-17, the email open at 10:00 is the newest and wins; for u-31, the lone signup wins. With typed per-event tables you would first UNION ALL every table (and edit that union every time a new event type is added) before you could even ask the question — the single table makes "any activity" free and future-proof.

Output:

entity_uuid	last_activity_ts	last_activity	revenue_impact
u-17	10:00	opened_email	—
u-31	08:00	signed_up	—

Why this works — concept by concept:

• Single table, one grain — "any activity" is expressible only because every event shares one shape; typed tables force a maintenance-heavy UNION ALL that grows with every new event type.
• QUALIFY + ROW_NUMBER — ranking within the entity_uuid partition returns the latest row without a self-join, which is the cheapest form of the "last activity" relationship.
• Clustering by (entity_uuid, ts) — the partition-and-order the query needs matches the physical clustering, so the warehouse prunes to each entity's micro-partitions instead of scanning the whole stream.
• Append-only immutability — "latest" is always well defined because rows are never updated; the newest ts is the truth, forever.
• Cost — O(rows) scan pruned by clustering to O(rows per entity) effective work; no join, no fan-out. Adding a new event type is O(0) query changes — it is just another activity value.

SQL
Topic — sql
SQL problems on window functions and single-table event queries

Practice →

Dimensional modeling Topic — dimensional-modeling Modeling problems on grain and single-table design

Practice →

---

3. Relationships and temporal joins (the 11 relationships)

Eleven time-anchored temporal joins turn one stream into every journey question

The mental model in one line: the Activity Schema defines a fixed vocabulary of about eleven temporal joins — relationships like first ever, last before, first after, aggregate all ever, aggregate before, and aggregate after — each of which relates a chosen primary activity to another appended activity on the same stream by entity_uuid and a time predicate, so that "the first order after signup" or "total revenue before churn" is a parameterised self-join rather than a bespoke query. Learn the eleven relationships once and you can compose any journey dataset by chaining them.

The anatomy of a relationship.

• Primary activity. The activity you anchor on — one row per occurrence becomes one row of the dataset (e.g. each signed_up).
• Appended activity. The activity you relate to the primary (e.g. completed_order).
• Time predicate. before, after, or ever relative to the primary's ts.
• Selection. first, last, or an aggregate (SUM/COUNT/MIN/MAX) over the matching appended rows.

The eleven relationships (the canonical set).

• First ever / Last ever. The earliest / latest appended activity for the entity, ignoring the primary's time.
• First before / Last before. The earliest / latest appended activity that happened before the primary's ts.
• First after / Last after. The earliest / latest appended activity after the primary's ts.
• First in between / Last in between. The earliest / latest appended activity between this primary and the entity's next primary occurrence.
• Aggregate all ever. SUM/COUNT/MIN/MAX of the appended activity across all time.
• Aggregate before / Aggregate after. The same aggregates restricted to before / after the primary's ts.

Why they are all the same shape.

• One join key, one order key. Every relationship joins the stream to itself ON appended.entity_uuid = primary.entity_uuid and filters on appended.ts versus primary.ts.
• Only the predicate and the pick change. before vs after vs ever is the time predicate; first vs last vs aggregate is the pick. Eleven relationships is really "3 predicates × a few picks."
• They compose. A dataset is a primary activity plus several appended relationships joined on, each adding a column — which is exactly section 4's job.
• They can be pre-computed. The two hottest — first-ever and next-occurrence — are the activity_occurrence/activity_repeated_at columns from section 2.

Worked example — "first activity before" as a temporal self-join

Detailed explanation. "First before" finds, for each occurrence of a primary activity, the earliest appended activity that happened before it. The canonical implementation is a self-join with a ts < predicate, reduced to one row with QUALIFY ROW_NUMBER. Walk through "for each completed order, the first page the customer ever viewed before that order."

• Primary. completed_order.
• Appended. viewed_page.
• Predicate. viewed_page.ts < completed_order.ts.
• Pick. first → earliest such viewed_page.

Question. For each completed_order, return the first viewed_page that occurred before it, for the same entity.

Input.

entity_uuid	activity	ts
u-17	viewed_page	09:02
u-17	viewed_page	09:05
u-17	completed_order	09:10

Code.

SELECT p.entity_uuid,
       p.ts                         AS order_ts,
       a.ts                         AS first_view_before_ts,
       a.feature_json:path::string  AS first_view_path
FROM   analytics.activity_stream p            -- primary
JOIN   analytics.activity_stream a            -- appended
       ON  a.entity_uuid = p.entity_uuid
      AND  a.activity     = 'viewed_page'
      AND  a.ts          <  p.ts               -- BEFORE the primary
WHERE  p.activity = 'completed_order'
QUALIFY ROW_NUMBER() OVER (PARTITION BY p.entity_uuid, p.ts
                           ORDER BY a.ts ASC) = 1;   -- FIRST -> earliest

Step-by-step explanation.

1. The stream is aliased twice: p for the primary (completed_order) and a for the appended (viewed_page); this self-join is the literal mechanic of every relationship.
2. The join condition ties them to the same entity_uuid and constrains a.ts < p.ts, which is the "before" time predicate — everything the entity did prior to the order.
3. Without a pick, this returns every prior page view per order; the QUALIFY ROW_NUMBER collapses it to one row per primary occurrence.
4. ORDER BY a.ts ASC ... = 1 selects the earliest qualifying page view — that is the "first" pick. Switching to DESC would give "last before."
5. PARTITION BY p.entity_uuid, p.ts scopes the ranking to a single primary occurrence, so a customer with two orders gets a correct "first view before" for each order independently.

Output.

entity_uuid	order_ts	first_view_before_ts	first_view_path
u-17	09:10	09:02	/pricing

Rule of thumb. Every "first/last before/after" relationship is one self-join with a ts inequality plus a QUALIFY ROW_NUMBER whose ORDER BY direction encodes first-vs-last. Memorise the skeleton; change only the predicate and the sort.

Worked example — "aggregate before" (running totals along the stream)

Detailed explanation. "Aggregate before" computes a SUM/COUNT over an appended activity restricted to before the primary's timestamp — e.g. "total revenue the customer had generated before each support ticket." The self-join stays, but the pick becomes a GROUP BY aggregate instead of a row pick. Walk through revenue-before-ticket.

• Primary. opened_ticket.
• Appended. completed_order.
• Predicate. completed_order.ts < opened_ticket.ts.
• Pick. SUM(revenue_impact).

Question. For each opened_ticket, compute the total revenue_impact from all completed_order activities before that ticket.

Input.

entity_uuid	activity	ts	revenue_impact
u-17	completed_order	09:10	49.00
u-17	completed_order	Aug-12	30.00
u-17	opened_ticket	Aug-15	—

Code.

SELECT p.entity_uuid,
       p.ts                                   AS ticket_ts,
       COALESCE(SUM(a.revenue_impact), 0)     AS revenue_before_ticket,
       COUNT(a.ts)                            AS orders_before_ticket
FROM   analytics.activity_stream p
LEFT   JOIN analytics.activity_stream a
       ON  a.entity_uuid = p.entity_uuid
      AND  a.activity     = 'completed_order'
      AND  a.ts          <  p.ts              -- BEFORE the ticket
WHERE  p.activity = 'opened_ticket'
GROUP  BY p.entity_uuid, p.ts;

Step-by-step explanation.

1. Same self-join skeleton: p is the primary (opened_ticket), a is the appended (completed_order), tied by entity_uuid and a.ts < p.ts.
2. The join is a LEFT JOIN so a ticket with no prior orders still returns a row (with a zeroed aggregate) rather than vanishing — the correct behaviour for "revenue before," which can legitimately be zero.
3. Instead of picking one appended row, we GROUP BY p.entity_uuid, p.ts and aggregate all matching appended rows — that is the "aggregate" pick.
4. COALESCE(SUM(...), 0) turns the NULL from a no-match LEFT JOIN into a clean 0, and COUNT(a.ts) counts prior orders (nulls excluded), giving two aggregates from one join.
5. The grain of the output is one row per primary occurrence — per ticket — which is exactly what "aggregate before, per ticket" means.

Output.

entity_uuid	ticket_ts	revenue_before_ticket	orders_before_ticket
u-17	Aug-15	79.00	2

Rule of thumb. "Aggregate before/after" is the same self-join as "first/last," but you LEFT JOIN + GROUP BY + SUM/COUNT instead of QUALIFY-picking one row. Always LEFT JOIN so zero is representable, and COALESCE the aggregate.

Worked example — "first in between" using the next primary occurrence

Detailed explanation. "In between" bounds the appended activity between the current primary and the entity's next primary occurrence — e.g. "the first page viewed between one login and the next login." This needs both a lower and an upper time bound, where the upper bound comes from a LEAD over the primary. Walk through views-between-logins.

• Primary. logged_in.
• Appended. viewed_page.
• Lower bound. viewed_page.ts >= this login.
• Upper bound. viewed_page.ts < next login (from LEAD).

Question. For each logged_in, return the first viewed_page before the entity's next logged_in.

Input.

entity_uuid	activity	ts
u-17	logged_in	09:00
u-17	viewed_page	09:03
u-17	logged_in	11:00

Code.

WITH sessions AS (   -- bound each login by the NEXT login
    SELECT entity_uuid, ts AS login_ts,
           LEAD(ts) OVER (PARTITION BY entity_uuid ORDER BY ts) AS next_login_ts
    FROM   analytics.activity_stream
    WHERE  activity = 'logged_in'
)
SELECT s.entity_uuid,
       s.login_ts,
       a.ts                        AS first_view_ts,
       a.feature_json:path::string AS first_view_path
FROM   sessions s
JOIN   analytics.activity_stream a
       ON  a.entity_uuid = s.entity_uuid
      AND  a.activity     = 'viewed_page'
      AND  a.ts          >= s.login_ts
      AND (a.ts < s.next_login_ts OR s.next_login_ts IS NULL)   -- IN BETWEEN
QUALIFY ROW_NUMBER() OVER (PARTITION BY s.entity_uuid, s.login_ts
                           ORDER BY a.ts ASC) = 1;

Step-by-step explanation.

1. The sessions CTE turns each logged_in into a half-open interval [login_ts, next_login_ts) using LEAD — this is how "in between" gets its upper bound.
2. The join relates each interval to viewed_page rows for the same entity that fall inside the interval: a.ts >= login_ts (lower) and a.ts < next_login_ts (upper).
3. The next_login_ts IS NULL branch handles the entity's last login, which has no next login — its interval runs to infinity, so all later views belong to it.
4. QUALIFY ROW_NUMBER ... ORDER BY a.ts ASC = 1 picks the first view inside each interval, the "first in between" selection.
5. This composes the "in between" relationship from a window function (for the bound) plus the standard self-join skeleton (for the pick) — a good illustration that the eleven relationships are built from a few reusable parts.

Output.

entity_uuid	login_ts	first_view_ts	first_view_path
u-17	09:00	09:03	/dashboard

Rule of thumb. "In between" = "after" with an upper bound supplied by LEAD over the primary. Always handle the final next_* IS NULL interval, or the entity's last session silently drops its appended activities.

SQL interview question on temporal relationships

A senior interviewer often asks: "From a single activity stream, get each user's first purchase after signup and the number of days between the two. Then tell me how you'd stop this query from doing a full self-join shuffle on a billion-row stream."

Solution Using a first-after self-join with occurrence pruning

-- First purchase AFTER signup, per user, with days-to-convert.
SELECT s.entity_uuid,
       s.ts                                   AS signup_ts,
       o.ts                                   AS first_order_ts,
       DATE_DIFF('day', s.ts, o.ts)           AS days_to_convert,
       o.revenue_impact                       AS first_order_value
FROM   analytics.activity_stream s
JOIN   analytics.activity_stream o
       ON  o.entity_uuid = s.entity_uuid
      AND  o.activity     = 'completed_order'
      AND  o.ts          >= s.ts               -- AFTER signup
WHERE  s.activity          = 'signed_up'
  AND  s.activity_occurrence = 1               -- the FIRST signup only (pre-computed)
QUALIFY ROW_NUMBER() OVER (PARTITION BY s.entity_uuid
                           ORDER BY o.ts ASC) = 1;   -- FIRST order after

-- Scale guard: cluster the stream so the self-join prunes to each
-- entity's micro-partitions instead of shuffling the whole table.
ALTER TABLE analytics.activity_stream CLUSTER BY (entity_uuid, ts);
-- And filter the appended side early (activity = 'completed_order')
-- so partition pruning drops non-order micro-partitions before the join.

Step-by-step trace.

entity_uuid	activity	ts	occurrence	role
u-17	signed_up	Aug-09	1	primary (kept)
u-17	completed_order	Aug-10	1	first after ✓
u-17	completed_order	Aug-12	2	after (not first)
u-31	signed_up	Aug-09	1	primary (no order)

Walking it: s anchors on signed_up and is restricted to activity_occurrence = 1 so a user who somehow has two signup rows still yields one primary. The self-join brings in every completed_order with o.ts >= s.ts (after signup); QUALIFY ROW_NUMBER ... ORDER BY o.ts ASC = 1 keeps only the earliest — Aug-10 for u-17. DATE_DIFF yields one day to convert. u-31 signed up but never ordered, so the inner join drops them (use LEFT JOIN if unconverted users must appear). At a billion rows, clustering on (entity_uuid, ts) plus filtering the appended side to completed_order prunes the join to each entity's few order partitions rather than a global shuffle.

Output:

entity_uuid	signup_ts	first_order_ts	days_to_convert	first_order_value
u-17	Aug-09	Aug-10	1	49.00

Why this works — concept by concept:

• First-after relationship — the o.ts >= s.ts predicate plus QUALIFY ROW_NUMBER ... ASC = 1 is the canonical "first after" temporal join; the whole answer is one parameterised self-join.
• Occurrence pruning — restricting the primary to activity_occurrence = 1 uses the pre-computed helper from section 2 to guarantee one primary per entity without a second window pass.
• Clustering by (entity_uuid, ts) — co-locating each entity's rows in time order lets the self-join prune to a handful of micro-partitions per entity, which is what stops the billion-row shuffle.
• Early appended filter — filtering o.activity = 'completed_order' before the join lets the engine drop non-order micro-partitions, shrinking the join's build side.
• INNER vs LEFT — INNER answers "converters only"; swap to LEFT JOIN to include never-purchased users as NULL — a one-word change that flips the business question.
• Cost — without clustering the self-join is O(N²) in the worst case; clustered on (entity_uuid, ts) it is O(events per entity) per entity, effectively O(N) with a small constant. The pre-computed occurrence column removes one window pass.

Self-join
Topic — self-join
Self-join and temporal-relationship problems

Practice →

SQL Topic — sql SQL problems on time-ordered joins and QUALIFY

Practice →

---

4. Building datasets and customer 360 from activities

A dataset is a primary activity with relationships appended — and customer 360 is just the widest one

The mental model in one line: a "dataset" in the Activity Schema is a primary activity plus a chosen set of appended relationships, each contributing one column, so a funnel, a cohort table, or a full customer 360 view is composed by picking a primary and chaining relationships off it — the same eleven temporal joins from section 3, assembled — rather than by writing a bespoke query or building a new fact table. The single table feeds all of them; the dataset is a composition, not a new physical model.

The dataset abstraction.

• Pick a primary. Every row of the dataset is one occurrence of the primary activity (e.g. one row per signed_up).
• Append relationships. Each appended relationship (first after: completed_order, aggregate before: revenue) adds one column, joined on the primary's entity_uuid and ts.
• One grain out. The dataset's grain is the primary's grain; appended relationships never fan it out because each resolves to one value per primary occurrence.
• Materialise or view. A dataset can be a view (always fresh) or an incremental table (fast to read) depending on cost.

Customer 360 as the widest dataset.

• Primary = the entity itself. Use first ever: signed_up as the anchor so there is exactly one row per customer.
• Append lots of relationships. Lifetime revenue (aggregate all ever), last-seen (last ever of any activity), first order, ticket count, days-since-signup — each a column.
• Drill-through via link. The link column on each contributing activity powers "click to the source record" in a 360 UI.
• Refreshes cheaply. Because it is a composition over one clustered table, the 360 rebuilds from the stream on a schedule.

Funnels and cohorts, composed.

• Funnel = ordered relationship existence. "signed_up → started_cart → completed_order" is three appended first after relationships whose presence/absence defines each stage.
• Cohort = group the primary by a bucket. Bucket the primary's ts (signup week) and compute conversion within the cohort.
• Retention = repeated occurrence. activity_repeated_at (section 2) gives next-occurrence directly, so N-day retention is a filter, not a join.

Worked example — a signup→cart→purchase funnel query

Detailed explanation. A funnel on the activity stream anchors on the entry activity (signup) and appends "did the entity later do stage 2, then stage 3?" as first after relationships, then counts how many reached each stage. Walk through a three-stage funnel.

• Stage 1 (primary). signed_up.
• Stage 2. first started_cart after signup.
• Stage 3. first completed_order after the cart.
• Metric. count of entities reaching each stage.

Question. Build a three-stage funnel (signup → cart → order) and report counts and conversion per stage.

Input.

entity_uuid	signed_up	started_cart	completed_order
u-17	Aug-09	Aug-09	Aug-10
u-31	Aug-09	Aug-09	—
u-42	Aug-09	—	—

Code.

WITH s AS (   -- one row per signup (the funnel entry)
    SELECT entity_uuid, ts AS signup_ts
    FROM   analytics.activity_stream
    WHERE  activity = 'signed_up' AND activity_occurrence = 1
),
cart AS (     -- first cart after signup
    SELECT s.entity_uuid, MIN(a.ts) AS cart_ts
    FROM   s JOIN analytics.activity_stream a
      ON a.entity_uuid = s.entity_uuid
     AND a.activity = 'started_cart' AND a.ts >= s.signup_ts
    GROUP BY s.entity_uuid
),
ord AS (      -- first order after the cart
    SELECT c.entity_uuid, MIN(a.ts) AS order_ts
    FROM   cart c JOIN analytics.activity_stream a
      ON a.entity_uuid = c.entity_uuid
     AND a.activity = 'completed_order' AND a.ts >= c.cart_ts
    GROUP BY c.entity_uuid
)
SELECT COUNT(DISTINCT s.entity_uuid)                              AS signed_up,
       COUNT(DISTINCT cart.entity_uuid)                          AS started_cart,
       COUNT(DISTINCT ord.entity_uuid)                           AS completed_order,
       ROUND(100.0 * COUNT(DISTINCT ord.entity_uuid)
                   / COUNT(DISTINCT s.entity_uuid), 1)           AS signup_to_order_pct
FROM   s
LEFT   JOIN cart ON cart.entity_uuid = s.entity_uuid
LEFT   JOIN ord  ON ord.entity_uuid  = s.entity_uuid;

Step-by-step explanation.

1. s establishes the funnel entry — one row per first signup — which fixes the denominator of every conversion rate.
2. cart is a first after relationship: the earliest started_cart at or after each signup, one row per entity that reached stage 2.
3. ord chains off cart, not off signup: it is the first completed_order after the cart, enforcing funnel order (you must cart before you can order).
4. The final LEFT JOINs keep every signup and attach cart/order timestamps where they exist; COUNT(DISTINCT ...) per stage counts how many entities reached it.
5. Conversion is orders / signups as a percentage — and because each stage chains off the previous one, the funnel respects sequence rather than merely counting who ever carted and who ever ordered independently.

Output.

signed_up	started_cart	completed_order	signup_to_order_pct
3	2	1	33.3

Rule of thumb. Build funnels by chaining first after relationships stage-to-stage (each stage anchored on the previous stage's timestamp, not the entry), so the funnel enforces order. Counting "ever did X" per stage independently overcounts and breaks the sequence.

Worked example — a customer-360 dataset build

Detailed explanation. Customer 360 anchors on one row per customer and appends many relationships: lifetime revenue, order count, first/last activity, days since signup. It is the widest dataset but structurally identical to a funnel — primary plus appended relationships. Walk through the build.

• Primary. first signed_up (one row per customer).
• Append. lifetime revenue (aggregate all ever), order count, last-seen (last ever), days-since-signup.
• Grain. one row per entity_uuid.
• Drill-through. carry link from the signup row.

Question. Build a customer-360 dataset with lifetime revenue, order count, last-seen activity, and days since signup.

Input.

entity_uuid	signed_up	orders	lifetime_rev	last_activity
u-17	Aug-09	2	79.00	opened_email

Code.

WITH base AS (   -- primary: one row per customer at first signup
    SELECT entity_uuid, ts AS signup_ts, link AS signup_link
    FROM   analytics.activity_stream
    WHERE  activity = 'signed_up' AND activity_occurrence = 1
),
rev AS (         -- aggregate all ever: revenue + order count
    SELECT entity_uuid,
           SUM(revenue_impact) AS lifetime_revenue,
           COUNT(*)            AS order_count
    FROM   analytics.activity_stream
    WHERE  activity = 'completed_order'
    GROUP  BY entity_uuid
),
last_seen AS (   -- last ever: newest activity of any kind
    SELECT entity_uuid, ts AS last_seen_ts, activity AS last_activity
    FROM   analytics.activity_stream
    QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_uuid ORDER BY ts DESC) = 1
)
SELECT b.entity_uuid,
       b.signup_ts,
       COALESCE(r.lifetime_revenue, 0)          AS lifetime_revenue,
       COALESCE(r.order_count, 0)               AS order_count,
       l.last_seen_ts,
       l.last_activity,
       DATE_DIFF('day', b.signup_ts, CURRENT_DATE()) AS days_since_signup,
       b.signup_link
FROM   base b
LEFT   JOIN rev       r ON r.entity_uuid = b.entity_uuid
LEFT   JOIN last_seen l ON l.entity_uuid = b.entity_uuid;

Step-by-step explanation.

1. base fixes the grain at one row per customer using the first-signup primary, so the final dataset cannot fan out.
2. rev is an aggregate all ever relationship — lifetime revenue and order count across every completed_order regardless of time.
3. last_seen is a last ever relationship implemented with QUALIFY ROW_NUMBER ... DESC = 1, giving the newest activity of any kind (the "last seen" a 360 UI shows).
4. The LEFT JOINs attach each relationship as a column; COALESCE makes never-purchased customers show 0 revenue rather than NULL, keeping the 360 clean.
5. days_since_signup is a derived column, and signup_link carries the drill-through — together they turn the row into an actionable profile rather than a bare aggregate.

Output.

entity_uuid	lifetime_revenue	order_count	last_activity	days_since_signup
u-17	79.00	2	opened_email	0

Rule of thumb. Customer 360 is a funnel's structural twin: one primary (first signup) plus many appended relationships as columns. Anchor the grain on the primary and LEFT JOIN every relationship so the profile never fans out and never drops a customer.

Worked example — a signup-week cohort retention table

Detailed explanation. A cohort table buckets the primary by a time grain (signup week) and measures a later behaviour (ordered within 7 days). It is a funnel grouped by cohort. Walk through weekly signup cohorts and 7-day conversion.

• Cohort key. DATE_TRUNC('week', signup_ts).
• Behaviour. first completed_order within 7 days of signup.
• Metric. cohort size and 7-day converters.
• Output grain. one row per signup week.

Question. Build a weekly signup cohort with 7-day order conversion.

Input.

entity_uuid	signup_ts	first_order_ts
u-17	Aug-03	Aug-04
u-31	Aug-03	Aug-20
u-42	Aug-10	—

Code.

WITH s AS (
    SELECT entity_uuid,
           ts                               AS signup_ts,
           DATE_TRUNC('week', ts)           AS cohort_week
    FROM   analytics.activity_stream
    WHERE  activity = 'signed_up' AND activity_occurrence = 1
),
conv AS (   -- first order within 7 days of signup
    SELECT s.entity_uuid, s.cohort_week,
           MIN(o.ts) AS first_order_ts
    FROM   s JOIN analytics.activity_stream o
      ON  o.entity_uuid = s.entity_uuid
     AND  o.activity     = 'completed_order'
     AND  o.ts >= s.signup_ts
     AND  o.ts <  DATEADD('day', 7, s.signup_ts)
    GROUP BY s.entity_uuid, s.cohort_week
)
SELECT s.cohort_week,
       COUNT(DISTINCT s.entity_uuid)                            AS cohort_size,
       COUNT(DISTINCT conv.entity_uuid)                         AS converted_7d,
       ROUND(100.0 * COUNT(DISTINCT conv.entity_uuid)
                   / COUNT(DISTINCT s.entity_uuid), 1)          AS conv_7d_pct
FROM   s
LEFT   JOIN conv ON conv.entity_uuid = s.entity_uuid
GROUP  BY s.cohort_week
ORDER  BY s.cohort_week;

Step-by-step explanation.

1. s assigns each first signup to a cohort_week via DATE_TRUNC, which is the cohort grouping key.
2. conv is a bounded first after relationship: the earliest order that is both after signup and within a 7-day window (o.ts < signup + 7 days).
3. The 7-day upper bound is what makes it a retention metric rather than lifetime conversion — u-31 ordered, but on Aug-20, outside the window, so they do not count as converted.
4. The outer LEFT JOIN + COUNT(DISTINCT ...) computes cohort size and converters per week, and the ratio gives 7-day conversion per cohort.
5. Grouping by cohort_week yields one row per cohort — the classic cohort-retention shape — all from the single stream, no cohort fact table required.

Output.

cohort_week	cohort_size	converted_7d	conv_7d_pct
Aug-03	2	1	50.0
Aug-10	1	0	0.0

Rule of thumb. A cohort is a funnel grouped by a bucketed primary timestamp, and retention is a bounded first after (ts < primary + window). The window bound is the whole difference between "converted ever" and "converted in N days."

Data-modeling interview question on composing datasets

A senior interviewer often asks: "Using only the activity stream, build a signup→purchase funnel dataset that the growth team can query per acquisition channel, and explain how you keep it fresh without rebuilding the whole thing nightly."

Solution Using a composed funnel dataset with incremental materialisation

-- Composed funnel dataset, sliceable by acquisition channel.
-- Primary = first signup; appended = first order after signup.
CREATE OR REPLACE TABLE analytics.ds_signup_funnel AS
WITH s AS (
    SELECT entity_uuid,
           ts                                        AS signup_ts,
           feature_json:source::string               AS channel
    FROM   analytics.activity_stream
    WHERE  activity = 'signed_up' AND activity_occurrence = 1
),
o AS (
    SELECT s.entity_uuid,
           MIN(a.ts)                                 AS first_order_ts,
           SUM(a.revenue_impact)                     AS first_order_rev
    FROM   s JOIN analytics.activity_stream a
      ON  a.entity_uuid = s.entity_uuid
     AND  a.activity     = 'completed_order'
     AND  a.ts          >= s.ts
    GROUP BY s.entity_uuid
)
SELECT s.entity_uuid, s.signup_ts, s.channel,
       o.first_order_ts,
       DATE_DIFF('day', s.signup_ts, o.first_order_ts) AS days_to_convert,
       (o.first_order_ts IS NOT NULL)                  AS converted
FROM   s LEFT JOIN o ON o.entity_uuid = s.entity_uuid;

-- Freshness: refresh ONLY entities with new activity since last run,
-- instead of rebuilding the whole dataset nightly.
MERGE INTO analytics.ds_signup_funnel tgt
USING (
    /* re-derive the funnel rows only for entities touched since watermark */
    SELECT f.* FROM analytics.ds_signup_funnel_incremental f
    WHERE f.entity_uuid IN (
        SELECT DISTINCT entity_uuid
        FROM   analytics.activity_stream
        WHERE  _loaded_at > (SELECT MAX(_run_at) FROM analytics._ds_runs)
    )
) src
ON  tgt.entity_uuid = src.entity_uuid
WHEN MATCHED     THEN UPDATE SET tgt.first_order_ts = src.first_order_ts,
                                 tgt.days_to_convert = src.days_to_convert,
                                 tgt.converted       = src.converted
WHEN NOT MATCHED THEN INSERT VALUES (src.entity_uuid, src.signup_ts, src.channel,
                                     src.first_order_ts, src.days_to_convert, src.converted);

Step-by-step trace.

Step	Action	Result
1	Primary = first signup	one row/customer + channel
2	Append first-after order	first_order_ts, revenue
3	Compute derived cols	days_to_convert, converted
4	Slice by channel	group ds_signup_funnel by channel
5	Incremental refresh	MERGE only touched entities

Walking it: the dataset is one primary (first signup, carrying the acquisition channel out of feature_json) plus one appended relationship (first order after signup). Derived columns give days_to_convert and a boolean converted. Because channel is a column, the growth team slices the funnel per acquisition channel with a plain GROUP BY channel. Freshness is handled by a MERGE that re-derives funnel rows only for entities whose activities loaded after the last run watermark (_loaded_at > MAX(_run_at)), so a nightly run touches thousands of changed customers, not millions of unchanged ones.

Output:

channel	signups	converted	conv_pct
google	1	1	100.0
direct	1	0	0.0

Why this works — concept by concept:

• Dataset = primary + relationships — the funnel is a composition (first signup + first-after order), not a new fact table, so a new slice dimension is just another feature_json column hoisted into the primary.
• Channel from feature_json — carrying source up from JSON makes the funnel sliceable per acquisition channel with a plain GROUP BY, no join to a dim_channel.
• LEFT JOIN for converted flag — keeping unconverted signups (converted = false) makes the denominator correct; an inner join would silently report inflated conversion.
• Incremental MERGE — refreshing only entities with _loaded_at past the watermark turns an O(all customers) nightly rebuild into O(changed customers), which is what "fresh without rebuilding everything" means.
• Watermark table — _ds_runs records each run's timestamp so the next run knows exactly which entities changed, giving idempotent incremental refreshes.
• Cost — full build is O(signups) with one clustered self-join; incremental refresh is O(entities changed since last run). Slicing by channel is O(rows in the dataset), a small materialised table, not the raw stream.

ETL
Topic — etl
ETL problems on incremental datasets and funnels

Practice →

Event modeling Topic — event-modeling Event-modeling problems on funnels, cohorts, and customer 360

Practice →

---

5. Trade-offs, tooling, and the modern-stack verdict

Temporal joins are the tax; clustering, occurrence columns, and the right tooling are how you pay it

The mental model in one line: the activity schema trades the star schema's model sprawl for the compute cost of temporal joins, and the verdict on whether it wins depends on three levers — clustering the stream by (entity_uuid, ts), pre-computing occurrence/next columns so hot relationships skip the join, and choosing tooling (narrator, dbt activity-schema packages, warehouse-native SQL) that generates the relationship joins for you — after which it beats the star for journeys and loses to star/OBT for heavy financial slice-and-dice, so mature stacks run a hybrid. Knowing where the pattern breaks is the senior signal, not evangelising it.

Performance of temporal joins at scale.

• The cost. Every relationship is a self-join with a ts inequality; on a billion-row stream, an unclustered self-join risks a full shuffle — the pattern's signature failure mode.
• Clustering is mandatory. CLUSTER BY (entity_uuid, ts) (Snowflake) / partition + cluster (BigQuery) co-locates each entity's rows so the join prunes to a few micro-partitions.
• Pre-computed occurrence columns. activity_occurrence and activity_repeated_at remove a window pass and let the two hottest relationships skip the self-join entirely.
• Narrow rows, hoisted hot columns. Keeping the table narrow and revenue_impact top-level minimises bytes scanned per join.

Tooling.

• narrator. The company that popularised the Activity Schema; its product generates the relationship joins and dataset composition from a UI over your stream.
• dbt activity-schema packages. Community dbt packages implement the relationships as macros so you compose datasets in dbt models rather than hand-writing self-joins.
• Warehouse-native SQL. You can run the entire pattern with plain SQL + window functions + clustering; no special tool is required, only discipline.
• BI on datasets, not the stream. Point BI tools at materialised datasets (funnels, 360), never at the raw stream, so dashboards do not each pay for the self-joins.

Where it beats and loses.

• Beats star for. Customer journeys, time-to-event, funnels, retention, auditable replay, fast onboarding — anything sequence-shaped.
• Loses to star/OBT for. Heavy financial slice-and-dice across many conformed dimensions, wide multi-dimensional roll-ups, and BI models where a purpose-built star is simply faster.
• Hybrid is the norm. Activity schema for journeys; star/OBT marts for finance and heavy slicing; reconcile totals (SUM(revenue_impact) == fct_revenue).
• OBT vs activity schema. One Big Table denormalises one process wide; the activity schema unifies all processes narrow-and-long. They solve different problems.

Worked example — a clustering and partition strategy for temporal joins

Detailed explanation. The single biggest performance lever is clustering the stream so self-joins prune. On Snowflake that is CLUSTER BY (entity_uuid, ts); on BigQuery it is partition-by-date plus cluster-by-entity. Walk through the strategy and how to verify pruning.

• Snowflake. Automatic clustering on (entity_uuid, ts).
• BigQuery. PARTITION BY DATE(ts) + CLUSTER BY entity_uuid, activity.
• Verify. Check the query profile prunes partitions on a relationship query.
• Maintain. Watch clustering depth / re-cluster cost as the stream grows.

Question. Choose and justify a clustering/partition strategy for a billion-row stream on both Snowflake and BigQuery.

Input.

Warehouse	Mechanism	Key
Snowflake	CLUSTER BY	(entity_uuid, ts)
BigQuery	PARTITION + CLUSTER	DATE(ts) + entity_uuid, activity

Code.

-- Snowflake: cluster so per-entity temporal joins prune micro-partitions.
ALTER TABLE analytics.activity_stream CLUSTER BY (entity_uuid, ts);

-- BigQuery: partition by day (prunes time ranges), cluster by entity+activity
-- (prunes to an entity's rows and to a single activity type within a partition).
-- CREATE TABLE analytics.activity_stream (
--   entity_uuid STRING, ts TIMESTAMP, activity STRING, feature_json JSON, ...
-- )
-- PARTITION BY DATE(ts)
-- CLUSTER BY entity_uuid, activity;

-- Verify pruning on a relationship query (Snowflake):
-- run the "first order after signup" join, then inspect the query profile —
-- "partitions scanned" should be a tiny fraction of "partitions total".
SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.activity_stream',
                                     '(entity_uuid, ts)');

Step-by-step explanation.

1. On Snowflake, CLUSTER BY (entity_uuid, ts) co-locates each entity's activities in time order; a self-join filtered to one entity then reads only that entity's micro-partitions instead of the whole table.
2. On BigQuery, PARTITION BY DATE(ts) prunes the time dimension (a "last 30 days" funnel scans 30 partitions), and CLUSTER BY entity_uuid, activity prunes within a partition to a single entity and activity type.
3. Clustering by activity as a secondary key on BigQuery matters because relationship queries always filter activity = '...' on the appended side; that filter becomes a cluster-prune.
4. Verification is empirical: run the relationship query and inspect "partitions scanned vs total"; if the ratio is not tiny, the clustering key is wrong or the query is not filtering the entity/activity early.
5. SYSTEM$CLUSTERING_INFORMATION reports clustering depth so you can watch degradation as the append-only stream grows and decide when re-clustering cost is justified.

Output.

Warehouse	Strategy	Effect
Snowflake	CLUSTER BY (entity_uuid, ts)	per-entity micro-partition pruning
BigQuery	PARTITION DATE(ts) + CLUSTER entity, activity	time + entity + activity pruning

Rule of thumb. Cluster the stream on (entity_uuid, ts) (add activity on BigQuery), then verify pruning in the query profile. An activity schema without clustering is a full-shuffle machine; clustering is not optional at scale.

Worked example — a dbt activity model with relationship macros

Detailed explanation. In a dbt stack the stream is a model, the occurrence columns are computed in that model, and each dataset is a downstream model that calls relationship macros. Walk through a minimal dbt activity model plus a macro-composed funnel.

• Staging model. stg_activity_stream unions sources into the canonical six columns.
• Occurrence model. adds activity_occurrence, activity_repeated_at via windows.
• Dataset model. calls a first_after macro to compose a funnel.
• Tests. dbt tests assert the grain contract.

Question. Write a dbt dataset model that composes "first order after signup" via a relationship macro.

Input.

dbt object	Role
stg_activity_stream	canonical stream
int_activity_occurrence	+ occurrence cols
ds_signup_to_order	funnel dataset

Code.

-- models/marts/ds_signup_to_order.sql
{{ config(materialized='incremental', unique_key='entity_uuid',
          cluster_by=['entity_uuid','signup_ts']) }}

with signups as (
    select entity_uuid, ts as signup_ts
    from {{ ref('int_activity_occurrence') }}
    where activity = 'signed_up' and activity_occurrence = 1
),

first_order as (
    -- relationship macro: FIRST 'completed_order' AFTER each signup
    {{ activity_first_after(
         primary=ref('signups'),
         stream=ref('int_activity_occurrence'),
         appended_activity='completed_order',
         primary_ts='signup_ts') }}
)

select s.entity_uuid,
       s.signup_ts,
       fo.appended_ts                              as first_order_ts,
       datediff('day', s.signup_ts, fo.appended_ts) as days_to_convert
from signups s
left join first_order fo using (entity_uuid)

{% if is_incremental() %}
  where s.entity_uuid in (
      select distinct entity_uuid from {{ ref('int_activity_occurrence') }}
      where _loaded_at > (select max(signup_ts) from {{ this }})
  )
{% endif %}

Step-by-step explanation.

1. The config block materialises the dataset incrementally and clusters it on (entity_uuid, signup_ts), so the dataset itself is fast to read and cheap to refresh.
2. signups selects the primary from the occurrence model, reusing the pre-computed activity_occurrence = 1 rather than re-deriving "first signup."
3. activity_first_after(...) is a relationship macro that expands to the standard self-join + QUALIFY ROW_NUMBER skeleton — the same SQL from section 3, but written once and reused across every dataset.
4. The left join ... using (entity_uuid) appends the relationship as columns, keeping every signup so conversion denominators stay correct.
5. The is_incremental() block limits the refresh to entities whose activities loaded since the last run, turning nightly rebuilds into incremental merges — the dbt expression of the section-4 watermark trick.

Output.

dbt model	materialization	composes
ds_signup_to_order	incremental, clustered	first-after via macro

Rule of thumb. In dbt, implement the eleven relationships as macros once, then compose datasets by calling them; materialise datasets incrementally and cluster them. Hand-writing the self-join in every model is how relationship logic drifts.

Worked example — reconciling the stream against the finance star

Detailed explanation. In a hybrid stack the activity schema and the finance star must agree on money, or trust evaporates. The reconciliation is a simple equality check: SUM(revenue_impact) on the stream must equal the star's fct_revenue total for the same period. Walk through the check.

• Stream side. SUM(revenue_impact) where activity = 'completed_order'.
• Star side. SUM(amount) from fct_revenue.
• Grain. per month.
• Tolerance. exact (or a tiny rounding epsilon).

Question. Write the reconciliation that flags any month where the stream and the finance star disagree on revenue.

Input.

month	stream_rev	star_rev
2026-07	120000.00	120000.00
2026-08	98000.00	97500.00

Code.

WITH stream_rev AS (
    SELECT DATE_TRUNC('month', ts) AS month,
           SUM(revenue_impact)     AS stream_rev
    FROM   analytics.activity_stream
    WHERE  activity = 'completed_order'
    GROUP  BY 1
),
star_rev AS (
    SELECT DATE_TRUNC('month', order_ts) AS month,
           SUM(amount)                   AS star_rev
    FROM   analytics.fct_revenue
    GROUP  BY 1
)
SELECT COALESCE(s.month, f.month)                       AS month,
       s.stream_rev,
       f.star_rev,
       ROUND(COALESCE(s.stream_rev,0) - COALESCE(f.star_rev,0), 2) AS delta,
       (ABS(COALESCE(s.stream_rev,0) - COALESCE(f.star_rev,0)) < 0.01) AS reconciled
FROM   stream_rev s
FULL   OUTER JOIN star_rev f ON s.month = f.month
ORDER  BY month;

Step-by-step explanation.

1. stream_rev aggregates revenue_impact per month from the activity stream, using only completed_order rows so refunds or other activities do not distort it.
2. star_rev aggregates the finance star's amount per month — the independent source of truth for revenue.
3. A FULL OUTER JOIN on month keeps months present in either side, so a month that exists in one model but not the other surfaces as a mismatch rather than disappearing.
4. delta is the signed difference and reconciled is a boolean within a one-cent epsilon; the epsilon absorbs floating-point rounding without hiding real gaps.
5. In the sample, July reconciles exactly but August is off by 500 — a flag that some completed_order activities carry a revenue_impact that disagrees with the star, which is precisely the kind of drift a hybrid stack must catch.

Output.

month	stream_rev	star_rev	delta	reconciled
2026-07	120000.00	120000.00	0.00	true
2026-08	98000.00	97500.00	500.00	false

Rule of thumb. In a hybrid, reconcile the stream's SUM(revenue_impact) against the finance star every period with a FULL OUTER JOIN and a small epsilon. If money does not tie out, nobody trusts either model — the reconciliation is the price of running both.

System-design interview question on when NOT to use an activity schema

A senior interviewer often asks: "You are excited about the activity schema, but I want to hear the other side — describe a concrete workload where you would deliberately not use it, what fails, and what you would build instead."

Solution Using a workload-fit analysis that rejects the activity schema for finance slice-and-dice

When NOT to use the activity schema — a worked rejection
========================================================

Workload: monthly financial reporting.
  Questions: "recognised revenue by plan by region by month by
              currency, with QoQ and YoY, sliced 6 ways, for the board."

Why the activity schema struggles here:
  1. Slice-and-dice across MANY dimensions = pivot the narrow stream
     on every query -> more CPU, more error-prone than a purpose star.
  2. Conformed finance dimensions (plan, region, currency, calendar)
     want to be real dim tables with SCD history, not feature_json.
  3. Roll-ups (QoQ/YoY) over many groupings favour a pre-aggregated
     fact/OBT, not repeated temporal joins.
  4. Auditors want a stable, documented star, not "trust the stream".

What to build instead:
  - fct_revenue (grain: recognised revenue line) +
    dim_plan, dim_region, dim_currency, dim_date (SCD2 where needed).
  - Optionally an OBT (one big denormalised table) for the BI tool.
  - Keep the activity schema for JOURNEYS; reconcile revenue to fct_revenue.

Verdict: HYBRID. Activity schema != finance mart.

-- The finance question is trivial in a star, awkward in the stream.
-- STAR (natural):
SELECT d.month, p.plan, r.region, SUM(f.amount) AS revenue
FROM   fct_revenue f
JOIN   dim_plan   p ON p.plan_key   = f.plan_key
JOIN   dim_region r ON r.region_key = f.region_key
JOIN   dim_date   d ON d.date_key   = f.date_key
GROUP  BY d.month, p.plan, r.region;   -- clean cube, purpose-built

Step-by-step trace.

Consideration	Activity schema	Finance star
Many-dim slice-and-dice	pivot stream (awkward)	native cube
Conformed dims + SCD	feature_json (weak)	real dim tables
QoQ / YoY roll-ups	repeated temporal joins	pre-aggregated fact
Auditor trust	"trust the stream"	documented star
Verdict	reject for finance	build this

Walking it: the finance workload is defined by slicing one measure (revenue) across many conformed dimensions with calendar roll-ups. Each of those needs is exactly what a star schema was invented for — real dimension tables with SCD history, a date dimension for QoQ/YoY, and a pre-aggregated fact for cube queries. Forcing this onto the activity stream means pivoting a narrow table and cramming dimensions into feature_json, which is slower, harder to audit, and easy to get wrong. The senior answer is not "activity schema everywhere"; it is "activity schema for journeys, a purpose-built star for finance, reconciled" — a hybrid.

Output:

Workload	Model chosen	Reason
Journeys / funnels / 360	activity schema	sequence-shaped, one grain
Finance slice-and-dice	star (+ optional OBT)	conformed dims, cubes, audit
Whole org	hybrid	each model where it wins

Why this works — concept by concept:

• Workload-fit over dogma — the decision is driven by the shape of the questions (sequence vs slice-and-dice), not by enthusiasm for a pattern; naming the anti-fit is the senior signal.
• Conformed dimensions — finance wants real dim_plan/dim_region/dim_date with SCD history, which feature_json models poorly; the star is purpose-built for this.
• Pre-aggregation for roll-ups — QoQ/YoY cubes favour a pre-aggregated fact/OBT over repeated temporal self-joins, which would recompute the cube every query.
• Auditability — a documented star is easier for auditors and finance to trust than "derive it from the stream," which matters for board-level reporting.
• Hybrid reconciliation — keeping the activity schema for journeys while running a finance star, and reconciling revenue between them, captures both models' strengths.
• Cost — finance-on-stream is O(pivots × temporal joins) per query; finance-on-star is O(fact scan) against a pre-modelled cube. For many-dimension slice-and-dice the star is both cheaper and clearer — so you build it.

Design
Topic — design
System-design problems on model selection and trade-offs

Practice →

Dimensional modeling
Topic — dimensional-modeling
Modeling problems on star vs OBT vs activity schema

Practice →

---

Cheat sheet — activity schema recipes

• What the activity schema is. One append-only, immutable activity_stream at a single grain — one row per (entity_uuid, activity, ts) — replaces per-process facts and conformed dimensions for journey-shaped analytics. It is a dimensional modeling alternative for sequences, not a wholesale star replacement; mature stacks run it alongside a finance star (hybrid).
• Canonical columns. entity_uuid (subject/join key), ts (event time/order key, UTC), activity (controlled verb), feature_json (per-activity attributes), revenue_impact (top-level because it is aggregated constantly), link (drill-through). Hoist to top-level anything you join or aggregate on; push the rest into feature_json.
• The one-grain contract. Assert on every load: uniqueness of (entity_uuid, activity, ts), non-null entity_uuid/ts/activity, and activity within the controlled vocabulary. A stream whose grain drifts poisons every dataset; enforce it as a dbt test that fails the build on any violation.
• Loading. One SELECT per source renaming to the canonical six, stacked with UNION ALL (never join sources). Build feature_json with OBJECT_CONSTRUCT/to_jsonb. Compute activity_occurrence (ROW_NUMBER OVER (entity_uuid, activity ORDER BY ts)) and activity_repeated_at (LEAD(ts)) in the same pass to pre-compute the two hottest relationships.
• The 11 relationships. first ever, last ever, first/last before, first/last after, first/last in between, aggregate all ever, aggregate before, aggregate after. All are the same self-join ON appended.entity_uuid = primary.entity_uuid; only the ts predicate (before/after/ever) and the pick (QUALIFY ROW_NUMBER for first/last, GROUP BY + SUM/COUNT for aggregate) change.
• First/last template. JOIN stream a ON a.entity_uuid = p.entity_uuid AND a.activity = '<appended>' AND a.ts <cmp> p.ts then QUALIFY ROW_NUMBER() OVER (PARTITION BY p.entity_uuid, p.ts ORDER BY a.ts <ASC|DESC>) = 1. ASC = first, DESC = last; < = before, >= = after.
• Aggregate-before/after template. Same self-join with a ts inequality, but LEFT JOIN + GROUP BY p.entity_uuid, p.ts + COALESCE(SUM(a.revenue_impact),0). Always LEFT JOIN so a zero aggregate is representable; COALESCE the result.
• In-between template. Bound the appended activity by [primary.ts, next_primary.ts) where next_primary.ts = LEAD(ts) OVER (PARTITION BY entity_uuid ORDER BY ts) over the primary. Handle the final next_* IS NULL interval or the last session drops its rows.
• Datasets = primary + appended relationships. Pick a primary (one row per occurrence), append relationships as columns (each resolves to one value per primary, so no fan-out), LEFT JOIN every relationship, materialise incrementally. Slice dimensions are feature_json fields hoisted onto the primary — no dim_* join needed.
• Customer 360 + funnels + cohorts. Customer 360 = primary first signup + many aggregate all ever / last ever relationships. Funnel = chain first after stage-to-stage (anchor each stage on the previous stage's timestamp, not the entry). Cohort = funnel grouped by a bucketed primary ts; retention = a bounded first after (ts < primary + window).
• Clustering (mandatory at scale). Snowflake CLUSTER BY (entity_uuid, ts); BigQuery PARTITION BY DATE(ts) CLUSTER BY entity_uuid, activity. Verify partition pruning in the query profile. Unclustered temporal self-joins full-shuffle a billion-row stream — clustering is the difference between O(events/entity) and O(N²).
• Tooling. narrator (originated the pattern; UI-generated relationships/datasets), dbt activity-schema packages (relationships as reusable macros), or plain warehouse-native SQL + windows + clustering. Point BI at materialised datasets, never the raw stream, so dashboards do not each pay for the self-joins.
• vs star / OBT + migration. Activity schema wins journeys, time-to-event, retention, auditable replay, onboarding; star/OBT win many-dimension financial slice-and-dice and pre-aggregated cubes. OBT denormalises one process wide; the activity schema unifies all processes narrow-and-long. Migrate by backfilling the stream from existing facts via UNION ALL, re-expressing journeys as macros, keeping finance in the star, and gating deprecation on a parity check.

Frequently asked questions

What is an activity schema?

An activity schema is a data-modeling approach that stores every meaningful thing an entity did as a single row in one append-only, immutable activity stream table, at exactly one grain: one row per (entity_uuid, activity, ts). Instead of a star schema's many fact tables and conformed dimensions, it keeps a narrow set of canonical columns — entity_uuid, ts, activity, feature_json, revenue_impact, link — and answers analytics questions by joining that one table to itself along time. It is a form of event-based modeling optimised for customer journeys, funnels, retention, and customer 360, and it is best understood as a dimensional modeling alternative for sequence-shaped questions rather than a replacement for every warehouse mart.

Activity schema vs star schema — how do they differ?

A star schema models each business process as its own fact table surrounded by conformed dimensions, which is excellent for slicing one measure across many dimensions (revenue by plan by region by month) but expensive to maintain: every new process is a new fact, a re-conformed dimension, and a fresh grain to reason about. An activity schema collapses every process into one single table at one grain, so a new process is just a new activity value and a journey question is one temporal self-join instead of a multi-fact join. The star wins financial slice-and-dice and pre-aggregated cubes; the activity schema wins journeys, time-to-event, and fast onboarding. Most mature stacks run a hybrid — activity schema for journeys, a star for finance — and reconcile the two.

What is the activity stream table?

The activity stream is the single physical table that is the activity schema. Its canonical columns are entity_uuid (the subject and join key), ts (the event timestamp and ordering key), activity (a short verb from a controlled vocabulary), feature_json (a narrow bag of per-activity attributes), revenue_impact (money hoisted to a top-level column because it is aggregated constantly), and link (a drill-through pointer to the source record). It is append-only and immutable — corrections arrive as new activities rather than in-place updates — which makes it replayable and time-travelable. Optional pre-computed helpers, activity_occurrence and activity_repeated_at, let the two hottest relationships skip a self-join, and clustering by (entity_uuid, ts) is what makes it perform.

What are the relationships in the activity schema?

The relationships are a fixed vocabulary of about eleven temporal joins that relate a chosen primary activity to an appended activity on the same stream: first ever, last ever, first before, last before, first after, last after, first in between, last in between, aggregate all ever, aggregate before, and aggregate after. Every one is structurally the same self-join — ON appended.entity_uuid = primary.entity_uuid with a ts predicate — and only two things vary: the time predicate (before/after/ever) and the pick (first/last via QUALIFY ROW_NUMBER, or an aggregate via GROUP BY + SUM/COUNT). Because they all share one shape, they compose: a dataset is a primary plus several appended relationships, and customer 360 is simply the widest such composition.

Does the activity schema perform at scale?

Yes, if you cluster and pre-compute; no, if you do not. The pattern's cost is that every relationship is a self-join with a ts inequality, and on a billion-row stream an unclustered self-join can degrade to a full shuffle. The fixes are concrete: cluster the stream on (entity_uuid, ts) (Snowflake) or partition by day and cluster by entity_uuid, activity (BigQuery) so joins prune to a few micro-partitions per entity; pre-compute activity_occurrence and activity_repeated_at so the hottest relationships skip the join; keep rows narrow with hot columns hoisted; and materialise datasets (funnels, 360) so BI tools read a small table rather than re-running self-joins. With those levers the effective cost is roughly O(events per entity), not O(N²).

When should I NOT use an activity schema?

Do not use it for heavy financial slice-and-dice — questions like "recognised revenue by plan by region by month by currency with QoQ and YoY," which want real conformed dimension tables with SCD history, a date dimension, and pre-aggregated cubes. Forcing that onto a narrow stream means pivoting on every query and cramming dimensions into feature_json, which is slower, harder to audit, and error-prone; a purpose-built star (or an OBT for the BI tool) is both cheaper and clearer. Also avoid it when the team is not comfortable reasoning about temporal joins, or when the warehouse cannot cluster the stream. The senior answer is a hybrid: activity schema for journeys, a star for finance, reconciled every period.

Practice on PipeCode

• Drill the dimensional-modeling practice library → for star-schema, OBT, and activity-schema design questions senior interviewers love.
• Rehearse the joins on the self-join practice library → for the temporal-relationship, first-after, and aggregate-before patterns.
• Sharpen the event angle with the event-modeling practice library → for funnels, cohorts, retention, and customer-360 datasets built from a single stream.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the activity-schema-vs-star decision against real graded inputs.

Turn the activity schema into muscle memory

Docs explain the pattern; PipeCode drills explain the decision — when the single-table activity stream beats a star, how the eleven temporal relationships compose into a funnel, when clustering saves you from a full-shuffle, and when finance slice-and-dice means you reach for a star instead. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the modeling trade-offs analytics engineers actually face.

Practice dimensional-modeling problems →
Practice design problems →