Views in ClickHouse: Regular, Parameterized, and Materialized Views

1. Introduction to Views in ClickHouse

A view in ClickHouse is a database object that looks and behaves like a table when you query it, but its contents are derived from a SQL query rather than being stored as independent data like a normal table. A view allows you to encapsulate complex queries, business logic, or derived calculations into a reusable and queryable structure, which helps in simplifying analytics and maintaining consistency across queries.

From a conceptual standpoint, a view can be thought of as a “virtual table” whose rows and columns are produced by executing a predefined SELECT query. This means that when you query a view, ClickHouse internally executes the underlying query and returns the result as if it were reading from a table.

ClickHouse provides multiple types of views, each designed for different use cases and performance considerations:

Regular (Normal) Views
Parameterized Views
Materialized Views

Understanding the differences between these view types is critical because they differ in how they store data, when they compute results, and how they react to changes in the source tables.

2. Sample Setup: Salary Table

Let us assume we have a database called sql_examples and a table called salary with the following structure:

CREATE TABLE sql_examples.salary
(
    name String,
    month String,
    salary UInt32
)
ENGINE = MergeTree
ORDER BY name;

Example data:

INSERT INTO sql_examples.salary VALUES
('Alice', 'Jan', 5000),
('Bob', 'Jan', 6000),
('Charlie', 'Jan', 7000);

This table will act as the source table for demonstrating different types of views.

3. Regular Views

3.1 Concept of Regular Views

A regular view is a non-materialized, logical layer on top of a SELECT query. It does not store any data on disk and instead dynamically retrieves data from the underlying table every time it is queried.

This makes regular views:

Lightweight
Always up to date
Dependent on source tables for data

Because the data is not stored separately, any change in the base table is immediately visible when querying the view.

3.2 Creating a Regular View

Suppose we want a view that shows double the salary.

CREATE VIEW sql_examples.salary_double_view AS
SELECT
    name,
    month,
    salary * 2 AS doubled_salary
FROM sql_examples.salary;

3.3 Querying the View

SELECT * FROM sql_examples.salary_double_view;

When this query runs, ClickHouse does not read from a stored dataset for the view, but instead executes the SELECT statement defined in the view.

If the base table changes:

INSERT INTO sql_examples.salary VALUES ('David', 'Jan', 8000);

Then:

SELECT * FROM sql_examples.salary_double_view;

The new data automatically appears, because the view always reflects the current state of the source table.

4. Parameterized Views

4.1 Concept of Parameterized Views

A parameterized view is similar to a regular view but allows parameters to be passed at query time. These parameters act like dynamic filters, enabling flexible and reusable query logic without redefining the view.

The key difference is that part of the filtering logic is deferred until query execution time, rather than being fully fixed at creation time.

4.2 Creating a Parameterized View

Example:

CREATE VIEW sql_examples.salary_filter_view AS
SELECT *
FROM sql_examples.salary
WHERE name = {name_param:String};

Here:

{name_param:String} is a parameter
Its value is supplied during query execution

4.3 Querying a Parameterized View

select * from parameterised_view(name='Alice');

This query returns only rows where name equals ‘Alice’, and the same view can be reused for any other name simply by changing the parameter value.

This approach is powerful for dashboards and applications where filtering conditions change frequently but the core query logic remains stable.

5. Materialized Views in ClickHouse

A materialized view in ClickHouse is a special type of view that does not merely store a query definition but actually stores the transformed data on disk, which allows expensive computations to be performed once at insert time instead of repeatedly at query time.

Unlike regular views, which are just saved SELECT queries executed on demand, a materialized view in ClickHouse acts more like a real table that is automatically populated based on data flowing into another table.

This design aligns perfectly with ClickHouse’s philosophy as a high-performance analytical database optimized for fast reads and append-heavy workloads, where precomputation and denormalization are often the keys to performance.

5.1 Why Materialized Views Exist

Before learning syntax, it is important to understand the motivation.

Materialized views exist to shift computational cost from query time to insert time, which dramatically improves performance for analytical workloads that repeatedly compute aggregates, transformations, or filtered datasets.

Consider scenarios like:

Pre-aggregating daily metrics
Creating rollups for dashboards
Transforming raw events into analytical formats
Filtering and storing only relevant subsets of data
Joining and denormalizing streaming data

If every dashboard query had to scan billions of rows and recompute aggregates, your system would be slow and expensive, so materialized views solve this by computing once and storing the result.

5.2 How Materialized Views Work in ClickHouse

A ClickHouse materialized view is triggered on INSERT into a source table, and the result of a defined SELECT query is automatically inserted into a target table.

Key idea:

INSERT → Source Table → MV SELECT → Target Table

Important:

The MV listens to inserts on the source table
The SELECT runs automatically
The output is written into another table
Queries read from the target table

This makes materialized views behave like automated ETL pipelines inside the database.

5.3 Basic Architecture

A materialized view involves:

Source table
Target table
Materialized view definition

5.3.1 Source Table

Example:

CREATE TABLE sales
(
    date Date,
    product String,
    amount UInt32
)
ENGINE = MergeTree
ORDER BY date;

5.3.2 Target Table

This stores precomputed data.

CREATE TABLE sales_daily
(
    date Date,
    total_amount UInt64
)
ENGINE = MergeTree
ORDER BY date;

5.3.3 Materialized View

CREATE MATERIALIZED VIEW mv_sales_daily
TO sales_daily
AS
SELECT
    date,
    sum(amount) AS total_amount
FROM sales
GROUP BY date;

Now every insert into sales automatically updates sales_daily.

5.3.4 Insert Flow Example

Insert data:

INSERT INTO sales VALUES
('2025-01-01', 'Laptop', 1000),
('2025-01-01', 'Mouse', 50),
('2025-01-02', 'Keyboard', 100);

Query:

SELECT * FROM sales_daily;

Result:

2025-01-01  1050
2025-01-02  100

The aggregation already happened during insert, so queries are fast.

6. Important Behavior Rules

6.1 Only INSERTs Trigger MVs

Materialized views in ClickHouse react only to INSERT operations, and they do not automatically reflect UPDATE or DELETE changes in the source table.

So:

INSERT → captured
UPDATE → ignored
DELETE → ignored

This is because ClickHouse is append-optimized.

6.2 Existing Data Is Not Auto-Loaded

When creating an MV, existing rows in the source table are not processed automatically.

If you need historical data:

Option 1:

CREATE MATERIALIZED VIEW ... POPULATE

Option 2 (safer in production):

INSERT INTO target_table
SELECT ... FROM source_table;

POPULATE can miss concurrent inserts, so many production systems avoid it.

7. Advanced Pattern — AggregatingMergeTree

For large-scale rollups:

ENGINE = AggregatingMergeTree()

Store aggregate states and merge later for massive scale.

8. Common Use Cases of Materialized Views in ClickHouse

Materialized views in ClickHouse are most powerful when they are used as continuous data pipelines that reshape, reduce, or enrich data at ingestion time so that queries become dramatically simpler and faster.

Instead of thinking of a materialized view as just a “cached query,” it is more accurate to think of it as a streaming transformation layer inside the database that runs automatically whenever new data arrives.

Let us examine the most important real-world use cases in detail.

8.1 Pre-Aggregation

Pre-aggregation means computing metrics like SUM, COUNT, AVG, MIN, MAX, or GROUP BY results at insert time so that analytical queries do not need to scan raw data repeatedly.

In analytical systems, the same aggregations are often computed thousands of times for dashboards, reports, and APIs. Recomputing them from raw data wastes CPU and increases latency.

A materialized view allows you to compute these aggregates once and store them.

Example Scenario

Imagine an e-commerce system generating millions of sales records per day, and dashboards frequently ask questions like:

Total daily revenue
Orders per day
Revenue by product
Revenue by country

Instead of scanning billions of rows each time, we pre-aggregate.

Benefit

Dashboard queries now read from a tiny aggregated table instead of scanning massive raw data, which leads to sub-second performance even at huge scale.

This pattern is extremely common in:

BI dashboards
Monitoring systems
Financial reporting
Real-time analytics

8.2 Filtering Streams

Filtering streams means storing only a subset of incoming data that is relevant for specific analytical needs, which reduces storage and speeds up queries.

Instead of storing everything everywhere, you selectively retain what matters for certain workloads.

Example Scenario

Suppose your system collects global user events, but a regional analytics team only cares about Indian users.

Rather than filtering at query time repeatedly, you filter once at ingestion.

Benefit

Queries on Indian users now scan a much smaller dataset, which improves performance and reduces storage costs.

This pattern is valuable when:

Regional data isolation is needed
Compliance requires separation
Specific teams need focused datasets
Cold data should be excluded

8.3 Data Transformation

Data transformation means converting raw or semi-structured data into cleaned, structured, and analytics-friendly formats at ingestion time.

Raw logs are often messy and not optimized for querying, so transforming them early simplifies analytics.

Example Scenario

Imagine ingesting raw JSON logs from applications where important attributes must be extracted.

Benefit

Now queries operate on clean columns instead of parsing strings repeatedly, which improves both performance and usability.

This is common in:

Log analytics
Observability platforms
Security monitoring
IoT ingestion pipelines

8.4 Denormalization

Denormalization means joining related data during ingestion so that future queries do not require expensive JOIN operations.

ClickHouse performs best with wide, denormalized tables because analytical queries often scan large datasets where JOINs can be costly.

Example Scenario

Suppose you have:

A fact table of purchases
A dimension table of product details

Instead of joining during every query, you join once.

Benefit

Queries now avoid runtime joins and instead read already-enriched data, which significantly improves query speed and simplifies analytics.

This is widely used in:

Star schema optimization
Real-time reporting
Recommendation systems
Customer analytics