Building a Telecom Fraud Detection System with SQL

Telecom fraud costs the industry over $38 billion annually. Most fraud schemes exploit the delay between fraud occurrence and detection — a window that batch-based systems extend to hours or days. RisingWave, a PostgreSQL-compatible streaming database, closes that window to seconds by evaluating fraud rules continuously against CDR and signaling event streams.

Why Streaming Fraud Detection Matters

The most damaging telecom fraud types are time-bounded: IRSF (International Revenue Share Fraud) and Wangiri calls generate fraudulent traffic in short bursts before the perpetrator disappears. SIM swap fraud enables account takeover within minutes of the swap. Batch detection systems that run every 15 minutes or every hour are simply too slow.

Real-time streaming fraud detection catches these patterns as they happen:

• IRSF: subscriber suddenly dials premium international numbers at high volume
• Wangiri: subscriber receives many short missed calls from international numbers and calls back
• SIM swap fraud: device change (new IMEI on existing IMSI) followed by immediate international calls or account changes
• Subscription fraud: newly activated SIM with immediate high-value usage — a sign of a fraudulent signup
• CLI spoofing: A-number does not match the network's registered IMSI-MSISDN mapping
• Roaming bypass fraud: domestic calls routed as roaming to avoid interconnect charges

How Streaming SQL Solves This

RisingWave ingests CDR events, SIM lifecycle events, and device change events from Kafka. Materialized views compute per-subscriber behavioral baselines and evaluate fraud rules against incoming events. A fraud signal is emitted to the fraud management system within seconds of the triggering event.

Because RisingWave is PostgreSQL-compatible, fraud analysts can query historical fraud patterns and tune rule thresholds directly in SQL without redeploying code.

Step-by-Step Tutorial

Step 1: Data Source

Ingest CDR events and SIM/device lifecycle events.

-- CDR stream (same schema as CDR processing article)
CREATE SOURCE cdr_fraud_input (
    cdr_id              VARCHAR,
    imsi                VARCHAR,
    imei                VARCHAR,
    a_number            VARCHAR,
    b_number            VARCHAR,
    call_start          TIMESTAMPTZ,
    duration_sec        INT,
    destination_country VARCHAR,
    cdr_type            VARCHAR,
    event_time          TIMESTAMPTZ
)
WITH (
    connector      = 'kafka',
    topic          = 'telecom.cdr.stream',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON
( TIMESTAMP COLUMN = event_time );

-- SIM and device lifecycle events (from HSS/HLR/UDM)
CREATE SOURCE sim_lifecycle_events (
    imsi            VARCHAR,
    sim_id          VARCHAR,    -- ICCID
    imei            VARCHAR,    -- device ID after change
    event_type      VARCHAR,    -- SIM_SWAP, DEVICE_CHANGE, ACTIVATION, DEACTIVATION
    msisdn          VARCHAR,    -- subscriber phone number
    event_time      TIMESTAMPTZ
)
WITH (
    connector      = 'kafka',
    topic          = 'telecom.sim.lifecycle',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON
( TIMESTAMP COLUMN = event_time );

Step 2: Core View

Compute per-subscriber behavioral profiles and detect fraud patterns using velocity checks, device change correlation, and subscription fraud indicators.

-- Call velocity per subscriber (5-minute windows)
CREATE MATERIALIZED VIEW cdr_velocity_5min AS
SELECT
    imsi,
    imei,
    window_start,
    window_end,
    COUNT(*)                                            AS call_count,
    COUNT(DISTINCT b_number)                           AS unique_destinations,
    SUM(duration_sec)                                  AS total_duration_sec,
    COUNT(*) FILTER (WHERE destination_country NOT IN ('HOME', 'US', 'CA', 'GB'))
                                                        AS intl_call_count,
    COUNT(*) FILTER (WHERE duration_sec < 5)           AS short_call_count
FROM TUMBLE(cdr_fraud_input, event_time, INTERVAL '5 MINUTES')
WHERE cdr_type = 'VOICE'
GROUP BY imsi, imei, window_start, window_end;

-- Recent SIM swap events (last 60 minutes)
CREATE MATERIALIZED VIEW recent_sim_swaps AS
SELECT
    imsi,
    sim_id,
    imei             AS new_imei,
    event_time       AS swap_time
FROM sim_lifecycle_events
WHERE event_type = 'SIM_SWAP'
  AND event_time >= NOW() - INTERVAL '60 MINUTES';

-- Subscription age (new activations last 24 hours)
CREATE MATERIALIZED VIEW new_activations AS
SELECT
    imsi,
    sim_id,
    msisdn,
    event_time AS activation_time
FROM sim_lifecycle_events
WHERE event_type = 'ACTIVATION'
  AND event_time >= NOW() - INTERVAL '24 HOURS';

-- Wangiri detection: many short incoming calls from international numbers
CREATE MATERIALIZED VIEW wangiri_callbacks AS
SELECT
    imsi,
    window_start,
    window_end,
    COUNT(*) FILTER (
        WHERE duration_sec BETWEEN 1 AND 30
        AND destination_country NOT IN ('HOME')
    ) AS intl_callback_count
FROM TUMBLE(cdr_fraud_input, event_time, INTERVAL '15 MINUTES')
WHERE cdr_type = 'VOICE'
GROUP BY imsi, window_start, window_end;

Step 3: Alerts and Sinks

Combine signals into a unified fraud alert stream.

-- IRSF / velocity fraud alerts
CREATE MATERIALIZED VIEW velocity_fraud_alerts AS
SELECT
    v.imsi,
    v.imei,
    v.window_end        AS alert_time,
    v.call_count,
    v.unique_destinations,
    v.intl_call_count,
    v.total_duration_sec,
    s.swap_time,
    n.activation_time,
    CASE
        WHEN s.swap_time IS NOT NULL AND v.intl_call_count > 5 THEN 'SIM_SWAP_FRAUD'
        WHEN n.activation_time IS NOT NULL AND v.call_count > 20 THEN 'SUBSCRIPTION_FRAUD'
        WHEN v.intl_call_count > 15                             THEN 'IRSF_SUSPECTED'
        WHEN v.call_count > 50                                  THEN 'CALL_STORM'
        ELSE 'VELOCITY_ANOMALY'
    END AS fraud_type,
    CASE
        WHEN s.swap_time IS NOT NULL AND v.intl_call_count > 5 THEN 'CRITICAL'
        WHEN v.intl_call_count > 15 OR v.call_count > 50       THEN 'CRITICAL'
        ELSE 'WARNING'
    END AS severity
FROM cdr_velocity_5min v
LEFT JOIN recent_sim_swaps s
    ON  v.imsi         = s.imsi
    AND v.window_start >= s.swap_time
LEFT JOIN new_activations n
    ON  v.imsi         = n.imsi
WHERE v.call_count > 20
   OR v.intl_call_count > 5
   OR (s.swap_time IS NOT NULL AND v.call_count > 3);

-- Wangiri fraud alerts
CREATE MATERIALIZED VIEW wangiri_alerts AS
SELECT
    imsi,
    window_end AS alert_time,
    intl_callback_count,
    'WANGIRI' AS fraud_type,
    CASE WHEN intl_callback_count > 10 THEN 'CRITICAL' ELSE 'WARNING' END AS severity
FROM wangiri_callbacks
WHERE intl_callback_count > 5;

CREATE SINK fraud_alerts_sink
FROM velocity_fraud_alerts
WITH (
    connector  = 'kafka',
    topic      = 'telecom.fraud.alerts',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

CREATE SINK wangiri_alerts_sink
FROM wangiri_alerts
WITH (
    connector  = 'kafka',
    topic      = 'telecom.fraud.alerts',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

Q: How do I add new fraud patterns without service disruption? Create a new materialized view with the new detection logic. RisingWave builds the new view while the existing ones keep running. Point the fraud management system at the new view when you're satisfied with its behavior, then drop the old one.

Q: Can I whitelist known high-volume business subscribers to reduce false positives? Yes. Create a subscriber_whitelist table with IMSI entries and their expected call volume. In the alert view, LEFT JOIN to the whitelist and add AND w.imsi IS NULL to the WHERE clause to exclude whitelisted subscribers.

Q: How do I correlate fraud alerts with account takeover attempts in the authentication system? Create a source for authentication events (failed OTP, password reset) and join it with the SIM swap events in a temporal window. If a SIM swap is followed by a failed OTP within 10 minutes, escalate to CRITICAL fraud priority.

Key Takeaways

• RisingWave evaluates IRSF, SIM swap, subscription fraud, and Wangiri patterns in real time using standard SQL — detection latency drops from hours to seconds.
• Joining CDR velocity data with SIM lifecycle events in streaming views enables correlation-based fraud detection that single-stream rules engines miss.
• Fraud rule updates are SQL DDL operations — no code deployments or service restarts required.
• The PostgreSQL interface lets fraud analysts query historical alert data and tune thresholds interactively without involving engineering teams.

Get started | Slack