SQL To Magic ETL Issue

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;