API Response to Database Row Normalization Prompt Template

This prompt is designed for integration engineers and backend developers who need to ingest third-party API payloads directly into internal relational databases. The core job-to-be-done is transforming a raw, often deeply nested JSON API response into a set of flat, normalized SQL INSERT statements that match a predefined table schema. The ideal user is someone building a data pipeline, a connector, or an internal tool that must turn external, semi-structured data into queryable, relational records without manual mapping or brittle transformation scripts.

Use this prompt when the source data is a complete JSON payload from a REST or GraphQL API, and the target is a known set of database tables with defined columns, primary keys, and foreign key relationships. It is particularly effective for flattening nested resources (e.g., a user object containing an array of orders), handling paginated lists, and mapping external field names to internal column naming conventions. The prompt requires you to provide the raw API response as [INPUT], the target database schema as [CONTEXT], and any specific mapping rules or constraints as [CONSTRAINTS]. You should not use this prompt for real-time, streaming data where latency is critical, or for schemas that are not yet finalized, as the prompt's value is in strict adherence to a known structure.

Avoid using this prompt as a substitute for a proper ETL (Extract, Transform, Load) tool when dealing with high-volume, low-latency data streams or complex stateful transformations. It is best suited for batch ingestion, data migration, or building internal admin tools where a human can review the generated SQL before execution. For high-risk financial or healthcare data, always route the generated SQL to a human approval queue and never execute it directly against a production database. The next step is to copy the prompt template, fill in your specific schema and API response, and run it against a test database to validate the output.

This template is designed to be copied directly into your prompt management system or codebase. It instructs the model to act as a data integration engineer, consuming a raw JSON API response and a target database schema, then producing a set of validated, insert-ready SQL statements. The template uses square-bracket placeholders for all dynamic inputs, ensuring you can swap in different payloads, schemas, and constraints without rewriting the core instructions.

textCopy
You are a precise data integration engineer. Your task is to normalize a JSON API response into a set of INSERT statements for a target database table.

## Input
- API Response (JSON):
```json
[API_RESPONSE]

• Target Table Schema (DDL):

sqlCopy
[TARGET_TABLE_SCHEMA]

• Field Mapping Rules (JSON):

jsonCopy
[FIELD_MAPPING]

Constraints

• [CONSTRAINTS]

Output Schema

Return a JSON object with the following structure:

jsonCopy
{
  "insert_statements": ["string"],
  "row_count": "integer",
  "warnings": ["string"],
  "unmapped_fields": ["string"]
}

Instructions

1. Parse the [API_RESPONSE] to identify all record objects. If the response is paginated, flatten all pages into a single list of records.
2. For each record, use the [FIELD_MAPPING] to map API field names to database column names. Apply any specified transformation functions (e.g., to_uppercase, parse_timestamp).
3. Generate a valid SQL INSERT statement for each record against the [TARGET_TABLE_SCHEMA]. Ensure all values are correctly quoted and escaped for the target SQL dialect.
4. Explicitly handle NULL values: if a mapped field is missing or null in the API response, use the SQL NULL keyword unless a default value is specified in the constraints.
5. If a value cannot be coerced to the target column type (e.g., a string in a numeric field), exclude the row from the output and add a descriptive warning.
6. Populate the unmapped_fields list with any API fields that were present but not found in the mapping rules.
7. If [CONSTRAINTS] includes a max_rows limit, process only the first N records.

To adapt this template, start by replacing the [API_RESPONSE] and [TARGET_TABLE_SCHEMA] placeholders with your actual data. The [FIELD_MAPPING] is the most critical component; it should be a JSON object where keys are API field paths (e.g., data.user.first_name) and values are objects specifying the target column and optional transformation. The [CONSTRAINTS] placeholder allows you to inject business rules like "on_type_mismatch": "skip_row" or "default_timestamp": "CURRENT_TIMESTAMP". For high-stakes data migration, always add a final instruction requiring human review of the warnings list before executing the generated SQL against a production database.

This prompt is designed to sit inside a data ingestion service that fetches third-party API responses, normalizes nested JSON into flat relational records, and produces INSERT statements for your target database. The implementation harness should treat the LLM call as one step in a broader pipeline: fetch → transform → validate → insert → log. The prompt template expects you to supply the raw API response as [API_RESPONSE], the target table schema as [TABLE_SCHEMA], and a field mapping dictionary as [FIELD_MAPPING]. The model's job is to produce a set of INSERT statements with correctly mapped columns, resolved foreign keys, and flattened nested structures. Do not pass raw API responses directly to the database without validation.

Wire the prompt into your application with a pre-processing step that extracts pagination metadata, handles rate limiting, and splits large responses into manageable chunks. After the model returns its output, run a validation layer that checks: (1) every INSERT statement parses as valid SQL, (2) column names match the provided [TABLE_SCHEMA] exactly, (3) required NOT NULL columns are populated, (4) foreign key values reference existing records or are explicitly NULL, and (5) row counts match the expected number of records from the source payload. For high-risk ingestion pipelines, add a human review queue for records where the model's confidence is low, where nested arrays expand into more than N child rows, or where field mapping ambiguity exceeds a threshold. Log every transformation with the input payload hash, output row count, validation results, and any repair actions taken.

For model choice, prefer models with strong JSON and SQL generation capabilities and explicit schema-following behavior. Set temperature low (0.0–0.2) to maximize output determinism. Implement a retry loop with up to three attempts if validation fails, feeding the specific validation errors back into the prompt as [PREVIOUS_ERRORS] so the model can self-correct. Add schema drift detection by comparing the incoming API response structure against a known schema snapshot; if new fields appear or existing fields change type, flag the record for review rather than silently coercing values. Avoid wiring this prompt directly into synchronous user-facing flows—batch ingestion, async workers, or ETL schedules are the right execution context. The next step after validation passes is to execute the INSERT statements inside a transaction, capture any database-level constraint violations, and feed those back into the pipeline for repair or escalation.