ClickHouse Data Lineage: Trace Columns Through Materialized Views

ClickHouse data lineage is the map of how columns flow through your ClickHouse pipeline: which Kafka engine tables, MergeTree tables, and materialized view SELECTs feed each downstream column, and which functions and aggregate states transform the data along the way. Because ClickHouse materialized views are defined entirely in SQL, accurate lineage comes from parsing that SQL. Gudu SQLFlow does this with a dedicated ClickHouse dialect parser and returns interactive, column-level lineage diagrams.

Why ClickHouse data lineage is a materialized view problem

In most warehouses, transformation logic lives in scheduled batch jobs. In ClickHouse it lives in materialized views. A ClickHouse MV is an insert trigger: every block written to the source table is pushed through the MV’s SELECT and inserted into a target table. Production pipelines chain them — a Kafka engine table feeds an MV that lands raw rows in a MergeTree table, further MVs read that table and populate SummingMergeTree or AggregatingMergeTree rollups, and dashboards query the rollups.

That design is fast, but it scatters your dataflow across many small CREATE MATERIALIZED VIEW statements. When a dashboard number looks wrong, or someone wants to drop a column from a Kafka topic, you have to reconstruct the chain by hand: query system.tables, read each MV’s create_table_query, follow every TO clause, and repeat until you reach the leaves. Each hop is a SELECT with its own expressions, filters, and GROUP BY — so tracing a single column correctly means actually parsing SQL, not grepping for table names.

SQLFlow automates exactly that step. Its ClickHouse parser is one of 39 dialect-specific parsers (not a generic ANSI grammar), so ClickHouse-only constructs — engine clauses, AggregateFunction columns, -State combinators, functions like toDate and uniq — are understood rather than skipped.

Example: tracing a column from Kafka to an AggregatingMergeTree

Here is a typical two-hop ClickHouse ingestion chain: a Kafka engine table, an MV that persists raw events into MergeTree, and a second MV that maintains a pre-aggregated rollup in an AggregatingMergeTree table.

CREATE TABLE events_kafka
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    revenue    Decimal(18, 2)
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list  = 'events',
         kafka_format      = 'JSONEachRow';

CREATE TABLE events_raw
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    revenue    Decimal(18, 2)
)
ENGINE = MergeTree
ORDER BY (event_type, event_time);

CREATE MATERIALIZED VIEW mv_events_raw TO events_raw AS
SELECT event_time, user_id, event_type, revenue
FROM events_kafka;

CREATE TABLE revenue_daily
(
    day           Date,
    event_type    LowCardinality(String),
    total_revenue AggregateFunction(sum, Decimal(18, 2)),
    buyers        AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (day, event_type);

CREATE MATERIALIZED VIEW mv_revenue_daily TO revenue_daily AS
SELECT
    toDate(event_time)  AS day,
    event_type,
    sumState(revenue)   AS total_revenue,
    uniqState(user_id)  AS buyers
FROM events_raw
WHERE event_type = 'purchase'
GROUP BY day, event_type;

Feed these four statements to SQLFlow and it resolves the full chain. For the rollup table it reports, per output column:

• revenue_daily.total_revenue comes from events_raw.revenue through sumState(), which in turn comes from events_kafka.revenue through mv_events_raw — a complete Kafka-to-rollup path in one diagram.
• revenue_daily.day derives from events_raw.event_time via toDate(); the applied function is recorded on the edge, not discarded.
• revenue_daily.buyers derives from events_raw.user_id via uniqState().
• events_raw.event_type influences every rollup column indirectly, because of the WHERE event_type = 'purchase' filter and the GROUP BY — SQLFlow marks these as indirect lineage, distinct from the direct dataflow edges.

That last point matters in ClickHouse more than most engines. Rollup correctness routinely depends on filter columns that never appear in the output. If someone renames event_type in the Kafka topic, table-level lineage says “the rollup depends on events_raw” — true and useless. Column-level lineage with indirect edges says “total_revenue and buyers are both filtered by this exact column,” which is the answer you actually need before you ship the change. Most lineage tools do not model indirect lineage at all; SQLFlow makes it a separately toggleable layer in the diagram.

What SQLFlow extracts from ClickHouse SQL

• Column-level lineage: for each output column, the exact source columns that feed it, and the functions, casts, subqueries, joins, and set operators on the path. Column references are resolved through CTEs, subqueries, views, and SELECT * expansion.
• Direct vs. indirect lineage: pure dataflow edges are kept separate from influence edges (columns used in WHERE, GROUP BY, and join conditions), and each layer can be toggled independently.
• Multi-hop MV chains: because each MV’s SELECT is parsed and its target table linked, chained materialized views resolve into one continuous graph from ingestion table to final rollup.
• Interactive diagrams plus structured data: drill into the diagram, or export lineage as JSON, CSV, or PNG, or pull it programmatically over the REST API.

How to get your ClickHouse SQL into SQLFlow

1. Paste or upload. Copy DDL and MV definitions (for example, the create_table_query values out of system.tables) into the browser, or upload your migration files. This is the fastest way to audit one pipeline.
2. Connect over JDBC. SQLFlow can pull metadata from a live database over JDBC, so table and view definitions come straight from the server instead of possibly stale files.
3. Automate extraction. The Grabit/SQLFlow-ingester utility extracts metadata on a schedule for repeatable, whole-estate scans.

In every mode, SQLFlow performs static analysis of SQL code only. It never reads rows from your tables, and with the On-Premise edition (Docker/Kubernetes, air-gap friendly) even the SQL text never leaves your network — relevant if your ClickHouse cluster carries event data you cannot send to a SaaS.

Ways to get lineage from ClickHouse, compared

Approach	Good at	Gap for ClickHouse MV chains
Reading system.tables by hand	Free, always current, no tooling	You become the parser; multi-hop chains and per-column paths are reconstructed manually and go stale in your head
Open-source parsers (sqllineage, sqlglot)	Scripting lineage checks for individual queries inside your own tooling	They are libraries, not lineage products: assembling chained MV statements into one navigable column-level graph, with diagrams and indirect-lineage separation, is code you write and maintain
Catalog-first platforms	Governance workflows, ownership, business glossary across the whole stack	Lineage depth depends on their per-dialect SQL parsing; a specialized parser typically goes deeper on column-level ClickHouse lineage
Gudu SQLFlow	Column-level ClickHouse lineage from a dedicated dialect parser, with direct/indirect separation and diagrams	Commercial tool; static analysis by design — it maps SQL logic, not runtime job telemetry

These approaches also combine: enterprise SQLFlow deployments export lineage to DataHub, Microsoft Purview, and OpenMetadata, so SQLFlow can act as the parsing engine behind the catalog you already run. At scale it batch-scans estates of 100+ databases and over a million columns with incremental scans and a persistent lineage repository — ClickHouse alongside the rest of your stack.

ClickHouse rarely lives alone

Real-time serving in ClickHouse usually sits next to other engines: a federation layer, local analytics, a batch warehouse. SQLFlow parses all of them with the same engine — the General SQL Parser, developed commercially since the mid-2000s and validated against roughly 13,600 per-dialect test fixtures — so lineage stays column-level across engine boundaries. If your stack includes them, see the companion guides on Trino data lineage for federated query layers and DuckDB data lineage for in-process analytics, or browse the data lineage knowledge base.

Frequently asked questions

Can SQLFlow trace lineage through chained ClickHouse materialized views?

Yes. Each materialized view’s SELECT is parsed and its target table is linked into the graph, so a Kafka engine table feeding an MV, feeding a MergeTree table, feeding another MV into an AggregatingMergeTree resolves as one continuous column-level path.

Does SQLFlow understand ClickHouse-specific SQL syntax?

Yes. ClickHouse is one of SQLFlow’s 39 dialect-specific parsers. It is a dedicated grammar, not a generic ANSI parser, so ClickHouse constructs such as engine clauses and aggregate function state columns are parsed rather than skipped.

Does SQLFlow need access to my ClickHouse data?

No. SQLFlow performs static analysis of SQL code and, optionally, schema metadata pulled over JDBC. It never reads rows from your tables, and the On-Premise edition keeps even the SQL text inside your network.

What does “indirect lineage” mean for a ClickHouse rollup?

A column used in a materialized view’s WHERE filter or GROUP BY never appears in the rollup’s output, yet it determines which rows are aggregated. SQLFlow records these as indirect lineage edges, separate from direct dataflow, and lets you toggle each layer in the diagram.

How much does SQLFlow cost for ClickHouse lineage?

SQLFlow Cloud has a free tier; premium is $49.99/month. SQLFlow On-Premise is $500/month or $4,800 one-time per selected database type, installable on two servers. Details are on the pricing page.