Copying large, production databases in their entirety into test environments is expensive, slow and risky from a data protection perspective. Following synthetic test data generation and data masking, the next step is database subsetting. This involves creating smaller, contextually meaningful and referentially consistent subsets from extensive data sets.
The first two parts of this series of articles have examined two fundamental strategies of modern test data management. Part 1 showed how synthetic test data can be generated from scratch to provide realistic datasets with no link to production information. Part 2 focused on data masking and explained how sensitive content in near-production data can be modified in such a way that data protection requirements are met whilst maintaining business context.
In practice, however, neither of these strategies alone is sufficient to cover all the requirements of modern development and test environments. Many organisations have very large production databases that have grown over the years and contain a vast number of records. Copying these datasets in their entirety into development, integration, training or acceptance systems is usually neither economically nor organisationally viable. The storage requirements are high, provisioning takes a long time, updates are labour-intensive, and the attack surface for sensitive information increases significantly. This is precisely where database subsetting comes into play. Instead of duplicating an entire database, a targeted subset is created. This subset is significantly smaller but still reflects the relevant business relationships. It is crucial that referential integrity is maintained. If, for example, a selection of customers is transferred, the associated orders, invoices, line items, payments or service cases must be carried over consistently. Only then will the test database behave in the same way as the original system from a business perspective.
Database subsetting is a controlled extraction process that reduces data volume, preserves business relevance and integrates data protection measures. Modern platforms such as IRI Voracity provide tools for this purpose, including subsetting, masking, synthetic data generation and automation.
Why complete database copies are problematic
In many organisations, it has long been common practice to regularly copy production databases into test or development environments. At first glance, this approach seems obvious: the data is real, complete and reflects all business-specific scenarios. This provides developers and testers with an environment that is very similar to the production system. For small or isolated applications, this approach may work in the short term. In large system landscapes, however, serious drawbacks quickly arise. Production databases can reach enormous sizes. If they are copied in full to several non-production environments, the storage requirements multiply. Large database copies cannot be updated as often as required. If setting up a test environment takes several hours or even days, this process is no longer compatible with agile development models, continuous integration or automated regression testing. Test data thus becomes a bottleneck. Teams work with out-of-date data, tests are difficult to reproduce, and fault analysis becomes time-consuming. The most significant drawback relates to data protection. Complete database copies often contain personal information, trade secrets, etc. The more frequently such copies are created and distributed, the greater the risk of unauthorised access. Even when data masking is used, the question remains as to whether all sensitive content has actually been identified and correctly transformed.
Basics of Database Subsetting
Database subsetting refers to the creation of a smaller, manageable yet functionally complete subset of a larger database. The aim is to include only those records required for a specific testing, development or training purpose. The resulting dataset should be smaller than the original, but still reflect the relevant relationships, data distributions and business rules. Database subsetting therefore begins with the master table. From there, the dependent records are identified along the foreign key relationships. If, for example, a defined set of customers is selected, all associated orders, line items, invoices and payments are included. Similarly, higher-level master data such as products, regions or tariffs can be included, provided they are necessary to ensure consistency. Database subsetting has three objectives: reduction, consistency and relevance. Reduction means that the volume of data is significantly reduced. Consistency means that primary and foreign key relationships are preserved. Relevance means that the subset is appropriate for the testing objective.
Strategies for meaningful database subsets
Suitable data can be selected using various strategies. One simple approach is percentage-based subsetting. This involves selecting a specific proportion of the data, for example five or ten per cent of a table. This strategy is suitable for initial development and functional tests when a smaller but fundamentally realistic data set is required. It is easy to explain and can be readily automated, but does not always yield results that are representative of the business context. Rare cases may be lost, whilst frequent standard cases dominate. A second strategy is rule-based subsetting. Here, business criteria are defined, such as all customers from a specific country, all orders from a particular year, and so on. This approach is useful when specific test objectives are being pursued. If, for example, a new billing logic is to be tested, a subset can contain precisely those contracts that are relevant to this logic. Rule-based subsetting is usually more meaningful than a purely random selection, but requires a good understanding of the data model and business processes. A third strategy is scenario-based subsetting. Here, data sets are specifically compiled along business process chains. A scenario might, for example, consist of new customer creation, order, delivery, invoice, payment and complaint. For testing, only those data records that fully map this process chain are then extracted. A fourth strategy is risk-oriented subsetting. Here, specific data areas are selected that have been prone to errors in the past or are particularly critical. These include, for example, special discounts, international tax rates, legacy contract models, migration effects or rare status combinations. This strategy increases test coverage because it not only takes typical cases into account but also deliberately incorporates complex boundary conditions.
Modern subsetting tools support these strategies via graphical wizards, SQL filters, rule definitions or scripts. In the IRI Workbench, the size, content and sorting of a subset can be defined, and masking or transformation rules can be applied to target tables.
Referential integrity as a key prerequisite
Relational databases do not consist of isolated tables, but of a network of dependencies (primary keys, foreign keys, constraints, indexes, business rules). A subset is only useful if these relationships remain valid within the reduced dataset. If a record is removed, orphaned relationships may arise in many places. A test database containing such inconsistencies leads to errors that stem not from the application, but from the faulty test data. A subsetting procedure must therefore analyse the relational structure of the data model. It must identify which tables depend on one another, the order in which data must be extracted, and what additional master data is required. The loading order is also often relevant: when setting up the target environment, parent tables must be loaded before child tables to ensure that foreign key checks do not fail.
In addition to technical foreign keys, many applications also contain implicit business relationships. Not every dependency is clearly modelled in the database schema. Some relationships arise via codes, composite keys, configuration values or application logic. A subsetting process should therefore also be able to incorporate business rules. IRI addresses this challenge through a wizard-based approach in the Workbench, where referentially correct child tables can be generated from a parent table whilst simultaneously applying masking or mapping rules.
Combining subsetting and masking
Although database subsetting reduces the volume of production data, it does not automatically eliminate the need to protect that data. Even a small subset may contain personal or business-critical information. For this reason, subsetting should, in most cases, be combined with data masking.
First, the data required for the subset is defined. Sensitive fields are then identified and assigned masking rules. During extraction, the data is not only reduced in size but also transformed. Names, email addresses, telephone numbers, account details, identifiers or free-text fields can be replaced, encrypted, pseudonymised or removed. Consistency is also crucial here. If a customer number is masked, the same replacement value must appear in all dependent tables. For testing purposes, it is not sufficient simply to obscure sensitive data; the masked data must continue to behave plausibly.
An example: In a banking application, a subset of all loan agreements for a specific product type is to be generated. The data includes customers, contracts, repayment schedules, collateral, payment histories and dunning statuses. These relationships are functionally important for development and testing. At the same time, real names, account numbers and addresses must not be visible. A combined subsetting and masking process therefore extracts only the relevant loan cases, preserves all relationships and transforms sensitive attributes into plausible replacement values.
The IRI solution supports this integration by allowing masking rules to be applied within subsetting jobs. In doing so, FieldShield functions such as encryption or pseudonymisation can be applied across the extracted data.
Fully Automated Test Data Management
Fully automated test data management means that test data processes are standardised, version-controlled and embedded within technical workflows. This includes the generation of synthetic data, the masking of near-production data, the subsetting of large databases, deployment to target systems and regular updates. These steps should not have to be carried out manually each time, but should be controllable via scripts, jobs, pipelines or self-service processes.
A typical automated workflow might look like this: after a new test environment has been created, a subsetting job is automatically initiated. This extracts a defined dataset from a source, applies masking rules, loads the data into a target environment and then carries out validations. Automated tests then commence. Once complete, data sets can be archived, reset or regenerated. In this way, test data provision becomes part of the development process.
Test Data Management thus describes the process of creating, maintaining and provisioning test data in controlled, secure and consistent states. A comprehensive TDM strategy includes, amongst other things, discovery, masking, subsetting, synthetic data generation, provisioning, quality checks and governance. In the IRI software, shared metadata plays a key role in this. Rules, data classes, masking specifications and job definitions can be reused. If a rule is defined centrally, it can be applied consistently across different jobs. The integration of test data processes into CI/CD processes can take place at several levels. At the simplest level, subsetting or masking jobs are launched via the command line. A build server calls up the relevant scripts, generates the required data and loads it into a test database.
Reproducibility is key. If a test fails, it must be possible to trace the data state with which it was executed. To this end, job definitions, selection criteria, masking rules and versions should be documented or version-controlled. Ideally, test data jobs are managed alongside application code or infrastructure definitions. This makes it possible to reconstruct later which data set led to which test result.
Practical example: Creating a referentially consistent subset
The database contains tables for customers, addresses, orders, order lines, products, invoices and payments. The aim is to set up a smaller test database. It should contain only a selected subset of customers, but fully represent all dependent business transactions. At the same time, sensitive customer data should be masked.
The first step involves analysing the data model. This includes identifying the key tables and relationships. In this example, the customer table forms the starting point. From there, relationships lead to addresses, orders and invoices. Orders, in turn, refer to order lines and products. Payments relate to invoices. In the second step, the selection criteria are defined. For the test environment, for example, all customers who have placed at least one order in the last twelve months and belong to a specific market segment should be included. Alternatively, a fixed number of customers or a percentage could be selected. In the third step, the dependent data is identified. For each selected customer, all relevant addresses, orders, order items, invoices and payments are included. In addition, the referenced products must be included so that the order items remain fully interpretable. Depending on the data model, further master data may be required, such as currencies, tax rates, delivery methods or discount groups. In the fourth step, masking rules are defined. Names are replaced with realistic aliases, email addresses are regenerated to be syntactically correct, telephone numbers are transformed whilst retaining their format, and customer numbers are consistently pseudonymised. Business metrics relevant to testing are retained or altered only in a controlled manner. This ensures the data remains plausible without revealing real individuals. In the fifth step, the subset is generated and loaded into a target environment.
The result is a significantly smaller test database that contains business-related transactions but no longer reveals any unprotected sensitive information. This database can be used for development, automated testing, fault analysis or training.
Conclusion
Database subsetting closes a key gap in modern test data management. Whilst synthetic test data generates entirely new datasets and masking protects production-related data, subsetting enables the targeted handling of very large databases. Instead of transferring complete production copies to non-production environments, smaller, referentially consistent and functionally relevant subsets are created. The benefits are immediate: test environments can be provisioned more quickly, infrastructure costs are reduced, data protection risks are minimised, and teams can carry out more targeted testing. However, it is crucial not to view subsetting in isolation. Only when combined with masking, synthetic data generation, automation and governance does it lead to holistic test data management. Platforms such as IRI Voracity demonstrate how these building blocks can be technically integrated: data classification, masking, subsetting, synthetic generation and automated provisioning all work in tandem.