How to reduce Magic ETL run time
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.
Best Answers
-
Have you tried using the performance tiles that are provided in Magic? They should work perfectly for this. Here's the documentation for them:
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!0 -
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Have you tried using the performance tiles that are provided in Magic? They should work perfectly for this. Here's the documentation for them:
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!0 -
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive