E-commerce EDW data model
Data Vault 2.0 raw vault for e-commerce: hubs (customer/product/order/payment/shipment), links (customer-order, a transactional order-line link with quantity and price, order-payment, order-shipment), and satellites (customer PII, product, an order-state effectivity satellite, payment details with a full-masked card token, and shipment status). Hash keys and load metadata are generated by the DDL layer.
E-commerce enterprise data warehouse modeled as a Data Vault 2.0 raw vault: customer, product, order, payment, and shipment hubs; relationship links plus a transactional order-line link; and descriptive plus effectivity satellites — built for integrating storefront, payment-gateway, and fulfillment source systems with full historization and the combined consumer-privacy and card-data lineage (GDPR, PCI-DSS) that makes Data Vault attractive for commerce platforms. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 15
- tables
- 94
- columns
- 13
- relationships
- 8
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs hold only commerce business keys
Separating customer_id, sku, and order_number into hubs lets the storefront, OMS, payment gateway, and WMS attach independently without reworking existing structures — the core agility of Data Vault.
Transactional order-line link
Order line items are immutable purchase events, so the line link is insert-only and carries its measures (quantity, unit_price, line_total) directly — no satellite needed.
Effectivity satellite for order state
An order moves through created → paid → shipped → delivered (or cancelled/refunded); an effectivity satellite records the valid period of each state for accurate as-of fulfillment reporting.
PCI-DSS full-masked card token, GDPR-hashed email
The payment satellite full-masks the card token and the customer satellite hashes email so the governance report flags cardholder and consumer data; load_date + record_source provide the audit lineage both PCI-DSS and GDPR require.
Tables in the E-commerce EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_customer
Grain: one row per unique hub_customer business key
| Column | Type | Keys |
|---|---|---|
| customer_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_product
Grain: one row per unique hub_product business key
| Column | Type | Keys |
|---|---|---|
| sku | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_order
Grain: one row per unique hub_order business key
| Column | Type | Keys |
|---|---|---|
| order_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_payment
Grain: one row per unique hub_payment business key
| Column | Type | Keys |
|---|---|---|
| payment_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_shipment
Grain: one row per unique hub_shipment business key
| Column | Type | Keys |
|---|---|---|
| shipment_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_customer_order
Grain: one row per lnk_customer_order relationship
Which customer placed which order.
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | |
| order_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_order_line
Grain: one row per order line item (non-historized link)
Transactional (non-historized) link — order line items with qty and price.
| Column | Type | Keys |
|---|---|---|
| order_hk | binary | |
| product_hk | binary | |
| line_id | varchar | BK |
| quantity | integer | |
| unit_price | decimal | |
| line_discount | decimal | |
| line_total | decimal | |
| currency | varchar | |
| load_date | timestamp | |
| record_source | varchar |
lnk_order_payment
Grain: one row per lnk_order_payment relationship
Which payment settled which order.
| Column | Type | Keys |
|---|---|---|
| order_hk | binary | |
| payment_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_order_shipment
Grain: one row per lnk_order_shipment relationship
Which shipment fulfilled which order.
| Column | Type | Keys |
|---|---|---|
| order_hk | binary | |
| shipment_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (5)
sat_customer_details
Grain: one row per customer_hk per change
Customer descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| shipping_address | varchar | PII |
| date_of_birth | date | PII |
| loyalty_tier | varchar | |
| marketing_consent | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_product_details
Grain: one row per product_hk per change
Product descriptive history.
| Column | Type | Keys |
|---|---|---|
| product_hk | binary | PK |
| load_date | timestamp | PK |
| product_name | varchar | |
| category | varchar | |
| brand | varchar | |
| list_price | decimal | |
| currency | varchar | |
| weight_grams | integer | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_order_status
Grain: one row per order per status change
Effectivity satellite tracking order state transitions over time.
| Column | Type | Keys |
|---|---|---|
| order_hk | binary | PK |
| load_date | timestamp | PK |
| order_state | varchar | |
| channel | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_payment_details
Grain: one row per payment_hk per change
Payment descriptive history — card token full-masked for PCI-DSS.
| Column | Type | Keys |
|---|---|---|
| payment_hk | binary | PK |
| load_date | timestamp | PK |
| payment_method | varchar | |
| card_token | varchar | PII |
| card_brand | varchar | |
| last_four | varchar | PII |
| amount | decimal | |
| currency | varchar | |
| authorization_code | varchar | |
| payment_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_shipment_status
Grain: one row per shipment per status change
Shipment status descriptive history.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | PK |
| load_date | timestamp | PK |
| carrier | varchar | |
| tracking_number | varchar | |
| shipment_state | varchar | |
| shipped_date | timestamp | |
| delivered_date | timestamp | |
| destination_country | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_code | varchar | BK |
| currency_name | varchar | |
| minor_units | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_customer_order.customer_hk → hub_customer (many-to-one)
- lnk_customer_order.order_hk → hub_order (many-to-one)
- lnk_order_line.order_hk → hub_order (many-to-one)
- lnk_order_line.product_hk → hub_product (many-to-one)
- lnk_order_payment.order_hk → hub_order (many-to-one)
- lnk_order_payment.payment_hk → hub_payment (many-to-one)
- lnk_order_shipment.order_hk → hub_order (many-to-one)
- lnk_order_shipment.shipment_hk → hub_shipment (many-to-one)
- sat_customer_details.customer_hk → hub_customer (many-to-one)
- sat_product_details.product_hk → hub_product (many-to-one)
- sat_order_status.order_hk → hub_order (many-to-one)
- sat_payment_details.payment_hk → hub_payment (many-to-one)
- sat_shipment_status.shipment_hk → hub_shipment (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.