Manage joins efficiently with relationships
Data Modeling in Sigma
Ben Harris
Technical Instructional Designer at Sigma
What are relationships?
- Predefine joins
- Anyone can add columns using your join logic
What are relationships?
- Join not performed until user adds a column from related table
- Reduce warehouse cost → join only when necessary
- Fewer errors from ad hoc joins
Relationships vs. Joins
- Relationships are not always better than Joins
- Relations are best when you only need related data sometimes
Relationships vs. Joins
- Example: niche transaction data
- Main use: analyze transactions
- Sometimes need account or customer info
Relationships vs. Joins
- Example: niche transaction data
- Join all three tables → always available
- But slower, higher query cost for most use
Relationships vs. Joins
- Example: niche transaction data
- Relationship fits: most of the time no join
- Users add account/customer columns when needed
Relationships vs. Joins
- Decide: join or relationship for your base table
Best practices for relationships
- Relationships are directional
Best practices for relationships
Examples:
- Transactions to accounts: many-to-one (N:1) relationship
- Accounts to transactions: one-to-many (1:N) relationship.
Best practices for relationships
- This course: N:1 and 1:1 relationships only
