Redesigning Data Warehouse Models: When and How to Use Dimensional Modeling

What Is a Data Model

A data model abstracts the static characteristics, dynamic behavior, and constraints of a system, providing a logical framework for representing and operating on data in a database. In a data‑warehouse context the model defines how business data are organized, stored, and accessed for analytical purposes.

Why Model Reconstruction Is Needed

Data duplication : Rapid response to business demands often leads to siloed, tightly coupled development, wasting storage and compute resources.

Missing business processes : Fast‑changing business logic leaves the existing model outdated, unable to satisfy current analytical needs.

Inconsistent data standards : Different contributors define the same metric or field differently, causing divergent meanings across tables.

Data non‑traceability : Lack of snapshot or versioning makes it impossible to compare data over time, hindering audit and debugging.

Data‑Warehouse Modeling Methods

Two mainstream approaches are used:

Normative (relational) modeling

Based on Inmon’s top‑down methodology, it models entities and relationships to describe the enterprise architecture and enforces 3NF. The focus is on data integration, consistency, and governance, making it suitable for stable business environments.

Dimensional modeling

Kimball’s bottom‑up method starts from analytical decision requirements. It builds star or snowflake schemas that deliver fast query performance for large‑scale, complex analytical workloads and support rapid fulfillment of new analysis requests.

Model Reconstruction Process

The reconstruction consists of three phases: information research, model design, and model implementation.

1. Information Research

Collect existing model artifacts to ensure compatibility with current usage and to capture user data requirements.

Conduct business research to clarify data construction needs, scope, and boundaries.

Gather analyst requirements to understand present and future model consumption scenarios.

Perform data‑sniffing on source systems to map data details and detect anomalies caused by evolving business models.

2. Model Design

Data‑warehouse layering : ODS (source layer), DWD/DIM (model layer), DWS (aggregation layer), ADS (application layer). The model layer handles cleaning, standardization, and dimension degradation to isolate downstream applications from upstream changes.

Warehouse standards (full‑process constraints):

NULL handling

Unit standardization

Enum/value mapping

Field format rules

Storage conventions

Script development guidelines

Table and column naming rules (lowercase, underscores, avoid SQL keywords, suffixes such as _cnt, _time, etc.)

Naming conventions per layer :

ODS layer: ods_{source_db}_{source_table}_[partition] DIM layer: dim_{business_line}_{subject}_{dimension}_[partition] DWD layer: dwd_{business_line}_{subject}_{process}_[partition] DWS layer:

dws_{business_line}_{subject}_{dimension}_{process}_[partition]

Modeling workflow :

Select business process : Identify the core process (e.g., order transaction, contract signing) that drives the fact table.

Declare grain : Define the level of detail for each fact row (e.g., one order = one grain, one order‑item = one grain).

Identify dimensions : Choose attributes that describe the context (region, organization, time, method, etc.).

Identify facts : Determine measurable metrics (payment amount, freight cost, etc.).

Add redundant dimensions : Embed high‑frequency downstream attributes (supplier name, payment method, product brand) directly in the fact table to reduce join complexity.

3. Model Implementation

Model development : Materialize the physical tables, monitor performance, and iteratively refine the design based on feedback.

Task deployment : Configure job dependencies, add data‑quality monitoring, and ensure reliable output.

Metadata maintenance : Keep documentation and lineage information up‑to‑date to accelerate onboarding and downstream propagation.

Replace legacy model : Compare new and old data, resolve inconsistencies, and retire the old model only after downstream consumers have migrated.

Conclusion

By adopting dimensional modeling together with a layered warehouse architecture, the rebuilt model (star‑schema detail layer plus multi‑dimensional aggregates) enriches data, clarifies management, and satisfies both ad‑hoc queries and complex analytical workloads. The optimal data model is not a single fixed solution; it must align with the organization’s analytical scenarios and evolve as business requirements change.