Adaptive warehouse data validation tool
View Patent ↗Techniques for data validation may include dynamically generating one or more database queries to be performed on a target data warehouse and a baseline data warehouse based on warehouse model metadata for the target data warehouse and the baseline data warehouse. The techniques may further include executing the one or more database queries against the target data warehouse and the baseline data warehouse to receive one or more data sets from the baseline data warehouse and one or more data sets from the target data warehouse. The techniques may further include comparing the one or more data sets from the baseline data warehouse and the one or more data sets from the target data warehouse to validate target data in the target data warehouse against baseline data in the baseline data warehouse.
1. A method for validating data in a data warehouse, the method comprising:
dynamically generating, by at least one processor, one or more database queries to be performed on a target data warehouse and a baseline data warehouse based on warehouse model metadata for the target data warehouse and the baseline data warehouse, including:
generating one or more queries against the warehouse model metadata,
executing the one or more queries against the warehouse model metadata to extract, from the warehouse model metadata, information regarding a warehouse object, the extracted information indicating one or more dimension tables referenced by a fact table in the warehouse object, and
dynamically generating the one or more database queries based at least in part on the extracted information;
executing, by the at least one processor, the one or more database queries against the target data warehouse and the baseline data warehouse to receive one or more data sets from the baseline data warehouse and one or more data sets from the target data warehouse; and
comparing, by the at least one processor, the one or more data sets from the baseline data warehouse and the one or more data sets from the target data warehouse to validate target data in the target data warehouse against baseline data in the baseline data warehouse.
2. The method of claim 1 , wherein target data is loaded using a data model that is different from a baseline data model that models the baseline data.
3. The method of claim 1 , wherein the baseline data and the target data each includes one or more warehouse objects.
4. The method of claim 3 , wherein one or more of the one or more warehouse objects includes the fact table that references the one or more dimension tables.
5. The method of claim 4 , wherein comparing the one or more data sets from the baseline data warehouse and the one or more data sets from the target data warehouse further comprises:
comparing, by the at least one processor, the one or more data sets from the baseline data warehouse and the one or more data sets from the target data warehouse to determine if, for the warehouse object in the target data warehouse, a foreign key for the respective fact table matches a primary key for a dimension table of the one or more dimension tables referenced by the respective fact table.
6. The method of claim 4 , wherein for each of the one or more warehouse objects, the warehouse model metadata includes respective one or more of physical object metadata, warehouse object metadata, and reference metadata.