LCFS - Migrate Schedule A Records from TFRS to Notional Transfers in LCFS with Version Chaining

[AlexZorkin]

Description:
Develop a Groovy ETL script to migrate Schedule A records from TFRS to the notional_transfer table in LCFS. Each TFRS compliance report includes a full set of Schedule A records, and supplemental reports may contain modified versions. The script will process compliance reports, identify supplemental chains, and compare Schedule A records to establish accurate version chains in LCFS using group_uuid and incremental version.

Key tasks include:

• Loop through compliance reports in TFRS and extract Schedule A records.
• Identify supplemental chains and compare Schedule A records for changes (added, updated, or removed).
• Map TFRS fields to LCFS fields and handle the creation of group_uuid and version chains for notional_transfer.
• Populate the notional_transfer table in LCFS, linking records to their respective compliance reports using the legacy_id field.

Purpose and Benefit to User:
Accurately migrates notional transfer data, preserving versioning and tracking changes across supplemental compliance reports. This ensures a complete and auditable record of all notional transfers, aligned with compliance requirements in LCFS.

Acceptance Criteria:

• Given I am a developer, when the ETL process runs, all Schedule A records from TFRS are migrated to LCFS with correct associations to their compliance reports using legacy_id.
• Given I am a developer, when the process runs, notional_transfer records are grouped by group_uuid and incremental versions are assigned for supplemental reports.
• Given I am a developer, when the process runs, changes between supplemental reports are accurately identified and reflected in the version chains.
• Given I am a developer, when the process runs, all required fields (quantity, legal_name, address_for_service, fuel_category_id, etc.) are correctly mapped to LCFS.
• Given I am running the ETL process, errors are logged, and processing continues without halting for other compliance reports.

Development Checklist:

1. Data Extraction:

   • Query TFRS compliance reports to extract Schedule A records grouped by compliance report ID.
   • Identify supplemental chains for each compliance report.

2. Mapping and Comparison:

   • Compare Schedule A records within each chain to detect changes (e.g., added, modified, or removed records).
   • Map TFRS fields to LCFS notional_transfer fields:
     • quantity → quantity
     • trading_partner → legal_name
     • postal_address → address_for_service
     • fuel_class_id → fuel_category_id
     • transfer_type_id → received_or_transferred (map 1 = "Transferred", 2 = "Received")

3. Version Chaining:

   • Assign a group_uuid to all records within a compliance report chain.
   • Increment version for each supplemental report in the chain.
   • Set action_type based on the type of change (e.g., CREATE, UPDATE, or DELETE).

4. Data Insertion:

   • Insert mapped records into the notional_transfer table with the correct version and compliance report associations via legacy_id.
   • Ensure notional_transfer records link to the correct compliance report in LCFS.

5. Error Handling and Logging:

   • Implement robust error handling to log issues without interrupting the ETL process for other records.
   • Log comparisons and detected changes between supplemental reports.

6. Testing and Validation:

   • Verify that all records are migrated accurately, including version chaining.
   • Validate associations with compliance reports in LCFS.
   • Test edge cases, such as compliance reports with multiple supplemental versions or significant changes in Schedule A records.

7. Documentation:

   • Document the script’s purpose, logic, and field mappings for future reference and maintenance.
   • Provide detailed guidance for running and verifying the ETL process.

Notes:

• The legacy_id field in LCFS compliance reports will be used to match the TFRS compliance report ID for associations.
• Ensure that received_or_transferred correctly reflects the intended transfer type.
• Leverage existing ETL scripts for compliance reports and fuel supplies as references for structuring and implementing version chaining.