ClickHouse data freshness check and dashboards

Connect ClickHouse

Add a ClickHouse connector with host, HTTP port, user, password, database and the HTTPS toggle. The field list is on the ClickHouse connector page in the docs, and how a connection test works is in the connectors overview.

The port is the first thing people get wrong. arcodash uses ClickHouse’s HTTP interface, so the port is 8123 by default (or the HTTPS port if you turn on use_https). The native TCP port 9000 will not work. ClickHouse Cloud serves HTTPS on port 8443.

Make a dedicated user that can only read:

CREATE USER arcodash_ro IDENTIFIED BY 'change-me' SETTINGS readonly = 1;
GRANT SELECT ON analytics.* TO arcodash_ro;
GRANT SELECT ON system.parts TO arcodash_ro;
GRANT SELECT ON system.tables TO arcodash_ro;
GRANT SELECT ON system.columns TO arcodash_ro;

readonly = 1 refuses inserts, DDL and setting changes for that user. arcodash sends your SQL as written over POST, so the grants and that setting are the guardrail. The system.columns grant matters because the schema browser reads it. The two other system grants are for the metadata tiles below; skip them if you do not want those. Whether the system grants are required on your server version depends on its access settings, so run a connection test and a schema refresh after creating the user.

Network access: arcodash Cloud connects from the internet and does not publish fixed outbound IP addresses, so the server needs to be reachable publicly, ideally over HTTPS with use_https on. Each query may run up to 300 seconds and a result keeps at most 10,000 rows (see Limits). A check returns one row, so neither limit gets in the way.

A freshness check

A check is a scheduled query that returns one numeric row. The monitor compares one column of that row with a threshold. See data quality monitors for how it is evaluated.

SELECT dateDiff('second', max(loaded_at), now()) AS seconds_since_last_row
FROM analytics.events

Monitor seconds_since_last_row with > and 3600 to alert after an hour of silence. now() returns the time at query analysis, so it is fixed for the whole query. If you need sub-second values, now64() returns a DateTime64 with a scale from 0 to 9, but for a lag measured in seconds now() is enough.

Prefer a load timestamp such as loaded_at over an event timestamp. Events can legitimately go quiet overnight; a loader should not. The background is in data freshness.

Where the table is a MergeTree sorted by that timestamp, max() is cheap. If it is not in the sort key, ClickHouse reads that one column, which is still far less than a row-store scan.

A volume check

SELECT count() AS rows_last_hour
FROM analytics.events
WHERE loaded_at >= now() - INTERVAL 1 HOUR

Monitor rows_last_hour with < and a floor. For tables with a daily rhythm, a baseline monitor learns the normal level instead of you guessing a floor. The pattern behind this is in row count anomaly detection in SQL.

A metric alert

Error rate over the last 15 minutes, from a request log:

SELECT ifNull(round(100 * countIf(status >= 500) / nullIf(count(), 0), 2), 0) AS error_pct
FROM analytics.requests
WHERE ts >= now() - INTERVAL 15 MINUTE

Monitor error_pct with > and 2. nullIf avoids a division by zero when the window is empty, and ifNull turns that into 0 so the monitor reads a number instead of going to unknown. Wire the alert to a channel with notification destinations.

Gotchas that matter for monitoring

• FINAL. On ReplacingMergeTree and similar engines, rows that are duplicates or superseded exist until a background merge removes them. count() without FINAL can overcount; FINAL gives the merged answer but does merge work at query time and can read extra primary key columns. Use it only on checks where duplicates change the number, and put the FINAL after the alias (FROM t AS x FINAL). Freshness does not need it.
• Freshness from system.parts. modification_time in system.parts is when a part’s directory was last modified, usually its creation. Merges create new parts, so it moves even when no new data arrived. Treat it as a hint about writes, and trust max(loaded_at) for data.
• Active parts only. Filter system.parts on active. Inactive parts are leftovers from merges waiting for deletion and will double count rows.
• Time zones. DateTime columns can carry a time zone, and now() follows the server’s. Compare like with like, or pass an explicit zone, for example now('UTC').
• Replicas. On a replicated cluster, a query hits one replica. A lagging replica looks like stale data, so point the connector at a replica you trust or add a check for replication delay.
• No trailing FORMAT. arcodash appends FORMAT JSONCompact. A query with its own FORMAT clause, with or without a SETTINGS clause after it, is rejected up front.

To generate freshness, row count, null and duplicate checks for a ClickHouse table, use the ClickHouse data check generator.

What a dashboard looks like

Three tiles on an events table. Hourly volume, using toStartOfInterval to bucket:

SELECT toStartOfInterval(ts, INTERVAL 1 HOUR) AS hour, count() AS events
FROM analytics.events
WHERE ts >= now() - INTERVAL 1 DAY
GROUP BY hour
ORDER BY hour

Freshness per table, for a single “what is stale” tile, using the parts table:

SELECT table, max(modification_time) AS last_write, sum(rows) AS rows
FROM system.parts
WHERE database = 'analytics' AND active
GROUP BY table
ORDER BY last_write DESC

Top event types today, as a bar chart:

SELECT event_type, count() AS n
FROM analytics.events
WHERE ts >= toStartOfDay(now())
GROUP BY event_type
ORDER BY n DESC
LIMIT 10

Save each query, pick a chart type, and schedule the ones that feed monitors. Scheduling is covered in scheduled refresh. If you would rather not write the checks by hand, Suggest checks with AI drafts them from your schema, and you bring your own AI key.

If you also run Postgres, the Postgres version of the freshness query is the same idea in another dialect. arcodash is hosted only, with 14 days free and no card.

docs

ClickHouse setup in the docs

Connection fields, an example configuration and caveats.