2

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?

  • 1
    The answer to the "systems/practices" would be to include a column with all data that is imported that signifies its source. As for whether the issue was caused by your cleaning process, you would simply have to save or log the changes that were made to each row. You could enable change tracking, but the overhead might impact performance too much--particularly if it's a data warehouse. – Tony Hinkle Aug 9 '17 at 16:58
  • 1
    @tony - that should be an answer, not a comment – Max Vernon Aug 9 '17 at 20:22
  • Yeah I have been dreaming up a system similar to that kind of column. Are there any practical guides to actually implementing it? – Josh Kraushaar Aug 10 '17 at 14:04
0

have you considered using Azure data factory? The service can ingest data from multiple sources and comes processing capabilities. The monitoring feature enables you to do some debugging/analysis work as well.

  • For that to work, though, all your data has to go through Azure Data Factory. It doesn't help you for existing apps & ETL processes. – Brent Ozar Aug 12 '17 at 2:57
1

The answer to the "systems/practices" would be to include a column with all data that is imported that signifies its source.

As for whether the issue was caused by your cleaning process, you simply need to save or log the changes that were made to each row, or enable change tracking. Any of these solutions will introduce overhead that will affect performance, so let's be careful out there!

  • The one-column approach fails when you do updates. Updates can come from multiple sources (including humans doing data cleansing.) – Brent Ozar Aug 12 '17 at 2:57
  • You could use just one column if it's used as a bitmask. – Tony Hinkle Aug 13 '17 at 0:42
  • Tony - forgives me, I must be missing something obvious. Say we have a table with 2 columns - name and address. The record is inserted by app A with just name populated. App B comes along and updates the address. App C comes along and updates both fields. Finally, app A comes back in and sets the name to null.. How do you track that history with a bitmask field? – Brent Ozar Aug 13 '17 at 18:13
  • I got off track after your comment, @BrentOzar. The OP was about how to track the original source of the data, and that is what the one column would be for--to signify the source. To track changes, that is a different matter, and yes you can't track all changes with one column. The bitmasky stuff could come in if you want to track what processes changed the row. It couldn't track what changed, but it could track that process A, process B, and process Q had touched that row. My interpretation of the question is that he is looking for ways to quickly identify what touched each row. – Tony Hinkle Aug 14 '17 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.