The Data Warehouse Detail (DWD) layer stores fine-grained fact tables built around individual business processes. As the core of dimensional modeling for a data warehouse, fact tables at the Data Warehouse Service (DWS) and DWD layers are both designed based on the business process. The DWD layer retains the most atomic records—preserving the full business context for downstream analysis.
To build wide tables, duplicate key dimension attributes directly into a fact table instead of always joining a separate dimension table. These stored dimension columns are called degenerate dimensions. They speed up queries and let you filter and aggregate data without additional joins.
Fact types and table types
Fact types
Every measure in a fact table falls into one of three categories:
| Fact type | Description | Example |
|---|---|---|
| Additive | Sums correctly across any dimension | Revenue, quantity |
| Semi-additive | Sums correctly across some dimensions, not all | Inventory balance (valid by location or product, but not summed across months) |
| Non-additive | Cannot be summed at all | Ratios, percentages |
Decompose non-additive facts into their additive components before storing them. For example, store a ratio's numerator and denominator as separate additive columns.
Fact table types
The DWD layer supports three fact table types. Choose the type that matches how your business process unfolds over time.
| Type | When to use | Key characteristic |
|---|---|---|
| Transaction fact table | Capturing events at a point in time | Stores the most atomic data; also called an atomic fact table |
| Periodic snapshot fact table | Recording state at regular intervals | Rows are inserted on a predictable schedule (daily, weekly) |
| Accumulating snapshot fact table | Tracking a process from start to finish | Has multiple date columns for key milestones; rows are updated as the process progresses |
For guidance on selecting the right type, see the data warehouse construction guide.
Design fact tables at the DWD layer
Follow these principles when modeling fact tables at the DWD layer.
Do:
Declare the statistic granularity before selecting dimensions or measures. The granularity defines what one row represents (for example, one order line or one transaction).
Include all measures relevant to a single business process in one fact table.
Store only measures at a single granularity per table.
Use the same unit for a measure across different fact tables.
Decompose non-additive facts into additive components.
Use degenerate dimensions to avoid unnecessary joins and improve query performance.
Handle null measure values explicitly—decide upfront whether to store zero, null, or a sentinel value.
Don't:
Associate a fact table at the DWD layer with more than one dimension. Mixed-dimension tables make granularity ambiguous and create confusing aggregations.
Mix measures of different granularities in the same table.
The four-step design process
For each business process you want to model, work through these steps in order:
Select the business process — identify the event or activity to measure (for example, order creation, payment, or shipment).
Declare the granularity — define what one row represents. This is the semantic anchor for all subsequent decisions.
Select dimensions — choose the dimensions that provide context for the event (who, what, where, when).
Select measures — choose the numeric facts that quantify the event.
Declaring granularity before selecting dimensions or measures prevents the most common modeling mistake: including data at different levels of detail in the same table.
Follow DWD layer design specifications
Naming convention
Fact table names follow this pattern:
dwd_{business_unit|pub}_{data_domain}_{business_process}[_{custom_tag}]_{partition_type}| Segment | Description |
|---|---|
dwd | Fixed prefix identifying the DWD layer |
{business_unit} or pub | Abbreviated business unit; use pub when data spans multiple business units |
{data_domain} | Abbreviated data domain (for example, trd for trade, itm for item) |
{business_process} | Abbreviated business process |
{custom_tag} | Optional tag for further disambiguation |
{partition_type} | di for daily incremental, df for daily full |
Examples:
| Table name | Description |
|---|---|
dwd_asale_trd_ordcrt_trip_di | Air ticket orders for Company A's e-commerce business, loaded daily incremental |
dwd_asale_itm_item_df | Product snapshots for Company A's e-commerce business, loaded daily full |
Tables in this tutorial
This tutorial builds three DWD fact tables for an e-commerce scenario:
| Table name | Description |
|---|---|
dwd_asale_trd_itm_di | Fact table of transactional product information |
ods_asale_trd_mbr_di | Fact table of transactional member information |
dwd_asale_trd_ord_di | Fact table of transactional order information |
Create tables
All three tables use degenerate dimensions to embed key attribute fields directly in the fact table, reducing the need for joins at query time. Each table is partitioned by date (ds) with a 400-day retention period.
CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di
(
item_id BIGINT COMMENT 'Product ID',
item_title STRING COMMENT 'Product name',
item_price DOUBLE COMMENT 'Product price',
item_stuff_status BIGINT COMMENT 'Product status: 0: new; 1: idle; 2: second-hand',
item_prov STRING COMMENT 'Province of the product',
item_city STRING COMMENT 'City of the product',
cate_id BIGINT COMMENT 'Product category ID',
cate_name STRING COMMENT 'Product category name',
commodity_id BIGINT COMMENT 'Commodity ID',
commodity_name STRING COMMENT 'Commodity name',
buyer_id BIGINT COMMENT 'Buyer ID',
)
COMMENT 'Fact table of transactional product information'
PARTITIONED BY (ds STRING COMMENT 'Date')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS ods_asale_trd_mbr_di
(
order_id BIGINT COMMENT 'Order ID',
bc_type STRING COMMENT 'Business category',
buyer_id BIGINT COMMENT 'Buyer ID',
buyer_nick STRING COMMENT 'Buyer nickname',
buyer_star_id BIGINT COMMENT 'Buyer star ID',
seller_id BIGINT COMMENT 'Seller ID',
seller_nick STRING COMMENT 'Seller nickname',
seller_star_id BIGINT COMMENT 'Seller star ID',
shop_id BIGINT COMMENT 'Shop ID',
shop_name STRING COMMENT 'Shop name'
)
COMMENT 'Fact table of transactional member information'
PARTITIONED BY (ds STRING COMMENT 'Date')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS dwd_asale_trd_ord_di
(
order_id BIGINT COMMENT 'Order ID',
pay_order_id BIGINT COMMENT 'Payment order ID',
pay_status BIGINT COMMENT 'Payment status: 1: unpaid; 2: paid; 3: refunded',
succ_time STRING COMMENT 'Transaction end time',
item_id BIGINT COMMENT 'Product ID',
item_quantity BIGINT COMMENT 'Quantity of purchased products',
confirm_paid_amt DOUBLE COMMENT 'Total revenue of received and confirmed products',
logistics_id BIGINT COMMENT 'Logistics order ID',
mord_prov STRING COMMENT 'Recipient''s province',
mord_city STRING COMMENT 'Recipient''s city',
mord_lgt_shipping BIGINT COMMENT 'Shipping method: 1: surface mail; 2: express delivery; 3: EMS',
mord_address STRING COMMENT 'Recipient''s address',
mord_mobile_phone STRING COMMENT 'Recipient''s mobile number',
mord_fullname STRING COMMENT 'Recipient name',
buyer_nick STRING COMMENT 'Buyer nickname',
buyer_id BIGINT COMMENT 'Buyer ID'
)
COMMENT 'Fact table of transactional order information'
PARTITIONED BY (ds STRING COMMENT 'Date')
LIFECYCLE 400;What's next
Build the DWS layer on top of these fact tables to create aggregated summaries for BI tools.
Review the data warehouse construction guide for end-to-end modeling guidelines.