Data Warehouse vs Data Lake vs Lambda/Kappa: Choosing the Right Architecture

OLTP vs OLAP

Goal – OLTP completes daily transactions; OLAP analyzes historical data to support decisions.

Users – OLTP serves customers and operators; OLAP serves analysts and managers.

Data – OLTP stores current, frequently‑changing rows; OLAP stores integrated, historical snapshots.

Operations – OLTP handles high‑frequency small transactions; OLAP runs low‑frequency, complex scans.

Design – OLTP is application‑centric; OLAP is analysis‑centric.

Example – OLTP: place order, deduct inventory; OLAP: region sales trends, user profiling.

Running a five‑year full‑table scan on the order database would consume CPU, memory and I/O, compete with online transactions, and produce inconsistent metrics because source systems use different codes, units and timestamps. Therefore analytical workloads belong in a separate OLAP system.

Data Warehouse Fundamentals

Subject‑oriented – organize data by analysis topics such as sales, users, products, supply‑chain.

Integrated – standardize codes, units and timestamps across systems.

Stable (non‑volatile) – data is primarily read‑only; new rows are appended or versioned rather than updated per transaction.

Time‑variant – preserve historical states to answer questions like “what was the user’s level last year?”.

Typical warehouse layers map raw ingestion to cleaned detail, aggregated summaries, and application‑ready tables (ODS → DWD → DWS → ADS).

ETL Process

Extract:   MySQL orders, Oracle membership, CSV store files, app logs, third‑party payment API, Kafka event stream

Transform: deduplication, missing‑value handling, date/amount standardization, code mapping, test‑order filtering, PII masking, multi‑source joins, metric calculation

Load:      Fact tables, dimension tables, summary tables, BI‑oriented application tables

ETL (or ELT) turns heterogeneous, low‑quality source data into a clean, reusable analytical dataset.

Dimensional Modeling

The first step is to define the grain, e.g., “one row = one order‑item”. A sample fact row:

DateKey  StoreKey  ProductKey  UserKey  Quantity  SalesAmount
20260728  S001      P100       U008     2         30

Dimension tables store descriptive attributes such as:

Date – day, week, month, quarter, holiday

Product – name, brand, category, specification

Store – city, province, region

User – age group, membership level, region

Star vs Snowflake Schemas

Star Schema:
    DateDim
        |
UserDim — FactTable — ProductDim
        |
    StoreDim

Characteristics: simple, few joins, easy reporting, some redundancy.

Snowflake Schema:
    FactTable
      ├─ DateDim
      ├─ UserDim
      ├─ ProductDim
      │    ├─ BrandDim
      │    └─ CategoryDim
      └─ StoreDim
           └─ CityDim
                └─ RegionDim

Characteristics: less redundancy, more normalized, more joins, higher query complexity.

OLAP Operations

Roll‑up : aggregate from day → month → quarter → year.

Drill‑down : expand from country → region → city → store.

Slice : fix one dimension value (e.g., July 2026) and view the remaining two‑dimensional cube.

Dice (cut block): select ranges on multiple dimensions (e.g., July‑August, East‑South, beverages & snacks).

Pivot (rotate): swap rows and columns for different presentation.

Data Warehouse vs Data Lake

Data State – Warehouse: cleaned, integrated, modeled; Lake: raw or lightly processed.

Data Types – Warehouse: primarily structured for analysis; Lake: structured, semi‑structured, unstructured.

Schema – Warehouse: schema‑on‑write; Lake: schema‑on‑read.

Main Users – Warehouse: BI analysts, managers; Lake: data engineers, data scientists, algorithm teams.

Primary Use – Warehouse: stable reports, unified metrics, OLAP; Lake: exploration, machine learning, raw retention, re‑computation.

Key Risks – Warehouse: modeling effort, change cost; Lake: data swamp without governance.

Lakehouse (湖仓一体) aims to combine the low‑cost, flexible storage of a lake with the governance, transaction support and query performance of a warehouse.

Lambda Architecture

Three layers address both accurate historical results and low‑latency updates:

Batch Layer : processes the entire immutable history and produces a Batch View (accurate but slow).

Speed (Accelerate) Layer : consumes recent increments and produces a Real‑time View (fast, may be approximate).

Serving Layer : merges the two views to answer queries; when a new batch run finishes, it overwrites the overlapping real‑time portion.

Example numbers: after midnight the batch view shows 10 M ¥ sales; the speed layer adds 0.3 M ¥ from the first 10 hours, yielding a query result of 10.3 M ¥. A later batch run may correct the 0.3 M ¥ to 0.29 M ¥, producing a new batch view of 10.29 M ¥.

Kappa Architecture

Kappa removes the dedicated batch chain and keeps a single stream processing pipeline. All events are persisted in an ordered log (e.g., Kafka, HDFS). Real‑time processing updates the result view. When business logic changes, the same pipeline replays the stored events to rebuild a new view, then switches queries to the new view.

Key steps for a rule change:

Deploy a new stream job with the updated rule.

Replay historical events from the log start (or from a checkpoint).

Write results to a new view.

Let the new job catch up with live events.

Switch queries to the new view.

Retire the old job and view.

Lambda vs Kappa Comparison

Processing Chains – Lambda: batch + real‑time; Kappa: single stream.

Historical Re‑computation – Lambda: full batch scan; Kappa: event replay to stream.

Real‑time Result Source – Lambda: speed layer; Kappa: same stream.

Code Base – Lambda: two sets (batch & stream); Kappa: one set.

Complexity – Lambda: higher; Kappa: lower.

Historical Analysis Strength – Lambda: strong; Kappa: limited by replay throughput.

Typical Scenarios – Lambda: complex historical + real‑time coexistence; Kappa: continuous event streams with unified logic.

Choosing the Right Architecture for the Retail Case

Requirements: accurate daily reports, sub‑second promotion dashboards, five‑year raw retention, occasional two‑year re‑computation. Because historical batch analysis and real‑time logic differ significantly, the recommended design adopts a Lambda architecture:

OLTP databases handle order, payment and inventory updates.

CDC/Kafka streams feed all source data into an ETL pipeline.

Cleaned data loads into a warehouse (ODS → DWD → DWS → ADS) with a star‑schema fact table.

Raw logs, images and events land in a data lake for machine‑learning and audit.

Lambda batch layer generates the nightly Batch View for the “yesterday report”.

Lambda speed layer updates a Real‑time View every few seconds for the promotion screen and anomaly detection.

Serving layer merges both views for queries.

If future workloads become purely event‑driven with stable logic, a migration to Kappa could reduce maintenance overhead.

Key Takeaways

Separate OLTP (transaction) from OLAP (analysis) to avoid resource contention.

ETL must extract, transform and load to achieve a unified, trustworthy analytical dataset.

Dimensional modeling (grain, fact, dimensions) structures data for multi‑dimensional queries.

Star schemas favor simplicity; snowflake schemas favor normalization.

Data warehouses provide stable, integrated, historical data; data lakes preserve raw, diverse data.

Lambda offers strong batch accuracy plus low‑latency increments; Kappa simplifies code but relies on efficient event replay.

Select Lambda when batch and real‑time requirements differ; choose Kappa when a single stream can satisfy both.