Real-Time Fraud and Geographic Threat Detection on Google Cloud Platform

Photo by Thomas Bormans on Unsplash

The Objective

You have an existing GCP-based system with multiple services, data sources, pipelines and databases, utilizing BigQuery as the main distributed data warehouse for integrated analytics.

The system processes business transactions and feeds them into BigQuery for reporting and analytics. This works well, but there are two gaps: fraud and threat detection happens after the fact — by which time fraudulent transactions have already been processed — and transaction data lacks geographic context for meaningful location-based analysis.

The goal is to introduce a real-time detection and enrichment layer that:

• Evaluates every transaction before completion
• Blocks fraudulent activity immediately
• Enriches transactions with geographic context (IP-to-location)

Key requirements:

• Sub-millisecond decision latency — fraud rules must execute within the transaction, not after it
• Decisions on multiple rule types — velocity limits, spending caps, and network-level attack detection, all evaluated atomically
• Seamless GCP integration — results must flow into BigQuery alongside existing business data, with minimal custom code
• Geographic intelligence — IP-to-location mapping for pattern analysis across countries and cities

This article describes one approach to design and implement this functionality.

Solution Architecture — Overview

The architecture has three layers:

• Detection — a Business Microservice routes every transaction through VoltDB for real-time fraud rule evaluation before it completes
• Storage — results are published to Pub/Sub and fan out into BigQuery (standard table + Iceberg) with zero custom ETL
• Enrichment — a Dataform pipeline converts raw IPs to geographic context using MaxMind GeoIP2 datasets

The following sections cover each layer and components in detail.

Detailed Architecture Breakdown

BigQuery: Integrated Analytics

BigQuery is used as an existing de-facto data warehousing solution that has data from other workflows and business use cases. Threat detection data needs to be co-located with existing business data for integrated analytics — joining threat patterns with customer profiles, transaction histories from other systems, or operational metrics.

Business Microservice

In a real deployment, this would be an existing service already running on GCP — exposing an API to accept and process requests from users, deployed as a container with REST API endpoints, capable of auto-scaling as load changes. Design and implementation of such a service is out of scope for this article.

For the purpose of this POC, the Java application generated using a Claude Skill (described in the next section) serves as a minimal stand-in for that microservice. It demonstrates the two critical integration points that any such service would need:

• connecting to VoltDB for real-time threat detection
• and publishing results to GCP Pub/Sub for downstream analytics in BigQuery

It intentionally omits everything else a production microservice would have — REST API endpoints, authentication, observability, error handling — but includes the full threat/fraud detection and GCP integration flow that is the focus of this article.

VoltDB for Threat Detection

The core requirement — evaluating multiple fraud rules atomically within a single transaction — rules out most databases. If you check a velocity rule and a spending rule in separate queries or services, a malicious actor can exploit the gap between checks.

VoltDB solves this with single-partition stored procedures: all rule-checks execute within one atomic ACID transaction, in-memory, with sub-millisecond latency. There is no gap between checking and committing. VoltDB’s TIME_WINDOW materialized views provide pre-computed, automatically maintained aggregations (e.g., “transactions per account in the last 30 seconds”) that can be queried with O(1) cost inside the transaction.

This combination — atomic multi-rule evaluation, in-memory speed, and built-in time-windowed aggregations — makes VoltDB a natural fit for the real-time detection layer.

Building with the VoltDB Skill

In order to utilize VoltDB — one has to decide what data should be stored there and what “logic” is needed to query it. Then, the service needs to connect to and interact with VoltDB using client libraries and APIs.

This functionality was built using Claude Code with the ***VoltDB Skill — a Claude Code extension that understands VoltDB’s data model and stored procedure patterns. In particular, the /voltdb-development*** skill was used to generate the initial working client code.

The development process:

• Describe the data model — the tables needed (accounts, transactions, subnet requests, merchants), their partition keys, and how they relate to each other
• Describe the critical operations — the fraud detection rules (velocity burst, spending spike, subnet rate) and transaction requirements
• The Skill generates working starter code — a complete Java application including
• • VoltDB connection setup and schema deployment
• • DDL with partitioned tables and materialized views
• • VoltDB-specific artifacts, such as Java stored procedures with the fraud detection logic
• • Client application code
• • Integration tests using Testcontainers that verify each rule triggers correctly

The generated application can run as-is on the cloud, locally, or in a Docker container — serving as the minimal working microservice described above.

Using this starter code, one can continue to fine-tune, add more logic, and integrate with other components of the system (like PubSub). This GIT repo has the minimally viable working application that is the result of this development.

Deploying on GCP

The application deploys as a Cloud Run job connecting to a VoltDB cluster on GKE, publishing results to Pub/Sub via a private VPC connector.

The GitHub repository includes everything needed to deploy this yourself:

• A Dockerfile that packages the application as a container image
• A deploy-cloudrun.sh script that builds the container image via Cloud Build and deploys to Cloud Run in a single step
• A PubSubPublishRunner integration test that seeds ~467 synthetic transactions covering all attack scenarios into a live GCP environment

Full deployment instructions, including VPC connector setup, internal load balancer configuration for VoltDB, and verification steps, are in the README.

The following sections dive into the details of the threat detection implementation in VoltDB

Threat Detection Data Model

The schema uses four tables across two partition spaces:

• ACCOUNTS (partition: ACCOUNT_ID) — stores account state and balances
• TRANSACTIONS (partition: ACCOUNT_ID) — transaction records; co-located with ACCOUNTS so all fraud rule checks run in a single atomic operation
• SUBNET_REQUESTS (partition: SUBNET) — tracks request counts per /24 subnet; separate partition space for network-level detection
• MERCHANTS (replicated) — small merchant reference table; available locally on every partition

Threat Detection Rules and Transactions

Three fraud rules are evaluated on every transaction:

• velocity burst rule that blocks accounts exceeding five transactions in any 30-second window
• spending spike rule that blocks accounts that exceed $5,000 in cumulative spend within 60 seconds
• subnet rate rule that blocks any single /24 subnet generating more than 100 requests within 5 seconds.

The first two rules protect individual accounts; the third detects coordinated network-level attacks where many different accounts are targeted from the same IP range.

Each rule is powered by a TIME_WINDOW materialized view — a pre-computed, in-memory aggregation that VoltDB maintains automatically on every insert.

Transaction Execution Flow

Each request passes through four steps. Steps 1 and 2 are the two VoltDB transaction boundaries — one per partition space — with the subnet count from Step 1 passed as input into Step 2.

Full implementation is in the GitHub repository.

Data Pipeline: Storage and GEO Enrichment

Enriched transaction data flows from VoltDB into Pub/Sub, then fans out to BigQuery for analytics and to Cloud Storage via Iceberg for open-format access.

GEO enrichment is handled downstream by a Dataform pipeline that converts raw IP addresses to countries, cities, and coordinates using MaxMind datasets.

The sections below cover each component in detail.

PubSub: Real-Time Data Movement to BigQuery

Once a transaction is evaluated by VoltDB, its result needs to move downstream for analytics. **Google Cloud Pub/Sub** handles this — acting as the message bus between the Java service and BigQuery.

The Java service publishes each completed transaction as a single denormalized JSON message to a single Pub/Sub topic (threat-transactions), with account_name and merchant_name embedded directly directly — so downstream consumers never need to join back to VoltDB.

From that single topic, data fans out to two destinations simultaneously:

• Standard BigQuery table (threat_transactions) — optimized for interactive SQL queries and BI tools
• BigLake Iceberg table (threat_transactions_iceberg) — open Parquet format on Cloud Storage, accessible from non-BigQuery engines like Spark or Trino

Iceberg on Cloud Storage

***Apache Iceberg is an open table format for large analytic datasets, designed to work across multiple query engines. The second PubSub subscription writes to a BigLake Iceberg table backed by Parquet files on Google Cloud Storage***. This is also a zero-code configuration — BigQuery manages the Iceberg metadata and Parquet files lifecycle automatically.

MaxMind for IP-to-Geo Lookup

Raw threat data contains IP addresses — precise, but meaningless for pattern analysis. Knowing that 81.2.69.142 was blocked tells you nothing; knowing it was a London IP does.

This is why the system integrates MaxMind GeoIP2 , a commercial IP geolocation database, which maps IPv4 and IPv6 addresses to countries, cities, and coordinates. It is distributed as downloadable CSV files, updated weekly, and is widely used in fraud detection, content localization, and network analytics.

Before the geo data can be used for enrichment, it needs to be converted into a numeric range format that BigQuery can match against.

The transformation and enrichment are handled by **Dataform** — a GCP-native ELT tool that runs SQL-based transforms directly in BigQuery using declarative SQLX files. It manages the dependency order between models automatically, making it a natural fit for a pipeline where each step depends on the previous one’s output. No custom code or external orchestration is needed.

The full IP-to-GEO pipeline runs in three steps:

• Load — MaxMind’s City Blocks and City Locations CSV files are loaded into BigQuery staging tables via bq load
• Transform — a Dataform SQLX model converts CIDR notation to INT64 start/end ranges producing the geo_ip_blocks table
• Enrich — a second SQLX model (threat_transactions_geo) joins transaction records with geo_ip_blocks at query time, adding country, city, and coordinates to each row

Since MaxMind publishes updated files weekly, the entire pipeline can be fully automated by scheduling a Dataform run that reloads the CSVs from a Cloud Storage bucket and re-executes the transforms with no manual intervention.

Threat Intelligence in Action

With the full pipeline in place — transactions screened by VoltDB, results streamed through Pub/Sub into BigQuery, and IP addresses enriched with geographic context — we can now ask the questions the system was built to answer: which threats are firing, where are they coming from, and which accounts are most at risk.

The queries below were run against BigQuery using a Python notebook with the google-cloud-bigquery client library. Results were pulled into pandas DataFrames and visualized with Plotly, chosen for its interactive hover tooltips and built-in geo map support.

The demo dataset contains 295 synthetic transactions distributed across six behavioral groups: normal traffic, velocity bursts, high-spend attacks, subnet floods, disabled-account attempts, and invalid-merchant transactions. Approximately 80 transactions are blocked across the three active fraud rules. The MaxMind geo data uses a small subset of the full database seeded with three known IP ranges — a London subnet (81.2.69.x), a US subnet in Milton, WA (216.160.83.x), and a Swedish subnet in Linköping (89.160.20.x) — which correspond to the geographic clusters visible in the heatmap queries.

Threats by Rule — Which Rules Fire Most?

Shows which fraud rules fire most frequently, how many accounts and source IPs are involved per rule, and the total dollar value of blocked transactions — broken down by day.

Threat Hotspots — Geographic Clustering with ST_CLUSTERDBSCAN

Groups blocked transactions by geographic proximity using a 20-mile radius, identifying whether threats originate from isolated IPs or coordinated multi-source clusters.

Rather than aggregating threats by country (too coarse) or by individual IP (too granular), we use BigQuery’s ST_CLUSTERDBSCAN to find threat hotspots — geographic areas where threats concentrate within a 20-mile radius.

The query that implements this (notebook):

Account Risk Profile

Ranks every account by the number of threat events triggered against it, how many distinct fraud rules fired, and its overall transaction acceptance rate — surfacing the highest-risk accounts at a glance.

Key Takeaways

1. VoltDB TIME_WINDOW views work well for rule-based fraud detection. Pre-computed, in-memory aggregations with automatic window expiry eliminate the need for external state management or stream processing frameworks.

2. PubSub BigQuery subscriptions are genuinely zero-code. The dual fan-out to standard and Iceberg tables required only configuration — no custom code, no operational overhead.

3. BigQuery + MaxMind GEO + Dataform handle geo enrichment cleanly. The range-based IP lookup using NET.IPV4_TO_INT64() and a broadcast join is efficient and maintainable as a SQLX model.

The system is ready to integrate into a larger GCP-based analytics platform, where threat detection data would sit alongside other business data in BigQuery for cross-domain analysis.

References

Source Code

• threat-detection-blueprint — full working POC: VoltDB client, GCP deployment scripts, Dataform models, BigQuery DDL, and seed data
• VoltDB Skills for Claude Code — the /voltdb-development skill used to generate the initial application code

VoltDB / Volt Active Data

• Volt Active Data — in-memory, ACID-compliant distributed database
• VoltDB Developer Edition — free developer license
• VoltDB on Kubernetes — deploying VoltDB on GKE

Google Cloud Platform

• BigQuery — serverless data warehouse for analytics
• Cloud Pub/Sub — real-time message streaming
• Pub/Sub BigQuery Subscriptions — zero-code direct write from Pub/Sub to BigQuery
• BigLake Iceberg tables — open-format Iceberg tables managed by BigQuery
• Dataform — GCP-native ELT tool for SQL-based BigQuery transformations
• Cloud Run — fully managed serverless container execution
• Google Kubernetes Engine (GKE) — managed Kubernetes for VoltDB cluster hosting
• Cloud Storage — object storage backing Iceberg/Parquet files
• ST_CLUSTERDBSCAN — BigQuery density-based spatial clustering function

MaxMind

• MaxMind GeoIP2 databases — commercial IP geolocation database (IPv4/IPv6 to country, city, coordinates)

Apache Iceberg

• Apache Iceberg — open table format for large analytic datasets

Development Tools and Libraries

• Claude Code — agentic coding tool used to generate the VoltDB application via the VoltDB Skill
• Testcontainers — framework for integration tests using Docker containers
• google-cloud-bigquery Python client — used for analytical queries in the Python notebook
• pandas — Python data analysis library used for DataFrame processing
• Plotly — interactive Python visualization library used for charts and geo maps