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 second query;
responsive to the first query being dependent on the second query, determine whether a lineage for the second query has been built;
responsive to the lineage for the second query being built, fetch the lineage for the second query from a database;
build a lineage for the first query by adding to the lineage for the second query;
add metadata to the lineage for the first query, the metadata comprising transformation logic associated with the first query;
evaluate the lineage for the second query with the transformation logic to transform data associated with the second query into data associated with the first query; and
generate a data report that includes the data associated with the first query and the data associated with the second query to automatically identify data gaps, data quality issues, or data inaccuracies and vulnerabilities.
2 . The processor of claim 1 wherein the first query is a search query language (SQL) query.
3 . The processor of claim 2 wherein the instructions, when executed by the processor, further cause the processor to:
use text of the SQL query to identify the second query.
4 . The processor of claim 2 wherein the instructions, when executed by the processor, further cause the processor to:
parse the SQL query to identify dependent tables and analyze interdependent queries used to populate the dependent tables.
5 . The processor of claim 1 wherein the instructions, when executed by the processor, further cause the processor to:
build the lineage for the first query in a recursive manner until a true source of the data associated with the first query is identified.
6 . The processor of claim 5 wherein the instructions, when executed by the processor, further cause the processor to:
build the lineage for the first query without an entire collection of queries on which the first query depends.
7 . The processor of claim 1 wherein the instructions, when executed by the processor, further cause the processor to:
build the lineage for the second query responsive to the lineage for the second query being unbuilt.
8 . A 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 second query;
responsive to the first query being dependent on the second query, determine whether a lineage for the second query has been built;
responsive to the lineage for the second query being built, fetch the lineage for the second query from a database;
build a lineage for the first query by adding to the lineage for the second query;
add metadata for the lineage for the first query, the metadata comprising transformation logic associated with the first query;
evaluate the lineage for the second query with the transformation logic to transform data associated with the second query into data associated with the first query; and
generate a data report that includes the data associated with the first query and the data associated with the second query to automatically identify data gaps, data quality issues, or data inaccuracies and vulnerabilities.
9 . The non-transitory computer-readable medium of claim 8 wherein the first query is a search query language (SQL) query.
10 . The non-transitory computer-readable medium of claim 9 wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to:
use text of the SQL query to identify the second query.
11 . The non-transitory computer-readable medium of claim 9 wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to:
parse the SQL query to identify dependent tables and analyze interdependent queries used to populate the dependent tables.
12 . The non-transitory computer-readable medium of claim 8 wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to:
build the lineage for the first query in a recursive manner until a true source of the data associated with the first query is identified.
13 . The non-transitory computer-readable medium of claim 12 wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to:
build the lineage for the first query without an entire collection of queries on which the first query depends.
14 . The non-transitory computer-readable medium of claim 8 wherein the set of instructions, in response to execution by the processor circuit, further cause the processor circuit to:
build the lineage for the second query responsive to the lineage for the second query being unbuilt.
15 . A computer-implemented method comprising:
determining whether a first query is dependent on a second query;
responsive to the first query being dependent on the second query, determining whether a lineage for the second query has been built;
responsive to the lineage for the second query being built, fetching the lineage for the second query from a database;
building a lineage for the first query by adding to the lineage for the second query;
adding metadata to the lineage for the first query, the metadata comprising transformation logic associated with the first query;
evaluating the lineage for the second query with the transformation logic to transform data associated with the second query into data associated with the first query; and
generating a data report that includes the data associated with the first query and the data associated with the second query to automatically identify data gaps, data quality issues, or data inaccuracies and vulnerabilities.
16 . The computer-implemented method of claim 15 wherein the first query is a search query language (SQL) query.
17 . The computer-implemented method of claim 16 further comprising:
using text of the SQL query to identify the second query.
18 . The computer-implemented method of claim 16 further comprising:
parsing the SQL query to identify dependent tables and analyze interdependent queries used to populate the dependent tables.
19 . The computer-implemented method of claim 15 further comprising:
building the lineage for the first query in a recursive manner until a true source of the data associated with the first query is identified.
20 . The computer-implemented method of claim 19 further comprising:
building the lineage for the first query without an entire collection of queries on which the first query depends.