Relationships¶
A relationship is the rule for joining one dataset to another, so nobody guesses the key. Its fields follow
Apache Ossie's relationship. Many rows of from match one row of to. A one-to-one join is a special case of
this; a many-to-many join needs a mapping table, which is not in the spec yet. The list sits beside datasets at the
top level.
You do not declare every join. Declare the grain of every dataset first: from the grains, an agent (and the
checker) can tell which joins are safe. Add a relationship only for joins the shape cannot explain: a key that looks
joinable but is not, differently named key columns, a mapping table in between, or a join to data outside the
project whose grain is not declared here.
For implementers
Declaring grains only helps if the agent compares them before it joins. A tool that shows datasets to an agent should show the grain of each dataset ("one row per market_id x sector x period_yq") and tell the agent to check the grain of both sides before a join: a join is safe for sums when the join columns are the full grain of one side.
Example¶
relationships:
- name: rents_to_markets
from: Rents
to: marketdata-example/Markets
from_columns: [market_id]
to_columns: [market_id]
ai_context: join on market_id, never on market names
from and to name a dataset in this project (Markets) or in another project (marketdata-example/Markets).
Definitions¶
Priority: Critical for a key that looks joinable but is not (names instead of ids, codes that lose a leading zero, a mapping table in between). For other joins the grain is usually enough, and a relationship is Useful. See What to write first.
| Key | Type | Required | Priority | Check | Description |
|---|---|---|---|---|---|
relationships[].name |
string | yes | Critical | error unless it matches [A-Za-z0-9_.-]+; error if duplicated |
Identifier. |
relationships[].from |
string | yes | Critical | error if a / form is not a valid dataset id (a column id is an error); a plain name must be a dataset |
The dataset on the many side: a dataset here or <project>/<Dataset>. |
relationships[].to |
string | yes | Critical | as for from |
The dataset on the one side. |
relationships[].from_columns |
list of column names | yes, at least one | Critical | error if empty; error if length differs from to_columns; error if a column is not declared on a local from dataset that declares columns |
Key columns of from. |
relationships[].to_columns |
list of column names | yes, at least one | Critical | as above | Key columns of to, matched to from_columns by position. |
relationships[].ai_context |
string or map | no | Critical | - | Guidance for an agent (Ossie field). |
When to is a dataset of this project, the checker warns (a hint) if to_columns are not that dataset's grain (one row of from
can then match several rows and the join double-counts), or if the dataset has no grain to compare with.