Translating Complex Looker Joins with COALESCE to Omni
When migrating from Looker to Omni, one of the more challenging scenarios involves translating complex join patterns that use COALESCE in the join conditions. This article walks through a real-world example of how to handle a four-table join where one table’s join condition depends on coalesced values from two other tables.
The Looker Pattern
Consider this Looker explore configuration:
#lookml
explore: A
join: B
relationship: one_to_one
sql_on: ${A.some_field} = ${B.some_field}
join: C
relationship: one_to_one
sql_on: ${A.some_field} = ${C.some_field}
join: D
relationship: one_to_one
sql_on: ${D.some_field} = COALESCE(${B.some_field}, ${C.some_field})
In this pattern:
-
Tables B and C both join to A using the same field
-
Table D joins using a COALESCE that prefers B’s field value, falling back to C’s field value if B’s is null
-
This creates a dependency where D’s join condition relies on data from both B and C
Translation Approach 1: Sequential Joins (Recommended)
The most straightforward translation maintains the join dependencies by structuring them sequentially:
Relationships File
# relationships.yml file
- join_from_view: A
join_to_view: B
join_type: always_left
sql_on: ${A.some_field} = ${B.some_field}
relationship_type: one_to_one
- join_from_view: A
join_to_view: C
join_type: always_left
sql_on: ${A.some_field} = ${C.some_field}
relationship_type: one_to_one
- join_from_view: C
join_to_view: D
join_type: always_left
sql_on: ${D.some_field} = COALESCE(${B.some_field}, ${C.some_field})
relationship_type: one_to_one
Topic File
base_view: A
joins:
B: {}
C:
D: {}
Why this works: By making D a child of C in the model hierarchy, we ensure that both B and C are available when D’s join condition is evaluated. The COALESCE function can reference fields from both tables.
Important note: You cannot repeat tables in Omni’s model hierarchy. Each table can only appear once in the joins structure.
Alternative Translation Approaches
Approach 2: Nesting Under B
D could also nest under B instead of C:
Topic file
base_view: A
joins:
B:
D: {}
C: {}
Important note: You cannot repeat tables in Omni’s model hierarchy. Each table can only appear once in the joins structure.
Approach 3: Direct Join from A to D
Since Omni allows referencing multiple tables in join conditions, you can join D directly from A:
Relationships file
# relationships.yml
- join_from_view: A
join_to_view: B
join_type: always_left
sql_on: ${A.some_field} = ${B.some_field}
relationship_type: one_to_one
- join_from_view: A
join_to_view: C
join_type: always_left
sql_on: ${A.some_field} = ${C.some_field}
relationship_type: one_to_one
- join_from_view: A
join_to_view: D
join_type: always_left
sql_on: ${D.some_field} = COALESCE(${B.some_field}, ${C.some_field})
relationship_type: one_to_one
Topic file
base_view: A
joins:
B: {}
C: {}
D: {}
Key Differences from Looker
Join Path Dependencies
-
Looker: All joins are directly defined in the explore with no concept of joining “from” a specific table - it’s implied by the SQL in the join condition
-
Omni: You explicitly define which table you’re joining from, but you have flexibility in where tables can be positioned in the hierarchy
COALESCE Handling
-
Looker: Can reference any joined table’s fields in any join condition
-
Omni: Fields from other joined tables can be referenced in join conditions regardless of hierarchy position, giving you multiple valid approaches
Relationship Definition
-
Looker: Relationships defined inline with joins
-
Omni: Relationships defined separately in relationships file, referenced by model
Best Practices
-
Maintain Join Dependencies: Structure your model hierarchy to ensure all referenced tables are available when join conditions are evaluated
-
Test Complex Conditions: Always validate that COALESCE and other complex join conditions work as expected in your Omni environment
When to Use Each Approach
All three approaches will generate valid SQL. Choose based on your data model’s logical relationships and team preferences for model organization.