Data warehouse (DWH)

A data warehouse (Data Warehouse, DWH) is a specialized database or information system designed for centralized collection, integration, cleaning, storage and analysis of large volumes of historical data from many heterogeneous sources (OLTP transactional systems, CRM, ERP, HRM, external sources, web analytics, mobile applications). Unlike operational databases optimized for fast writing and updating of individual records (OLTP — Online Transaction Processing), a data warehouse is optimized for complex analytical queries (OLAP — Online Analytical Processing) that are executed over large volumes of historical data, often with aggregations, groupings and joins of many tables. The key characteristics of a data warehouse according to Inmon: subject orientation (data is organized by subject areas, not by applications), integration (data from different sources is brought to a unified format, codes and units of measurement), non-volatility (data after loading does not change — only new slices are added) and chronology support (the history of changes for the entire period is preserved). The scaling technology plays a key role in the design of modern data warehouses, allowing scaling of capacity and performance as the volume of information grows.

Architecture and components of a data warehouse

The classic architecture of a data warehouse includes several levels. The data source layer (Source Layer): relational DBMS (Postgres Pro, Oracle, MS SQL), flat files (CSV, Excel, JSON, XML), streaming data (Kafka, Kinesis), APIs of external systems (REST, SOAP), web scraping and mobile SDKs. The ETL/ELT level (Extract, Transform, Load / Extract, Load, Transform): tools for extracting, cleaning, transforming, enriching and loading data into the warehouse (Apache Airflow, Talend, Pentaho, Informatica, SSIS, dbt). The warehouse core level: the Stage zone (raw, untransformed data in the original format), the ODS zone (Operational Data Store — an operational storage for fresh data), the Core zone (the core — a normalized or dimensional data model: Data Vault 2.0, 3NF, Inmon, Kimball), the Data Marts zone (data marts — subsets of data optimized for specific business tasks: finance, sales, procurement, marketing, HR). The access level (Access/Consumption Layer): business intelligence tools (BI: Tableau, Power BI, Qlik, Yandex DataLens, Luxms BI), report building, dashboards, ad-hoc queries (SQL), predictive analytics (Python/R, machine learning). Modern data warehouses are increasingly built using the Lakehouse concept, which combines the flexibility of data lakes (Data Lake) for storing unstructured and semi-structured data and the power of structured warehouses with ACID transactions.

Data warehouse construction methodologies

There are various DWH construction methodologies. The Kimball model (Ralph Kimball, dimensional modeling): building data marts for individual business processes using a "star" schema (star schema: a fact table + dimension tables) or a "snowflake" schema (snowflake schema). Advantages: fast development, comprehensibility for business users, high query performance. The Inmon model (Bill Inmon, Corporate Information Factory — CIF): starts with building a normalized corporate warehouse (Enterprise DWH, 3NF), on the basis of which data marts are then built by subject areas. Advantages: a single source of truth, no data duplication. The Data Vault model (Dan Linstedt): a hybrid approach with the division of entities into Hub (business keys), Link (connections between keys) and Satellite (attributes and context). Advantages: high flexibility to source changes, full traceability of data lineage, parallel loading. The choice of methodology depends on business requirements, source complexity, available team competencies and the project budget. For Russian companies working with state information systems, an important requirement is compliance with personal data legislation and the requirement to store data on the territory of the Russian Federation.

Business value of a data warehouse

A data warehouse is the foundation for corporate analytics and data-driven decision making. Without a high-quality DWH, it is impossible to build reliable and timely reports, predictive models and dashboards for management. The implementation of a DWH allows a company to move from reactive management based on intuition and belated reports to proactive management based on the analysis of historical data and trend identification. The ROI from a DWH implementation can reach hundreds of percent by identifying hidden efficiency reserves, optimizing the assortment, reducing inventory, improving customer service, detecting fraudulent operations and optimizing marketing campaigns. Modern BI tools connect to the data warehouse, providing business users with the ability to independently (self-service BI) build dashboards and reports without the participation of IT specialists. The specialists of the Fintech company have experience in building data warehouses of varying complexity, ensuring high performance, reliability, scalability and full compliance with regulatory requirements.