ETL custom transformations

[mahalakshme]

Issue:

• Frequently clients complain that reports are slow and that when they want to showcase to government or funders they feel bad.

Approach:

• Run some data transformation queries when running ETL
• Add platform support to add hooks for the above where implementors can just add queries.

AC:

Enhance ETL to perform the following

• For each org, read config json and sql script files from a specific folder, if exists (Otherwise, Skip the whole flow)
• Read the DDL json config files to determine the report_tables that need to be created
• For each report table config entry,
• • Check if the report_table exists, if not then create the table
• • Modifying existing table structure will not supported
• Read the DML json config file, for each table (ordered manner)
• • Process the sqls entries one by one, in ordered manner
• • • Invoke the insert sqls
• • • Invoke the update sqls in ordered manner
• Note that, for each table data insert / update operation, we'll specify dateTime range "previousCutoffDateTime" <=> "currentCutoffDateTime" and only data within this range will get synced to the report_table. This is to avoid data miss for some dependant table executed later.

Note for Technical QA and implementation team

Refer post-etl-sync-processing-config.json for DDL and DML structure sample.
Refer https://github.com/avniproject/avni-etl/blob/13.0/post-etl-sync-setup-readme.md for details about post-etl-sync processing.

OLD AC: (Retained for reference sake)

• Identify if the queries in reference if replaced with the below will provide significant performance boost.
• To start with just find a way to provide platform support for adding SQL queries for the columns to be created.
• Updating of queries should automatically reflect without the need of ETL redeployment/restart
• Make the above generic so that even custom tables can be created. This will be very useful since generally loading of dashboard which shows the cumulative calculations is slow. If this is complex can be done in a separate card.
• No need to add UI for the above. Based on how above works, we can decide later.

Reference

For testing purpose, refer the 'Custom Requirement' sheet here. Refer the columns marked in Orange that are difficult to calculate.

[petmongrels]

Initial thoughts on creating transformed data

Terminology

• report table - The table which will have all the data required for any report. More than one BI report can use this but possibly there is one report table per BI report.
• source table(s) - all the ETL tables that impacts the data in report table is present. these are our regular etl tables like individual, individual_child, etc

DDL (Create table)

• read the metadata json file to get the name of table
• if the table doesn't exist then create the table by running sql file provided

DML

• for each source table to report table mapping, we would have configuration in json under dml.sqls
• for each mapping, first the insert SQL will be run and then the update SQLs will be run. For each invocation of SQL, the last sync time will be passed to SQL as a parameter. This parameter should be used to determine what has changed and restrict the number of rows involved for performance.
• the key detail here is that the insert and updates will change only some columns of the report table. This will allow us to write simpler and modular SQL.

https://gist.github.com/petmongrels/95d2d435275b884c177ef81752f51680