Generating Referentially Consistent Synthetic Test Data for PostgreSQL in .NET Application Modernization

Satyajit Gokhale, Aiswaryaa Venugopalan, Ruchi Agarwal, Nits Jeganathan, Khurram Khawaja, Vijay Mandadi, and Luke Huan contributed to this article.

Within the .NET application modernization service of AWS Transform, enterprise customers are also provided an option to migrate the database layer of the application from Microsoft SQL Server to Amazon Aurora PostgreSQL Compatible Edition. Modernizing to PostgreSQL database helps in eliminating proprietary licensing costs, unlocking cloud-native scalability, and using a mature open-source ecosystem. While schema conversion tools translate the source DDL into PostgreSQL-compatible definitions, they cannot guarantee correctness. Teams must still confirm that the resulting schema holds up under real-world workloads, a step that ultimately determines whether the migration succeeds or fails.

Traditional approaches rely on testing against production data or manual spot-checks, and neither of these scales well. Testing against real customer data raises privacy and compliance concerns, while manual generation is infeasible for enterprise schemas with hundreds of interrelated tables. To address these concerns, we need realistic synthetic data generated at scale that respects all constraints laid out in the migrated schema.

In this post, we describe how AWS Transform for SQL Server modernization uses an agentic solution to generate constraint-respecting synthetic data at scale as part of the SQL Server modernization workflow during .NET application modernization, by deriving its execution strategy directly from the schema’s relational structure without manual configuration. To learn more about synthetic data generation in AWS Transform for SQL Server modernization, refer to Create SQL Server modernization job.

Solution Overview

The synthetic data generation AI agent in AWS Transform provides automated, end-to-end population of migrated PostgreSQL schemas by deploying the target to a live PostgreSQL database engine and generating referentially consistent data directly against it. This AI agent is developed using Amazon Strands Agents framework, where all large language model (LLM) invocations are through Amazon Bedrock . This Strands-based AI agent validates that the populated database satisfies all foreign key, uniqueness, check, and type constraints.

Figure 1: The synthetic data generation workflow

As shown in Figure 1, the synthetic data generation proceeds as follows:

1. The agent loads the PostgreSQL schema DDL into a live PostgreSQL database instance and extracts metadata by querying the system catalogs directly.
2. The agent builds a dependency graph that establishes the generation order, respecting foreign key (FK) relationships between tables so that it populates parent tables before their children tables.
3. The agent distributes tables across multiple sub-agents that operate in parallel, adhering to the generation order. For foreign key relationships, the sub-agents generating data for child tables can access the data generated for parent tables.
4. The agent validates each generated INSERT against the live PostgreSQL database instance (through an Amazon ECS container). When constraint violations occur, we feed structured error diagnostics back to the sub-agent, which then regenerates the data.

Walkthrough

Under the hood, this workflow runs as four discrete stages of 1) schema analysis, 2) establishing generation order, 3) distribution of generation order across multiple sub-agents, and 4) validation, error recovery, and iterative generation of synthetic data. This entire workflow for generating validated synthetic data from a DB schema is grounded on two core principles:

• Ground truth from the DB engine. The agent introspects system catalogs on the live database and applies deterministic dependency analysis before invoking the LLM so that generation happens within a verified constraint space rather than relying on regex-parsed DDL or unconstrained model output.
• Validated and realistic synthetic data. The agent inserts generated rows against the live database to confirm foreign-key integrity, type conformance, and constraint satisfaction before the dataset is accepted. Unlike rule-based generators that only satisfy format and type constraints, LLM-driven generation produces field values that are semantically coherent and representative of the target domain.

With those principles in mind, the four stages work as follows:

1. Schema Analysis

Objective: Extract all metadata from the target PostgreSQL schema for downstream constraint-aware generation.

The agent first loads the schema into a live PostgreSQL instance and extracts all metadata using psql by querying the information_schema and system catalogs directly rather than using regex to parse DDL files. This is an intentional design choice: regex parsing is brittle against dialect variations, conditional DDL, and vendor-specific syntax, whereas querying the database engine’s own catalogs returns exactly what the engine understood. Any errors encountered during schema loading (for example, unsupported object types or syntax issues) are surfaced for review before proceeding.

The extracted metadata includes:

• Tables with columns, types, and nullability
• Primary keys and unique constraints
• Foreign key relationships (including multi-column composite keys)
• CHECK constraints and default values

2. Establish Generation Order

Objective: Determine a valid table generation sequence that respects all foreign key dependencies.

Using the extracted metadata, the agent constructs a Directed Acyclic Graph (DAG) where nodes represent tables and edges represent foreign key relationships. A valid generation order is established using topological sort (Kahn’s algorithm). Tables with no dependencies (with in-degree zero in the DAG) are generated first, followed by tables that depend on them. This level-ordering is used in the next stage to maximize parallelism while respecting all dependency constraints.

For composite foreign keys (multi-column references), the agent additionally identifies the specific column groups that form joint constraints. This distinction is important because individually valid column values, when combined, may not correspond to any actual row in the parent table, which is a constraint satisfaction problem that single-column analysis would miss.

3. Distribute Generation Across Multiple Sub-Agents

Objective: Maximize throughput by parallelizing data generation while guaranteeing referential integrity.

Using the DAG from the previous stage, the agent assigns tables to parallel sub-agents based on their relational dependencies. The key principle: tables that are transitively connected through foreign key relationships are assigned to the same sub-agent. This allows each sub-agent to resolve all FK dependencies locally without needing to communicate with other sub-agents to obtain parent table values.

Tables with no foreign key relationships (independent tables) are distributed across sub-agents for load balancing, providing early parallelism. Within each sub-agent’s assignment, tables are processed sequentially in dependency order, i.e, parent objects before child objects. The sub-agents themselves execute in parallel.

4. Validation, Error Recovery, and Iterative Generation

Objective: Generate and validate data row-by-row against the live database, recovering from failures automatically.

Each sub-agent processes its assigned tables one at a time. For each table, the agent generates INSERT statements using LLM, with the generation prompt informed by schema context (column types, constraints) and actual rows from parent tables already persisted in the live database.

The live PostgreSQL database instance serves a dual role during this stage:

• Validation. Each generated INSERT is executed against the live database. Success confirms that all constraints (such as, type, CHECK, FK, UNIQUE, NOT NULL) are satisfied.
• State accumulation. Successfully inserted rows are committed to the database, where they become queryable as FK reference values for downstream tables, meaning the generation context grows richer as the process progresses.

When a generated INSERT fails, the database returns structured error diagnostics including the constraint violated, affected columns, and conflicting values. These diagnostics are fed back into the generation prompt, and the agent re-generates. If a table cannot be populated after repeated corrective attempts, it is marked as failed with a diagnostic summary for manual review.

The agent respects referential integrity during error recovery: it will not modify or remove rows from parent tables that have already been referenced by populated child tables. Generation state is persisted after each table completes, so that the process to resume from any interruption point without data loss.

Conclusion

In the process of modernizing a .NET application, enterprises often want to modernize the database layer by migrating from Microsoft SQL Server to Amazon Aurora PostgreSQL Compatible Edition. Before committing real data to a newly migrated schema (in PostgreSQL), teams need confidence that all database operations will behave correctly. However, subtle FK violations, constraint mismatches, and type conversion errors only surface under realistic data load. Generating referentially consistent synthetic data bridges this trust gap: it tests the target schema without exposing sensitive production data, so that when the actual migration runs, the schema has already been proven sound.

The multi-agent approach described in this post automates this process by deriving its execution strategy directly from the schema’s foreign key structure, distributing work across parallel sub-agents without manual orchestration, and using the live database as both validator and generation context source. Together, these design choices support end-to-end synthetic data generation for enterprise-scale schemas with hundreds of interrelated tables.

To get started, try generating synthetic data for your first schema migration with AWS Transform. For more information, see the following resources:

• AWS Transform
• Amazon Aurora PostgreSQL
• Amazon Bedrock
• Amazon Strands Agents