---
title: "Keep clickhouse integration prometheus scrapeable"
excerpt: "Wire clickhouse integration prometheus via .prometheus Pipes, Grafana SQL, or remote write. Schemas and scrape configs that stay queryable."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-08-11 00:00:00"
publishedOn: "2026-08-11 00:00:00"
updatedOn: "2026-08-11 00:00:00"
status: "published"
---

Prometheus thinks in **scrapes, labels, and time series**. ClickHouse{% sup %}®{% /sup %} 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](https://prometheus.io/docs/instrumenting/exposition_formats/) 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**

| Path | You have | You want | Tradeoff |
| --- | --- | --- | --- |
| **A. Tinybird `.prometheus` Pipes** | SQL metrics in ClickHouse/Tinybird | Grafana/Datadog scrape HTTP endpoints | Recompute on scrape; keep queries fast |
| **B. Grafana → ClickHouse SQL** | Tables already in ClickHouse | Dashboards + alerts without exposition format | Not Prometheus-native; simpler ops |
| **C. Remote write → ClickHouse** | Prometheus/OTel as collector | Months of sample history + SQL | Adapter ops + label discipline |
| **D. Monitor Tinybird org** | Tinybird workspaces | Prometheus-style org metrics | Platform monitoring, not app KPIs |

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

```text
                    ┌── 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](https://www.tinybird.co/) publishes SQL Pipes as HTTP endpoints. Append `.prometheus` to return exposition format per [Tinybird Prometheus docs](https://www.tinybird.co/docs/publish/api-endpoints/guides/consume-api-endpoints-in-prometheus-format).

### **Required output schema**

| Column | Type | Required | Notes |
| --- | --- | --- | --- |
| `name` | String | Yes | Metric name (`http_request_count`) |
| `value` | Number | Yes | Must be numeric |
| `help` | String | No | `# HELP` line in output |
| `type` | String | No | `counter`, `gauge`, `histogram`, `summary`, `untyped` |
| `labels` | Map(String, String) | No | Label set for the series |
| `timestamp` | Number | No | Unix timestamp if not "now" |

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

### **Pipe SQL: request volume + latency gauges**

```sql
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:

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

Example output shape (truncated):

```text
# 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:

```yaml
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:

```bash
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](https://www.tinybird.co/blog/clickhouse-integration-grafana) avoids an extra scrape hop.

```sql
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.

```text
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**

```sql
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**

```sql
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:

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

### **Label cardinality rules**

Write these in the adapter config review checklist:

| Label | Usually OK | Usually forbidden |
| --- | --- | --- |
| `service`, `region`, `env`, `status_class` | Yes | |
| `route`, `queue`, `topic` | Yes if bounded | |
| `user_id`, `trace_id`, `request_id` | | Never as Prometheus labels |
| `url` full path | | Use templated route |

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](https://www.tinybird.co/blog/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:

```sql
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](https://www.tinybird.co/docs/monitoring/service-datasources) for workspace telemetry. Publish organization metrics as `.prometheus` endpoints for Grafana or Datadog.

The [tinybird-org-metrics-exporter](https://github.com/tinybirdco/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**

| Approach | Best for | Weak for |
| --- | --- | --- |
| Prometheus recording rules | Infra rates, pre-aggregated PromQL | Joins to event/billing tables |
| Tinybird `.prometheus` Pipes | Business KPIs, funnel metrics, pipeline lag | Sub-second push gauges |
| Remote write → ClickHouse | Long-retention PromQL labels | Ad hoc SQL without rollups |

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:

| Alert type | Canonical source | Notes |
| --- | --- | --- |
| Infra saturation (CPU, disk) | Prometheus node exporters | Keep in Prometheus |
| App SLO from events | ClickHouse rollup or Tinybird Pipe | Export via Path A |
| Business KPI | SQL Pipe | Do not double-write to Prometheus AND ClickHouse |

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](https://www.tinybird.co/docs/ingest/events-api) or [Kafka connector](https://www.tinybird.co/docs/forward/ingest-data/connectors/kafka)
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](https://www.tinybird.co/)

Example Tinybird Pipe published as endpoint + Prometheus export:

```sql
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](https://www.tinybird.co/customer-stories/resend). 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.

{% cta
  title="Prometheus endpoints from SQL"
  text="Tinybird publishes Pipes in Prometheus format. Managed ClickHouse, streaming ingestion, and sub-second SQL APIs in one platform."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}