Skip to main content

Thin dimensional spine for multi-fact event analysis

This guide walks you through building a thin dimensional spine in Omni — a modeling pattern that lets you analyze many independent event tables together by time and shared entity attributes without incurring fanout or poor performance. While the examples below use mobile gaming events (logins, sessions, ads, purchases), this pattern applies to any domain with multiple event streams sharing common dimensions, such as SaaS product events, marketplace activity, or IoT telemetry.

When to use this pattern

Use this pattern when:
  • You have many event tables sharing common dimensions like user_id.
  • Analysts want a single Topic for analysis across multiple event streams.
  • Queries typically touch only a subset of those event streams.
  • Performance and predictable SQL generation are priorities.

How to model thin spine tables

A common approach to multi-fact analysis is to model a massive join of calendar x users x all event tables. This quickly leads to fanout, slow queries, and unpredictable SQL. Instead, you can model a thin dimensional spine — a table where each row represents the existence of activity, not the details of the activity itself. Instead of this:
Avoid this pattern, which joins everything together
calendar x users x all event tables
You model this:
Use this pattern, which creates a thin spine of activity
(event_date_day, user_id, event_type)
Which you could use to create a spine table like dim__user_event_spine_thin. This table contains one row per (event_date_day, user_id, event_type) combination that actually occurred:
user_idevent_date_dayevent_type
1012025-01-10login
1012025-01-10session_start
1012025-01-10ad_impression
2042025-01-10iap_purchase
Using Snowflake? Cluster your spine by (event_date_day, event_type) to improve query performance.

Modeling a secondary date spine

In addition to the thin spine, you should maintain a date-only spine table (dim__date_spine) for calendar descriptors. This gives you a clean place for all calendar fields without bloating the thin spine. To build it:
  • Fields - Include attributes like is_weekday, is_us_federal_holiday, or fiscal calendar fields.
  • Join logic - Join to the topic on: 
    dim__user_event_spine_thin.event_date_day = dim__date_spine.date_day

Storage considerations

While the spine is logically thin, it can still become large in absolute storage terms. Keep these factors in mind:
  • Row counts - Each (user, time grain, event_type) combination produces a row
  • Granularity - Hourly (or finer) spines significantly multiply row counts
  • Pruning - Pruning helps query performance, but not storage footprint
To keep storage manageable:
  • Default to daily granularity unless finer-grain analysis is truly required
  • Use separate spines for different grains (e.g., a daily user spine and an hourly session spine)
  • Be deliberate about which event types you include in the spine

Spine materialization benefits

You may wonder whether it’s worth materializing the spine given its potential size. While the spine can grow large, the cost is a known and bounded trade-off:

Build-time cost

The spine intentionally shifts cost from query-time to build-time

Reduce effective scan size

Pruning makes the effective scan much smaller than the full table size

Improve predictability

CTE-only patterns can complicate cohort analysis and reusability

Semantic stability

The spine creates a stable semantic surface for downstream analysis

Build and model the spine with dbt and Omni

In this section, we’ll walk you through how to build and materialize the thin spine table using dbt and then model it in Omni. In this guide, we’re using the following event tables:
  • Engagement - event__login, event__session_start
  • Monetization and ads - event__iap_purchase, event__ad_impression
  • Shared fields - All event tables share user_id and event_ts (timestamp)

Requirements

To follow along, you’ll need:
  • Familiarity with dbt
  • Familiarity with modeling in Omni
1

Build a generate_event_spine_from_models dbt macro

First, you’ll build a generate_event_spine_from_models macro in dbt. This pattern uses the macro to derive event_type from model names and emit a UNION ALL spine query.The macro expects:
  • Event models are named like event__login, event__iap_purchase, etc.
  • event_type is the suffix after event__.
  • Each event model includes event_ts and user_id columns, or you override these via arguments
generate_event_spine_from_models macro
{% macro generate_event_spine_from_models(event_model_names,
  event_ts_col='event_ts',
  user_id_col='user_id',
  date_grain='day',
  event_prefix='event__',
  lower_bound_date_sql=None,
  upper_bound_date_sql=None) %}

{% if event_model_names is not iterable %}
  {{ exceptions.raise_compiler_error("generate_event_spine_from_models: event_model_names must be an iterable of strings") }}
{% endif %}

{% set selects = [] %}
{% for name in event_model_names %}
  {% if (name | trim).startswith(event_prefix) %}
    {% set event_type = (name | trim)[(event_prefix | length):] %}
  {% else %}
    {{ exceptions.raise_compiler_error("generate_event_spine_from_models: model " ~ name ~ " does not start with expected prefix.") }}
  {% endif %}

  {% set rel = ref(name | trim) %}

  {% set where_clauses = [] %}
  {% if lower_bound_date_sql is not none %}
    {% do where_clauses.append("to_date(date_trunc('"~date_grain~"', "~ event_ts_col ~ ")) >= " ~lower_bound_date_sql) %}
  {% endif %}
  {% if upper_bound_date_sql is not none %}
    {% do where_clauses.append("to_date(date_trunc('"~date_grain ~"', "~ event_ts_col~")) <= " ~ upper_bound_date_sql) %}
  {% endif %}

  {% set where_sql %}
    {% if where_clauses | length > 0 %} where {{ where_clauses | join("\n and ") }} {% endif %}
  {% endset %}

  {% set stmt %}
    select
      to_date(date_trunc('{{ date_grain }}', {{ event_ts_col }})) as event_date_day,
      {{ user_id_col }} as user_id,
      '{{ event_type }}' as event_type
    from {{ rel }}
    {{ where_sql }}
    group by 1, 2, 3
  {% endset %}
  {% do selects.append(stmt) %}
{% endfor %}

{{ selects | join("\nunion all\n") }}
{% endmacro %}
2

Build an incremental dim__user_event_spine_thin dbt model

The next step is to build a dim__user_event_spine_thin model in dbt.The spine model is incremental and supports two modes:
  1. Append-only - Only inserts new spine rows beyond what already exists
  2. Merge with a 7-day lookback window - Recomputes the most recent 7 days and merges into the target
dim__user_event_spine_thin model
{% set event_models = ['event__login', 'event__session_start', 'event__ad_impression', 'event__iap_purchase'] -%}
{% set mode = var('spine_incremental_mode', 'merge_7d') -%}
{% set loopback_days = var('spine_loopback_days', 7) -%}
{% set full_day_offset = var('spine_full_day_offset_days', 1) -%}

{{ config(
    materialized='incremental',
    unique_key=['event_date_day', 'user_id', 'event_type'],
    incremental_strategy='merge',
    cluster_by=['event_date_day', 'event_type']
)}}

with event_max_days as (
    {% for m in event_models %}
    select
        '{{m}}' as event_model,
        max(to_date(event_ts)) as max_event_day
    from {{ ref(m) }}
    {% if not loop.last %} union all {% endif %}
    {% endfor %}
),
bounds as (
    select
        min(max_event_day) as last_available_day,
        dateadd('day', -{{ full_day_offset }}, min(max_event_day)) as last_full_day
    from event_max_days
),
incremental_window as (
    select
        last_full_day,
        case
            when '{{ mode }}' = 'merge_7d'
            then dateadd('day', -{{ loopback_days }}, last_full_day)
            else null
        end as loopback_start_day
    from bounds
),
spine_src as (
    {{ generate_event_spine_from_models(
        event_model_names=event_models,
        event_ts_col='event_ts',
        user_id_col='user_id',
        date_grain='day',
        lower_bound_date_sql="(select loopback_start_day from incremental_window)",
        upper_bound_date_sql="(select last_full_day from incremental_window)"
    )}}
)
select
    event_date_day,
    user_id,
    event_type
from spine_src s
{% if is_incremental() %}
    {% if mode == 'append' %}
        where s.event_date_day > (select coalesce(max(event_date_day), to_date('1900-01-01')) from {{ this }})
        and s.event_date_day <= (select last_full_day from incremental_window)
    {% else %}
        where s.event_date_day <= (select last_full_day from incremental_window)
    {% endif %}
{% else %}
    where s.event_date_day <= (select last_full_day from incremental_window)
{% endif %}
3

Build the topic in Omni

Once the spine is built, configure your Omni topic to use it as the base view. This topic uses the secondary date spine for calendar descriptors and enforces a date filter for safety.
user_activity.topic file
label: User Activity
group_label: Multi-Fact Analytics
base_view: dim_user_event_spine_thin

joins:
  # Calendar descriptors — all date filtering should use this view
  dim__date_spine: {}

  # Attributes
  attr__users_profile: {}
  attr__users_progression: {}
  attr__users_monetization: {}
  attr__users_event_bounds: {}

  # Event tables
  event__login: {}
  event__session_start: {}
  event__ad_impression: {}
  event__iap_purchase: {}

# Default workbook filter (visible/removable)
default_filters:
  dim__date_spine.date_day:
    time_for_duration: [ 7 complete days ago, 7 days ]

# Required pruning / safety filters
always_where_sql: |
  (
    -- Require a date filter on the date spine.
    -- If a user removes the default date filter, this evaluates to FALSE and returns 0 rows.
    case when {{dim__date_spine.date_day.is_filtered}} then true else false end
  )
  AND
  (
    -- Prune event_type for optimal Snowflake partition performance
    ${dim__user_event_spine_thin.event_type} IN (
      case when {{ event__login.in_query }} then 'login' end,
      case when {{ event__session_start.in_query }} then 'session_start' end,
      case when {{ event__ad_impression.in_query }} then 'ad_impression' end,
      case when {{ event__iap_purchase.in_query }} then 'iap_purchase' end
    )
  )
4

Curate the topic fields

To make sure users filter consistently on the date spine, hide timestamps and non-common dimensions within the event views:
Snippet of user_activity.topic
views:
  event__login:
    dimensions:
      event_time:
        hidden: true
      login_method:
        hidden: true
      platform:
        hidden: true
  event__session_start:
    dimensions:
      event_time:
        hidden: true
      session_type:
        hidden: true
  event__iap_purchase:
    dimensions:
      event_time:
        hidden: true
      sku:
        hidden: true
      purchase_currency:
        hidden: true
5

Define relationships

The core philosophy of this pattern is to keep the thin spine minimal — it contains only join keys, event_type, and dates. All other metadata is joined in from external views.There are two key architectural decisions to understand:
  • Prevent unnecessary symmetric aggregation - Event relationships are explicitly set to one_to_one even though the actual cardinality is one_to_many. This prevents Omni from unnecessarily employing symmetric aggregation.
  • Minimal base - Many teams hide most or all dimensions on the thin spine so users primarily interact with date fields from the date spine, attributes from attr_ tables, and measures from event_ views.
Every additional event table follows this same structure. Repeat this pattern when you add new events.
Omni Relationships file
# Spine → date spine (calendar descriptors)
- join_from_view: dim__user_event_spine_thin
  join_to_view: dim__date_spine
  join_type: always_left
  on_sql: ${dim__user_event_spine_thin.event_date_day} = ${dim__date_spine.date_day}
  relationship_type: many_to_one

# Spine → attributes
- join_from_view: dim__user_event_spine_thin
  join_to_view: attr__users_profile
  join_type: always_left
  on_sql: ${dim__user_event_spine_thin.user_id} = ${attr__users_profile.user_id}
  relationship_type: many_to_one

- join_from_view: dim__user_event_spine_thin
  join_to_view: attr__users_progression
  join_type: always_left
  on_sql: ${dim__user_event_spine_thin.user_id} = ${attr__users_progression.user_id}
  relationship_type: many_to_one

- join_from_view: dim__user_event_spine_thin
  join_to_view: attr__users_event_bounds
  join_type: always_left
  on_sql: ${dim__user_event_spine_thin.user_id} = ${attr__users_event_bounds.user_id}
  relationship_type: many_to_one

# Spine → events (conditional joins)
- join_from_view: dim__user_event_spine_thin
  join_to_view: event__login
  join_type: always_left
  on_sql: |
    ${dim__user_event_spine_thin.event_type} = 'login'
    AND ${dim__user_event_spine_thin.user_id} = ${event__login.user_id}
    AND ${dim__user_event_spine_thin.event_date_day} = ${event__login.event_time[date]}
  relationship_type: one_to_one

- join_from_view: dim__user_event_spine_thin
  join_to_view: event__session_start
  join_type: always_left
  on_sql: |
    ${dim__user_event_spine_thin.event_type} = 'session_start'
    AND ${dim__user_event_spine_thin.user_id} = ${event__session_start.user_id}
    AND ${dim__user_event_spine_thin.event_date_day} = ${event__session_start.event_time[date]}
  relationship_type: one_to_one

- join_from_view: dim__user_event_spine_thin
  join_to_view: event__ad_impression
  join_type: always_left
  on_sql: |
    ${dim__user_event_spine_thin.event_type} = 'ad_impression'
    AND ${dim__user_event_spine_thin.user_id} = ${event__ad_impression.user_id}
    AND ${dim__user_event_spine_thin.event_date_day} = ${event__ad_impression.event_time[date]}
  relationship_type: one_to_one

- join_from_view: dim__user_event_spine_thin
  join_to_view: event__iap_purchase
  join_type: always_left
  on_sql: |
    ${dim__user_event_spine_thin.event_type} = 'iap_purchase'
    AND ${dim__user_event_spine_thin.user_id} = ${event__iap_purchase.user_id}
    AND ${dim__user_event_spine_thin.event_date_day} = ${event__iap_purchase.event_time[date]}
  relationship_type: one_to_one
6

Build an example analysis

With the spine materialized and the Omni model built, you can try out what you’ve created by building an analysis. In this example, you’ll build a cross-event analysis that looks at Week x Country with multi-event measures.In an Omni workbook, create a query with the following configuration:
  • Dimensions - dim__date_spine.date_day (grouped by week) and attr__users_profile.country
  • Measures - event__login.login_count, event__session_start.session_count, event__ad_impression.ad_impression_count, and event__iap_purchase.iap_revenue
  • Derived metric - avg_logins_per_user, calculated by dividing login count by spine-level distinct users
7

Optional: Add new event streams

This pattern is designed to make adding new event types mechanical and low-risk. To add a new event stream:
  1. dbt - Add the event model name to event_models in the spine SQL
  2. Omni topic - In the Omni topic, add event__new_event: {} to the joins section
  3. Pruning - Add a case when {{ event__new_event.in_query }} then 'new_event' end line to always_where_sql.
  4. Relationships - In the Omni model’s relationships file, add a conditional one_to_one relationship join matching on event_type, user_id, and date grain

Best practices for modeling spines in Omni

Now that you’ve walked through modeling spines in Omni, we have a few tips to reduce surprises in aggregation and filtering. Keep the following in mind when modeling in Omni.

General tips

  • Hide non-common dimensions - Expose only curated measures and optionally timestamps for drilling
  • Prefer curated metrics - Instead of raw fields like purchase_amount, expose measures like iap_revenue or payer_count
  • Keep cohort-defining fields in attribute space - Put cohort keys in attr__ tables so they behave consistently across event streams

Apply topic best practices

As your model grows, topics can become difficult to use if the field list gets too large or too heterogeneous. Keep these guidelines in mind:
  • Focus over mega-topics - Prefer multiple focused topics (e.g., engagement, monetization) over a single mega-topic
  • User-facing product - Treat topics as user-facing products with coherent scopes
  • Spine reusability - The thin spine is infrastructure — you can reuse it across multiple topics
For more topic design guidance, refer to the Topic design best practices guide.

Use filtered measures for event-specific breakdowns

Instead of exposing event-specific dimensions directly, create curated filtered measures. For example, if event__login has a login_method dimension, you can create specific measures for counts of Google or iCloud logins:
event__login.view (illustrative)
measures:
  login_count:
    aggregate_type: count
  google_login_count:
    label: Google Logins
    aggregate_type: count
    filters:
      event__login.login_method:
        is: google
  icloud_login_count:
    label: iCloud Logins
    aggregate_type: count
    filters:
      event__login.login_method:
        is: icloud

Build a cross-event analysis

With this pattern in place, you can build cross-event analyses like a weekly report by country. You would use:
  • Dimensions - dim__date_spine.date_day (grouped by week) and attr__users_profile.country
  • Measures - event__login.login_count, event__session_start.session_count, event__ad_impression.ad_impression_count, and event__iap_purchase.iap_revenue
  • Derived metrics - For example, avg_logins_per_user calculated by dividing login count by spine-level distinct users

Next steps

Now that you understand thin dimensional spines and how to build your own, you can use them to build different types of analysis. For example, start with the Build cohort analysis with thin spine guide.