Verifying Data in Production Whitepaper

When you're testing in development, the goal is to make sure that the code is working successfully, so you typically control the inputs to the processes by using known test data. This approach gives you the ability to verify that the process produces the expected results. So, in this case, the focus of the testing is on the code functionality.

When you're verifying data in production, however, you don’t control the inputs. You're now dealing with real, production data.

So the focus shifts from verifying the process or code itself to verifying that the data manipulated by the process is still valid and reasonable. You do this by verifying that the input matches the output, with any appropriate transformations or modifications accounted for. Since you no longer control the input, you also have to validate that the output is reasonable, based on past history. In development testing, your tests will often deal with individual records, comparing expected and actual values at a detail level.

However, since the data volumes in production systems can be quite large, and the data is expected to be transformed as it moves between systems, you don’t usually focus production data verification on a line-by-line reconciliation.

The performance impact from doing row-by-row validations in a production system usually outweighs the benefits. Rather, you would focus initially on testing aggregates and roll-ups of information. If these show that the data is incorrect, then you would go to a more detailed level of data to identify the underlying issues that caused the incorrect data.

Back to TOC

Data structures are usually different between source operational systems and reporting data structures, and the data is often cleaned, transformed, and otherwise modified when it is moved from repository to repository. Reporting applications can add their own layers of transformation, including new calculations and filtering, that have to be accounted for in the reconciliation.

The primary measures of reconciliation are totals, counts, and balances. You can calculate these easily from data storage by running aggregate queries against the data store. For example, if you are verifying a SQL Server repository, you might use a query like the images provided to get the count of orders and the totals for sales.

A similar query can be executed against the data warehouse to retrieve the same numbers, and the results can be compared to validate that they match. Any differences should trigger additional research to discover the source of the mismatch. These queries might also need to be filtered for specific time ranges.

It usually isn’t necessary, nor wise for performance, to process your entire history for the totals each time you run your data verification. Rather, you will likely want to filter the results to data that has been modified or moved since you last ran the verification.

Another common approach is to run the verification for a specific time window—for example, records modified in the last 24 hours or the last 7 days.

However, if the volume of “unknown” geography relationships for sales grows to 10%, that might be unacceptable to your business users for reporting accuracy. This tolerance will be different for other details—most business users would be unhappy about 5% of their sales relating to “unknown” products.

To verify that data is related, you'll be testing counts and totals, but they'll be subdivided by the relationships. In the case that you are checking a specific relationship, like an “unknown” record of a dimension, you might use a query that filters results to just that subset of data. This allows your data verification tests to do specific checking for certain relationships and report any unexpected variances.

Another technique for validating relationships can be to look at specific groupings and the distribution across groups. This can help you to quickly identify outliers and unusual relationships.

For example, the query above would let you easily compare categories to see whether sales were abnormally high in any particular category, which would indicate that the categories were being related incorrectly. By using a second query (see below) that leveraged the results from the categorized sales query, you can test for anomalies in the distribution.

These values can also be useful in the next step, checking the reasonableness of the data. This is another place where being able to persist the values and results from the verification tests is beneficial.

As currently implemented, this test is useful but doesn’t deal well with the normal variances that most organizations experience.

Perhaps Fridays tend to be your busiest days or there are seasonal factors that lead to significant differences in the data for different time periods.

This type of test gets much more powerful when you combine it with historical results and expectations. If your data verification framework enables you to store test results, you can implement verification tests that compare the current results to historical data and trends.

You can also leverage other sources of historical data to implement trend comparisons. With the proper setup, these tests can automatically adjust to changing business scenarios and trends.

One way to leverage historical information is to use standard deviation. In the example query batch below, the historical test results for total sales are used to determine a baseline standard deviation value.

The difference between the current day’s sales and the previous day’s sales is then calculated and compared to the standard deviation. If it exceeds the calculated standard deviation, an alert would be raised.

Back to TOC

However, step-by-step validtion is targeted toward technical users who have a good understanding of the complete system. This approach might not work well for business users, who are often less concerned with where the problem occurred than the end result. Operations people do find the finer-grained approach beneficial.

End-to-end

End-to-end verification focuses comparing the starting values in your source systems (one end) with the final values in your reporting data structures, reports, and dashboards (the other end).

The goal of this validation is to look at the macro level and major endpoints of the system, without considering the individual transition points. Endpoints are usually the places where the data crosses the boundaries of your data-centric systems. Endpoints can be originating (where data enters your data-centric system) or terminating (where data flows outside the control of your data-centric system). In some cases, an endpoint might function as both.

Some data-centric systems might have many endpoints. For example, systems that include point-of-sale applications might have hundreds or even thousands of endpoints. In this case, you have to identify a reasonable place to start the testing. If there is an easy way to replicate the tests over multiple endpoints, that might be an option. If not, you might consider working a little further up the flow of data to a point where the systems are combined into a common store.

Out of the three R’s, end-to-end tests usually focus on reconciliation and reasonableness. You will typically want to reconcile the data at your endpoints, and verifying the reasonableness of the data in your endpoints is critical to having confidence in the data. Relationships can be validated in these verifications too, but more commonly step-by-step verification is used to validate relationships.

End-to-end verification works very well as a way of reporting system status for business users, who are generally more interested in the high-level picture. Often, the business user really wants to know: “Can I run my reports this morning and have confidence in the results?” An end-to-end test that reconciles the source system totals to the values in the data warehouse can fill this need nicely.

End-to-end tests are also useful as quick smoke checks. Because end-to-end verification usually has fewer test cases and focuses on high-level aggregates, they can be run more quickly than the step-by-step verifications. Many organizations benefit from having both—they run the end-to-end verifications to determine whether there are any major issues, and then run the step-by-step validations to identify specific problem areas.

Back to TOC

Transition points

The transition points where data is moving or being transformed in this system are listed below, with details on example verifications for each.

1. Order processing to stage

Very little transformation is done at this point. The primary goal is to move the data to the staging area for additional processing.

To validate accurate data movement, you would implement reconciliation that compared the key metrics from the order processing system to the staging area. This would include total orders, total sales amounts, and current inventory levels. The queries for retrieving this information would be very similar since there aren’t significant changes to the data structure in this step.

Relationship and reasonableness verifications are optional at this step. In many scenarios, it would be beneficial to implement some reasonableness verification that looks at the count and total of the data transferred to determine whether it matches the historical trends.

If the data movement is done incrementally, the verifications should be filtered so that they look at the current increment. For example, if the sales information for the previous day is being moved to the staging area, you would want to ensure that your queries were filtered to include only that day’s information. That helps narrow the scope of any identified data mismatches.

For example, you might have sales that are filtered out for missing or invalid data during processing. Validation would ensure that all totals from the original staging tables are compared to totals that include both the data inserted into the data warehouse, as well as any data that has been excluded as invalid.

4. DW to OLAP

This transition point is primarily focused on changing the storage of the data rather than modifying it. Typically, the data is retrieved from a relational store and written to a multidimensional store with different physical storage properties. As part of the OLAP storage, additional calculations might be provided.

Because the data and business rules aren’t typically coming into play, the verification for this transition point is focused on reconciliation and relationships. The total amounts and counts should not change, and the data categorization should remain the same.

Your verification tests should focus on ensuring that the totals match, that the associated facts and dimensions are still aligned, and that any new calculations implemented in the OLAP store return correct values.

6. OLAP to report

The verification for this transition point matches the DW to Report transition point. In this case, the only difference is the data source for the reports.

2. CRM

The CRM, like order processing, is an originating end point. You would apply the same verification steps to it. The metrics would be values for customer counts, outstanding quotes, geographic breakdown of customers, etc.

3. DW

The data warehouse is considered a terminating endpoint, as some users report directly from the warehouse, and other applications and systems use it as a source of data. For verification, your primary concerns are reconciliation and reasonableness.

The reconciliation verification should compare the amounts and counts with the originating endpoints. In this case, that would mean comparing the data warehouse total sales amount and item counts with the values captured from the order processing system, and the customer counts and quote amounts with the CRM system.

This is another point where you will want to persist the values for the recompilation verification so that you can test the reasonableness of the data.

Even though the data was tested for reasonableness at the source, and the data is reconciled, the processes for loading the warehouse can be complicated. The reasonableness check, in this case, verifies that the data matches trends, and those unusual scenarios (like a single salesperson making all of the sales for the day) are caught.

4. OLAP

Since the OLAP system is another point where data is consumed by other systems and users, it's also considered a terminating endpoint. In this case, you would focus on reconciling to the CRM and order processing systems, using the same metrics as described above for the data warehouse.

5. Reports

Reports and dashboards are often the terminating endpoint for users, although thanks to the prevalence of Excel and the ease of exporting most reports to it, the data might still flow to other downstream users.

For this endpoint, reconciliation to source systems is the most important verification step. Particularly since this might be the only aspect of your data-centric system that end users interact with, it's important to make sure that the values on the report reconcile to the source systems and to the data warehouse or OLAP store, as appropriate.

Back to TOC

• History: Storing a history of test results is beneficial for reporting purposes, as well as seeing trends in test results. It’s also vital to doing effective reasonableness verification, so this is a must-have feature.

• Support for multiple types of data stores: Very few organizations work with a single application interface or data storage technology. Your test framework should support all the tools you work with and be flexible to handle future ones. Ideally, it will support an extensibility model so that new data providers can be added easily.

• Dashboards/reports: In addition to alerting, some users will want the ability to see current verification status of their data-centric systems, and to drill into details about test failures and issues. Ideally, this should be web-based or in a format that is easy to share with your users.

Having a proper framework in place means that you can focus on implementing the business logic for your tests, rather than worrying about how to run a query. While you can do some level of data-centric testing without a framework, it is strongly recommended.

Back to TOC

Evaluating data-centric testing approaches, tools, and techniques

This article and the related content, Testing Data and Data-Centric Applications and Testing Data-Centric Code in Development have focused on data-centric testing from a development standpoint and for production data verification. I've presented some of the benefits of adopting a data-centric view of testing in your organization and provided some examples of how to implement data-centric testing at both the development and production level.

With this introduction to data-centric testing, you can evaluate your approach, tools, and techniques for dealing with the data-driven nature of organizations today. Data is a vital part of the business and is only becoming more important. 

At SentryOne, we believe that it’s necessary to go beyond saying that your data is good—you should be able to prove it. If you can show, through repeatable tests and verification, that your data is reconciled, related, and reasonable, you will have confidence in your work and give your users confidence to make the decisions they need to make. And at the end of the day, everyone working on your data-centric systems will be happier and more productive.