A Complete Guide to Data Warehouse Layering: ODS, DWD, DIM, DWS, and More

Why Layer Data?

Data layering aims to achieve ordered and controllable data. By progressively cleaning, structuring, aggregating, and optimizing raw data, scattered and inconsistent source information becomes clean, standardized, and easy‑to‑use data products.

Typical problems include user information scattered across core, CRM, and marketing systems with inconsistent field names and definitions, leading to duplicated logic and difficulty tracing issues. The four core values of layering are:

Clear structure: each layer has a defined responsibility boundary.

Traceable lineage: issues can be quickly located to a specific layer and table.

Reduced duplicate computation: shared intermediate data is computed once.

Shielding source complexity: changes in source systems are absorbed within the warehouse.

ODS – Operational Data Store (Source Layer)

ODS synchronizes raw source data without alteration, preserving the source schema and finest grain. Basic processing includes filtering malformed records, discarding rows with null key fields, unifying time formats, and applying basic naming conventions.

Storage strategies:

Incremental storage – store only daily new or changed records, partitioned by date (suitable for high‑volume, frequently updated transactional data).

Full storage – store a full snapshot each day (suitable for low‑volume, slowly changing dimension data).

Link (slowly changing) storage – add start and end timestamps to record each change history (useful for tracking user status changes).

Typical retention is 3–6 months, longer if data volume is small. Tools such as FineDataLink can help integrate multiple source systems to meet ODS’s multi‑source ingestion needs.

DWD – Data Warehouse Detail (Detail Layer)

DWD is the most intensive data‑governance layer, driven by business processes to build fine‑grained fact tables.

Data cleaning – remove nulls, dirty data, abnormal enum values, out‑of‑range records (e.g., orders with empty ID or negative amounts).

Data standardization – unify formats across sources (e.g., boolean 0/1 vs true/false, timestamps vs strings).

Data masking – mask sensitive fields such as phone numbers, ID numbers, and bank cards.

Dimension degeneration – denormalize high‑cardinality dimensions directly into fact tables to reduce join overhead (e.g., embed order ID dimension into the fact table).

Each fact record represents a concrete business event (order, payment, refund) with measurable metrics (order amount, count, etc.). The grain matches ODS, but data quality is significantly higher. FineDataLink provides built‑in cleaning and transformation functions to support these tasks.

DIM – Dimension Layer

DIM stores descriptive information that provides analytical axes and coordinates, such as geographic hierarchies, date hierarchies, or product attributes.

Two categories:

High‑cardinality dimensions – large tables (millions to billions of rows) like user or product master data.

Low‑cardinality dimensions – small tables (thousands of rows) like enumeration mappings or date tables.

Design principle: wide tables that keep related attributes together, minimizing multi‑table joins. Updates should be infrequent; slowly changing dimensions are handled with link tables.

DWS – Data Warehouse Service (Service Layer)

DWS builds on DWD and performs light aggregation by analysis theme, typically on a daily basis. Each theme (user, product, order, traffic, logistics, etc.) results in one to three wide tables containing daily metrics.

Example: a user‑topic DWS wide table aggregates a user’s daily login count, order count, order amount, payment count, payment amount, add‑to‑cart count, etc., all in a single row representing one day’s behavior.

Wide tables often contain 60–200 columns, allowing downstream analysts to obtain results by simple aggregation without complex joins.

DWT – Data Warehouse Topic (Topic Layer)

DWT extends DWS by performing cumulative aggregation over time. It builds full‑width tables where each row represents the cumulative behavior of an entity from its inception to the present.

Example: a user‑topic DWT wide table records total order count, total payment amount, total login days, etc., accumulated since registration.

The key distinction from DWS is the time dimension: DWS provides a snapshot for a single day, while DWT provides a running total up to the current date. Modeling in DWT is driven by analysis needs rather than business process.

ADS – Application Data Service (Application Layer)

ADS is the topmost layer, delivering highly aggregated data tailored to specific business scenarios such as daily active users, retention rates, product repurchase rankings, or risk‑user lists.

Data is often loaded into MySQL, Redis, Elasticsearch, or similar stores for online service calls, or into BI tools for analyst consumption. Although the volume is relatively small, each table is purpose‑built for a concrete use case.

Layer Calling Conventions

Data flow must follow a strict direction: lower layers feed higher layers, and reverse calls are prohibited. Violating this rule breaks data lineage, making maintenance and troubleshooting difficult.

FineDataLink offers visual task management and intelligent scheduling to enforce these conventions, making lineage traceable and reducing duplicate development.

Practical Considerations

Implementing layering reveals real‑world challenges, such as where to draw the boundary between ODS and DWD. Different organizations adopt different practices; the key is to establish consistent team standards.

More layers increase development, scheduling, and maintenance costs. A three‑layer baseline (ODS, DWD, DIM) is common; further subdivision should be based on business volume and team capability.

The essence of data warehouse layering is to manage data complexity in a structured way, ensuring clear data flow and a reliable foundation for analysis.