Is it possible to switch a data set to a different dataflow?

LCash

I've got a team who uses the Excel plugin extensively, through which they query a particular dataset. However, this dataset is based on an ETL flow which I'd like to recreate in SQL so that we can iterate on it more quickly. Since they'd have to remake all of their query tables to be based on this new data set, I'm wondering whether it's possible to switch the dataset to the SQL flow to save this hassle

I expect not as I've not found any documentation around it but wanted to double-check!

swagner

If the resulting dataset isn't massive, you could just pass the completed new SQL flow as an input set in the current ETL and keep the same output step that's powering your cards.

MarkSnodgrass

Scott is correct, you can create a new SQL dataflow that does what you want. In your existing ETL, have the input dataset be whatever you called the SQL output dataset and then just connect it directly to the output dataset of your existing ETL. It would result in an extremely basic ETL that just has an input dataset and output dataset, but it would keep your end users having to do anything on their end.

You might need to insert a Select Columns tile to ensure that column names stayed the same as case-sensitivity can be an issue.

Also, if you don't have Magic ETL 2.0 yet, you should ask your CSM to enable it. It runs a lot faster and you have more capabilities than before. Here's a link to the KB article that leads to more KB articles https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/Creating_a_Magic_ETL_v2_DataFlow_(Beta)

jaeW_at_Onyx

+1 to @MarkSnodgrass

It is tempting for SQL developers to want to use MySQL and Redshift (and fair enough Magic may not seem intuitive), but it keeps the SQL developers as the bottleneck both in performance and distribution of workload (they become the only people who can implement changes on dataflows) -- job security, but more midnight phone calls.

I recommend building up Magic skills b/c pound-for-pound it will run faster than MySQL with less effort spent on performance optimization (you don't have to index Magic, whereas your performance in MySQL is dependent on your DBA skills). Domo will continue to invest in improving Magic performance and enriching features; whereas MySQL is currently on 5.6 (which for example doesn't support CTEs or Window Functions).

Also, if you want to hand ownership of datasets to the business, or share projects between users, handing over MySQL transforms between users can be more difficult than Magic, especially if SQL devs don't bother to comment or format their code.

Magic 2.0 is worth a look as it adds the FORMULA tile which allows you to create SQL-esque SELECT statements in an ETL pipeline (which massively simplifies building CASE statements).

----

to answer your original question, yes, you can change the output dataset of an ETL to point at a different dataset using the Java CLI

https://knowledge.domo.com/Administer/Other_Administrative_Tools/Command_Line_Interface_(CLI)_Tool#section_34

https://www.youtube.com/watch?v=ysUHYaICYD8

swagner

If the resulting dataset isn't massive, you could just pass the completed new SQL flow as an input set in the current ETL and keep the same output step that's powering your cards.

LCash

Thanks @swagner, that should do it!

MarkSnodgrass

Scott is correct, you can create a new SQL dataflow that does what you want. In your existing ETL, have the input dataset be whatever you called the SQL output dataset and then just connect it directly to the output dataset of your existing ETL. It would result in an extremely basic ETL that just has an input dataset and output dataset, but it would keep your end users having to do anything on their end.

You might need to insert a Select Columns tile to ensure that column names stayed the same as case-sensitivity can be an issue.

Also, if you don't have Magic ETL 2.0 yet, you should ask your CSM to enable it. It runs a lot faster and you have more capabilities than before. Here's a link to the KB article that leads to more KB articles https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/Creating_a_Magic_ETL_v2_DataFlow_(Beta)

jaeW_at_Onyx

+1 to @MarkSnodgrass

It is tempting for SQL developers to want to use MySQL and Redshift (and fair enough Magic may not seem intuitive), but it keeps the SQL developers as the bottleneck both in performance and distribution of workload (they become the only people who can implement changes on dataflows) -- job security, but more midnight phone calls.

I recommend building up Magic skills b/c pound-for-pound it will run faster than MySQL with less effort spent on performance optimization (you don't have to index Magic, whereas your performance in MySQL is dependent on your DBA skills). Domo will continue to invest in improving Magic performance and enriching features; whereas MySQL is currently on 5.6 (which for example doesn't support CTEs or Window Functions).

Also, if you want to hand ownership of datasets to the business, or share projects between users, handing over MySQL transforms between users can be more difficult than Magic, especially if SQL devs don't bother to comment or format their code.

Magic 2.0 is worth a look as it adds the FORMULA tile which allows you to create SQL-esque SELECT statements in an ETL pipeline (which massively simplifies building CASE statements).

----

to answer your original question, yes, you can change the output dataset of an ETL to point at a different dataset using the Java CLI

https://knowledge.domo.com/Administer/Other_Administrative_Tools/Command_Line_Interface_(CLI)_Tool#section_34

https://www.youtube.com/watch?v=ysUHYaICYD8

LCash

Thanks @MarkSnodgrass and @jaeW_at_Onyx !