Pre-Aggregation approaches

ETL vs Materialized Views vs Projections in ClickHouse.

Pre-Aggregation approaches: ETL vs Materialized Views vs Projections

ETLMVProjections
Realtimenoyesyes
How complex queries can be used to build the preaggregatonanycomplexvery simple
Impacts the insert speednoyesyes
Are inconsistancies possibleDepends on ETL. If it process the errors properly - no.yes (no transactions / atomicity)no
Lifetime of aggregationanyanySame as the raw data
Requirementsneed external tools/scriptingis a part of database schemais a part of table schema
How complex to use in queriesDepends on aggregation, usually simple, quering a separate tableDepends on aggregation, sometimes quite complex, quering a separate tableVery simple, quering the main table
Can work correctly with ReplacingMergeTree as a sourceYesNoNo
Can work correctly with CollapsingMergeTree as a sourceYesFor simple aggregationsFor simple aggregations
Can be chainedYes (Usually with DAGs / special scripts)Yes (but may be not straightforward, and often is a bad idea)No
Resources needed to calculate the incrementMay be signigicantUsually tinyUsually tiny
Last modified 2022.03.02: Create preaggregations.md (eb80238)