LogoLogo
Back to OsmosDeveloper DocsOsmos BlogWhat's New
  • Welcome to Osmos
  • 👋Getting Started with Osmos
    • Terminology
  • 🎉What's New
  • 🧩Osmos API Reference
  • ⌨️Osmos Chat
  • 👩‍💻Developer Docs
    • Manage API Keys
    • Embedding an Osmos Uploader
    • Embedding Uploader Jobs Table
    • Turning on Advanced Mode Uploader
    • Customizing Uploader Styling
    • Passing Parameterized Fields
    • Configuring Uploader's "Recall" functionality
    • Optional Uploader Settings
    • Uploader Submission Callback
    • Configuring AutoClean for your Uploader
    • Uploader Client-Side Validation
      • Data Validators
      • Checking for Duplicate values in a field
      • Creating Dropdown-Controlled Fields
      • Dynamic Dropdown Options
      • Dropdown Interaction with Validation Functions
    • Validation and Transformation Webhooks
      • OpenAPI Validation Webhook Testing
    • Parser Webhook for file based connectors
  • 🔠Datasets
    • Osmos Datasets
      • Uploading Data to your Table
      • Creating Primary and Foreign keys
      • Osmos Dataset Destination Connector
      • Osmos Dataset Source Connector
      • Dataset Edits
    • Datasets Query Builder
      • Query Builder Metadata
    • Performing Look Ups
      • Performing Joins
        • Types of Joins
  • ⏏️Uploader
    • Creating an Osmos Uploader
      • Testing your Osmos Uploader
    • Uploader Validation Summary
    • Advanced Mode
      • Overview
      • Process
    • Standard Mode
      • Overview
      • AutoClean
      • Process
    • AI AutoMapping
    • Uploaders Page
    • Uploader Details Page
  • 🔀Pipelines
    • Step 1. Select the Source
    • Step 2. Select a Destination
    • Step 3. Map & Transform Data
    • Step 4. Schedule the Pipeline
    • Step 5. Review & Confirm
    • Pipelines Page
    • Pipeline Details Page
  • ⏩Data Transformations
    • AutoMap
    • Column Mapping & Data Cleanup Panel
    • QuickFixes
    • AI Value Mapping
    • AI AutoClean
    • Lookups
      • Performing Lookups
    • SmartFill
    • Formulas
      • Date & Time Formulas
        • DateTime Format Specifiers
        • Timezone specifiers
      • Math Formulas and Operators
      • Logical Formulas & Operators
        • True & False Casting
      • Text Formulas
      • Other Formulas
    • Deduplication
  • ↘️Source Connectors
    • Amazon S3
    • Azure Blob Storage
    • BigQuery
    • Email
    • FTP
    • Google Cloud Storage (GCS)
    • Google Drive
    • Google Sheets
    • HTTP API (Call an Osmos API)
    • HTTP API (Osmos Calls Your API)
    • Osmos Dataset
    • Snowflake
    • Accessing Sources behind firewall
  • ↖️Destination Connectors
    • Amazon S3
    • BigQuery
    • FTP
    • Google Cloud Storage (GCS)
    • Google Drive
    • Google Sheets
    • HTTP API (Call an Osmos API)
    • HTTP API (Osmos Calls Your API)
      • Passing Dynamic Tokens in the API Header
    • MySQL
    • Osmos Dataset
    • PostgreSQL
    • Snowflake
    • Accessing Destinations behind firewall
  • 🗂️Projects
  • ⚙️Administration
    • Email Notifications
  • 🔒Security
  • 📞Support
  • Back to Osmos.io
Powered by GitBook
On this page
  • Overview
  • Scenario
  • Features
  • Inputs
  • Configuring the Join
  • Tips

Was this helpful?

  1. Datasets
  2. Performing Look Ups

Performing Joins

PreviousPerforming Look UpsNextTypes of Joins

Last updated 10 months ago

Was this helpful?

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

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.

  1. 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.

  2. 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.

  3. 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.

Configuring the Join

  1. Open Osmos Query builder by selecting the table you would like to query.

  1. With all three tables joined, the user can click Select Columns to specify which fields to include in the output.

  1. 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

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 for more.

Select the table(s) you would like to join to your original table, the 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.

🔠
type of join
Tips
Osmos Query Builder.