Data quality check using a structured query language hint
A data quality check using a structured query language hint is described. A query optimizer of a database management system may receive a data quality check instruction as part of a database query statement for a database query. The query optimizer may modify a query execution plan for the database query based on the data quality check instruction so that completion of the database query is dependent on a result of a data quality check defined by the data quality check instruction. The database management system may execute the modified query execution plan.
1 . A method comprising:
receiving, by a query optimizer of a database management system, a data quality check hint as part of a database query statement for a database query of a database;
retrieving, by the query optimizer, a data quality check instruction from a plurality of stored data quality check instructions based on the data quality check hint, the data quality check instruction defining a condition that differentiates reliable data stored in the database from unreliable data stored in the database;
modifying, by the query optimizer, a query execution plan for the database query based on the data quality check instruction so that completion of the database query while executing the query execution plan is dependent on the condition defined by the data quality check instruction; and
executing, by the database management system, the modified query execution plan by:
completing the database query and returning query results in response to the condition being satisfied; and
generating an exception that blocks completion of the database query in response to the condition not being satisfied, the exception further including at least of one of logging the condition not being satisfied, rolling back an operation, and communicating a reason for the condition not being satisfied.
2 . The method of claim 1 , wherein the modifying, by the query optimizer, the query execution plan for the database query based on the data quality check instruction so that the completion of the database query while executing the query execution plan is dependent on the condition defined by the data quality check instruction comprises inserting, by the query optimizer, logic into the query execution plan corresponding to the condition.
3 . The method of claim 1 , wherein the data quality check instruction comprises programmable variables.
4 . The method of claim 1 , wherein the data quality check instruction is selected by the query optimizer from the plurality of stored data quality check instructions based on a context of the database query statement.
5 . The method of claim 1 , wherein the condition of the data quality check instruction includes identifying a record in a target data table of the database, and wherein:
completing the database query and returning the query results is in response to identifying the record in the target data table; and
generating the exception that blocks completion of the database query is in response to not identifying the record in the target data table.
6 . The method of claim 1 , wherein the condition of the data quality check instruction includes an absence of duplicate records in a target data table of the database, and wherein:
completing the database query and returning query results is in response to the absence of the duplicate records in the target data table; and
generating the exception that blocks completion of the database query is in response to the duplicate records being present in the target data table.
7 . The method of claim 1 , wherein the database query comprises a join of two target data tables in the database, the condition of the data quality check instruction includes a join result of the two target data tables not including duplicate records, and wherein:
completing the database query and returning query results is in response to the join result of the two target data tables not including the duplicate records; and
generating the exception that blocks completion of the database query is in response to the join result of the two target data tables including the duplicate records.
8 . The method of claim 1 , wherein the database query comprises a join of two target data tables in the database, the condition of the data quality check instruction includes overlapping records of the two target data tables, and wherein:
completing the database query and returning query results is in response to the overlapping records being present in the two target data tables; and
generating the exception that blocks completion of the database query is in response to not identifying the overlapping records.
9 . The method of claim 1 , wherein the data quality check instruction is a scalar query, the condition of the data quality check instruction includes a first Boolean value, and wherein:
completing the database query and returning query results is in response to a result of the scalar query converting to the first Boolean value; and
generating the exception that blocks completion of the database query is in response to the result of the scalar query converting to a second Boolean value.
10 . The method of claim 1 , wherein the data quality check instruction is a subquery of the database query.
11 . A method comprising:
receiving, by a query optimizer of a database management system, a database query statement for a database query of a database;
identifying, by the query optimizer, a data quality check hint in the database query statement;
retrieving, by the query optimizer, a data quality check instruction from a plurality of stored data quality check instructions based on the data quality check hint, the data quality check instruction defining a condition that differentiates reliable data stored in the database from unreliable data stored in the database;
generating, by the query optimizer, a query execution plan for the database query statement based on the data quality check instruction so that completion of the database query via the query execution plan is dependent on satisfying the condition defined by the data quality check instruction; and
executing, by the database management system, the query execution plan by:
completing the database query and returning query results in response to the condition being satisfied; and
generating an exception that blocks completion of the database query in response to the condition not being satisfied, the exception further including at least of one of logging the condition not being satisfied, rolling back an operation, and communicating a reason for the condition not being satisfied.
12 . The method of claim 11 , wherein the generating, by the query optimizer, the query execution plan for the database query based on the data quality check instruction so that completion of the query execution plan is dependent on satisfying the condition defined by the data quality check instruction comprises inserting, by the query optimizer, logic into the query execution plan corresponding to the condition.
13 . The method of claim 11 , wherein the condition is selected by the query optimizer based on a keyword in the data quality check hint.
14 . The method of claim 11 , wherein the condition is one of a presence of a target record in a target data table of the database, an absence of duplicate records in the target data table, a shared reference in two target data tables, and a scalar query resulting in a desired binary value.
15 . The method of claim 1 , wherein the unreliable data includes data stored in the database that is at least one of inaccurate, incomplete, inconsistent, and invalid.
16 . The method of claim 1 , wherein the database query statement defines at least one data retrieval operation to be performed on data stored in the database during the database query, and wherein generating the exception in response to the condition not being satisfied blocks the at least one data retrieval operation.
17 . The method of claim 1 , wherein the database query statement defines at least one data manipulation operation to be performed on data stored in the database during the database query, and wherein generating the exception in response to the condition not being satisfied blocks the at least one data manipulation operation.