A Comprehensive Panorama of Data Modeling for Big Data Systems

Why Data Modeling Is Needed

Organizing and storing data in a structured way is a core challenge; a good data model balances performance, cost, and efficiency, reduces redundancy, improves user experience, and ensures consistent statistical calculations.

Relational DB vs. Data Warehouse

OLTP systems focus on random read/write operations and use 3NF entity‑relationship models to guarantee consistency, while OLAP systems handle batch reads, prioritize data integration and complex query performance, and therefore require different modeling techniques.

Typical Data‑Warehouse Modeling Methodologies

ER Model : Integrates data across systems by topic, exemplified by Teradata’s FS‑LDM for financial services.

Dimensional Model : Built from analysis‑driven requirements; uses star or snowflake schemas. Design steps include selecting business processes, granularity, identifying dimension tables, and choosing facts.

Data Vault Model : Emphasizes an auditable base layer with history, traceability, and atomicity, using subject‑oriented structures and additional normalization.

Anchor Model : Extends Data Vault to 6NF, making extensions additive rather than mutative, resulting in a key‑value‑style schema.

Alibaba’s Data‑Modeling Practice Overview

Stage 1: Built on Oracle for reporting‑only needs.

Stage 2: Adopted Greenplum MPP with ODL/BDL/IDL/ADL layers; attempted ER modeling but faced rapid business changes and knowledge gaps, leading to high risk.

Stage 3: Leveraged Hadoop and MaxCompute, adopting Kimball’s dimensional modeling to create a public‑layer data architecture.

The public‑layer aims to handle a 2.5× annual data growth, using the “OneData” system for unified metric definitions, model design, and tooling.

OneData Implementation Process

Conduct thorough business research and requirement analysis.

Design overall data architecture by defining data domains and building a bus matrix that maps business processes to dimensions.

Abstract reporting needs into metric systems and use OneData tools for metric definition and model design.

Develop code and operate the pipeline.

Dimension Design

Basic Concepts

Facts represent measures; dimensions describe the environment (e.g., buyer, seller, product, time). Dimension attributes are columns used for grouping, filtering, and labeling.

Design Steps

Select or create a dimension, ensuring uniqueness.

Identify the primary dimension table (often the ODS table).

Determine related dimension tables and their relationships.

Choose dimension attributes from primary and related tables.

Guidelines: generate rich attributes, provide meaningful textual descriptions, and distinguish numeric attributes from facts.

Advanced Topics

Dimension Integration : unify naming, field types, and code values across applications; use vertical (same domain, different source) or horizontal (different domains) integration patterns.

Horizontal/Vertical Splitting : decide between separate dimension tables per classification (Scheme 1) or a single table containing all attributes (Scheme 2) based on change frequency and query patterns.

Slowly Changing Dimensions : three handling methods – overwrite, insert new rows, or add columns.

Snapshot Dimensions : Alibaba stores daily full snapshots instead of proxy keys to simplify queries, accepting storage overhead.

Extreme Storage : combines historical lag‑based tables with view‑level rewrites to hide storage details.

Micro‑Dimensions : isolate volatile attributes into separate tables with surrogate keys; Alibaba avoids this due to combinatorial explosion.

Special Dimensions : recursive hierarchies (balanced vs. unbalanced), behavior dimensions derived from facts, multi‑value dimensions, and miscellaneous flag dimensions.

Fact‑Table Design

Fundamentals

Facts capture business process measures and are linked to dimensions. Granularity defines the detail level of each row. Facts can be additive, semi‑additive, or non‑additive. Degenerated dimensions store dimension attributes directly in the fact table.

Fact‑Table Types

Transaction Fact Table : records atomic events (e.g., order creation, payment, shipment).

Periodic Snapshot Fact Table : samples state at regular intervals (e.g., daily balances).

Cumulative Snapshot Fact Table : tracks lifecycle milestones and time intervals between processes.

Design Principles

Include all measures related to the business process.

Only select measures that belong to the process.

Decompose non‑additive measures into additive components.

Declare granularity before choosing dimensions and measures.

Maintain a single granularity per fact table.

Keep units consistent across measures.

Handle NULLs (e.g., replace with zero).

Use degenerated dimensions to improve usability.

Four‑Step Fact‑Table Design Process

Select business process and fact‑table type.

Declare granularity (e.g., sub‑order level).

Determine dimensions that describe the environment (buyer, seller, product, etc.).

Identify facts that measure the process, ensuring they match the declared granularity.

Optionally add redundant dimension attributes for downstream efficiency.

Transaction Fact Table Variants

Single‑Process Fact Table : one table per business process (e.g., 1688 order‑placement fact).

Multi‑Process Fact Table : combines several processes; either separate columns per process or a shared column with a process label.

Choosing between them depends on process similarity, shared granularity, and downstream user clarity.

Periodic Snapshot Fact Table

Captures state metrics (e.g., account balance) at fixed intervals; may include prior‑period values for comparative analysis.

Cumulative Snapshot Fact Table

Records timestamps for key milestones (e.g., order creation → payment → shipment) and computes intervals; handles non‑linear and multi‑source processes with business‑driven rules for first vs. last occurrence.

Aggregation Fact Tables (DWS)

Provide pre‑aggregated results for common queries (e.g., daily seller sales, monthly category totals). Principles: ensure consistency with detail tables, avoid mixing aggregation levels in one table, and choose aggregation dimensions and measures deliberately.

Implementation Details

Use date‑partitioned full tables for cumulative snapshots, optionally with a far‑future partition for ongoing records.

Apply “OneData” naming conventions to indicate statistical periods (1d, nd, td).

In summary, the article provides a step‑by‑step analytical framework for building robust data models and fact tables in large‑scale data‑warehouse environments, illustrating each decision with concrete Alibaba examples and visual diagrams.