ClickHouse for telecom CDR and network analytics

A mobile operator processing 200M voice CDRs and 1B data session records per day cannot run network KPI dashboards off a row-oriented billing replica. NOC teams need five-minute drop rates by cell. Fraud teams need SQL over roaming patterns across six hours. Product teams need usage APIs scoped by subscriber without waiting for the nightly mediation batch to Redshift.

ClickHouse^® fits telecom analytics because charging data records (CDRs) are append-only, dimensions repeat (MCC/MNC, cell, result codes), and queries are aggregations over time windows. Per ClickHouse MergeTree documentation, the cost model is bytes read per query, not rows stored. Columnar storage compresses repetitive CDR fields (same MNC, same result code across millions of rows). MergeTree partitioning drops old months without DELETE storms.

This post covers CDR types, mediation ingest, schema contracts, QoS and capacity KPIs, fraud patterns, deduplication, rollups, and the mistakes that break production telecom ClickHouse clusters.

CDR types and what each table needs

Telecom analytics mixes several record families. Treat them as separate fact tables instead of one "events" blob.

3GPP charging specifications define CDR structure for circuit-switched and packet-switched services. Your mediation layer normalizes vendor-specific ASN.1 or proprietary formats into a canonical schema before analytics sees rows. Network analytics cares about result codes and cell IDs. Billing analytics cares about rate plans and charge amounts. Same raw feed, different sort keys and retention policies.

Mediation pipeline layout

Typical flow from network elements to analytics:

MSC / PGW / SMSC / IMS
  → mediation (normalize, enrich MCC/MNC, geo, rate plan)
  → Kafka (partitioned) or S3 (batch)
  → ClickHouse MergeTree
  → materialized views → hourly/daily rollups → NOC / fraud / APIs

Batch path: SFTP CSV/AVRO drops to S3, INSERT via ClickHouse s3 table function or external loader. Fits billing reconciliation, regulatory archive backfill, and replay after mediation bug fixes.

Streaming path: Kafka topics partitioned by region or toStartOfHour(event_time) so consumers keep pace with peak CDR spikes. Per ClickHouse Kafka engine guidance, the standard pattern is a Kafka engine table, materialized view, and MergeTree destination. That matches the three-part setup in how to stream Kafka to ClickHouse: ClickHouse consumes from Kafka automatically with consumer group management and offset tracking handled by the engine.

HTTP path: Probe metrics and OSS counters from edge collectors via NDJSON POST when Kafka is overkill for smaller feeds.

Single-row inserts at CDR volume create too many parts and trigger merge backlog. ClickHouse documentation recommends batch inserts of thousands to hundreds of thousands of rows; at scale, teams report millions of rows per second per server when batches are sized correctly. Mediation should microbatch before ClickHouse sees rows.

CDR fact table

Denormalize attributes NOC and fraud filters hit on every query. Do not normalize subscriber or cell dimensions into join tables for the hot path. Telecom dashboards filter region → service → time on every refresh; joins at query time over billions of CDRs fail SLOs.

CREATE TABLE cdr_events
(
    event_time         DateTime64(3),
    cdr_id             String,
    subscriber_id      String,
    imsi               String,
    msisdn             String,
    call_type          LowCardinality(String),   -- voice, sms, data
    direction          LowCardinality(String),   -- mo, mt
    result             LowCardinality(String),   -- success, failed, dropped
    release_cause      LowCardinality(String),   -- vendor-normalized cause code
    duration_sec       UInt32,
    data_bytes_up      UInt64,
    data_bytes_down    UInt64,
    apn                LowCardinality(String),
    destination_prefix LowCardinality(String),
    mcc                LowCardinality(String),
    mnc                LowCardinality(String),
    lac                UInt32,
    cell_id            String,
    region             LowCardinality(String),
    roaming            UInt8,
    partner_mcc        LowCardinality(String),
    charge_amount      Float64,
    currency           LowCardinality(String),
    mediation_version  UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, call_type, result, event_time, subscriber_id);

ORDER BY (region, call_type, result, event_time, subscriber_id) matches NOC dashboards: filter region and service, slice time, drill to subscriber. Put subscriber_id later unless most queries start from a single account lookup.

Per ClickHouse LowCardinality documentation, LowCardinality on MCC/MNC, call_type, and result keeps GROUP BY on network dimensions fast when distinct values stay in the low thousands.

DateTime64(3) preserves sub-second ordering when mediation timestamps events within the same second. Use UTC in storage; convert to local NOC timezone in the presentation layer.

Signaling events and probe metrics

SS7/Diameter signaling and radio probe counters fit a long/narrow schema separate from CDR facts:

CREATE TABLE signaling_events
(
    event_time       DateTime64(3),
    procedure        LowCardinality(String),  -- attach, detach, pdn_connect
    protocol         LowCardinality(String),  -- diameter, sip, map
    result_code      LowCardinality(String),
    latency_ms       UInt32,
    subscriber_id    String,
    imsi             String,
    region           LowCardinality(String),
    node_id          LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, procedure, result_code, event_time);

CREATE TABLE network_metrics
(
    event_time    DateTime64(3),
    probe_id      LowCardinality(String),
    region        LowCardinality(String),
    cell_id       String,
    metric_name   LowCardinality(String),
    metric_value  Float64,
    severity      LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, metric_name, probe_id, event_time);

Correlate voice drops with radio congestion by joining CDR aggregates to probe metrics on cell_id and time bucket in application SQL or Pipes. Do not wide-join at ingest unless join keys are stable and cardinality is controlled.

Attach failure storm detection:

SELECT
    toStartOfMinute(event_time) AS minute,
    region,
    node_id,
    count() AS attach_attempts,
    countIf(result_code != '2001') AS failures,
    failures / attach_attempts AS failure_rate,
    quantile(0.95)(latency_ms) AS p95_latency_ms
FROM signaling_events
PREWHERE procedure = 'attach' AND region = 'eu-west'
WHERE event_time >= now() - INTERVAL 30 MINUTE
GROUP BY minute, region, node_id
HAVING attach_attempts >= 100
ORDER BY failure_rate DESC;

QoS KPI queries

Five-minute traffic and failure volume by region:

SELECT
    toStartOfFiveMinute(event_time) AS bucket,
    region,
    call_type,
    count() AS events,
    countIf(result != 'success') AS failed_events,
    failed_events / events AS failure_rate,
    sum(duration_sec) AS total_duration_sec,
    sum(data_bytes_up + data_bytes_down) AS total_data_bytes
FROM cdr_events
WHERE event_time >= now() - INTERVAL 1 HOUR
GROUP BY bucket, region, call_type
ORDER BY bucket, region, call_type;

Answer seizure ratio (ASR) for voice:

SELECT
    toStartOfHour(event_time) AS hour,
    region,
    countIf(result IN ('success', 'dropped')) AS answered_or_attempted,
    countIf(result = 'success') AS answered,
    answered / answered_or_attempted AS asr
FROM cdr_events
PREWHERE call_type = 'voice'
WHERE event_time >= today() - 7
GROUP BY hour, region
ORDER BY hour, region;

Top cells by voice drop rate (minimum volume threshold):

SELECT
    cell_id,
    region,
    count() AS voice_attempts,
    countIf(result = 'dropped') AS drops,
    drops / voice_attempts AS drop_rate
FROM cdr_events
PREWHERE call_type = 'voice'
WHERE event_time >= now() - INTERVAL 15 MINUTE
GROUP BY cell_id, region
HAVING voice_attempts >= 200
ORDER BY drop_rate DESC
LIMIT 50;

Use PREWHERE when the filtered column appears early in the sort key. Per ClickHouse PREWHERE documentation, it reduces bytes read before the time filter on billion-row tables. The five rules for faster SQL apply directly: align filters with sort keys, pre-aggregate for long windows, and avoid SELECT *.

Capacity planning and congestion correlation

Capacity teams need sustained utilization, not just failure counts. Combine CDR volume with probe metrics:

WITH cdr_by_cell AS (
    SELECT
        toStartOfFiveMinute(event_time) AS bucket,
        cell_id,
        region,
        count() AS voice_attempts,
        sum(duration_sec) AS total_talk_time_sec
    FROM cdr_events
    PREWHERE call_type = 'voice'
    WHERE event_time >= now() - INTERVAL 2 HOUR
    GROUP BY bucket, cell_id, region
),
probe_by_cell AS (
    SELECT
        toStartOfFiveMinute(event_time) AS bucket,
        cell_id,
        region,
        avgIf(metric_value, metric_name = 'prb_utilization_pct') AS avg_prb_util,
        avgIf(metric_value, metric_name = 'active_users') AS avg_active_users
    FROM network_metrics
    WHERE event_time >= now() - INTERVAL 2 HOUR
    GROUP BY bucket, cell_id, region
)
SELECT
    c.bucket,
    c.cell_id,
    c.region,
    c.voice_attempts,
    c.total_talk_time_sec,
    p.avg_prb_util,
    p.avg_active_users
FROM cdr_by_cell AS c
LEFT JOIN probe_by_cell AS p
    ON c.bucket = p.bucket AND c.cell_id = p.cell_id AND c.region = p.region
WHERE p.avg_prb_util > 80
ORDER BY p.avg_prb_util DESC;

Cells with high PRB utilization and rising drop rates are expansion candidates. Cells with high drops but low utilization point to core or interconnect issues, not radio capacity.

Data session analytics by APN

Product and network teams slice data CDRs by APN to understand tethering plans, IoT profiles, and enterprise VPN usage:

SELECT
    toStartOfHour(event_time) AS hour,
    region,
    apn,
    count() AS sessions,
    countIf(result = 'failed') AS failed_sessions,
    failed_sessions / sessions AS failure_rate,
    sum(data_bytes_up + data_bytes_down) / 1024 / 1024 / 1024 AS total_gb
FROM cdr_events
PREWHERE call_type = 'data'
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY hour, region, apn
ORDER BY hour DESC, total_gb DESC;

Session setup latency proxy (time from first to last record in a session, when mediation emits partial records):

SELECT
    apn,
    quantile(0.95)(duration_sec) AS p95_session_duration_sec,
    quantile(0.95)(data_bytes_down) AS p95_download_bytes,
    count() AS sessions
FROM cdr_events
PREWHERE call_type = 'data' AND result = 'success'
WHERE event_time >= today() - 1
GROUP BY apn
ORDER BY sessions DESC;

IoT APNs with high session counts and low bytes often indicate stuck PDP contexts. Consumer APNs with rising failure rates often trace to PGW capacity or DNS issues, not radio.

Interconnect and wholesale revenue assurance

Interconnect disputes require matching inbound/outbound minute and SMS counts against partner settlements:

SELECT
    toStartOfDay(event_time) AS day,
    partner_mcc,
    destination_prefix,
    call_type,
    direction,
    sum(duration_sec) / 60 AS total_minutes,
    count() AS events,
    sum(charge_amount) AS billed_amount
FROM cdr_events
WHERE event_time >= today() - 30
  AND roaming = 1
GROUP BY day, partner_mcc, destination_prefix, call_type, direction
ORDER BY day, billed_amount DESC;

Compare billed_amount rollups against partner invoices. Mismatch patterns (systematic under-count on MO voice to one MCC) usually trace to mediation mapping errors, not ClickHouse query bugs.

Ingest health and consumer lag

Telecom peaks are predictable (New Year's Eve, major events). Monitor pipeline health before NOC trusts dashboards:

SELECT
    toStartOfMinute(now()) AS check_time,
    max(event_time) AS latest_cdr,
    dateDiff('second', latest_cdr, now()) AS ingest_lag_sec,
    count() AS rows_last_5m
FROM cdr_events
WHERE event_time >= now() - INTERVAL 5 MINUTE;

Alert when ingest_lag_sec exceeds 120 during business hours. Pair with Kafka consumer lag on mediation topics. Per ClickHouse Kafka engine docs, consumer settings (kafka_max_block_size, kafka_poll_timeout_ms) affect batch size and lag under spike load.

Query system.parts for partition part counts after major events:

SELECT
    partition,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk
FROM system.parts
WHERE database = currentDatabase()
  AND table = 'cdr_events'
  AND active
GROUP BY partition
ORDER BY active_parts DESC;

Sustained active_parts > 300 per partition signals insert batches are too small or merges cannot keep up.

Fraud and roaming analytics

Multi-region roaming in a short window:

SELECT
    subscriber_id,
    imsi,
    uniq(region) AS regions_seen,
    uniq(partner_mcc) AS partner_networks,
    count() AS roaming_events,
    sum(charge_amount) AS total_charges
FROM cdr_events
WHERE event_time >= now() - INTERVAL 6 HOUR
  AND roaming = 1
GROUP BY subscriber_id, imsi
HAVING regions_seen >= 3
ORDER BY total_charges DESC;

Premium-rate short-call pattern:

SELECT
    subscriber_id,
    destination_prefix,
    count() AS calls,
    avg(duration_sec) AS avg_duration,
    sum(charge_amount) AS charges
FROM cdr_events
WHERE event_time >= now() - INTERVAL 24 HOUR
  AND call_type = 'voice'
  AND result = 'success'
GROUP BY subscriber_id, destination_prefix
HAVING calls >= 50 AND avg_duration < 5
ORDER BY charges DESC;

SIM box / gateway fraud (high MO SMS volume, low unique destinations):

SELECT
    subscriber_id,
    imsi,
    count() AS sms_count,
    uniq(destination_prefix) AS unique_destinations,
    sms_count / unique_destinations AS msgs_per_dest
FROM cdr_events
PREWHERE call_type = 'sms' AND direction = 'mo'
WHERE event_time >= now() - INTERVAL 1 HOUR
GROUP BY subscriber_id, imsi
HAVING sms_count >= 500 AND msgs_per_dest > 20
ORDER BY sms_count DESC;

Fraud models combine CDR SQL features with external lists (premium prefixes, SIM swap events, device fingerprint blocks). ClickHouse handles feature extraction windows; rules engines handle decisions. For velocity-based alerts, see real-time anomaly detection patterns that compare current windows to baselines.

Deduplication when mediation replays

Mediation replays and late CDRs create duplicates. Per ReplacingMergeTree documentation, deduplication occurs during background merges, not at insert time. Do not rely on it for immediate correctness.

CREATE TABLE cdr_events_dedup
(
    event_time         DateTime64(3),
    cdr_id             String,
    -- same columns as cdr_events ...
    mediation_version  UInt64
)
ENGINE = ReplacingMergeTree(mediation_version)
PARTITION BY toYYYYMM(event_time)
ORDER BY (cdr_id, event_time);

Query with FINAL only in batch reports. Serving dashboards should read hourly rollups built from deduplicated inserts or use argMax(column, mediation_version) on raw tables for latest state without FINAL:

SELECT
    cdr_id,
    argMax(result, mediation_version) AS result,
    argMax(charge_amount, mediation_version) AS charge_amount,
    max(mediation_version) AS latest_version
FROM cdr_events_dedup
WHERE event_time >= today() - 1
GROUP BY cdr_id;

Hourly rollups and retention tiers

Raw CDRs stay hot 30–90 days. Rollups keep 12–24 months for capacity planning and regulatory reporting.

CREATE TABLE cdr_hourly
(
    hour             DateTime,
    region           LowCardinality(String),
    call_type        LowCardinality(String),
    result           LowCardinality(String),
    events           UInt64,
    total_duration   UInt64,
    total_data_bytes UInt64,
    total_charges    Float64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (region, call_type, result, hour);

CREATE MATERIALIZED VIEW cdr_hourly_mv TO cdr_hourly AS
SELECT
    toStartOfHour(event_time) AS hour,
    region,
    call_type,
    result,
    count() AS events,
    sum(duration_sec) AS total_duration,
    sum(data_bytes_up + data_bytes_down) AS total_data_bytes,
    sum(charge_amount) AS total_charges
FROM cdr_events
GROUP BY hour, region, call_type, result;

TTL on raw facts:

ALTER TABLE cdr_events MODIFY TTL event_time + INTERVAL 90 DAY;
ALTER TABLE cdr_hourly MODIFY TTL hour + INTERVAL 730 DAY;

Billing marts may require 7-year retention on aggregated revenue; network ops rarely need raw CDRs beyond 90 days when hourly rollups exist. Split retention policies by table, not one global TTL.

Tinybird for telecom analytics

Telecom teams usually already run Kafka for mediation output. The gap is a query layer that keeps up with peak CDR volume and exposes NOC KPIs as APIs without operating ClickHouse clusters.

Tinybird is a managed ClickHouse platform with streaming ingestion, SQL transformations, and sub-second SQL APIs. Per Tinybird's product page, it runs native ClickHouse and includes schema iteration, Branches (zero-copy environments with production data), and workspace monitoring.

Ingest paths that match telecom volume:

1. Kafka connector — Consumes from Kafka topics into Tinybird Data Sources. Per Kafka connector documentation, supported setups include Confluent Platform and Confluent Cloud, Redpanda, and AWS MSK (including IAM/OAuthBearer authentication).
2. Events API — NDJSON microbatch over HTTP. Per Events API documentation, default capacity is up to 100 requests per second per Data Source and up to 10 MB per request on Free plans (100 MB on Developer, SaaS, and Enterprise plans). The docs recommend microbatching several events per request for higher throughput. Gzip compression is supported.

From SQL to NOC APIs:

Define Data Sources with partition and sort keys aligned to region, call_type, and event_time. Write SQL Pipes for drop rates, roaming alerts, and capacity rollups, then publish them as HTTP API endpoints. For subscriber-scoped care queries, use JWTs with fixed parameters: values set in fixed_params cannot be overridden from the URL, which fits row-level scoping by subscriber_id.

Schema changes:

When mediation adds new fields, Tinybird's schema iteration supports safe migrations with zero downtime. Test changes on a Branch before deploying to production ingestion.

Customer references:

Canva reports 3.6 PB processed per month and 54 ms p99 query latency on Tinybird's managed ClickHouse product page. Resend reports 100 TB processed per month and 62 ms p90 query latency without relying on cache, per Tinybird's Resend customer story. Tinybird is SOC 2 Type II certified, per its security and compliance product information.

For streaming analytics architectures that must stay fresh under CDR spikes, Tinybird collapses ingest, storage, rollups, and API auth into one platform so network teams write SQL instead of operating merge backlogs.

5 operational mistakes on telecom ClickHouse

These are the schema and ingest errors that show up repeatedly when NOC, fraud, and billing teams share one cluster. Each breaks dashboards or KPIs in a predictable way; catch them in staging before peak traffic.

1. Sort key starts with subscriber_id

Region and cell dashboards scan the full table. Every NOC query reads billions of extra granules because the primary filter column (region) is not in the leading sort key positions.

Fix: Lead with region, then call_type, then result, then event_time. Reserve subscriber-first sort keys for dedicated care-portal tables, not the shared CDR fact.

2. No rollup tier for long-range charts

NOC teams open 90-day trend charts against raw CDR tables. Queries timeout or evict hot cache because they scan terabytes of five-minute-granularity facts.

Fix: Build hourly SummingMergeTree rollups via materialized views. Point dashboards at rollups for ranges beyond 7 days; keep raw tables for incident drill-down only.

3. Tiny single-row inserts from mediation

Mediation posts one CDR per HTTP request. ClickHouse creates a new part per insert. Merge threads fall behind during peak hours; ingest lag shows up as stale NOC dashboards.

Fix: Microbatch 10k–100k rows at mediation or use the Kafka engine pattern from Kafka to ClickHouse example. Target multi-thousand-row inserts minimum.

4. Ignoring duplicate CDRs after mediation replay

Mediation replays after a bug fix double-count revenue and inflate failure rates. Finance and NOC dashboards show diverging totals until someone reconciles against the mediation replay window.

Fix: ReplacingMergeTree(mediation_version) with ORDER BY (cdr_id, event_time). Serve dashboards from hourly rollups or argMax queries, not FINAL on raw facts.

5. One wide table for CDR, signaling, and probes

Schema churn breaks materialized views. Compression ratios degrade because voice duration columns sit next to RSRP floats. Query plans scan irrelevant columns.

Fix: Separate fact tables per record family. Join on cell_id and time bucket at query time or in Pipes when correlating drops with radio metrics.

Production validation checklist

Before pointing NOC dashboards at ClickHouse in production, verify:

1. Ingest lag — p95 time from mediation emit to queryable row < 60 seconds at peak CDR rate
2. Part count — system.parts active parts per partition stay below ops thresholds; no sustained insert rate creating >100 parts/hour
3. Parity — hourly rollup event counts match raw CDR counts within known mediation delay bounds
4. Sort key proof — explain plan for top 5 NOC queries shows granule skipping on region and event_time
5. Dedup — replay test: re-insert 1% of yesterday's CDRs; KPI dashboards unchanged when reading rollups

Frequently Asked Questions (FAQs)

How long should raw CDRs stay in ClickHouse?

Network ops typically keep raw facts 30–90 days and hourly rollups 12–24 months. Billing may archive aggregates to S3 for 7-year regulatory retention while dropping raw CDRs earlier. Split TTL by table; one global policy overspends on storage or underserves compliance.

Can one ClickHouse cluster serve billing and NOC teams?

Yes, with separate tables or databases and different sort keys. Billing queries often start from subscriber_id; NOC queries start from region and cell_id. Sharing one sort key for both guarantees one team scans the full cluster on every query.

Should mediation write directly to ClickHouse or through Kafka?

Kafka adds durability and replay when mediation replays or backfills. Direct HTTP insert works for probe metrics and smaller feeds. At hundreds of millions of CDRs per day, Kafka with the ClickHouse Kafka connector pattern is the default production path.

How do I handle mediation schema changes?

Add nullable columns with controlled ALTER, or park unknown fields in a JSON column until analytics models catch up. Test schema changes on a Tinybird Branch or staging cluster before peak windows. Never block production ingest on strict DDL that rejects new vendor fields.

Is ReplacingMergeTree enough for billing-grade deduplication?

For dashboards reading hourly rollups, yes, when combined with mediation_version. For real-time billing APIs that need immediate uniqueness, use argMax at query time or dedupe in mediation before insert. FINAL on billion-row tables is not a serving pattern.

When should telecom teams use Tinybird instead of self-hosted ClickHouse?

When you want managed ingestion, SQL APIs, and schema workflows documented on tinybird.co rather than operating ClickHouse clusters, Kafka consumers, and API layers yourself. Self-hosted ClickHouse remains the choice when you need full control over deployment topology and compliance boundaries Tinybird's hosted model does not cover.