Using extensions to define joins

[mikhailxu]

When modeling, joins can be redefined in different explores. For example, wherever we join users, we will join in user facts and other tables in normalized schema designs. These “join chains” can lead to severe code bloat.

In this example, the join chain will be extended into other explores.

Here’s a starting generated model:

## Order Items view
explore: order_items {
  view_name: order_items
  
  join: users {
    relationship: many_to_one
    sql_on: ${order_items.user_id} = ${users.id} ;;
  }
  
  join: user_order_facts {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_order_facts.user_id} = ${users.id} ;;
  }
  
  join: user_profile {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_profile.user_id} = ${users.id} ;;
  }
}

## Users view
explore: users {
  view_name:  users
  join: user_order_facts {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_order_facts.user_id} = ${users.id} ;;
  }
  
  join: user_profile {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_profile.user_id} = ${users.id} ;;
  }
}

Let’s convert use the Users explore as the extended explore into the Order Items explore:

## Order Items view
explore: order_items {
  view_name: order_items
  extends: [users]
  join: users {
    relationship: many_to_one
    sql_on: ${order_items.user_id} = ${users.id} ;;
  }
}

## Users view
explore: users {
  view_name:  users
  join: user_order_facts {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_order_facts.user_id} = ${users.id} ;;
  }
  
  join: user_profile {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_profile.user_id} = ${users.id} ;;
  }
}

The key feature of extends that makes this possible is that it takes an array of explores in order to construct the new explore. We can do this with multiple chains of common joins:

## Order Items view
explore: order_items {
  view_name: order_items
  extends: [users, orders]
  join: users {
    relationship: many_to_one
    sql_on: ${order_items.user_id} = ${users.id} ;;
  }
}

## Orders view

explore: orders {
  view_name:  orders
  join: orders_product_facts {
    view_label: "Orders"
    relationship: many_to_one
    sql_on: ${orders_product_facts.order_id} = ${orders.id} ;;
  }
  join: checkout_session {
    relationship: many_to_one
    sql_on: ${checkout_session.order_id} = ${orders.id} ;;
  }
}

## Users view
explore: users {
  view_name:  users
  join: user_order_facts {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_order_facts.user_id} = ${users.id} ;;
  }
  
  join: user_profile {
    view_label: "Users"
    relationship: many_to_one
    sql_on: ${user_profile.user_id} = ${users.id} ;;
  }
}

These are simplified and ideal examples of using extends to manage long join chains. In the real world, we may not have a schema structure or explore structures that allow us to build something as elegant. We may also need to apply overrides to add or remove fields.

To reduce code and maintain flexibility is challenging to balance. Plan for the entire model before refactoring into this pattern.