LCFS - Migrate Compliance Report Summary from TFRS to LCFS

[AlexZorkin]

Description:
Develop a Groovy ETL script to migrate compliance report summaries from TFRS to LCFS. TFRS compliance reports contain a summary field stored as JSON, which includes key metrics such as retained fuel volumes, deferred obligations, and credits offset. The goal is to map this data accurately into the LCFS compliance_report_summary table, ensuring historical data is preserved for display and auditing.

If any fields required to fully migrate TFRS data are missing in LCFS, these fields must be added to the LCFS schema as part of this ticket.

Purpose and Benefit to User:
Ensures historical compliance data from TFRS is accurately migrated and available in LCFS. This preserves critical information for auditing and review while enabling a seamless transition between systems.

Acceptance Criteria:

• Given I am a developer, when the ETL process runs, all compliance report summaries from TFRS are migrated to LCFS, preserving the integrity of historical data.
• Given I am a developer, when I run the ETL process, all fields in the TFRS summary JSON are mapped to corresponding fields in the LCFS compliance_report_summary table.
• Given I am a developer, when the ETL process encounters fields in TFRS that do not exist in LCFS, those fields are added to the LCFS schema and included in the migration.
• Given I am running the ETL process, errors are logged and do not interrupt the migration of other compliance reports.

Development Checklist:

1. Schema Updates (if necessary):

   • Review the summary field in TFRS for any data elements not currently represented in the LCFS compliance_report_summary table.
   • Add missing fields to LCFS, such as:
     • credits_offset_a: Credits offset from previous reports.
     • credits_offset_b: Credits offset in supplemental reports.
     • credits_offset_c: Credits to be returned due to a decrease in debits in supplemental reports.

2. Data Extraction:

   • Extract compliance report summaries from TFRS, focusing on the summary JSON field.
   • Parse the JSON to retrieve individual fields, including lines, credits, and retained/deferred volumes.

3. Mapping and Transformation:

   • Map fields from TFRS to LCFS:
     • gasoline_class_retained → line_6_renewable_fuel_retained_gasoline.
     • diesel_class_retained → line_6_renewable_fuel_retained_diesel.
     • gasoline_class_deferred → line_8_obligation_deferred_gasoline.
     • diesel_class_deferred → line_8_obligation_deferred_diesel.
     • credits_offset → New LCFS field for total credits offset.
   • Ensure supplemental report data aligns with versioning and does not overwrite original compliance report data.

4. Versioning:

   • Use the group_uuid and version fields to chain summaries for original and supplemental compliance reports.

5. Data Insertion:

   • Insert mapped data into the LCFS compliance_report_summary table, linking summaries to compliance reports using compliance_report_id.

6. Error Handling and Logging:

   • Implement error handling to log issues, ensuring the process continues for other compliance reports.

7. Testing and Validation:

   • Validate migrated summaries to ensure all TFRS data fields are present and correctly mapped.
   • Test scenarios with supplemental reports and verify versioning chains are correctly established.

8. Documentation:

   • Document the ETL process, including schema changes, field mappings, and assumptions.
   • Provide usage instructions and validation steps for future maintenance.

Notes:

• Supplemental reports in TFRS create updated summaries that must be versioned correctly in LCFS using group_uuid and version.
• Missing fields, such as credits_offset_a, credits_offset_b, and credits_offset_c, will be added to the LCFS compliance_report_summary table to accommodate the data structure in TFRS.
• Ensure robust error handling to log and skip problematic records without halting the migration process.
• Leverage existing ETL scripts for compliance reports as a reference for structure and methodology.