Offline vs Real‑Time Data Warehouse: Full Architecture Comparison

1. Core Logic of Data Warehouses

A data warehouse is an analytical data storage and processing system whose primary goal is to provide clear, easy‑to‑use, high‑quality data for reporting, analysis, and business decision‑making. Unlike operational databases that emphasize write performance and transaction consistency, data warehouses focus on read performance and historical data completeness. The first key step in building a warehouse is layering, where each layer has a single responsibility, enabling clear data processing logic, easy verification, reuse across layers, and rapid fault isolation.

2. Offline Data Warehouse – Stable Batch Processing

Offline warehouses process data in fixed‑interval batches, typically with a T+1 latency; some low‑frequency scenarios use T+7. Their architecture revolves around layered design and dimensional modeling, which determine usability and maintainability.

2.1 Layered Design

ODS layer : Entry point that ingests data from business databases, logs, crawlers, third‑party APIs using tools such as DataX or Sqoop. It stores raw snapshots without cleaning, preserving the original state for later debugging.

DWD layer : Core normalization layer that cleans, deduplicates, and formats raw data, separating it into fact and dimension tables. Data quality here directly impacts downstream accuracy.

DWS layer : Public aggregation layer that lightly aggregates DWD details into wide tables per business domain, improving query efficiency and reducing duplicate computation.

ADS layer : Application layer that transforms aggregated data into business metrics for reports (daily, weekly, monthly) used by product, operations, and analysts.

DIM layer : Stores dimension data such as time, geography, and product attributes, supporting both high‑cardinality and low‑cardinality dimensions for various join needs.

2.2 Dimensional Modeling

The mainstream modeling method is dimensional modeling, with star, snowflake, and constellation schemas. The constellation schema, which shares dimensions across multiple fact tables, is most commonly used in enterprises because it accommodates multi‑business‑theme analysis.

Star schema : Single fact table with direct dimension links; simple and high‑performance.

Snowflake schema : Extends star by normalizing dimensions; more complex and slower, rarely used.

Constellation schema : Multiple fact tables sharing dimensions; fits multi‑business scenarios and is the preferred choice for offline warehouses.

Construction follows a strict workflow: requirement analysis, data profiling, model design, ETL development, data validation, task scheduling, and production rollout, with standards such as unified fact granularity, additive metrics, and consistent naming.

3. Real‑Time Data Warehouse – Low‑Latency Stream Processing

Real‑time warehouses arise from business needs for instant dashboards, recommendations, and user‑behavior analysis, demanding latency from seconds to minutes—far higher than the T+1 of offline warehouses.

3.1 Main Architectures

Lambda architecture : Separate real‑time and batch processing pipelines; real‑time layer handles streaming data for low latency, batch layer ensures data accuracy. Merges results from both layers.

Kappa architecture : Uses a single streaming engine (e.g., Flink) for both real‑time and reprocessing via log replay, simplifying development and reducing cost.

Stream‑batch hybrid : Combines advantages of Lambda and Kappa, often built on a data lake with ACID capabilities, delivering both real‑time freshness and batch reliability.

3.2 Layered Design in Real‑Time Context

Real‑time ODS : Ingests binlog, logs, and event data via Flink CDC, Flume, Logstash into Kafka, storing data as streams.

Real‑time DWD : Performs streaming ETL with Flink, handling cleaning, standardization, dimension association, and out‑of‑order or late data.

Real‑time DWS : Uses Flink window aggregation to produce second‑ or minute‑level aggregates, writing results to real‑time OLAP stores such as ClickHouse or Doris.

Real‑time ADS : Provides metrics directly from real‑time OLAP tools for dashboards, queries, and risk control.

4. Core Differences and Selection Logic

The decisive distinction lies in data processing method and latency. Selection criteria include:

Latency requirements: T+1 favors offline; sub‑minute needs mandate real‑time.

Team technical expertise: Real‑time streaming demands higher skill; insufficient experience raises risk.

Budget and resources: Real‑time incurs higher infrastructure and personnel costs.

Data volume and query complexity: Massive data with complex queries suits offline batch; moderate volume with fixed query patterns suits real‑time.

Most companies operate both warehouses in a complementary fashion rather than replacing one with the other.

5. Emerging Trend: Unified Stream‑Batch Warehouse

To reduce duplication and cost, a unified “stream‑batch” warehouse integrates both processing modes with a single technology stack, ensuring consistent data definitions while supporting both offline and real‑time workloads. The author’s team uses FineDataLink , a tool that enables seamless offline batch integration and real‑time stream integration without switching tools, thereby lowering construction and maintenance effort.