Introduction

A reliable test environment needs realistic, consistent, and compliant data. Manual seeding or ad-hoc scripts produce brittle test sets: missing referential integrity, unrealistic distributions, PII leakage, and hard-to-reproduce failures. A Test Data Fabric is an automated, repeatable platform that generates relational test data across schemas while respecting constraints, business rules, and privacy.

This article explains how to design and implement a production-ready Test Data Fabric using .NET for backend orchestration, SQL Server / Postgres for target databases, and an Angular UI for configuration and preview. You’ll get architecture, data models, algorithms (graph-walk, dependency resolution, domain distribution), implementation patterns, sample code snippets, testing and governance, and operational tips.

Goals and Non-goals

Goals

Non-goals

High-Level Architecture

┌──────────────────────────┐    ┌──────────────────────┐    ┌──────────────────┐
│ Angular Admin Console    │ -> │ Orchestration API    │ -> │ Target Databases │
│ (configure fabric, jobs) │    │ (.NET Core)          │    │ (SQL Server/Postgres)
└──────────┬───────────────┘    └──┬───────────────────┘    └──────┬───────────┘
           │                        │                               │
           ▼                        │                               ▼
┌──────────────────────────┐        │                     ┌────────────────────┐
│ Template / Rules Store   │ <------┘                     │ Data Generators     │
│ (JSON/YAML/DB)           │                              │ (Generators, Faker) │
└──────────────────────────┘                              └────────────────────┘

Components

Key Concepts

Schema Graph

Model tables and FK edges as a directed acyclic graph (DAG). Generation order respects dependencies: parent tables first (or use multi-pass strategies for circular references).

Templates and Column Profiles

Each column has a profile: type, generator, domain constraints, uniqueness, nullable, distribution (zipf, normal, uniform), masking rules for PII.

Example column profile

{
  "table":"Customer",
  "column":"Email",
  "generator":"EmailGenerator",
  "uniqueness":"global",
  "nullable":false,
  "distribution": { "type":"categorical", "values":["gmail.com","yahoo.com","corp.com"], "weights":[0.5,0.3,0.2] }
}

Referential Integrity Strategies

Domain Awareness

Domain rules encode business invariants: e.g., Order.Total == SUM(OrderLine.Amount), Stock >= 0. The fabric must either generate consistent aggregates or run reconciliation jobs post-generation.

Reproducibility

Seeded PRNGs with recorded seed per job produce identical datasets for the same configuration. Persist seeds and job manifests for replay.

Workflow Diagram

1. User configures template in Angular UI and requests N customers, M orders.
2. UI sends template + job request to .NET Orchestration API.
3. Orchestration resolves schema graph, selects generators, computes volumes per table.
4. Generator modules produce rows in-memory or buffered to disk, mapping PKs and FKs.
5. Orchestration writes to temporary staging tables or directly bulk-loads target DB.
6. Post-checks run: constraints, business rules, uniqueness audits.
7. Job summary and sample data returned to UI for review.

Detailed Implementation Steps

1 — Schema Introspection

Read database schema (INFORMATION_SCHEMA or system catalog) to build table metadata:

.NET snippet using Npgsql / SqlClient:

using var conn = new NpgsqlConnection(connStr);
await conn.OpenAsync();
var cmd = new NpgsqlCommand("SELECT table_name,column_name,data_type FROM information_schema.columns WHERE table_schema='public'", conn);
using var rdr = await cmd.ExecuteReaderAsync();
while (await rdr.ReadAsync()) {
  // build metadata
}

2 — Build Dependency Graph

Construct graph with nodes as tables, edges from parent → child (FK direction). Detect cycles; for cycles use deferred constraints or placeholder PKs.

3 — Plan Volumes

User provides high-level volumes (e.g., 10k customers, average 5 orders/customer). Orchestration expands to table row counts using per-table multi-assignment:

Orders = Customers * avgOrders
OrderLines = Orders * avgLines

Allow variability (Poisson sampling) for realistic skew.

4 — Choose Generators

Generators are modular. Examples:

Generators accept config: locale, distribution, nullRate, uniqueness key.

5 — Key Mapping & FK Assignment

While generating parent rows, store mapping from synthetic natural key to DB PK (or generate PK on the fly). For large volumes, avoid storing full mapping in memory: use streaming with deterministic generation or use bucketed mapping saved to local storage (e.g., RocksDB or Redis) to look up when generating children.

Example deterministic mapping:

6 — Bulk Load

Write generated rows to CSV/NDJSON and use DB bulk loaders:

Use temporary staging schema to reduce locks and then INSERT INTO target SELECT ....

7 — Enforce Business Rules

For rules like totals, either:

Prefer child-first for aggregates (generate lines then compute parent totals) if possible.

8 — Post-Checks and Repair

Run validation checks:

Algorithms and Techniques

Graph Walk Generation

Topological sort tables; for each table:

  1. Determine count.

  2. For i in 1..count:

    • Generate PK (or request identity).

    • Generate non-FK columns.

    • If FK to already-generated parent(s), choose parent via distribution (uniform, Zipfian).

    • Attach row to batch.

For cycles, use deferred constraints or iterative fill.

Sampling Distributions

Support distributions to mimic real data:

Implement RNG with seeded source so sampling is repeatable.

Uniqueness and Collisions

Uniqueness generator must check collisions. For high-volume uniqueness, use deterministic namespace + counter (e.g., CUST-{seed}-{i}) instead of random to avoid costly checks.

Referential Selection Strategies

When picking parent for child:

Domain Awareness Examples

Angular Admin UI Ideas

UI should let product engineers and QA:

Example components

Security, Compliance & PII Handling

Testing Strategy

Performance Considerations

Operational Playbook

Example: Minimal .NET Generator Skeleton

public class TableGenerator
{
    private readonly IGeneratorFactory _fact;
    private readonly Random _rng;

    public TableGenerator(IGeneratorFactory fact, int seed) {
        _fact = fact; _rng = new Random(seed);
    }

    public async Task GenerateAsync(TableMetadata meta, long count, Func<long, long> pickParent) {
        var batch = new List<Dictionary<string, object>>();
        for(long i=0;i<count;i++) {
            var row = new Dictionary<string, object>();
            row[meta.PkColumn] = meta.GeneratePk(i);
            foreach(var col in meta.Columns.Where(c => !c.IsFk)) {
                var gen = _fact.Create(col.Profile);
                row[col.Name] = gen.Next(_rng);
            }
            foreach(var fk in meta.ForeignKeys) {
                row[fk.Column] = pickParent(i); // deterministic selection
            }
            batch.Add(row);
            if(batch.Count >= 10000) {
                await BulkInsert(meta.TableName, batch);
                batch.Clear();
            }
        }
        if(batch.Count>0) await BulkInsert(meta.TableName, batch);
    }
}

Common Pitfalls and Remedies

Summary

A Test Data Fabric transforms seeding from ad-hoc scripts into a repeatable, auditable, domain-aware process. Key takeaways: