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 presented with the opportunity to migrate their database layer from Microsoft SQL Server to the Amazon Aurora PostgreSQL Compatible Edition. This transition to a PostgreSQL database not only eliminates proprietary licensing costs but also unlocks cloud-native scalability and leverages a robust open-source ecosystem. Although schema conversion tools can translate the source DDL into PostgreSQL-compatible definitions, they do not guarantee correctness. It remains imperative for teams to verify that the resulting schema performs well under real-world workloads, a crucial step that ultimately dictates the success or failure of the migration.

Traditional methodologies often depend on testing against production data or conducting manual spot-checks, neither of which scales effectively. Testing with actual customer data raises privacy and compliance issues, while manual generation becomes impractical for enterprise schemas that may contain hundreds of interrelated tables. To tackle these challenges, there is a need for realistic synthetic data that can be generated at scale, adhering to all constraints defined in the migrated schema.

This article explores how AWS Transform for SQL Server modernization employs an agentic solution to generate constraint-respecting synthetic data at scale. This process is integrated into the SQL Server modernization workflow during .NET application modernization, deriving its execution strategy directly from the schema’s relational structure without requiring manual configuration. For further insights into synthetic data generation in AWS Transform for SQL Server modernization, please refer to Create SQL Server modernization job.

Solution Overview

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

Figure 1: The synthetic data generation workflow

As illustrated in Figure 1, the synthetic data generation process unfolds 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 constructs a dependency graph that establishes the order of generation, ensuring that foreign key (FK) relationships are respected by populating parent tables prior to their child tables.
3. The agent distributes tables across multiple sub-agents that operate in parallel, adhering to the established generation order. Sub-agents generating data for child tables can access the data created for parent tables.
4. The agent validates each generated INSERT against the live PostgreSQL database instance (via an Amazon ECS container). In cases of constraint violations, structured error diagnostics are relayed back to the sub-agent, prompting a regeneration of the data.

Walkthrough

This workflow operates through four distinct stages: 1) schema analysis, 2) establishing generation order, 3) distributing the generation order across multiple sub-agents, and 4) validation, error recovery, and iterative generation of synthetic data. The entire process of generating validated synthetic data from a database schema is anchored on two fundamental principles:

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

With these principles in mind, the four stages function as follows:

1. Schema Analysis

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

The agent begins by loading the schema into a live PostgreSQL instance and extracting all metadata using psql through direct queries to the information_schema and system catalogs. This design choice is intentional: regex parsing can be fragile against dialect variations, conditional DDL, and vendor-specific syntax, while querying the database engine’s catalogs provides precise information as understood by the engine. Any errors encountered during schema loading, such as unsupported object types or syntax issues, are flagged for review before proceeding.

The extracted metadata encompasses:

• 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.

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

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

3. Distribute Generation Across Multiple Sub-Agents

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

Leveraging the DAG from the previous stage, the agent assigns tables to parallel sub-agents based on their relational dependencies. The guiding principle is that tables connected through foreign key relationships are allocated to the same sub-agent, allowing local resolution of all FK dependencies without inter-sub-agent communication for parent table values.

Independent tables, devoid of foreign key relationships, are distributed among sub-agents for load balancing, facilitating early parallelism. Within each sub-agent’s assignment, tables are processed sequentially in dependency order, meaning parent objects are handled before child objects. The sub-agents themselves operate in parallel.

4. Validation, Error Recovery, and Iterative Generation

Objective: Generate and validate data row-by-row against the live database, with automatic recovery from failures.

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

During this stage, the live PostgreSQL database instance serves a dual purpose:

• 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, becoming queryable as FK reference values for downstream tables, enriching the generation context as the process advances.

In instances where a generated INSERT fails, the database returns structured error diagnostics, detailing the violated constraint, affected columns, and conflicting values. These diagnostics are integrated back into the generation prompt, prompting the agent to regenerate. If a table cannot be populated after repeated corrective attempts, it is marked as failed, accompanied by a diagnostic summary for manual review.

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