Performing Joins
Overview
Performing a join involves the combination of multiple tables within a database. In Osmos, once you have created your Dataset(s) and have added at least two Tables, you are ready to perform a look-up.
A Join is created in Osmos Datasets and is managed through the Osmos Query Builder.
Scenario
A user has three types of insurance source files that they wish to combine into a master file using join logic. This can be achieved with Osmos Query Builder in Datasets.
Features
Datasets
Datasets Query builder
Inputs
The user will need to upload their three source files to three respective dataset tables. Note, that these tables need to reside within the same Dataset.
Claims Table - this will serve as the base of our lookup as the user will be combining the relevant information from Policies and Customers tables to create a master Claims document. To achieve this the Claims Table must have fields that can be used to reference the data which will be joined. In this case, the Claims Table has foreign key relationships with both the Policies and Customers tables.
Policies Table - includes the specific Policy information the user wants to associate with a Claim and the primary key associated with the Claims table's foreign key.
Customer Table - includes the specific customer information the user wants to associate with a Claim and the primary Key associated with the Claims table's foreign key.
Foreign key relationships are the safest way to ensure referential integrity among Joins, but note that Joins can be made based on any values within tables, see Tips for more.
Configuring the Join
Open Osmos Query builder by selecting the table you would like to query.
Select the table(s) you would like to join to your original table, the type of join you would like to use, and the fields to be joined on. In this example, the inner join will result in showing only the records where the Policy Holder ID from the Customers table is matched to the same Policy Holder ID in the Claims table, and the Policy number from the Policies table is matched to the same Policy number in the Claims table.
With all three tables joined, the user can click Select Columns to specify which fields to include in the output.
With the proper fields selected the user can now export their queried data and/or use this query in a pipeline to export data on a cadence.
Tips
As stated above, while foreign key relationships between tables are not required, they are highly suggested, as with more lenient approaches joins can create bad data in your output. Joins do not operate the same way as many other operations that selectively combine or append data, like lookups in Osmos Uploaders or Pipelines, or vlookups in Excel.
With Osmos lookups
When a lookup cannot find a match between a lookup field in your source and a matcher field in your lookup table, it results in an error
When a lookup finds multiple matches to a lookup field in the lookup table, it results in an error
With Excel vlookup
When a vlookup cannot find a match between a lookup value and a value in your table array, it returns an error (
#N/A)When a vlookup finds multiple matches to a lookup field in the lookup table, it returns the first matched field it can find and ignores other matches
With Joins
When a join cannot find a match between fields specified in a join, rows will be output or not output based on the type of join selected
When a join finds multiple matches between fields specified in a join, it may have multiple rows of output for a row that began as a single input, creating a new record for each type of combination the matches could create
Last updated
