Model Relationships Between Datasets

What you will do

Describe how several tables join, check that the keys really behave, define a reusable measure and export the model so other code can use it.

Before you start

• Two or more datasets in the Data Hub that share a key. For example, an orders table and a customers table, or two tables each with an Id column.

Step 1: Open the model

In the Data Hub choose Model relationships across your datasets. Add the datasets you want to the canvas. Each appears as a table node listing its columns.

Step 2: Draw a relationship

Drag from a field on one table to a field on another. A line appears. Click the line to inspect it and choose its cardinality: one-to-one, one-to-many or many-to-many. You can also let DLWAY suggest relationships by name and value. Suggestions are dashed and labelled suggested until you accept them.

Step 3: Validate

Validate the relationship. DLWAY checks referential integrity in both directions: how many rows on each side have no partner (orphans), and whether a key that should be unique is. A clean one-to-one relationship reports zero orphans each way. If you find orphans, trace them with the Transform window, fix the source, and validate again.

Step 4: Add a measure

Choose New measure. Name it, then enter a formula over the modelled tables, for example SUM(orders.amount). The editor tells you if the formula does not make sense for your tables. Preview the measure: it is computed against the live tables, so you see a real number. Save it; measures persist with the model.

Step 5: Export

Choose Export model and pick a format:

• JSON for the model itself
• SQL DDL for creating the tables, keys and foreign keys in a database
• Pandera for validating DataFrames
• Pydantic for typed records

Open each export and compare how the same relationship is expressed.

Step 6: Use it

Run the joins in the Transform window or a pipeline, and keep the model as the written record of how your data fits together.

Related

The Data Model.