Data Lineage Basics
This article gives you a basic idea about the main elements in a data lineage result generated by the Gudu SQLFlow in the JSON/XML format.
We use a simple Oracle SQL query to demostrate the data lineage analysis result.
INSERT INTO deptsal
SUM(e.sal + Nvl(e.comm, 0)) AS sal
FROM dept d
left join (SELECT *
WHERE hiredate > DATE '1980-01-01') e
ON e.deptno = d.deptno
GROUP BY d.deptno,
The data lineage generated by the Gudu SQLFlow for the above SQL.
All database objects discovered during the data lineage analysis are stored in the
In the above sample SQL, there are four tables founded:
- DEPTSAL you can use
$.dbobjs.nameto return the table name, and
$.dbobjs.typeto return the type of this object which is
tablein this case. you can also use expression like this to get this table:$.dbobjs[?(@.name=='deptsal')].name
- SQL_CONSTANTS This is not a real table, but a table generated by the Gudu SQLFlow to store the constant used in the SQL query.$.dbobjs[?(@.name=='SQL_CONSTANTS')].name
Relation is the atom unit of the data lineage. Relation build a link between the source and target column( column-level lineage).
Those relations are stored in the
A relation includes the
sourcesand other attributes.
- type There are 2 types of relation between source and target column. a) Direct dataflow: represented by the
fddin the JSON output. b) In-direct dataflow: represented by the
fdrin the JSON output. In-direct dataflow also known as
impacttype, which means the value of the source column doesn't affect the value of the target column, but effect the rows number of the target column. For instance, the relation between a source column in the where clause and the column in the select list is a In-direct relation(impact).
- target This is the target column.
- sources These are source columns where the data of the target column comes from.
Return a relation which target column is dept_no
Let's say you want to trace back from
DEPT.deptnoin the above diagram.
- Discover the relation 1 using the following JSON path$.relations[?(@.target.column=='dept_no')]
- Discover the relation 2 using the following JSON path$.relations[?(@.target.column=='deptno')]
This is a brief introduction about the structure and elements in a data lineage result generated by the Gudu SQLFlow.
Please check the data lineage structure reference for more detailed information.