Build a call detail record system for a mobile carrier. Process millions of calls and texts, compute billing in real time, and analyze network usage patterns.

Note
 This tutorial assumes you have a running 3-node Ferrosa cluster. If you have not set one up yet, follow the cluster-setup guide first — it only takes about 5 minutes.

Create the Keyspace

Telecom data is high volume and high value. A single carrier can generate billions of call records per month. Let’s create a keyspace with triple replication so nothing gets lost.

CREATE KEYSPACE IF NOT EXISTS telecom
  WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
  };

With RF=3, your call detail records survive any single node failure. In production, you would use NetworkTopologyStrategy to spread replicas across data centers, but SimpleStrategy is perfect for learning.

Design the Schema

Telecom data has a natural time-series shape: every call, text, and data session has a subscriber, a timestamp, and a set of measurements. We will design four tables around the most common access patterns.

Call Records

This is the core table. We use a compound partition key of (subscriber_id, call_date) so that each day’s records for a subscriber land on the same partition. This keeps partitions from growing unbounded over months of data.

CREATE TABLE IF NOT EXISTS call_records (
    subscriber_id    text,
    call_date        date,
    call_time        timestamp,
    call_id          uuid,
    call_type        text,       -- voice, sms, data
    duration_seconds int,
    data_bytes       bigint,
    destination      text,
    cell_tower       text,
    cost             decimal,
    PRIMARY KEY ((subscriber_id, call_date), call_time, call_id)
) WITH CLUSTERING ORDER BY (call_time DESC, call_id ASC);

Subscribers

Basic subscriber information: plan details, usage limits, and current balance.

    subscriber_id        text PRIMARY KEY,
    name                 text,
    plan_type            text,
    monthly_limit_minutes int,
    monthly_limit_data_gb int,
    balance              decimal,
    activated_at         timestamp
);

Billing Summary

Pre-aggregated monthly totals. Rather than scanning millions of call records at bill time, we maintain running totals. The billing month is stored as text (like '2026-03') for easy range queries.

    subscriber_id       text,
    billing_month       text,
    total_voice_minutes int,
    total_sms           int,
    total_data_mb       bigint,
    total_cost          decimal,
    PRIMARY KEY (subscriber_id, billing_month)
) WITH CLUSTERING ORDER BY (billing_month DESC);

Network Events

Cell tower health, outages, and maintenance logs. Partitioned by tower and date so network operations teams can quickly pull up a tower’s history.

    cell_tower   text,
    event_date   date,
    event_time   timestamp,
    event_type   text,
    severity     text,
    description  text,
    PRIMARY KEY ((cell_tower, event_date), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

Four tables, each designed for a specific team: billing, customer service, network operations. That is how you model telecom data — start with the queries, work backward to the schema.

Add Subscribers

Let’s create three subscribers with different plan types. In a real carrier, you might have millions — but the access pattern is the same.

-- ── Subscribers ──

INSERT INTO subscribers (subscriber_id, name, plan_type, monthly_limit_minutes, monthly_limit_data_gb, balance, activated_at)
VALUES ('SUB-1001', 'Alice Nakamura', 'unlimited', -1, 50, 85.00, '2024-06-15 10:00:00');

INSERT INTO subscribers (subscriber_id, name, plan_type, monthly_limit_minutes, monthly_limit_data_gb, balance, activated_at)
VALUES ('SUB-1002', 'Ben Carlisle', 'prepaid', 500, 10, 22.50, '2025-01-20 14:30:00');

INSERT INTO subscribers (subscriber_id, name, plan_type, monthly_limit_minutes, monthly_limit_data_gb, balance, activated_at)

Alice has an unlimited plan (we use -1 for no minute cap). Ben is on prepaid with a smaller balance. Carmen has a family plan with shared limits. Each represents a different billing scenario.

Ingest Call Records

Now let’s simulate a day’s worth of activity. In production, these records would stream in from network switches at thousands per second. Here we will insert them one at a time, but the schema handles bulk writes just as well.

-- ── Call Records ──

-- Alice Nakamura: March 17, 2026
INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1001', '2026-03-17', '2026-03-17 08:12:00', uuid(), 'voice', 245, 0, '+1-555-0199', 'TWR-NYC-042', 0.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1001', '2026-03-17', '2026-03-17 09:45:00', uuid(), 'data', 0, 52428800, 'streaming', 'TWR-NYC-042', 0.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1001', '2026-03-17', '2026-03-17 12:30:00', uuid(), 'sms', 0, 0, '+1-555-0234', 'TWR-NYC-015', 0.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1001', '2026-03-17', '2026-03-17 14:05:00', uuid(), 'voice', 480, 0, '+1-555-0301', 'TWR-NYC-015', 0.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1001', '2026-03-17', '2026-03-17 18:20:00', uuid(), 'data', 0, 104857600, 'video-call', 'TWR-NYC-042', 0.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1001', '2026-03-17', '2026-03-17 21:10:00', uuid(), 'sms', 0, 0, '+1-555-0199', 'TWR-NYC-042', 0.00);

-- Ben Carlisle: March 17, 2026 (prepaid, costs apply)
INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1002', '2026-03-17', '2026-03-17 07:30:00', uuid(), 'voice', 120, 0, '+1-555-0455', 'TWR-CHI-008', 0.24);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1002', '2026-03-17', '2026-03-17 10:15:00', uuid(), 'sms', 0, 0, '+1-555-0488', 'TWR-CHI-008', 0.10);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1002', '2026-03-17', '2026-03-17 13:00:00', uuid(), 'data', 0, 20971520, 'browsing', 'TWR-CHI-012', 0.50);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1002', '2026-03-17', '2026-03-17 16:45:00', uuid(), 'voice', 900, 0, '+44-20-7946-0958', 'TWR-CHI-008', 4.50);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1002', '2026-03-17', '2026-03-17 19:30:00', uuid(), 'sms', 0, 0, '+1-555-0455', 'TWR-CHI-012', 0.10);

-- Carmen Delgado: March 17, 2026
INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1003', '2026-03-17', '2026-03-17 06:50:00', uuid(), 'voice', 360, 0, '+1-555-0612', 'TWR-MIA-003', 0.36);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1003', '2026-03-17', '2026-03-17 11:20:00', uuid(), 'data', 0, 209715200, 'app-download', 'TWR-MIA-003', 1.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1003', '2026-03-17', '2026-03-17 15:00:00', uuid(), 'voice', 1200, 0, '+52-55-1234-5678', 'TWR-MIA-007', 6.00);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1003', '2026-03-17', '2026-03-17 17:40:00', uuid(), 'sms', 0, 0, '+1-555-0612', 'TWR-MIA-003', 0.10);

INSERT INTO call_records (subscriber_id, call_date, call_time, call_id, call_type, duration_seconds, data_bytes, destination, cell_tower, cost)
VALUES ('SUB-1003', '2026-03-17', '2026-03-17 20:15:00', uuid(), 'data', 0, 524288000, 'streaming', 'TWR-MIA-003', 2.50);

That is 20 call records across three subscribers. Notice how Ben’s international call to London at 16:45 costs $4.50 — that is the kind of detail billing systems need to capture. Alice’s unlimited plan shows $0.00 for everything.

Generate Billing Summaries

In a real system, you would compute these with a batch job or stream processor. For this tutorial, we will insert the pre-computed summaries directly.

-- ── Billing Summaries ──

INSERT INTO billing_summary (subscriber_id, billing_month, total_voice_minutes, total_sms, total_data_mb, total_cost)
VALUES ('SUB-1001', '2026-03', 142, 38, 4200, 85.00);

INSERT INTO billing_summary (subscriber_id, billing_month, total_voice_minutes, total_sms, total_data_mb, total_cost)
VALUES ('SUB-1001', '2026-02', 128, 45, 3800, 85.00);

INSERT INTO billing_summary (subscriber_id, billing_month, total_voice_minutes, total_sms, total_data_mb, total_cost)
VALUES ('SUB-1002', '2026-03', 67, 22, 1500, 18.40);

INSERT INTO billing_summary (subscriber_id, billing_month, total_voice_minutes, total_sms, total_data_mb, total_cost)
VALUES ('SUB-1002', '2026-02', 45, 18, 980, 12.75);

INSERT INTO billing_summary (subscriber_id, billing_month, total_voice_minutes, total_sms, total_data_mb, total_cost)
VALUES ('SUB-1003', '2026-03', 210, 55, 8500, 110.00);

INSERT INTO billing_summary (subscriber_id, billing_month, total_voice_minutes, total_sms, total_data_mb, total_cost)
VALUES ('SUB-1003', '2026-02', 195, 62, 7200, 110.00);

Log Network Events

Cell towers generate their own stream of events: maintenance windows, capacity warnings, and outages. Let’s add a few realistic entries.

-- ── Network Events ──

INSERT INTO network_events (cell_tower, event_date, event_time, event_type, severity, description)
VALUES ('TWR-NYC-042', '2026-03-17', '2026-03-17 03:00:00', 'maintenance', 'info', 'Scheduled firmware update completed successfully');

INSERT INTO network_events (cell_tower, event_date, event_time, event_type, severity, description)
VALUES ('TWR-NYC-042', '2026-03-17', '2026-03-17 14:22:00', 'capacity', 'warning', 'Active connections at 85% capacity, consider load balancing');

INSERT INTO network_events (cell_tower, event_date, event_time, event_type, severity, description)
VALUES ('TWR-CHI-008', '2026-03-17', '2026-03-17 09:15:00', 'outage', 'critical', 'Power failure detected, switching to backup generator');

INSERT INTO network_events (cell_tower, event_date, event_time, event_type, severity, description)
VALUES ('TWR-CHI-008', '2026-03-17', '2026-03-17 09:18:00', 'recovery', 'info', 'Backup generator online, service restored in 3 minutes');

INSERT INTO network_events (cell_tower, event_date, event_time, event_type, severity, description)
VALUES ('TWR-MIA-003', '2026-03-17', '2026-03-17 20:00:00', 'capacity', 'warning', 'Streaming traffic surge, bandwidth utilization at 92%');

Notice the Chicago tower had a brief outage at 9:15 AM but recovered quickly. In a real NOC (Network Operations Center), these events would trigger alerts and populate dashboards.

Run Operational Queries

Let’s run the queries that different teams in a telecom company use every day.

Call History for a Subscriber

Customer service needs this constantly. Because we partitioned by (subscriber_id, call_date), pulling up a single day is a single-partition read — the fastest query Ferrosa can do.

-- Alice's calls on March 17
SELECT call_time, call_type, duration_seconds, destination, cost
FROM call_records
WHERE subscriber_id = 'SUB-1001'

Daily Usage Summary

How much data did a subscriber use today? This is the kind of query that powers "check my usage" features in mobile apps.

-- Ben's data usage on March 17
SELECT call_time, data_bytes, destination
FROM call_records
WHERE subscriber_id = 'SUB-1002'
  AND call_date = '2026-03-17'

Billing Summary

Pull up a subscriber’s bill history, most recent first.

ALLOW FILTERING;

-- Carmen's billing history

Network Events by Tower

When a tower has issues, the NOC team needs its event log immediately.

FROM billing_summary
WHERE subscriber_id = 'SUB-1003';

-- All events for Chicago tower on March 17

Subscriber Balance

A quick lookup for prepaid customers who want to check their remaining credit.

FROM network_events
WHERE cell_tower = 'TWR-CHI-008'
  AND event_date = '2026-03-17';

High-Cost Calls

Find expensive calls — useful for fraud detection or helping customers understand their bills.

SELECT name, plan_type, balance, monthly_limit_minutes, monthly_limit_data_gb
FROM subscribers
WHERE subscriber_id = 'SUB-1002';

-- Ben's calls that cost more than $1.00
SELECT call_time, call_type, destination, duration_seconds, cost

Subscriber Profile Update

When a prepaid customer tops up their account, you update their balance. This is a single-partition write — fast and atomic.

WHERE subscriber_id = 'SUB-1002'
  AND call_date = '2026-03-17'
  AND cost > 1.00

Voice Calls Only

Customer service often needs to pull up just voice calls for a dispute. Filter by call_type to narrow it down.

-- Ben tops up his prepaid balance by $20
UPDATE subscribers
SET balance = 42.50
WHERE subscriber_id = 'SUB-1002';

Carmen made two voice calls — a local one and a 20-minute international call to Mexico. That international call at $6.00 would be the one to look at if she calls in to dispute her bill.

Month-Over-Month Comparison

Pull two months of billing data for a subscriber to spot usage trends. This is useful for "your usage this month" dashboards.

SELECT call_time, destination, duration_seconds, cost
FROM call_records
WHERE subscriber_id = 'SUB-1003'
  AND call_date = '2026-03-17'
  AND call_type = 'voice'

Alice’s data usage went from 3,800 MB in February to 4,200 MB in March. If she is approaching her 50 GB cap, the app could trigger a proactive notification.

Tip
 Partition design tip: The compound partition key (subscriber_id, call_date) keeps each day’s records in a bounded partition. Without the date component, a subscriber with years of history would create a massive partition — exactly the kind of problem Ferrosa’s data model helps you avoid.

What You Learned

You just built the data layer for a mobile carrier. Here is a summary of everything you accomplished:

  • Designed a compound partition key to keep call record partitions bounded by day

  • Used descending clustering order so the most recent records always come back first

  • Built a pre-aggregated billing table to avoid expensive full-table scans at bill time

  • Modeled network events with tower-and-date partitioning for fast NOC lookups

  • Ran operational queries across billing, customer service, and network operations use cases

The data model you built here handles the three things telecom systems care about most: high write throughput for call records, fast lookups for customer service, and time-bounded queries for billing. Ferrosa’s partition-based architecture makes all three efficient at any scale.