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

# Multi-Grain Tables

## Introduction

A **multi-grain table** or a **mixed-grain table** mixes two or more such levels in the same structure. For instance:

* A sales fact table that stores daily totals by store but also includes monthly totals in the same table.
* A web analytics table where some events are logged at the session level and others at the page-view level.
* A financial fact table that records both transaction-level entries and quarterly adjustments.

The grain of a table is the level of detail at which each row is stored.

A fine grain example: one row per order line item (most detailed).
A coarser grain example: one row per order or one row per day.

In general, multi-grain data falls in two categories that lead to different modeling approaches:

1. **Mixed-Grain**: Each data point has a single grain, but they are mixed: some web events are at session-level and some are at page-view level.
2. **Multi-Grain**: Each data point has multiple grains: store sales are recorded both daily and monthly.

<Tip>
  **Why it matters**

  1. Complicates joins and aggregations: Queries can double-count if the analyst doesn't know which grain they are working with.
  2. Harder governance: Business logic like “one row = one transaction” no longer holds.

  Honeydew can enforce single-grain consistency to avoid ambiguity.
</Tip>

## Modeling Category 1: Different data, different grains ("Mixed-Grain")

*Each data point exists at a single grain, but grains differ across event types (e.g., session-level vs page-view-level events).*

To model different data recorded at different grains, separate grains to different fact tables.

For example, for web analytics table that mixes session-level and page-view level events, separate to:

* `fact_sessions` (grain = session)
* `fact_pageviews` (grain = page view), typically connected to its session by `session_id` with a many:1 relationship.

<Tip>
  Don't denormalize into one wide table — it introduces double-counting traps and complex modeling.
</Tip>

## Modeling Category 2: Same data, multiple versions ("Multi-Grain")

*Each data point exists at more than one grain (e.g., store sales recorded daily and monthly).*

The is a more complex case as that introduces **duplicated data** into the semantic data.

Honeydew supports modeling multi-grain tables:

1. Ensure join consistency without row duplication.
2. Adjust metric logic by the chosen grain.
3. Hide the "multi-grain" data modeling complexity from the business user or AI agent.

### Multiple grain tables for same data

While a multi-grain table is a *single* table with data at different grains, some data models have
separate tables for the same data, each at a different grain, but not mixed together.

For example:

* `sales_daily_fact`: 1 row per store per day.
* `sales_monthly_fact`: 1 row per store per month. The computation would include end-of-month adjustments, so it is not a simple transformation from `sales_daily_fact`.

To handle this case in Honeydew, transform multiple grain tables into a **single** multi-grain entity.

Create an entity with [Custom SQL](/modeling/source-data#custom-sql) that UNIONs the facts:

```sql theme={null}
SELECT 'daily' as grain, * FROM sales_daily_fact
UNION ALL
SELECT 'monthly' as grain, * FROM sales_monthly_fact
```

<Tip>
  Always use `UNION ALL` to union multiple grains rather than `UNION` for significantly faster performance
</Tip>

### How to Handle Duplicate Grains

Consider a multi-grain fact `fact_sales` that records per store both the daily and the monthly sales volume, as indicated in the `grain` column.

If naively counting per store sales volume as `SUM(sales)`, the result would be wrong: it would double count daily and monthly totals.

When operating with multi grain data, must always deduplicate it.

There are two deduplication approaches:

1. At the domain level, applying to all calculations at once
2. At per-metric level, applying to only a particular metric

#### Domain-Level Deduplication

With domain-level deduplication, double counting is avoided by removing the duplicated data via
a [Domain Source Filter](/domains#source-filters) that enforces a choice of grain.

<Note>
  Domain-level deduplication ensures any single user query sees only one grain of the data - the one it chose to show.

  Different queries can operate at different grains, but no query can see multiple grains at once.
</Note>

1. Create a table called `dim_grain` with the list of possible grains. That would be used by the user to choose current grain by filtering on it.
2. Create a source filter in a domain that enforces a specific grain is chosen:

```yaml theme={null}
type: domain
name: all

entities:
  - fact_sales
  - ...
  # Grain choice entity
  - dim_grain

# Ensure a single grain is always chosen in the fact - 'daily' by default
source_filters:
  - name: daily_grain_filter
    sql: fact_sales.grain = GET_FIELD_SELECTION(dim_grain.grain, 'daily')
```

A metric that counts sales is simple:

```sql theme={null}
fact_sales.total_sales = SUM(fact_sales.sales)
```

Since it runs after the source filter is applied, it only counts over a single version of the data.

If different counting is required, can create more complex logic based on the currently chosen grain:

```sql theme={null}
fact_sales.total_sales =
    CASE GET_FIELD_SELECTION(dim_grain.grain)
        -- simple summing at a daily grain
        WHEN 'daily' THEN SUM(fact_sales.sales)
        -- different logic for monthly grain - include adjustments
        WHEN 'monthly' THEN SUM(fact_sales.sales - fact_sales.monthly_adjustment)
    END
```

<Tip>
  For more information about `GET_FIELD_SELECTION`, see [conditional filtering](/advanced-modeling/conditional-filtering).
</Tip>

#### Metric-Level Deduplication

With metric-level deduplication, double counting is avoided in metric definitions, but data itself stays duplicated.

<Warning>
  Metric level deduplication does not reduce data in the fact entity.

  It requires attention in every metric. Summing up sales without a filter (by mistake) would otherwise result in duplicated data.
</Warning>

Use this approach when duplicated data is required for cross-grain computation and a domain level deduplication is not applicable.

To create metric level deduplication use [filtered metrics](/calculations/metrics#filtered-metrics), such as:

```sql theme={null}
fact_sales.total_daily_sales =
    SUM(fact_sales.sales) FILTER (WHERE fact_sales.grain = 'daily')

fact_sales.total_monthly_sales =
    SUM(fact_sales.sales) FILTER (WHERE fact_sales.grain = 'monthly')
```

To avoid multiple metrics with multiple names, can also create a single metric that enforces deduplication
using [conditional filtering](/advanced-modeling/conditional-filtering):

```sql theme={null}
-- This metric is computed at the 'daily' level, unless the user chooses 'monthly' by filtering on fact_sales.grain = 'monthly'
-- Regardless to whether the user chose a grain or not, it will always compute correctly.
fact_sales.total_sales =
   SUM(fact_sales.sales) FILTER (WHERE fact_sales.grain = GET_FIELD_SELECTION(fact_sales.grain, 'daily'))
```

This can be further extended to handle different logic for different grains, such as:

```sql theme={null}
fact_sales.total_sales =
    CASE GET_FIELD_SELECTION(dim_grain.grain)
        -- simple summing at a daily grain
        WHEN 'daily' THEN SUM(fact_sales.sales) FILTER (WHERE fact_sales.grain = 'daily')
        -- different logic for monthly grain - include adjustments
        WHEN 'monthly' THEN SUM(fact_sales.sales - fact_sales.monthly_adjustment) FILTER (WHERE fact_sales.grain = 'monthly')
    END
```

<Tip>
  For more information about `GET_FIELD_SELECTION`, see [conditional filtering](/advanced-modeling/conditional-filtering).
</Tip>