Skip to content

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.