ClickHouse® Materialized Views: Complete Developer Guide

This comprehensive guide covers everything you need to know about ClickHouse® Materialized Views - from basic concepts to advanced optimization techniques. Learn how materialized views work as insert triggers, when to use them vs projections, and expert strategies for building high-performance analytics applications. What you'll learn: - What materialized views are and how they work as insert triggers - How to create materialized views for data transformation and aggregation - When to use materialized views vs projections in ClickHouse - Advanced aggregation techniques with AggregatingMergeTree - Expert tips for avoiding common pitfalls and performance issues - Real-world examples with Tinybird's web analytics starter kit Perfect for: - Developers building real-time analytics applications - Data engineers optimizing ClickHouse performance - Teams choosing between materialized views and projections - Anyone working with ClickHouse aggregation and data transformation Master the materialized view patterns that power ClickHouse's performance and learn how to choose the right approach for your analytics pipeline needs. 00:00 Intro 00:39 What is a materialized view A materialized view is a continuous streaming result of a query saved as a new data source. As new data is ingested into a table, there's a transformation pipeline that processes the data and saves results in a new table you can query like any other table. This happens at insert time, making materialized views also called "insertion triggers." 01:24 How materialized views work in ClickHouse Materialized views work by processing data blocks as they're inserted. 03:48 When to use Materialized Views Materialized views solve three main use cases: data transformation following extract-load-transform patterns (good for parsing logs, processing JSON, filtering data), aggregation for calculating metrics by dimensions and point-in-time queries, and pre-aggregating data as it's being inserted. 04:56 Materialized view end to end example with Tinybird code Demonstrates creating a materialized view using Tinybird's web analytics starter kit. 09:20 Making a ClickHouse query 1000x faster with a materialized view Shows how to optimize aggregation queries by creating a materialized view that pre-calculates event counts per action type. Instead of scanning the full table (100,000+ rows), the materialized view reduces the query to just 29 rows by using AggregatingMergeTree with countState functions. 12:44 AggregateFunctions in ClickHouse Explains the various aggregate functions available in ClickHouse, including functions for calculating unique values, counts, means, max values, and more. 13:56 When and when NOT to use Projections in ClickHouse Projections are similar to materialized views but with limitations. Use projections for reordering tables (when filtering by columns not in the sorting key) or simple aggregations. 15:22 Materialized Views vs Projections in ClickHouse Detailed comparison showing materialized views work as insert triggers (applied to each block), while projections work as per-part mutations (applied when new parts are created). 17:23 How to use Projections to speed up simple queries Shows how to create projections using ALTER TABLE commands for simple aggregations or table reordering. 18:35 Expert advices when using Materialized Views Key expert tips: only the left table triggers materialized views in joins, beware of memory issues with large right tables, Union All isn't supported in open-source ClickHouse (but is in Tinybird's fork), avoid over-aggregation (ClickHouse is fast at 10% aggregation ratio), monitor insert latency and timeouts, avoid chains of materialized views when possible, and use proper dev tools that abstract database operations for schema evolution. Learn more: - Best practices when using Materialized Views - https://tbrd.co/mvs - How to stream data to ClickHouse - https://tbrd.co/streaming - HTTP streaming to ClickHouse - https://tbrd.co/events-api-yt - Streaming OTel traces, logs and metrics to ClickHouse - https://tbrd.co/otel - Web analytics - https://tbrd.co/wask-v2 - How to ingest 1 BILLION rows per second in ClickHouse: https://tbrd.co/1b-rows ClickHouse for Developers is a video series focused on simplifying the complex parts of ClickHouse and highlighting the features that help you build your product. We'll cover schema design, data types, engines, indexing, partitioning, query optimization, monitoring, SQL, materialized views, and the many small but important details you need to know to effectively build an analytics product as a developer or software engineer. Tinybird is not affiliated with, associated with, or sponsored by ClickHouse, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc.