Real-Time Payment Processing Analytics with Streaming SQL

Real-time payment processing analytics means computing transaction success rates, throughput, settlement status, and exception counts as payments flow through the system — not after batch reconciliation. With streaming SQL, payment operations teams gain a live view of the entire payment lifecycle, enabling faster incident response and SLA compliance.

Why Payment Analytics Can't Wait for Batch

Modern payment networks process millions of transactions per hour. ISO 20022, RTP (Real-Time Payments), FedNow, SWIFT, and card network transactions all demand sub-second processing. But analytics on those payments have historically lagged hours behind, relying on batch ETL pipelines that aggregate data overnight.

The consequence: when a payment processor experiences elevated failure rates or a settlement delay, operations teams often learn about it from customer complaints rather than internal monitoring. A streaming analytics layer eliminates this blind spot.

Payment analytics dashboards powered by streaming SQL can show, in real time:

• Transactions per second by payment rail
• Success/failure rates by merchant, region, or bank
• Settlement queue depth and aging
• Chargeback and dispute volumes

System Architecture

A streaming payment analytics system with RisingWave connects to:

• Payment event streams on Kafka (authorization, clearing, settlement events)
• Reference data (merchants, banks, payment rails) in RisingWave tables
• Downstream consumers (dashboards, alerting, reconciliation systems)

Ingesting Payment Events

-- Payment lifecycle events from Kafka
CREATE SOURCE payment_events (
    event_id        VARCHAR,
    payment_id      VARCHAR,
    merchant_id     VARCHAR,
    acquirer_id     VARCHAR,
    issuer_id       VARCHAR,
    payment_rail    VARCHAR,  -- 'visa', 'mastercard', 'ach', 'rtp', 'wire'
    event_type      VARCHAR,  -- 'authorized', 'cleared', 'settled', 'declined', 'reversed'
    amount          NUMERIC,
    currency        VARCHAR,
    region          VARCHAR,
    error_code      VARCHAR,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'payments.events',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Merchant reference data
CREATE TABLE merchant_reference (
    merchant_id    VARCHAR PRIMARY KEY,
    merchant_name  VARCHAR,
    category_code  VARCHAR,
    tier           VARCHAR,  -- 'enterprise', 'smb', 'micro'
    country        VARCHAR
);

Real-Time Transaction Throughput and Success Rate

Compute rolling success rates per payment rail using hopping windows:

CREATE MATERIALIZED VIEW payment_throughput_metrics AS
SELECT
    payment_rail,
    region,
    COUNT(*)                                          AS total_txns,
    COUNT(*) FILTER (WHERE event_type = 'authorized') AS authorized_count,
    COUNT(*) FILTER (WHERE event_type = 'declined')   AS declined_count,
    COUNT(*) FILTER (WHERE event_type = 'reversed')   AS reversed_count,
    SUM(amount) FILTER (WHERE event_type = 'settled') AS settled_volume,
    ROUND(
        COUNT(*) FILTER (WHERE event_type = 'authorized') * 100.0
        / NULLIF(COUNT(*), 0),
        2
    )                                                 AS auth_success_rate_pct,
    window_start,
    window_end
FROM HOP(
    payment_events,
    event_time,
    INTERVAL '1 MINUTE',
    INTERVAL '5 MINUTES'
)
GROUP BY
    payment_rail,
    region,
    window_start,
    window_end;

This hopping window gives a 5-minute rolling view, updated every minute — the right granularity for operations dashboards that need trend visibility without noise.

Settlement Aging and Exception Detection

Track payments stuck in processing using session windows to detect stalled settlements:

CREATE MATERIALIZED VIEW settlement_exceptions AS
SELECT
    p.payment_id,
    p.merchant_id,
    m.merchant_name,
    p.payment_rail,
    p.amount,
    MIN(p.event_time) FILTER (WHERE p.event_type = 'cleared') AS cleared_at,
    MAX(p.event_time) FILTER (WHERE p.event_type = 'settled') AS settled_at,
    EXTRACT(
        EPOCH FROM (
            MAX(p.event_time) FILTER (WHERE p.event_type = 'settled')
            - MIN(p.event_time) FILTER (WHERE p.event_type = 'cleared')
        )
    ) / 3600                                                   AS settlement_hours,
    CASE
        WHEN MAX(p.event_time) FILTER (WHERE p.event_type = 'settled') IS NULL
             AND MIN(p.event_time) FILTER (WHERE p.event_type = 'cleared') < NOW() - INTERVAL '24 HOURS'
        THEN 'STUCK_IN_CLEARING'
        WHEN EXTRACT(EPOCH FROM (
            MAX(p.event_time) FILTER (WHERE p.event_type = 'settled')
            - MIN(p.event_time) FILTER (WHERE p.event_type = 'cleared')
        )) / 3600 > 48
        THEN 'LATE_SETTLEMENT'
        ELSE 'NORMAL'
    END                                                        AS exception_type
FROM payment_events p
LEFT JOIN merchant_reference m ON p.merchant_id = m.merchant_id
GROUP BY
    p.payment_id,
    p.merchant_id,
    m.merchant_name,
    p.payment_rail,
    p.amount
HAVING
    CASE
        WHEN MAX(p.event_time) FILTER (WHERE p.event_type = 'settled') IS NULL
             AND MIN(p.event_time) FILTER (WHERE p.event_type = 'cleared') < NOW() - INTERVAL '24 HOURS'
        THEN true
        WHEN EXTRACT(EPOCH FROM (
            MAX(p.event_time) FILTER (WHERE p.event_type = 'settled')
            - MIN(p.event_time) FILTER (WHERE p.event_type = 'cleared')
        )) / 3600 > 48
        THEN true
        ELSE false
    END;

Sending Alerts to Operations

CREATE SINK payment_ops_alerts_sink
FROM payment_throughput_metrics
WITH (
    connector = 'kafka',
    topic = 'payment.ops.alerts',
    properties.bootstrap.server = 'broker:9092'
)
FORMAT PLAIN ENCODE JSON;

Batch vs. Streaming Payment Analytics

FAQ

Q: How does RisingWave handle exactly-once semantics for payment events? A: RisingWave uses Kafka consumer group offsets and checkpoint-based state management. Combined with idempotent Kafka producers on the upstream side, this ensures exactly-once processing semantics for event ingestion.

Q: Can we compute interchange and fee analytics in the same system? A: Yes. Fee rates can be stored in a reference table and joined with payment events in a materialized view to compute interchange revenue, network fees, and margin in real time.

Q: How do we handle currency conversion for multi-currency analytics? A: Maintain a live exchange rate table updated from a Kafka source carrying FX rate events. Join payment events against this table using temporal joins to normalize amounts to a reporting currency.

Q: What is the query latency for fraud analysts querying materialized views? A: Materialized views in RisingWave behave like standard PostgreSQL tables for query purposes. Read latency is typically sub-10ms for simple lookups and sub-100ms for aggregation queries over millions of rows.

Q: Can we integrate RisingWave with our existing BI tools like Tableau or Looker? A: Yes. RisingWave exposes a PostgreSQL wire protocol interface. Any tool with a PostgreSQL connector — Tableau, Looker, Metabase, Grafana — can query RisingWave materialized views directly without additional adapters.

Get Started

Transform your payment operations with live streaming analytics:

• Launch your first streaming pipeline: docs.risingwave.com/get-started
• Join the payments and fintech community: risingwave.com/slack