Tracking Records for Regulatory Compliance

1. Identify Regulatory Compliance Requirements
• Determine what data needs to be tracked for regulatory purposes.
• Understand which operations (inserts, updates) need to be recorded and the type of metadata required (e.g., Job Id, Job Name, operation type).
2. Design the Tracking Tables
• For each target table, create a corresponding tracking table with a "_RGG" suffix.
• The tracking table should include:
• All data columns from the target table.
• Additional compliance-related columns:
• Job Id
• Job Name
• Date and time of the operation
• Type of operation (Insert or Update)
3. Set Up the Flow Data Copy
• Create a mechanism to copy the flow data before it is applied to the target table.
• This ensures that all modifications are recorded for compliance purposes.
4. Integrate Tracking into the ETL Process
• Modify the ETL flow to populate the tracking table after the insert and update operations to the target table are performed.
• This can be done in the integration layer after applying the changes to the target table and before the end of the IKM (Integration Knowledge Module).
5. Example: Modify Oracle Incremental Update IKM
• After the “Update Existing Rows” and “Insert New Rows” tasks, add tasks to populate the tracking table.
• For each operation, insert a record into the "_RGG" tracking table, including the relevant compliance metadata (Job Id, Job Name, operation type, and timestamp).
6. Review and Validate Data for Compliance
• Periodically review the tracking table to ensure that it contains accurate records of the data operations.
• Validate that the Job Id, Job Name, and operation types are correctly captured and aligned with the corresponding target data changes.
7. Monitor and Report for Auditing
• Ensure that you have mechanisms in place for monitoring and auditing the tracking data, especially for regulatory reporting.
• Set up alerts or reports to flag any anomalies or unexpected operations for review.
8. Secure the Tracking Data
• Implement necessary security measures to protect the integrity and confidentiality of the tracking data.
• Ensure that the tracking table is only accessible by authorized users for regulatory auditing purposes.