Behavior Changes and Feature Updates in Magic ETL

​

Intro

• Performance
• New/Updated Tiles
• User Interface
• Advanced Options

  ​

  Why Upgrade?

  We wanted to make Magic ETL more internally consistent and more aligned with other Domo features like Beast Mode. One of our goals was to eliminate all cases where Magic ETL resolves column name collisions without the user’s input. Joins and Append Rows both deal with name collisions, and Magic ETL now deals with both of them differently. We heard from many customers that this change is important for data cleanliness. Rethinking auto-rename is just the start. Magic ETL now features a new formula system with over 200 functions inspired by Domo Beast Mode, various SQL databases, and Spreadsheet software. Formulas are a game-changer for Magic ETL. The functionality offered by the formula system goes beyond expressing a cluster of traditionally-configured tiles more concisely. In some cases, it even surpasses what is possible in a MySQL DataFlow. Formulas can be used to edit columns in place or add new columns. or as part of a Group By or Filter Rows tile, to construct complex aggregations or filter conditions. For more information, see Writing Formulas in Magic ETL. For the complete list of supported functions available in the new formula system, see Supported Functions in Magic ETL.

  ​

  Notable Behavior Changes

  Some changes in Magic ETL might cause transforms to work differently. This could manifest as errors like “Column not found” or “Duplicate column name”, or more subtly as different output data. As such, it is important to be aware of all of these changes before migrating an existing DataFlow to Magic ETL v2 or before writing a new DataFlow if you are already very familiar with Magic ETL’s previous behavior.

  ​

  Filter Rows

  Null values are treated differently by the comparison functions. In SQL and Magic ETL v2’s DataFlows, the result of any comparison with null is always null, which is treated by the Filter Rows tile like a false result. In most contexts in Magic ETL v2, null is not equal to null, but null is also not not equal to null; null is not greater than zero, but it is also not less than zero, nor is it equal to zero. If you have nulls in columns referenced by Filter Rows tiles, all of those rows will be dropped, regardless of the function or its other argument. The only functions that can deal with nulls truthfully are “is null” and “is not null”. In Magic ETL v1, a filter might have tested “column = null”. This will no longer have the same result; such filters should be changed to use the “is null” operator. If after switching a flow to Magic ETL v2, it runs successfully, but the output seems to be missing rows, this change is a likely culprit. It can be especially surprising with the “is not equal to” function. It might seem like “column <> 5” should only drop rows where column’s value is 5, but in fact it will also drop rows where column is null, for the reason described above. The table below has additional examples of null behavior in Filter Rows.

  Test	Evaluated Result	Filter Effect
  4 <> 5	true	keep
  5 <> 5	false	drop
  null <> 5	null	drop
  null = null	null	drop
  null <> null	null	drop
  null is null	true	keep
  null is not null	false	drop
  5 is not null	true	keep
  5 is null	false	drop

  To handle nulls in more complex ways, use the new formula system. The Filter Rows tile is one of three tiles (Group By and Add Formula being the other two) which supports formula evaluation. The IFNULL() function works particularly well for handling a null result from a filter expression. For example, if column “col” has null values, and we want to filter for rows whose value for “col” is not 5, preserving our nulls, we could write this expression: IFNULL(col <> 5, TRUE) Besides null handling, there are many other scenarios that filter formulas make easier. In Magic ETL v1, the single formula below would have required two separate Filter Rows tiles with multiple conditions each.

  ​

  Date and Time Parsing

  With date and time parsing, the process of converting incoming text into Date or Date & Time values has changed significantly. Generally, it is stricter than it used to be. Some text that parsed successfully in Magic ETL v1 might result in an error in Magic ETL v2. The goal of being stricter is avoiding scenarios where a value is silently misinterpreted, like a month being mixed up with a day. Magic ETL now uses a fixed list of unambiguous date and time formats, plus a locale-specific list of ambiguous formats (e.g. “01/02/2020” is January 2nd in the US and February 1st in most other places). If Magic ETL fails to parse a date, a custom format can now be specified on the Input DataSet tile, on a per-column basis. For information on the format specification, refer to Oracle’s documentation of the Java DateTimeFormatter class.

  ​

  Date and Time Operations

  Date and time operations in Magic ETL are now performed by default in the company timezone specified in the Domo Admin menu. Magic ETL v1 performed these operations in UTC, the international standard time. This means that a company whose timezone is America/Denver that uses the “Month of date” operation on a Date & Time column will get the month of that timestamp from Denver’s perspective, not that of Greenwich (UTC). The WEEK_OF_YEAR() function in the Date Operations tile is also now different. Week 1 is the first week with a Sunday in the year. This differs from previous versions of Magic ETL and Beast Mode, where week 1 is the first partial week. Using the Add Formula tile, week behavior can be adjusted: the WEEK() function accepts a second argument: an integer from 0 to 7, representing the “week mode” to use when calculating the week of a date. These modes are described in Oracle’s MySQL documentation, and are reflected in the behavior of Beast Mode. Mode 0 (or WEEK() without the mode argument) yields the same result as the WEEK_OF_YEAR() function in the Date Operations tile.

  ​

  Joins

  There are two major changes to the Join Data tile:
  1. It is no longer important to ensure that the table with fewer duplicate join keys is on the left of the join. The issue in Magic ETL manifesting as the error, “The left input cannot include over 10,000 duplicates”, has been eliminated.
  2. Name conflicts are no longer resolved without user input. Instead, we have added a section to the Join Data tile’s configuration for describing name conflict resolution. This section can be filled out automatically or manually. In addition to renaming conflicting columns, it is now possible to remove them in the Join Data tile’s configuration as well.

  ​

  Append Rows (Union)

  Previously, when two or more columns going into an Append Rows tile had the same names but different data types, they would not be combined. Instead, two or more new columns would be created, with names like “ID Whole Number” and “ID Text”. Now, the Append Rows tile never changes column names. Columns that have different types will have their data converted to the best type possible given the types of all the columns that share a name. For example, if one column had type Whole Number, while another had type Decimal, the columns would be combined into a column with type Decimal. In cases where the types are wholly incompatible, like Dates and Decimals, the resulting column will be Text type. This behavior can be replaced with stricter behavior using a new option in the Append Rows tile configuration. The stricter behavior is to throw an error (i.e. fail the execution) if two columns with the same names have different types.

  ​

  Other Considerations

  Beyond understanding behavior changes in Magic ETL, it is valuable to learn about the increased transform functionality. Those familiar with the previous version of Magic ETL should be sure to try out the following:
  • The new Add Formula tile—which supports over 200 functions, most of which are unique to the new Magic ETL. When using Add Formula for the first time, be sure to expand the formula editor and browse the function list.
  • The Input DataSet tile—which now supports changing the type of incoming columns, as well as configuring how errors and nulls should be handled. If you want to interpret all nulls in a Text column as the Empty String instead, you can now do so.
  • The new Dynamic Unpivot tile—which makes more sense in some scenarios than the older Unpivot (Collapse Columns) tile. If you want to pivot all columns except a few, rather than pivoting a few columns and leaving most alone, consider using this new tile.
  This video details the behavior changes. Video - Upgrading Magic ETL

  ​

  Feature Updates

  ​

  Performance

  Faster Runtimes Magic ETL is now much more performant than the previous version. While each DataFlow’s performance changes may vary, most DataFlows will run significantly faster simply by upgrading to the new version. For detailed instructions on how to do this, see Upgrading Magic ETL. We have even found that Magic ETL now often outperforms many MySQL and RedShift DataFlows.

​

New/Updated Tiles

​

User Interface

​

Advanced Options