ATM Fraud detection with Kafka and KSQL

Slide 1

Slide 1 text

ATM Fraud Detection @rmoff with Apache Kafka  and KSQL Photo by Freddie Collins on Unsplash

Slide 2

Slide 2 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Spotting fraud in realtime hoto by Mirza Babic on Unsplash

Slide 3

Slide 3 text

Photo by Lasaye Hommes on Unsplash

Slide 4

Slide 4 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff • Account id • Location • Amount • Inbound stream of ATM data https://github.com/rmoff/gess

Slide 5

Slide 5 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Demo!

Slide 6

Slide 6 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Spot patterns within this stream Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff • Account id • Location • Amount • Inbound stream of ATM data https://github.com/rmoff/gess

Slide 11

Slide 11 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff KSQL : Stream Processing with SQL TXN_ID, ATM, CUSTOMER_NAME, CUSTOMER_PHONE ATM_POSSIBLE_FRAUD; SELECT FROM

Slide 12

Slide 12 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff

Slide 13

Slide 13 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Customer details ATM fraud txns with customer details Elasticsearch Notification service 1. Spot fraud in stream of transactions 2.Enrich transaction events with customer data

Slide 14

Slide 14 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff KSQL is the Streaming SQL Engine for Apache Kafka

Slide 15

Slide 15 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff KSQL for Real-Time Monitoring • Log data monitoring, tracking and alerting • syslog data • Sensor / IoT data CREATE STREAM SYSLOG_INVALID_USERS AS SELECT HOST, MESSAGE FROM SYSLOG WHERE MESSAGE LIKE '%Invalid user%'; http://cnfl.io/syslogs-filtering / http://cnfl.io/syslog-alerting

Slide 16

Slide 16 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff KSQL for Streaming ETL CREATE STREAM vip_actions AS   SELECT userid, page, action FROM clickstream c LEFT JOIN users u ON c.userid = u.user_id   WHERE u.level = 'Platinum'; Joining, filtering, and aggregating streams of event data

Slide 17

Slide 17 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff KSQL for Anomaly Detection CREATE TABLE possible_fraud AS  SELECT card_number, count(*)  FROM authorization_attempts   WINDOW TUMBLING (SIZE 5 SECONDS)  GROUP BY card_number  HAVING count(*) > 3; Identifying patterns or anomalies in real-time data, surfaced in milliseconds

Slide 18

Slide 18 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff CREATE STREAM pageviews WITH (PARTITIONS=4, VALUE_FORMAT='AVRO') AS   SELECT * FROM pageviews_json; KSQL for Data Transformation Make simple derivations of existing topics from the command line

Slide 19

Slide 19 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff KSQL in Development and Production Interactive KSQL  for development and testing Headless KSQL  for Production Desired KSQL queries have been identified REST “Hmm, let me try  out this idea...”

Slide 20

Slide 20 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Stream Stream joins Orders Shipments Which orders haven't shipped? order.id = shipment.order_id Leadtime shipment_ts - order_ts

Slide 21

Slide 21 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Stream Stream joins ATM transactions

Slide 22

Slide 22 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Stream Stream joins ATM transactions

Slide 23

Slide 23 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Demo!

Slide 24

Slide 24 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Self-Join (Cartesian product) Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds T Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds T1 T2

Slide 25

Slide 25 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds T1 T2 Self-Join (Cartesian product) ATM_TXNS T1 INNER JOIN ATM_TXNS T2 ON T1.ACCOUNT_ID = T2.ACCOUNT_ID

Slide 26

Slide 26 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Self-Join (Cartesian product) FROM ATM_TXNS T1 INNER JOIN ATM_TXNS T2 WITHIN 10 MINUTES ON T1.ACCOUNT_ID = T2.ACCOUNT_ID Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds T1 T2

Slide 27

Slide 27 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Self-Join T1 Txn ID T2 Txn ID T1 Time T2 Time T1 ATM T2 ATM xxx116d91d6-ef17 116d91d6-ef17 11:56:58 11:58:19 Midland Halifax 116d91d6-ef17 xxx116d91d6-ef17 11:58:19 11:56:58 Halifax Midland xxx116d91d6-ef17 xxx116d91d6-ef17 11:56:58 11:56:58 Midland Midland 116d91d6-ef17 116d91d6-ef17 11:58:19 11:58:19 Halifax Halifax

Slide 28

Slide 28 text

Slide 29

Slide 29 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Exclude joins on the same txn WHERE T1.TRANSACTION_ID != T2.TRANSACTION_ID T1 Txn ID T2 Txn ID T1 Time T2 Time T1 ATM T2 ATM xxx116d91d6-ef17 116d91d6-ef17 11:56:58 11:58:19 Midland Halifax 116d91d6-ef17 xxx116d91d6-ef17 11:58:19 11:56:58 Halifax Midland

Slide 30

Slide 30 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Exclude joins on the same txn T1 Txn ID T2 Txn ID T1 Time T2 Time T1 ATM T2 ATM xxx116d91d6-ef17 116d91d6-ef17 11:56:58 11:58:19 Midland Halifax 116d91d6-ef17 xxx116d91d6-ef17 11:58:19 11:56:58 Halifax Midland Duplicate results (A:B / B:A)

Slide 31

Slide 31 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Join Windows Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds T1 T2 WITHIN 10 MINUTES WHERE T1.TRANSACTION_ID != T2.TRANSACTION_ID

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Only join forward Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds Ac. ID Transaction ID Time ATM A42 xxx116d91d6-ef17 11:56:58 Midland A42 116d91d6-ef17 11:58:19 Halifax A42 09c2f660-ef17 19:31:11 Lloyds T1 T2 WITHIN (0 MINUTES, 10 MINUTES) WHERE T1.TRANSACTION_ID != T2.TRANSACTION_ID

Slide 35

Slide 35 text

Slide 36

Slide 36 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Only join forward T1 Txn ID T2 Txn ID T1 Time T2 Time T1 ATM T2 ATM xxx116d91d6-ef17 116d91d6-ef17 11:56:58 11:58:19 Midland Halifax WITHIN (0 MINUTES, 10 MINUTES) Ignore events in the right-hand stream prior to those in the left

Slide 37

Slide 37 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Only join forward T1 Txn ID T2 Txn ID T1 Time T2 Time T1 ATM T2 ATM xxx116d91d6-ef17 116d91d6-ef17 11:56:58 11:58:19 Midland Halifax Legit Dodgy!

Slide 38

Slide 38 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Photo by Esteban Lopez on Unsplash

Slide 39

Slide 39 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Calcuate distance between ATMs GEO_DISTANCE(TX1.location->lat, TX1.location->lon, TX2.location->lat, TX2.location->lon, 'KM') TX1 TX2

Slide 40

Slide 40 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Calculate time between transactions TX2.ROWTIME - TX1.ROWTIME AS MILLISECONDS_DIFFERENCE (TX2.ROWTIME - TX1.ROWTIME) / 1000 / 60 / 60 AS HOURS_DIFFERENCE

Slide 41

Slide 41 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Photo by Esteban Lopez on Unsplash GEO_DISTANCE(…) / HOURS_DIFFERENCE AS KMH_REQUIRED

Slide 42

Slide 42 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff So speaking of time… ksql> PRINT 'atm_txns_gess' ; Format:JSON { "ROWTIME": 1544116309152, "ROWKEY": "null", "account_id": "a218", "timestamp": "2018-12-06 17:09:58 +0000", "atm": "HSBC", …} Kafka message timestamp 2018-12-06 17:11:49 Event time

Slide 43

Slide 43 text

ksql> PRINT 'atm_txns_gess' ; Format:JSON { "ROWTIME": 1544116309152, "ROWKEY": "null", "account_id": "a218", "timestamp": "2018-12-06 17:09:58 +0000", CREATE STREAM ATM_TXNS_GESS (account_id VARCHAR, timestamp VARCHAR, … WITH (KAFKA_TOPIC='atm_txns_gess', TIMESTAMP='timestamp', TIMESTAMP_FORMAT= 'yyyy-MM-dd HH:mm:ss X'); "timestamp": "2018-12-06 17:09:58 +0000",

Slide 44

Slide 44 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff But what about the account holder?

Slide 45

Slide 45 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Photo by Samuel Zeller on Unsplash

Slide 46

Slide 46 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Customer details ATM fraud txns with customer details Elasticsearch Notification service 1. Enrich transaction events with customer data

Slide 47

Slide 47 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sources syslog flat file CSV JSON MQTT

Slide 48

Slide 48 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sinks Amazon S3 MQTT

Slide 49

Slide 49 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sources Sinks Amazon S3 MQTT syslog flat file CSV JSON MQTT

Slide 50

Slide 50 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Confluent Hub hub.confluent.io • One-stop place to discover and download : • Connectors • Transformations • Converters

Slide 51

Slide 51 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Demo Time! Customer details Kafka Connect Debezium

Slide 52

Slide 52 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Do you think that’s a table you are querying?

Slide 53

Slide 53 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff The Table Stream Duality Account ID Balance 12345 €50 Account ID Amount 12345 + €50 12345 + €25 12345 -€60 Account ID Balance 12345 €75 Account ID Balance 12345 €15 Time Stream Table

Slide 54

Slide 54 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff The truth is the log. The database is a cache of a subset of the log. —Pat Helland Immutability Changes Everything http://cidrdb.org/cidr2015/Papers/CIDR15_Paper16.pdf Photo by Bobby Burch on Unsplash

Slide 55

Slide 55 text

Slide 56

Slide 56 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Ac. ID T1 Time ATM T2 Time ATM A42 11:56:58 Midland 11:58:19 Halifax Suspect Transactions Dodgy!

Slide 57

Slide 57 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Name Phone Ac. ID T1 Time ATM T2 Time ATM Robin M 1234 567 A42 11:56:58 Midland 11:58:19 Halifax Suspect Transactions

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Slide 60

Slide 60 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff What can we do with it? Photo by Joshua Rodriguez on Unsplash

Slide 61

Slide 61 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Realtime Operations View & Analysis

Slide 62

Slide 62 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Push notification to the customer

Slide 63

Slide 63 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Confluent Community Components Apache Kafka with a bunch of cool stuff! For free! Database Changes Log Events loT Data Web Events … CRM Data Warehouse Database Hadoop Data  Integration … Monitoring Analytics Custom Apps Transformations Real-time Applications … Confluent Platform Confluent Platform Apache Kafka® Core | Connect API | Streams API Data Compatibility Schema Registry Monitoring & Administration Confluent Control Center | Security Operations Replicator | Auto Data Balancing Development and Connectivity Clients | Connectors | REST Proxy | CLI SQL Stream Processing KSQL Datacenter Public Cloud Confluent Cloud CONFLUENT FULLY-MANAGED CUSTOMER SELF-MANAGED

Slide 64

Slide 64 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff Free Books! https://www.confluent.io/apache-kafka-stream-processing-book-bundle

Slide 65

Slide 65 text

@rmoff robin@confluent.io https://www.confluent.io/ksql http://cnfl.io/slack https://cnfl.io/demo-scene

Slide 66

Slide 66 text

ATM Fraud Detection with Apache Kafka and KSQL @rmoff • CDC Spreadsheet • Blog: No More Silos: How to Integrate your Databases with Apache Kafka and CDC • #partner-engineering on Slack for questions • BD team (#partners / partners@confluent.io) can help with introductions on a given sales op Resources #EOF