Help Center/ Data Warehouse Service/ Developer Guide/ Developer Guide (9.1.1.x)/ DWS Materialized Views/ Use Cases of Materialized Views/ Financial Transaction Risk Control
Updated on 2026-07-02 GMT+08:00
View PDF
Share

Financial Transaction Risk Control

Scenario

A financial platform needs to collect transaction data in real time and calculate transaction risk control rules.

Business Requirements

Collect statistics on the transaction amount and number of transactions in real time, and detect abnormal transactions.

Solution

Use incremental refresh of materialized views to enable real-time computing.

Example

  1. Create a transaction log table. 

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE transaction_log (
transaction_id BIGINT,
user_id BIGINT,
merchant_id BIGINT,
amount DECIMAL(15,2),
transaction_type VARCHAR(20),
risk_level VARCHAR(10),
transaction_time TIMESTAMP
) WITH (orientation = COLUMN);

  2. Create a materialized view to store real-time risk control indicators. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    CREATE MATERIALIZED VIEW mv_risk_indicators
REFRESH FAST EVERY (interval '1 min')
ENABLE QUERY REWRITE
WITH (orientation = COLUMN)
AS
SELECT
user_id,
DATE_TRUNC('hour', transaction_time) as stat_hour,
COUNT(*) as transaction_count,
SUM(amount) as total_amount,
AVG(amount) as avg_amount,
MAX(amount) as max_amount,
COUNT(CASE WHEN risk_level = 'HIGH' THEN 1 END) as high_risk_count,
COUNT(CASE WHEN risk_level = 'MEDIUM' THEN 1 END) as medium_risk_count
FROM transaction_log
WHERE transaction_time >= now() - interval '24 hours'
GROUP BY user_id, DATE_TRUNC('hour', transaction_time);

  3. Create a materialized view to detect abnormal transactions in real time. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
    		 
    CREATE MATERIALIZED VIEW mv_anomaly_detection
REFRESH FAST EVERY (interval '5 min')
ENABLE QUERY REWRITE
AS
SELECT
ri.user_id,
ri.stat_hour,
ri.transaction_count,
ri.total_amount,
ri.avg_amount,
ri.high_risk_count,
CASE
WHEN ri.transaction_count > 100 THEN 'Abnormal: high-frequency transaction' -- Rule for detecting abnormal number of transactions
WHEN ri.total_amount > 100000 THEN 'Abnormal: large-amount transaction' -- Rule for detecting abnormal transaction amount
WHEN ri.high_risk_count > 5 THEN 'Abnormal: high-risk transaction' -- Rule for detecting abnormal transactions
ELSE 'Normal'
END as anomaly_type
FROM mv_risk_indicators ri
WHERE ri.stat_hour >= now() - interval '1 hour';

Parent Topic: Use Cases of Materialized Views