Best Of

Have you tried to rebuild your MySQL dataflow in Magic ETL? The latest Magic ETL runs extremely fast and I would expect it to handle 7m in under a couple minutes. You can also do formula rules in the filter tile so that you could filter to month to date at the beginning and then do the rest of your work if you found you needed to try and make the ETL run even faster. If you haven't watched the Domopalooza breakout session called Demystifying Data Pipelines, I would suggest watching that is it gives a nice overview of the different types of data transformation tools and when to use them.

Hope this helps.

It sounds like you have different data granularity in your datasets and that is causing the the duplicate rows. It sounds like your budget data granularity is month/department and your actuals are date/dept/account(?). If you're just joining on department, you're probably getting a cartesian join of every month X every transaction.

You could fix the join by either aggregating your datasets to the same level (i.e. group by on your actuals to get a monthly spend and then join on dept and month for the two datasets).

I would actually do an append instead of a join - this will provide more flexibility, and you can use beast modes in your cards to report as needed but it gives you the flexibility to still be able to drill down to transactional level information if you need to. @jaeW_at_Onyx has a great youtube video that outlines the general methodology https://www.youtube.com/watch?v=PVbOeLSae9o

oh goodness... defo use @MarkSnodgrass method. it's easier to read and stores the data as type DATE which will have more flexibility in cards than type TEXT

@MarkSnodgrass + @pstrauss

before you pivot, make sure to assign a value to how to prioritize the match.

i.e.

matchValue =1 if JOIN on Name

matchValue = 2 if JOIN on Description

matchValue =3 if JOIN on

then Pivot

then JOIN

then RANK on MatchValue desc

then filter where Rank = 1 to only keep one version of the row.

Good lukc!

You could try and use the unpivot tile and then potentially just join once. If you haven't looked into the dynamic unpivot and unpivot tiles, you could see if that would help you long-term. Here's the KB article.

https://domohelp.domo.com/hc/en-us/articles/360044951294-New-Magic-ETL-Tiles-Pivot

@imelendez

Stacker was built back in Magic 1.0 era.

Magic 2.0 should outperform virtually every ETL engine EXCEPT Adrenaline Dataflows and in some cases Data Set Views.

UNLESS you are doing something that a database is MUCH better at (GROUP BYs, Window Functions and JOINs).

But even then, to opt for a SQL dataflow option, the volume of data would have to be so high that it outweighs the amount of time it takes to transfer and load the data into tables (and index) as per @GrantSmith

especially with Magic 2.0+Partitioning almost all the 'enterprise tools' Stacker and Data Assembler should be superfluous

@vikrao what's the business question you're trying to answer in plain english?

i suspect it's something like "which users havent paid an invoice in the last x months.

if so rework your filter as a formula based off CURRENT_DATE