Domo's Magic ETL has gotten a major upgrade. Not only has the data processing engine been significantly upgraded making most DataFlows run remarkably faster and in more efficient ways, but new tiles and functionality have been added to make the most out of transforming your data using Magic ETL v2. All of the new features can be divided into four main categories:
The new engine is much more performant than Magic ETL v1. While each DataFlow's performance changes may vary, most DataFlows will run significantly faster simply by flipping the Try the New Magic ETL toggle switch.
We have even found that Magic ETL v2 often outperforms many MySQL and RedShift DataFlows.
DataSet Views as inputs
DataSet Views can now be used as inputs in Magic ETL v2. This allows for easier filtering, aggregating, renaming, or dropping columns, and performs Beast Mode functions prior to bringing your data into Magic ETL v2. Try filtering out all unnecessary rows before bringing your data into Magic ETL v2 for reduced runtimes.
One of the limitations of Magic ETL v1 is that at every DataFlow run, the engine loads all of the input DataSet's data, every time, even if that data is unnecessary for the transformations taking place. Now, with Magic ETL v2, at the start of each run, the system reviews the state of the DataFlow's inputs and outputs. Where possible, only new rows added to the inputs since the last DataFlow execution will be processed during that DataFlow execution. Those rows will then be auto-appended to the output DataSet(s) resulting in the exact same output data but in a dramatically reduced runtime. To learn more about this optimization, see Magic ETL v2 DataFlow Auto Append Processing.
If you're a SQL power user, transitioning to Magic ETL v1 often felt clunky. At times, what took one line of MySQL code can take 15+ mouse clicks in Magic ETL v1. With the Add Formula tile, that frustration is no more. The Add Formula tile is a row-by-row expression evaluator that allows you to write SQL-style syntax directly into your Magic ETL v2 DataFlow. Create and modify your columns with compounded expressions. Case statements, statistical utility functions, and time-value-money operations are all easily accomplishable with this new tile.
The Alter Columns tile is an upgraded version of the Set Column Type tile. Now you can easily rename, remove, or change the data types of your columns in one simple tile.
This new tile is the inverse of the Unpivot tile. If you expect your schema on an input DataSet to change, using the Dynamic Unpivot tile allows you to narrow a table by pivoting all column data except those columns specified into new rows. Any columns now excluded in the configuration will become row values.
One important distinction you need to consider when using the Formula Tile is "What type of transformation am I trying to perform?" If the answer involves aggregations in any way, then you should look to the Group By tile which now supports SQL-style expressions with formula support. The Add Formula tile performs row-by-row operations which does not support aggregations. If you want to use operations like SUM, MEDIAN, or PERCENTILE, then be sure to select the Group By tile to aggregate your data.
The Filter Rows tile now supports SQL-style expressions as well. In Magic ETL v1, performing a compound filter statement required multiple mouse clicks. In Magic ETL v2, complex filter rules are quickly configurable with this expression evaluator. For more information on how to write filter formulas, see How to Write a Filter Formula in Magic ETL v2.
Pivot & Unpivot
What was the Uncollapse Columns tile is now the Pivot tile. What was the Collapse Columns tile is now the Unpivot tile.
Historically, column name collisions were difficult to handle when joining data in Magic ETL v1. In Magic ETL v2, the Join Data tile enables you to easily specify what should happen when duplicate columns names occur. With easy column name conflict resolution and drop columns options, the Join Data tile has never been easier.
Python & R Scripting
Scripting tiles are now much more flexible. Specifying a schema for your output DataSet is now optional. Executing your script provides the needed schema for your output DataSet, saving you time. You can run a preview to generate the schema or specify it when you need it as part of downstream tiles.
Color-Coded, curved lines
In Magic ETL v1, complex ETLs were difficult to follow and understand by viewing the graph alone. Now with color-coded, curved lines, lines are by default colored by data source, allowing you to more quickly grasp the flow of your transformations. Don't like the default colors? You can change them using the color picker.
Notes on tiles
Have you ever come into a complex Magic ETL DataFlow you did not create or that has been running untouched for months? Deciphering what transformations are taking place or even why can sometimes be difficult. With annotate functionality on individual tiles, you can write detailed explanations regarding what the DataFlow is doing on every tile.
New tile categorizations
Finding the specific tile you needed in Magic ETL v1 could be difficult given that the drop-down categories were limited to DataSets, Edit Columns, Edit Data, and Combine Data. Magic ETL v2 has much more detailed categorization: Text, Dates and Numbers, Utility, Filter, Combine Data, Aggregate, and Pivot. The tile tooltips have also been updated to reflect exactly what's new about each tile.
Selectable text in Data Previews
Easily interact with the preview data in a Magic ETL v2 DataFlow with selectable text. Now you can quickly copy and paste individual cells or a group of cells from the Preview or Data tabs.
Enhancements to data type handling
The inclusion of formulas makes Magic ETL v2 a more versatile tool. This new feature highlighted the need to ensure data type handling is configurable upon the initial load of a DataSet as well as throughout the DataFlow. Easily specify what data type a column should be as well as the expected format and what to do when the data is unreadable as that specified data type.
Preview and Data Table on Input DataSet tile
Due to the addition of data type classification upon input, we've introduced a new Preview tab on the Input DataSet tile. Now you can easily see the raw data in the Data tab as well as how that data has changed per our configured transform settings with the new Preview tab.
DataFlow Transform Settings
Magic ETL v2 provides the ability to handle time zones, locales, and collation modes as well as specify default date and timestamp formats for your DataFlow. This is accessible at the DataFlow level as well as the individual tile level when one would need to specify what time zone a tile should be performed in.