All Collections
Data
Understanding Join direction and quality
Understanding Join direction and quality
Liron Baram avatar
Written by Liron Baram
Updated over a week ago

Understanding Join direction and quality

The foundation of join quality is based on ensuring your joins always use the fields containing multiples as the foreign key, with the unique field in your master table as the primary key.

The join quality table includes stats to explain how join quality is assigned to the join you've created. The join quality table includes these stats:

  • Keys count - number of unique values in the keys used in the join.

  • Rows count - number of overall rows in the table.

  • Key quality - Keys count divided by Rows count. We expect to see 100% on the master table on a one-to-many join. This indicates a good quality for the join.

  • Orphans - number of values from the Keys count that don't have a corresponding value in the other table. This information can help to ensure you've connected the correct fields. Usually, you expect to see close to zero on the join quality table.

Join Quality statistics showing 100% for Key Quality and zero Orphans.

Figure 5: Join Quality statistics showing 100% for Key Quality and zero Orphans.

Joins Quality and business value

Checks the key and counts the records to ensure the relationship is one-to-many or many-to-one, as appropriate for the data.

We also ensure that the relationship for your keys are in the 'right direction.' It helps to reduce the likelihood of displaying incorrect data in your chart.

For example, a 'null' response indicates that your chart may be displaying information that doesn't match the data keys that you have.

Join suggestions displays

Previewing Join Quality suggestions

Mouse over each suggestion to preview the join quality suggestion

Applying join suggestions

If you're happy with all the suggestions, click Apply All.

The user can apply one specific suggestion by clicking on the check icon. The join will be added immediately.

By clicking on "Apply all" the user applies all the selected joins.

Defining and manually applying data joins

Define Data Joins >

Drag and drop > one connection per field.

Creating the joins (keys) between your tables

To add more joins > add from the Data Joins tab

Managing data joins

Number of suggestions

At the top, we always show the number of available suggestions we have for the user to choose from. This is visible even when the box is contracted.

Number of selected joins

All suggestions are preselected and are shown under the number of suggested joins. This is visible even when the box is contracted.

Dismiss a suggestion

The user can dismiss a specific suggestion by clicking the "X" icon. That join won't be included if the user clicks the "Apply all" button.

View full field/table name

In situations in which the field/table name is too long, we will display the full name in a tooltip. The maximum width of the tooltip is 230px **how many characters is that?**.

Hover

When hovering over an individual suggested join, the user should be able to identify that join in the table view (dotted spaghetti).

Expand / contract list

The user should always be able to contract and expand the suggestions list again by clicking on the arrow icon at the top right.

Join direction

To improve understanding and support troubleshooting your data model output, Astrato represents the join direction in your data view with an arrow that always starts from the ‘many’ side to the ‘one’ side.

Our data engine applies the UNION ALL operations by merging the two SELECT statements and then integrating the outcome by integrating a further SELECT statement with a GROUP BY aggregation to remove the duplicates.

Figure 3: Join direction showing the arrow always begins from many to one.

Please note: The engine's decision is uniquely based on the direction of the arrow and not on the data itself. That’s why the direction of the joins is so essential.

Did this answer your question?