When modeling topics, simplicity in naming conventions is key.
Our goal to make the BI layer self-serve beings with prioritizing ease of navigation for our end users.
Aliasing joins is a good start to build out tables of logical concepts. For this example they’ll be buyers and sellers.
Suppose we have an orders table containing fields such as seller_id and buyer_id , alongside a users table identified by an id key. To segment the users table into buyers and sellers, we need to join either the buyer_id or seller_id field with the id field in the users table. Essentially, we aim to join the same tables twice but in different manners each time. This is where aliasing comes in handy.
Error we would see without the alias
If we try to make two joins without using aliasing we would reach a validation error.
Relationship duplication: there are multiple relationships between "order_items" and "users". This may lead to surprising results. Use a different alias for one of the relationships.
Solution: Use aliasing
To join the same tables twice but in different manners each time, we use aliasing.
In our relationships file we do the following:
Define the buyers join as:
# Define the buyers join
- join_from_view: order_items
join_to_view: users
join_to_view_as: buyers
join_type: always_left
on_sql: ${order_items.buyer_id} = ${buyers.id}
relationship_type: assumed_many_to_one
Define the sellers join as:
# Define the sellers join
- join_from_view: order_items
join_to_view: users
join_to_view_as: sellers
join_type: always_left
on_sql: ${order_items.seller_id} = ${sellers.id}
relationship_type: assumed_many_to_one
Use the alias in the join on condition
It’s important to note that in the on_sql: block we are using the table alias as the prefix (sellers) in the join ${order_items.seller_id} = ${sellers.id}.