> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omni.co/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.omni.co/feedback

```json
{
  "path": "/guides/patterns/thin-dimension-spine",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

# Thin dimensional spine for multi-fact event analysis

> Analyze multiple event streams together without fanout using a thin dimensional spine.

export const categoryIcons = {
  'administration': 'lock',
  'api': 'terminal',
  'connections': 'database',
  'dashboards': 'table-columns',
  'embed': 'code',
  'errors': 'exclamation',
  'modeling': 'wrench',
  'patterns': 'plus',
  'schedules & alerts': 'envelope',
  'visualizations': 'chart-column',
  'workbooks': 'book'
};

export const GuideSidebar = ({category, relatedLinks, updatedDate}) => {
  const [progress, setProgress] = React.useState(0);
  React.useEffect(() => {
    const sidebar = document.querySelector('.guide-sidebar');
    if (!sidebar) return;
    let container = sidebar.parentElement;
    while (container && !container.querySelector('.guide-header')) {
      container = container.parentElement;
    }
    if (container && !container.classList.contains('guide-page-layout')) {
      container.classList.add('guide-page-layout');
    }
  }, []);
  React.useEffect(() => {
    const handleScroll = () => {
      const scrollTop = window.scrollY;
      const docHeight = document.documentElement.scrollHeight - window.innerHeight;
      const scrollPercent = docHeight > 0 ? scrollTop / docHeight * 100 : 0;
      setProgress(Math.min(100, Math.max(0, scrollPercent)));
    };
    window.addEventListener('scroll', handleScroll, {
      passive: true
    });
    handleScroll();
    return () => window.removeEventListener('scroll', handleScroll);
  }, []);
  const icon = category ? categoryIcons[category.toLowerCase()] || 'book' : 'book';
  return <aside className="guide-sidebar">
      <div className="guide-sidebar-content">
        <a href="/guides" className="guide-sidebar-back">
          <Icon icon="arrow-left" iconType="solid" size={14} />
          <span>All guides</span>
        </a>

        <div className="guide-sidebar-section">
          <div className="guide-sidebar-label">Progress</div>
          <div className="guide-sidebar-progress">
            <div className="guide-mascot">
              <svg viewBox="0 0 450 450" width="48" height="48">
                <defs>
                  <clipPath id="progressClip">
                    <rect x="0" y={450 - progress * 4.5} width="450" height={progress * 4.5} />
                  </clipPath>
                  <linearGradient id="blobbyGradient" x1="55.9753" y1="0" x2="492.197" y2="169.724" gradientUnits="userSpaceOnUse">
                    <stop stopColor="#BCA2F3" />
                    <stop offset="0.572917" stopColor="#FF7AA2" />
                    <stop offset="1" stopColor="#F3D4A2" />
                  </linearGradient>
                </defs>

                {}
                <circle cx="223.901" cy="223.901" r="213.901" transform="matrix(-0.999988 -0.0049013 0.00491945 -0.999988 447.797 449.992)" fill="#FAFAFA" stroke="#480B38" strokeWidth="20" />

                {}
                <circle cx="223.901" cy="223.901" r="213.901" transform="matrix(-0.999988 -0.0049013 0.00491945 -0.999988 447.797 449.992)" fill="url(#blobbyGradient)" stroke="#480B38" strokeWidth="20" clipPath="url(#progressClip)" />

                {}
                <path d="M310.41 195.084C310.41 200.052 301.362 212.472 284.328 212.472C266.585 212.472 258.246 201.294 258.246 195.912" stroke="#480B38" strokeWidth="17.3883" strokeMiterlimit="1.33344" strokeLinecap="round" />
                <circle cx="21.168" cy="21.168" r="21.168" transform="matrix(-1 0 0 1 388.658 169.001)" fill="#480B38" />
                <circle cx="21.168" cy="21.168" r="21.168" transform="matrix(-1 0 0 1 223.467 169.001)" fill="#480B38" />
              </svg>
            </div>
            <span className="guide-sidebar-progress-text">{Math.round(progress)}%</span>
          </div>
        </div>

        {category && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Category</div>
            <div className="guide-sidebar-category">
              <Icon icon={icon} iconType="solid" size={14} />
              <span>{category}</span>
            </div>
          </div>}

        {updatedDate && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Last updated</div>
            <div className="guide-sidebar-date">{updatedDate}</div>
          </div>}

        {relatedLinks && relatedLinks.length > 0 && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Related</div>
            <ul className="guide-sidebar-links">
              {relatedLinks.map((link, index) => <li key={index}>
                  <a href={link.href}>{link.title}</a>
                </li>)}
            </ul>
          </div>}
      </div>
    </aside>;
};

export const GuideTitle = ({title}) => {
  return <div className="guide-header">
      <h1 className="guide-title">{title}</h1>
    </div>;
};

<GuideSidebar
  categoryIcons={categoryIcons}
  category="patterns"
  updatedDate="February 2026"
  relatedLinks={[
{ title: "Topics", href: "/modeling/topics" },
{ title: "Relationships", href: "/modeling/relationships" },
{ title: "Templated filters", href: "/modeling/templated-filters" },
{ title: "Measures", href: "/modeling/measures" }
]}
/>

<GuideTitle title="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:

```yaml title="Avoid this pattern, which joins everything together" theme={null}
calendar x users x all event tables
```

You model this:

```yaml title="Use this pattern, which creates a thin spine of activity" theme={null}
(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\_id** | **event\_date\_day** | **event\_type** |
| :----------- | :------------------- | :-------------- |
| 101          | 2025-01-10           | login           |
| 101          | 2025-01-10           | session\_start  |
| 101          | 2025-01-10           | ad\_impression  |
| 204          | 2025-01-10           | iap\_purchase   |

<Tip>
  **Using Snowflake?** Cluster your spine by (`event_date_day`, `event_type`) to improve query performance.
</Tip>

### 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:
  ```sql theme={null}
  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:

<Columns cols={2}>
  <Card title="Build-time cost" icon="hand-holding-dollar" horizontal>
    The spine intentionally shifts cost from query-time to build-time
  </Card>

  <Card title="Reduce effective scan size" icon="arrow-down" horizontal>
    Pruning makes the effective scan much smaller than the full table size
  </Card>

  <Card title="Improve predictability" icon="list-check" horizontal>
    CTE-only patterns can complicate cohort analysis and reusability
  </Card>

  <Card title="Semantic stability" icon="anchor" horizontal>
    The spine creates a stable semantic surface for downstream analysis
  </Card>
</Columns>

## 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](/integrations/dbt)
* Familiarity with [modeling](/modeling) in Omni

<Steps>
  <Step title="Build a generate_event_spine_from_models dbt macro" titleSize="h3">
    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

    ```sql title="generate_event_spine_from_models macro" expandable theme={null}
    {% 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 %}
    ```
  </Step>

  <Step title="Build an incremental dim__user_event_spine_thin dbt model" titleSize="h3">
    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

    ```sql title="dim__user_event_spine_thin model" expandable theme={null}
    {% 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 %}
    ```
  </Step>

  <Step title="Build the topic in Omni" titleSize="h3">
    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.

    ```yaml title="user_activity.topic file" expandable highlight={3} theme={null}
    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
        )
      )
    ```
  </Step>

  <Step title="Curate the topic fields" titleSize="h3">
    To make sure users filter consistently on the date spine, hide timestamps and non-common dimensions within the event views:

    ```yaml title="Snippet of user_activity.topic" expandable theme={null}
    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
    ```
  </Step>

  <Step title="Define relationships" titleSize="h3">
    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](/analyze-explore/sql/symmetric-aggregates).
    * **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.

    <Warning>
      Every additional event table follows this same structure. Repeat this pattern when you add new events.
    </Warning>

    ```yaml title="Omni Relationships file" expandable theme={null}
    # 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
    ```
  </Step>

  <Step title="Build an example analysis" titleSize="h3">
    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](/analyze-explore), 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
  </Step>

  <Step title="Optional: Add new event streams" titleSize="h3">
    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
  </Step>
</Steps>

## 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](/modeling/topics/best-practices).

### 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:

```yaml title="event__login.view (illustrative)" theme={null}
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](/guides/patterns/spine-cohort-analysis).