How to Use LLMs to Write Data Transformation Code (dbt + ChatGPT Demo)

Writing data transformation logic used to be one of the most time-consuming parts of analytics engineering. But with Large Language Models (LLMs) like ChatGPT, Claude, or Gemini, data professionals can now automate SQL generation, create dbt models faster, and reduce human error without compromising accuracy.

In this guide, i’ll show how to use ChatGPT to write dbt code, clean transformations, and optimize queries plus a live demo of how LLMs can supercharge your modern data stack.

What Are LLMs and Why They Matter in Data Engineering

LLMs (Large Language Models) are AI systems trained on massive text datasets. They understand and generate human-like language including programming and SQL syntax.
For data engineers, that means you can:

• Generate dbt models from natural language.
• Convert messy logic into clean, optimized SQL.
• Automate documentation and schema creation.
• Get AI-driven explanations for complex transformations.

How dbt and ChatGPT Work Together

dbt (Data Build Tool) helps analysts and engineers transform raw data into clean, usable tables. Pairing dbt with ChatGPT allows you to accelerate model creation by describing transformations in plain English.

Example Prompt:

“Write a dbt model that aggregates customer revenue by region and product category, excluding cancelled orders.”

ChatGPT Output:

SELECT 
  region,
  product_category,
  SUM(order_amount) AS total_revenue
FROM {{ ref('orders') }}
WHERE order_status != 'cancelled'
GROUP BY region, product_category

You can directly paste this SQL into your dbt model , saving hours of manual writing.

Automating Documentation with LLMs

ChatGPT can also write dbt model documentation:

“Generate YAML schema for the above model with column descriptions.”

This returns:

version: 2
models:
  - name: customer_revenue_by_region
    description: "Aggregates customer revenue by region and product category."
    columns:
      - name: region
        description: "Geographic region of the customer."
      - name: product_category
        description: "Product category purchased."
      - name: total_revenue
        description: "Sum of all order amounts excluding cancelled orders."

Advanced Prompt Tips for Better dbt Code

1. Be specific – include table names, conditions, and metrics.
2. Ask for optimization – e.g., “optimize this for Snowflake.”
3. Request explanations – LLMs can explain why they chose certain joins or filters.
4. Validate results – Always test the generated code in staging before production.

Demo: ChatGPT + dbt Workflow

1. Describe your transformation goal in plain English.
2. Let ChatGPT generate the SQL/dbt model.
3. Review logic for accuracy and performance.
4. Paste the code into your dbt project.
5. Run dbt test to validate.

This workflow can reduce coding time by up to 60% for routine transformations.

Limitations & Best Practices

While LLMs are powerful, they can:

• Produce incorrect SQL if context is missing.
• Over-simplify complex joins.
• Miss edge cases or schema nuances.

Use AI as a co-pilot, not a replacement. Always review, test, and document.

FAQ