How to reduce Magic ETL run time

Harsh

Hi Everyone.

I'm looking for a way to reduce the run time of Magic ETL 2 data flows. The ETL takes about 3 hours 30 minutes to run I'm working with several left joins and formulas in sequence on about 12 datasets of 10k rows each which generates an output of 30k rows. Beyond this, the ETL performs no other complex operations.

 

brycec

Have you tried using the performance tiles that are provided in Magic? They should work perfectly for this. Here's the documentation for them:

https://domo-support.domo.com/s/article/4405156517527?language=en_US

GrantSmith

When I'm developing ETLs I like to be a teapot - Short and Stout.

Short

Reduce the dataset size to the smallest you need before doing any more processing. Have a filter tile be your first tile or use a Dataset View to pre-filter your dataset and then use that as your input (this will cause less data to be transferred to Magic ETL engine reducing the execution time. Also by using an Alter Columns or Select Columns to only use the columns you need for the ETL will also help reduce the amount of data the ETL needs to process

Stout

This doesn't mean make your dataset wide with a bunch of columns but we want to be wide and leverage Magic ETL's parallel processing powers. Rather than having a single pipe that you're joining multiple datasets to one at a time do multiple joins or multiple different operations on the datasets before you do a join. For example if I have four tables, customers, orders, order line items, products. Instead of Joining customers to orders and then joining that output to line items and then joining that output to products you'd instead join Customers and orders together in one pipe and at the same time join the line items to the products and then joining those two outputs together. This will allow Domo to do multiple operations at the same time reducing your processing time.

brycec

Have you tried using the performance tiles that are provided in Magic? They should work perfectly for this. Here's the documentation for them:

https://domo-support.domo.com/s/article/4405156517527?language=en_US

GrantSmith

When I'm developing ETLs I like to be a teapot - Short and Stout.

Short

Reduce the dataset size to the smallest you need before doing any more processing. Have a filter tile be your first tile or use a Dataset View to pre-filter your dataset and then use that as your input (this will cause less data to be transferred to Magic ETL engine reducing the execution time. Also by using an Alter Columns or Select Columns to only use the columns you need for the ETL will also help reduce the amount of data the ETL needs to process

Stout

This doesn't mean make your dataset wide with a bunch of columns but we want to be wide and leverage Magic ETL's parallel processing powers. Rather than having a single pipe that you're joining multiple datasets to one at a time do multiple joins or multiple different operations on the datasets before you do a join. For example if I have four tables, customers, orders, order line items, products. Instead of Joining customers to orders and then joining that output to line items and then joining that output to products you'd instead join Customers and orders together in one pipe and at the same time join the line items to the products and then joining those two outputs together. This will allow Domo to do multiple operations at the same time reducing your processing time.