Skip to main content

Relation properties

Relations enable the non-technical users to use dimensions and measures from other datasets when there is a relation between the source dataset and the target datasets. Typically, Metriql generates JOIN statements in SQL. You can define relations under meta.metriql.relations property.

Creating relation via dbt test#

seeds:
  - name: countries
    columns:
      - name: iso_code
models:
  - name: pageviews
    meta:
      metriql:
        relations:
          country:
            to: ref('countries')
            sql: "{TABLE}.country = {TARGET}.iso_code"
            type: left_join 
            relationship: many_to_many

Creating relation via meta.metriql#

In some cases, you need to write an SQL expression to build up a relation between the datasets. In this case, you can use meta.metriql.relations as follows:

seeds:
  - name: countries
    columns:
      - name: iso_code
models:
  - name: pageviews
    meta:
      metriql:
        relations:
          countries:
            to: ref('countries')
            type: left_join 
            sql: "get_iso_code({TABLE}.country) = {TARGET}.iso_code"
            relationship: many_to_many

Here is the full list of properties that you can use:

relationship:#

Available values are one_to_one, one_to_many, many_to_many, and many_to_many.

The default is:

relationship: one_to_one

type:#

Available values are left_join, right_join, inner_join, full_join

If you use left_join, the following SQL expression is generated:

SELECT ...
LEFT JOIN target ON (source.source_col = target.target_col)

The default is:

type: left_join

label:#

The value that will be visible in the user interface. The default value is the relation name but you can override it as follows:

label: Countries

hidden:#

Hides the relation in the user interface. It's useful in case you want to test the relationship in the ELT layer with dbt but hide it from the end users.

The default is:

hidden: false

Self joins#

You can join the same table or model more than once by specifying multiple join relations with different names. Here is an example:

relations: 
    customers: 
      relationship: 'one_to_one'
      type: 'left_join'
      to: ref('apps')
    vendors: 
      relationship: 'one_to_one'
      type: 'inner_join'
      to: ref('apps')

Join through another relation#

In some cases, you may want to join a model that doesn't have a direct relationship to your model. Let's say that you have three models; events, campaigns, and ad_networks. There is a relation between events and campaigns model via events.campaign_id = campaigns.id and campaigns to ad_networks via campaigns.ad_network_id = ad_networks.id.

Rather than calculating the number of unique users who came from a specific campaign, you want to see different ad networks and see how they perform compared to each other. Since you don't have a direct relationship between the events and ad_networks model, you can't easily select the columns and define the relation. Instead, you need to join the campaigns model to be able to join ad_networks model. In that case, you can use the following SQL relation from events model to ad_networks model.

   relations: 
    campaign: 
      relationship: one_to_one
      type: left_join
      to: ref('campaigns')
      source: campaign_id
      target: id
    ad_network:
      relationship: many_to_one
      type: left_join
      to: ref('ad_networks')
      sql: '{TARGET}.id = {relation.campaign.dimension.ad_network_id}'

Since your sql references the campaign relation in events model, the join statements for both campaigns and ad_networks model will be included automatically when you select a dimension from ad_network model.

Edit this page