Designing a Payments Reporting Platform on GCP: A Fictional Case Study

A tool-by-tool walkthrough of how I would architect a cloud-native payments reporting stack for a regional bank from raw transaction data to executive dashboards

6 min read · Data Engineering · Google Cloud Platform · Analytics Engineering

Note: Clearwater Financial is a fictional bank created for this case study. All scenarios, metrics, and outcomes are hypothetical, designed to illustrate realistic architectural decisions in banking data reporting. This is an architectural thought exercise informed by patterns common in the payments and banking industry.

The Problem

Many regional banks still run their payments reporting the same way they did 15 years ago: nightly batch jobs extract data from the core banking system, load it into an on-premises Oracle warehouse, and generate reports that analysts find waiting for them at 8am, reflecting data that is already 7+ hours stale.

In this case study, I explore how I would re-architect that reporting stack for a fictional bank, Clearwater Financial, using Google Cloud Platform. The goal is not to rebuild the entire payments platform, the core banking system stays in place. The goal is specifically to modernize how payment transaction data flows into reporting: faster, more reliable, and ready for AI-powered analytics.

The brief:

• Replace overnight batch reporting with near real time data freshness
• Create a single source of truth for 5 business units Operations, Risk, Product, Reconciliation, and Compliance
• Satisfy 7-year regulatory data retention (PCI-DSS, SOX, Basel III)
• Build an analytics foundation that can support fraud scoring without a separate ML platform

Scope: What This Architecture Covers

Before diving in, it is worth being explicit about what this is and is not.

In scope: How transaction data is ingested, transformed, stored, and served for reporting and analytics purposes.

Out of scope: The core banking system itself, payment processing, card network integrations, and settlement infrastructure. Those systems remain unchanged. This architecture sits downstream of them.

Think of it as a reporting layer built on top of an existing payments system, not a replacement for it.

Architecture Overview

The reporting platform is built across five layers:

Core Banking System
       ↓
[1] Ingestion      →  Pub/Sub + Cloud Storage
[2] Processing     →  Dataflow + Cloud Composer + dbt
[3] Storage        →  BigQuery
[4] AI / ML        →  BigQuery ML → Vertex AI Pipelines
[5] Serving        →  Looker + Looker Studio

Each layer has a single, clearly defined responsibility. No layer does another layer’s job.

The core banking system generates two types of data: real-time transaction events (card swipes, ACH initiations, wire confirmations) and batch files (end-of-day settlement files, correspondent bank reconciliation reports).

Pub/Sub handles the real-time stream. The core banking system publishes one event per transaction to a Pub/Sub topic. Downstream consumers — the Dataflow processing pipeline, the compliance audit logger — each receive an independent copy via their own subscriptions. The core banking system never needs to know who is consuming its events. Adding a new consumer later requires no changes upstream.

Cloud Storage handles batch files. End-of-day files from correspondent banks land in Cloud Storage as the raw data lake zone. Lifecycle policies automatically tier files to Nearline (after 90 days) and Coldline (after 1 year), satisfying the 7-year retention requirement at near-zero archival cost.

Key trade-off: Pub/Sub delivers messages at least once, not exactly once. A transaction event may arrive twice on a network retry. Downstream deduplication on transaction_id in the Dataflow pipeline is mandatory, not optional.

Layer 2 — Processing: Transforming Raw Data

Raw transaction events are not ready for reporting. They need enrichment, masking, scheduling, and modeling. Three tools handle this.

Dataflow processes the real-time stream from Pub/Sub. It enriches each event with merchant category codes, applies PCI-compliant tokenization to card numbers (no raw card data ever reaches BigQuery), deduplicates on transaction_id, and writes enriched records to BigQuery's streaming insert API. The same Dataflow pipeline handles historical batch reprocessing from Cloud Storage — one codebase for both real-time and batch.

Cloud Composer (managed Apache Airflow) orchestrates the scheduled reporting jobs. Monthly Basel III reports, daily reconciliation runs, and weekly risk summaries all run as Composer DAGs with hard dependency chains — reconciliation cannot start until Dataflow’s settlement stream signals completion. Failed jobs retry automatically and alert the on-call team before business users notice.

dbt governs all SQL-based transformation inside BigQuery. This is the single most impactful decision in the architecture. Before dbt, each business unit maintained its own SQL definitions. The Risk team’s “settled transaction” differed from Operations’. Every cross-functional meeting opened with a metric reconciliation debate.

dbt enforces a single definition for every metric, version-controlled in Git, tested on every deployment, and documented in a browsable data catalog. The transformation follows a three-layer pattern:

• Staging — one-to-one with source tables, light renaming only, no business logic
• Intermediate — joins and business logic: transactions enriched with customer, merchant, and account data
• Marts — audience-specific aggregations: ops_daily_settlement, risk_fraud_signals, compliance_regulatory_extract

dbt’s schema tests (not_null, unique, accepted_values) run on every deployment, catching data quality issues before they reach analyst dashboards rather than after an escalation.

Layer 3 — Storage: BigQuery as the Reporting Warehouse

All reporting in this architecture runs against BigQuery — Google’s serverless columnar analytics warehouse.

Two optimization decisions are non-negotiable from day one:

Partition by transaction_date. Every payments report filters by date. Partitioning reduces query cost by 70–80% by scanning only relevant date partitions rather than the full table history. On three years of transaction data, this is the difference between a $2 query and a $35 query.

Cluster by account_id. After date filtering, most queries aggregate by account. Clustering physically co-locates rows with the same account, further reducing bytes scanned.

Both are enforced via Organization Policy before any analyst has warehouse access. Cost controls are architecture decisions — not operational afterthoughts.

Note: This architecture does not include Cloud Spanner. The operational payments ledger (the source of truth for live transactions) remains in the existing core banking system. BigQuery is purely the reporting and analytics layer.

Layer 4 — AI/ML: Fraud Scoring Without a Separate ML Platform

Clearwater Financial’s data team has strong SQL skills but no dedicated ML engineers. The AI layer is designed around that constraint.

Phase 1 — BigQuery ML allows the team to train a logistic regression fraud classifier directly inside BigQuery using SQL. No data movement, no Python, no separate ML platform. A first fraud scoring model — trained on three years of transaction features already in BigQuery — can be in production within two weeks of clean data being available. It is not the most sophisticated model. It is a working model that demonstrates value and earns investment in Phase 2.

Phase 2 — Vertex AI Pipelines automates the full retraining cycle once the team is ready: pull updated training data from BigQuery → re-engineer features → retrain → evaluate against the current model → promote if precision/recall improve → log to the Vertex AI Model Registry. The Model Registry provides the compliance audit trail regulators require: exactly which model version scored which transaction and when.

This phased approach is deliberate. Building full MLOps infrastructure before a single model proves value is a common and expensive mistake.

Layer 5 — Serving: The Right Tool for Each Audience

Two tools serve two different audiences — using one for both creates either over-engineering or governance problems.

Looker serves executives and compliance teams. Its LookML semantic layer defines every business metric centrally — “net settled revenue” is calculated once and every dashboard, every team, every report sees the same number. Row-level security ensures each team only sees authorized data. SOX audit logging records who ran which report, when, with which filters.

Looker Studio serves operational teams. Settlement exception counts, daily reconciliation views, and intraday transaction volumes do not require Looker’s governance overhead. Looker Studio connects directly to BigQuery, costs nothing, and lets Operations and Reconciliation analysts build their own daily working reports without raising tickets. In practice this reduces data team ticket volume for simple report requests by an estimated 40–60%.

What This Architecture Does Not Solve

Being explicit about limitations is as important as the design itself.

This architecture improves reporting latency — from overnight batch to near-real-time. It does not reduce payment processing latency. If a customer’s wire transfer takes 2 hours to settle, this architecture reports that more quickly — it does not make the settlement faster.

It also assumes the core banking system can publish events to Pub/Sub. In practice, legacy core banking systems often cannot do this natively. An intermediate CDC (Change Data Capture) layer — using tools like Debezium or Datastream — is frequently required as a prerequisite step before this architecture can be implemented.

Conclusion

A payments reporting modernization on GCP does not require rebuilding the entire payments stack. The core banking system stays in place. The goal is a clean, cloud-native reporting layer downstream of it: reliable ingestion, governed transformations, a cost-controlled warehouse, and self-service reporting for every team.