Techniques for building data lineages for queries
Various embodiments are generally directed to techniques for building data lineages for queries, such as SQL queries. Some embodiments are particularly directed to a lineage tool that is able to construct data lineages in a recursive manner that uses the text of a query to identify dependent tables. In several embodiments, the data lineage tool may parse SQL queries to identify columns and dependent tables, including analyzing interdependent queries used to populate dependent tables and proceeding until the true source of data is identified. In several embodiments, the data lineage tool may utilize the relationships and dependencies to build element and table level lineages.
1. An apparatus, comprising:
a processor; and
memory comprising instructions that when executed by the processor cause the processor to:
determine whether a first query is dependent on a historical query in historical data;
determine whether a historical lineage has been previously built for the historical query in the historical data;
responsive to the historical lineage being previously built for the historical query, fetch the historical lineage from the historical data;
build a data lineage for the first query by adding to the historical lineage to define upstream dependencies that capture relationships between and within the historical query and the first query for end-to-end lineage generation;
add metadata to the data lineage, the metadata comprising transformation logic associated with the first query; and
generate a data report using the data lineage to automatically identify any data inaccuracies and vulnerabilities.
2. The apparatus of claim 1 , wherein the instructions, when executed by the processor, further cause the processor to:
determine whether the first query is dependent on a second query in the historical data;
determine whether a second lineage has been previously built for the second query in the historical data;
when the second lineage has been previously built for the second query, fetch the second lineage from the historical data; and
use the second lineage to build the data lineage.
3. The apparatus of claim 1 , wherein the instructions, when executed by the processor, further cause the processor to merge the data lineage into a lineage graph comprising at least a portion of the historical lineage.
4. The apparatus of claim 1 , wherein the instructions, when executed by the processor, further cause the processor to parse the first query to determine whether the first query is dependent on the historical query.
5. The apparatus of claim 1 , wherein the instructions, when executed by the processor, further cause the processor to merge the data lineage, including the metadata added, into a lineage graph.
6. The apparatus of claim 1 , wherein the first query comprises a structured query language (SQL) query.
7. At least one non-transitory computer-readable medium comprising a set of instructions that, in response to execution by a processor circuit, cause the processor circuit to:
determine whether a first query is dependent on a historical query in historical data;
determine whether a historical lineage has been previously built for the historical query in the historical data;
responsive to the historical lineage being previously built for the historical query, fetch the historical lineage from the historical data;
build a data lineage for the first query by adding to the historical lineage to define upstream dependencies that capture relationships between and within the historical query and the first query for end-to-end lineage generation;
add metadata to the data lineage, the metadata comprising transformation logic associated with the first query; and
generate a data report using the data lineage to automatically identify any data inaccuracies and vulnerabilities.
8. The at least one non-transitory computer-readable medium of claim 7 , wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to:
determine whether the first query is dependent on a second query in the historical data;
determine whether a second lineage has been previously built for the second query in the historical data;
when the second lineage has been previously built for the second query, fetch the second lineage from the historical data; and
use the second lineage to build the data lineage.
9. The non-transitory computer-readable medium of claim 7 , wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to merge the data lineage into a lineage graph comprising at least a portion of the historical lineage.
10. The non-transitory computer-readable medium of claim 7 , wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to parse the first query to determine whether the first query is dependent on the historical query.
11. The non-transitory computer-readable medium of claim 7 , wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to merge the data lineage, including the metadata added, into a lineage graph.
12. A computer-implemented method, comprising:
determining whether a first query is dependent on a historical query in historical data;
determining whether a historical lineage has been previously built for the historical query in the historical data;
responsive to the historical lineage being previously built for the historical query, fetching the historical lineage from the historical data;
building a data lineage for the first query by adding to the historical lineage to define upstream dependencies that capture relationships between and within the historical query and the first query for end-to-end lineage generation;
adding metadata to the data lineage, the metadata comprising transformation logic associated with the first query; and
generating a data report using the data lineage to automatically identify any data inaccuracies and vulnerabilities.
13. The computer-implemented method of claim 12 , comprising:
determining whether the first query is dependent on a second query in the historical data;
determining whether a second lineage has been previously built for the second query in the historical data;
when the second lineage has been previously built for the second query, fetching the second lineage from the historical data; and
using the second lineage to build the data lineage.
14. The apparatus of claim 1 , wherein the instructions, when executed by the processor, further cause the processor to build the data lineage based on at least one conditional statement in the first query, the at least one conditional statement comprising one or more of a ‘where’ clause or a ‘group by’ clause.
15. The non-transitory computer-readable medium of claim 7 , wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to build the data lineage based on at least one conditional statement in the second first query, the at least one conditional statement comprising one or more of a ‘where’ clause or a ‘group by’ clause.
16. The computer-implemented method of claim 12 , comprising:
building the data lineage based on at least one conditional statement in the first query, the at least one conditional statement comprising one or more of a ‘where’ clause or a ‘group by’ clause.