No matter what the size of your dataset, DataFlows help you manage your ETL jobs within Domo. But which tool should you use when?
Chances are that someday the data you bring into Domo won’t be in the perfect form. It might be denormalized or have the incorrect level of aggregation. But when a business question requires this imperfect data, you just might need to rely on DataFlows.
As defined by Domo, a DataFlow is “a script that transforms input datasets and outputs new datasets.” So, DataFlows are simply a way to manage your Extract Transform Load (ETL) jobs within your Domo instance. This enables the user to leverage the power of Domo’s cloud to do the transformations without another system in place. For enterprise organizations with larger datasets, the advantage of highly scalable computing resources on demand to process data is very exciting. Very, very exciting.
There are two generic types of DataFlows available within Domo today: GUI- based and SQL- based.
The graphical user interface based ETL processing tool is called Magic ETL and has a very low barrier to entry. As long as you understand basic data organization and can drag icons onto a field, you should be able to figure out how to use Magic. This tool comes with capabilities to join data, stack data, and do some column based math, all through an easy-to-use GUI.
However, what Magic lacks is true SQL translation capabilities. So, if you are a SQL jockey looking to do a subselect, you’ll be more comfortable using MySQL or RedShift.
The two SQL-based options are MySQL and Redshift, with MySQL being the standard SQL environment. Each provides access to a SQL editor, where you have access to the functionality of either database.
Having trouble deciding between MySQL or Redshift? We suggest that MySQL be used when 1-3 million rows are being transformed and Redshift when over 3 million rows are being transformed.
Both MySQL and Redshift support the system functions of their respective databases, allowing you to do cool stuff like this:
, @curRow := @curRow + 1 AS RowNumber
SELECT @curRow := 0
Magic ETL and the SQL systems allow transformation of data inside the Domo cloud, leveraging their scalable computing resources. While the SQL systems offer more flexibility in terms of their functionality, Magic ETL certainly is more approachable to a less technical audience.