Fraud Detection

Build a real-time fraud scoring system. Ingest financial transactions, detect suspicious patterns, and flag anomalies — all with sub-millisecond lookups.

Note	This tutorial assumes you have a running Ferrosa cluster. If you have not set one up yet, follow the Getting Started guide first. You should be able to connect with `cqlsh` before continuing.

Create the keyspace

Fraud detection systems need strong durability guarantees — you cannot afford to lose a transaction record. A replication factor of 3 means every transaction is stored on three separate nodes, so even if a node fails, your data is safe.

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

USE fraud;

Design the schema

A fraud detection system revolves around three things: transactions (the raw financial events), account profiles (behavioral baselines for each customer), and alerts (flagged anomalies that need investigation). We will create a table for each.

Transactions table

This is your primary data table. Every card swipe, wire transfer, and online purchase lands here. Partitioning by account_id keeps all of a customer’s transactions together, and clustering by transaction_time DESC puts the newest ones first.

CREATE TABLE IF NOT EXISTS transactions (
    account_id        text,
    transaction_time  timestamp,
    transaction_id    uuid,
    amount            decimal,
    currency          text,
    merchant          text,
    merchant_category text,
    location          text,
    country           text,
    status            text,
    risk_score        int,
    PRIMARY KEY (account_id, transaction_time, transaction_id)

Note	Why include transaction_id in the key? A customer could have two transactions at the exact same timestamp (think: a split-second double-tap on a payment terminal). Adding `transaction_id` as a tiebreaker guarantees uniqueness in the clustering order.

Account profile table

This stores each customer’s risk baseline: their typical spending patterns, home country, and whether they have been flagged. Your fraud scoring engine reads this to decide if a transaction looks normal.

CREATE TABLE IF NOT EXISTS account_profile (
    account_id           text PRIMARY KEY,
    customer_name        text,
    home_country         text,
    avg_transaction      decimal,
    max_transaction      decimal,
    last_transaction_time timestamp,
    flagged              boolean,
    risk_level           text

Alerts table

When a transaction trips a fraud rule, an alert is created. We partition alerts by alert_date so you can efficiently pull all alerts for a given day — the most common query for fraud analysts working through their queue.

CREATE TABLE IF NOT EXISTS alerts (
    alert_date     date,
    alert_time     timestamp,
    alert_id       uuid,
    account_id     text,
    transaction_id uuid,
    rule_triggered text,
    severity       text,
    details        text,
    resolved       boolean,
    PRIMARY KEY (alert_date, alert_time, alert_id)

Here is a quick reference for how these tables work together:

Table Partition Key Clustering Key Serves

Table	Partition Key	Clustering Key	Serves
transactions	`account_id`	`transaction_time DESC`	Per-account transaction history
account_profile	`account_id`	—	Behavioral baseline lookup
alerts	`alert_date`	`alert_time DESC`	Daily analyst queue

transactions

account_id

transaction_time DESC

Per-account transaction history

account_profile

account_id

—

Behavioral baseline lookup

alerts

alert_date

alert_time DESC

Daily analyst queue

Notice the pattern: each table is optimized for a specific workflow. The transactions table powers the investigation view, account_profile provides context, and alerts drives the analyst’s daily queue.

Your schema is in place. Let’s load it with realistic transaction data.

Populate with sample data

Create account profiles

We will set up three customers with different spending patterns. Pay attention to the avg_transaction and max_transaction values — they establish what "normal" looks like for each account.

-- ── Account Profiles ──

-- Regular consumer, moderate spending
INSERT INTO account_profile
    (account_id, customer_name, home_country, avg_transaction, max_transaction,
     last_transaction_time, flagged, risk_level)
VALUES ('ACCT-1001', 'Sarah Chen', 'US', 85.50, 450.00,
    '2026-03-17 18:30:00+0000', false, 'low');

-- Business traveler, higher spending, multiple countries
INSERT INTO account_profile
    (account_id, customer_name, home_country, avg_transaction, max_transaction,
     last_transaction_time, flagged, risk_level)
VALUES ('ACCT-2002', 'Marcus Johnson', 'US', 220.00, 3500.00,
    '2026-03-17 20:15:00+0000', false, 'medium');

-- New account, limited history
INSERT INTO account_profile
    (account_id, customer_name, home_country, avg_transaction, max_transaction,
     last_transaction_time, flagged, risk_level)

Insert normal transactions

First, some everyday transactions that look completely normal for each account:

    '2026-03-17 14:00:00+0000', true, 'high');

-- ── Normal Transactions ──

-- Sarah's normal spending
INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-1001', '2026-03-17 08:15:00+0000', uuid(), 4.75, 'USD', 'Blue Bottle Coffee', 'food_beverage', 'San Francisco, CA', 'US', 'approved', 2);

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-1001', '2026-03-17 12:30:00+0000', uuid(), 32.50, 'USD', 'Whole Foods Market', 'grocery', 'San Francisco, CA', 'US', 'approved', 1);

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-1001', '2026-03-17 14:00:00+0000', uuid(), 67.99, 'USD', 'Target', 'retail', 'San Francisco, CA', 'US', 'approved', 3);

-- Marcus's business travel
INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-2002', '2026-03-16 09:00:00+0000', uuid(), 189.00, 'USD', 'Delta Airlines', 'travel', 'Atlanta, GA', 'US', 'approved', 5);

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-2002', '2026-03-16 19:30:00+0000', uuid(), 245.00, 'USD', 'Marriott Downtown', 'lodging', 'Chicago, IL', 'US', 'approved', 4);

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-2002', '2026-03-17 07:45:00+0000', uuid(), 28.00, 'USD', 'Starbucks', 'food_beverage', 'Chicago, IL', 'US', 'approved', 1);

-- Priya's normal UK spending

Insert suspicious transactions

Now the interesting part. Let’s add some transactions that should trigger fraud rules — large amounts, unusual countries, and rapid-fire purchases:

VALUES ('ACCT-3003', '2026-03-16 10:00:00+0000', uuid(), 12.50, 'GBP', 'Tesco Express', 'grocery', 'London', 'GB', 'approved', 1);

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-3003', '2026-03-16 15:20:00+0000', uuid(), 35.00, 'GBP', 'Boots Pharmacy', 'pharmacy', 'London', 'GB', 'approved', 2);

-- ── Suspicious Transactions ──

-- Sarah: sudden large purchase, way above her average
INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-1001', '2026-03-17 18:30:00+0000', d1a7e100-e39b-11ef-8001-000000000010, 2899.99, 'USD', 'ElectroMart Online', 'electronics', 'San Francisco, CA', 'US', 'pending_review', 85);

-- Sarah: another large purchase 2 minutes later
INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-1001', '2026-03-17 18:32:00+0000', d1a7e100-e39b-11ef-8001-000000000011, 1549.00, 'USD', 'LuxWatch.com', 'luxury', 'San Francisco, CA', 'US', 'declined', 92);

-- Marcus: transaction in a country he has never visited
INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-2002', '2026-03-17 20:15:00+0000', d1a7e100-e39b-11ef-8001-000000000012, 3200.00, 'EUR', 'Premium Electronics GmbH', 'electronics', 'Berlin', 'DE', 'pending_review', 78);

-- Priya: card used in two countries within hours (impossible travel)
INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-3003', '2026-03-17 11:00:00+0000', d1a7e100-e39b-11ef-8001-000000000013, 850.00, 'USD', 'Best Buy', 'electronics', 'New York, NY', 'US', 'declined', 95);

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)

Create alerts

These alerts are what a fraud analyst sees in their dashboard. Each one links back to the transaction that triggered it:

INSERT INTO transactions (account_id, transaction_time, transaction_id, amount, currency, merchant, merchant_category, location, country, status, risk_score)
VALUES ('ACCT-3003', '2026-03-17 14:00:00+0000', d1a7e100-e39b-11ef-8001-000000000015, 199.99, 'GBP', 'John Lewis', 'retail', 'London', 'GB', 'approved', 15);

-- ── Alerts ──

INSERT INTO alerts (alert_date, alert_time, alert_id, account_id, transaction_id, rule_triggered, severity, details, resolved)
VALUES ('2026-03-17', '2026-03-17 18:30:05+0000', uuid(),
    'ACCT-1001', d1a7e100-e39b-11ef-8001-000000000010,
    'amount_exceeds_3x_average', 'high',
    'Transaction of $2899.99 is 33x above account average of $85.50', false);

INSERT INTO alerts (alert_date, alert_time, alert_id, account_id, transaction_id, rule_triggered, severity, details, resolved)
VALUES ('2026-03-17', '2026-03-17 18:32:05+0000', uuid(),
    'ACCT-1001', d1a7e100-e39b-11ef-8001-000000000011,
    'rapid_succession', 'critical',
    'Two high-value transactions within 2 minutes. Combined: $4448.99', false);

INSERT INTO alerts (alert_date, alert_time, alert_id, account_id, transaction_id, rule_triggered, severity, details, resolved)
VALUES ('2026-03-17', '2026-03-17 20:15:05+0000', uuid(),
    'ACCT-2002', d1a7e100-e39b-11ef-8001-000000000012,
    'unusual_country', 'medium',
    'Transaction in DE (Germany), account has no history in this country', false);

INSERT INTO alerts (alert_date, alert_time, alert_id, account_id, transaction_id, rule_triggered, severity, details, resolved)
VALUES ('2026-03-17', '2026-03-17 12:30:05+0000', uuid(),
    'ACCT-3003', d1a7e100-e39b-11ef-8001-000000000013,
    'impossible_travel', 'critical',
    'Card used in US (New York) 1 hour after UK (London) purchase. Travel time: ~8 hours minimum.', false);

Query your data

View recent transactions for an account

The first thing a fraud analyst does when reviewing an alert is pull up the customer’s recent activity. This is a single-partition read — fast and efficient:

-- View recent transactions for an account
SELECT transaction_time, amount, currency, merchant, country, risk_score, status
FROM transactions
WHERE account_id = 'ACCT-1001'

You will see Sarah’s transactions in reverse chronological order, with the two suspicious electronics purchases at the top.

Find high-risk transactions

Pull all transactions for a given account that have an elevated risk score. This helps the analyst focus on what matters:

-- Find transactions for an account in a specific date range
SELECT transaction_time, amount, merchant, country, risk_score
FROM transactions
WHERE account_id = 'ACCT-1001'

This gives you all of Sarah’s transactions from today. In your application code, you would filter for risk_score > 70 after fetching the results.

Review today’s alerts

Pull up the fraud analyst’s daily queue — every alert generated today, newest first:

  AND transaction_time <= '2026-03-17 23:59:59+0000';

-- Review today's alerts
SELECT alert_time, account_id, rule_triggered, severity, details, resolved

Because we partitioned by alert_date, this reads a single partition. Even with millions of historical alerts, today’s queue loads instantly.

Find unresolved alerts only

Analysts want to see what still needs attention. Pull today’s alerts and filter for resolved = false:

WHERE alert_date = '2026-03-17';

-- Find unresolved alerts only
SELECT alert_time, account_id, rule_triggered, severity, details
FROM alerts

Important

About ALLOW FILTERING: In production, you would typically handle this filter in your application layer or create a separate table for unresolved alerts. We use ALLOW FILTERING here because the partition is small (one day of alerts). For large partitions, always prefer a dedicated table.

Check an account’s risk profile

When investigating an alert, you need the customer’s baseline. This is a single-row lookup by primary key:

  AND resolved = false
ALLOW FILTERING;

-- Check an account's risk profile

You will see that Priya’s average transaction is only 45 GBP, her home country is GB, and she is already flagged. The 850 USD transaction from New York is wildly out of pattern.

Detect unusual country patterns

To find all of Priya’s transactions from outside her home country, query her partition and filter by country in your application:

FROM account_profile
WHERE account_id = 'ACCT-3003';

-- Detect unusual country patterns

Looking at the results, you can see the pattern: legitimate UK purchases followed by a burst of US transactions — classic card-not-present fraud from a stolen card number.

Review transactions in a specific time window

To zoom in on the suspicious activity window for Sarah’s account, use a time-range filter on the clustering key:

FROM transactions
WHERE account_id = 'ACCT-3003';

-- Review transactions in a specific time window
SELECT transaction_time, amount, merchant, status, risk_score

This narrows the results to just that one-hour window where the suspicious activity occurred. Both high-value transactions will appear, making the rapid-succession pattern obvious.

Compare a transaction against the account baseline

A core fraud detection operation is comparing a transaction amount against the customer’s historical average. First, fetch the profile:

WHERE account_id = 'ACCT-1001'
  AND transaction_time >= '2026-03-17 18:00:00+0000'
  AND transaction_time <= '2026-03-17 19:00:00+0000';

Sarah’s average is $85.50 and her previous maximum was $450. That $2,899.99 electronics purchase is over 33 times her average and 6 times her previous max. In your application code, you would compute this ratio and assign the risk score accordingly.

Resolve an alert

After investigating, the analyst confirms the unusual-country alert for Marcus was actually him on a business trip. Mark it as resolved:

Tip	In practice, you would capture the `alert_id` from your earlier query results. CQL requires all primary key columns in an `UPDATE` — this ensures you are modifying exactly the right row.

Update an account’s risk level

After investigating, the analyst decides to flag Sarah’s account while the investigation is open:

SELECT avg_transaction, max_transaction
FROM account_profile
WHERE account_id = 'ACCT-1001';

Verify the update:

-- Update an account's risk level
UPDATE account_profile
SET flagged = true, risk_level = 'high'
WHERE account_id = 'ACCT-1001';

What you learned

You built the data layer for a real-time fraud detection system. Here is what you practiced:

Time-series partitioning for transactions. By partitioning on account_id and clustering on transaction_time DESC, you can pull any customer’s recent activity in a single read. This pattern works at any scale — whether a customer has 10 transactions or 10 million.
Date-bucketed alert queues. Partitioning alerts by alert_date keeps daily workloads bounded. An analyst’s queue never touches historical data unless they explicitly query a different date.
Composite clustering keys for uniqueness. Adding transaction_id after transaction_time prevents collisions when two transactions happen at the same instant. This is a common pattern in high-throughput systems.
Risk scoring at write time. By computing the risk_score when the transaction is written, you avoid expensive recalculation on reads. The score is just another column — no joins, no subqueries.
Profile lookups for context. The account_profile table gives O(1) access to behavioral baselines. Your fraud engine compares each transaction against these baselines to decide if it looks suspicious.

From here, you could add a merchant_risk table to score merchants independently, a device_fingerprints table to track which devices are associated with each account, or a blocked_cards table for instant card-level blocks.