Why Does a Data Warehouse Need Layering? Understanding ODS, DWD, DWS, and ADS

Many companies start data analysis by connecting directly to business systems for reporting. While this works quickly at first, over time it leads to inconsistent sales figures across departments, differing customer identifiers, and a proliferation of messy reports.

Three long‑term problems arise when reporting directly from operational databases:

Business systems are not designed for analysis. Their primary goal is to support transactions such as order placement, shipping, invoicing, and approval, not to provide a stable analytical view.

Direct queries impact system performance. Heavy reporting workloads—especially large aggregations and multi‑table joins—can slow down the production system.

Metric definitions become uncontrolled. Different teams extract data independently, leading to disputes over where a number comes from.

Data‑warehouse layering addresses these issues by separating raw ingestion, cleaning, aggregation, and application layers.

ODS – Operational Data Store (Raw Data Layer)

The ODS layer synchronizes data from source systems (order, CRM, ERP, finance, e‑commerce, etc.) and retains the original structure as closely as possible. It acts like a “raw archive” of enterprise data, allowing later traceability back to the source records. Tools such as FineDataLink can be used to establish stable, scheduled data‑ingestion pipelines.

DWD – Data Warehouse Detail (Detail Data Layer)

DWD cleans, standardizes, and integrates the ODS data to produce a unified set of detailed records. It resolves nulls, duplicates, inconsistent formats, and mismatched codes (e.g., customer IDs across systems). The result is a “standard order detail” table that includes consistent fields for order amount, status, dates, and related dimensions.

DWS – Data Warehouse Summary (Summary Data Layer)

DWS aggregates the standardized detail data by business subjects (customer, product, channel, region, etc.) to create reusable metric tables. For example, a “monthly sales” metric is calculated once in DWS and then referenced by multiple downstream reports, ensuring a single, consistent definition.

ADS – Application Data Service (Application Data Layer)

ADS tailors the aggregated data to specific business scenarios, producing structures that directly support dashboards, BI tools, and decision‑making applications. It delivers ready‑to‑use data for sales dashboards, financial profit analysis, inventory turnover, and customer segmentation without requiring end users to understand underlying tables.

In practice, a single order flows through the layers as follows:

ODS: the raw order record as stored in the source system.

DWD: a cleaned, standardized order‑detail record linked to customer, product, and region dimensions.

DWS: the order contributes to aggregated metrics such as total sales, customer contribution, and product volume.

ADS: the aggregated metrics are presented in business dashboards and decision‑support reports.

Layering solves five key problems:

Data chaos: Unified standards for customers, products, and organizations emerge after ODS → DWD processing.

Metric inconsistency: Public metrics in DWS keep calculations identical across reports.

Duplicate development: Reusable cleaned data and aggregated metrics reduce the need to rebuild pipelines for each new report.

Performance: Pre‑aggregated data in DWS and scenario‑specific structures in ADS avoid heavy scans of raw detail tables.

Traceability: Any questionable figure can be traced back step‑by‑step from ADS → DWS → DWD → ODS.

The final takeaway is that a well‑designed data warehouse is not about adding more tables; it is about giving data a clear path, consistent rules, reusable assets, and reliable outputs for business decision‑making.