Test Data That Looks Like Production

A query flies through a tiny test table and stalls on the real workload. Test data that looks like production needs similar shapes and relationships, not real customer details.

Profile Shapes Before Generating Test Data That Looks Like Production

Count rows by important categories, dates, statuses, and tenants. Measure NULL rates, duplicate patterns where allowed, and skew in common join keys. These shapes affect query plans and application behavior. A uniform random generator can create many rows while missing the conditions that matter.

I ask which query or workflow the test is meant to support. A report grouping by month needs realistic date distribution. An upsert needs existing and new keys. A spatial search needs clustered and sparse areas. “Large” alone is not a test data specification.

Keep profiles as aggregates under appropriate privacy review. The goal is to reproduce distributions without copying names, emails, or secrets. Build synthetic values under a documented generation rule.

SELECT OrderStatus, COUNT_BIG(*) AS RowsPresent
FROM dbo.Orders
GROUP BY OrderStatus
ORDER BY RowsPresent DESC;

Preserve Referential Integrity

Create parent rows before children and maintain valid foreign keys. A large OrderLine table with random CustomerIds that do not exist cannot exercise real joins. The generator should respect one-to-many relationships and optionality.

I use the model’s constraints as a test. If synthetic data loads only after disabling all foreign keys, it is not modeling the production domain. Negative test cases can be added separately and labeled as invalid data.

Include edge relationships: customers with no orders, orders with many lines, and products with no recent sales. These cases reveal query logic and UI states that a perfectly uniform dataset misses.

SELECT o.CustomerId
FROM dbo.Orders AS o
LEFT JOIN dbo.Customer AS c ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;

Match Volume Where It Changes Behavior

A handful of rows cannot expose sort spills, poor estimates, long scans, or index maintenance cost. Generate enough rows to cross the workload’s important thresholds, then verify table and index sizes. Do not invent a universal row count in the design. Use the target system’s measured scale.

I compare the test environment’s resource limits with production. A production-sized table on a much smaller server can be useful for stress testing but not for predicting production duration directly. Label what the test can and cannot show.

Include growth over time, not only one static snapshot. A partitioned table needs old and current ranges. A report query can behave differently after a new month arrives.

SELECT OBJECT_NAME(object_id) AS table_name,
       SUM(row_count) AS rows_present,
       SUM(used_page_count) * 8.0 / 1024 AS used_mb
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
GROUP BY object_id
ORDER BY used_mb DESC;

Reproduce Skew and Hot Keys So Test Data Looks Like Production

Production data is rarely uniform. A few tenants or products can hold a large share of rows. Some statuses can be rare. The optimizer’s choice can change when a parameter selects a hot key versus a cold one. Synthetic test data that looks like production should include that skew.

I test both common and rare parameter values. One cached plan can fit one poorly. A generator that assigns every customer the same number of orders hides that issue. Use a documented distribution that resembles the aggregate profile.

Avoid reproducing a real person’s unique pattern. Aggregate distributions and synthetic keys are enough for most performance tests. Privacy is part of test data quality, not a separate cleanup after generation.

SELECT TOP (20) CustomerId, COUNT_BIG(*) AS OrderRows
FROM dbo.Orders
GROUP BY CustomerId
ORDER BY OrderRows DESC;

Include Valid and Invalid Edge Cases

Create NULLs where allowed, boundary dates, maximum lengths, duplicate business keys where the source can send them, and rejected rows for the load path. Keep invalid cases in a separate feed or test batch so they do not undermine the main dataset’s integrity.

I write expected outcomes for each edge case. An invalid date should be rejected with a clear reason. A missing optional phone number should load. A test set without expected results is just colorful data.

Use stable seeds or deterministic generation where possible. Repeatable data makes before-and-after comparisons meaningful. If every run generates a different shape, a query plan change can be hard to attribute.

SELECT MAX(DATALENGTH(CustomerName)) AS LongestNameBytes,
       SUM(CASE WHEN CustomerName IS NULL THEN 1 ELSE 0 END) AS MissingNames
FROM dbo.Customer;

Keep Sensitive Data Out of the Test

Do not copy production personal information into development simply to make rows look real. Masking can fail to remove hidden fields or preserve linkability. Synthetic generation from aggregate profiles is safer for many test needs. Apply privacy rules to logs, exports, and backups too.

I review free-text columns separately. They can contain personal data even when structured columns are masked. A realistic Notes field does not need a real customer’s words. Generate plausible lengths and formats instead.

Access controls still matter in test environments. A dataset with no real identities can still reveal business volumes or patterns. Keep only the profile detail needed for the test.

SELECT name, is_masked
FROM sys.masked_columns
WHERE object_id = OBJECT_ID(N'dbo.Customer');

Validate That Test Data Looks Like Production

After generation, compare row counts, category proportions, NULL rates, key distribution, date span, and referential integrity with the approved profile. Record deviations. A generator can finish successfully while producing a very different workload.

I run the actual query and application paths intended for the test. A dataset can match statistics but miss one critical workflow. Check both performance and correctness, including edge cases and failures.

Test data looks like production when it exercises the same decisions and query shapes without exposing real records. Profile, generate, validate, and label its limits. Then a fast test result has a meaningful context.

SELECT MIN(OrderDate) AS FirstOrder,
       MAX(OrderDate) AS LastOrder,
       COUNT_BIG(*) AS OrdersPresent
FROM dbo.Orders;

Which distribution makes the production query hard: a few large customers, many tiny orders, seasonal dates, or heavily repeated values? A generator that fills every column uniformly can make an index appear better than it is. I sample the shape of permitted production aggregates, then create synthetic values that reproduce skew without copying private records.

Preserve foreign-key relationships and the order of operations. Parent rows must exist before child rows, and test data should include valid edge states such as empty orders and customers with many orders. Add intentional NULL patterns only where the schema permits them. A database full of random text can pass a row-count check while failing every meaningful join test.

Scale the data to exercise the plan choices and maintenance work you care about. Check table size, index size, statistics, and query plans after loading. Record the generator seed and rules so another run produces a comparable fixture. Test data should be disposable and clearly marked. The goal is a realistic workload shape, not a convincing set of fake customer names.

Related reading on this blog: Generating Test Data That Behaves Like the Real Thing and Install AdventureWorks and WideWorldImporters: Updated 2026.

Realistic test data is not copied production data, it is a measured shape built from safe synthetic rows.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.