SQL To Magic ETL Issue

ArborRose

Domo's use of mySQL as an ETL is frustrating slow. I have a solution that runs in seconds when I duplicate it in MS SQL, but when applied in Domo it takes forever.

I need to figure out how to do the following with tiles in Magic ETL. I have created an example using store locations as anonymized data.

Assume I have a table of transactions for dates from 2022 through today. I have a rate table that defines the amount that should have been billed. When I apply the rate table (price rates) against transactions, I can see whether the correct amount was billed (result set).

In SQL, I can use a query like this:

SELECT t.Company, t.Store, t.Sale_date, t.Code, t.Amount, r.Rate

FROM #Transactions t

LEFT OUTER JOIN #PricingRates r ON t.Company = r.Company

AND (

(r.Store IS NULL) OR

(t.Store IS NOT NULL AND t.Store = r.Store)

)

AND t.Code = r.Code

AND t.Sale_date >= r.effective_date

AND t.Sale_date < r.next_effective_date;

My problem is in duplicating the join condition. Sometimes the rate table has the store location(s) specified. In those cases, it must join on the store. If the store isn't specified, it must ignore the store in the join. And if there is no rate specified, it must remain null. I tried splitting into two filtered paths but somehow didn't get the right resultset.

The following SQL creates the example I show.

-- Create Transactions temporary table

CREATE TABLE #Transactions (

Company varchar(50),

Store varchar(50),

[Sale_date] date,

Code varchar(50),

Amount integer,

Rate varchar(50)

);


INSERT INTO #Transactions (Company, Store, [Sale_date], Code, Amount) VALUES ('CompanyA', 'Store1', '2022-02-01', 'CodeX', 800);

INSERT INTO #Transactions (Company, Store, [Sale_date], Code, Amount) VALUES ('CompanyA', 'Store1', '2022-07-01', 'CodeX', 800);

INSERT INTO #Transactions (Company, Store, [Sale_date], Code, Amount) VALUES ('CompanyA', 'Store1', '2023-03-01', 'CodeY', 800);

INSERT INTO #Transactions (Company, Store, [Sale_date], Code, Amount) VALUES ('CompanyB', 'Store2', '2023-05-08', 'CodeY', 1500);

INSERT INTO #Transactions (Company, Store, [Sale_date], Code, Amount) VALUES ('CompanyA', 'Store2', '2023-03-01', 'CodeY', 1100);


-- Create PricingRates temporary table

CREATE TABLE #PricingRates (

Company varchar(50),

Store varchar(50),

Code varchar(50),

Rate integer,

effective_date date,

next_effective_date date

);


INSERT INTO #PricingRates (Company, Store, Code, Rate, effective_date, next_effective_date) VALUES ('CompanyA', 'Store1', 'CodeX', 800, '2022-01-01', '2022-05-31');

INSERT INTO #PricingRates (Company, Store, Code, Rate, effective_date, next_effective_date) VALUES ('CompanyA', 'Store1', 'CodeX', 900, '2022-06-01', '2022-12-31');

INSERT INTO #PricingRates (Company, Store, Code, Rate, effective_date, next_effective_date) VALUES ('CompanyA', 'Store1', 'CodeX', 1000, '2023-01-01', '9999-12-31');

INSERT INTO #PricingRates (Company, Store, Code, Rate, effective_date, next_effective_date) VALUES ('CompanyA', 'Store2', 'CodeX', 1000, '2024-01-01', '9999-12-31');

INSERT INTO #PricingRates (Company, Store, Code, Rate, effective_date, next_effective_date) VALUES ('CompanyB', NULL, 'CodeY', 1500, '2022-01-01', '2023-06-30');

INSERT INTO #PricingRates (Company, Store, Code, Rate, effective_date, next_effective_date) VALUES ('CompanyB', NULL, 'CodeY', 1600, '2023-07-01', '9999-12-31');


SELECT * FROM #Transactions;

SELECT * FROM #PricingRates;

BenSchein

@ArborRose sorry, I scanned too quickly there. In addition to the SQL tile in magic, there is also a SQL based join option that I think would help you achieve this in magic. We had it in beta but the UX was confusing so just finishing up some changes to that now. I think in general these two features would let you get to everything you need. @AndreaLovesData would have more details on when those may come out of beta. hopefully this fall.

BenSchein

@ArborRose are you using the SQL tile in Magic? I believe if you use the WITH you can essentially create these temporary tables in one magic SQL tile. Still in beta I know.

ArborRose

@benschein - Those temporary tables are data examples to represent what I'm doing. My actual data is quite different (medical transactions and insurance rates). I don't have a SQL tile in Magic ETL (not signed up for beta anything).

BenSchein

@ArborRose sorry, I scanned too quickly there. In addition to the SQL tile in magic, there is also a SQL based join option that I think would help you achieve this in magic. We had it in beta but the UX was confusing so just finishing up some changes to that now. I think in general these two features would let you get to everything you need. @AndreaLovesData would have more details on when those may come out of beta. hopefully this fall.

AndreaHenderson

@ArborRose We're looking to restart the freeform "ON" clause beta for Magic this month. Both SQL tile and the "ON" clause should be out of beta later this summer / early fall. The join you're describing is definitely messy to create in Magic without the SQL Tile or freeform "ON" clause, but I think it can be done. I'm going to play with the SQL and sample data you've provided and see if I can figure out how to create it in Magic. I'll follow up here with what I learn.

ArborRose

@AndreaLovesData I appreciate your help. If you message me your email, I can provide you a better example. Just like the one I posted but with actual data (no private information).

ArborRose

I may have a working solution. I will need to replace my test datasets with actual data and validate numbers.