0

New to a software engeneer job and I am kinda blocked on the way to go with my new project To explain this in a scheme.

  1. I have a source of data in a custom app that is for most part of the time the result of a stored procedure.
  2. Here is a path (Fictional used as an example) of the path that the data had to do before going to the Data Source CODPOT.
  3. Here is a representation (Fictional used as an example) of what the table of origin of the data for CODPOT can be.

So, the goal of the project being to be able in the program and this dynamically (So applicable for all sources and this regardless of the model, the original table or the type of data) to get it's original table to allow analysts to more easily verify and correlate data. That way they won't need to recreate the complete life path of a data, because manually it can sometime take up to 3 days to retrace. The thing is most part of the time, the stored procedure uses OPENQUERY to get data from distant servers, and ever in these servers, there can be views before finding the original table. So if we want put this in the scheme, at step 2 most part of the time one the square is an OPENQUERY to an other server.

Visualization of the problem

What can I do to be able to trace back the original table, can anyone guide me to the right path ? Because frankly I have no idea how I could do it. I've already looked up for INFORMATION_SCHEMA, ORIGINAL_DB_NAME and I am not sure if "sp_describe_first_result_set" will be working for this. I've already looked all of these (And more, but these are the mostconsistent with what I want):

Maybe there is some sort of history in SQL Management that I can exploit to get this information or something like this. Thx for your time !

EDIT: Now that I've found that what I wanna do is Data Lineage of a SQL Server,to clarify I am looking for advice/guidance on how I can do this, what language should I use to create the tool, will I need to create multiple tool, where to start to create the tool, what will I need for the tool (History table, metadata, etc...), If I need metadata, how can I access multiple layer of metadata. Maybe some advice of already existing tool etc...

6
  • There isn't any one single technique that will get you what you want, short of embedding the original table name in the names of each of your fields. Sep 7, 2023 at 14:50
  • That is what I was afraid of haha, there isn't really any dynamic solution to this
    – Dolotboy
    Sep 7, 2023 at 14:51
  • Foreign keys won't work for your use case? And if not, then why? Foreign keys give you traceability from one table to another — but only as long as these tables are in the same database. Sep 7, 2023 at 16:20
  • @GregBurghardt, he says he's hopping between engines, and foreign keys won't help you link the output of a query to its inputs (e.g. answering questions like "which field in which table, does the data for this aggregate column come from?").
    – Steve
    Sep 7, 2023 at 16:53
  • 1
    Not every stocked procedure call an OPENQUERY to hop between engines, but the most part does, so there is no point in doing something that'll only work 15% of the time... That's how I see it !
    – Dolotboy
    Sep 7, 2023 at 16:56

1 Answer 1

2

If you're hopping between engines and moving through many layers of queries and views, then it's very unlikely you'll find an automated solution that can trace the dependencies.

I seem to remember reading a while ago that Google (or whoever) was doing some research on this, so it's not a solved problem.

You'll also find that whilst it might make sense in simple cases to think of data "coming from" a specific place, once you start doing anything more than simple fetching of data and start doing any real processing, then simply compiling dependencies in a mechanical way might produce a wildly long and uninformative list.

Typically you'd have to have an idea of your purpose in performing the tracing, and then analyse all the processing logic in detail to decide whether a particular dependency is a relevant one or not. The number of actual dependencies - things that can theoretically determine or alter the output - is often much, much larger than the number of dependencies which are relevant to a particular question or investigation.

7
  • 1
    Thank you for your answer, it really helps me. I am looking for a solution/answer, but knowing that something is not possible or really unlikely is really useful to know for the progress of my solution development. Also, thank you for the information that this is a known problem and currently being investigated, I'll try to find the "article", it will be useful to me. Indeed, it's very easy to think that if we have a piece of data, it's because it has a starting point that's cast in stone and therefore easy to find, but that's not the case at all haha. As soon as I hit 15 reps I upvote this !
    – Dolotboy
    Sep 7, 2023 at 16:54
  • @Dolotboy, bit annoying that I can't remember much detail of what I'd previously read about this (and I'm not even sure how long ago, or how current it was when I read it), but just searching now, it seems a key term here is "data lineage".
    – Steve
    Sep 7, 2023 at 17:38
  • That is exactly what I found in Google's Research. The publication itself didn't really help me, but the term "Data Lineage" that was at it's top has been an INCREDIBLE advancement on my researchs. I've found some tool that already exist and could possibly do what I wanna do. So far i've found Gudu SQLFlow, DataPlex and some other that I haven't much looked at for the moment ! I'm trying to understand how they work to see if there is that I could simply "install" over the SQL servers
    – Dolotboy
    Sep 7, 2023 at 18:04
  • would you happen to know anything about ETL ? I've been looking and it seems that before being able to do Data Lineage, I need an ETL process (Which is currently raw by SQL). So from what I understand I would need to restructure the way data flows through the DB with an ETL to "standardise" everything and after that I would be able to use an external Data Lineage tool such as (SQLFlow, Dataplex, Tokern or else) or to create a custom Data Lineage tool (Which I still don't know how). Would you happen to know anything about this and correct me if I'm wrong ? Does it make sens to you ?
    – Dolotboy
    Sep 8, 2023 at 14:48
  • @Dolotboy, I'm familiar with the term "ETL" but it's a piece of jargon that I don't use myself and I'm not really sure what it means or what it contrasts with in this context. "Extract, transform, load" is just a variant on "fetch, execute, store" - pretty generic concepts which seem to apply timelessly to basically all computing and data processing (not to mention virtually all manufacturing activity - e.g. you extract iron ore, fire it in the furnace, and load the resulting ingot into the storehouse). (1/2)
    – Steve
    Sep 8, 2023 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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