Practical Guide to Building the DWD Layer in a Data Warehouse

1. DWD Layer Structure

The DWD layer parses user log behavior facts and remodels transactional business data using a dimensional model (dimensional degeneration). It is defined as the most granular fact layer driven by business processes, often called a logical fact table, and may include redundant dimension attributes to form wide tables.

1.1 Review of DWD Concept

"Detail‑grain fact layer: built on business processes, constructing the finest‑grain fact tables. Important dimension attributes may be duplicated for wide‑table handling. These tables are also known as logical fact tables."

1.2 Four Modeling Steps

The DWD layer modeling consists of four steps:

Select business processes (e.g., order fact, payment fact).

Declare grain – ensure each row represents the smallest possible business event.

Confirm dimensions – typically time, user, location, product, coupon, activity.

Confirm facts – define the measures for each fact table.

2. DWD Layer – Transactional Fact Tables

2.1 Concept

Transactional fact tables store immutable rows representing a single transaction or event (e.g., a sales order, a payment). Data is loaded incrementally; once inserted, rows are never updated.

2.2 Payment Fact Table

Required dimensions: time, user, region. The region field is missing in ods_payment_info, so it is obtained via a join with ods_order_info.

droptableifexists dwd_fact_payment_info;</code>
<code>create external table dwd_fact_payment_info (</code>
<code>  `id` string COMMENT 'id',</code>
<code>  `out_trade_no` string COMMENT '对外业务编号',</code>
<code>  `order_id` string COMMENT '订单编号',</code>
<code>  `user_id` string COMMENT '用户编号',</code>
<code>  `alipay_trade_no` string COMMENT '支付宝交易流水编号',</code>
<code>  `payment_amount` decimal(16,2) COMMENT '支付金额',</code>
<code>  `subject` string COMMENT '交易内容',</code>
<code>  `payment_type` string COMMENT '支付类型',</code>
<code>  `payment_time` string COMMENT '支付时间',</code>
<code>  `province_id` string COMMENT '省份ID'</code>
<code>) COMMENT '支付事实表' PARTITIONED BY (`dt` string) STORED AS PARQUET</code>
<code>LOCATION '/warehouse/gmall/dwd/dwd_fact_payment_info/'</code>
<code>TBLPROPERTIES ("parquet.compression"="lzo");

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;</code>
<code>INSERT OVERWRITE TABLE dwd_fact_payment_info PARTITION (dt='2021-05-03')</code>
<code>SELECT</code>
<code>  pi.id, pi.out_trade_no, pi.order_id, pi.user_id, pi.alipay_trade_no,</code>
<code>  pi.total_amount, pi.subject, pi.payment_type, pi.payment_time, oi.province_id</code>
<code>FROM (SELECT * FROM ods_payment_info WHERE dt='2021-05-03') pi</code>
<code>JOIN (SELECT id, province_id FROM ods_order_info WHERE dt='2021-05-03') oi</code>
<code>ON pi.order_id = oi.id;

2.3 Refund Fact Table

All required dimensions (time, user, product) exist in ods_order_refund_info, so data can be loaded directly.

droptableifexists dwd_fact_order_refund_info;</code>
<code>create external table dwd_fact_order_refund_info (</code>
<code>  `id` string COMMENT '编号',</code>
<code>  `user_id` string COMMENT '用户ID',</code>
<code>  `order_id` string COMMENT '订单ID',</code>
<code>  `sku_id` string COMMENT '商品ID',</code>
<code>  `refund_type` string COMMENT '退款类型',</code>
<code>  `refund_num` bigint COMMENT '退款件数',</code>
<code>  `refund_amount` decimal(16,2) COMMENT '退款金额',</code>
<code>  `refund_reason_type` string COMMENT '退款原因类型',</code>
<code>  `create_time` string COMMENT '退款时间'</code>
<code>) COMMENT '退款事实表' PARTITIONED BY (`dt` string) STORED AS PARQUET</code>
<code>LOCATION '/warehouse/gmall/dwd/dwd_fact_order_refund_info/'</code>
<code>TBLPROPERTIES ("parquet.compression"="lzo");

INSERT OVERWRITE TABLE dwd_fact_order_refund_info PARTITION (dt='2021-05-03')
SELECT id, user_id, order_id, sku_id, refund_type, refund_num, refund_amount, refund_reason_type, create_time
FROM ods_order_refund_info
WHERE dt='2021-05-03';

3. DWD Layer – Periodic Snapshot Fact Tables

3.1 Concept

Periodic snapshot tables retain data at fixed intervals (e.g., daily sales, monthly balances). They capture a full snapshot at the end of each period, using full‑load synchronization.

3.2 Cart Snapshot Fact Table

droptableifexists dwd_fact_cart_info;</code>
<code>create external table dwd_fact_cart_info (</code>
<code>  `id` string COMMENT '编号',</code>
<code>  `user_id` string COMMENT '用户id',</code>
<code>  `sku_id` string COMMENT 'skuid',</code>
<code>  `cart_price` string COMMENT '放入购物车时价格',</code>
<code>  `sku_num` string COMMENT '数量',</code>
<code>  `sku_name` string COMMENT 'sku名称 (冗余)',</code>
<code>  `create_time` string COMMENT '创建时间',</code>
<code>  `operate_time` string COMMENT '修改时间',</code>
<code>  `is_ordered` string COMMENT '是否已经下单。1为已下单;0为未下单',</code>
<code>  `order_time` string COMMENT '下单时间',</code>
<code>  `source_type` string COMMENT '来源类型',</code>
<code>  `source_id` string COMMENT '来源编号'</code>
<code>) COMMENT '加购事实表' PARTITIONED BY (`dt` string) STORED AS PARQUET</code>
<code>LOCATION '/warehouse/gmall/dwd/dwd_fact_cart_info/'</code>
<code>TBLPROPERTIES ("parquet.compression"="lzo");

INSERT OVERWRITE TABLE dwd_fact_cart_info PARTITION (dt='2021-05-03')
SELECT id, user_id, sku_id, cart_price, sku_num, sku_name, create_time, operate_time, is_ordered, order_time, source_type, source_id
FROM ods_cart_info
WHERE dt='2020-06-14';

3.3 Favorite Fact Table

The favorite fact table follows the same pattern as the cart snapshot, using time, product, and user dimensions.

4. DWD Layer – Cumulative Snapshot Fact Tables

4.1 Concept

Cumulative snapshot tables track entities that change over time (e.g., order lifecycle, coupon usage). They require incremental loads that update existing rows as the business process progresses.

4.2 Coupon Usage Fact Table (Cumulative Snapshot)

droptableifexists dwd_fact_coupon_use;</code>
<code>create external table dwd_fact_coupon_use (</code>
<code>  `id` string COMMENT '编号',</code>
<code>  `coupon_id` string COMMENT '优惠券ID',</code>
<code>  `user_id` string COMMENT 'userid',</code>
<code>  `order_id` string COMMENT '订单id',</code>
<code>  `coupon_status` string COMMENT '优惠券状态',</code>
<code>  `get_time` string COMMENT '领取时间',</code>
<code>  `using_time` string COMMENT '使用时间(下单)',</code>
<code>  `used_time` string COMMENT '使用时间(支付)'
<code>) COMMENT '优惠券领用事实表' PARTITIONED BY (`dt` string) STORED AS PARQUET</code>
<code>LOCATION '/warehouse/gmall/dwd/dwd_fact_coupon_use/'</code>
<code>TBLPROPERTIES ("parquet.compression"="lzo");

Note: the partition dt is derived from get_time.

INSERT OVERWRITE TABLE dwd_fact_coupon_use PARTITION (dt)
SELECT id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time,
       COALESCE(date_format(used_time,'yyyy-MM-dd'), date_format(expire_time,'yyyy-MM-dd'), '9999-99-99')
FROM ods_coupon_use
WHERE dt='2021-05-03';

Daily incremental load uses a full‑outer join between the previous day's snapshot and the current day's ODS data to merge new and updated records.

SET hive.exec.dynamic.partition.mode=nonstrict;</code>
<code>SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;</code>
<pre><code>INSERT OVERWRITE TABLE dwd_fact_coupon_use PARTITION (dt)
SELECT IF(new.id IS NULL, old.id, new.id),
       IF(new.coupon_id IS NULL, old.coupon_id, new.coupon_id),
       IF(new.user_id IS NULL, old.user_id, new.user_id),
       IF(new.order_id IS NULL, old.order_id, new.order_id),
       IF(new.coupon_status IS NULL, old.coupon_status, new.coupon_status),
       IF(new.get_time IS NULL, old.get_time, new.get_time),
       IF(new.using_time IS NULL, old.using_time, new.using_time),
       IF(new.used_time IS NULL, old.used_time, new.used_time),
       date_format(IF(new.get_time IS NULL, old.get_time, new.get_time), 'yyyy-MM-dd')
FROM (SELECT * FROM dwd_fact_coupon_use WHERE dt IN (SELECT date_format(get_time,'yyyy-MM-dd') FROM ods_coupon_use WHERE dt='2021-05-04')) old
FULL OUTER JOIN (SELECT * FROM ods_coupon_use WHERE dt='2021-05-04') new
ON old.id = new.id;

Conclusion

The DWD layer processes fact tables at the finest grain, providing a reliable source for downstream DWS and DWT layers. Its dimensional modeling follows four steps: select business process, declare grain, confirm dimensions, and confirm facts. The article demonstrates these steps with concrete Hive SQL scripts for transactional, snapshot, and cumulative fact tables.

Reference books: Data Warehouse 4th Edition; Data Warehouse Toolbox; DAMA Guide to Data Management; Huawei Data Road.