Master DWD, DWS, and Wide‑Table Modeling for Scalable Data Warehouses

Model Positioning

DWD Layer (Data Warehouse Detail) – The most granular fact‑detail layer that retains atomic business events. Characteristics include keeping raw fields, applying dimension degradation (e.g., joining user_id to user_name), using star/snowflake dimension modeling, and a data grain equal to the original log or transaction (one order, one click). Goal: provide a clean, consistent, reusable detail data foundation for upper layers.

DWS Layer (Data Warehouse Summary) – A lightly or heavily aggregated public summary layer oriented to analysis topics. It builds wide tables per domain (user behavior, transaction, product, traffic) with aggregation grain of "dimension combination + time window" (e.g., user+day, shop+week). Emphasis is on reusability and query performance. Goal: support reports, BI, ad‑hoc queries, metric platforms and avoid duplicate calculations.

Wide‑Table Driven – Uses a wide table as the core deliverable, pre‑joining and pre‑aggregating facts, dimensions, and derived metrics into a highly cohesive data service unit. Advantages: simple single‑table queries, high performance (fewer joins, suitable for OLAP engines), and unified semantic definitions.

Design Principles

Single Responsibility : DWD handles only cleaning and standardization; DWS handles only aggregation and wide‑table construction, without mixing business logic.

Consistency : Dimension degradation, metric definitions, and time partitions must be globally unified (e.g., a consistent definition of "active user").

Reuse First : DWS wide tables should cover more than 80% of common scenarios to avoid siloed development.

Light Aggregation : DWS should not over‑pre‑compute full historical aggregates, preserving flexibility.

Dimension Degradation : Redundant common dimension fields in DWD/DWS to reduce join overhead.

Lifecycle Management : Retain DWD data for 1–3 years; retain DWS data on demand (typically 30–180 days).

Model Design Example – E‑Commerce User Behavior

DWD Example (dwd_user_action_di)

-- Table: dwd_user_action_di (partition: dt)
user_id          STRING,
session_id       STRING,
action_type      STRING,   -- click / cart / buy
item_id          STRING,
category_id      STRING,
brand_id         STRING,
action_time      TIMESTAMP,
-- Dimension degradation
user_gender      STRING,
user_city        STRING,
item_name        STRING,
category_name    STRING,
brand_name       STRING

Note: Dimension degradation flattens user, item, and category information into the detail layer.

DWS Example (dws_user_daily_agg)

-- Table: dws_user_daily_agg (partition: dt)
user_id          STRING,
dt               STRING,   -- date
gender           STRING,
city             STRING,
click_cnt        BIGINT,
cart_cnt         BIGINT,
buy_cnt          BIGINT,
total_gmv        DECIMAL(18,2),
last_login_days  INT,      -- days since last login
is_active_today  BOOLEAN   -- derived flag

This wide table can directly serve user profiling, retention analysis, GMV reports, and other downstream applications.

Technical Implementation Highlights

ETL Process

ODS → [Cleaning/Standardization] → DWD → [Aggregation/Wide‑Table Construction] → DWS → ADS/BI/APP

Tool Selection

Scheduling : Airflow, DolphinScheduler, DataWorks

Compute Engines : Spark, Flink, Hive, MaxCompute

Storage : HDFS, Hudi, Iceberg, ClickHouse (column‑store optimization for DWS)

Wide‑Table Construction Techniques

Incremental Updates : Use MERGE INTO / upsert (e.g., Flink + Hudi for real‑time DWS).

Slowly Changing Dimensions (SCD) : Version management for changing dimension attributes such as user city.

Metric Reuse : Encapsulate metric logic in UDFs or shared libraries.

Performance Optimizations

Partition or bucket DWS tables on frequent filter fields (e.g., dt, user_id).

Use columnar storage formats (Parquet/ORC) with compression (Snappy/Zstd).

Create materialized views or indexes for hot query columns (e.g., Doris, ClickHouse).

Common Pitfalls

DWD Layer Aggregation : Breaks detail granularity and prevents traceability – keep aggregation in DWS.

Over‑Customizing DWS Wide Tables : Leads to siloed tables and high maintenance – abstract generic wide tables per domain.

Ignoring Dimension Degradation : Causes frequent joins and poor performance – duplicate common dimensions in DWD/DWS.

Inconsistent Metric Definitions : Results in divergent metric values across tables – maintain a metric dictionary referenced by DWS.

Bypassing Layered Architecture : Directly outputting to ADS creates tight coupling – strictly follow DWD → DWS → ADS.

Full Re‑run of DWS : Wastes resources and delays data freshness – adopt incremental update mechanisms (e.g., Flink state, Delta Lake).

Conclusion

The core idea of DWD + DWS + wide‑table driving is “clear details, reusable summaries, service‑oriented wide tables”. DWD acts as the data foundation ensuring quality and consistency; DWS serves as the capability center by materializing analysis‑ready wide tables; the wide table itself is the delivery interface that makes data consumption simple and efficient. This pattern excels in high‑concurrency, low‑latency, and highly reusable enterprise data‑warehouse scenarios, but designers must avoid over‑designing wide tables and should enforce proper layering, governance, and continuous iteration.