Data Subsetting: Smaller Test Databases With Integrity Intact

Data subsetting is the process of extracting a smaller, representative portion of a database while keeping every row that the selected records depend on, so the subset still works as a database. Teams use it to build test environments that are faster to provision, cheaper to store and expose less personal data than a full production copy. The hard part is referential integrity: a subset with orphaned records is not usable test data.

What data subsetting is

A subset is defined by where you start and which relationships you follow. You choose starting criteria, such as 5% of customers, all orders from one region, or the records behind specific test cases. The subsetting process then follows relationships to pull in everything those records need: the parent rows they reference and the child rows that belong to them.

The output is a smaller database with the same schema, valid keys and real-world data patterns, ready to be masked and loaded into development, QA or integration environments.

Why subset test data

• Cost: full copies multiply storage across every environment, branch and team. Subsets shrink that footprint.
• Speed: smaller databases restore, refresh and reset faster, which matters when CI pipelines create an environment per run.
• Privacy: GDPR's data minimization principle (Article 5(1)(c)) requires personal data to be limited to what is necessary for the purpose. A subset contains fewer people, so there is less to mask and less to lose.
• Focus: a subset built around specific test cases makes failures easier to diagnose than a full copy.

Subsets are not right for every test. Performance and volume testing may need full-size data, and some migration tests need complete history. Those cases call for masked full copies or synthetic scale-up instead.

Referential integrity in subsets

Every relational subset has to answer the same question: if I keep this row, what else must I keep? Why referential integrity matters in test data management covers the consequences of getting it wrong. The rules that matter most:

1. Parents are mandatory. If you keep an order, you must keep its customer, product and sales representative, or foreign keys point at nothing.
2. Children are a choice. Keeping a customer does not force you to keep every one of their orders, but tests usually need some, so define how far down the hierarchy to follow.
3. Undeclared relationships count. Many applications enforce relationships in code rather than in the database schema. These must be declared, or the subset will look valid to the database while the application breaks.
4. Cycles need a rule. Tables that reference each other, or themselves, such as an employee table with a manager column, need a strategy that stops the traversal from pulling in the whole database.
5. Ratios are approximate. Asking for 10% of orders can return far more than 10% of customers or products, because shared parent records are always included.

Data subsetting approaches

Ratio-based

Keep a random fraction of rows from one or more driving tables and follow the relationships. This suits general-purpose development and QA databases that need realistic variety.

Filter-based

Select rows with conditions, such as a region, product line, customer segment or date range, expressed as SQL filters. This suits team-specific environments and keeps the data relevant to one module.

Test case-driven

Start from the exact entities a test suite needs, for example the customers referenced by automated regression tests, and include their full dependency graph. This suits stable, repeatable automation.

Time-based (time slicing)

Keep transactions from a recent period, plus the master data they reference. This is common in ERP systems, where years of history make up most of the volume.

Combine subsetting with masking

Subsetting reduces how much personal data reaches a test environment; it does not remove it. A 5% subset of a large customer database still contains many real people. Mask the subset before it leaves a controlled zone, and run both steps in one pipeline:

• Subset first, so masking processes less data.
• Run both steps inside the controlled environment, so no unmasked intermediate copy lands in test.
• Mask consistently, so the same customer gets the same replacement in every table of the subset and in any other system you load.
• Rerun both on every refresh, from the same versioned configuration.

SAP considerations

SAP systems make subsetting harder than most databases:

• Scale and history: transactional tables hold years of documents, which is why full system copies are so large.
• Relationships outside the database schema: SAP defines most table relationships in the application layer rather than as database foreign keys, so a tool that reads only database constraints sees a fraction of the real links.
• Document flows cross periods: a purchase order, goods receipt and invoice can fall in different months. Cutting strictly by date leaves broken process chains.
• Master and transactional data behave differently: transactions can be filtered by posting date, but customers, vendors and materials referenced by those transactions must be kept regardless of when they were created.
• Cross-system processes: order-to-cash and procure-to-pay often span SAP and non-SAP systems, which all need the same customers and documents.

Time slicing addresses this by selecting a period, then looking outside it for the records each business process needs to stay complete. Database copies, time slicing and refreshes compares the options for SAP landscapes. Combined with masking, time slicing produces SAP test systems that are smaller and safe to use.

How to validate a subset

A subset that loads without errors can still be unusable. Before handing it to teams, check that:

• No foreign key is orphaned: every key value in the subset exists in the table it references, including relationships declared outside the schema.
• Row counts make sense: investigate tables that came back empty or unexpectedly large. An empty parent table usually means its child tables are empty too.
• Business processes are complete: an order has its lines, delivery and invoice; an employee has an organizational assignment.
• A smoke suite passes: run a handful of end-to-end tests against the subset, not just schema checks.
• No real personal data remains after masking, including in free-text columns and attachments.

How Synthesized helps

Synthesized subsets databases with ratios, per-table SQL filters and virtual foreign keys for relationships the schema does not declare, keeping foreign keys valid with no orphaned records. Subsetting, masking and generation run in the same test data management platform, so a subset can be masked before it reaches test. For SAP, time slicing is driven by the business processes and modules you select, applies different logic to master and transactional data, and looks outside the chosen period so no process breaks. SAP data masking then replaces personal data with the same values in every table and connected system.

FAQ

What is data subsetting?

Data subsetting creates a smaller version of a database by selecting a portion of its records and including every related record they depend on. The result keeps the original schema and valid relationships, so applications and tests run against it as they would against a full copy, with less storage, faster refreshes and less personal data.

Does data subsetting make test data GDPR compliant?

Not on its own. Subsetting supports data minimization under GDPR Article 5(1)(c) by reducing how much personal data is copied, but the records that remain are still real personal data. Combine subsetting with masking or pseudonymization, access controls and defined retention, and check with your DPO or counsel how the rules apply to your environments.

How do you keep referential integrity when subsetting?

Follow relationships from the records you select: always include referenced parent rows, decide how many child rows to keep, and declare relationships that exist only in application logic so they are followed too. Handle self-referencing and circular tables with an explicit rule, and validate the subset by checking that no foreign key points to a missing row.

What is time slicing in SAP?

Time slicing creates a smaller SAP test system by keeping transactional data from a selected period, such as recent months, plus the master data and related documents those transactions need. Because SAP document flows cross period boundaries, a good time slice looks outside the date range to keep orders, deliveries and invoices complete and usable for testing.

Sources

• GDPR Article 5
• Synthesized docs: how subsetting works
• Synthesized docs: data filtering
• Synthesized docs: virtual foreign keys
• Synthesized docs: sap