Bringing denormalized and rollup tables into Holistics

Question

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 for a single fact table, or Galaxy schema once you have several sharing dimensions.

Register your table as a pre-aggregate

Use ExternalPersistence 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:

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:

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:

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:

Table daily_agent_summary {
  ticket_date date
  agent_name varchar
  total_tickets integer
}

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 and 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
• Data models in Holistics
• Relationships overview
• Datasets overview