I am doing some integrity work at the moment, investigating cases when something goes wrong with incoming data.
The system I am working with involves multiple servers, including Sybase and SQL Sever 2016. The data is generally pulled from a stream, uploaded into a database in the first Server, cleaned and sent to a database in the next server, etc. Data is also sometimes manually uploaded or manually updated, or entered in through other processes.
Ultimately what this means is if something is wrong with the data, which is only captured/logged in the final server (SQL Server) before being sent out, there are numerous fail points where it can happen. Even different cells in the same row can have different origins by the time the data reaches the final server.
My question is: Is there any existing software which could actually track the origin of a given piece of data? If something goes wrong I would like to have an easier time backtracking the path the data took, to indicate whether the issue was a glitch in our cleaning process or if the data itself had issues from the moment it entered our system.
Alternatively are there any general systems/practices of data management which would make this tracking process either, rather than having to check every step of the way to see where the failure occurred?