How We Transformed a Microservice Finance System into a Scalable Big Data Warehouse

Introduction

During the rapid growth of ZhaiZhai's business, the financial system became a critical hub linking business and finance. The early "pyramid" microservice architecture collected upstream and downstream data for financial indicators but soon revealed problems such as difficulty tracing metric differences, low data processing efficiency, and insufficient system stability.

Phase 1: Microservice Architecture Issues

1.1 Initial Architecture Design

1.2 Storage Design

<code>业务特点分析：
   特点1：各业务数据字段基本不相同，几乎没有可抽取出的统一字段，如果想统一存储，只能以JSON字符串的形式；
   特点2：需要对加工后的财务数据实时可查，若以JSON存储，不方便结构化查询；
   特点3：如果不统一存储，来一个业务新建一些表，维护成本很高，万一数据量大，还涉及到分库分表问题；
   特点4：源数据来源方式不相同，有用接口的，有用云窗的，有人工后台录入的；
</code>

Because the early data volume was small, the team stored all source data as JSON strings in a single MySQL table

source_data

and synchronized it to Elasticsearch via binlog listening for multidimensional queries.

1.3 Scheduling Model Design

The scheduling model uses the distributed task framework XXL‑Job for offline batch jobs.

1.4 Processing Model Design

Data is processed in memory via RPC for dimension joins and metric calculations, then written to

dwd_financial

and aggregated into

dws_financial

for Hive export.

Phase 2: Architecture Evolution Considerations

2.1 Core Problem Analysis

Problem 1: Data Integrity

Data is scattered across microservice databases, forming islands. Financial calculations need cross‑service dimension and metric data.

<code>// Order amount calculation needs multiple service calls
public BigDecimal calculateOrderAmount(Long orderId) {
    Order order = orderService.getOrder(orderId); // get base order
    User user = userService.getUser(order.getUserId()); // get user level
    Coupon coupon = couponService.getCoupon(...); // get coupon info
    // ...more service calls
}
</code>

RPC calls are unstable; dimension loss rate can reach 10% even with retries.

Failed retries cause whole pipeline blockage, leading to inaccurate metrics.

Problem 2: Unreliable Scheduling & Data Sync

ES sync status is invisible, causing calculations to start before ES is ready.

XXL‑Job may finish after the cloud‑window data extraction, leading to data inconsistency.

Problem 3: Scalability Bottlenecks

Single‑node processing hits latency up to 6 hours.

Adding new metrics requires code changes and redeployment.

Resources cannot scale elastically.

2.2 Solution Ideas

Avoid RPC calls : Load dimension data into a centralized data lake instead of real‑time microservice calls.

Decouple analysis workload : Separate analytical load from transactional services.

Unified scheduling ecosystem : Use cloud‑window unified signal scheduling.

Parallel processing : Adopt Spark for distributed computation.

2.3 Decision Path

Three options were evaluated:

Enhance existing microservice architecture – low change, but cannot solve analysis bottlenecks.

Introduce a big‑data technology stack – professional analysis capability, higher learning cost.

Adopt commercial solutions – quick to launch, high cost, low flexibility.

Key decision factors:

Business data volume exceeds single‑node capacity.

Monthly offline processing reaches tens of millions to billions of records.

Financial analysis needs multi‑dimensional capabilities.

Team has a 3‑month window for technical transformation.

The team chose to introduce a big‑data stack.

2.4 Fundamental Difference Between Real‑Time RPC and Batch Processing

Microservice RPC : Real‑time calls to multiple services, prone to latency and failures.

Big‑Data Batch : ETL aggregates data first, eliminating runtime dependencies.

Batch frameworks (MapReduce, Spark) move computation to the data nodes, reducing network traffic.

Phase 3: New Architecture Design

3.1 Data Model Design

ODS (Operational Data Store) : Stores raw data snapshots.

DW (Data Warehouse) : Stores cleaned, standardized operational data.

DIM (Dimension Layer) : Dictionary tables such as category, city, channel, device type, payment status.

DM (Data Market) : Subject‑oriented data marts for OLAP analysis.

ADS (Application Data Store) : Data services directly serving applications.

3.1.2 Dimensional Modeling

Process: select business process → define grain → determine dimensions → design fact tables.

<code>-- Shared dimension table
CREATE TABLE dim_time (
    date_key INT PRIMARY KEY,
    full_date DATE,
    day_of_week TINYINT,
    month TINYINT,
    quarter TINYINT,
    year SMALLINT
);

-- Order fact table
CREATE TABLE fact_orders (
    order_id BIGINT,
    date_key INT REFERENCES dim_time,
    -- other fields ...
);

-- Inventory fact table
CREATE TABLE fact_inventory (
    sku_id BIGINT,
    date_key INT REFERENCES dim_time,
    -- other fields ...
);
</code>

Benefits: reusable dimensions, high extensibility, efficient ETL development.

3.1.3 Scheduling System

Unified cloud‑window task dependency management for parent‑child tasks.

Key‑path monitoring and automatic retries.

3.2 Big‑Data Technology Selection

Compute Engine Comparison

<code>Engine      Model          Scale   Latency   SQL Compatibility   Fault Tolerance   Resource Cost   Learning Curve   Best Scenario
Hive(MR)    Batch(MapReduce) <10TB  Hours     HiveQL             Disk Checkpoint    High IO          Low              Historical data ETL
Hive(Tez)   DAG Batch       <50TB   Min‑Hours HiveQL             Task Retry          Medium           Low              Medium‑scale data warehouse
Spark SQL   In‑memory Batch >10PB   Seconds   ANSI SQL           Memory Lineage     High Memory       Medium           Large‑scale ETL & iterative compute
Flink Batch Stream‑batch    1PB+   Seconds   ANSI SQL           Exactly‑once        High             High             Real‑time warehouse
</code>

OLAP Engine Comparison

<code>Engine      Single‑Table Speed   Multi‑Table Join   Real‑time Write   Concurrency   Compression
StarRocks   Fast (vectorized)    Best (various JOIN)  Seconds (PK)    High (kQPS)   High (columnar)
Doris       Moderate            Good (CBO)           Seconds (Kafka/Flink) Medium (hundreds QPS) High
ClickHouse  Ultra‑fast (wide tables) Weak (needs pre‑agg)  Batch only      Low (high per‑query cost) Highest
</code>

Considering SparkSQL’s low learning curve, existing cloud‑window integration, and ability to handle >10 PB, SparkSQL was selected as the compute engine. For OLAP, StarRocks was chosen for its superior multi‑table join performance and concurrency.

3.3 Data Warehouse Architecture Diagram

3.4 Data Processing Examples

Replace Java service calculations with SparkSQL:

<code>-- Simple order amount calculation
SELECT order_id, original_amount, shipping_fee,
       original_amount + shipping_fee AS total_amount
FROM dwd_order_detail
WHERE dt = '${biz_date}';
</code>

<code>-- Multi‑dimensional join (replaces Java RPC)
SELECT o.order_id, o.original_amount,
       CASE WHEN u.vip_level = 'PLATINUM' THEN o.original_amount * 0.9 ELSE o.original_amount END AS vip_adjusted_amount,
       COALESCE(c.coupon_amount, 0) AS coupon_deduction,
       (o.original_amount + o.shipping_fee - COALESCE(c.coupon_amount,0)) AS final_amount
FROM dwd_order_detail o
LEFT JOIN dim_user u ON o.user_id = u.user_id AND u.dt = '${biz_date}'
LEFT JOIN dim_coupon c ON o.coupon_id = c.coupon_id AND c.dt = '${biz_date}'
WHERE o.dt = '${biz_date}';
</code>

<code>-- Window function: recent 3‑order average per user
SELECT order_id, user_id, amount,
       AVG(amount) OVER (PARTITION BY user_id ORDER BY create_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_amount
FROM dwd_order_detail
WHERE dt BETWEEN date_sub('${biz_date}',30) AND '${biz_date}';
</code>

<code>-- Register and use a UDF
spark.udf.register("calculate_tax", (amount DECIMAL) -> {...});
SELECT order_id, calculate_tax(amount) FROM orders;
</code>

3.5 Issues Encountered

Data Consistency

Discrepancies between sales GMV (order creation time) and finance (payment success time) were resolved by aligning statistical granularity and adding daily validation jobs.

Data Skew

During a promotion, SKU 888 dominated the dataset, causing a single reducer bottleneck. The skew was mitigated by adding a random suffix to the skewed key and expanding the dimension table, distributing the load across 100 partitions.

<code>-- Add random suffix to skewed SKU
WITH skewed_data AS (
  SELECT order_id,
         CASE WHEN sku_id='888' THEN CONCAT(sku_id,'_',CAST(FLOOR(RAND()*100) AS INT)) ELSE sku_id END AS skewed_sku_id,
         quantity
  FROM fact_orders
),
expanded_dim AS (
  SELECT sku_id, sku_name, pos
  FROM dim_sku LATERAL VIEW EXPLODE(ARRAY_RANGE(0,100)) t AS pos
  WHERE sku_id='888'
  UNION ALL
  SELECT sku_id, sku_name, NULL AS pos
  FROM dim_sku WHERE sku_id!='888'
)
SELECT a.order_id,
       COALESCE(b.sku_name, c.sku_name) AS sku_name,
       SUM(a.quantity) AS total_qty
FROM skewed_data a
LEFT JOIN expanded_dim b ON a.skewed_sku_id = CONCAT(b.sku_id,'_',b.pos) AND b.sku_id='888'
LEFT JOIN dim_sku c ON a.skewed_sku_id = c.sku_id AND c.sku_id!='888'
GROUP BY a.order_id, COALESCE(b.sku_name, c.sku_name);
</code>

After optimization, job duration dropped from 3 hours to 25 minutes.

3.6 Architecture Comparison Results

RPC stability: eliminated, failure rate near 0.

Task reliability: automated scheduling raised success rate to 99.8% and cut ops effort by 75%.

Data accuracy: unified processing increased correctness to >99.9%.

Processing capacity: distributed computation lifted throughput 5×.

Re‑run efficiency: reduced from >4 hours to 30 minutes (87.5% improvement).

Future Outlook

To meet real‑time reporting demands, the roadmap includes:

Adopting a Lambda architecture for batch‑stream convergence.

Introducing Flink for streaming computation and Kudu for low‑latency analytics.

Applying microservice resilience patterns (e.g., Sentinel) to streaming pipelines.

Expanding financial data coverage across the group to enable predictive analytics.

Conclusion

The evolution from a microservice‑centric finance system to a big‑data‑driven warehouse demonstrates that there is no universally "best" architecture—only the one that best fits current business needs. The experience shared here aims to guide teams facing similar scalability and reliability challenges.