Materialized views

A materialized view is a view that PostHog materializes: it runs the view's query on a schedule and stores the result as a table in the data warehouse. Queries then read the stored table instead of running the query again, which is much faster. This is useful for expensive and frequently used queries like KPI dashboards or queries behind customer-facing analytics.

Materialized views can read other views, so you can build data in layers: clean raw source data in one view, then aggregate it in another.

The Models page

The Models page shows every materialized view in your project. It has these tabs:

• Overview. The health of your materialized views: Models needing attention lists failed and suspended views, and Models behind schedule lists views whose data is older than it should be.
• Models. Every view, with its status.
• Lineage. A graph of which views read which sources and other views.
• Data quality. The results of your data quality checks.

Click a view to open it. Its Materialization tab has the refresh cadence, Sync now, and the run history.

Materializing a view

There are two ways to materialize a view:

1. At creation time: When saving a query as a view in the SQL editor, check Materialize this view in the save dialog. The view materializes right away and refreshes once a day. You can change that later with Refresh every on its Materialization tab.

2. After creation: Open the view from the Models page, go to its Materialization tab, pick how often it should refresh, and click Materialize.

Once materialized, you can query the view like any other table.

Refresh cadence

Each view refreshes on its own cadence, from every 15 minutes to every 30 days. Set it with Refresh every on the view's Materialization tab. A new materialized view refreshes once a day.

For example, if you sync your billing data to Postgres using a cron job daily and link Postgres as a source in PostHog, you could set the view built on that billing data to refresh daily as well.

The cadence a view can use depends on the sources it reads and the views that read it. See freshness and refresh cadence.

Views that depend on other views

If a view reads from another view, PostHog refreshes them in dependency order, so a view never reads a half-written table. An upstream view refreshes at least as often as the fastest view downstream of it. See upstream views keep up with downstream ones.

To manually trigger a view along with its dependencies or dependents, use the MCP view-run tool with the direction parameter set to upstream or downstream.

Incremental materialization

Every run of a materialized view rebuilds the whole table by default. With incremental materialization, each run reads only the rows that are new since the last run. It then updates the table in place.

Use it when the query is slow, or when the source table is large and mostly unchanging. A daily revenue rollup over two years of Stripe charges is a good example. A full rebuild reads all two years. An incremental run reads only the days that changed.

Turning on incremental refresh

You can turn it on when you save a view, or later on an existing one.

1. Open the view in the SQL editor.
2. Select the Materialization tab below the query.
3. Under Refresh mode, select Incremental.
4. Fill in the settings below, then save.

The same settings appear in the Save as view dialog when you check Materialize this view.

PostHog offers only the columns that can do each job. A string or a UUID column cannot be an incremental column, because neither has a reliable order. Both can be part of a unique key.

A unique key column must never be null. A null value adds a duplicate row on every run instead of updating one. Wrap the column in coalesce() to give it a fallback value.

When PostHog rebuilds the whole table

Some runs rebuild the table from scratch, even on an incremental view:

• The first run after you materialize the view.
• Any run after you change the query, the incremental column, or the unique key.
• A run you start with Run full refresh, in the menu next to Sync now.

A change to Re-read recent data does not cause a rebuild. It applies from the next run.

Each run in the runs table carries a tag that says incremental or full refresh. The row count of an incremental run counts the rows it wrote, including the rows it re-read.

Use Run full refresh after you correct data upstream. It takes as long as the first materialization did.

Which queries can be incremental

PostHog checks your query as you edit it. If a query cannot be incremental, the editor names the construct that blocks it, and the view stays on full refresh.

A query cannot be incremental when it uses:

• LIMIT, OFFSET, or LIMIT BY, at any level. Which rows these let through changes as data arrives.
• A top-level ORDER BY. A sort has no effect on a materialized table. Sort when you query the view instead.
• A top-level SELECT DISTINCT without a GROUP BY. Deduplicating one window says nothing about rows in other windows.
• HAVING. A group can pass the condition on one run and fail it on a later one.
• Window functions. Their frames reach across rows outside the window that the run recomputes.
• GROUP BY CUBE, ROLLUP, or GROUPING SETS. One source row contributes to several output rows.
• EXCEPT or INTERSECT. One removed row can add or remove output rows anywhere.

Aggregates are supported, including count(DISTINCT ...) and exact percentiles. A grouped query has two extra rules:

• The incremental column must be one of the GROUP BY columns.
• The unique key must include every GROUP BY column.

Together these rules make each run recompute every group it touches in full, so no aggregate goes stale.

The editor also shows warnings that do not block the save:

• now(), today(), and rand() make each run depend on when it ran. A later run can then change rows that an earlier run wrote.
• An incremental column that comes from a subquery or CTE that aggregates. Results stay correct, but each run reads as much data as a full refresh.

Things to know

• An incremental run never deletes rows. If a row disappears from your source, the stored row stays until you rebuild.
• A row that arrives later than the lookback window is missed. Widen Re-read recent data, or rebuild to pick it up.
• Incremental views work in the SQL editor only. Endpoints always refresh in full.

Watching runs

Run history on a view's Materialization tab lists every run with its status, row count, and any error. Use it to confirm a view is refreshing on schedule and to read the error when one isn't.

To quickly find views by their run status, type status:failed, status:running, or status:completed in the search field of the SQL editor sidebar or the Models tab. You can combine status tokens with a name search, for example revenue status:failed.

To test new data before PostHog publishes it, block materialization on failing data quality checks.

A view that fails 5 times in a row is suspended and stops refreshing until you resume it. See failures and suspension. For specific errors, see troubleshooting.

Tips for materializing views

• The purpose of materialization is to speed up queries, so you don't need to materialize views that are already fast.

• You can materialize only the slow part of a larger query, like a with expression or a subquery. Often times, materializing a subset is a good way to create a resource that's reusable, including insights and other data warehouse views.

• Views update on their cadence, not in real time. A dashboard that reads a view shows new data only after the view refreshes. See how fresh is the data, really.

• Materialization queries get more time than standard queries, but an inefficient query can still time out. A run's query stops after 10 minutes. PostHog tries a run up to 3 times before it marks the run as failed. A run that runs out of memory is not retried. See a run timed out.