---
title: "ClickHouse IoT data"
excerpt: "IoT deployments generate time-series telemetry at a rate that most databases can't ingest and query simultaneously. Thousands of devices reporting every second, anomaly detection that needs to run in real time, and dashboards that need to show fleet-wide metrics without scanning terabytes of raw readings. ClickHouse is built for exactly this pattern."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-06-15 00:00:00"
publishedOn: "2026-06-15 00:00:00"
updatedOn: "2026-06-15 00:00:00"
status: "published"
---

IoT deployments produce time-series data at a rate that quickly overwhelms traditional databases. A fleet of 10,000 devices reporting every 10 seconds generates 86.4 million readings per day. At 100,000 devices, that's 864 million. At a million devices, you are ingesting billions of rows daily, and you still need to answer questions about current device state, anomalies in the last hour, and fleet-wide trends over the past 90 days, all in under a second.

ClickHouse® handles this workload because it was designed for exactly this pattern: high-throughput append-only writes, columnar storage that compresses time-series data efficiently, and vectorized execution that processes billions of rows to answer aggregation queries fast.

This post covers the device telemetry schema, time-series aggregation patterns, anomaly detection, fleet analytics, data lifecycle management, and the materialized view patterns that keep IoT dashboards fast at scale.

## Device telemetry schema

IoT data is append-only by nature. Device readings don't change after they are recorded. The `MergeTree` engine, sorted by device and time, is the right foundation:

```sql
CREATE TABLE device_readings
(
    event_time      DateTime64(3),
    device_id       String,
    fleet_id        LowCardinality(String),
    device_type     LowCardinality(String),  -- 'temperature_sensor', 'pressure_gauge', 'flow_meter'
    location_id     LowCardinality(String),
    metric_name     LowCardinality(String),  -- 'temperature', 'pressure', 'flow_rate', 'battery_pct'
    metric_value    Float64,
    metric_unit     LowCardinality(String),  -- 'celsius', 'bar', 'l/min', 'pct'
    quality         LowCardinality(String),  -- 'good', 'uncertain', 'bad'
    latitude        Nullable(Float32),
    longitude       Nullable(Float32),
    firmware_ver    LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (fleet_id, device_type, device_id, metric_name, event_time);
```

The sort key puts `fleet_id` and `device_type` first because most operational queries filter by fleet or device type before device ID. For queries that filter by device ID directly (current state of a specific device), `device_id` is third in the key so ClickHouse can skip non-matching granules efficiently. `LowCardinality` on `fleet_id`, `device_type`, `location_id`, `metric_name`, `metric_unit`, `quality`, and `firmware_ver` keeps GROUP BY on these columns fast. According to ClickHouse's official documentation, `LowCardinality` provides the most benefit for columns with fewer than 10,000 distinct values, which covers all of these.

## Current device state

The most common dashboard query for IoT: what is the current reading for each device in a fleet?

```sql
SELECT
    device_id,
    metric_name,
    argMax(metric_value, event_time)    AS current_value,
    argMax(metric_unit, event_time)     AS unit,
    argMax(quality, event_time)         AS quality,
    max(event_time)                     AS last_seen,
    dateDiff('second', max(event_time), now()) AS seconds_since_last
FROM device_readings
WHERE fleet_id = {{ String(fleet_id, required=True) }}
  AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY device_id, metric_name
ORDER BY seconds_since_last DESC;
```

The `argMax` pattern retrieves the latest value per device-metric pair without needing `ReplacingMergeTree` or background deduplication. Devices that haven't reported in the last hour, high `seconds_since_last` values, appear at the top of the result, making offline device detection trivial.

## Time-series aggregation and downsampling

Raw readings at 10-second intervals are expensive to query over long time ranges. A materialized view pre-aggregates to 1-minute buckets using `AggregatingMergeTree`, reducing the data volume for dashboard queries by 6x at the 1-minute level and 360x at the 1-hour level:

```sql
CREATE TABLE device_readings_1m
(
    minute          DateTime,
    device_id       String,
    fleet_id        LowCardinality(String),
    metric_name     LowCardinality(String),
    avg_value_state AggregateFunction(avg, Float64),
    min_value_state AggregateFunction(min, Float64),
    max_value_state AggregateFunction(max, Float64),
    p95_value_state AggregateFunction(quantile(0.95), Float64),
    reading_count   UInt32
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (fleet_id, device_id, metric_name, minute);

CREATE MATERIALIZED VIEW device_readings_1m_mv TO device_readings_1m AS
SELECT
    toStartOfMinute(event_time)         AS minute,
    device_id,
    fleet_id,
    metric_name,
    avgState(metric_value)              AS avg_value_state,
    minState(metric_value)              AS min_value_state,
    maxState(metric_value)              AS max_value_state,
    quantileState(0.95)(metric_value)   AS p95_value_state,
    count()                             AS reading_count
FROM device_readings
WHERE quality = 'good'
GROUP BY minute, device_id, fleet_id, metric_name;
```

Querying the pre-aggregated table for trend analysis over 30 days:

```sql
SELECT
    toStartOfHour(minute)                   AS hour,
    avgMerge(avg_value_state)               AS avg_value,
    minMerge(min_value_state)               AS min_value,
    maxMerge(max_value_state)               AS max_value,
    quantileMerge(0.95)(p95_value_state)    AS p95_value,
    sum(reading_count)                      AS total_readings
FROM device_readings_1m
WHERE fleet_id = {{ String(fleet_id, required=True) }}
  AND device_id = {{ String(device_id, required=True) }}
  AND metric_name = {{ String(metric_name, 'temperature') }}
  AND minute >= now() - INTERVAL 30 DAY
GROUP BY hour
ORDER BY hour;
```

This query scans the 1-minute rollup rather than 30 days of raw 10-second readings. The data volume is 6x smaller and the query returns proportionally faster.

## Anomaly detection on sensor data

Z-score against a device's own 30-day baseline identifies sensor readings that are statistically unusual, distinguishing real anomalies from expected variance:

```sql
WITH device_baselines AS (
    SELECT
        device_id,
        metric_name,
        avgMerge(avg_value_state)       AS baseline_avg,
        stddevPopMerge(stddev_state)    AS baseline_stddev
    FROM (
        SELECT
            device_id,
            metric_name,
            avgState(metric_value)          AS avg_value_state,
            stddevPopState(metric_value)    AS stddev_state
        FROM device_readings
        WHERE quality = 'good'
          AND event_time >= today() - 30
        GROUP BY device_id, metric_name
    )
    GROUP BY device_id, metric_name
)
SELECT
    r.device_id,
    r.fleet_id,
    r.metric_name,
    r.metric_value,
    r.event_time,
    b.baseline_avg,
    round((r.metric_value - b.baseline_avg) / nullIf(b.baseline_stddev, 0), 2) AS z_score
FROM device_readings AS r
INNER JOIN device_baselines AS b USING (device_id, metric_name)
WHERE r.event_time >= now() - INTERVAL 15 MINUTE
  AND r.quality = 'good'
  AND abs((r.metric_value - b.baseline_avg) / nullIf(b.baseline_stddev, 0)) > 3
ORDER BY abs(z_score) DESC
LIMIT 100;
```

## Fleet-wide operational analytics

For operations teams monitoring a whole fleet in real time:

```sql
SELECT
    toStartOfHour(event_time)                   AS hour,
    fleet_id,
    device_type,
    count()                                     AS total_readings,
    uniq(device_id)                             AS active_devices,
    countIf(quality = 'bad')                    AS bad_quality_readings,
    round(
        countIf(quality = 'bad') / count() * 100, 2
    )                                           AS bad_quality_pct,
    countIf(
        dateDiff('second', event_time, now()) > 60
    )                                           AS stale_readings
FROM device_readings
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour, fleet_id, device_type
ORDER BY hour DESC, bad_quality_pct DESC;
```

High `bad_quality_pct` by `device_type` points to sensor calibration issues. High `stale_readings` by `fleet_id` points to connectivity problems. Both are operationally actionable signals that the query surfaces in milliseconds across millions of readings.

## Data lifecycle management with TTL

IoT data has different value at different ages. Raw 10-second readings matter for troubleshooting recent incidents. Older data is useful only for trend analysis, which the 1-minute rollup table already handles. TTL rules automatically tier data to cheaper storage as it ages:

```sql
ALTER TABLE device_readings MODIFY TTL
    event_time + INTERVAL 7 DAY TO VOLUME 'warm',    -- SSD-backed warm tier
    event_time + INTERVAL 90 DAY TO VOLUME 'cold',   -- S3-backed cold tier
    event_time + INTERVAL 2 YEAR DELETE;             -- delete raw after 2 years
```

The 1-minute rollup table retains indefinitely since it is already compact. Raw readings older than 90 days move to object storage where the cost per GB is a fraction of SSD-backed storage. Queries against the 1-minute rollup table for historical trend analysis are unaffected by the TTL policy on the raw table.

## Geospatial fleet tracking

For mobile IoT deployments (vehicles, shipping containers, field equipment), tracking last known position and computing distance-based metrics:

```sql
SELECT
    device_id,
    fleet_id,
    argMax(latitude, event_time)        AS last_lat,
    argMax(longitude, event_time)       AS last_lon,
    max(event_time)                     AS last_seen,
    dateDiff('minute', max(event_time), now()) AS minutes_offline,
    count()                             AS readings_24h
FROM device_readings
WHERE fleet_id = {{ String(fleet_id, required=True) }}
  AND latitude IS NOT NULL
  AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY device_id, fleet_id
ORDER BY minutes_offline DESC;
```

For geofence alerting, devices that have transmitted a position outside an expected boundary:

```sql
SELECT
    device_id,
    argMax(latitude, event_time)    AS last_lat,
    argMax(longitude, event_time)   AS last_lon,
    max(event_time)                 AS last_seen
FROM device_readings
WHERE fleet_id = {{ String(fleet_id, required=True) }}
  AND latitude IS NOT NULL
  AND event_time >= now() - INTERVAL 15 MINUTE
GROUP BY device_id
HAVING last_lat NOT BETWEEN {{ Float32(min_lat, 40.0) }} AND {{ Float32(max_lat, 42.0) }}
    OR last_lon NOT BETWEEN {{ Float32(min_lon, -74.0) }} AND {{ Float32(max_lon, -72.0) }};
```

The parameterized bounding box means one endpoint serves multiple geofenced zones, with the zone coordinates passed as query parameters. At query time, ClickHouse evaluates this against the `argMax` result for each device in under 100ms even across thousands of devices.

## Tinybird for IoT

IoT analytics has the same two problems at every scale: getting data in fast enough and querying it fast enough. The ingestion problem and the query problem usually require separate infrastructure choices, which is why IoT analytics architectures often end up with MQTT brokers, Kafka topics, stream processors, and analytical databases all requiring separate operation.

For [real-time data ingestion](https://www.tinybird.co/blog/real-time-data-ingestion) at IoT scale, Tinybird's Events API accepts batched NDJSON over HTTP, making readings queryable within seconds of device transmission. For higher-volume deployments, the Kafka connector consumes from your existing broker topics. Either path writes directly to Tinybird datasources without a separate stream processing layer.

The [clickhouse create materialized view example](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example) pattern applies directly: define the 1-minute rollup as a materialized view in Tinybird, and the pre-aggregation happens automatically as data arrives. Your dashboard queries read the rollup. [Backfilling materialized views](https://www.tinybird.co/blog/backfilling-materialized-views-diy-vs-tinybird-and-best-practices) from historical raw data, when you add a new rollup after the raw table already has months of data, is a common operational challenge that Tinybird handles with built-in tooling.

The anomaly detection query becomes a Tinybird Pipe published as an HTTP endpoint. Your alerting system calls it every minute, gets the list of devices with Z-score above 3, and fires alerts. The fleet dashboard query becomes another Pipe. [Real-time data visualization](https://www.tinybird.co/blog/real-time-data-visualization) tools like Grafana, Metabase, or your own frontend call these endpoints directly.

For [building real-time apps](https://www.tinybird.co/blog/build-real-time-apps) on top of IoT data, where device owners need to see their own equipment's performance, each endpoint enforces `fleet_id` or `device_id` parameter scoping. One endpoint serves all tenants, with the SQL enforcing data isolation. No application-level row filtering required.

Tinybird is SOC 2 Type II certified. Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Your fleet dashboard, current-state endpoint, and anomaly detector all run inside that latency envelope. Tinybird handles cluster scaling, replication, TTL execution, and rollup maintenance. Your team writes SQL and ships product features.

{% cta
  title="IoT analytics at device scale"
  text="Tinybird is managed ClickHouse with streaming ingestion and real-time SQL endpoints. Query billions of sensor readings in milliseconds."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}