Transformation Code Generation

Transformation Code Generation

Context

Once a source-to-target mapping (STM) has been reviewed for gaps and ambiguities (see the STM Analysis prompt), this prompt generates working SQL or PySpark transformation code from the cleaned-up specification. The AI implements each rule exactly as written, flags any remaining ambiguities as inline comments, and lists post-generation issues for review.

Run this prompt after completing the STM Analysis to ensure gaps and ambiguities are resolved first. Always review the generated code against the actual source schema before running — the AI works from the mapping description, not the live database.

The Prompt

Generate [SQL / PySpark] transformation code from the following source-to-target mapping.

For each target field, implement the specified transformation rule exactly. Where the rule is ambiguous, flag it with a comment and implement the most conservative interpretation.

Target table: [TARGET TABLE NAME]
Source table(s): [SOURCE TABLE NAMES]
Database / dialect: [e.g. Snowflake SQL / SparkSQL / PostgreSQL]

Mapping:
| Target field | Source field | Transformation rule |
|---|---|---|
| [field] | [source] | [rule — e.g. "direct copy", "UPPER()", "lookup in ref table X"] |
| ... | ... | ... |

Additional rules:
- [Any join conditions, filter criteria, or business rules not captured in the table above]

After generating the code, list:
1. Any ambiguous rules you had to interpret
2. Any source fields referenced but not found in the mapping
3. Any target fields with no source mapping (will need a default or manual input)

Usage Instructions

1. Open build-cli in your terminal.
2. Copy the prompt above.
3. Fill in the SQL dialect or PySpark context (for PySpark, specify whether you’re using DataFrames or Spark SQL, and mention the cluster or AWS Glue version if relevant).
4. Paste your STM as a table — the more structured the mapping, the better the generated code.
5. Review the generated code against the actual source schema before running.
6. For PySpark output: follow up with the Python Pipeline Review pattern before submitting for peer review.

Example Output

The AI will produce transformation code such as:

-- Snowflake SQL: stg_orders → mart_order_summary
INSERT INTO mart_order_summary (
    order_id,
    customer_key,
    order_date,
    order_status,
    total_amount_usd
)
SELECT
    o.order_id,
    c.customer_key,
    TRY_TO_DATE(o.order_date_str, 'YYYY-MM-DD') AS order_date,  -- VARCHAR → DATE
    UPPER(TRIM(o.status)) AS order_status,
    -- AMBIGUOUS: exchange rate table not specified; using fx_rates.daily_rate as of order_date
    o.total_amount * fx.daily_rate AS total_amount_usd
FROM stg_orders o
JOIN dim_customer c ON o.customer_id = c.source_customer_id
LEFT JOIN fx_rates fx ON fx.currency = o.currency AND fx.rate_date = TRY_TO_DATE(o.order_date_str, 'YYYY-MM-DD')
WHERE o.load_date = CURRENT_DATE;

Post-generation issues:

1. Exchange rate table ambiguity (see comment) — confirm with business which rate to use.
2. record_status target field has no source mapping — defaulting to NULL; add a default or mapping rule.