Mastering ODS/DWD/DWS/ADS: Clear Boundaries for Production‑Grade Data Warehouses

Data warehouses must be layered to ensure data traceability, reuse, and maintainability while avoiding redundancy and high maintenance costs.

Four‑layer core logic

01 Why layer?

Data traceability – quickly locate the layer where an error originated.

Reduce duplicate computation – each detail is processed only once.

Unified metrics – consistent processing rules per layer prevent inconsistent results.

02 Core boundaries

ODS (Operational Data Store) – raw data entry, no business logic; only minimal format conversion and sensitive‑field masking. Example fields: order_id, user_id, phone (masked), etc. Forbidden: cleaning, joins, business calculations.

DWD (Data Warehouse Detail) – standardized detail layer. Sources exclusively from ODS. Operations include deduplication, null filling, filtering invalid records, standardizing code values, necessary joins, and table splitting. Keeps the finest‑grain detail. Forbidden: aggregation.

DWS (Data Warehouse Summary) – thematic aggregation layer. Sources from DWD. Operations: group by business theme (user, order, product) and time granularity, calculate core metrics. Provides summarized data for ADS. Forbidden: complex business logic.

ADS (Application Data Service) – final layer serving BI dashboards, data screens, APIs. Primarily sources from DWS, with occasional direct DWD access for detailed reports. Operations: selective filtering, secondary metric derivation, format adaptation. Retains only fields required by applications. Forbidden: massive processing or storing redundant data.

Practical e‑commerce case

Step‑by‑step example using order data:

ODS: ingest raw order table, mask phone numbers, keep all fields.

DWD: clean duplicates, filter invalid orders, fill nulls, standardize status codes, join user and product tables, split into order_detail and order_pay tables.

DWS: aggregate by date + user, compute order count, total amount, payment count, payment amount, cancel count, etc.

ADS: filter recent 30 days, compute average order value and conversion rate, adapt schema for BI tools.

Common pitfalls and solutions

Doing cleaning or joins in ODS – keep ODS raw.

Aggregating in DWD – move aggregation to DWS.

Processing per application in DWS – keep DWS theme‑centric for reuse.

Storing redundant fields in ADS – retain only required columns.

Summary

Each layer must have a clear boundary and responsibility: ODS stores raw data, DWD standardizes details, DWS provides theme‑level summaries, and ADS delivers application‑ready data. Following these rules enables a maintainable, production‑grade data warehouse for any industry.