Magic ETL – Aggregating and Joining Data

January 23, 2022

Domo’s Magic ETL tool is a very powerful and useful tool that allows you to combine and transform your data, allowing you to create new datasets that can be used for visualizations. This allows you to automate processes that you might try and do outside of Domo via Excel or other tools. If you haven’t spent time using Magic ETL, I would encourage you to get familiar with the different tiles that are available to you and discover how they may be of use to you.

Two of the most commonly used tiles in Magic ETL are the Group By tile and the Join Data tile. The Group By tile allows you to aggregate data, such as sum, count, average, minimum or maximum. For example, if you had a dataset of claims data, you could count the number of claims in a year and the total cost of the claims using the count and sum aggregators, respectively.

The Join Data tile allows you to combine two datasets together on a unique column, allowing you to use data from both datasets. The Join Data tile allows you to include all records from one table and only the matching records from another table (left join), include all records from both tables (full join), or just the matching record from both tables (inner join). For example, you may want to join your claims dataset with your policy premium dataset to be able to analyze who has incurred more losses than the premium that they have paid. In this case, you would use a left join to include all of the premium records and include the matching claims data when they had claims during that policy period.

To learn more about the Group By and Join Data tiles, read through these Domo knowledgebase articles.