Page History

...

The Metadb reporting database serves as a platform for generating reports for library staff on data from all functional areas of the FOLIO system. The Metadb software extracts, transforms, and loads data from the FOLIO application server continuously to the reporting database through its process of data synchronization. Both current and historical records are saved in tables in the reporting database. Some of these tables contain fields with patron identifiers, such as "user_id" and "requester_id." Patron identifiers are needed for current transactions – such as loans, requests, and fines that are open – to enable library staff to identify and communicate with those patrons. Patron identifiers for historical transactions – such as loans, requests, and fines that are closed – are not needed. To uphold the Cornell Library's Commitment to Privacy policies, patron identifiers on historical transaction records should be replaced with NULL values in the Metadb reporting database.

Risks

Like many reporting databases, the data fields and tables in Metadb are woven together with a complex set of interdependencies, connections, and data relationships. Deleting data in a PostgreSQL reporting database without a thorough understanding of the impacts on its infrastructure and data integrity poses several significant risks. 

• Reporting databases involve complex relationships between tables, and removing records without considering these connections can disrupt referential integrity. For example, if a deleted record is referenced elsewhere, it may leave orphaned data or trigger unintended cascading deletions. This compromises the accuracy and trustworthiness of the data.
• Reporting databases also commonly store historical, audit, or compliance-related information. Unintended deletions could permanently remove critical data needed for analysis, regulatory compliance, or business intelligence. This can affect decision-making processes and may even expose the organization to legal risks. Additionally, ETL pipelines that feed or consume the reporting data could break or propagate errors downstream if the source data unexpectedly changes.
• Reports and dashboards that rely on specific datasets might stop working or produce inaccurate results. In addition, deletions can interfere with database performance, especially if the system isn't properly maintained afterward to remove leftover data fragments. If triggers or materialized views are tied to the deleted data, they might behave unpredictably or become outdated. Finally, deletions without proper logging or oversight may raise security concerns and create audit gaps.

It’s crucial to approach deletions cautiously—starting with a deep analysis of the schema, understanding dependencies, and always backing up the data. Deletions should ideally be tested in non-production environments and wrapped in transactions to prevent irreversible mistakes.

Recommendations-complexity and risks

Current Loans are already purged of patron data, but Historical Loans are not

...