Keep clickhouse integration prometheus scrapeable

Prometheus thinks in scrapes, labels, and time series. ClickHouse^® thinks in columns, partitions, and SQL aggregations over billions of rows. A working clickhouse integration prometheus setup makes that translation explicit instead of hoping a generic exporter guesses your schema.

Most failures look the same in incident review:

• The scrape succeeds but Grafana shows NaN because the Pipe returns JSON column names Prometheus cannot parse
• Remote write lands 400M label combinations because user_id became a Prometheus label
• The same KPI exists in three places (Prometheus recording rule, ClickHouse MV, Tinybird Pipe) and alerts disagree by 20%
• Dashboards query raw samples for 90-day ranges and time out

This post covers four integration paths, the table contracts each path needs, scrape configuration, cardinality rules, and acceptance tests so your metrics stay trustworthy.

What success looks like

You are done when:

1. Every exported metric has an owner, a name, allowed label keys, and a documented type (counter, gauge, etc.)
2. Scraped endpoints return valid Prometheus exposition format on every refresh
3. Long-retention analytics query rollups, not raw samples, for ranges beyond a few days
4. Cardinality budgets are written down before production (labels × values)
5. One system is canonical for alerting; the others are downstream views
6. Failed scrapes page someone (empty body, auth errors, schema drift)

If your only goal is "we can graph something," you will rebuild the pipeline in quarter two.

Pick a path before you write SQL

Paths A and B overlap. Pick one primary for each metric family.

                    ┌── Path A: Tinybird Pipe `.prometheus` ──► Grafana scrape
App / infra events ─┤
                    ├── Path B: Grafana ClickHouse datasource ──► panel SQL
                    └── Path C: Prometheus remote write ──► MergeTree samples ──► rollups

Path A: Tinybird Pipe endpoints in Prometheus format

Tinybird publishes SQL Pipes as HTTP endpoints. Append .prometheus to return exposition format per Tinybird Prometheus docs.

Required output schema

Multiple metrics in one Pipe use UNION ALL. Order by name so output is stable scrape-to-scrape.

Pipe SQL: request volume + latency gauges

SELECT
    'http_request_count' AS name,
    toFloat64(count()) AS value,
    'Total HTTP requests in window' AS help,
    'counter' AS type,
    map('service', service_name, 'method', method, 'status', status_code) AS labels
FROM http_requests
WHERE event_time >= now() - INTERVAL 5 MINUTE
GROUP BY service_name, method, status_code

UNION ALL

SELECT
    'http_request_duration_seconds' AS name,
    quantile(0.95)(request_time) AS value,
    'p95 HTTP request duration in seconds' AS help,
    'gauge' AS type,
    map('service', service_name, 'method', method) AS labels
FROM http_requests
WHERE event_time >= now() - INTERVAL 5 MINUTE
GROUP BY service_name, method

ORDER BY name

Export URL:

https://api.tinybird.co/v0/pipes/http_slo_metrics.prometheus

Example output shape (truncated):

# HELP http_request_count Total HTTP requests in window
# TYPE http_request_count counter
http_request_count{method="GET",service="checkout",status="200"} 18432
# HELP http_request_duration_seconds p95 HTTP request duration in seconds
# TYPE http_request_duration_seconds gauge
http_request_duration_seconds{method="GET",service="checkout"} 0.042

Auth and scrape config

Use a token with PIPES:READ on the endpoint. Pass Bearer auth from Prometheus or Grafana Agent:

scrape_configs:
  - job_name: tinybird_checkout_slos
    scrape_interval: 30s
    scrape_timeout: 25s
    metrics_path: /v0/pipes/http_slo_metrics.prometheus
    scheme: https
    static_configs:
      - targets: ['api.tinybird.co']
    authorization:
      credentials: YOUR_PIPE_READ_TOKEN

Validate with curl before wiring Grafana:

curl -s \
  -H "Authorization: Bearer YOUR_PIPE_READ_TOKEN" \
  "https://api.tinybird.co/v0/pipes/http_slo_metrics.prometheus" \
  | head -20

When Path A fits

• KPIs are computed in SQL over ClickHouse data (pipeline lag, queue depth, revenue counters)
• You already use Tinybird Pipes for JSON APIs and want the same SQL for monitoring
• Scrape interval is 30s–5m and query cost per scrape is acceptable

Path A mistakes

1. Missing type on counters

Grafana rate() and increase functions assume counter semantics. Without type = 'counter', panels show wrong derivatives.

Fix: Set type explicitly for every exported metric.

2. High-cardinality labels in SQL

Exporting user_id, trace_id, or full URL paths as labels creates millions of series. Prometheus memory and scrape time explode.

Fix: Aggregate before export. Keep labels bounded (service, region, status_class).

3. Full-history scan per scrape

Running 90-day aggregations on every 30s scrape times out and burns compute.

Fix: Scrape rollups aligned to the scrape window (5m, 1h), not raw fact tables.

4. Duplicate metric names in one Pipe

Two rows with the same name and label set produce undefined scrape behavior.

Fix: One logical metric per name + label combination; use UNION ALL with distinct names.

Path B: Grafana panels on ClickHouse (no exposition format)

When metrics already live in ClickHouse and your team lives in Grafana, the ClickHouse Grafana plugin avoids an extra scrape hop.

SELECT
    toStartOfMinute(event_time) AS time,
    service_name,
    countIf(status = 'error') AS errors,
    count() AS total,
    errors / total AS error_rate
FROM app_events
WHERE $__timeFilter(event_time)
GROUP BY time, service_name
ORDER BY time

Grafana macros ($__timeFilter, $__interval) keep panels time-aware. Alert rules can target the same query.

Tinybird variant: publish the SQL as a Pipe, consume JSON from Grafana Infinity datasource when you need parameterized endpoints shared with apps and Grafana.

Path B is not Prometheus exposition format. Use it when Grafana is the only consumer and you do not need Prometheus-native recording rules on that metric.

Path C: Remote write samples into ClickHouse

Teams outgrow Prometheus local retention (often 15–30 days) but still want PromQL-adjacent labels in long-term storage. Remote write (Prometheus native or via OpenTelemetry Collector) lands samples in ClickHouse for SQL analytics.

Prometheus / Grafana Agent / OTel Collector
  → remote write adapter (community or custom)
  → ClickHouse MergeTree (samples)
  → materialized views (1m / 5m rollups)
  → Grafana SQL or Tinybird `.prometheus` export for alerts

Sample table contract

CREATE TABLE prometheus.samples_raw
(
    timestamp       DateTime64(3),
    metric_name     LowCardinality(String),
    value           Float64,
    labels          Map(String, String),
    scrape_job      LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY (metric_name, timestamp);

Do not put unbounded label keys in the sort key. Filter on metric_name and time first.

One-minute rollup

CREATE TABLE prometheus.samples_1m
(
    minute          DateTime,
    metric_name     LowCardinality(String),
    service         LowCardinality(String),
    value_sum       Float64,
    value_count     UInt64,
    value_max       Float64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (metric_name, service, minute);

CREATE MATERIALIZED VIEW prometheus.samples_1m_mv TO prometheus.samples_1m AS
SELECT
    toStartOfMinute(timestamp) AS minute,
    metric_name,
    labels['service'] AS service,
    sum(value) AS value_sum,
    count() AS value_count,
    max(value) AS value_max
FROM prometheus.samples_raw
GROUP BY minute, metric_name, service;

Query samples_1m for dashboards older than a week. Keep raw samples 7–14 days with TTL:

ALTER TABLE prometheus.samples_raw
    MODIFY TTL timestamp + INTERVAL 14 DAY;

Label cardinality rules

Write these in the adapter config review checklist:

Hash or drop high-cardinality labels before insert. ClickHouse will store them; your bill and query time will not forgive you.

For OTLP metrics landing alongside Prometheus remote write, see clickhouse integration opentelemetry for per-signal schemas.

Remote write adapter operations

Production remote write into ClickHouse usually requires:

1. Label allowlist in the adapter config (drop trace_id, request_id, raw URLs)
2. Batch sizing — align with ClickHouse insert recommendations (thousands of rows per batch)
3. Retry policy — at-least-once delivery creates duplicate samples; rollups must use sum/max appropriately or dedupe with version columns
4. Backpressure — when ClickHouse insert lag grows, adapters should shed or sample, not unbounded buffer
5. Monitoring — track adapter lag, insert error rate, and samples dropped by label policy

Query remote-write history for long-window analytics:

SELECT
    toStartOfHour(minute) AS hour,
    metric_name,
    service,
    sum(value_sum) / sum(value_count) AS avg_value,
    max(value_max) AS peak_value
FROM prometheus.samples_1m
WHERE minute >= now() - INTERVAL 30 DAY
  AND metric_name = 'http_request_duration_seconds'
GROUP BY hour, metric_name, service
ORDER BY hour;

Keep PromQL recording rules for short-retention infra metrics. Use ClickHouse rollups when SQL must join request metrics to billing events or user cohorts.

Path D: Monitor Tinybird with Prometheus-format endpoints

Tinybird exposes Service Data Sources for workspace telemetry. Publish organization metrics as .prometheus endpoints for Grafana or Datadog.

The tinybird-org-metrics-exporter repo includes sample scrape configs and dashboards. Use Path D for platform monitoring (reads, writes, endpoint errors), not application business KPIs.

Recording rules vs SQL export

Rule: if the metric definition requires a join across tables Prometheus does not have, compute it in SQL and export via Path A. If the metric is a pure function of exporter samples, keep it in Prometheus.

Example metric that belongs in SQL export: checkout error rate weighted by revenue tier (join http_requests to accounts). Example that belongs in Prometheus: node CPU utilization from node_exporter.

Alerting without duplicate truth

Pick one canonical layer per alert:

Example Grafana alert on a Tinybird scrape target: fire when http_request_duration_seconds p95 > 0.5 for 10m. Same SQL should back the dashboard and the exported series.

Acceptance tests before production

1. Schema: Pipe returns rows with non-null name and numeric value for a known traffic window
2. Format: curl .prometheus URL; output parses in promtool check metrics (or Grafana scrape preview)
3. Auth: Invalid token returns 401; valid token returns 200 within SLA
4. Cardinality: Label permutation count documented; scrape series count stable week-over-week
5. Load: Scrape interval × query cost fits budget at peak traffic
6. Failure: Break upstream ingest; exported counters reflect drop within two scrape intervals
7. Drift: Add a label in SQL; confirm dashboards and alerts updated in same PR

Tinybird for Prometheus-friendly SQL metrics

Path A is the fastest route when ClickHouse already backs your product data and you need Prometheus-compatible scrape targets without operating a remote-write adapter.

Workflow:

1. Ingest events via the Events API or Kafka connector
2. Build rollups as materialized views or SQL Pipes aligned to scrape windows (1m, 5m)
3. Publish SLO Pipes with Prometheus column shape (name, value, labels, type, help)
4. Scrape https://api.tinybird.co/v0/pipes/<pipe>.prometheus from Grafana Agent or Prometheus
5. Monitor workspace health via Service Data Sources in the Workspace

Example Tinybird Pipe published as endpoint + Prometheus export:

NODE checkout_error_rate
SQL >
    SELECT
        'checkout_error_rate' AS name,
        countIf(status = 'error') / count() AS value,
        'Checkout error ratio last 5 minutes' AS help,
        'gauge' AS type,
        map('region', region) AS labels
    FROM checkout_events
    WHERE event_time >= now() - INTERVAL 5 MINUTE
    GROUP BY region

TYPE endpoint

Resend processes 100TB per month on Tinybird with 62ms p90 query latency without relying on cache, per Tinybird's Resend customer story. Tinybird is SOC 2 Type II certified.

Frequently Asked Questions (FAQs)

Should I scrape .prometheus or query ClickHouse from Grafana?

Scrape when you need Prometheus-native alerting, federation, or Datadog Prometheus intake. Query ClickHouse directly when Grafana is the only consumer and you want fewer moving parts.

Can one Pipe feed JSON apps and Prometheus scrapes?

Yes. Publish the Pipe as an endpoint. Apps call .json; scrapers call .prometheus. Same SQL, two formats.

Why does Grafana show stale values between scrapes?

Prometheus stores scraped snapshots. If you need second-level freshness, lower scrape interval or push gauges via a different path. SQL metrics recomputed on scrape always reflect the query window, not live streaming.

Is remote write better than scraping SQL metrics?

Remote write fits classic Prometheus instrumentation (client libraries, exporters). SQL export fits metrics defined over event tables (funnels, billing, pipeline lag). Many teams use both for different metric families.

How do I test cardinality before launch?

Run the export SQL, count distinct labels maps, multiply label values, compare to Prometheus series budget (often low tens of thousands per job, not millions).

Does Tinybird replace Prometheus?

No. Tinybird is managed ClickHouse with sub-second SQL APIs and .prometheus endpoint format. Prometheus (or Grafana Agent) remains the scraper and alert scheduler; ClickHouse/Tinybird remains the analytical store and SQL engine.

How do I export histograms from ClickHouse to Prometheus?

Prometheus histograms expect _bucket, _sum, and _count suffix series. In SQL export, either pre-compute bucket counts in rollups and emit multiple rows with consistent label sets, or export summary quantiles as gauges (type = 'gauge') and document that Grafana should not apply histogram_quantile to them.

What scrape interval should I use for SQL-computed metrics?

Match interval to query cost and freshness needs. Start with 60s for rollups over 5-minute windows. Drop to 30s only after measuring p95 Pipe latency under peak load. Sub-15s intervals on expensive SQL usually mean you need a materialized rollup, not a faster scrape.