Tutorial
Chapter 5 — Mastering DMDL
Goal: Expand your model with new entities (PRODUCT, ORDER_LINE) to master Relationships and advanced Attribute Types.
Prerequisites: You must have completed Chapter 4: Loading Strategies.
The Scenario
Marketing comes to you with a request: "We need to know which Books are selling the best."
Currently, your model only has CUSTOMER and ORDER. We need to add PRODUCT and the link between them (ORDER_LINE).
Step 1: Define the New Entities
Edit model.yaml and add these two new entities after your existing CUSTOMER and ORDER:
# ... existing entities (CUSTOMER, ORDER) ...
- id: PRODUCT
name: PRODUCT
definition: Items available for sale
description: Book catalog
attributes:
- id: PRODUCT_ID
name: PRODUCT_ID
definition: Unique product identifier
type: STRING
effective_timestamp: true
- id: PRODUCT_TITLE
name: PRODUCT_TITLE
definition: Title of the book
type: STRING
effective_timestamp: true
- id: PRODUCT_FORMAT
name: PRODUCT_FORMAT
definition: Format of the book (Hardcover, Paperback)
type: STRING
effective_timestamp: true
- id: ORDER_LINE
name: ORDER_LINE
definition: Individual item within an order
description: Link table between Orders and Products
attributes:
- id: ORDER_LINE_ID
name: ORDER_LINE_ID
definition: Unique ID for the line item
type: STRING
effective_timestamp: true
- id: QUANTITY
name: QUANTITY
definition: Number of units purchased
type: NUMBER
effective_timestamp: true
- id: LINE_AMOUNT
name: LINE_AMOUNT
definition: Total value of this line item
type: NUMBER
effective_timestamp: true
Step 2: Add the Relationships
Add these relationships to your existing relationships section:
# ... existing relationship
# NEW: Order line references a product
- name: IS_FOR_PRODUCT
definition: Line item references a product
source_entity_id: ORDER_LINE
target_entity_id: PRODUCT
# NEW: Order contains order lines
- name: BELONGS_TO
definition: Order line belongs to an order
source_entity_id: ORDER_LINE
target_entity_id: ORDER
Key Insight: Relationships are defined from the "many" side to the "one" side. An ORDER_LINE belongs to exactly one ORDER, but an ORDER can have many ORDER_LINEs.
Step 3: Generate Mappings for the New Entities
Generate mapping templates for all entities that don't have mappings yet:
daana-cli generate mapping
This command scans your model and generates templates only for new entities. Existing mappings are safely skipped:
Generating mapping templates...
Model: model.yaml
Output: mappings/
✓ Generated 2 mapping template(s)
Generated PRODUCT: product-mapping.yaml
Generated ORDER_LINE: order_line-mapping.yaml
⊘ Skipped 2 existing mapping(s)
Skipped CUSTOMER: customer-mapping.yaml already exists
Skipped ORDER: order-mapping.yaml already exists
To update existing mappings, use: daana-cli merge mapping
Note: If you need to update existing mappings after model changes, use
daana-cli merge mappinginstead - it preserves your transformation expressions while adding new attributes.
Step 4: Edit the Product Mapping
Edit mappings/product-mapping.yaml to configure the source table:
entity_id: PRODUCT
mapping_groups:
- name: default_mapping_group
tables:
- connection: dev
table: stage.products
primary_keys:
- product_id
ingestion_strategy: FULL
entity_effective_timestamp_expression: updated_at
attributes:
- id: PRODUCT_ID
transformation_expression: product_id
- id: PRODUCT_TITLE
transformation_expression: title
- id: PRODUCT_FORMAT
transformation_expression: format
Validate your mappings to check progress:
daana-cli check mapping
Checking 4 mapping(s) in directory: mappings
→ customer-mapping.yaml ✓
→ order-mapping.yaml ✓
→ order_line-mapping.yaml ✘
→ product-mapping.yaml ✓
✘ order_line-mapping.yaml:
• [schema] Table 'public.order_lines' not found in database
Summary:
Total: 4
Valid: 3
Invalid: 1
The product mapping is now valid. Let's fix the order line mapping next.
Step 5: Edit the Order Line Mapping
Edit mappings/order_line-mapping.yaml:
entity_id: ORDER_LINE
mapping_groups:
- name: default_mapping_group
tables:
- connection: dev
table: stage.sales_order_lines
primary_keys:
- CONCAT(CAST(order_id AS TEXT), '-', CAST(order_line_id AS TEXT))
ingestion_strategy: FULL
entity_effective_timestamp_expression: CURRENT_TIMESTAMP
attributes:
- id: ORDER_LINE_ID
transformation_expression: CONCAT(CAST(order_id AS TEXT), '-', CAST(order_line_id AS TEXT))
- id: QUANTITY
transformation_expression: quantity
- id: LINE_AMOUNT
transformation_expression: (quantity * unit_price) - discount_amount
relationships:
# ORDER_LINE → PRODUCT relationship
- id: ORDER_LINE_IS_FOR_PRODUCT_PRODUCT
atomic_context: ORDER_LINE_IS_FOR_PRODUCT_PRODUCT
source_table: stage.sales_order_lines
target_transformation_expression: product_id
# ORDER_LINE → ORDER relationship
- id: ORDER_LINE_BELONGS_TO_ORDER
atomic_context: ORDER_LINE_BELONGS_TO_ORDER
source_table: stage.sales_order_lines
target_transformation_expression: order_id
Important:
- The relationship
idin mappings follows the pattern:SOURCE_ENTITY_RELATIONSHIP_NAME_TARGET_ENTITY.- Composite Primary Keys: In real-world scenarios,
order_line_idtypically restarts for each order (1, 2, 3...). To create a unique identifier, we use aCONCATexpression inprimary_keyscombiningorder_idandorder_line_id.See Relationship Mappings for the full field reference including
atomic_context.
Validate all mappings are now correct:
daana-cli check mapping
Checking 4 mapping(s) in directory: mappings
→ customer-mapping.yaml ✓
→ order-mapping.yaml ✓
→ order_line-mapping.yaml ✓
→ product-mapping.yaml ✓
Summary:
Total: 4
Valid: 4
Invalid: 0
All mappings are valid. Now let's deploy.
Step 6: Update Workflow & Deploy
Add the new mappings to workflow.yaml:
mappings:
- mappings/customer-mapping.yaml
- mappings/order-mapping.yaml
- mappings/product-mapping.yaml # NEW
- mappings/order_line-mapping.yaml # NEW
Validate your workflow:
daana-cli check workflow
Checking workflow: workflow.yaml
Workflow: BOOK_RETAILER_WORKFLOW
Workflow ID: 1424339281
Model: ✓
Entity Coverage:
- CUSTOMER: mapped ✓
- ORDER: mapped ✓
- PRODUCT: mapped ✓
- ORDER_LINE: mapped ✓
✓ Workflow valid
All four entities are now mapped. Deploy and execute:
daana-cli deploy
daana-cli execute
Step 7: Query Your Complete Model
Now you can answer Marketing's question:
docker exec -it daana-customerdb psql -U dev -d customerdb
-- Which books are selling the best?
SELECT
p."PRODUCT_TITLE",
p."PRODUCT_FORMAT",
SUM(ol."QUANTITY") as total_units_sold,
SUM(ol."LINE_AMOUNT") as total_revenue
FROM daana_dw.view_product p
JOIN daana_dw.view_order_line_with_rel ol -- use the generated _with_rel view to access related entities' keys
ON ol.product_key = p.product_key
GROUP BY p."PRODUCT_TITLE", p."PRODUCT_FORMAT"
ORDER BY total_revenue DESC
LIMIT 10;
Result:
PRODUCT_TITLE | PRODUCT_FORMAT | total_units_sold | total_revenue
----------------------------------+----------------+------------------+---------------
The Way of Kings | Hardcover | 2 | 65.98
Becoming | Hardcover | 2 | 56.98
Children of Blood and Bone | Hardcover | 3 | 51.97
It Ends with Us | Paperback | 3 | 49.97
American Gods | Hardcover | 2 | 45.98
The Ocean at the End of the Lane | Hardcover | 2 | 31.98
Americanah | Paperback | 2 | 28.48
Norwegian Wood | Paperback | 2 | 25.18
The Testaments | Hardcover | 1 | 24.99
Words of Radiance | Hardcover | 1 | 23.99
(10 rows)
Summary of Key DMDL Concepts
Attribute Types
STRING- Text valuesNUMBER- Numeric values (integers, decimals)START_TIMESTAMP- When an entity lifecycle beginsEND_TIMESTAMP- When an entity lifecycle endsUNIT- Measurement units (currency, weight units, etc.)
Ingestion Strategies
FULL- Complete table refreshINCREMENTAL- Delta processing with watermarkFULL_LOG- Snapshot series for SCD2TRANSACTIONAL- Append-only events
Relationship Direction
Relationships are defined from the child (many) to the parent (one):
ORDER_LINE → PRODUCT(many lines can reference one product)ORDER_LINE → ORDER(many lines belong to one order)ORDER → CUSTOMER(many orders belong to one customer)
Group Attributes
- Bundle related attributes (Amount + Currency)
- Enforce data quality at the model level
- Semantic clarity in your data warehouse