SQL To Magic ETL Issue

Options

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;

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

Tagged:

Best Answer

  • BenSchein
    BenSchein Domo Product Manager
    Answer ✓
    Options

    @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.

Answers

  • BenSchein
    BenSchein Domo Product Manager
    Options

    @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
    Options

    @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).

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • BenSchein
    BenSchein Domo Product Manager
    Answer ✓
    Options

    @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
    AndreaHenderson Domo Product Manager
    Options

    @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.

    Domo Product Manager for Data Transformation (MagicETL)

  • ArborRose
    ArborRose Coach
    edited June 26
    Options

    @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).

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach
    edited June 26
    Options

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **