Techniques for Data Subset

In my previous posts, I explained about Data Subset in TDM & Implementation Approaches to Data Sub-setting.  In this post, I will explain some basic techniques with the help of which we can do a data subset.

• First N records
• This is a pretty simple technique, wherein the first N records of a table is retrieved from the Production database.  This can be achieved using a simple SQL Query such as
• SELECT TOP 10000 * FROM DBO.CUSTOMERS
• Based on a filter criteria
• This is a scenario where the subset conditions can be based on a simple filter criteria like Age > 50, City = London, etc.  This is easier to implement in cases where the subset requirements are less complicated.  An example query for such technique would be
• SELECT * FROM DBO.CUSTOMERS WHERE AGE > 50 AND CITY = 'LONDON'
• Based on a complex SQL query
• Sometimes the subset requirements can be more complicated.  It might involve dependencies across multiple tables.  What that means is

Implementation Approaches to Data Sub-setting

In one of my previous post, I described about the process of Data Subset.  In this post we will focus on the implementation approaches to data sub-setting.

There are 3 broad categories in which you can implement sub-setting.

SQL Query based approach

In this approach, we will use SQL queries to fetch the subset of the production data and load them it into the target environment.  Lets assume you have 2 tables in your production from which you need to create a small subset.  The following shows the relationship of the tables Customers and Orders where they are related through the custid field.  

The picture also shows the sample data within those tables.  So we need to subset this.  We find out a sample condition.  Lets assume we will pull out only the customer ids which are odd numbers.  A simple query will do this trick.  The following will be the query for the Customers table.

Top smells that indicate that your project needs TDM

In my previous posts, I explained about the basics of Test Data Creation, Challenges in Production Cloning, Data Subset and Data Masking.  In this post we will focus on a slightly different note.

Invariably every problem has a symptom that we call smells in the modern Agile days.  So this post is going to focus on the typical smells that indicate that your project needs Test Data Management (TDM).

• Testers waste more time preparing test data rather than testing the application
• This is probably the number one symptom or smell that warrants a TDM process and solution in place.
• Testers depend a lot of Business Analysts to provide the required test data
• This is also one of the top symptom when it comes to the need for TDM.  There is a lot of dependency for test data from the Business Analysts.

Data Masking in TDM

In my previous posts, I explained about the Challenges in Production Cloning.  One of the major challenges in the Production Cloning approach is Data Security.  This post will focus on the solution for Data Security, Data Masking.

As already explained, Data Masking is the process of masking the sensitive fields from the complete data set.    The whole objective of data masking is to ensure that no sensitive data is leaked into non-production regions like the Dev and Testing regions.

What are the sensitive fields that needs to be masked?  That basically depends on the project needs.  But some of the generic fields that need to be masked are:

• Personal information like First names, Last Names, Email IDs, DOB, Phone & Fax numbers, SSN Numbers, National Insurance Numbers, Other national unique identifiers.
• In Banking, Financial Services & Insurance industry - Bank Balances, Account numbers, Credit card numbers, Policy numbers, etc.
• In Healthcare industry - PHI attributes like Medical record numbers, Member IDs, etc.

This list is by no means exhaustive, but will give a fair idea of how many fields are sensitive in nature that needs to be handled with care.  Any lapse in masking any of these fields might have a big impact on the Organization as a whole.

Challenges in Data Masking

Data Subset in TDM

In my previous post, I discussed the Challenges in Production Cloning approach.  In this post, we will focus on its solution, the Data Subset process / Data Sub-setting.

Data subset is the process of slicing a part of the Production Database and loading it into the Test Database.  For ex. instead of cloning a 50 TB production database, create a subset that is only 50 GB worth data and put it back into the Test Database.  Lets assume in a retail application, you have a Customers table having 10 million customers and Orders table having 100 million orders and 100 million other transaction tables, our subset process will try to shrink the sizes to good reasonable limits as depicted in the picture below.

Advantages of data sub-setting

Challenges in Production Cloning approach

In my previous articles, I have already discussed the topics "How to create Test Data" and "Top 3 Challenges in using Production data in Test Environments".  In this post we will focus on the challenges that we face in Production Cloning approach and how to overcome those challenges.

1.  Infrastructure

Even though it is highly recommended to have the Test Environment in the same lines as Production, it is not always feasible to test under those real-time conditions.  It is highly recommended to do Performance / load / stress tests exactly mimicking the Production database, but the expensive infrastructure requirements might be an overkill for Functional Testing.  But cloning might force you to have production like infrastructure which will translate into higher costs for the customer.

2.  High Storage Costs

Another major challenge associated with Production Cloning is that all the production data needs to be stored in testing region.  Assuming the production data is 50 TBs (Terabytes), the Test Database also needs to hold 50 TBs of data.  So storage has to be provided for storing all of the data.  And with the databases being backed up regularly, that would mean higher storage costs for the customer.

Top 3 Challenges in using Production data in Test Environments

In my previous post "How to create Test Data", I explained the concept of creating test data directly from the production data.  In this post we will concentrate on the Top 3 challenges in using the Production data for testing purposes.

Data Security

This is by far the most crucial challenge of using Production data in Test Environments.  Production data can contain a lot of sensitive information.  Even though the data sets will be rich in nature in the Production database, the very thought of using production data involves a lot of risk.  For ex. if you are testing an application for a bank, production data will contain real customer information like Names, Addresses, Account Numbers, Balances, Credit Card Numbers, etc.  As you can see, if you try to use these data for testing, it exposes huge security risks for the bank. So how do we overcome this, the answer is Data Masking.

Data Masking is the process of masking of the sensitive fields from the complete data set.  Please read my future post on Data Masking and the Techniques used for Data Masking for more details.  The following figure depicts the data security challenge and the approaches.

Data Security Challenge