Extend Magic FILTER to use a value calculated from another table
I'm creating a Magic ETL to fix DCM "attribution window". The algorithm is
- Get the last 14 days and save this as "DCM rolling 14 days".
- Load the existing "DCM history" file.
- Filter "DCM history" where Date < MIN(date) in "DCM rolling 14 days".
- Append 3 with the "DCM rolling 14 days".
- Write this out as the "DCM history" file.
In SQL this can be easily written as
SELECT * FROM prod_dcm
WHERE Date < (SELECT MIN(Date) FROM dcm_14_days_rolling LIMIT 1)
SELECT * FROM dcm_14_days_rolling
This is difficult in Magic as Filter can't use a calculated value from another table. The Magic implementation typically requires the use of a groupby and join and looks like this
The difference in performance is about 2x.
I'd like to see FILTER extended so it can use a value calculated from another table so I can easily write the attribution window transformation and have ok performance.