Real-Time Credit Scoring with Streaming SQL

Real-time credit scoring with streaming SQL means maintaining continuously updated behavioral signals — payment velocity, utilization trends, overdraft patterns — as transactions occur, so lending systems can make instant credit decisions using current data rather than a month-old bureau snapshot.

Why Static Credit Scores Miss the Mark

Traditional credit scoring relies on monthly credit bureau data refreshes and periodic model runs. A FICO score computed on the first of the month reflects account behavior through the prior statement cycle. By the time a loan application arrives mid-month, the score may be three to six weeks stale.

This staleness has real consequences:

• A borrower who just paid down 80% of revolving debt may still appear highly utilized
• An applicant who missed three payments in the last two weeks shows as current
• Early signs of financial stress — increased cash advance frequency, overdraft activity — are invisible until the next batch

Streaming credit scoring solves this by continuously computing behavioral signals from live transaction and account data, providing lenders with a dynamic view of creditworthiness between bureau pulls.

The Streaming Credit Signal Architecture

RisingWave maintains a set of behavioral credit signals as continuously updated materialized views:

• Utilization velocity: How fast is the borrower using available credit?
• Payment behavior: Are they making minimum payments, paying in full, or falling behind?
• Cash advance frequency: Increasing cash advance usage often precedes default
• Overdraft patterns: Frequency and severity of overdraft events
• Transaction volatility: Irregular income/spending patterns

These signals augment static bureau data in real-time decisioning models.

Setting Up Behavioral Data Sources

-- Transaction stream from connected accounts (open banking / bank feeds)
CREATE SOURCE account_transactions (
    transaction_id   VARCHAR,
    customer_id      VARCHAR,
    account_id       VARCHAR,
    account_type     VARCHAR,  -- 'checking', 'savings', 'credit', 'loan'
    txn_category     VARCHAR,  -- 'purchase', 'payment', 'cash_advance', 'fee', 'transfer'
    amount           NUMERIC,
    running_balance  NUMERIC,
    credit_limit     NUMERIC,
    txn_time         TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'open.banking.transactions',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Bureau snapshot reference (refreshed monthly)
CREATE TABLE bureau_snapshots (
    customer_id        VARCHAR PRIMARY KEY,
    bureau_score       INTEGER,
    derogatory_marks   INTEGER,
    total_accounts     INTEGER,
    total_debt         NUMERIC,
    snapshot_date      DATE
);

Continuous Behavioral Signal Computation

Compute rolling credit behavioral signals using hopping windows:

CREATE MATERIALIZED VIEW credit_behavioral_signals AS
SELECT
    customer_id,
    -- Utilization metrics
    AVG(running_balance / NULLIF(credit_limit, 0)) FILTER (
        WHERE account_type = 'credit'
    )                                                          AS avg_utilization_30d,
    MAX(running_balance / NULLIF(credit_limit, 0)) FILTER (
        WHERE account_type = 'credit'
    )                                                          AS peak_utilization_30d,

    -- Payment behavior
    COUNT(*) FILTER (
        WHERE txn_category = 'payment'
        AND account_type = 'credit'
    )                                                          AS credit_payments_30d,
    SUM(amount) FILTER (
        WHERE txn_category = 'payment'
        AND account_type = 'credit'
    )                                                          AS total_credit_payments_30d,

    -- Stress indicators
    COUNT(*) FILTER (
        WHERE txn_category = 'cash_advance'
    )                                                          AS cash_advances_30d,
    COUNT(*) FILTER (
        WHERE running_balance < 0
        AND account_type = 'checking'
    )                                                          AS overdraft_events_30d,
    MIN(running_balance) FILTER (
        WHERE account_type = 'checking'
    )                                                          AS min_checking_balance_30d,

    -- Income/spending stability
    STDDEV(amount) FILTER (
        WHERE txn_category = 'purchase'
    )                                                          AS purchase_volatility,

    window_start,
    window_end
FROM HOP(
    account_transactions,
    txn_time,
    INTERVAL '1 DAY',
    INTERVAL '30 DAYS'
)
GROUP BY
    customer_id,
    window_start,
    window_end;

Combining Behavioral Signals with Bureau Data

Join live behavioral signals with static bureau data for a composite credit view:

CREATE MATERIALIZED VIEW composite_credit_profile AS
SELECT
    s.customer_id,
    b.bureau_score,
    b.derogatory_marks,
    s.avg_utilization_30d,
    s.peak_utilization_30d,
    s.cash_advances_30d,
    s.overdraft_events_30d,
    s.credit_payments_30d,
    s.min_checking_balance_30d,
    -- Synthetic risk adjustment signal
    CASE
        WHEN s.cash_advances_30d >= 3
             OR s.overdraft_events_30d >= 5
             OR s.peak_utilization_30d > 0.95
        THEN 'ELEVATED'
        WHEN s.avg_utilization_30d > 0.70
             OR s.overdraft_events_30d >= 2
        THEN 'MODERATE'
        ELSE 'NORMAL'
    END                              AS behavioral_risk_flag,
    b.snapshot_date                  AS bureau_as_of,
    s.window_end                     AS signals_as_of
FROM credit_behavioral_signals s
JOIN bureau_snapshots FOR SYSTEM_TIME AS OF s.window_start AS b
    ON s.customer_id = b.customer_id;

Batch vs. Streaming Credit Scoring

FAQ

Q: Does real-time behavioral scoring replace traditional credit bureau scores? A: No — it augments them. Bureau scores (FICO, VantageScore) remain the primary underwriting signal for regulatory and model governance reasons. Behavioral signals from streaming SQL provide a real-time overlay that helps adjust decisions between bureau refreshes and catch emerging risk that static scores miss.

Q: How do we comply with Fair Credit Reporting Act (FCRA) requirements with streaming signals? A: FCRA governs data obtained from consumer reporting agencies. First-party behavioral signals derived from the applicant's own account activity with your institution are not subject to FCRA in the same way. However, legal review is essential — compliance requirements vary by signal type and use case.

Q: Can we use RisingWave to feed a real-time ML model for credit scoring? A: Yes. Materialized views in RisingWave can be queried via the PostgreSQL interface by ML serving infrastructure. Many teams use RisingWave to pre-compute features that a model server fetches in real time during decisioning.

Q: How do we handle customers with limited transaction history? A: The behavioral signal views use LEFT JOINs and NULLIF to handle cases where signals are null or zero. The composite profile flags customers with insufficient behavioral history, allowing the decisioning system to fall back to bureau-only scoring.

Q: What is the typical latency between a transaction occurring and the behavioral signals updating? A: Behavioral signals update within seconds of a transaction being committed to the Kafka source. The materialized view incremental computation adds sub-second latency on top of Kafka ingestion time.

Get Started

Build real-time credit intelligence with streaming SQL:

• Start the quickstart: docs.risingwave.com/get-started
• Talk to lending and fintech builders: risingwave.com/slack