Are there best practices or standardized syntax for writing transformation logic

business-intelligencedata-warehousedocumentationetl

This may not be the appropriate forum, but its the closest I could find. I write a lot of source to target mappings for work. These documents just describe the operations/logic/transformations/etc. needed to get the data from A to B. I find myself phrasing the same thing different ways, or sometimes the descriptions get convoluted if the logic is complex.

Are there any best practices for writing the transformation logic for relational databases? For example, how to say 'Join tables A,B, and C for each combination of fields D,E, and F select the maximum value between fields G and H.' I feel that somebody has probably standardized the natural language for relational database transformation logic, but I can't find anything to that effect.

Best Answer

SQLish

Honestly, your best bet is probably ANSI-compliant SQL, because it can eliminate the ambiguities which you've mentioned.

If there's any trouble with it needing to be written in "natural language", just add in the apprpriate prepositions and such...

Select the maximum paid amount, the customer account number, and the customer phone number 
from the 'Invoices' table joined to the 'Customers' table, 
joined on the condition that 
the 'Invoices'.customer_id column equals the 'Customers'.customer_id column, 
grouping by the customer account number and customer phone number.
Related Question