Attribute level lineage

[saroj9958]

Hi Team,

We want attribute level lineage for that we are using DerivesFrom collection. Basically we need source column name ,table name, destination column name and table name for that we are joining derives from>attribute first to get column name but how can we get the table name/uri for source and destination?

We have tried the we can get for the read and write type from joining with operation but for the Transformation type we are not able to get. It will be really helpful if you can guide me, how to join operation and datasource collection and get the table name/uri for transformation type?

Thanks in Advance,
Saroj

[wajda]

Not every attribute represents a column in a table. Attributes that form a transformation operation output schema might be virtual or be a part of a temporary table depending on the implementation of the engine that produced the given execution plan. If you are looking for a dependency between columns of persistent data sources (database tables) you need to traverse the graph from the write operation up to all connected read operations, that should contain necessary details about the data sources they read from.

[saroj9958]

@wajda thanks for the reply.
Request you to pls elaborate more how we can traverse from write to Read for transformation operation. what all documents and edges need to use and relation between them for joining if possible. That will be great help.
Basically our need to to get all write attribute and against them there source attribute info with uri.

Thanks again,
Saroj

[wajda]

I'm still not sure what you mean by mentioning the transformation operations in that context. Take a look at the persistent model diagram. Hope it helps. https://github.com/AbsaOSS/spline/blob/gh-pages/docs/Persistent%20model.pdf

[saroj9958]

@wajda Sorry for that. let me explain you again in details.

WE are trying for Attribute level lineage. Our requirement is get all attribute from derives from and get there source and destination URI For that below are the steps we are following:

1. We believe _from column is destination attribute and _to column is Source attribute in derivesFrom edge to get dependency between attributes
2. for each destination and source we are trying to get URI for that
3. We are able to get URI from Data Source if attribute is in either readsFrom or in writesTo edge. but
4. There are few attribute for which we observed is available in Operation node and type for those are Transformation we are not able to get Data Source URI for those transformation. Also we observed that those attribute are coming from some CTE table.

Now we have 2 question:

1. Can we get URI for transformation type in Operation node? if yes then what logic we should follow?
2. We need Attribute level lineage let say for example there are 24 column/attribute in destination then we need there source column/attribute and there uri. is it possible then which all nodes/edges we should use? we don't need in between transformation attribute right now.

Hope my question is clear.

Thanks in advance.
Saroj

[wajda]

1. We believe _from column is destination attribute and _to column is Source attribute in derivesFrom edge to get dependency between attributes

Correct

3. We are able to get URI from Data Source if attribute is in either readsFrom or in writesTo edge. but

The phrase does not make any sense to me. Attributes are stored as nodes, and are not connected with neither readsFrom nor writesTo edge. What exactly did you mean by that?

4. There are few attribute for which we observed is available in Operation node and type for those are Transformation we are not able to get Data Source URI for those transformation. Also we observed that those attribute are coming from some CTE table.

That still doesn't make any sense to me. What is CTE table?

Now we have 2 question:

1. Can we get URI for transformation type in Operation node? if yes then what logic we should follow?
2. We need Attribute level lineage let say for example there are 24 column/attribute in destination then we need there source column/attribute and there uri. is it possible then which all nodes/edges we should use? we don't need in between transformation attribute right now.

Ok, so if understand it correctly all you need it to traverse the graph along the highlighted edges (see the schreenshot).

Basically, you take a destination Attribute, then go the its parent along the derivesFrom edge, and then again, and again, until there is either no more derivesFrom edges connected to the attribute, or the attribute is connected to a Read type operation via the produces edge. This attribute would be one of your source attributes for the given destination. Then go along the produces edge to an Operation node (its type should be Read), and from there, through the readFrom edge, you will get to the DataSource node and take its URI. Simple.
Note however, that sometimes the Read operation can be missing (depending on the execution plan structure and/or the completeness of the metadata captured by the agent). If you recursively followed the derivesFrom edges and ended up on the attribute that has no more derivesFrom edges to follow and is produced by the operation of type Transformation, then there is no source URI that corresponds to this attribute. Again, it could either be an incomplete execution plan, or a generated attribute, that actually was never read from any persistent source. So it depends.