Dimension modeling

An event row says what happened. A dimension table says who it happened to: the user's country, the company's industry, the plan the account pays for. Mitzu joins dimension tables to event tables so you can filter and break down any event, funnel or retention insight by these properties, and so you can find users and companies by them in lookup.

This page covers the shape Mitzu expects, how the join works, and how to model properties that change over time. Adding a table in the app is described on the dimension tables settings page.

The shape of a dimension table

A dimension table has one row per entity and one column per property. A user dimension table looks like this:

user_id	email	country	signup_date	plan	is_paying
u_17	ana@example.com	US	2025-11-03	pro	true
u_42	ben@example.com	DE	2026-02-14	free	false

• Primary key. One column identifies the entity, here user_id. Its values must match the ID column for that entity on your event tables.
• Properties. Every other column becomes a dimension property. Nested JSON, MAP, VARIANT and STRUCT columns are expanded into one property per key, just like on event tables.
• No event time or event name. A dimension table describes the entity, not something it did.

Each dimension table belongs to one entity. User is built in. Add others, such as Company, Team or Workspace, on the Entities page, then set the matching ID column on your event tables. A company dimension table looks the same, keyed on company_id:

company_id	name	industry	employee_count	plan
c_102	Acme Ltd	Manufacturing	1200	enterprise
c_217	Bright Labs	Software	45	starter

Several tables for one entity

An entity can have more than one dimension table, for example user profile data from your product database and user attributes from your CRM. Mitzu joins only the tables an insight actually uses. When two tables have columns with the same name, set a Display name prefix on each table, such as CRM , so the properties are easy to tell apart.

Computed properties

Properties derived from events, such as the first purchase date, lifetime order count or the acquisition channel of the first visit, are best calculated in the warehouse, for example in a dbt model, and stored as dimension columns. They are then defined once, read cheaply, and match the numbers in your other reports.

How Mitzu joins a dimension table

When an insight uses a dimension property, Mitzu adds a LEFT JOIN from the event table to the dimension table, on the event table's ID column for that entity and the dimension table's primary key:

SELECT ...
FROM analytics.checkout_completed AS events
LEFT JOIN analytics.users AS users
    ON events.user_id = users.user_id
WHERE users.country = 'DE'

Two consequences follow from this:

• Events without a matching row are kept. Their dimension properties are empty (NULL). A filter such as country is DE drops them, and a break down puts them in an empty group.
• Every extra row per key duplicates events. If a user has two rows, each of that user's events is joined twice and counted twice. Event counts, sums and averages then come out too high.

note

On ClickHouse, Mitzu joins dimension tables with ANY LEFT JOIN, which keeps one matching row per event. Extra rows per key don't inflate counts there, but each event picks an arbitrary one of them, so its properties can be wrong without any visible sign.

Check the key before adding a table, on every warehouse:

SELECT user_id, count(*)
FROM analytics.users
GROUP BY user_id
HAVING count(*) > 1

Tables that legitimately hold several rows per entity, such as a history of plan changes or a daily snapshot, need a custom join condition so that each event matches exactly one row, on ClickHouse too. The next section shows how.

Slowly changing dimensions

Some properties change: a user upgrades from free to pro, a company moves to a new country, an account manager is reassigned. Warehouses handle this with slowly changing dimensions (SCD), and the type you choose decides what Mitzu shows for events that happened before the change.

Take a user who signs up on the free plan in January and upgrades to pro on 1 February. The question "how many checkouts did free users make in January?" has two possible answers:

Approach	The user's January checkouts are counted as	Answers
Current state (SCD Type 1)	pro	"What do today's pro users do, across their whole history?"
Point in time (SCD Type 2 or snapshots)	free	"What did users do while they were on each plan?"

Neither is wrong; they answer different questions. Mitzu supports both, and you can offer both side by side.

Current state: SCD Type 1

A Type 1 table keeps one row per entity and overwrites it when a property changes. This is the default dimension table described above, and it needs no extra configuration. Every event, old or new, sees the entity's current properties.

Use it for properties that rarely change or where the latest value is what matters, such as country, signup date or the acquisition channel.

If your warehouse holds the history (Type 2, below) and you also want the current state, add a view that keeps only the current row, and register the view as an ordinary dimension table:

CREATE VIEW analytics.users_current AS
SELECT *
FROM analytics.users_history
WHERE valid_to IS NULL

Point in time: SCD Type 2

A Type 2 table keeps a row for every version of an entity, each with the period it was valid for. A new row is added when a property changes, and the previous row is closed:

user_id	plan	country	valid_from	valid_to
u_42	free	DE	2026-01-05 09:12:00	2026-02-01 00:00:00
u_42	pro	DE	2026-02-01 00:00:00	NULL
u_17	pro	US	2025-11-03 15:40:00	NULL

The current version has an open end: valid_to is NULL. dbt snapshots produce exactly this shape, with the columns dbt_valid_from and dbt_valid_to.

To make each event see the version that was valid at its event time, add the table as a dimension table and set this Custom join condition in the Configure dimension table modal:

__evt_user_id == __dim_user_id
and __evt_event_time >= __dim_valid_from
and (__dim_valid_to == None or __evt_event_time < __dim_valid_to)

Mitzu then joins on the user and the validity period:

LEFT JOIN analytics.users_history AS d1
    ON events.user_id = d1.user_id
    AND events.event_time >= d1.valid_from
    AND (d1.valid_to IS NULL OR events.event_time < d1.valid_to)

The user's January checkouts now count as free and February's as pro. Filters, break downs and property aggregations in segmentation, funnel, retention and journey insights all use the version valid at each event.

For a dbt snapshot, the condition is:

__evt_user_id == __dim_user_id
and __evt_event_time >= __dim_dbt_valid_from
and (__dim_dbt_valid_to == None or __evt_event_time < __dim_dbt_valid_to)

Keep these rules in mind:

• Include the key. A custom join condition replaces the primary-key join completely. Without __evt_user_id == __dim_user_id, every event would match the rows of every user.
• Make periods half-open. Use >= on the start and < on the end, so an event that falls exactly on a change matches one version, not two.
• Don't let periods overlap. Two versions valid at the same moment duplicate the events in that moment, just like a duplicate key does. On ClickHouse, each of those events gets one of the two versions at random.
• Cover the whole history. Events before the first valid_from match no row and have empty properties. If the history starts later than your events, start each user's first version at the signup time, or at a date before your earliest event.
• Use the same time zone for valid_from, valid_to and the event time.

Point in time: daily snapshots

Some pipelines save a full copy of the dimension table every day instead of a change history:

snapshot_date	user_id	plan	seats
2026-01-31	u_42	free	1
2026-02-01	u_42	pro	5
2026-02-01	u_17	pro	12

Match each event to the snapshot of its day:

__evt_user_id == __dim_user_id and date(__evt_event_time) == __dim_snapshot_date

Snapshots are simpler to build than a Type 2 table, but they need a row for every entity on every day you have events, and they can only tell the state at day granularity. A change during the day applies to the whole day. date() uses the time zone of the warehouse session, so store the event time in the same zone as the snapshot dates.

tip

On ClickHouse, use daily snapshots. Older ClickHouse versions reject range comparisons such as >= and < in a join condition with Unsupported JOIN ON conditions, while the equality join of a snapshot works on every warehouse.

Offering both views

Analysts often want both answers. Register the current view and the history as two dimension tables of the same entity, each with its own Display name prefix, for example Current and At event . A property then appears twice, as Current Plan and At Event Plan, and the analyst picks the question they mean.

Where point-in-time joins apply

The custom join condition is used wherever Mitzu joins a dimension table to events or to a metric table. Some features read a dimension table on its own, without an event to take the time from. They read every row of a history or snapshot table:

• Lookup and lookup cohorts match an entity when any of its versions matches. Plan is pro finds every user who was ever on pro, and a user can appear once per version.
• Listing the users behind an insight joins on the key only, so the property columns may show any of the user's versions.
• Metric tables use the condition with __evt_ referring to the metric table's columns. A history table is only joined to a metric table that has every __evt_ column the condition uses, for example a user_id and an event_time column.

For lookup and cohorts by current properties, register a current-state view as described above and use its properties there.

warning

Range joins are more expensive than key joins, because the warehouse compares every event with every version of its user. Cluster or sort the history table by the key, and keep the number of versions down by only recording changes to the properties you analyze.

Custom join condition reference

A custom join condition is an expression over two kinds of placeholders:

Placeholder	Refers to
__evt_<column>	A column of the event table (or the metric table)
__dim_<column>	A column of the dimension table

The condition must use at least one placeholder of each kind. It can contain:

• comparisons: == (or =), !=, <, <=, >, >=. Chains such as __dim_valid_from <= __evt_event_time < __dim_valid_to are read as two comparisons joined with and,
• and, or, not and parentheses,
• arithmetic: +, -, *, /,
• function calls, which are passed to the warehouse as written, for example date(__evt_event_time). Use a function your warehouse has: Microsoft Fabric, for example, has no date(),
• numbers, quoted strings such as '2026-01-01', and None. Comparing with None checks for a missing value: __dim_valid_to == None becomes valid_to IS NULL, and != None becomes IS NOT NULL.

The condition can span several lines, and # starts a comment that runs to the end of its line. Mitzu checks the syntax and the placeholders when you save it. It doesn't check that the columns exist, so test the table with a simple insight after saving.

Checklist

• One row per entity, or a custom join condition that matches exactly one row per event.
• A primary key whose values match the entity ID column on the event tables.
• Derived properties calculated in the warehouse.
• For history tables: half-open, non-overlapping periods that cover your event history, and a current-state view for lookup.
• Display name prefixes where tables share column names.

Next: add your dimension tables, or bring warehouse-calculated numbers in with metric tables.