We designed and built a unified AI data pipeline over 14 weeks: Data platform architecture (weeks 1-4): lakehouse architecture on AWS. S3 as the data lake with three zones: raw (landing zone — immutable copies of source data), processed (cleaned, validated, standardised), and curated (feature-engineered, model-ready). AWS Glue Data Catalog as the metadata layer — every table, column, and transformation documented. Apache Airflow (managed — AWS MWAA) for orchestration — replacing the 200 ad hoc SQL scripts with documented, versioned, monitored DAGs (Directed Acyclic Graphs). Data ingestion (weeks 2-6): connecting all 11 data sources. Transaction databases (4 country databases): CDC via Debezium — capturing every insert, update, and delete in real-time, streaming to Kafka, and landing in S3 within seconds. The real-time stream also feeding the fraud detection pipeline.
Schema harmonisation: the 4 country databases mapped to a unified transaction schema — currency standardised to SGD with original currency preserved, timestamps normalised to UTC with original timezone preserved, and country-specific fields mapped to a standardised taxonomy. Merchant data (Salesforce): daily batch sync via Salesforce Bulk API — incremental extraction based on last-modified timestamp. Customer data (split between 2 systems): unified customer record created by matching across systems using deterministic matching (email, phone) and probabilistic matching (name similarity for cases where customers registered with different email addresses). Risk and fraud data: real-time streaming from the fraud detection system — risk scores, alert details, and investigation outcomes flowing into the data pipeline for model retraining. Marketing and analytics data (Segment, Amplitude): batch extraction via API — user events, campaign performance, and app usage data enriching the customer profile. Data quality and governance (weeks 3-8): systematic data quality management.
Data quality rules: 340 automated quality checks running on every data ingestion — schema validation (expected columns, data types), null checks (mandatory fields), range checks (transaction amounts within expected ranges per country — a SGD 500,000 SME transaction is plausible; a SGD 50M transaction needs investigation), referential integrity (every transaction references a valid merchant and customer), freshness checks (data arriving within expected latency — real-time streams within 30 seconds, batch data within the scheduled window), and cross-source consistency (transaction counts in the payment system matching the fraud system matching the accounting system — discrepancies flagged for investigation). Data classification: every column tagged as Personal (NRIC, name, address, phone, email), Sensitive Financial (account balances, transaction details), Business Confidential (merchant agreements, pricing), or Public.
Access controls: implemented per classification — data scientists accessing anonymised transaction features (no personal data), business analysts accessing aggregated dashboards, compliance team accessing full data with audit logging. Data lineage: dbt (data build tool) managing all transformations with built-in documentation — every metric traceable from dashboard to source. When MAS asks "how is this number calculated?" — the answer is generated automatically from the dbt documentation. Feature store and ML pipeline (weeks 6-12): centralised feature engineering.
Feature store (Feast on AWS): 280 production features across 4 categories — customer features (transaction velocity, average transaction value, preferred payment methods, account age, activity patterns), merchant features (daily/weekly/monthly GMV, chargeback rate, industry category, risk tier, active days), transaction features (amount relative to customer average, time since last transaction, geographic distance from usual locations, currency, cross-border flag), and contextual features (time of day, day of week, public holiday flag, payday proximity, promotional event flag). Each feature: versioned, documented with business definition, and monitored for drift (distribution changes trigger alerts). Feature computation: batch features recomputed daily at 2 AM SGT (when transaction volume is lowest), real-time features computed at inference time via Kafka Streams. Training-serving consistency: the same feature computation code used for both historical training data and real-time inference — eliminating training-serving skew. Regulatory reporting automation (weeks 10-14): automated report generation.
MAS monthly transaction report: automated DAG running on the 1st business day of each month — querying curated data, computing required metrics, generating the report in MAS-required format, and routing for compliance team review before submission. Quarterly risk assessment: automated data collection and metric computation — the compliance team reviewing and adding qualitative commentary rather than computing every number manually. Audit-ready data: every number in every report traceable to source data through automated lineage documentation.