ClickStack - Materialized Views

Edit the source

#### Edit the source[​](#edit-the-source "Direct link to Edit the source")

Navigate to the relevant **source** in HyperDX and open the **Edit configuration** dialog. Scroll to the section for materialized views.

Add the materialized view

#### Add the materialized view[​](#add-the-materialized-view "Direct link to Add the materialized view")

Select **Add materialized view**, then choose the database and target table that back the materialized view.

Select metrics

#### Select metrics[​](#select-metrics "Direct link to Select metrics")

In most cases, the timestamp, dimension, and metric columns will be inferred automatically. If not, specify them manually.

For metrics, you must map:

- The original column name, for example, `Duration` to
- The corresponding aggregate column in the materialized view, for example `avg__Duration`

For dimensions, specify all the columns, other than the timestamp, that the view groups by.

Select the time granularity

#### Select the time granularity[​](#select-time-granularity "Direct link to Select the time granularity")

Select the **time granularity** of the materialized view, for example, one minute.

Select the minimum date

#### Select the minimum date[​](#specify-the-minimum-date "Direct link to Select the minimum date")

Specify the minimum date for which the materialized view contains data. This represents the earliest timestamp available in the view and is typically the time at which the view was created, assuming ingestion has been continuous.

Note

Materialized views are **not automatically backfilled** when they're created, so they will only contain rows generated from data inserted after creation.
A full guide on backfilling materialized views can be found under ["Backfilling Data."](/docs/data-modeling/backfilling#scenario-2-adding-materialized-views-to-existing-tables)

If the exact start time is unclear, you can determine it by querying the minimum timestamp from the target table, for example:

```
SELECT min(Timestamp) FROM otel_traces_1m
```

Save the source

#### Save the source[​](#save-the-source "Direct link to Save the source")

Save the source configuration.

Once a materialized view has been registered, it's used automatically by ClickStack whenever a query is eligible without requiring changes to dashboards, visualizations, or alerts. ClickStack evaluates each query at execution time and determines whether a materialized view can be applied.

### Verifying acceleration in dashboards and visualizations[​](#verifying-acceleration-in-dashboards-and-visualizations "Direct link to Verifying acceleration in dashboards and visualizations")

It's important to remember that incremental materialized views only contain data inserted **after the view was created**. They're not automatically backfilled, which keeps them lightweight and inexpensive to maintain. For this reason, users must explicitly specify the valid time range for a view when registering it.

Note

ClickStack will only use a materialized view if its minimum timestamp is less than or equal to the start of the query's time range, ensuring the view contains all required data. Although queries are internally split into time-based subqueries, materialized views are applied either to the entire query or not at all. Future improvements may allow views to be used selectively for eligible subqueries.

ClickStack provides clear visual indicators to confirm whether a materialized view is being used.

1. **Check the optimization status** When viewing a dashboard or visualization, look for the lightning bolt or `Accelerated` icon:

- **Green lightning bolt** indicates the query is accelerated by a materialized view.
- **Orange lightning bolt** indicates the query is executed against the source table.

2. **Inspect optimization details** Click the lightning bolt icon to open a details panel showing:

- **Active materialized view**: the view selected for the query, including its estimated row count.
- **Skipped materialized views**: compatible views that weren't selected, along with their estimated scan sizes.
- **Incompatible materialized views**: views that couldn't be used and the specific reason why.

3. **Understand common incompatibility reasons** A materialized view may not be used if:

- The **query time range** starts before the view's minimum timestamp.
- The **visualization granularity** isn't a multiple of the view's granularity.
- The **aggregation function** requested by the query isn't present in the view.
- The query uses **custom count expressions**, such as `count(if(...))`, that can't be derived from the view's aggregation states.

These indicators make it easy to confirm whether a visualization is accelerated, understand why a particular view was selected, and diagnose why a view wasn't eligible.

### How materialized views are selected for visualizations[​](#how-views-are-selected "Direct link to How materialized views are selected for visualizations")

When a visualization is executed, ClickStack may have multiple candidates available, including the base table, and multiple materialized views. To ensure optimal performance, ClickStack automatically evaluates and selects the most efficient option using ClickHouse's [`EXPLAIN ESTIMATE`](/docs/sql-reference/statements/explain#explain-estimate) mechanism.

The selection process follows a well-defined sequence:

1. **Validate compatibility**
   ClickStack first determines whether a materialized view is eligible for the query by checking:

   - **Time coverage**: the query's time range must fall entirely within the materialized view's available data range.
   - **Granularity**: the visualization's time bucket must be equal to or coarser than the view's granularity.
   - **Aggregations**: the requested metrics must be present in the view and computable from its aggregation states.
2. **Transform the query**
   For compatible views, ClickStack rewrites the query to target the materialized view's table:

   - Aggregation functions are mapped to the corresponding materialized columns.
   - `-Merge` combinators are applied to aggregation states.
   - Time bucketing is adjusted to align with the view's granularity.
3. **Select the best candidate**
   If multiple compatible materialized views are available, ClickStack runs an [`EXPLAIN ESTIMATE`](/docs/sql-reference/statements/explain#explain-estimate) query for each candidate and compares the estimated number of rows and granules scanned. The view with the lowest estimated scan cost is selected.
4. **Graceful fallback**
   If no materialized view is compatible, ClickStack automatically falls back to querying the source table.

This approach consistently minimizes data scanned and delivers predictable, low-latency performance without requiring changes to visualization definitions.

Materialized views remain eligible even when visualizations include filters, search constraints, or time bucketing, provided that all required dimensions are present in the view. This allows views to accelerate dashboards, histograms, and filtered charts without requiring changes to visualization definitions.

#### Example of choosing materialized views[​](#example-of-choosing-materialized-view "Direct link to Example of choosing materialized views")

Consider two materialized views created on the same trace source:

- `otel_traces_1m`, grouped by minute, `ServiceName`, and `StatusCode`
- `otel_traces_1m_v2`, grouped by minute, `ServiceName`, `StatusCode`, and `SpanName`

The second view contains additional grouping keys and therefore produces more rows and scans more data.

If a visualization requests **average duration per service over time**, both views are technically valid. ClickStack issues an [`EXPLAIN ESTIMATE`](/docs/sql-reference/statements/explain#explain-estimate) query for each candidate and compares the estimated granule counts i.e.:

```
EXPLAIN ESTIMATE
SELECT
    toStartOfHour(Timestamp) AS hour,
    ServiceName,
    avgMerge(avg__Duration) AS avg__Duration
FROM otel_v2.otel_traces_1m
GROUP BY
    hour,
    ServiceName
ORDER BY hour DESC