SQL Server Test Data Generation: Tools and Workflow

Generating realistic test data for SQL Server is a recurring pain point for QA teams, developers, and DBAs. Production‑like volumes, referential integrity, data‑masking rules, and performance constraints all collide when you try to spin up a usable test database. This guide walks through the practical decisions you’ll face, the tooling landscape, a repeatable workflow, and the most common failure modes—so you can ship reliable test environments without reinventing the wheel each sprint.

---

1. Why the Problem Is Harder Than It Looks

Symptom	Root cause	Impact on testing
Foreign‑key violations after bulk insert	Data generated table‑by‑table without respecting parent‑child order	Broken integration tests, false negatives
Performance‑test data looks nothing like production	Uniform random values, no skew, no hot‑spot rows	Load‑test results don’t predict real‑world behaviour
Sensitive columns leak into lower environments	No masking or synthetic replacement strategy	Compliance risk, audit findings
Schema drift breaks the generator	Generator scripts are tightly coupled to a single schema version	Frequent manual re‑work, CI pipeline failures

If any of those sound familiar, you’re not alone. The goal of this post is to give you a decision framework and a concrete workflow that reduces those failure modes to a manageable checklist.

---

2. Prerequisites – What You Need Before You Start

Item	Why it matters	Quick verification
Current schema script (DDL)	Generators need exact column types, constraints, indexes	sqlpackage /Action:Extract /TargetFile:schema.dacpac
Data‑classification matrix	Identifies PII, PCI, PHI, and columns that must be masked	Spreadsheet or data‑catalog tagging
Target data volume	Determines whether you need bulk‑load optimisations (minimal logging, table‑lock)	Row‑count estimates per table from production statistics
Referential‑integrity map	Parent‑child ordering, self‑references, circular FKs	sp_fkeys or a generated ER diagram
CI/CD hook point	Where the generation step will run (build, release, nightly)	Pipeline YAML / Jenkinsfile location
Tool licence / access	Some commercial tools require a licence server; open‑source tools need a build agent	Verify network reachability from build agents

Tip: Capture the above in a living “Test‑Data Contract” document (Markdown in the repo). It becomes the single source of truth for every stakeholder.

---

3. Tool Landscape – Choose the Right Abstraction Level

Category	Representative tools	Strengths	Weaknesses	Typical fit
Commercial GUI‑driven generators	Redgate SQL Data Generator, dbForge Data Generator for SQL Server, ApexSQL Generate	Rich UI, built‑in masking, visual FK handling, data‑profile import	Licence cost, Windows‑only agents, limited extensibility	Teams that prefer point‑and‑click, have budget, need quick onboarding
Scriptable / CLI‑first tools	QA3 Test Data Generator (/tools/test-data-generator), SQL Data Generator (command line), dbForge CLI, custom PowerShell/Python scripts	Version‑controlled, CI‑native, can be templated, works on Linux agents	Steeper learning curve, you own the masking logic	Automation‑first shops, containerised pipelines, open‑source preference
ETL / Data‑factory approaches	Azure Data Factory, SSIS, dbt + seed models	Leverages existing data‑movement pipelines, can transform production snapshots	Requires staging area, more moving parts, latency	Organisations already invested in a data‑factory platform
Database‑native features	bcp, BULK INSERT, INSERT … SELECT from a staging table, TABLESAMPLE	No extra tooling, maximal throughput	No built‑in referential awareness, manual FK ordering	One‑off loads, very large volumes where tool overhead is unacceptable
Synthetic data libraries	Faker.NET, Bogus (C#), Faker.js (Node), go-faker	Programmatic control, easy to embed in unit‑test fixtures	Not schema‑aware, you must map objects to tables yourself	Unit‑test data, micro‑service contract tests

Decision Matrix (quick‑score)

Requirement	Weight (1‑5)	Commercial GUI	CLI / QA3	ETL	Native	Synthetic lib
CI/CD native	5	2	5	3	4	4
Referential integrity handling	5	5	4	3	2	1
Masking / compliance	4	5	4	3	2	2
Cost	3	2	5	3	5	5
Learning curve	3	5	3	2	4	4
Weighted total	—	3.6	4.2	2.8	3.2	3.0

Score is illustrative; adjust weights to your context.
For most modern DevOps pipelines the CLI / QA3 column wins because it balances automation, cost, and referential handling.

---

4. A Repeatable Generation Workflow

Below is a pipeline‑agnostic workflow you can implement in Azure Pipelines, GitHub Actions, GitLab CI, Jenkins, or any orchestrator.

┌─────────────────────┐
│ 1. Pull schema DDL   │
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 2. Render generator │
│    config (YAML/JSON)│
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 3. Run generator     │
│    (CLI / container)│
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 4. Validate output   │
│    – row counts      │
│    – FK integrity    │
│    – masking rules   │
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 5. Publish artefact  │
│    (bacpac / .sql)   │
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 6. Deploy to test DB │
│    (sqlpackage)      │
└─────────────────────┘

4.1 Step‑by‑step Details

Step	Action	Artefacts	Tools / Commands
1. Pull schema	Extract a DACPAC or generate CREATE TABLE scripts from the source (prod‑like) database.	schema.dacpac or schema.sql	sqlpackage /Action:Extract /SourceConnectionString:"..." /TargetFile:schema.dacpac
2. Render config	A templated YAML/JSON that defines: table‑level row counts, column generators, masking rules, FK order.	gen-config.yaml	Hand‑crafted + Jinja2 / Helm template; stored in repo.
3. Run generator	Execute the generator in a container to guarantee identical runtime.	generated-data.bacpac (or a folder of .sql bulk files)	docker run --rm -v $(pwd):/work qa3/test-data-generator:latest generate /work/gen-config.yaml
4. Validate	Run a validation suite (see checklist below). Fail fast if any rule breaks.	Validation report (JUnit XML)	Custom PowerShell / Python script; sqlcmd -i validate.sql
5. Publish	Push the bacpac / SQL files as a pipeline artefact.	test-data.bacpac	PublishBuildArtifacts@1
6. Deploy	Deploy to the target test instance (or spin up a container).	Running test DB	sqlpackage /Action:Publish /SourceFile:test-data.bacpac /TargetConnectionString:"..."

Why a container?
The generator (especially the QA3 CLI) may depend on .NET 8, specific NuGet packages, or a particular version of sqlpackage. Packaging it eliminates “works on my machine” drift.

---

5. Worked Example – Generating a 10 M‑row Order‑Line Dataset

Assume a simplified e‑commerce schema:

CREATE TABLE dbo.Customers (
    CustomerId   BIGINT IDENTITY PRIMARY KEY,
    Email        NVARCHAR(256) NOT NULL,
    FullName     NVARCHAR(200) NOT NULL,
    CreatedUtc   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);


CREATE TABLE dbo.Orders (
    OrderId      BIGINT IDENTITY PRIMARY KEY,
    CustomerId   BIGINT NOT NULL REFERENCES dbo.Customers(CustomerId),
    OrderDate    DATETIME2 NOT NULL,
    Status       TINYINT NOT NULL,               -- 1=New,2=Paid,3=Shipped,4=Cancelled
    TotalAmount  DECIMAL(18,2) NOT NULL
);


CREATE TABLE dbo.OrderLines (
    OrderLineId  BIGINT IDENTITY PRIMARY KEY,
    OrderId      BIGINT NOT NULL REFERENCES dbo.Orders(OrderId),
    ProductSku   NVARCHAR(50) NOT NULL,
    Quantity     INT NOT NULL,
    UnitPrice    DECIMAL(18,2) NOT NULL
);

5.1 Generator Config (YAML)

# gen-config.yaml


schema: "./schema.dacpac"
target: "./output/test-data.bacpac"


tables:
  - name: dbo.Customers
    rowCount: 500000               # 0.5 M customers
    columns:
      - name: Email
        generator: "email"         # built‑in synthetic
        unique: true
      - name: FullName
        generator: "name"
      - name: CreatedUtc
        generator: "dateTimeBetween"
        args: { start: "2018-01-01", end: "2024-12-31" }


- name: dbo.Orders
    rowCount: 2000000              # 2 M orders
    dependsOn: [dbo.Customers]     # ensures FK order
    columns:
      - name: CustomerId
        generator: "foreignKey"
        args: { table: "dbo.Customers", column: "CustomerId" }
      - name: OrderDate
        generator: "dateTimeBetween"
        args: { start: "2018-01-01", end: "2024-12-31" }
      - name: Status
        generator: "weightedChoice"
        args:
          choices: [1,2,3,4]
          weights: [0.15,0.55,0.25,0.05]   # realistic status skew
      - name: TotalAmount
        generator: "computed"
        args:
          expression: "SELECT SUM(Quantity*UnitPrice) FROM dbo.OrderLines WHERE OrderId = @OrderId"
          # The generator will evaluate after OrderLines are created (see note below)


- name: dbo.OrderLines
    rowCount: 10000000             # 10 M lines
    dependsOn: [dbo.Orders]
    columns:
      - name: OrderId
        generator: "foreignKey"
        args: { table: "dbo.Orders", column: "OrderId" }
      - name: ProductSku
        generator: "regex"
        args: { pattern: "SKU-[A-Z]{3}-\\d{4}" }
      - name: Quantity
        generator: "intBetween"
        args: { min: 1, max: 20 }
      - name: UnitPrice
        generator: "decimalBetween"
        args: { min: 0.99, max: 499.99, precision: 2 }

Notes on the example

• dependsOn drives topological ordering – the CLI builds a DAG and emits INSERT batches in the correct sequence.
• TotalAmount is a computed column that references OrderLines. The generator handles this by first materialising OrderLines into a temp table, aggregating per OrderId, then updating Orders. If your tool cannot do cross‑table computation, fall back to a post‑generation SQL script.
• weightedChoice gives you realistic status distribution without hand‑coding a case statement.

5.2 Running the Generator

# Build the container once (CI caches layers)


docker build -t qa3/test-data-generator:latest -f Dockerfile.generator .


# Execute inside the pipeline


docker run --rm \
  -v $(pwd):/work \
  qa3/test-data-generator:latest \
  generate /work/gen-config.yaml

The run produces output/test-data.bacpac (≈ 3 GB for the above volumes). Deploy time on a clean Azure SQL DB (General Purpose, 8 vCore) is ~12 minutes with sqlpackage /p:BatchSize=50000.

5.3 Validation Script (run after deploy)

-- validate.sql
SET NOCOUNT ON;


-- 1. Row‑count sanity
SELECT 'Customers' AS TableName, COUNT(*) AS RowCnt FROM dbo.Customers
UNION ALL SELECT 'Orders', COUNT(*) FROM dbo.Orders
UNION ALL SELECT 'OrderLines', COUNT(*) FROM dbo.OrderLines;


-- 2. FK integrity (should return 0 rows)
SELECT 'Orphan Orders' AS CheckName, COUNT(*) AS Violations
FROM dbo.Orders o LEFT JOIN dbo.Customers c ON o.CustomerId = c.CustomerId
WHERE c.CustomerId IS NULL
UNION ALL
SELECT 'Orphan OrderLines', COUNT(*)
FROM dbo.OrderLines ol LEFT JOIN dbo.Orders o ON ol.OrderId = o.OrderId
WHERE o.OrderId IS NULL;


-- 3. Masking verification – no real email domains
SELECT TOP 1 'RealDomainLeak' AS CheckName, Email
FROM dbo.Customers
WHERE Email LIKE '%@gmail.com' OR Email LIKE '%@outlook.com';


-- 4. Distribution checks
SELECT Status, COUNT(*) AS Cnt, COUNT(*) * 1.0 / SUM(COUNT(*)) OVER() AS Pct
FROM dbo.Orders GROUP BY Status;


-- 5. TotalAmount consistency (sample 1000 orders)
SELECT TOP 1000 o.OrderId,
       o.TotalAmount,
       SUM(ol.Quantity * ol.UnitPrice) AS ComputedTotal
FROM dbo.Orders o
JOIN dbo.OrderLines ol ON ol.OrderId = o.OrderId
GROUP BY o.OrderId, o.TotalAmount
HAVING ABS(o.TotalAmount - SUM(ol.Quantity * ol.UnitPrice)) > 0.01;

The pipeline fails if any Violations > 0 or the RealDomainLeak query returns rows.

---

6. Validation Checklist – Run Every Time

#	Check	Automation	Pass criteria
1	Row‑count thresholds (min / max per table)	SQL script + CI gate	Within ±5 % of config
2	Foreign‑key integrity (no orphans)	Same script	0 violations
3	Unique constraints (PK, AK)	SELECT COUNT(*) - COUNT(DISTINCT …)	0 duplicates
4	Masking compliance (PII columns synthetic)	Regex / dictionary scan	0 matches to production patterns
5	Statistical distribution (status, dates, numeric ranges)	Chi‑square / KS test (or visual)	p‑value > 0.05 vs. target profile
6	Checksum / hash of non‑masked columns	CHECKSUM_AGG	Matches baseline (detects drift)
7	**Performance baseline