Warehouses

ClickHouse

system.query_log carries read_rows and written_rows per statement; system.parts carries real write activity.

Fig. 1 — ClickHouse discovery

What the definition declares, against what the runtime shows.

ClickHouse records more about its own behaviour than almost any database here. system.query_log carries the statement text, its duration and — the part that matters — read_rows and written_rows for every query. That is a per-statement reconciliation available with no instrumentation at all, which is rare enough to be worth saying twice.

The characteristic ClickHouse failure is not lost rows but rows that went somewhere other than where the pipeline believed. A materialized view fires on insert to its source table, so a backfill written directly to the underlying table silently skips every view downstream. Duplicates in a ReplacingMergeTree persist until a merge that is not guaranteed to happen. Both are invisible to a monitor watching for errors, and both are plainly visible in the system tables.

What is read 7 sources

What Decim reads from ClickHouse

Every item below is read-only, and each is a specific view, endpoint or file rather than a category of access. If something here is unacceptable in your environment, it can be removed from the query catalogue — see the agent for how that works.

ClickHouse — metadata sources

What	From	Why it matters
Statement history	`system.query_log`	`read_rows` and `written_rows` per query — reconciliation for free
Write activity	`system.parts`	Rows and bytes per active part, with `modification_time`
Table structure	`system.tables`	Engine, `total_rows` and the full `create_table_query`
Materialized views	`system.tables` where engine is `MaterializedView`	The insert-triggered transform graph, including the target table each writes to
Dictionaries	`system.dictionaries`	Lookup tables, their source, last successful update and last exception
Mutations	`system.mutations`	`is_done` and `latest_fail_reason` — stuck ALTERs and DELETEs
Replication state	`system.replicas`, `system.replication_queue`	`absolute_delay`, readonly state and queue depth

Permissions required

Written out in full, because "read-only access" is not a specification. Nothing below grants the ability to write, and row access is requested only on the specific tables you name.

Read-only user with a constrained settings profile sql

CREATE USER decim_agent IDENTIFIED WITH sha256_password BY '...';

-- System tables only. No blanket grant on the system database.
GRANT SELECT ON system.query_log         TO decim_agent;
GRANT SELECT ON system.parts             TO decim_agent;
GRANT SELECT ON system.tables            TO decim_agent;
GRANT SELECT ON system.columns           TO decim_agent;
GRANT SELECT ON system.dictionaries      TO decim_agent;
GRANT SELECT ON system.mutations         TO decim_agent;
GRANT SELECT ON system.replicas          TO decim_agent;
GRANT SELECT ON system.replication_queue TO decim_agent;
GRANT SHOW TABLES, SHOW COLUMNS ON *.*   TO decim_agent;

-- Reconciliation tables, named explicitly.
GRANT SELECT ON sales.rejected_transactions TO decim_agent;

-- ClickHouse lets you cap the agent at the server rather than trusting
-- it to behave. readonly = 1 permits read queries and forbids changing
-- settings; the limits bound its cost on a production cluster.
CREATE SETTINGS PROFILE decim_readonly SETTINGS
    readonly            = 1,
    max_execution_time  = 30,
    max_result_rows     = 5000,
    max_memory_usage    = 1000000000,
    max_threads         = 2;

ALTER USER decim_agent SETTINGS PROFILE decim_readonly;

What it builds

What this source contributes to the pipeline topology and to the evidence available during an investigation:

The insert-triggered transform graph from materialized view definitions and their targets
Target nodes and write volumes from part-level metadata, independent of any pipeline's own logging
Lookup nodes from dictionaries, including when each last refreshed successfully
Read and write lineage per statement from query_log, covering ad-hoc work as well as scheduled

Nodes learned from a definition are marked declared; nodes observed running are marked observed; nodes both declared and observed are verified. Where two sources disagree, the disagreement is recorded as a drift note rather than resolved silently.

Failure modes

Failure modes it surfaces

What this source is uniquely good at proving — and, just as usefully, at disproving. An investigation that can refute a hypothesis cheaply is worth as much as one that confirms it.

Failure modes ClickHouse evidence distinguishes

Failure mode	The signal
Backfill bypassed a materialized view	Rows in the source table with no matching rows in the view's target
Duplicates surviving in ReplacingMergeTree	`count()` differing from `count() FINAL`
Insert silently deduplicated	A retried block absorbed by the replicated deduplication window
Dictionary stale or failing	`last_exception` set in `system.dictionaries`
Mutation stuck	`is_done = 0` with a `latest_fail_reason`

A materialized view fires on INSERT, so a backfill written to the target table skips it entirely

ClickHouse materialized views are insert triggers, not views. When a row is inserted into the source table, the view's SELECT runs over that block and the result is written to the view's target. Nothing else triggers it.

So a backfill that writes directly into the underlying MergeTree table — the obvious way to load historical data — populates the source and leaves every downstream view untouched. The source table is correct, the aggregates are silently short, and nothing errors. Worse, an ALTER TABLE … UPDATE or DELETE on the source does not propagate either, so a correction applied to the base table leaves the views permanently divergent.

This is the ClickHouse instance of the general rule that a successful write is not a completed pipeline, and it is the single most common reason a ClickHouse aggregate disagrees with its own source.

Limits

What this integration cannot tell you. Stated because an investigation that overstates its sources produces confident wrong answers, which is worse than an honest blocked.

system.query_log must be enabled (log_queries = 1) and is flushed asynchronously — the last few seconds of activity may not be there yet
Query log TTL defaults to 30 days, so an older incident may have lost its statement history
system.tables.total_rows is exact for MergeTree engines and NULL for Distributed and View engines
On a cluster, system tables are per-replica — use clusterAllReplicas() or the whole picture is one node's view

Related integrations

Snowflake — ACCOUNT_USAGE gives a year of query, copy and task history — including rows loaded and rows rejected.
BigQuery — INFORMATION_SCHEMA.JOBS gives every statement, its referenced tables and its output row count.
Apache Kafka — Consumer lag is the leading indicator. Reads group offsets, topic ends and partition assignment.
dbt — manifest.json is the model graph; run_results.json is what actually happened. Both are artefacts, not APIs.

See all integrations, or how the sources are combined into one graph.

Get started

Investigating a ClickHouse pipeline?

Bring an incident you already know the answer to. If Decim gets it wrong, that is a more useful demo than one where it doesn't.

Request a demo Read the docs