Kafka to MySQL to a Real-Time Fraud Dashboard

Club the producer, two consumers, two MySQL tables, and Grafana into one live decision system.

Learning Outcomes and Timeline

• Run independent Kafka consumer groups for transaction storage and alert storage.
• Explain why live dashboards use ingestion time while business analysis uses event time.
• Build Grafana panels that visibly update as MySQL rows arrive.
• Translate a live fraud signal into an operational decision.

The Complete Architecture

  transactions_16.csv
       |
       v
  transaction_producer_16.py
       |
       v
  transactions-topic-l16 (Kafka)
       |                         |
       v                         v
  consumer_store_mysql_16.py  consumer_alerts_mysql_16.py
  group: mysql-store-l16      group: mysql-alert-l16
       |                         |
       v                         v
transactions table          fraud_alerts table
       \                         /
        \                       /
         +------ Grafana ------+
              refresh: 5s

Download the Isolated Lecture 16 Files

The Lecture 16 generator stores its last issued number in .transactions_sequence_l16. Repeated batches therefore continue the ID range instead of colliding with rows already stored in sda_course_l16.

For larger or custom datasets, use the course-wide Configurable Transaction Generator.

Lab Part 1 — Start a Clean Live Pipeline

1. Start MySQL and Grafana

docker network create sda-analytics

docker run -d \
  --name sda-mysql \
  --network sda-analytics \
  -e MYSQL_ROOT_PASSWORD=admin \
  -p 3306:3306 \
  mysql:8.4

docker run -d \
  --name grafana \
  --network sda-analytics \
  -p 3000:3000 \
  -e GF_SECURITY_ADMIN_PASSWORD=admin \
  grafana/grafana:latest

If the containers already exist, use docker start sda-mysql grafana instead of creating them again.

2. Install Python Packages and Generate Data

pip install kafka-python mysql-connector-python
  python generate_transactions_16.py

3. Reset the Demo Tables

Use a clean run so counts rise from zero and every student can see the dashboard move.

Stop any producer or consumer processes left from an earlier run before resetting the tables.

docker exec -it sda-mysql mysql -uroot -padmin

CREATE DATABASE IF NOT EXISTS sda_course_l16;
USE sda_course_l16;
DROP TABLE IF EXISTS fraud_alerts;
DROP TABLE IF EXISTS transactions;
CREATE USER IF NOT EXISTS 'grafana'@'%' IDENTIFIED BY 'grafana';
GRANT SELECT ON sda_course_l16.* TO 'grafana'@'%';
FLUSH PRIVILEGES;
exit

4. Run Four Terminals

Start both consumers before the producer. The producer sends approximately three transactions per second at the default delay.

Lab Part 2 — Build the Real-Time Dashboard

Open http://localhost:3000. Add a separate MySQL datasource for this lab: host sda-mysql:3306, database sda_course_l16, user grafana, password grafana. Name it MySQL — Lecture 16 Live.

1. Create a dashboard named Live Fraud Operations — Lecture 16.
2. Set the time range to Last 5 minutes.
3. Set Refresh to 5s.
4. Build the four panels below.

Panel A — Transactions Received (Stat)

SELECT COUNT(*) AS value
FROM transactions
WHERE $__timeFilter(ingested_at);

This number should increase on nearly every refresh.

Panel B — Live Throughput (Time Series)

SELECT
  $__timeGroupAlias(ingested_at, '5s'),
  COUNT(*) AS value
FROM transactions
WHERE $__timeFilter(ingested_at)
GROUP BY 1
ORDER BY 1;

Business question: Is the transaction stream flowing at the expected rate?

Panel C — Alerts by Type (Bar Chart)

SELECT alert_type, COUNT(*) AS value
FROM fraud_alerts
WHERE $__timeFilter(detected_at)
GROUP BY alert_type
ORDER BY value DESC;

Business question: Which risk pattern is creating the current review workload?

Panel D — Live Alert Queue (Table)

SELECT
  detected_at,
  severity,
  alert_type,
  transaction_id,
  user_name,
  amount,
  merchant,
  city,
  message
FROM fraud_alerts
WHERE $__timeFilter(detected_at)
ORDER BY detected_at DESC
LIMIT 20;

Use value mappings or cell colours: critical = red, warning = amber.

Lab Part 3 — Observe, Decide, and Troubleshoot

What Students Should Observe

1. The transaction count rises continuously.
2. The throughput line forms a stable band while the producer runs.
3. The alert queue updates only when a flagged event arrives.
4. Stopping the producer flattens throughput without deleting historical rows.

60-Second Management Briefing

1. Signal: State current throughput and alert count.
2. Pattern: Identify the dominant alert type and severity.
3. Decision: Choose which cases analysts should inspect first.
4. Caveat: Explain that flagged means “review,” not confirmed fraud.

Troubleshooting

Key Takeaways

• Kafka decouples producers from multiple independent business workflows.
• MySQL turns stream outputs into queryable operational tables.
• Grafana refresh creates near-real-time visibility; it does not consume Kafka directly.
• Event time and processing time answer different management questions.
• A useful dashboard ends with a prioritised action, not merely a chart.