MV in ClickHouse: data loss and control

Materialized Views in ClickHouse: Data Loss Scenarios

Materialized views (MVs) in ClickHouse act like triggers on INSERT operations in an append-only environment. They capture data changes without support for UPDATE or DELETE. MVs are reliable when rules are followed, but table schema changes can lead to data loss.

Key risk: ClickHouse allows modifying source and target tables independently of active MVs. This sets it apart from traditional DBMSs.

Classic Workflow

Create test tables and an MV for demonstration:

-- Source table
DROP TABLE IF EXISTS test_orders;
CREATE TABLE test_orders (
    order_id String,
    order_dt Datetime
)
ENGINE = MergeTree
ORDER BY order_id;

-- Target table
DROP TABLE IF EXISTS final_test_orders;
CREATE TABLE final_test_orders (
    order_id String,
    order_dt Datetime
)
ENGINE = MergeTree
ORDER BY order_id;

-- MV to redirect data
DROP VIEW IF EXISTS mv_final_test_orders;
CREATE MATERIALIZED VIEW mv_final_test_orders
TO default.final_test_orders (
    order_id String,
    order_dt Datetime
)
AS SELECT order_id, order_dt FROM test_orders;

Insert data:

INSERT INTO test_orders VALUES 
('QWE123', '2025-03-01 12:00:00'),
('RTY456', '2025-03-01 13:00:00'),
('XYZ789', '2025-03-01 14:00:00');

The query SELECT * FROM final_test_orders will return all records correctly.

Breaking the Target Table Schema

Change the column type in final_test_orders:

DROP TABLE IF EXISTS final_test_orders;
CREATE TABLE final_test_orders (
    order_id UInt64,
    order_dt Datetime
)
ENGINE = MergeTree
ORDER BY order_id;

Repeat the insert into test_orders. The MV will throw a String to UInt64 conversion error. Data will be written to the source table but not the target. The INSERT will fail due to block atomicity.

ClickHouse inserts data in blocks. An error in the MV interrupts the entire INSERT, but partial blocks may persist.

Ignoring MV Errors

The setting materialized_views_ignore_errors=true suppresses MV errors:

INSERT INTO test_orders SETTINGS materialized_views_ignore_errors = true VALUES 
('QWE123', '2025-03-01 12:00:00'),
('RTY456', '2025-03-01 13:00:00'),
('XYZ789', '2025-03-01 14:00:00');

Data is committed to test_orders, but nothing appears in final_test_orders. This is a controlled loss in the target table.

Monitoring Errors via System Logs

Use system.query_views_log to track MV failures:

SELECT event_time, view_name, exception
FROM system.query_views_log 
WHERE event_date = today() AND exception_code != 0
ORDER BY event_time DESC
LIMIT 1000;

The query returns the error timestamp, MV name, and exception text. Set up alerting for these events in production environments.

Combining materialized_views_ignore_errors=true with monitoring minimizes risks.

Uncontrolled Losses from Column Mismatches

MVs use column names, not positions. Missing columns are filled with default values without errors.

Rename the column:

DROP TABLE IF EXISTS final_test_orders;
CREATE TABLE final_test_orders (
    orders String,
    order_dt Datetime
)
ENGINE = MergeTree
ORDER BY orders;

The insert into test_orders will succeed. In final_test_orders, the orders column will be filled with empty strings — data lost silently.

Recommendations for Operating MVs

• Check for columns in the target table when creating an MV.
• Test MVs after deployment.
• Avoid schema changes to tables with active MVs.
• Implement alerting on system.query_views_log.

Key Points:

• MVs are uncontrolled ETL without transactional guarantees.
• Block inserts create a risk of partial losses on errors.
• materialized_views_ignore_errors=true preserves source data but requires monitoring.
• Column name mismatches lead to silent failures.
• ClickHouse documentation warns about MV risks.

— Editorial Team