# Bringing denormalized and rollup tables into Holistics

> Learn when a denormalized (pre-joined) or rollup (pre-aggregated) table makes sense in Holistics, and how to keep it fast without giving up a reusable model

## Question

:::info Question
"My dbt layer already handles cleaning, joining, and some aggregation, so I've got wide, pre-joined tables (denormalized) and a few rollup/summary tables (pre-aggregated) built for performance.

What's the best way to bring these into Holistics: model on top of them directly, or break everything back into normalized, fact and dimension tables?"
:::

## Answer

This is a common question when working with bringing data tables into BI/analytics tools: choosing between pre-joined/pre-aggregated tables and normalized tables.

Normally for most analytics tools, it's a real tradeoff:

| | Pre-joined / pre-aggregated tables | Normalized (fact + dimension) tables |
|---|---|---|
| Query performance | Fast, nothing to join or aggregate at query time | Slower, Holistics joins and aggregates at query time |
| Self-service flexibility | Rigid: only the dimensions baked in at build time are queryable, so it limits the range of questions end users can ask | Flexible: any dimension can be joined in, so end users aren't limited to what was pre-selected |
| Maintainability | Dimension values are duplicated in every such table (`flatten_orders`, `flatten_users`, etc.), so they can drift out of sync when something changes upstream | Dimensions live in one place, so there's a single source of truth to update |

In Holistics, **you don't have to choose.**

Holistics allows you to use _normalized data models_ for self-serve flexiblity, while utilizing the _pre-joined, pre-aggregated tables behind the scene_ for better performance. The engine automatically serves matching queries from those tables underneath, so end users get a clean, flexible model without giving up speed.

For normalized table modeling patterns, see [Star schema](/docs/modeling/modeling-patterns/star-schema) for a single fact table, or [Galaxy schema](/docs/modeling/modeling-patterns/galaxy-schema) once you have several sharing dimensions.

## Register your table as a pre-aggregate

Use [`ExternalPersistence`](/docs/aggregate-awareness/persistence#external-persistence) to point Holistics at your existing table. This works the same way whether your table is pre-joined (flattened, but at the same grain as the fact) or pre-aggregated (rolled up to a coarser grain, like daily or regional totals): either way, Holistics treats it as a precomputed table it can route matching queries to.

Say your normalized model is built on these source tables:

```dbml
Table tickets {
  id integer [pk]
  agent_id integer
  customer_id integer
  created_at datetime
}

Table agents {
  id integer [pk]
  name varchar
}

Table customers {
  id integer [pk]
  tier varchar
}
```

### Pre-joined (denormalized) table

Your existing pre-joined table might look like this, one row per ticket with the agent and customer attributes already flattened in:

```dbml
Table flatten_tickets {
  ticket_id integer [pk]
  agent_name varchar
  customer_tier varchar
  created_at datetime
}
```

Register it as a pre-aggregate on your `fct_tickets` model. Since `flatten_tickets` is at the same grain as `fct_tickets` (one row per ticket, no precomputed counts), map `ticket_id` as a dimension instead of a measure. Holistics then computes `total_tickets` as `COUNT(ticket_id)` at query time:

```aml
Dataset support {
  models: [fct_tickets, dim_agents, dim_customers]

  pre_aggregates: {
    agg_tickets_flat: PreAggregate {
      dimension ticket_id {
        for: r(fct_tickets.id)
      }
      dimension agent_name {
        for: r(dim_agents.name)
      }
      dimension customer_tier {
        for: r(dim_customers.tier)
      }
      dimension created_at {
        for: r(fct_tickets.created_at)
        type: 'datetime'
      }

      persistence: ExternalPersistence {
        table_name: 'flatten_tickets'
      }
    }
  }
}
```

### Pre-aggregated (rollup) table

A pre-aggregated (rollup) table works the same way, just at a coarser grain. Your rollup table might look like this, one row per agent per day:

```dbml
Table daily_agent_summary {
  ticket_date date
  agent_name varchar
  total_tickets integer
}
```

```aml
Dataset support {
  models: [fct_tickets, dim_agents]

  pre_aggregates: {
    agg_tickets_daily_agent: PreAggregate {
      dimension agent_name {
        for: r(dim_agents.name)
      }
      dimension ticket_date {
        for: r(fct_tickets.created_at)
        type: 'date'
      }
      measure total_tickets {
        for: r(fct_tickets.id)
        aggregation_type: 'count'
      }

      persistence: ExternalPersistence {
        table_name: 'daily_agent_summary'
      }
    }
  }
}
```

Thanks to [dimension awareness](/docs/aggregate-awareness/dimension-awareness) and [join awareness](/docs/aggregate-awareness/join-awareness), Holistics automatically routes matching queries to your existing table instead of joining or aggregating at query time. (The pre-aggregate's dimension and measure names just need to match your table's column names.)

**The result:** end users and other datasets see a clean, reusable, normalized model. Underneath, eligible queries transparently hit your fast, pre-joined or pre-aggregated table. You get the flexibility of normalized modeling and the performance of your existing tables, without picking one over the other.

## Additional resources

- [Aggregate Awareness overview](/docs/aggregate-awareness)
- [Data models in Holistics](/docs/data-model)
- [Relationships overview](/docs/relationships)
- [Datasets overview](/docs/datasets/)