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

LCash
LCash Member
in Magic ETL

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!

Best Answers

  • swagner
    swagner Contributor
    Answer ✓

    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
    MarkSnodgrass Coach
    Answer ✓

    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)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    +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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • swagner
    swagner Contributor
    Answer ✓

    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
    LCash Member

    Thanks @swagner, that should do it!

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    +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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • LCash
    LCash Member

    Thanks @MarkSnodgrass and @jaeW_at_Onyx !

Categories