Mastering the DWS Layer: Core Strategies for Scalable Data Warehouses

Core Positioning of the DWS Layer

The DWS (Data Warehouse Service) layer sits between the DWD (Data Warehouse Detail) and ADS (Application Data Service) layers, focusing on data aggregation and domain services. It typically fulfills four core functions: data aggregation, cross‑domain integration, performance acceleration, and unified data export for BI, reporting, and API consumption.

Design Goals

Design is driven by business OKRs, ensuring deep understanding of key scenarios and defining core analysis dimensions (e.g., traffic, transaction, user). The layer aims for "out‑of‑the‑box" usability—balancing pre‑computation with flexibility for dynamic drill‑down—and high model reuse, targeting at least 80% of new requirements to be satisfied by existing models.

Key Design Points

1. Data Modeling Strategy

Domain partitioning defines core tables for each business domain:

User domain : dws_user_behavior – daily active users, 7‑day retention, average usage time.

Transaction domain : dws_trade_daily – GMV, order count, average order value, refund amount.

Traffic domain : dws_traffic_session_agg – page views, unique visitors, bounce rate, average visit depth.

Product domain : dws_product_sales_sku – sales amount, inventory turnover, top‑10 SKUs.

Example DDL for a user‑behavior table:

CREATE TABLE dws_user_behavior (
  user_id BIGINT COMMENT 'User ID',
  dt STRING COMMENT 'Date (yyyyMMdd)',
  -- Dimension degeneration fields
  gender INT COMMENT 'Gender',
  city STRING COMMENT 'City',
  -- Behavioral metrics
  pv_count BIGINT COMMENT 'Daily PV',
  cart_count BIGINT COMMENT 'Add‑to‑cart count',
  order_amount DECIMAL(16,2) COMMENT 'Order amount',
  -- Cross‑domain association field
  last_order_time TIMESTAMP COMMENT 'Most recent order time',
  ...
) PARTITIONED BY (dt STRING) STORED AS PARQUET;

2. Aggregation Computation Strategy

Three paradigms are used:

Fixed dimension : scheduled offline jobs (Hive/Spark) for deterministic high‑frequency analyses such as daily province GMV.

Dynamic dimension : real‑time OLAP engines (ClickHouse, Doris) to support arbitrary dimension combinations.

Hierarchical roll‑up : incremental multi‑level aggregation (hour→day→month) using Kylin or StarRocks.

3. Storage Optimization

Columnar formats (Parquet/ORC) with ZSTD compression reduce storage costs. Tables are partitioned by date and bucketed by user ID to improve pruning. Materialized views are created in OLAP stores (e.g., ClickHouse MATERIALIZED VIEW) for frequently queried aggregates.

Typical Challenges and Solutions

Dimension explosion : limit degraded dimensions, embed only high‑frequency dimensions, and use bitmap encoding for sparse attributes.

Data freshness : adopt a layered pipeline—near‑real‑time layer with Flink for hourly updates and offline layer for deep analysis.

Metric definition consistency : deploy a centralized metric management platform (e.g., Superset with a metric dictionary) to enforce uniform calculation logic across teams.

Storage cost : implement lifecycle management, retaining recent 90 days in hot storage and archiving older data to low‑cost object storage (OSS).

Best Practices

Business‑driven modeling: collaborate with product owners to define core KPIs such as DAU and conversion rate, avoiding over‑aggregation.

Query pattern optimization: use wide tables with dimension degeneration for hot queries; keep detailed DWD tables for complex, low‑frequency queries.

Data service exposure: expose aggregated datasets via API gateways (e.g., GraphQL) to enable flexible filtering.

Automated monitoring: establish data‑quality alerting (e.g., GMV day‑over‑day drop >10% triggers a notification).

Conclusion

The DWS layer is the critical turning point that transforms raw warehouse data into consumable services. Its design must be business‑oriented, balancing storage cost and query performance while maintaining strong domain understanding, systematic thinking, and continuous model optimization.