Skip to content

info note on junction table joins [DOC-1090] #7567

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 3 commits into from
Apr 16, 2025
Merged

info note on junction table joins [DOC-1090] #7567

Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
d1ba6b7
info note on junction table joins
stayseesong Apr 14, 2025
ab27669
edits
stayseesong Apr 16, 2025
f7772be
re-write
stayseesong Apr 16, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
16 changes: 14 additions & 2 deletions src/unify/data-graph/index.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -354,13 +354,25 @@ For many:many relationships, define the join on between the two entity tables wi

**Junction table spec**

| Parameters | Definition |
| --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Parameters |Definition |
| --------------- | --------------------------------- |
| `table_ref` | Defines the fully qualified table reference to the join table: `[database name].[schema name].[table name]`. Segment flexibly supports tables, views and materialized views |
| `primary_key` | The unique identifier for the given table. Must be a column with unique values per row |
| `left_join_on` | Define the relationship between the left entity table and the junction table: `[left entity slug].[column name] = [junction table column name]`. Note that schema and table are implied within the junction table column name, so you do not need to define it again |
| `right_join_on` | Define the relationship between the junction table and the right entity table: `[junction table column name] = [right entity slug].[column name]`. Note that schema and table are implied within the junction table column name, so you do not need to define it again |


When you define a many-to-many relationship using a junction table, `left_join_on` and `right_join_on` tell Data Graph how to connect each entity to the junction table:

* Use `left_join_on` to specify which column in the junction table links to the parent (left) entity.

* Use `right_join_on` to specify which column links to the child (right) entity.

These fields define the join conditions, but they don’t control how the join is executed. Data Graph always performs inner joins, even if you specify a `left_join_on`.

If you need behavior similar to a left join (like including unmatched rows), create a view in your warehouse with the logic you’re targeting and reference that view as an entity in your graph.


**Example:**

```python
Expand Down
Loading