Manage joins efficiently with relationships
1. Manage joins efficiently with relationships
Good job with Metrics. In this video, we'll learn how we can further increase the efficiency and usefulness of our data model using Relationships.2. What are relationships?
Relationships allow you to predefine joins for users. Starting from a source table, you can define a relationship to another table. Once the relationship is defined, anyone using the source table can add columns from the related table using your join logic.3. What are relationships?
But, the predefined join isn't performed until the user adds a column from the related table. This means that relationships can reduce warehouse costs by only performing a join when it's necessary. And, because the logic is defined in the data model, they can help reduce errors from ad hoc joins.4. Relationships vs. Joins
Note that relationships aren't always preferable to joins, though. There are use cases for both. Relationships are best-used when you need the related data sometimes, but not all the time.5. Relationships vs. Joins
For example, imagine a data model table that shows a niche set of transaction data. The main use case is to analyze transactions. On occasion, however, users need to know something about the accounts or customers associated with these specific transactions.6. Relationships vs. Joins
You could choose to join all three tables together, so that all the data is always available. But, this would slow down your data model and increase query costs unnecessarily for the majority of its use.7. Relationships vs. Joins
This is a perfect use case for a relationship. Most of the time, only transaction data is shown and no join is performed. But, users can add in account or customer columns whenever they need them, without manually writing join logic.8. Relationships vs. Joins
Keep this in mind when deciding if your base table should use a join or a relationship.9. Demo
While editing a data model, you can add and manage relationships in the editor panel for any table element, under the **Relationships** section of the **Modeling** tab.10. Demo
Clicking **Add relationship** opens the **Add a relationship** modal, where you can define the relationship's name, description, and join.11. Demo
Relationships also appear in the Entity Relationship Diagram view of the data model, denoted by these arrows.12. Demo
On the data model overview page, relationships are listed on the primary source element, under **Relationships**.13. Best practices for relationships
Keep in mind that relationships are directional. Like with Joins, it is best to consider the granularity of your data tables before creating a relationship to create the intended result.14. Best practices for relationships
For example, a relationship between transactions and accounts is a many-to-one (N:1) relationship, as there can be multiple transactions for one account. If we were to switch the order of these tables, and form a relationship between accounts and transactions, that would be a one-to-many 1:N relationship.15. Best practices for relationships
For simplicity, this course only covers N:1 and 1:1 relationships.16. Let's practice!
Let's test your knowledge of relationships, when to use them, and how they differ from a traditional join.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.