---
title: "Keep clickhouse integration datadog queryable"
excerpt: "Datadog archives, OP pipelines, Federated Logs, API export into ClickHouse®. Ops in Datadog; long-retention SQL stays queryable."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-08-16 00:00:00"
publishedOn: "2026-08-16 00:00:00"
updatedOn: "2026-08-16 00:00:00"
status: "published"
---

Datadog owns incident response: monitors, Log Explorer, APM traces, on-call workflows. ClickHouse{% sup %}®{% /sup %} owns analytical SQL over high-volume events at storage costs that do not scale linearly with indexed log spend. A working **clickhouse integration datadog** setup keeps both systems in their lane instead of forcing one tool to do everything. For when each system should own which workload, see [ClickHouse vs Datadog](https://www.tinybird.co/blog/clickhouse-vs-datadog).

Most integration failures show up in postmortems the same way: archives land in S3 but nobody owns the loader into ClickHouse; Observability Pipelines dual-ship raw JSON without a table contract; a cron job hammers the Logs Search API and hits rate limits; on-call queries Datadog dashboards while product analytics queries ClickHouse and the same error rate differs because definitions diverged.

Datadog offers three fundamentally different ways to get data into ClickHouse. Pick one primary mode per signal type before you configure buckets or Workers.

## Three ingest modes: live, cold, and batch

| Mode | Datadog feature | ClickHouse role | Latency |
| --- | --- | --- | --- |
| **Live routing** | Observability Pipelines Worker | Real-time facts + rollups | Seconds |
| **Cold archive** | Log Archives → S3/GCS/Azure | Compliance + historical backfill | Hours |
| **Batch pull** | Logs Search / Metrics APIs | One-time export or aggregate sync | Minutes to hours |

Most production teams combine **live routing for facts** and **cold archive for compliance**, with API batch pull only for prototypes or aggregate-only syncs.

```text
Live traffic ──► Observability Pipelines Worker ──┬──► Datadog (indexed / monitors)
                                                  └──► ClickHouse (long retention / SQL)
Datadog Archives ──► S3 / GCS / Azure ──► scheduled loader ──► ClickHouse (cold / compliance)
Datadog APIs ──► batch worker ──► ClickHouse (backfill / custom reports)
```

## Observability Pipelines: the live path

Observability Pipelines collect and process logs and metrics inside your infrastructure, then route to destinations you choose. The Worker runs in your VPC on a Vector-based runtime.

Pipeline anatomy per Datadog docs:

1. **Source:** Datadog Agent, OpenTelemetry, or other supported intake
2. **Processors:** filter, enrich, redact PII, generate log-based metrics
3. **Destinations:** up to three per log pipeline; each destination type once per pipeline

Templates that matter for ClickHouse integrations: Dual Ship Logs, Sensitive Data Redaction, Log Enrichment, and Archive Logs for cold storage alongside ClickHouse.

### Native ClickHouse destination

Datadog documents a ClickHouse destination for Observability Pipelines (Preview). Configure it when you set up a pipeline in the UI, API, or Terraform.

Required:

- ClickHouse HTTP interface reachable from the Worker
- Target database and table with `INSERT` permission
- Format: `json_each_row` (default), `json_as_object`, `json_as_string`, or `arrow_stream` for higher throughput

```bash
export DD_OP_DESTINATION_CLICKHOUSE_ENDPOINT_URL="https://clickhouse.example.com:8443"
export DD_OP_DESTINATION_CLICKHOUSE_USERNAME="opw_writer"
export DD_OP_DESTINATION_CLICKHOUSE_PASSWORD="<secret>"
```

Default batching flushes at 10 MB or 1 second unless you override max events or timeout.

```text
Datadog Agent / OTel ──► OPW processors (redact, enrich) ──┬──► Datadog Logs intake
                                                           └──► ClickHouse HTTP insert
```

Set **Skip unknown fields** intentionally: fail on unknown fields in staging to catch schema drift; drop unknown fields in production when agents add attributes frequently.

### Processor chain before ClickHouse insert

Typical Observability Pipelines processor order for dual-ship:

1. **Filter:** Drop health-check noise (`status:info service:healthcheck`)
2. **Remap:** Normalize `service`, `status`, `env` to lowercase bounded values
3. **Redact:** Hash or remove PII fields (`email`, `credit_card`, raw `message` substrings)
4. **Log to metric:** Emit `error_count` for Datadog monitors on the indexed path only

Redaction belongs in the Worker, not in ClickHouse SQL at query time. If compliance requires proving redaction, log redaction counts to a side channel rather than storing raw PII "just for debugging."

Default Worker batching (10 MB or 1 second) balances insert throughput against latency. For product-facing APIs that read live facts, target sub-30-second end-to-end lag and alert when Worker-to-ClickHouse insert error rate exceeds 0.1% over 5 minutes.

### Federated Logs: query ClickHouse from Log Explorer

Federated Logs (Preview) lets engineers query logs stored in ClickHouse from Log Explorer without re-ingesting them. Pair live pipeline ingestion with Federated Logs when on-call should stay in Datadog while data remains in your cluster.

## Log Archives: S3 is not ClickHouse ingest

Log Archives forward ingested logs to cloud storage you control (S3, Azure Storage, or GCS). Archives support rehydration back into Datadog and archive search from Log Explorer. Neither feature writes directly to ClickHouse. The S3→ClickHouse leg is **your loader**.

Setup in Datadog docs: configure the cloud provider integration, create a private bucket with required object permissions, route logs on the Log Archiving & Forwarding page, and monitor archive upload metrics.

```sql
CREATE TABLE datadog_logs_archive
(
    event_time      DateTime64(3),
    service         LowCardinality(String),
    status          LowCardinality(String),
    host            String,
    tags            Map(String, String),
    message         String,
    attributes      JSON,
    dd_source       LowCardinality(String),
    dd_tags         Array(String),
    archive_path    String,
    ingested_at     DateTime64(3) DEFAULT now64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (service, status, event_time)
TTL event_time + INTERVAL 395 DAY;
```

Production loaders listen for object-created events, parse gzip-compressed archive objects, and batch-insert via [ClickHouse HTTP](https://clickhouse.com/docs/en/interfaces/http) `JSONEachRow`. Run idempotently with `ReplacingMergeTree(archive_path, line_offset)` so archive replays do not double-count rollups. S3 loader design and partition layout are covered in [ClickHouse integration Amazon S3](https://www.tinybird.co/blog/clickhouse-integration-amazon-s3).

Archive objects typically arrive as gzip JSON lines with Datadog metadata (`service`, `status`, `host`, `tags`, `attributes`). Parse tags into `Map(String, String)` at load time. Do not store `@dd.tags` as a single opaque string if dashboards filter on `env` or `version`.

Archives optimize for Datadog rehydrate and archive search. ClickHouse ingestion requires a separate pipeline that parses Datadog's archive format on a schedule or via object-storage triggers.

## API batch export when pipelines are not ready yet

When archives and pipelines are not available, or you need a one-time backfill from indexed logs, use Datadog's public APIs. There is no single export-all endpoint.

### Logs Search API

- **POST** `/api/v2/logs/events/search`
- Default page limit is 50; maximum is 1000 events per request
- For larger result sets, pass the cursor from the prior response

```bash
curl -L -X POST "https://api.datadoghq.com/api/v2/logs/events/search" \
  -H "Content-Type: application/json" \
  -H "DD-API-KEY: ${DD_API_KEY}" \
  -H "DD-APPLICATION-KEY: ${DD_APP_KEY}" \
  --data-raw '{
    "filter": {
      "from": "2026-08-11T00:00:00+00:00",
      "to": "2026-08-11T01:00:00+00:00",
      "query": "service:checkout status:error"
    },
    "page": { "limit": 1000 },
    "sort": "timestamp"
  }'
```

Insert each page into ClickHouse with multi-row `JSONEachRow` batches. Persist cursor and window ID per job so retries are idempotent.

For KPI backfill without raw events, use the Log Analytics API aggregate endpoint and store results in `SummingMergeTree` tables. For metric history, land points in a narrow table:

```sql
CREATE TABLE datadog_metrics
(
    metric_time   DateTime64(3),
    metric_name   LowCardinality(String),
    value         Float64,
    tags          Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(metric_time)
ORDER BY (metric_name, metric_time);
```

Keep Datadog monitors on native metric intake. Use ClickHouse metric tables for long-window joins to log rollups and product data.

API export suits backfill and aggregate syncs. Move live traffic to Observability Pipelines once validated.

## KPI ownership: who answers which question

Dual-write means the same telemetry stream is intentionally delivered to Datadog and ClickHouse with a written contract about which system is canonical for which question.

| Question type | Canonical system | Why |
| --- | --- | --- |
| Page on-call, live tail, trace+log correlation | Datadog | Monitors, indexed search, incident workflows |
| Product funnel, billing joins, ML features | ClickHouse | SQL over billions of rows, custom retention |
| Compliance archive browse | Datadog Archive Search or S3 | Native rehydrate permissions |
| 12-month error rate by `plan_tier` | ClickHouse rollup | Cheaper storage; join to product tables |

Document each KPI with both Datadog monitor query and ClickHouse SQL. Run a daily parity job comparing counts by `service` over the previous day:

```sql
SELECT
    toStartOfHour(event_time) AS hour,
    service,
    status,
    count() AS event_count
FROM datadog_logs_live
WHERE event_time >= today() - 1 AND event_time < today()
GROUP BY hour, service, status
ORDER BY hour, service;
```

Alert when any `(service, status)` hour diverges more than 1% from the Datadog log-based metric. On-call error rates should come from [real-time error monitoring](https://www.tinybird.co/blog/real-time-error-monitoring) patterns in Datadog; product SLO backtests come from ClickHouse rollups.

## Schema for Datadog tags and attributes

[MergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree) sort keys should lead with bounded dimensions, not unbounded tag keys.

| Column role | Guidance |
| --- | --- |
| `event_time` | `DateTime64(3)`; parse time zones explicitly in OPW |
| `service`, `status`, `env` | `LowCardinality(String)` when bounded |
| `tags` / `attributes` | `Map` or `JSON`; never lead the sort key with unbounded keys |
| Raw payload | `json_as_string` column only if you defer parsing |
| Dedup | Version column or content hash for at-least-once pipelines |

Rollups for long-range dashboards. Pre-aggregate with [materialized views](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example) instead of scanning raw tables for 30-day charts:

```sql
CREATE MATERIALIZED VIEW datadog_logs_5m
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (service, status, minute)
AS
SELECT
    toStartOfFiveMinutes(event_time) AS minute,
    service,
    status,
    count() AS event_count
FROM datadog_logs_live
GROUP BY minute, service, status;
```

Query rollups for ranges beyond 7 days. Reserve raw tables for incident drill-down. Log pipeline architecture at scale is covered in [real-time logs analytics architectures](https://www.tinybird.co/blog/real-time-logs-analytics-architectures).

## Acceptance tests before production

1. **Live path:** Observability Pipelines inserts succeed with fail-on-unknown enabled in staging
2. **Archive path:** Loader replays a 24-hour archive window without duplicate rows in rollups
3. **API path:** Backfill job checkpoints cursors and completes without rate-limit bans
4. **Parity:** Daily count job agrees with Datadog within 1% for top services
5. **Sort key:** `EXPLAIN` on top queries shows granule skipping on `service` and `event_time`
6. **APIs:** Product endpoints read from rollups, not raw tables, for ranges beyond 7 days

## Tinybird as the ClickHouse serving layer

If ClickHouse is the analytical store behind your Datadog integration, Tinybird is managed ClickHouse with ingestion, SQL Pipes, and HTTP APIs. You keep Datadog for on-call, indexed search, and monitors. Tinybird replaces the Kafka consumers, insert tuning, auth layer, and custom API code most teams bolt on after the integration works.

### Ingest paths that match Observability Pipelines output

Wire the same shaped events your Worker dual-ships to ClickHouse:

1. **Events API** — POST JSON from a small forwarder or directly from OPW HTTP output. Default limits: 100 requests per second per Data Source; 10 MB per request on Free plans, 100 MB on Developer, SaaS, and Enterprise plans. Use `wait=true` when you need write acknowledgement before responding upstream.
2. **Kafka connector** — Connect the topic your Worker or agent fan-out already writes. Supported setups include Confluent Platform and Confluent Cloud, Redpanda, and AWS MSK.

Both paths land rows in MergeTree Data Sources within seconds. No separate loader tier for product-facing queries.

### Model facts, rollups, and APIs as Pipes

Typical workflow:

1. Define a raw `datadog_logs` Data Source with `PARTITION BY toYYYYMM(event_time)` and `ORDER BY (service, status, event_time)`
2. Add a 5-minute rollup Pipe or materialized aggregation aligned to your Datadog monitor windows
3. Publish the rollup Pipe as an HTTP endpoint for product dashboards per [build real-time APIs on ClickHouse](https://www.tinybird.co/blog/build-real-time-apis-clickhouse-tinybird)
4. Monitor ingest lag and query latency via Service Data Sources in the workspace

Example Pipe for error rate by service (consumed by your app, not Datadog):

```sql
NODE service_error_rate
SQL >
    SELECT
        service,
        countIf(status IN ('error', 'critical')) AS errors,
        count() AS total,
        errors / total AS error_rate
    FROM datadog_logs
    WHERE event_time >= now() - INTERVAL 15 MINUTE
    GROUP BY service
    ORDER BY error_rate DESC

TYPE endpoint
```

### Tenant scoping and schema iteration

For product-facing log explorers, use JWTs with fixed parameters: values in `fixed_params` cannot be overridden from the URL, which fits row-level scoping by `organization_id` or `env`.

Tinybird offers schema iteration (safe migrations with zero downtime) and Branches (zero-copy environments with production data). Test new attribute mappings from Observability Pipelines on a Branch before production Worker rollout.

Canva reports 3.6 PB processed per month and 54 ms p99 query latency on Tinybird's product page. Resend reports 100 TB per month and 62 ms p90 query latency without relying on cache. Tinybird is SOC 2 Type II certified.

## 5 operational mistakes on Datadog ClickHouse integrations

### 1. Treating Log Archives as real-time ClickHouse ingest

Archives batch to object storage asynchronously. Loader lag of hours is normal.

**Fix:** Observability Pipelines for live facts. Archive loaders for compliance and backfill only.

### 2. Sort key led by `trace_id` or `user_id`

Datadog encourages high-cardinality facets for investigation. ClickHouse sort keys must lead with bounded dimensions.

**Fix:** `ORDER BY (service, status, event_time)`. Keep trace IDs in `attributes`.

### 3. OPW `json_each_row` without column allowlist

Agents add fields weekly. ClickHouse rejects unknown columns or silently drops fields you needed.

**Fix:** Stable core schema. Park extras in `JSON` or `Map(String, String)`. Test with fail-on-unknown in staging.

### 4. API backfill without cursor checkpointing

Logs Search API pages expire. A crashed worker duplicates rows or skips gaps.

**Fix:** Persist cursor and window ID per job. Rate-limit to documented pagination limits.

### 5. Two KPI definitions and no parity job

Datadog monitors fire on log-based metrics. Product teams query ClickHouse rollups. Filters differ and numbers disagree during incidents.

**Fix:** One definition document per KPI with both Datadog and SQL forms. Nightly parity query with alert on greater than 1% divergence.

## What the integration comes down to

Datadog integration with ClickHouse is not rip-and-replace. Datadog stays the system of record for on-call, indexed search, and monitors inside its retention window. ClickHouse holds long retention, high-cardinality SQL, joins to product data, and customer-facing analytics APIs.

Use Observability Pipelines for live dual-write, Log Archives for compliance backfill, and API export only where batch pull is enough. Write down which system owns each KPI before you wire the second destination.

## Frequently Asked Questions (FAQs)

### Does Datadog natively archive logs to ClickHouse?

No. Log Archives write to S3, Azure Storage, or GCS. ClickHouse ingestion uses Observability Pipelines' ClickHouse destination or a custom loader from archive storage.

### Can I query ClickHouse logs inside Datadog without re-ingesting?

Yes, with Federated Logs (Preview). Logs stay in ClickHouse; Log Explorer queries them in place alongside Datadog-indexed logs.

### How many destinations can one Observability Pipelines log pipeline have?

Up to three destinations per log pipeline, and each destination type only once per pipeline. Dual-ship Datadog and ClickHouse fits within that limit.

### What is the Logs Search API page size limit?

Default limit is 50; maximum is 1000 logs per request. Use cursor pagination for larger exports.

### Should I use API export or Observability Pipelines for ongoing ingest?

Observability Pipelines for live dual-write at volume. API export for backfill, prototypes, or aggregate-only syncs.

### How do metrics fit into a Datadog + ClickHouse integration?

Route live metrics through Observability Pipelines where supported, or query historical timeseries via the Metrics API into ClickHouse tables. Keep Datadog monitors on native metric intake.

### When should teams add Tinybird on top of ClickHouse?

When you want managed ingestion, SQL APIs, and schema workflows rather than operating ClickHouse clusters, insert tuning, and auth layers yourself.

{% cta
  title="SQL APIs on your Datadog log pipeline"
  text="Tinybird is managed ClickHouse with the Events API, Kafka connector, and sub-second SQL endpoints. Keep Datadog for ops; ship analytics from the same events."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}