Intro
This article describes how to configure the Combine Data tiles in Magic ETL. Learn how to create a Magic ETL DataFlow.
- Append Rows tile
- Join Data tile
- Split Join tile (limited release)
Append Rows Tile
Note: You can only append data rows from input DataSets having the same data structure and data type. If the data structures do not match, the Append Rows tile creates extra columns to accommodate the non-matching data. For example, if you tried to append text to a whole numbers column, two columns would be created—one for the whole numbers column and one for the text column.
Use Case
For example, consider this transform flow.
Note: When previewing, the transformed data might not sort rows in the order you expect. You can control the row order when displaying data in table cards.
Configure the Append Rows Tile
- Select the Append Rows tile on the canvas to open the tile editor.
-
(Optional) At the top of the tile editor, rename the tile by selecting
Edit and entering the name you want.
-
In the Configuration tab of the tile editor, select the dropdown in step 1 and choose which columns to include in the append from the following options:
- Include columns from DataSet — Select one of the input DataSets to include its columns.
- Include all columns — Includes unique columns from all of the input DataSets.
- Only include shared columns — Includes only columns that are used in every input DataSet.
- Choose how to handle when column types don’t match: Use best compatible data type OR Display a type error.
- Review changes to be made to each DataSet.
-
Select Done in the top right corner of the tile editor.
Tip: You can preview the data transformed by a tile by selecting
Run Preview. When the transform is complete, the results are available in the Preview tab of the tile editor.
Appending Rows in Magic ETL (video)
Join Data Tile
Configure the Join Data Tile
- Connect the two input DataSets you want to combine to a Join Data tile on the canvas.
- Select the Join Data tile to open the tile editor at the bottom of the screen.
-
(Optional) In the tile editor, rename the tile by selecting
Edit by the name field and entering the name you want.
-
In step 1 of the editor, select a DataSet from the Left Table dropdown. (The Right Table is automatically populated by the other DataSet.)
In the Join Type dropdown, select the type of join to perform. Learn about the available join types below.
-
In step 2 of the editor, define the join criteria. There are two options:
- Join columns from each table together based on matching values with an Equality condition.
- Write your own expression for how to join the data with an Expression condition.
-
In step 3 of the editor, make any changes to output columns. Magic ETL will alert you of some conflicts, such as column naming.
-
Select Done when you’re finished in the editor.
Tip: You can preview the data transformed by a tile by selecting
Run Preview. When the transform is complete, the results are available in the Preview tab of the tile editor.
Add an Equality Condition
If you choose + Equality condition in step 2 of the tile editor, use the dropdowns to choose a pair of columns from the left and right tables that can be joined based on matching values. You can add as many column pairs as needed by selecting + Equality condition again.
Write a Join Expression
If you choose + Expression condition in step 2 of the tile editor, use the freeform field to write an expression that identifies the relationship between the tables.
Expand (diagonal arrows icon) to open the formula editor so that you have more room to compose your join expression.
You can use inequality operators such as (such as <, >, >=, and <+); keywords that allow you to indicate the relationship as a range (like BETWEEN); and other functions you might use in a SQL-based join.
Select 
Validate formula (circular arrows icon) to validate your join expression before running your DataFlow.
Join Types
This table describes the available join types.Join | Description |
|---|---|
Inner | Includes only matching rows in both input DataSets. |
Left Outer | Includes all rows from the first input DataSet (on the left) and matching rows from the second input DataSet (on the right). |
Right Outer | Includes all rows from the second input DataSet (on the right) and matching rows from the first input DataSet (on the left). |
Full Outer |
Join Data Configuration
The gear
next to the Join Type dropdown menu provides you with the option to restrict your joins to one of several types:
- Many to Many (Not Restricted)
- Many to One
- One to Many
- One to One
Split Join Tile (Limited Release)
If you have a Domo integration with Snowflake or Databricks, you can access the Split Join tile in Magic ETL. This tile allows you to have multiple output streams from a join in your DataFlow. It is useful when you want to see the results of a join and when rows didn’t join from the left or right tables.Configure Split Join Tile
- Drag a Split Join tile to the canvas from the Combine Data section of the left panel. This tile has a bigger footprint on the canvas than a standard tile, and it grows as you add more output streams.
-
Connect the left and right tables on your canvas to the tile.
-
Configure your join and column handling in the tile editor as you would with a regular
Join Data tile.
-
Under Output Names, check the boxes next to the output you would like. You can rename them.
The outputs you select display on the canvas in multiple nodes.
- Build your downstream DataFlow from any of the outputs.