Lanzhou Bank Data Warehouse MPP Distributed Database Replacement Project

The existing data platform at Bank of Lanzhou serves as the foundational data integration and service layer for regulatory reporting, internal reports, and various application systems. Currently, it faces challenges including inefficient batch data processing, inadequate data timeliness for downstream applications, and insufficient capabilities in real-time data services, model development, and unified data management. To address these issues, we are adopting an industry-leading technical architecture to build a data warehouse that integrates and processes data, achieves bank-wide unified data management, improves data processing efficiency, and provides consistent data services for all application systems while enforcing data standards through the data governance platform. The key challenges and urgent needs at the database level for our data warehouse are as follows:

Scalability: The legacy transactional database runs on an all-in-one system with limited capacity. As data volume grows, expanding capacity on the all-in-one system is difficult and costly. The new MPP distributed database must offer easy scalability, enabling online expansion of both compute and storage resources.

Large-scale statistical performance: With continuously increasing data volumes, the transactional database is experiencing degraded performance, making it hard to support complex queries on large tables and ensure efficient statistical analysis for data warehousing workloads. The new MPP distributed database must deliver high-performance capabilities for large-table joins, grouping, aggregation, OLAP, and statistical analysis on massive datasets.

Data redundancy: The existing transactional database relies on centralized shared storage with only a single copy of data. Backups are performed via traditional tape libraries, which take an extremely long time to back up and restore large datasets, preventing rapid recovery in the event of failures. The new MPP distributed database must support multi-copy data replication technology, leveraging distributed storage to enhance high availability.

Operational efficiency: As data volumes rapidly increase, the legacy transactional database has become increasingly difficult to operate and maintain, requiring significant effort to handle faults and performance issues. The new MPP distributed database should feature an intuitive interface, provide graphical O&M tools, and simplify troubleshooting and fault resolution to reduce operational complexity. The goal of the data warehouse MPP distributed database project is to leverage the bank’s foundational network resources, integrate distributed storage and computing technologies, consolidate and optimize diverse information assets, strengthen information sharing and management, and build a secure, controllable information resource framework that provides information service support for application development. Once completed, the MPP distributed data warehouse will be capable of storing massive volumes of heterogeneous and multi-structured data, offer unified management of all data resources, and deliver diverse computing and service support for data warehouse applications.

The primary database cluster consists of 3 coordinator nodes and 10 compute nodes, deployed across 10 servers running GBase 8a MPP Cluster. Each server is configured with two bonded 10 Gigabit Ethernet NICs for high availability, connecting to two 10 Gigabit switches to enable high-speed interserver data exchange within the GBase 8a MPP Cluster. Additionally, each server has two bonded Gigabit Ethernet NICs connected to two Gigabit switches, also providing high availability, for data exchange between the GBase 8a MPP Cluster and external nodes such as application servers and monitoring systems. The typical system architecture is described below.

Data Source Layer: the bank’s existing business systems.

Extraction and Loading Layer: uses ETL tools to extract, load, and transform massive volumes of data from source systems.

Storage Management Layer: after cleansing, the loading server distributes data to each node based on predefined rules, building the primary data warehouse and various data marts. The scale of each mart depends on the business domain.

Analytics and Presentation Layer: the bank employs third-party analysis and mining tools to retrieve data from the data warehouse or data marts for further analysis and feeds the results into corresponding business modules.

Application Portal Layer: the bank’s internal and external systems use middleware to organize the required data from each module and present it through the portal.

The Bank of Lanzhou data warehouse is designed to unify the storage, management, sharing, and service of massive data, supporting various applications. It establishes specialized topics for different business lines, forming a complete architecture for data collection, loading, storage, analysis, and application presentation.

The Bank of Lanzhou data warehouse primarily encompasses the storage management layer and the extraction and loading layer. Its core functional components include four parts: the pre-processing system, the ETL platform, the data warehouse, and the unified data service interface.

• Pre-processing system: temporarily stores all collected data in the original source format, insulating source systems from interference and preparing data for verification and ETL processing.

• ETL platform: performs efficient data extraction, cleansing, transformation, validation, and loading, moving massive data from source systems to the target data warehouse.

• Data warehouse: stores and analyzes data within the platform, supporting upper-layer business requirements such as querying, statistics, and reporting.

• Unified data service interface: a high-performance service layer that delivers unified data services to upper-layer applications, meeting needs such as data querying, data interoperability, data exchange, data analysis, directory services, compound queries, and information comparison.

To ensure system performance while enabling data sharing and analysis, data within the storage management layer is organized into three tiers: the operational data layer (ODS), the data warehouse layer (DW), and the data mart layer (DM). The MPP database typically operates within the DW and DM layers, as illustrated in the following diagram.

Data Warehouse and Data Mart Diagram

Implementation

Bank of Lanzhou's data warehouse system officially went live in July 2023. As business systems continued to expand, data storage demand surged rapidly. The current database cluster consists of 10 nodes, with each data node storing 2.4TB × 14 = 33.6TB (approximately 31TB usable), bringing the total data volume to 248TB. The cluster's storage utilization has already exceeded 80%. In February 2024, a capacity expansion request was initiated, planning to add 10 more nodes and scale total capacity up to 1.2PB. The project has been awarded and the expansion will be implemented shortly.

Benefits

• High scalability: Replaced a foreign database appliance, eliminating scalability challenges and high costs; enables online scaling of both compute and storage capacity.

• High performance: Substantially improved performance for large-table join queries, data grouping, aggregation, OLAP, and statistical analysis on massive datasets.

• High availability: Utilizes active-active clustering, multi-copy data replication, and distributed MPP technologies to enhance database reliability and availability.

• Easy maintenance: Offers a user-friendly interface with graphical management tools, simplifying troubleshooting and reducing operational complexity.