Allow SQL translations for more Prepare processors, save/crowdsource translations

natejgardner

Using SQL engine for a Prepare recipe can significantly improve performance. Not all processors can be translated to SQL, but the formula processor has a special field to fill in equivalent SQL. I often run into processors that don't have built-in translations (or whose built-in translations throw errors) and break out the formula processor to provide a translation.

It would be wonderful if I could provide custom SQL for processors directly and then save these translations for reuse by my team. For example, using the transform string processor, I get the error "TRIM mode is not translatable" when selecting the trim leading/trailing whitespace option. In Teradata, this can be translated to TRIM(my_column). The current workaround is to delete the processor, add a formula processor, and provide the SQL translation. I'd love to be able to keep processors where they are, add my translation, then have the option to save it for reuse throughout my projects. That way, it could be automatically applied to existing recipes to make them SQL-compatible, and my whole team would have access to translations I wrote. I'd also like to be able to see and modify the SQL translations of processors that do have SQL support as well.

On a related note, it would also be great if I could automatically identify all prepare recipes using DSS Engine that are fully SQL translatable and select which ones to bulk switch to SQL. It would also be very helpful to be able to see the SQL translation of any processor in the processor library pane, as well as the final SQL generated by a prepare recipe when using the SQL engine.

tgb417

@natejgardner

All of these are great ideas. Thanks for submitting this Product Idea.