Designing a Data Warehouse Development Management System and Process: Practical Insights

Bank Data Warehouse Definition

Bill Inmon (1991) defined a data warehouse as a subject‑oriented, integrated, non‑volatile, time‑variant collection of data that supports decision making. Core characteristics: subject‑oriented, integrated, time‑variant, decision‑support, enterprise scope, atomic detail, snapshot capture.

Bank System Landscape and Warehouse Positioning

Banking systems consist of transaction (real‑time OLTP) systems and data (OLAP) systems. Transaction systems (e.g., ATM, mobile, core banking) run 24×7 and must guarantee stability; they are unsuitable for heavy analytical workloads. Data systems aggregate data from transaction systems, perform cleaning, transformation, and provide data for operations management, risk control, marketing, and reporting. The data warehouse (or data bus) centralizes extraction, storage, processing, and distribution of data for enterprise‑wide analysis.

ODS vs. Data Warehouse

ODS (Operational Data Store) stores current, volatile, detailed data for fast (second‑level) queries. Data warehouse stores historical data for analytical queries over larger time spans. Example: counting mobile‑bank transactions per minute belongs to ODS; monthly trend analysis belongs to the warehouse. Many banks merge ODS and warehouse functions on a single platform.

Data Mart vs. Data Warehouse

Data mart is a subset of the warehouse tailored to a specific business domain (e.g., finance, marketing, risk). In banks, marts are built on top of the warehouse to serve departmental analysts.

Data Lake vs. Data Warehouse

Data lake retains raw data of all formats (structured, semi‑structured, unstructured) using low‑cost storage such as Hadoop. Data warehouse transforms data into structured relational tables for SQL‑based analysis. Banks increasingly embed Hadoop‑based lake capabilities within the warehouse architecture, while analysts still prefer structured data for business analysis.

Data Middle Platform vs. Data Warehouse

The data middle platform (originated by Alibaba) unifies data collection, computation, storage, and standardization across many business systems, reducing siloed development and enabling reusable data assets. Banks are adopting similar concepts but lag behind internet companies in data‑driven product development and AI adoption.

Data Warehouse Development Process

1. Standards First – Naming conventions for ETL jobs, tables, fields, scripts; ETL development guidelines; data model design and maintenance rules (e.g., primary‑key consistency). Standards enable tracking of job critical paths, table counts, storage usage, and impact of source changes.

2. Development Workflow

Requirement analysis – define interface fields for data marts and the warehouse.

Model development and maintenance – verify existing logical/physical models satisfy interface needs; extend source tables or adjust models as required.

ETL development – write and test extraction, transformation, and loading scripts; validate results in production.

3. Roles and Responsibilities

Model team – performs requirement analysis and maintains logical/physical models.

ETL developers – implement code and maintain jobs.

Testers – validate ETL scripts and data quality.

Data Warehouse Development Management System

Key capabilities to enforce standards and streamline the workflow:

Data model maintenance – Manage source‑system research, logical models (e.g., ERWIN, PowerDesign), and automatically generate physical models with indexes, compression, and partitioning.

ETL job configuration and code generation – Store data mapping documents, generate transformation scripts, manage collection and loading jobs, and produce scheduler scripts integrated with the scheduler.

Test environment and version‑control integration – Place generated ETL scripts in version‑control repositories, automate deployment to test environments, and ensure consistency between code and production.

Integrating these functions improves development efficiency, code quality, and maintainability across multiple teams and vendor tools.

Conclusion

Effective data warehouse development in banking requires a clear architectural role, disciplined standards, well‑defined processes, and supportive management tooling to handle large‑scale, evolving data while ensuring accuracy and stability for enterprise analytics.